Re: set autovacuum=off

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<http://robey.github.com/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 http://wiki.postgresql.org/wiki/Guide_to_reporting_problemsfor 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
that.

Thanks,
-Alessandro