From: Kevin Grittner <kgrittn@ymail.com>
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
To: John R Pierce <pierce@hogranch.com>
Cc: pgsql <pgsql-general@postgresql.org>
Subject: Re: Checkpoint_segments optimal value
Date: Mon, 28 Jul 2014 07:20:26 -0700
Message-ID: <1406557226.17685.YahooMailNeo@web122301.mail.ne1.yahoo.com> (raw)
In-Reply-To: <CAOf_bQaAx1uRH+GAMReYwOGWgOLgZJUyPabUS2V5-e_CB13Y=g@mail.gmail.com>
References: <CAOf_bQYehfKp7CNRa_pYQ+-SmHyDSYJ8bddbaixXUcWmSjH6wA@mail.gmail.com>
<1405975881.98993.YahooMailNeo@web122303.mail.ne1.yahoo.com>
<53CD8048.4010002@hogranch.com>
<CAOf_bQaAx1uRH+GAMReYwOGWgOLgZJUyPabUS2V5-e_CB13Y=g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Prabhjot Sheena <prabhjot.sheena@rivalwatch.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, prabhjot.sheena@rivalwatch.com, pierce@hogranch.com, pgsql-general@postgresql.org
Subject: Re: Checkpoint_segments optimal value
In-Reply-To: <1406557226.17685.YahooMailNeo@web122301.mail.ne1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox