On 07/18/2011 03:56 PM, Tomas Vondra wrote:
Dne 18.7.2011 22:11,ktm@xxxxxxxx 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)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
