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

Re: set autovacuum=off

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
Cc: Peter van Hardenberg <pvh(at)heroku(dot)com>, Andy Colson <andy(at)squeakycode(dot)net>, Thom Brown <thom(at)linux(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: set autovacuum=off
Date: 2012-02-23 21:07:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Thu, Feb 23, 2012 at 11:26 AM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> **
> You need to rethink things a bit. Databases can fail in all sorts of ways
> and can slow down during bursts of activity, data dumps, etc. You may need
> to investigate some form of intermediate buffering.
> Currently my "buffer" (such as it is) is Kestrel<> which
queues up INSERTs and then executes them one at a time. This keeps the rest
of the app from being held back, but it becomes a problem when the queue
fills up faster than it can drain. For one particularly heavy logger, I
tried writing it all to an unconstrained table with the idea that I would
copy that table (using INSERT . . .  SELECT . . .) into another table with
constraints, reducing the data in the process (deduping and such). Problem
was, even my constraint-less table wasn't fast enough. Perhaps buffering to
a local file and then using COPY would do the trick.

>  ...Apparently the last four columns don't exist in my database. As for
> the first four, that is somewhat illuminating....
> Then you are not running a current version of PostgreSQL so the first step
> to performance enhancement is to upgrade. (As a general rule - there are
> occasionally specific cases where performance decreases.)
> We're using 9.0.6. Peter, how do you feel about upgrading? :)

How are you batching them? Into a temp table that is copied to the main
> table? As a bunch of insert statements within a single connection (saves
> probably considerable time due to eliminating multiple connection setups)?
> With one PREPARE and multiple EXECUTE (to save repeated planning time - I'm
> not sure this will buy you much for simple inserts, though)? With COPY
> (much faster as many records are inserted in a single statement but if one
> fails, all fail)?
> The second one (a bunch of insert statements within a single connection).
As I mentioned above, I was going to try the temp table thing, but that
wasn't fast enough. COPY might be my next attempt.

> And what is the 50ms limit? Is that an average? Since you are batching, it
> doesn't sound like you need every statement to complete in 50ms. There is
> always a tradeoff between overall maximum throughput and maximum allowed
> latency.
> No, not average. I want to be able to do 100-200 INSERTs per second (90%
of those would go to one of two tables, the other 10% would go to any of a
couple dozen tables). If 1% of my INSERTs take 100 ms, then the other 99%
must take no more than 9 ms to complete.
...actually, it occurs to me that since I'm now committing batches of 1000,
a 100ms latency per commit wouldn't be bad at all! I'll have to look into
that.... (Either that or my batching isn't working like I thought it was.)

> I recommend you abandon this thread as it presupposes a now seemingly
> incorrect cause of the problem and start a new one titled something like
> "Tuning for high insert rate" where you describe the problem you want to
> solve. See a good guide to the information that will be helpful in diagnosis.
> I'll leave the title as is since I think simply renaming this message
would cause more confusion than it would prevent. But this gives me
something to chew on and when I need to return to this topic, I'll do just


In response to


pgsql-performance by date

Next:From: Jayashankar K BDate: 2012-02-23 21:09:10
Subject: Re: [PERFORM] Disable-spinlocks while compiling postgres 9.1 for ARM Cortex A8
Previous:From: Andy ColsonDate: 2012-02-23 20:57:29
Subject: Re: set autovacuum=off

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