Message-ID: <4E24F925.8080404@turnkey.bz>
Date: Mon, 18 Jul 2011 21:25:25 -0600
From: "M. D." <lists@turnkey.bz>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Tomas Vondra <tv@fuzzy.cz>, pgsql-performance@postgresql.org
Subject: Re: cpu comparison
References: <4E248E04.2090506@turnkey.bz>
	<20110718201132.GI19928@staff-mud-56-27.rice.edu>
	<4E24AC18.807@fuzzy.cz>
In-Reply-To: <4E24AC18.807@fuzzy.cz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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)