Обсуждение: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge
Hi,
I recently came across a performance difference between two machines that surprised me:
Postgres Version / OS on both machines: v9.6.3 / MacOS 10.12.5
Machine A: MacBook Pro Mid 2012, 2.7 GHz Intel Core i7 (Ivy Bridge), 8 MB L3 Cache, 16 GB 1600 MHz DDR3 [1]
Machine B: MacBook Pro Late 2016, 2.6 GHz Intel Core i7 (Skylake), 6 MB L3 Cache,16 GB 2133 MHz LPDDR3 [2]
Query Performance on Machine A: [3]
CTE Scan on zulu (cost=40673.620..40742.300 rows=3434 width=56) (actual time=6339.404..6339.462 rows=58 loops=1)
CTE zulu
-> HashAggregate (cost=40639.280..40673.620 rows=3434 width=31) (actual time=6339.400..6339.434 rows=58
loops=1)
Group Key: mike.two, mike.golf
-> Unique (cost=37656.690..40038.310 rows=34341 width=64) (actual time=5937.934..6143.161 rows=298104
loops=1)
-> Sort (cost=37656.690..38450.560 rows=317549 width=64) (actual time=5937.933..6031.925
rows=316982loops=1)
Sort Key: mike.two, mike.lima, mike.echo DESC, mike.quebec
Sort Method: quicksort Memory: 56834kB
-> Seq Scan on mike (cost=0.000..8638.080 rows=317549 width=64) (actual time=0.019..142.831
rows=316982loops=1)
Filter: (golf five NOT NULL)
Rows Removed by Filter: 26426
Query Performance on Machine B: [4]
CTE Scan on zulu (cost=40621.420..40690.100 rows=3434 width=56) (actual time=853.436..853.472 rows=58 loops=1)
CTE zulu
-> HashAggregate (cost=40587.080..40621.420 rows=3434 width=31) (actual time=853.433..853.448 rows=58 loops=1)
Group Key: mike.two, mike.golf
-> Unique (cost=37608.180..39986.110 rows=34341 width=64) (actual time=634.412..761.678 rows=298104
loops=1)
-> Sort (cost=37608.180..38400.830 rows=317057 width=64) (actual time=634.411..694.719 rows=316982
loops=1)
Sort Key: mike.two, mike.lima, mike.echo DESC, mike.quebec
Sort Method: quicksort Memory: 56834kB
-> Seq Scan on mike (cost=0.000..8638.080 rows=317057 width=64) (actual time=0.047..85.534
rows=316982loops=1)
Filter: (golf five NOT NULL)
Rows Removed by Filter: 26426
As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine B when looking at the "Exclusive" time
withexplain.depesz.com [3][4]. I.e. Machine B is ~10x faster at sorting than Machine B (for this particular query).
My question is: Why?
I understand that this is a 3rd gen CPU vs a 6th gen, and that things have gotten faster despite stagnant clock speeds,
butseeing a 10x difference still caught me off guard.
Does anybody have some pointers to understand where those gains are coming from? Is it the CPU, memory, or both? And in
particular,why does Sort benefit so massively from the advancement here (~10x), but Seq Scan, Unique and HashAggregate
don'tbenefit as much (~2x)?
As you can probably tell, my hardware knowledge is very superficial, so I apologize if this is a stupid question. But
I'dgenuinely like to improve my understanding and intuition about these things.
Cheers
Felix Geisendörfer
[1]
http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-core-i7-2.7-15-mid-2012-retina-display-specs.html
[2]
http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-core-i7-2.6-15-late-2016-retina-display-touch-bar-specs.html
[3] https://explain.depesz.com/s/hmn
[4] https://explain.depesz.com/s/zVe
=?utf-8?Q?Felix_Geisend=C3=B6rfer?= <felix@felixge.de> writes:
> I recently came across a performance difference between two machines that surprised me:
> ...
> As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine B when looking at the "Exclusive" time
withexplain.depesz.com [3][4]. I.e. Machine B is ~10x faster at sorting than Machine B (for this particular query).
I doubt this is a hardware issue, it's more likely that you're comparing
apples and oranges. The first theory that springs to mind is that the
sort keys are strings and you're using C locale on the faster machine but
some non-C locale on the slower. strcoll() is pretty darn expensive
compared to strcmp() :-(
regards, tom lane
On Fri, Aug 25, 2017 at 8:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I doubt this is a hardware issue, it's more likely that you're comparing > apples and oranges. The first theory that springs to mind is that the > sort keys are strings and you're using C locale on the faster machine but > some non-C locale on the slower. strcoll() is pretty darn expensive > compared to strcmp() :-( strcoll() is very noticeably slower on macOS, too. -- Peter Geoghegan
> On Aug 25, 2017, at 17:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?utf-8?Q?Felix_Geisend=C3=B6rfer?= <felix@felixge.de> writes:
>> I recently came across a performance difference between two machines that surprised me:
>> ...
>> As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine B when looking at the "Exclusive" time
withexplain.depesz.com [3][4]. I.e. Machine B is ~10x faster at sorting than Machine B (for this particular query).
>
> I doubt this is a hardware issue, it's more likely that you're comparing
> apples and oranges. The first theory that springs to mind is that the
> sort keys are strings and you're using C locale on the faster machine but
> some non-C locale on the slower. strcoll() is pretty darn expensive
> compared to strcmp() :-(
You're right, that seems to be it.
Machine A was using strcoll() (lc_collate=en_US.UTF-8)
Machine B was using strcmp() (lc_collate=C)
After switching Machine A to use lc_collate=C, I get:
CTE Scan on zulu (cost=40673.620..40742.300 rows=3434 width=56) (actual time=1368.610..1368.698 rows=58 loops=1)
CTE zulu
-> HashAggregate (cost=40639.280..40673.620 rows=3434 width=56) (actual time=1368.607..1368.659 rows=58
loops=1)
Group Key: mike.two, ((mike.golf)::text)
-> Unique (cost=37656.690..40038.310 rows=34341 width=104) (actual time=958.493..1168.128 rows=298104
loops=1)
-> Sort (cost=37656.690..38450.560 rows=317549 width=104) (actual time=958.491..1055.635
rows=316982loops=1)
Sort Key: mike.two, ((mike.lima)::text) COLLATE "papa", mike.echo DESC, mike.quebec
Sort Method: quicksort Memory: 56834kB
-> Seq Scan on mike (cost=0.000..8638.080 rows=317549 width=104) (actual time=0.043..172.496
rows=316982loops=1)
Filter: (golf five NOT NULL)
Rows Removed by Filter: 26426
So Machine A needs 883ms [1] for the sort vs 609ms [2] for Machine B. That's ~1.4x faster which seems reasonable :).
Sorry for the delayed response, I didn't have access to machine B to confirm this right away.
> regards, tom lane
This is my first post to a PostgreSQL mailing list, but I've been lurking
for a while. Thank you for taking the time for replying to e-mails such
as mine and all the work you've put into PostgreSQL over the years.
I'm deeply grateful.
> On Aug 25, 2017, at 17:43, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Fri, Aug 25, 2017 at 8:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I doubt this is a hardware issue, it's more likely that you're comparing
>> apples and oranges. The first theory that springs to mind is that the
>> sort keys are strings and you're using C locale on the faster machine but
>> some non-C locale on the slower. strcoll() is pretty darn expensive
>> compared to strcmp() :-(
>
> strcoll() is very noticeably slower on macOS, too.
>
Thanks. This immediately explains what I saw when testing this query on a Linux machine that was also using
lc_collate=en_US.UTF-8but not being slowed down by it as much as the macOS machine.
[1] https://explain.depesz.com/s/LOqa
[2] https://explain.depesz.com/s/zVe