Re: database slowdown while a lot of inserts occur

On a Linux system you can use tools like "sar" and "iostat" to watch
disk activity and view the writes/second or I am sure there are other
tools you can use. Watch CPU and memory with "top" If it does appear
to be an I/O issue there are
some things you can do in either hardware or software, or if it is a
CPU/ memory issue building indexes or running updates on triggers

A simple suggestion is:
Move the bulk insert application to run during 'off' or 'slow'
hours if possible.

Some Software suggestions are:
Use the PG "Copy" to do the bulk insert
http://www.postgresql.org/docs/9.0/static/sql-copy.html
(or)
Drop the indexes (or triggers), do the inserts and build indexes
and triggers.

Some Hardware suggestions are dependendent on if it is I/O, CPU, or
memory bottleneck.

Deron

On Thu, Mar 29, 2012 at 11:59 AM, Campbell, Lance <lance(at)illinois(dot)edu> 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.
>
>
>
> 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
>
>