Thread: Performance of batch COMMIT
Each week I have to update a very large database. Currently I run a commit about every 1000 queries. This vastly increased performance but I am wondering if the performance can be increased further. I could send all of the queries to a file but COPY doesn't support plain queries such as UPDATE, so I don't think that is going to help. The only time I have to run a commit is when I need to make a new table. The server has 4GB of memory and fast everything else. The only postgresql.conf variable I have changed is for the shared_memory.
Would sending all of the queries in a single query string increase performance?
What is the optimal batch size for commits?
Are there any postgresql.conf variable that should be tweaked?
Anybody have any suggestions?
On Mon, Dec 19, 2005 at 11:44:15AM -0800, Benjamin Arai wrote: > Each week I have to update a very large database. Currently I run a commit > about every 1000 queries. This vastly increased performance but I am > wondering if the performance can be increased further. I could send all of > the queries to a file but COPY doesn't support plain queries such as UPDATE, > so I don't think that is going to help. The only time I have to run a > commit is when I need to make a new table. The server has 4GB of memory and > fast everything else. The only postgresql.conf variable I have changed is > for the shared_memory. You should probably increase work_memory and maintenance_work_memory as well; possibly some other things. > Would sending all of the queries in a single query string increase > performance? The size of the query string shouldn't make any noticible difference unless you're trying to plow through a lot of statements. > What is the optimal batch size for commits? The size you need to guarantee consistency. If you're going to need to back a bunch of work out by hand if something fails mid-way through you're just creating extra work for yourself. The only reason I can think of for limiting transaction size is that I think certain operations (like AFTER triggers) can end up holding on to a lot of memory until the transaction commits. Though I'm not certain about that, it's possible they only take memory until the command that fired the triggers completes. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461