From gnanam@zoniac.com Tue Mar  30 09:32:43 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 1EDE46325AC
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 06:32:43 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 54390-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 09:32:32 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id 5C42062DBD0
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Mar 2010 06:32:32 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2U9WRok016902
	for <pgsql-performance@postgresql.org>; Tue, 30 Mar 2010 04:32:29 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: <pgsql-performance@postgresql.org>
Subject: REINDEXing database-wide daily
Date: Tue, 30 Mar 2010 15:02:23 +0530
Organization: Zoniac Software Private Limited
Message-ID: <00f801cacfeb$e8b8f4a0$ba2adde0$@com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00F9_01CAD01A.027130A0"
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcrP6+Z1Wjal5+jzQFm9DfnI/NXtxw==
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5
	tests=BAYES_50=0.001, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/381
X-Sequence-Number: 38206

This is a multipart message in MIME format.

------=_NextPart_000_00F9_01CAD01A.027130A0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,

 

We're using PostgreSQL 8.2.

 

I have a question in connection to this question posted by me earlier:

http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.php

 

In our application, DML operations (INSERT/UPDATE/DELETE) are heavily
performed in a day.

 

I also read about pg_autovacuum & REINDEX at:

http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html

http://www.postgresql.org/docs/8.2/static/sql-reindex.html

 

I do not want to run pg_autovacuum daemon on a busy hour.

 

In case, if I can afford to take my database offline at low-usage time and
perform REINDEX database-wide manually/linux cron, to boost up index
performance, what is the community answer/suggestion on the following:

1.     Is it a good idea to perform this on a daily basis?

2.     Any implications of doing this on a daily basis?

3.     Is there a way to find out bloated indexes?

4.     Any other maintenance command, like ANALYZE, that has to be executed
before/after REINDEX?

5.     Is there a way to find out when REINDEX was last run on an
INDEX/TABLE/DATABASE?

 

NOTE: I've also seen from my past experience that REINDEX database-wide
greatly improves performance of the application.

 


------=_NextPart_000_00F9_01CAD01A.027130A0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:9.0pt;
	font-family:"Arial","sans-serif";
	color:navy;}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
	{mso-style-priority:34;
	margin-top:0in;
	margin-right:0in;
	margin-bottom:0in;
	margin-left:.5in;
	margin-bottom:.0001pt;
	font-size:9.0pt;
	font-family:"Arial","sans-serif";
	color:navy;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Arial","sans-serif";
	color:navy;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
 /* List Definitions */
 @list l0
	{mso-list-id:977496868;
	mso-list-type:hybrid;
	mso-list-template-ids:-1857256646 1566071620 67698713 67698715 67698703 =
67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
	{mso-level-tab-stop:none;
	mso-level-number-position:left;
	margin-left:.75in;
	text-indent:-.25in;}
ol
	{margin-bottom:0in;}
ul
	{margin-bottom:0in;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>Hi,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>We're using PostgreSQL 8.2.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I have a question in connection to this question =
posted by
me earlier:<o:p></o:p></p>

<p class=3DMsoNormal><a
href=3D"http://archives.postgresql.org/pgsql-performance/2010-03/msg00343=
.php">http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.p=
hp</a><o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>In our application, DML operations =
(INSERT/UPDATE/DELETE)
are heavily performed in a day.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I also read about pg_autovacuum &amp; REINDEX =
at:<o:p></o:p></p>

<p class=3DMsoNormal><a
href=3D"http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.=
html">http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.ht=
ml</a><o:p></o:p></p>

<p class=3DMsoNormal><a
href=3D"http://www.postgresql.org/docs/8.2/static/sql-reindex.html">http:=
//www.postgresql.org/docs/8.2/static/sql-reindex.html</a><o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I do not want to run pg_autovacuum daemon on a busy =
hour.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>In case, if I can afford to take my database =
offline at
low-usage time and perform REINDEX database-wide manually/linux cron, to =
boost
up index&nbsp; performance, what is the community answer/suggestion on =
the
following:<o:p></o:p></p>

<p class=3DMsoListParagraph =
style=3D'margin-left:.75in;text-indent:-.25in;
mso-list:l0 level1 lfo1'><![if !supportLists]><span =
style=3D'mso-list:Ignore'>1.<span
style=3D'font:7.0pt "Times New Roman"'>&nbsp;&nbsp;&nbsp;&nbsp; =
</span></span><![endif]>Is
it a good idea to perform this on a daily basis?<o:p></o:p></p>

<p class=3DMsoListParagraph =
style=3D'margin-left:.75in;text-indent:-.25in;
mso-list:l0 level1 lfo1'><![if !supportLists]><span =
style=3D'mso-list:Ignore'>2.<span
style=3D'font:7.0pt "Times New Roman"'>&nbsp;&nbsp;&nbsp;&nbsp; =
</span></span><![endif]>Any
implications of doing this on a daily basis?<o:p></o:p></p>

<p class=3DMsoListParagraph =
style=3D'margin-left:.75in;text-indent:-.25in;
mso-list:l0 level1 lfo1'><![if !supportLists]><span =
style=3D'mso-list:Ignore'>3.<span
style=3D'font:7.0pt "Times New Roman"'>&nbsp;&nbsp;&nbsp;&nbsp; =
</span></span><![endif]>Is
there a way to find out bloated indexes?<o:p></o:p></p>

<p class=3DMsoListParagraph =
style=3D'margin-left:.75in;text-indent:-.25in;
mso-list:l0 level1 lfo1'><![if !supportLists]><span =
style=3D'mso-list:Ignore'>4.<span
style=3D'font:7.0pt "Times New Roman"'>&nbsp;&nbsp;&nbsp;&nbsp; =
</span></span><![endif]>Any
other maintenance command, like ANALYZE, that has to be executed =
before/after
REINDEX?<o:p></o:p></p>

<p class=3DMsoListParagraph =
style=3D'margin-left:.75in;text-indent:-.25in;
mso-list:l0 level1 lfo1'><![if !supportLists]><span =
style=3D'mso-list:Ignore'>5.<span
style=3D'font:7.0pt "Times New Roman"'>&nbsp;&nbsp;&nbsp;&nbsp; =
</span></span><![endif]>Is
there a way to find out when REINDEX was last run on an =
INDEX/TABLE/DATABASE?<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>NOTE: I've also seen from my past experience that =
REINDEX
database-wide greatly improves performance of the =
application.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

</div>

</body>

</html>

------=_NextPart_000_00F9_01CAD01A.027130A0--




From andy@squeakycode.net Tue Mar  30 14:06:11 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 5B9FE634889
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 11:06:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 05514-04-4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 14:05:49 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from marajade.localdomain (vcs5.camavision.com [63.228.164.252])
	by mail.postgresql.org (Postfix) with ESMTP id 1A5C16349AB
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Mar 2010 11:05:24 -0300 (ADT)
Received: from [192.168.10.84] (unknown [192.168.10.84])
	(Authenticated sender: andy)
	by marajade.localdomain (Postfix) with ESMTPA id 6544753C8B;
	Tue, 30 Mar 2010 09:05:22 -0500 (CDT)
Message-ID: <4BB20521.5000307@squeakycode.net>
Date: Tue, 30 Mar 2010 09:05:21 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3
MIME-Version: 1.0
To: Gnanakumar <gnanam@zoniac.com>, pgsql-performance@postgresql.org
Subject: Re: REINDEXing database-wide daily
References: <00f801cacfeb$e8b8f4a0$ba2adde0$@com>
In-Reply-To: <00f801cacfeb$e8b8f4a0$ba2adde0$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.891 tagged_above=-10 required=5 tests=AWL=0.708,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/386
X-Sequence-Number: 38211

On 3/30/2010 4:32 AM, Gnanakumar wrote:
> Hi,
>
> We're using PostgreSQL 8.2.
>
> I have a question in connection to this question posted by me earlier:
>
> http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.php
>
> In our application, DML operations (INSERT/UPDATE/DELETE) are heavily
> performed in a day.
>
> I also read about pg_autovacuum & REINDEX at:
>
> http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html
>
> http://www.postgresql.org/docs/8.2/static/sql-reindex.html
>
> I do not want to run pg_autovacuum daemon on a busy hour.
>
> In case, if I can afford to take my database offline at low-usage time
> and perform REINDEX database-wide manually/linux cron, to boost up index
> performance, what is the community answer/suggestion on the following:
>
> 1. Is it a good idea to perform this on a daily basis?
>
> 2. Any implications of doing this on a daily basis?
>
> 3. Is there a way to find out bloated indexes?
>
> 4. Any other maintenance command, like ANALYZE, that has to be executed
> before/after REINDEX?
>
> 5. Is there a way to find out when REINDEX was last run on an
> INDEX/TABLE/DATABASE?
>
> NOTE: I've also seen from my past experience that REINDEX database-wide
> greatly improves performance of the application.
>


I could be way off base here, so I hope others will confirm/deny this: 
I think the more often you run vacuum, the less you notice it.  If you 
wait for too long then vacuum will have to work harder and you'll notice 
a speed decrease.  But many small vacuums which dont have as much work 
to do, you wont notice.

It could be, and I'm guessing again, because your database grew from 3 
to 30 gig (if I recall the numbers right), REINDEX had lots of affect. 
But if vacuum can keep up with space reuse, REINDEX may not be needed. 
(maybe a few weeks or once a month).

-Andy





From Kevin.Grittner@wicourts.gov Tue Mar  30 15:05:40 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 94897633E1B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 12:05:40 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 18033-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 30 Mar 2010 15:05:30 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id A871F633C83
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Mar 2010 12:05:29 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Tue, 30 Mar 2010 10:05:27 -0500
Message-Id: <4BB1CCE302000025000301F5@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Tue, 30 Mar 2010 10:05:23 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,<gnanam@zoniac.com>
Subject: Re: REINDEXing database-wide daily
References: <00f801cacfeb$e8b8f4a0$ba2adde0$@com>
In-Reply-To: <00f801cacfeb$e8b8f4a0$ba2adde0$@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 201003/389
X-Sequence-Number: 38214

"Gnanakumar" <gnanam@zoniac.com> wrote:
 
> We're using PostgreSQL 8.2.
 
Newer versions have much improved the VACUUM and CLUSTER features. 
You might want to consider upgrading to a later major version.
 
> I have a question in connection to this question posted by me
> earlier:
> 
>
http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.php
 
I hope that you have stopped using VACUUM FULL on a regular basis,
based on the responses to that post.  The FULL option is only
intended as a means to recover from extreme heap bloat when there is
not room for a CLUSTER.  Any other use is going to cause problems. 
If you continue to use it for other purposes, you may not get a lot
of sympathy when you inevitably experience those problems.
 
> I do not want to run pg_autovacuum daemon on a busy hour.
 
You would probably be surprised to see how much of a performance
boost you can get during your busy times by having a properly
configured autovacuum running.  My initial reaction to seeing
performance degradation during autovacuum was to make it less
aggressive, which lead to increasing bloat between autovacuum runs,
which degraded performance between runs and made things that much
worse when autovacuum finally kicked in.  It was only by using
aggressive maintenance to clean up the bloat and then configuring
autovacuum to be much more aggressive that I saw performance during
peak periods improve; although on some systems I had to introduce a
10 ms vacuum cost delay.
 
This is one of those areas where your initial intuitions can be
totally counter-productive.
 
> In case, if I can afford to take my database offline at low-usage
> time and perform REINDEX database-wide manually/linux cron, to
> boost up index performance, what is the community
> answer/suggestion on the following:
> 
> 1.     Is it a good idea to perform this on a daily basis?
 
No.  It is generally not something to run on a routine basis, and if
you're not careful you could make performance worse, by making the
indexes so "tight" that most of your inserts or updates will cause
index page splits.
 
> 2.     Any implications of doing this on a daily basis?
 
We haven't found it necessary or useful, but if you have an
appropriate fill factor, I suppose it might not actually do any
damage.  There is some chance, based on your usage pattern, that a
daily CLUSTER of some tables might boost performance by reducing
random access, but daily REINDEX is unlikely to be a win.
 
> 3.     Is there a way to find out bloated indexes?
 
I don't have anything offhand, but you might poke around pg_class
looking at reltuples and relpages.
 
> 4.     Any other maintenance command, like ANALYZE, that has to be
> executed before/after REINDEX?
 
Not generally, but I seem to remember that there can be exceptions. 
Indexes on expressions?  GIN?
 
> 5.     Is there a way to find out when REINDEX was last run on an
> INDEX/TABLE/DATABASE?
 
I don't think so.
 
> NOTE: I've also seen from my past experience that REINDEX
> database-wide greatly improves performance of the application.
 
I don't doubt that; if you've been shooting yourself in the foot by
running VACUUM FULL, then REINDEX would be a good bandage to
alleviate the pain.
 
My suggestion is to clean up your existing bloat by running CLUSTER
on all tables, configure autovacuum to aggressive values similar to
what you see in 8.3 or 8.4 and turn it on, run a nightly VACUUM
ANALYZE VERBOSE of the database and review the output to make sure
your fsm settings are adequate and to monitor bloat, and eliminate
all use of VACUUM FULL or REINDEX unless you've somehow slipped up
and allowed extreme bloat.  This will allow tables and indexes to
"settle in" to an efficient size where they are not constantly
giving up disk space to the OS at night and then having to reacquire
it from the OS when under heavy load during the day.
 
-Kevin