2010/5/14 Piotr Legiecki <piotrlg@xxxxxxxxxx>
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-on A:
-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
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
Have you compared the PostgreSQL configurations between servers? (postgresql.conf) And how was it installed? Package or compiled from scratch?
And has the new DB been VACUUM'd?
Thom
