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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)