Skip site navigation (1) Skip section navigation (2)

Re: database slowdown while a lot of inserts occur

From: Deron <fecastle(at)gmail(dot)com>
To: "Campbell, Lance" <lance(at)illinois(dot)edu>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: database slowdown while a lot of inserts occur
Date: 2012-03-29 18:17:24
Message-ID: CAF3Lvs6nrXDdzCCaS-Hv_aCS4P=ct6Q=EzuKzt1WqEs-Xxsz9g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>
>

In response to

Responses

pgsql-performance by date

Next:From: Bob LunneyDate: 2012-03-29 19:27:08
Subject: Re: database slowdown while a lot of inserts occur
Previous:From: Filippos KalamidasDate: 2012-03-29 18:14:05
Subject: Re: database slowdown while a lot of inserts occur

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group