SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-09-2008, 07:32 AM
| ||||
| ||||
 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?      |
|
04-09-2008, 07:32 AM
| ||||
| ||||
| Re: Performance of batch COMMIT 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings |
Linear Mode
