Re: Checkpoint_segments optimal value

From: Kevin Grittner <kgrittn(at)ymail(dot)com>
To: Prabhjot Sheena <prabhjot(dot)sheena(at)rivalwatch(dot)com>, John R Pierce <pierce(at)hogranch(dot)com>
Cc: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Re: Checkpoint_segments optimal value
Date: 2014-07-28 14:20:26
Message-ID: 1406557226.17685.YahooMailNeo@web122301.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Jeff Janes 2014-07-28 17:29:25 Re: Very slow planning performance on partition table
Previous Message John Scalia 2014-07-28 13:38:01 Re: I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2014-07-28 14:30:44 Re: Re: [GENERAL] pg_dump behaves differently for different archive formats
Previous Message François Beausoleil 2014-07-28 14:09:04 Pairwise array sum aggregate function?