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 09:43:45 +0000
Message-ID: <45ED37D1.3000503@archonet.com> (raw)
In-Reply-To: <1173172748.4917.23.camel@hatman>
References: <1172596221.393824.41130@j27g2000cwj.googlegroups.com>
<45ED1996.7050008@archonet.com>
<1173167607.4917.8.camel@hatman>
<45ED217D.30404@archonet.com>
<1173172748.4917.23.camel@hatman>
joël Winteregg wrote:
>
>>> 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.
>
> Ahh ok ;-) sorry for my bad english... (yeah, i have been testing
> several transaction size 10000, 20000 and 100000)
Not your bad English, my poor reading :-)
>>>> 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?
>
> I did both test. On the local machine (using UNIX sockets) i can reach
> 18000 insert/sec with 10 clients and prepared statements. The same test
> using clients on the remote machine provide me 13000 inserts/sec.
OK, so we know what the overhead for network connections is.
> Now, with multiple client (multi-threaded inserts) my both CPU are quite
> well used (both arround 90%) so i maybe think that disk speeds are now
> my bottleneck. What do you think ? or maybe i will need a better CPU ?
>
>> 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
>
> Yeah, i'm running the 8.2.3 version ! i didn't know about multiple
> inserts sets ! Thanks for the tip ;-)
Ah-ha! Give it a go, it's designed for this sort of situation. Not sure
it'll manage thousands of value clauses, but working up from 10 perhaps.
I've not tested it for performance, so I'd be interesting in knowing how
it compares to your other results.
>> 2. You can do a COPY from libpq - is it really not possible?
>>
>
> Not really but i have been testing it and inserts are flying (about
> 100000 inserts/sec) !!
What's the problem with the COPY? Could you COPY into one table then
insert from that to your target table?
--
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: <45ED37D1.3000503@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