Обсуждение: old server, new server, same performance

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

Piotr Legiecki <piotrlg@ams.edu.pl> wrote:

> Why there is no difference in database speed between those two
> machines?

Could you post the contents of the postgresql.conf files for both
(stripped of comments) and explain what you're using for your
benchmarks?  In particular, it would be interesting to know how many
concurrent connections are active running what mix of queries.

-Kevin

2010/5/14 Piotr Legiecki <piotrlg@ams.edu.pl>:
> Hi
> 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 (???)

It could well be you're measuring the time it takes to trasnfer that
data from server to client.

How fast is select count(*) from table on each machine?

Kevin Grittner wrote:
> Piotr Legiecki <piotrlg@ams.edu.pl> wrote:
>
>
>> Why there is no difference in database speed between those two
>> machines?
>>
>
> Could you post the contents of the postgresql.conf files for both
> (stripped of comments) and explain what you're using for your
> benchmarks?  In particular, it would be interesting to know how many
> concurrent connections are active running what mix of queries.
>
It would be also interesting to know how many disks are there in the new
server, and the size of the database (select
pg_size_pretty(pg_database_size('yourdb'))).

regards,
Yeb Havinga

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

Agree with Thom,

I also had the same problem back then when I was migrate from old servers to new server.

After I vacuum the DB at the new servers the result back to normal.

Rgrds

Sent from my BlackBerry®powered by AyahNaima

---

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

Hello all,
I was testing how much time a pg_dump backup would take to get restored. Initially, I tried it with psql (on a backup taken with pg_dumpall). It took me about one hour. I felt that I should target for a recovery time of 15 minutes to half an hour. So I went through the blogs/documentation etc and switched to pg_dump and pg_restore. I tested only the database with the maximum volume of data (about 1.5 GB). With
pg_restore -U postgres -v -d PROFICIENT --clean -Fc proficient.dmp
it took about 45 minutes. I tried it with
pg_restore -U postgres -j8 -v -d PROFICIENT --clean -Fc proficient.dmp
Not much improvement there either. Have I missed something or 1.5 GB data on a machine with the following configuration will take about 45 minutes? There is nothing else running on the machine consuming memory or CPU. Out of 300 odd tables, about 10 tables have millions of records, rest are all having a few thousand records at most.

Here are the specs  ( a pc class  machine)-

PostgreSQL 8.4.3 on i686-pc-linux-gnu
CentOS release 5.2
Intel(R) Pentium(R) D CPU 2.80GHz
2 GB RAM
Storage is local disk.

Postgresql parameters (what I felt are relevant) -
max_connections = 100
shared_buffers = 64MB
work_mem = 16MB
maintenance_work_mem = 16MB
synchronous_commit on


Thank you for any suggestions.
Jayadevan





DISCLAIMER:

"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."

On Mon, May 17, 2010 at 1:04 AM, Jayadevan M
<Jayadevan.Maymala@ibsplc.com> wrote:
> Hello all,
> I was testing how much time a pg_dump backup would take to get restored.
> Initially, I tried it with psql (on a backup taken with pg_dumpall). It took
> me about one hour. I felt that I should target for a recovery time of 15
> minutes to half an hour. So I went through the blogs/documentation etc and
> switched to pg_dump and pg_restore. I tested only the database with the
> maximum volume of data (about 1.5 GB). With
> pg_restore -U postgres -v -d PROFICIENT --clean -Fc proficient.dmp
> it took about 45 minutes. I tried it with
> pg_restore -U postgres -j8 -v -d PROFICIENT --clean -Fc proficient.dmp
> Not much improvement there either. Have I missed something or 1.5 GB data on
> a machine with the following configuration will take about 45 minutes? There
> is nothing else running on the machine consuming memory or CPU. Out of 300
> odd tables, about 10 tables have millions of records, rest are all having a
> few thousand records at most.
>
> Here are the specs  ( a pc class  machine)-
>
> PostgreSQL 8.4.3 on i686-pc-linux-gnu
> CentOS release 5.2
> Intel(R) Pentium(R) D CPU 2.80GHz
> 2 GB RAM
> Storage is local disk.
>
> Postgresql parameters (what I felt are relevant) -
> max_connections = 100
> shared_buffers = 64MB
> work_mem = 16MB
> maintenance_work_mem = 16MB
> synchronous_commit on

I would suggest raising shared_buffers to perhaps 512MB and cranking
up checkpoint_segments to 10 or more.  Also, your email doesn't give
too much information about how many CPUs you have and what kind of
disk subsystem you are using (RAID?  how many disks?) so it's had to
say if -j8 is reasonable.  That might be too high.

Another thing I would recommend is that during the restore you use
tools like top and iostat to monitor the system.  You'll want to check
things like whether all the CPUs are in use, and how the disk activity
compares to the maximum you can generate using some other method
(perhaps dd).

One thing I've noticed (to my chagrin) is that if pg_restore is given
a set of options that are incompatible with parallel restore, it just
does a single-threaded restore.  The options you've specified look
right to me, but, again, examining exactly what is going on during the
restore should tell you if there's a problem in this area.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Mon, May 17, 2010 at 12:04 AM, Jayadevan M
<Jayadevan.Maymala@ibsplc.com> wrote:
> Hello all,
> I was testing how much time a pg_dump backup would take to get restored.
> Initially, I tried it with psql (on a backup taken with pg_dumpall). It took
> me about one hour. I felt that I should target for a recovery time of 15
> minutes to half an hour. So I went through the blogs/documentation etc and
> switched to pg_dump and pg_restore. I tested only the database with the
> maximum volume of data (about 1.5 GB). With
> pg_restore -U postgres -v -d PROFICIENT --clean -Fc proficient.dmp
> it took about 45 minutes. I tried it with
> pg_restore -U postgres -j8 -v -d PROFICIENT --clean -Fc proficient.dmp
> Not much improvement there either. Have I missed something or 1.5 GB data on
> a machine with the following configuration will take about 45 minutes? There
> is nothing else running on the machine consuming memory or CPU. Out of 300
> odd tables, about 10 tables have millions of records, rest are all having a
> few thousand records at most.
>
> Here are the specs  ( a pc class  machine)-
>
> PostgreSQL 8.4.3 on i686-pc-linux-gnu
> CentOS release 5.2
> Intel(R) Pentium(R) D CPU 2.80GHz
> 2 GB RAM
> Storage is local disk.
>
> Postgresql parameters (what I felt are relevant) -
> max_connections = 100
> shared_buffers = 64MB
> work_mem = 16MB
> maintenance_work_mem = 16MB
> synchronous_commit on

Do the big tables have lots of indexes? If so, you should raise
maintenance_work_mem.

Peter

I increased shared_buffers and maintenance_work_memto
128MB and 64MB and the restore was over in about 20 minutes. Anyway, I am
learning about PostgreSQL and it is not a critical situation. Thanks for
all the replies.
Regards,
Jayadevan




From:   Robert Haas <robertmhaas@gmail.com>
To:     Jayadevan M <Jayadevan.Maymala@ibsplc.com>
Cc:     pgsql-performance@postgresql.org
Date:   22/05/2010 16:59
Subject:        Re: [PERFORM] pg_dump and pg_restore



On Mon, May 17, 2010 at 1:04 AM, Jayadevan M
<Jayadevan.Maymala@ibsplc.com> wrote:
> Hello all,
> I was testing how much time a pg_dump backup would take to get restored.
> Initially, I tried it with psql (on a backup taken with pg_dumpall). It
took
> me about one hour. I felt that I should target for a recovery time of 15
> minutes to half an hour. So I went through the blogs/documentation etc
and
> switched to pg_dump and pg_restore. I tested only the database with the
> maximum volume of data (about 1.5 GB). With
> pg_restore -U postgres -v -d PROFICIENT --clean -Fc proficient.dmp
> it took about 45 minutes. I tried it with
> pg_restore -U postgres -j8 -v -d PROFICIENT --clean -Fc proficient.dmp
> Not much improvement there either. Have I missed something or 1.5 GB
data on
> a machine with the following configuration will take about 45 minutes?
There
> is nothing else running on the machine consuming memory or CPU. Out of
300
> odd tables, about 10 tables have millions of records, rest are all
having a
> few thousand records at most.
>
> Here are the specs  ( a pc class  machine)-
>
> PostgreSQL 8.4.3 on i686-pc-linux-gnu
> CentOS release 5.2
> Intel(R) Pentium(R) D CPU 2.80GHz
> 2 GB RAM
> Storage is local disk.
>
> Postgresql parameters (what I felt are relevant) -
> max_connections = 100
> shared_buffers = 64MB
> work_mem = 16MB
> maintenance_work_mem = 16MB
> synchronous_commit on

I would suggest raising shared_buffers to perhaps 512MB and cranking
up checkpoint_segments to 10 or more.  Also, your email doesn't give
too much information about how many CPUs you have and what kind of
disk subsystem you are using (RAID?  how many disks?) so it's had to
say if -j8 is reasonable.  That might be too high.

Another thing I would recommend is that during the restore you use
tools like top and iostat to monitor the system.  You'll want to check
things like whether all the CPUs are in use, and how the disk activity
compares to the maximum you can generate using some other method
(perhaps dd).

One thing I've noticed (to my chagrin) is that if pg_restore is given
a set of options that are incompatible with parallel restore, it just
does a single-threaded restore.  The options you've specified look
right to me, but, again, examining exactly what is going on during the
restore should tell you if there's a problem in this area.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company







DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."