pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 8.2.5 slow performance on INSERT on Linux
4+ messages / 3 participants
[nested] [flat]
* PostgreSQL 8.2.5 slow performance on INSERT on Linux
@ 2007-12-02 21:34 Beyers Cronje <bcronje@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Beyers Cronje @ 2007-12-02 21:34 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I'm busy evaluating PostgreSQL and I'm having performance problems on one of
my servers. I have a very simple one table database, and the client using
Mono 1.2.5.1 is running a loop doing INSERTs on the table. Initially I
tested this on my development PC, an old P4 system with 2GB RAM and 10,000
INSERTs took ~12 secs on average, which I was fairly satisfied with. I then
moved everything over to our test server, a new Dell 1950 server with quad
core Xeon processors, 4GB RAM and SCSI hdd expecting to see better
performance, but instead performance dropped to ~44 secs for 10,000 INSERTs.
This obviously is not acceptable. Both the PC and server are running the
exact same PostgreSQL version, Mono version, client application and both
tests were run under very low load and on an empty table. I noticed that CPU
utilization on the Dell server is very low, 1-2% utilization, so obviously
it's not a load problem. Only the test application is accessing the
database.
So my question is, can anyone please give me some tips on what commands or
tools I can use to try and pin down where exactly the performance drop is
coming from? I'm obviously new to PostgreSQL so even basic checks can be
relevant.
Kind regards
Beyers Cronje
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL 8.2.5 slow performance on INSERT on Linux
@ 2007-12-02 21:46 Pavel Stehule <pavel.stehule@gmail.com>
parent: Beyers Cronje <bcronje@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2007-12-02 21:46 UTC (permalink / raw)
To: Beyers Cronje <bcronje@gmail.com>; +Cc: pgsql-performance
On 02/12/2007, Beyers Cronje <bcronje@gmail.com> wrote:
> Hi all,
>
> I'm busy evaluating PostgreSQL and I'm having performance problems on one of
> my servers. I have a very simple one table database, and the client using
> Mono 1.2.5.1 is running a loop doing INSERTs on the table. Initially I
> tested this on my development PC, an old P4 system with 2GB RAM and 10,000
> INSERTs took ~12 secs on average, which I was fairly satisfied with. I then
> moved everything over to our test server, a new Dell 1950 server with quad
> core Xeon processors, 4GB RAM and SCSI hdd expecting to see better
> performance, but instead performance dropped to ~44 secs for 10,000 INSERTs.
> This obviously is not acceptable. Both the PC and server are running the
> exact same PostgreSQL version, Mono version, client application and both
> tests were run under very low load and on an empty table. I noticed that CPU
> utilization on the Dell server is very low, 1-2% utilization, so obviously
> it's not a load problem. Only the test application is accessing the
> database.
>
> So my question is, can anyone please give me some tips on what commands or
> tools I can use to try and pin down where exactly the performance drop is
> coming from? I'm obviously new to PostgreSQL so even basic checks can be
> relevant.
>
> Kind regards
>
> Beyers Cronje
>
Hello
a) use COPY instead INSERT (it's much faster) if it is possible
b) check your configuration and read this article
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
Regards
Pavel Stehule
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL 8.2.5 slow performance on INSERT on Linux
@ 2007-12-02 22:53 Greg Smith <gsmith@gregsmith.com>
parent: Beyers Cronje <bcronje@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Greg Smith @ 2007-12-02 22:53 UTC (permalink / raw)
To: Beyers Cronje <bcronje@gmail.com>; +Cc: pgsql-performance
On Sun, 2 Dec 2007, Beyers Cronje wrote:
> Initially I tested this on my development PC, an old P4 system with 2GB
> RAM and 10,000 INSERTs took ~12 secs on average, which I was fairly
> satisfied with. I then moved everything over to our test server, a new
> Dell 1950 server with quad core Xeon processors, 4GB RAM and SCSI hdd
> expecting to see better performance, but instead performance dropped to
> ~44 secs for 10,000 INSERTs.
Your development system is probably running inexpensive IDE disks that
cache writes, while the test server is not caching. If you loop over
single inserts, PostgreSQL's default configuration will do a physical
commit to disk after every one of them, which limits performance to how
fast the disk spins. If your server has 15K RPM drives, a single client
can commit at most 250 transactions per second to disk, which means 10,000
inserts done one at a time must take at least 40 seconds no matter how
fast the server is.
There's a rambling discussion of this topic at
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm that
should fill in some background here.
If you use COPY instead of INSERT, that bypasses the WAL and you don't see
this. Also, if you adjust your loop to do multiple inserts as a single
transaction, that will change the behavior here as well.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL 8.2.5 slow performance on INSERT on Linux
@ 2007-12-02 23:32 Beyers Cronje <bcronje@gmail.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 4+ messages in thread
From: Beyers Cronje @ 2007-12-02 23:32 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
>
> Your development system is probably running inexpensive IDE disks that
> cache writes, while the test server is not caching. If you loop over
> single inserts, PostgreSQL's default configuration will do a physical
> commit to disk after every one of them, which limits performance to how
> fast the disk spins. If your server has 15K RPM drives, a single client
> can commit at most 250 transactions per second to disk, which means 10,000
> inserts done one at a time must take at least 40 seconds no matter how
> fast the server is.
>
> There's a rambling discussion of this topic at
> http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm<http://www.westnet.com/%7Egsmit...
> should fill in some background here.
This is exactly what is happening. Thank you for the above link, the article
was very informative.
If you use COPY instead of INSERT, that bypasses the WAL and you don't see
> this. Also, if you adjust your loop to do multiple inserts as a single
> transaction, that will change the behavior here as well.
I will give COPY a go and see how it performs. For testing we specifically
only did one insert per transaction, we will obviously optimize the actual
application to do multiple insert per transaction where-ever possible.
Kind regards
Beyers Cronje
PS Thank you for the quick responses Greg and Pavel. It is always
encouraging starting off with a new product and seeing there are people
passionate about it.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-12-02 23:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-02 21:34 PostgreSQL 8.2.5 slow performance on INSERT on Linux Beyers Cronje <bcronje@gmail.com>
2007-12-02 21:46 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-12-02 22:53 ` Greg Smith <gsmith@gregsmith.com>
2007-12-02 23:32 ` Beyers Cronje <bcronje@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox