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
