Re: Performance degradation of inserts when database size grows

Tomas Vondra <tv(at)fuzzy(dot)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(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance