My PostgreSQL server has two CPUs (OS: Fedora 11), each with 4 cores. Total is 8cores. Now I have several clients running at the same time to do insert and update on the same table, each client having its own connection. I have made two testing with clients running in parallel to load 20M data in total. Each testing, the data is split evenly by the client number such that each client only loads a piece of data.
1) Long transaction: A client does the commit at the end of loading. Result: Each postgres consumes 95% CPU. The more clients run in parallel, the slower the total runing time is (when 8 clients, it is slowest). However, I expect the more clients run in parallel, it should be faster to load all the data.
2) Short transaction: I set the clients to do a commit on loading every 500 records. Results: Each postgres consumes about 50%CPU. Now the total running is as what i have expected; the more clients run in parallel, the faster it is (when 8 clients, it is fastest).
Could anybody help to why when I do the long transaction with 8 clients, it is slowest? How can I solve this problem? As I don't want to use the 2), in which I have to set the commit size each time.