From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: database slowdown while a lot of inserts occur
Date: Sat, 31 Mar 2012 03:21:33 +0200
Message-ID: <4F765C1D.7010205@fuzzy.cz> (raw)
In-Reply-To: <B75CD08C73BD3543B97E4EF3964B7D701E43F734@CITESMBX5.ad.uillinois.edu>
References: <B75CD08C73BD3543B97E4EF3964B7D701E43F734@CITESMBX5.ad.uillinois.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: database slowdown while a lot of inserts occur
In-Reply-To: <4F765C1D.7010205@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox