pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
WAL configuration and REINDEX
7+ messages / 5 participants
[nested] [flat]

* WAL configuration and REINDEX
@ 2006-09-21 10:40  Jose Manuel Garci­a Valladolid <garcia_josval@gva.es>
  0 siblings, 1 reply; 7+ messages in thread

From: Jose Manuel Garci­a Valladolid @ 2006-09-21 10:40 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Jose Alarcon <alarcon_jos@gva.es>

Hello admins,

 I have a PostgreSQL 8.1.4 server under Linux Red Hat with several 
databases. The server is configured with WAL archiving turned on. As a 
maintenance process, every night one cron job launches a backup process 
to the server with
pg_dump, then the server shuts down and starts up and do a REINDEX 
DATABASE to all databases to keep all tables reindexed.

 After 30 days of no activity to the server (this is a DBA test server) 
the amount of WAL segments is increasing at very high speed. Every 
REINDEX process generates between 25 and 30 WAL segments. With this 
behavior (and no activity!!) I can not maintain this amount of data to 
performs future WAL backups and recovers.

 Any idea to decrease the number of WAL segments generated?
 Is possible to know when a table would be reindexed and to avoid dayly 
REINDEX process?

 Thank you.
 Jose Manuel García V.

Attachments:

garcia_josval.vcf (text/x-vcard, 449B) download begin:vcard fn;quoted-printable:Jose Manuel Garc=C3=ADa Valladolid n;quoted-printable:Garc=C3=ADa Valladolid;Jose Manuel org;quoted-printable;quoted-printable:Conseller=C3=ADa de Infraestructuras y Transportes;Servicio de Organizaci=C3=B3n e Inform=C3=A1tica adr;quoted-printable;dom:;;Av. Blasco Iba=C3=B1ez 50;Valencia;VALENCIA email;internet:garcia_josval@gva.es title:Analista x-mozilla-html:FALSE url:http://www.cit.gva.es version:2.1 end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: WAL configuration and REINDEX
@ 2006-09-22 02:50  Jim Nasby <jimn@enterprisedb.com>
  parent: Jose Manuel Garci­a Valladolid <garcia_josval@gva.es>
  0 siblings, 1 reply; 7+ messages in thread

From: Jim Nasby @ 2006-09-22 02:50 UTC (permalink / raw)
  To: garcia_josval@gva.es; +Cc: pgsql-admin@postgresql.org, Jose Alarcon <alarcon_jos@gva.es>

On Sep 21, 2006, at 6:40 AM, Jose Manuel Garci a Valladolid wrote:
> I have a PostgreSQL 8.1.4 server under Linux Red Hat with several  
> databases. The server is configured with WAL archiving turned on.  
> As a maintenance process, every night one cron job launches a  
> backup process to the server with
> pg_dump, then the server shuts down and starts up and do a REINDEX  
> DATABASE to all databases to keep all tables reindexed.
>
> After 30 days of no activity to the server (this is a DBA test  
> server) the amount of WAL segments is increasing at very high  
> speed. Every REINDEX process generates between 25 and 30 WAL  
> segments. With this behavior (and no activity!!) I can not maintain  
> this amount of data to performs future WAL backups and recovers.
>
> Any idea to decrease the number of WAL segments generated?
> Is possible to know when a table would be reindexed and to avoid  
> dayly REINDEX process?

I think you're running off some pretty old information.

First, you should only enable WAL archiving if you intend to use  
PITR. pg_dump and PITR are completely un-related. In fact, if you've  
never taken a base filesystem backup, all those old WAL files are  
completely useless.

Second, as long as you are vacuuming frequently enough, there's no  
reason you should ever have to REINDEX. You certainly shouldn't be  
doing it on a regular basis.
--
Jim Nasby                                    jimn@enterprisedb.com
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


--
Jim Nasby                                    jimn@enterprisedb.com
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: WAL configuration and REINDEX
@ 2006-09-24 22:23  adey <adey11@gmail.com>
  parent: Jim Nasby <jimn@enterprisedb.com>
  0 siblings, 1 reply; 7+ messages in thread

From: adey @ 2006-09-24 22:23 UTC (permalink / raw)
  To: Jim Nasby <jimn@enterprisedb.com>; +Cc: garcia_josval@gva.es, pgsql-admin@postgresql.org, "Jose Alarcon" <alarcon_jos@gva.es>

I apologise if I am uninformed, but it is my understanding that REINDEX is
still required even with the most regular of vacuums?

On 9/22/06, Jim Nasby <jimn@enterprisedb.com> wrote:
>
> On Sep 21, 2006, at 6:40 AM, Jose Manuel Garci a Valladolid wrote:
> > I have a PostgreSQL 8.1.4 server under Linux Red Hat with several
> > databases. The server is configured with WAL archiving turned on.
> > As a maintenance process, every night one cron job launches a
> > backup process to the server with
> > pg_dump, then the server shuts down and starts up and do a REINDEX
> > DATABASE to all databases to keep all tables reindexed.
> >
> > After 30 days of no activity to the server (this is a DBA test
> > server) the amount of WAL segments is increasing at very high
> > speed. Every REINDEX process generates between 25 and 30 WAL
> > segments. With this behavior (and no activity!!) I can not maintain
> > this amount of data to performs future WAL backups and recovers.
> >
> > Any idea to decrease the number of WAL segments generated?
> > Is possible to know when a table would be reindexed and to avoid
> > dayly REINDEX process?
>
> I think you're running off some pretty old information.
>
> First, you should only enable WAL archiving if you intend to use
> PITR. pg_dump and PITR are completely un-related. In fact, if you've
> never taken a base filesystem backup, all those old WAL files are
> completely useless.
>
> Second, as long as you are vacuuming frequently enough, there's no
> reason you should ever have to REINDEX. You certainly shouldn't be
> doing it on a regular basis.
> --
> Jim Nasby                                    jimn@enterprisedb.com
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
>
> --
> Jim Nasby                                    jimn@enterprisedb.com
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: WAL configuration and REINDEX
@ 2006-09-24 23:32  Tom Lane <tgl@sss.pgh.pa.us>
  parent: adey <adey11@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2006-09-24 23:32 UTC (permalink / raw)
  To: adey <adey11@gmail.com>; +Cc: Jim Nasby <jimn@enterprisedb.com>; garcia_josval@gva.es, pgsql-admin@postgresql.org, "Jose Alarcon" <alarcon_jos@gva.es>

adey <adey11@gmail.com> writes:
> I apologise if I am uninformed, but it is my understanding that REINDEX is
> still required even with the most regular of vacuums?

REINDEX shouldn't be required at all under normal circumstances.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: WAL configuration and REINDEX
@ 2006-09-25 03:19  adey <adey11@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: adey @ 2006-09-25 03:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Thanks Tom.
How does Postgres handle page splits and redirections in indexes then
please?
I had an unsuccessful look at :-
http://www.postgresql.org/docs/8.1/interactive/index-functions.html

and

http://www.postgresql.org/docs/8.1/interactive/indexes-types.html


On 9/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> adey <adey11@gmail.com> writes:
> > I apologise if I am uninformed, but it is my understanding that REINDEX
> is
> > still required even with the most regular of vacuums?
>
> REINDEX shouldn't be required at all under normal circumstances.
>
>                        regards, tom lane
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: WAL configuration and REINDEX
@ 2006-09-25 22:38  adey <adey11@gmail.com>
  parent: adey <adey11@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: adey @ 2006-09-25 22:38 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Hi Tom,
Further to my request below, the following recommendation exists in the
Postgres 8.1 documentation:-
 http://www.postgresql.org/docs/8.1/interactive/routine-reindex.html



On 9/25/06, adey <adey11@gmail.com> wrote:
>
> Thanks Tom.
> How does Postgres handle page splits and redirections in indexes then
> please?
> I had an unsuccessful look at :-
> http://www.postgresql.org/docs/8.1/interactive/index-functions.html
>
> and
>
> http://www.postgresql.org/docs/8.1/interactive/indexes-types.html
>
>
>  On 9/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > adey <adey11@gmail.com> writes:
> > > I apologise if I am uninformed, but it is my understanding that
> > REINDEX is
> > > still required even with the most regular of vacuums?
> >
> > REINDEX shouldn't be required at all under normal circumstances.
> >
> >                        regards, tom lane
> >
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: WAL configuration and REINDEX
@ 2006-09-26 15:02  Alvaro Herrera <alvherre@commandprompt.com>
  parent: adey <adey11@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Alvaro Herrera @ 2006-09-26 15:02 UTC (permalink / raw)
  To: adey <adey11@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

adey wrote:
> Hi Tom,
> Further to my request below, the following recommendation exists in the
> Postgres 8.1 documentation:-
> http://www.postgresql.org/docs/8.1/interactive/routine-reindex.html

Did you actually read that page?  It says that there are some
circumstances under which reindex is still required, but they should be
rare.

It doesn't have anything to do with page splits though.  The problem is
with page merging not being implemented (contiguous partially-filled
pages are not merged)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2006-09-26 15:02 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-21 10:40 WAL configuration and REINDEX Jose Manuel Garci­a Valladolid <garcia_josval@gva.es>
2006-09-22 02:50 ` Jim Nasby <jimn@enterprisedb.com>
2006-09-24 22:23   ` adey <adey11@gmail.com>
2006-09-24 23:32     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-25 03:19       ` adey <adey11@gmail.com>
2006-09-25 22:38         ` adey <adey11@gmail.com>
2006-09-26 15:02           ` Alvaro Herrera <alvherre@commandprompt.com>

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