Re: database slowdown while a lot of inserts occur

Lance,

Have faced the same issue with thousands of small inserts (actually they were inserts/updates) causing the database to slowdown. You have received good suggestions from the list, but listing them as points will make the issue clearer:

1) Disk configuration: RAID 5 was killing the performance after the database grew beyond 100 GB. Getting a RAID 10 with 12 spindles made a world of difference in my case. You can use iostat as Deron has suggested below to get information of latency which should help you find if disks are a bottleneck. Unless server RAM is very small and it also doubles up as application server or has other processes running, the RAM should not be a bottleneck.

Also have separate logging and data disks, which has been suggested in many posts in past.

2) Invoking Batch mode in program: In JDBC, there is a batch insert mode. Invoking the batch mode for a set of records has increased the efficiency of inserts in my case. It would be safe to suggest that use of batch mode in programming language you have used will give improved speeds.

3) Dropping indexes/ triggers: This will not work if the application has multiple instances running at same time OR if the insert is actually an insert/update.

4) You should think of using COPY command since you have mentioned that the table is NOT used by other applications, but caveat of multiple instances mentioned above will still hold true.

5) Enabling autovacuum and autoanalyse : A must. Infact you should force a vacuum and analyze if the insert batch is large.

HTH,

Shrirang Chitnis

The information contained in this message, including any attachments, is attorney privileged and/or confidential information intended only for the use of the individual or entity named as addressee. The review, dissemination, distribution or copying of this communication by or to anyone other than the intended addressee is strictly prohibited. If you have received this communication in error, please immediately notify the sender by replying to the message and destroy all copies of the original message.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Deron
Sent: Thursday, March 29, 2012 11:47 PM
To: Campbell, Lance
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] 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
>
>

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