Thread: WAL configuration and REINDEX
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.
Attachment
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)
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
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
Thanks Tom.
How does Postgres handle page splits and redirections in indexes then please?
I had an unsuccessful look at :-
and
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
Hi Tom,
Further to my request below, the following recommendation exists in the Postgres 8.1 documentation:-
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 :-andOn 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
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.