From: Richard Huxton <dev@archonet.com>
To: joël Winteregg <joel.winteregg@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Insert performance
Date: Tue, 06 Mar 2007 08:08:29 +0000
Message-ID: <45ED217D.30404@archonet.com> (raw)
In-Reply-To: <1173167607.4917.8.camel@hatman>
References: <1172596221.393824.41130@j27g2000cwj.googlegroups.com>
<45ED1996.7050008@archonet.com>
<1173167607.4917.8.camel@hatman>
joël Winteregg wrote:
> Hi Richard,
>
>>> Here is my problem. With some heavy insert into a simple BD (one
>>> table, no indexes) i can't get better perf than 8000 inserts/sec. I'm
>>> testing it using a simple C software which use libpq and which use:
>>> - Insert prepared statement (to avoid too many request parsing on the
>>> server)
>>> - transaction of 100000 inserts
>> Are each of the INSERTs in their own transaction?
>>
>
> No, as said above transactions are made of 100000 inserts...
Hmm - I read that as just meaning "inserted 100000 rows". You might find
that smaller batches provide peak performance.
>> If so, you'll be limited by the speed of the disk the WAL is running on.
>>
>> That means you have two main options:
>> 1. Have multiple connections inserting simultaneously.
>
> Yes, you're right. That what i have been testing and what provide the
> best performance ! I saw that postgresql frontend was using a lot of CPU
> and not both of them (i'm using a pentium D, dual core). To the opposit,
> the postmaster process use not much resources. Using several client,
> both CPU are used and i saw an increase of performance (about 18000
> inserts/sec).
>
> So i think my bottle neck is more the CPU speed than the disk speed,
> what do you think ?
Well, I think it's fair to say it's not disk. Let's see - the original
figure was 8000 inserts/sec, which is 0.125ms per insert. That sounds
plausible to me for a round-trip to process a simple command - are you
running the client app on the same machine, or is it over the network?
Two other things to bear in mind:
1. If you're running 8.2 you can have multiple sets of values in an INSERT
http://www.postgresql.org/docs/8.2/static/sql-insert.html
2. You can do a COPY from libpq - is it really not possible?
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, joel.winteregg@gmail.com
Subject: Re: Insert performance
In-Reply-To: <45ED217D.30404@archonet.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