Re: Checkpoint_segments optimal value

Prabhjot Sheena <prabhjot(dot)sheena(at)rivalwatch(dot)com> wrote:

> PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9)

Running anything on that version is insane.  Not only has the 8.3
major release been out of support since February of 2013, but
you're missing about 4 years of fixes for serious bugs and security
issues since the last patch version of 8.3 before it went out of
support.  Any vacuum or planning problems you are seeing on that
release are very likely fixed on a supported version.

> This is what i did to improve query performance. i recreated all
> the indexes on work_unit table and have been running vacuum
> analyze through cron job 3 times a day on two tables that are in
> the query. The query performance is between 2 to 3 seconds now.

That's pretty solid evidence that you have been having problems
with bloat.  The most common cause of this is not setting
autovacuum to be aggressive enough.

> autovacuum_max_workers = 1

Vacuum of one big table could starve all other tables, resulting in
bloat.  If anything, this should be set larger than the default.
You might want to try setting it to somewhere in the 3 to 5 range,
but then watch for any long periods where all workers are busy.  If
you see that, you probably need more workers.  I would also boost
autovacuum_vacuum_cost_limit to maybe 500.

> The strange thing i noticed is that just today at one time query
> performance came down to under 1 second and started using this
> query plan

There's really no point hypothesizing about what might cause that
in such an old version with so many known bugs.  The best way to
improve performance would be to upgrade.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company