Re: Slow INSERT

Tom Lane wrote:
> It's hard to see how inserting to such a simple table would be slow.

Indeed.

> Is the number of inserts between slowdowns perfectly repeatable? My
> first thought is that the fast case is associated with inserting onto a
> page that is the same one last inserted to, and the slow case is
> associated with finding a new page to insert onto (which, given that you
> never UPDATE or DELETE, will always mean extending the file). Given
> that the table rows are fixed width, the number of rows that fit on a
> page should be constant, so this theory cannot be right if the number of
> inserts between slowdowns varies.

I ran some tests to support this hypothesis. Every 500th insert is a tad
slower, but it is insignificant (normally the INSERT lasts 1.5ms, every
500th is 9ms). During my tests (10 runs of 1000 INSERTS) I had
experienced only one "slow" insert (2000ms). It is clearly caused by
other processes running on this server, but such degradation of
performance is highly suspicious, because the server very rarely goes
over load 1.0. Just for the record, it is FreeBSD 4.9 and the system
never swaps.

> Also, are all the inserts being issued by the same server process, or
> are they scattered across multiple processes? I'm not sure this theory
> holds water unless all the inserts are done in the same process.

Nope. It is a webserver, so these requests are pushed through several
persistent connections (20-30, depends on current load). This insert
occurs only once per pageload.

--
Michal Taborsky
http://www.taborsky.cz