From: Beyers Cronje <bcronje@gmail.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.2.5 slow performance on INSERT on Linux
Date: Mon, 3 Dec 2007 01:32:21 +0200
Message-ID: <329bff5c0712021532r2f65369dkeb8d09ca45f97002@mail.gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0712021743510.13068@westnet.com>
References: <329bff5c0712021334k474483bav278f79e7e27b26ac@mail.gmail.com>
<Pine.GSO.4.64.0712021743510.13068@westnet.com>
>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bcronje@gmail.com, gsmith@gregsmith.com
Subject: Re: PostgreSQL 8.2.5 slow performance on INSERT on Linux
In-Reply-To: <329bff5c0712021532r2f65369dkeb8d09ca45f97002@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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