pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcpu comparison
9+ messages / 7 participants
[nested] [flat]
* cpu comparison
@ 2011-07-18 19:48 M. D. <lists@turnkey.bz>
0 siblings, 3 replies; 9+ messages in thread
From: M. D. @ 2011-07-18 19:48 UTC (permalink / raw)
To: pgsql-performance
I have 2 small servers, one a fairly new server with a x3450 (4-core
with HT) cpu running at 2.67GHz and an older E5335 (4-core) cpu running
at 2GHz.
I have been quite surprised how the E5335 compares very closely to the
x3450, but maybe I have tested it wrongly.
here's the CPUINFO:
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
stepping : 7
cpu MHz : 1995.036
cache size : 4096 KB
physical id : 3
siblings : 1
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu tsc msr pae cx8 apic mtrr cmov pat clflush acpi
mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc pni vmx ssse3 cx16
lahf_lm
bogomips : 4989.65
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
OS: CentOS 64bit
Postgres: 9.0.4 compiled
processor : 7
vendor_id : GenuineIntel
cpu family : 6
model : 30
model name : Intel(R) Xeon(R) CPU X3450 @ 2.67GHz
stepping : 5
cpu MHz : 2660.099
cache size : 8192 KB
physical id : 0
siblings : 8
core id : 3
cpu cores : 4
apicid : 7
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx
rdtscp lm constant_tsc ida nonstop_tsc pni monitor ds_cpl vmx smx est
tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm [8]
bogomips : 5319.92
OS: CentOS 32bit
Postgres: 9.0.4 compiled
In my testing I have a 32bit CentOS on the x3450, but a 64bit CentOS on
the E5335. Can this make such a bit difference or should the perform
fairly close to the same speed? Both servers have 8GB of RAM, and the
database I tested with is only 3.7GB.
I'm a bit surprised as the x3450 has DDR3, while the E5335 has DDR2, and
of course because of the cycle speed difference alone I would think the
X3450 should beat the E5335.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-18 20:11 ktm@rice.edu <ktm@rice.edu>
parent: M. D. <lists@turnkey.bz>
2 siblings, 1 reply; 9+ messages in thread
From: ktm@rice.edu @ 2011-07-18 20:11 UTC (permalink / raw)
To: M. D. <lists@turnkey.bz>; +Cc: pgsql-performance
On Mon, Jul 18, 2011 at 01:48:20PM -0600, M. D. wrote:
> I have 2 small servers, one a fairly new server with a x3450 (4-core
> with HT) cpu running at 2.67GHz and an older E5335 (4-core) cpu
> running at 2GHz.
>
> I have been quite surprised how the E5335 compares very closely to
> the x3450, but maybe I have tested it wrongly.
>
> here's the CPUINFO:
> processor : 3
> vendor_id : GenuineIntel
> cpu family : 6
> model : 15
> model name : Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
> stepping : 7
> cpu MHz : 1995.036
> cache size : 4096 KB
> physical id : 3
> siblings : 1
> core id : 0
> cpu cores : 1
> fpu : yes
> fpu_exception : yes
> cpuid level : 10
> wp : yes
> flags : fpu tsc msr pae cx8 apic mtrr cmov pat clflush
> acpi mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc pni vmx
> ssse3 cx16 lahf_lm
> bogomips : 4989.65
> clflush size : 64
> cache_alignment : 64
> address sizes : 36 bits physical, 48 bits virtual
> power management:
> OS: CentOS 64bit
> Postgres: 9.0.4 compiled
>
> processor : 7
> vendor_id : GenuineIntel
> cpu family : 6
> model : 30
> model name : Intel(R) Xeon(R) CPU X3450 @ 2.67GHz
> stepping : 5
> cpu MHz : 2660.099
> cache size : 8192 KB
> physical id : 0
> siblings : 8
> core id : 3
> cpu cores : 4
> apicid : 7
> fdiv_bug : no
> hlt_bug : no
> f00f_bug : no
> coma_bug : no
> fpu : yes
> fpu_exception : yes
> cpuid level : 11
> wp : yes
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr
> pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm
> pbe nx rdtscp lm constant_tsc ida nonstop_tsc pni monitor ds_cpl vmx
> smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm [8]
> bogomips : 5319.92
> OS: CentOS 32bit
> Postgres: 9.0.4 compiled
>
>
> In my testing I have a 32bit CentOS on the x3450, but a 64bit CentOS
> on the E5335. Can this make such a bit difference or should the
> perform fairly close to the same speed? Both servers have 8GB of
> RAM, and the database I tested with is only 3.7GB.
>
> I'm a bit surprised as the x3450 has DDR3, while the E5335 has DDR2,
> and of course because of the cycle speed difference alone I would
> think the X3450 should beat the E5335.
>
Yes, you have basically shown that running two different tests give
different results -- or that an apple is not an orange. You need to
only vary 1 variable at a time for it to mean anything.
Regards,
Ken
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-18 21:56 Tomas Vondra <tv@fuzzy.cz>
parent: ktm@rice.edu <ktm@rice.edu>
0 siblings, 2 replies; 9+ messages in thread
From: Tomas Vondra @ 2011-07-18 21:56 UTC (permalink / raw)
To: pgsql-performance
Dne 18.7.2011 22:11, ktm@rice.edu napsal(a):
>> > In my testing I have a 32bit CentOS on the x3450, but a 64bit CentOS
>> > on the E5335. Can this make such a bit difference or should the
>> > perform fairly close to the same speed? Both servers have 8GB of
>> > RAM, and the database I tested with is only 3.7GB.
>> >
>> > I'm a bit surprised as the x3450 has DDR3, while the E5335 has DDR2,
>> > and of course because of the cycle speed difference alone I would
>> > think the X3450 should beat the E5335.
>> >
> Yes, you have basically shown that running two different tests give
> different results -- or that an apple is not an orange. You need to
> only vary 1 variable at a time for it to mean anything.
He just run the same test on two different machines - I'm not sure
what's wrong with it? Sure, it would be nice to compare 32bit to 32bit,
but the OP probably can't do that and wonders if this is the cause. Why
is that comparing apples and oranges?
According to http://www.cpubenchmark.net, the X3450 is about 2x as fast
as E5335 (5,298 vs. 2,575), although this is just a synthetic score.
I'm a bit confused by the E5335 cpuinfo output, because it says "cpu
cores : 1" as I'd expect "4" here.
I do recall hyperthreading generally was not recommended for a DB, not
sure if that changed recently. A quick search revealed this post
http://serverfault.com/questions/219791/hyperthreading-vs-sql-server-postgresql
stating that since Nehalem CPUs (and X3450 is Nehalem) this should not
be a problem anymore. Not sure if it's true, I guess it's worth testing
as it might slow down the X3450 box.
OP: We need more details about the test's has run, without them we're
just guessing. Have you collected some system stats (vmstat, iostat)
during the test?
Tomas
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-18 22:03 Josh Berkus <josh@agliodbs.com>
parent: M. D. <lists@turnkey.bz>
2 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2011-07-18 22:03 UTC (permalink / raw)
To: pgsql-performance
On 7/18/11 12:48 PM, M. D. wrote:
> I have 2 small servers, one a fairly new server with a x3450 (4-core
> with HT) cpu running at 2.67GHz and an older E5335 (4-core) cpu running
> at 2GHz.
>
> I have been quite surprised how the E5335 compares very closely to the
> x3450, but maybe I have tested it wrongly.
What test? What were the results?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-18 23:19 ktm@rice.edu <ktm@rice.edu>
parent: Tomas Vondra <tv@fuzzy.cz>
1 sibling, 0 replies; 9+ messages in thread
From: ktm@rice.edu @ 2011-07-18 23:19 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
On Mon, Jul 18, 2011 at 11:56:40PM +0200, Tomas Vondra wrote:
> Dne 18.7.2011 22:11, ktm@rice.edu napsal(a):
> >> > In my testing I have a 32bit CentOS on the x3450, but a 64bit CentOS
> >> > on the E5335. Can this make such a bit difference or should the
> >> > perform fairly close to the same speed? Both servers have 8GB of
> >> > RAM, and the database I tested with is only 3.7GB.
> >> >
> >> > I'm a bit surprised as the x3450 has DDR3, while the E5335 has DDR2,
> >> > and of course because of the cycle speed difference alone I would
> >> > think the X3450 should beat the E5335.
> >> >
> > Yes, you have basically shown that running two different tests give
> > different results -- or that an apple is not an orange. You need to
> > only vary 1 variable at a time for it to mean anything.
>
> He just run the same test on two different machines - I'm not sure
> what's wrong with it? Sure, it would be nice to compare 32bit to 32bit,
> but the OP probably can't do that and wonders if this is the cause. Why
> is that comparing apples and oranges?
>
It is only that 32 vs. 64 bit, compiler and other things can easily make
a factor of 2 change in the results. So it is not telling you much about
the processor differences, neccessarily.
Regards,
Ken
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-19 00:47 Greg Smith <greg@2ndquadrant.com>
parent: M. D. <lists@turnkey.bz>
2 siblings, 1 reply; 9+ messages in thread
From: Greg Smith @ 2011-07-19 00:47 UTC (permalink / raw)
To: M. D. <lists@turnkey.bz>; +Cc: pgsql-performance
M. D. wrote:
> I'm a bit surprised as the x3450 has DDR3, while the E5335 has DDR2,
> and of course because of the cycle speed difference alone I would
> think the X3450 should beat the E5335.
Try comparing them with stream-scaling to see what happens:
https://github.com/gregs1104/stream-scaling
You can't really test CPU performance in a simple way anymore; it varies
depending on the number of processes running at once. This test is the
best way I've found to show how that works. On a single thread, the
X3450 may not be significantly better than the E5535. But what should
happen is that total speed keeps going up as you add more threads on the
newer system, while the old DDR2 model stays as the same basic total.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-19 01:38 Scott Marlowe <scott.marlowe@gmail.com>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2011-07-19 01:38 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: M. D. <lists@turnkey.bz>; pgsql-performance
On Mon, Jul 18, 2011 at 6:47 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> M. D. wrote:
>>
>> I'm a bit surprised as the x3450 has DDR3, while the E5335 has DDR2, and
>> of course because of the cycle speed difference alone I would think the
>> X3450 should beat the E5335.
>
> Try comparing them with stream-scaling to see what happens:
>
> https://github.com/gregs1104/stream-scaling
>
> You can't really test CPU performance in a simple way anymore; it varies
> depending on the number of processes running at once. This test is the best
> way I've found to show how that works. On a single thread, the X3450 may
> not be significantly better than the E5535. But what should happen is that
> total speed keeps going up as you add more threads on the newer system,
> while the old DDR2 model stays as the same basic total.
By way of example we have a server with dual 6 core opterons that runs
on 667MHz memory and it maxes out the stream test with 8 threads,
getting no faster as you add threads. OTOH, our 4x12 core opteron
machines with 1333MHz memory and like 8 different channels to it,
scales right up to 40 or more threads running the stream test.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-19 03:25 M. D. <lists@turnkey.bz>
parent: Tomas Vondra <tv@fuzzy.cz>
1 sibling, 1 reply; 9+ messages in thread
From: M. D. @ 2011-07-19 03:25 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
On 07/18/2011 03:56 PM, Tomas Vondra wrote:
> Dne 18.7.2011 22:11,ktm@rice.edu napsal(a):
>>>> In my testing I have a 32bit CentOS on the x3450, but a 64bit CentOS
>>>> on the E5335. Can this make such a bit difference or should the
>>>> perform fairly close to the same speed? Both servers have 8GB of
>>>> RAM, and the database I tested with is only 3.7GB.
>>>>
>>>> I'm a bit surprised as the x3450 has DDR3, while the E5335 has DDR2,
>>>> and of course because of the cycle speed difference alone I would
>>>> think the X3450 should beat the E5335.
>>>>
>> Yes, you have basically shown that running two different tests give
>> different results -- or that an apple is not an orange. You need to
>> only vary 1 variable at a time for it to mean anything.
> He just run the same test on two different machines - I'm not sure
> what's wrong with it? Sure, it would be nice to compare 32bit to 32bit,
> but the OP probably can't do that and wonders if this is the cause. Why
> is that comparing apples and oranges?
>
> According tohttp://www.cpubenchmark.net, the X3450 is about 2x as fast
> as E5335 (5,298 vs. 2,575), although this is just a synthetic score.
>
> I'm a bit confused by the E5335 cpuinfo output, because it says "cpu
> cores : 1" as I'd expect "4" here.
>
> I do recall hyperthreading generally was not recommended for a DB, not
> sure if that changed recently. A quick search revealed this post
>
> http://serverfault.com/questions/219791/hyperthreading-vs-sql-server-postgresql
>
> stating that since Nehalem CPUs (and X3450 is Nehalem) this should not
> be a problem anymore. Not sure if it's true, I guess it's worth testing
> as it might slow down the X3450 box.
>
> OP: We need more details about the test's has run, without them we're
> just guessing. Have you collected some system stats (vmstat, iostat)
> during the test?
>
> Tomas
>
Thank you. That was exactly my reason for posting.
I did some more serious testing, and it seems like what I was testing
with did not give my proper results at all, or maybe because I had not
tweaked the config file.
After more testing, I'm seeing the x3450 more than 2x faster as the
E5335. This is just a simple test, but it's something that is run on a
continuous basis in this application so that's what I wanted to test
with. Table item_change has around 2M rows.
If someone would, please, can you tell me if it would help me to
partition the item_change table (it has a date column)? As far as I've
seen, an application needs to change if a table is partitioned, right?
Here's the query I ran:
explain analyse select item.item_id,item_plu.number,item.description,
(select dept.name from dept where dept.dept_id = item.dept_id),
(select subdept.name from subdept where subdept.subdept_id =
item.subdept_id),
(select sum(on_hand) from item_change where item_change.item_id =
item.item_id),
(select sum(on_order) from item_change where item_change.item_id =
item.item_id),
(select sum(total_cost) from item_change where item_change.item_id =
item.item_id),
(select price from item_price where item_price.item_id = item.item_id
and item_price.zone_id = 'OUe1zXgADRnWemS1grOerQ' and
item_price.price_type = 0
and item_price.size_name = item.sell_size)
from item
join item_plu on item.item_id = item_plu.item_id and item_plu.seq_num = 0
where item.inactive_on is null;
E5335
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.27..56795323.05 rows=79821 width=95) (actual
time=0.270..35769.722 rows=72273 loops=1)
Merge Cond: (item.item_id = item_plu.item_id)
-> Index Scan using item_pkey on item (cost=0.00..9599.57
rows=72249 width=86) (actual time=0.011..216.709 rows=72273 loops=1)
Filter: (inactive_on IS NULL)
-> Index Scan using item_plu_pkey on item_plu (cost=0.00..5551.89
rows=79821 width=32) (actual time=0.013..226.435 rows=80114 loops=1)
Index Cond: (item_plu.seq_num = 0)
SubPlan 1
-> Seq Scan on dept (cost=0.00..5.16 rows=1 width=8) (actual
time=0.003..0.007 rows=1 loops=72273)
Filter: (dept_id = $0)
SubPlan 2
-> Index Scan using subdept_pkey on subdept (cost=0.00..5.27
rows=1 width=8) (actual time=0.009..0.011 rows=1 loops=72273)
Index Cond: (subdept_id = $1)
SubPlan 3
-> Aggregate (cost=231.86..231.87 rows=1 width=6) (actual
time=0.152..0.153 rows=1 loops=72273)
-> Index Scan using item_change_i2 on item_change
(cost=0.00..231.63 rows=91 width=6) (actual time=0.021..0.094 rows=28
loops=72273)
Index Cond: (item_id = $2)
SubPlan 4
-> Aggregate (cost=231.86..231.87 rows=1 width=5) (actual
time=0.132..0.133 rows=1 loops=72273)
-> Index Scan using item_change_i2 on item_change
(cost=0.00..231.63 rows=91 width=5) (actual time=0.021..0.076 rows=28
loops=72273)
Index Cond: (item_id = $2)
SubPlan 5
-> Aggregate (cost=231.86..231.87 rows=1 width=8) (actual
time=0.133..0.134 rows=1 loops=72273)
-> Index Scan using item_change_i2 on item_change
(cost=0.00..231.63 rows=91 width=8) (actual time=0.021..0.075 rows=28
loops=72273)
Index Cond: (item_id = $2)
SubPlan 6
-> Index Scan using item_price_i3 on item_price (cost=0.00..5.29
rows=1 width=7) (actual time=0.015..0.017 rows=1 loops=72273)
Index Cond: (item_id = $2)
Filter: ((zone_id = 'OUe1zXgADRnWemS1grOerQ'::bpchar) AND
(price_type = 0) AND ((size_name)::text = ($3)::text))
Total runtime: 35871.253 ms
(29 rows)
X3450
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.15..57610807.07 rows=80066 width=95) (actual
time=0.141..14680.486 rows=72247 loops=1)
Merge Cond: (item.item_id = item_plu.item_id)
-> Index Scan using item_pkey on item (cost=0.00..10446.59
rows=72181 width=86) (actual time=0.005..79.796 rows=72247 loops=1)
Filter: (inactive_on IS NULL)
-> Index Scan using item_plu_pkey on item_plu (cost=0.00..5456.43
rows=80066 width=32) (actual time=0.012..75.303 rows=80085 loops=1)
Index Cond: (item_plu.seq_num = 0)
SubPlan 1
-> Seq Scan on dept (cost=0.00..5.16 rows=1 width=8) (actual
time=0.001..0.003 rows=1 loops=72247)
Filter: (dept_id = $0)
SubPlan 2
-> Index Scan using subdept_pkey on subdept (cost=0.00..5.27
rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=72247)
Index Cond: (subdept_id = $1)
SubPlan 3
-> Aggregate (cost=234.53..234.54 rows=1 width=6) (actual
time=0.060..0.060 rows=1 loops=72247)
-> Index Scan using item_change_i2 on item_change
(cost=0.00..234.29 rows=92 width=6) (actual time=0.018..0.041 rows=28
loops=72247)
Index Cond: (item_id = $2)
SubPlan 4
-> Aggregate (cost=234.53..234.54 rows=1 width=5) (actual
time=0.053..0.053 rows=1 loops=72247)
-> Index Scan using item_change_i2 on item_change
(cost=0.00..234.29 rows=92 width=5) (actual time=0.018..0.034 rows=28
loops=72247)
Index Cond: (item_id = $2)
SubPlan 5
-> Aggregate (cost=234.53..234.54 rows=1 width=8) (actual
time=0.053..0.053 rows=1 loops=72247)
-> Index Scan using item_change_i2 on item_change
(cost=0.00..234.29 rows=92 width=8) (actual time=0.018..0.034 rows=28
loops=72247)
Index Cond: (item_id = $2)
SubPlan 6
-> Index Scan using item_price_i3 on item_price (cost=0.00..5.29
rows=1 width=7) (actual time=0.012..0.013 rows=1 loops=72247)
Index Cond: (item_id = $2)
Filter: ((zone_id = 'OUe1zXgADRnWemS1grOerQ'::bpchar) AND
(price_type = 0) AND ((size_name)::text = ($3)::text))
Total runtime: 14695.559 ms
(29 rows)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: cpu comparison
@ 2011-07-19 05:05 Samuel Gendler <sgendler@ideasculptor.com>
parent: M. D. <lists@turnkey.bz>
0 siblings, 0 replies; 9+ messages in thread
From: Samuel Gendler @ 2011-07-19 05:05 UTC (permalink / raw)
To: M. D. <lists@turnkey.bz>; +Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
I'm just top posting this because this whole thread needs a reset before it
goes any farther.
Start with a real description of these hosts - Number and types of disks,
filesystem configs, processors, memory, OS, etc. If your db is small enough
to fit into RAM, please show us the db config you are using which ensures
that you are making best use of available RAM, etc.
Then we need to know what your test looks like - showing us a query and an
explain plan without any info about the table structure, indexes, number of
rows, and table usage patterns doesn't provide anywhere near enough info to
diagnose inefficiency.
There are several documents linked right from the page for this mailing list
that describe exactly how to go about providing enough info to get help from
the list. Please read through them, then update us with the necessary
information, and I'm sure we'll be able to offer you some insight into what
is going on.
And for the record, your app probably doesn't need to change to use table
partitioning, at least when selecting data. Depending upon how data is
loaded, you may need to change how you do inserts. But it is impossible to
comment on whether partitioning might help you without knowing table
structure, value distributions, query patterns, and number of rows in the
table. If you are always selecting over the whole range of data,
partitioning isn't likely to buy you anything, for example.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-07-19 05:05 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-18 19:48 cpu comparison M. D. <lists@turnkey.bz>
2011-07-18 20:11 ` ktm@rice.edu <ktm@rice.edu>
2011-07-18 21:56 ` Tomas Vondra <tv@fuzzy.cz>
2011-07-18 23:19 ` ktm@rice.edu <ktm@rice.edu>
2011-07-19 03:25 ` M. D. <lists@turnkey.bz>
2011-07-19 05:05 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-07-18 22:03 ` Josh Berkus <josh@agliodbs.com>
2011-07-19 00:47 ` Greg Smith <greg@2ndquadrant.com>
2011-07-19 01:38 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox