Обсуждение: 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@illinois.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
>
>

On 03/29/2012 03:27 PM, Bob Lunney wrote:
> Lance,
>
> May small inserts cause frequent fsyncs.  Is there any way those small
> inserts can be batched into some larger sets of inserts that use copy
> to perform the load?



Or possibly a prepared statement called many times in a single
transaction, if you're not using that already. It's not as efficient as
COPY, but it's often a much less extensive change to the code.

cheers

andrew

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
12spindles made a world of difference in my case. You can use iostat as Deron has suggested below to get information of
latencywhich should help you find if disks are a bottleneck. Unless server RAM is very small and it also doubles up as
applicationserver 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
hasincreased the efficiency of inserts in my case. It would be safe to suggest that use of batch mode in programming
languageyou 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
theinsert 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,
butcaveat 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
informationintended only for the use of the individual or entity named as addressee.  The review, dissemination,
distributionor copying of this communication by or to anyone other than the intended addressee is strictly prohibited.
Ifyou have received this communication in error, please immediately notify the sender by replying to the message and
destroyall copies of the original message. 


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Deron
Sent: Thursday, March 29, 2012 11:47 PM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.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@illinois.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Thu, Mar 29, 2012 at 12:02 PM, Campbell, Lance <lance@illinois.edu> wrote:
> I forgot to mention that the slowdown in particular for other applications
> is when they are trying to insert or update tables unrelated to the
> application mentioned in my prior application that does the massive small
> inserts.

It sounds like you're just flooding your IO channels.  Can you
throttle your write rate in your main application that's inserting so
many inserts?  Something that sleeps 10ms between every hundred rows
or some such?

On 29.3.2012 21:27, Bob Lunney wrote:
> Lance,
>
> May small inserts cause frequent fsyncs.  Is there any way those small
> inserts can be batched into some larger sets of inserts that use copy to
> perform the load?

Not necessarily - fsync happens at COMMIT time, not when the INSERT is
performed (unless each INSERT stands on it's own).

Tomas

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