Re: database slowdown while a lot of inserts occur

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: database slowdown while a lot of inserts occur
Date: 2012-03-31 01:21:33
Message-ID: 4F765C1D.7010205@fuzzy.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2012-03-31 01:32:31 Re: Tablespaces on a raid configuration
Previous Message Tomas Vondra 2012-03-31 01:11:08 Re: database slowdown while a lot of inserts occur