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
