From: Santhakumaran <santhkn@yahoo.com>
To: 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 19:01:06 -0700
Message-ID: <phu0cpg1cm737uhvpvp6r8da.1306288866249@email.android.com> (raw)
Tomas Vondra <tv@fuzzy.cz> wrote:
>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
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
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: santhkn@yahoo.com, tv@fuzzy.cz
Subject: Re: Performance degradation of inserts when database size grows
In-Reply-To: <phu0cpg1cm737uhvpvp6r8da.1306288866249@email.android.com>
* 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