Re: database slowdown while a lot of inserts occur

Hi,

On 29.3.2012 19:59, Campbell, Lance wrote:
> 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.

Can you provide more info? Show us some vmstat / 'iostat -x' logs so
that we can see what kind of bottleneck are you hitting. Provide more
details about your system (especially I/O subsystem - what drives, what
RAID config etc.)

Also, we need more details about the workload. Is each INSERT a separate
transaction or are they grouped into transactions fo multiple INSERTs?

> 1) What should I do to confirm that the database is the issue and
> not the applications?

Well, usually the application is the culprit. Some applications are
designed so that it's almost certain there was a 'let's poke the
database as hard as possible' goal at the beginning. Not sure it's this
case, though.

Might be a misconfigured database too - what are the basic parameters
(shared buffers, ...)?

> 2) How can I identify where the bottle neck is occurring if the
> issue happens to be with the database?

Watching 'iostat -x' or 'top' will usually point you the right
direction. Is the CPU fully utilized => you're doing something that
needs more CPU time than you have? Is the I/O wait high (say above 50%)?
Well, you have issues with I/O bottlenecks (either random or
sequential). The less visible bottlenecks are usually related to memory,
bus bandwidth etc.

Tomas