Re: cpu comparison - Mailing list 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)