Re: Performance degradation of inserts when database size grows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux