Few words regarding small inserts and a lot of fsyncs:
If it is your problem, you can fix this by using battery-backed raid card. Similar effect can be reached by turning synchronious commit off. Note that the latter may make few last commits lost in case of sudden reboot. But you can at least test if moving to BBU will help you. (Dunno if this setting can be changed with SIGHUP without restart).
Note that this may still be a lot of random writes. And in case of RAID5 - a lot of random reads too. I don't think batching will help other applications. This is the tool to help application that uses batching. If you have random writes, look at HOT updates - they may help you if you will follow requirements.
Check your checkpoints - application writes to commit log first (sequential write), then during checkpoints data is written to tables (random writes) - longer checkpoints may make you life easier. Try to increase checkpoint_segments.
If you have alot of data written - try to move you commit logs to another drive/partition.
If you have good raid card with memory and BBU, you may try to disable read cache on it (leaving only write cache). Read cache is usually good at OS level (with much more memory) and fast writes need BBU-protected write cache.
Best regards, Vitalii Tymchyshyn
2012/3/29 Campbell, Lance
<lance@illinois.edu>PostgreSQL 9.0.x
We have around ten different applications that use the same database. When one particular application is active it does an enormous number of inserts. Each insert is very small. During this time the database seems to slow down in general. The application in question is inserting into a particular table that is not used by the other applications.
1) What should I do to confirm that the database is the issue and not the applications?
2) How can I identify where the bottle neck is occurring if the issue happens to be with the database?
I have been using PostgreSQL for eight years. It is an amazing database.
Thanks,
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382
--
Best regards,
Vitalii Tymchyshyn