Re: database slowdown while a lot of inserts occur

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxx> 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
>
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux