Message-ID: <4F765C1D.7010205@fuzzy.cz>
Date: Sat, 31 Mar 2012 03:21:33 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: database slowdown while a lot of inserts occur
References: 
 <B75CD08C73BD3543B97E4EF3964B7D701E43F734@CITESMBX5.ad.uillinois.edu>
In-Reply-To: 
 <B75CD08C73BD3543B97E4EF3964B7D701E43F734@CITESMBX5.ad.uillinois.edu>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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