On Thu, Feb 23, 2012 at 11:26 AM, Steve Crawford <
> 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
> 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
In response to
pgsql-performance by date
|Next:||From: Jayashankar K B||Date: 2012-02-23 21:09:10|
|Subject: Re: [PERFORM] Disable-spinlocks while compiling postgres 9.1 for
ARM Cortex A8 |
|Previous:||From: Andy Colson||Date: 2012-02-23 20:57:29|
|Subject: Re: set autovacuum=off|