Message-ID: <4DDC0514.3070207@fuzzy.cz>
Date: Tue, 24 May 2011 21:20:52 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101219 Lightning/1.0b3pre Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Performance degradation of inserts when database size
 grows
References: <BANLkTi=VKBmRLVLDjy8qxpWx_6-rmbUaXg@mail.gmail.com>
	<BANLkTimBDQHNDBtLbtzssq6LOR9u-H7rBA@mail.gmail.com>
In-Reply-To: <BANLkTimBDQHNDBtLbtzssq6LOR9u-H7rBA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Dne 24.5.2011 07:24, Terry Schmitt napsal(a):
> As near as I can tell from your test configuration description, you have
> JMeter --> J2EE --> Postgres.
> Have you ruled out the J2EE server as the problem? This problem may not
> be the database.
> I would take a look at your app server's health and look for any
> potential issues there before spending too much time on the database.
> Perhaps there are memory issues or excessive garbage collection on the
> app server?

It might be part of the problem, yes, but it's just a guess. We need to
se some iostat / iotop / vmstat output to confirm that.

The probable cause here is that the indexes grow with the table, get
deeper, so when you insert a new row you need to modify more and more
pages. That's why the number of buffers grows over time and the
checkpoint takes more and more time (the average write speed is about 15
MB/s - not sure if that's good or bad performance).

The question is whether this is influenced by other activity (Java GC or
something)

I see three ways to improve the checkpoint performance:

  1) set checkpoint_completion_target = 0.9 or something like that
     (this should spread the checkpoint, but it also increases the
     amount of checkpoint segments to keep)

  2) make the background writer more aggressive (tune the bgwriter_*
     variables), this is similar to (1)

  3) improve the write performance (not sure how random the I/O is in
     this case, but a decent controller with a cache might help)

and then two ways to decrease the index overhead / amount of modified
buffers

  1) keep only the really necessary indexes (remove duplicate, indexes,
     remove indexes where another index already performs reasonably,
     etc.)

  2) partition the table (so that only indexes on the current partition
     will be modified, and those will be more shallow)

Tomas