2010/5/14 Piotr Legiecki <piot...@ams.edu.pl> > 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 >
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
