Message-ID: <4BED08B4.4030507@sci.pam.szczecin.pl>
Date: Fri, 14 May 2010 10:24:20 +0200
From: Piotr Legiecki <piotrlg@sci.pam.szczecin.pl>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: old server, new server, same performance
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

Hi

I have a situation at my work which I simply don't understand and hope 
that here I can find some explanations.

What is on the scene:
A - old 'server' PC AMD Athlon64 3000+, 2GB RAM, 1 ATA HDD 150GB, Debian 
etch, postgresql 8.1.19
B - new server HP DL 360, 12GB RAM, Intel Xeon 8 cores CPU, fast SAS 
(mirrored) HDDs, Debian 64 bit, lenny, backported postgresql 8.1.19
C - our Windows application based on Postgresql 8.1 (not newer)

and second role actors (for pgAdmin)
D - my old Windows XP computer, Athlon64 X2 3800+, with 100Mbit ethernet
E - new laptop with Ubuntu, 1000Mbit ethernet

The goal: migrate postgresql from A to B.

Simple and works fine (using pg_dump, psql -d dbname <bakcup_file).

So what is the problem? My simple 'benchmarks' I have done with pgAdmin 
in spare time.

pgAdmin is the latest 1.8.2 on both D and E.
Using pgAdmin on my (D) computer I have run SELECT * from some_table; 
and noted the execution time on both A and B servers:
- on A (the old one) about 120sec
- on B (the new monster) about 120sec (???)

(yes, there is almost no difference)

On the first test runs the postgresql configs on both servers were the 
same, so I have started to optimize (according to postgresql wiki) the 
postgresql on the new (B) server. The difference  with my simple select 
* were close to 0.

So this is my first question. Why postgresql behaves so strangely?
Why there is no difference in database speed between those two machines?

I thought about hardware problem on B, but:
hdparm shows 140MB/sec on B and 60MB on A (and buffered reads are 8GB on 
B and 800MB on A)
bonnie++ on B:
> Version 1.03d       ------Sequential Output------ --Sequential Input- --Random-
>                     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
> Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
> malwa           24G 51269  71 49649  10 34974   6 48969  82 147840  13  1150   1
on A:
> Version  1.03       ------Sequential Output------ --Sequential Input- --Random-
>                     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
> Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
> irys             4G 42961  93 41125  13 14414   3 20262  48 38487   5 167.0   0

Here the difference in writings is not so big (wonder why, the price 
between those machines is huge) but in readings are noticeably better on B.

Ok, those were the tests done using my old Windows PC (D) computer. So I 
have decided to do the same using my new laptop with Ubuntu (E).
The results were soooo strange that now I am completely confused.

The same SELECT:
- on A first (fresh) run 30sec, second (and so on) about 11sec (??!)
- on B first run 80sec, second (and so on) about 80sec also

What is going on here? About 8x faster on slower machine?

One more thing comes to my mind. The A server has iso-8859-2 locale and 
database is set to latin2, the B server has utf8 locale, but database is 
still latin2. Does it matter anyway?

So here I'm stuck and hope for help. Is there any bottleneck? How to 
find it?

Regards
Piotr