pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michal Taborsky <michal@taborsky.cz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow INSERT
Date: Tue, 29 Jun 2004 17:09:47 +0200
Message-ID: <40E1863B.9070208@taborsky.cz> (raw)
In-Reply-To: <29095.1088519446@sss.pgh.pa.us>
References: <40E160E6.4080105@taborsky.cz>
	<29095.1088519446@sss.pgh.pa.us>

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




view thread (8+ messages)  latest in thread

Message-ID: <40E1863B.9070208@taborsky.cz>
Permalink:  ../40E1863B.9070208@taborsky.cz/
Also on:    postgresql.org/message-id/40E1863B.9070208@taborsky.cz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: michal@taborsky.cz, tgl@sss.pgh.pa.us
  Subject: Re: Slow INSERT
  In-Reply-To: <40E1863B.9070208@taborsky.cz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox