Message-ID: <48EE98FE.60100@mochima.com>
Date: Thu, 09 Oct 2008 19:51:26 -0400
From: Carlos Moreno <morenopg@mochima.com>
User-Agent: Thunderbird 2.0.0.17 (X11/20080925)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: "Mysterious" issues with newly installed 8.3
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit


Ok, I know that such an open and vague question like this one
is...  well, open and vague...  But still.

The short story:

Just finished an 8.3.4 installation on a new machine, to replace
an existing one;  the new machine is superior (i.e., higher
performance) in virtually every way --- twice as much memory,
faster processor, faster drives, etc.

I made an exact copy of the existing database on the new
machine, and the exact same queries run on both reveal that
the old machine beats the new one by a factor of close to 2 !!!!
(i.e., the same queries run close to twice as fast on the old
machine!!!)

To make things worse:  the old machine is in operation, under
normal workload  (and right now the system may be around
peak time), and the new machine is there sitting doing nothing;
just one user logged in using psql to run the queries --- *no-one
and nothing* is connecting to the new server.

So... What's going on???


The details:

CPU:
New: Opteron DC 1218HE  (1MB cache per core) @2.6GHz
Old:  Athlon64 X2  (512K cache per core)  @2.2GHz

RAM:
New:  4GB
Old:   2GB

HD:
Doesn't matter the capacity, but I have every reason to believe
the new one is faster --- hdparm reports 105MB/sec transfer
rate;  the measurement for the old server is meaningless, since
it is in operation  (i.e., there is actual database activity), so it
measures between 50MB/sec and 70MB/sec.  Given its age, I
would estimate 70 to 80 MB/sec

OS:
New:  CentOS 5.2  (gcc 4.1.2)
Old:  FC6  (gcc 4.1.2)

PG:
New:  8.3.4 installed from source
Old:   8.2.4 installed from source

Presumably relevant configuration parameters --- shared_buffers
was set to 250MB on the old one;  I set it to 500MB on the new
one  (kinda makes sense, no?  1/8 of the physical memory in both
cases).

I set max_fsm_pages a little bit higher on the new one (409600
instead of 307200 on the old one).  The rest is pretty much
identical  (except for the autovacuum --- I left the defaults in the
new one)


The old machine is vacuum-analyzed once a day  (around 4AM);
on the new one, I ran a vacuumdb -z -f after populating it.


Some interesting outputs:

explain analyze select count(*) from users;
New:
 Aggregate  (cost=8507.11..8507.12 rows=1 width=0) (actual
time=867.582..867.584 rows=1 loops=1)
   ->  Seq Scan on users  (cost=0.00..7964.49 rows=217049 width=0)
(actual time=0.016..450.560 rows=217049 loops=1)
 Total runtime: 867.744 ms

Old:
 Aggregate  (cost=17171.22..17171.22 rows=1 width=0) (actual
time=559.475..559.476 rows=1 loops=1)
   ->  Seq Scan on users  (cost=0.00..16628.57 rows=217057 width=0)
(actual time=0.009..303.026 rows=217107 loops=1)
 Total runtime: 559.536 ms

Running the same command again several times practically
does not change anything.


explain analyze select count(*) from users where username like 'A%';
New:
 Aggregate  (cost=6361.28..6361.29 rows=1 width=0) (actual
time=87.528..87.530 rows=1 loops=1)
   ->  Bitmap Heap Scan on users  (cost=351.63..6325.33 rows=14376
width=0) (actual time=6.444..53.426 rows=17739 loops=1)
         Filter: ((username)::text ~~ 'a%'::text)
         ->  Bitmap Index Scan on c_username_unique  (cost=0.00..348.04
rows=14376 width=0) (actual time=5.383..5.383 rows=17739 loops=1)
               Index Cond: (((username)::text >= 'a'::text) AND
((username)::text < 'b'::text))
 Total runtime: 87.638 ms

Old:
 Aggregate  (cost=13188.91..13188.92 rows=1 width=0) (actual
time=61.743..61.745 rows=1 loops=1)
   ->  Bitmap Heap Scan on users  (cost=392.07..13157.75 rows=12466
width=0) (actual time=7.433..40.847 rows=17747 loops=1)
         Filter: ((username)::text ~~ 'a%'::text)
         ->  Bitmap Index Scan on c_username_unique  (cost=0.00..388.96
rows=12466 width=0) (actual time=5.652..5.652 rows=17861 loops=1)
               Index Cond: (((username)::text >= 'a'::character varying)
AND ((username)::text < 'b'::character varying))
 Total runtime: 61.824 ms


Any ideas?