From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Performance degradation of inserts when database size grows
Date: Tue, 24 May 2011 21:20:52 +0200
Message-ID: <4DDC0514.3070207@fuzzy.cz> (raw)
In-Reply-To: <BANLkTimBDQHNDBtLbtzssq6LOR9u-H7rBA@mail.gmail.com>
References: <BANLkTi=VKBmRLVLDjy8qxpWx_6-rmbUaXg@mail.gmail.com>
<BANLkTimBDQHNDBtLbtzssq6LOR9u-H7rBA@mail.gmail.com>
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
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: tv@fuzzy.cz
Subject: Re: Performance degradation of inserts when database size grows
In-Reply-To: <4DDC0514.3070207@fuzzy.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