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>
Message-ID: <1406557226.17685.YahooMailNeo@web122301.mail.ne1.yahoo.com>
Date: Mon, 28 Jul 2014 07:20:26 -0700
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Checkpoint_segments optimal value
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>,
 John R Pierce <pierce@hogranch.com>
Cc: pgsql <pgsql-general@postgresql.org>
In-Reply-To: 
 <CAOf_bQaAx1uRH+GAMReYwOGWgOLgZJUyPabUS2V5-e_CB13Y=g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

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.=A0 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.=A0 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.=A0 The most common cause of this is not setting
autovacuum to be aggressive enough.

> autovacuum_max_workers =3D 1

Vacuum of one big table could starve all other tables, resulting in
bloat.=A0 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.=A0 If
you see that, you probably need more workers.=A0 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.=A0 The best way to
improve performance would be to upgrade.

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


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