Re: database slowdown while a lot of inserts occur

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(at)illinois(dot)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