pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedREINDEX DATABASE
11+ messages / 7 participants
[nested] [flat]
* REINDEX DATABASE
@ 2005-07-26 07:41 Eugene E. <sad@bankir.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Eugene E. @ 2005-07-26 07:41 UTC (permalink / raw)
To: pgsql-sql
Hello
Would you like to advice to use REINDEX DATABASE on regular basis ?
if (yes)
how it should be connected with VACUUM FULL ANALYZE which is run
regularly ? (reindex before vacuum or vacuum before reindex?)
else
haw to determine _when_ to run REINDEX ?
many thanks
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-07-26 13:19 Christopher Browne <cbbrowne@acm.org>
parent: Eugene E. <sad@bankir.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Christopher Browne @ 2005-07-26 13:19 UTC (permalink / raw)
To: pgsql-sql
> Hello
>
> Would you like to advice to use REINDEX DATABASE on regular basis ?
>
> if (yes)
> how it should be connected with VACUUM FULL ANALYZE which is run
> regularly ? (reindex before vacuum or vacuum before reindex?)
>
> else
> haw to determine _when_ to run REINDEX ?
If you are doing ordinary VACUUM ANALYZE frequently enough, it
shouldn't be necessary to either VACUUM FULL or REINDEX.
Back in the 7.2 days, there were sorts of update patterns that would
mandate reindexing every so often, as you could get cases where index
pages would be very sparsely populated. That was alleviated in
version 7.3, I believe, and was clearly evident in 7.4.
You know you need to REINDEX if analysis of an index shows that it is
sparsely populated. This generally shows up if you do an analyze on
the table and find an index has more pages than tuples.
But if you run VACUUM reasonably frequently, this shouldn't be
necessary...
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/rdbms.html
Rules of the Evil Overlord #153. "My Legions of Terror will be an
equal-opportunity employer. Conversely, when it is prophesied that no
man can defeat me, I will keep in mind the increasing number of
non-traditional gender roles." <http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-07-26 14:17 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2005-07-26 14:17 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
O Christopher Browne Ýãñáøå óôéò Jul 26, 2005 :
> > Hello
> >
> > Would you like to advice to use REINDEX DATABASE on regular basis ?
> >
> > if (yes)
> > how it should be connected with VACUUM FULL ANALYZE which is run
> > regularly ? (reindex before vacuum or vacuum before reindex?)
> >
> > else
> > haw to determine _when_ to run REINDEX ?
>
> If you are doing ordinary VACUUM ANALYZE frequently enough, it
> shouldn't be necessary to either VACUUM FULL or REINDEX.
>
> Back in the 7.2 days, there were sorts of update patterns that would
> mandate reindexing every so often, as you could get cases where index
> pages would be very sparsely populated. That was alleviated in
> version 7.3, I believe, and was clearly evident in 7.4.
>
> You know you need to REINDEX if analysis of an index shows that it is
> sparsely populated. This generally shows up if you do an analyze on
> the table and find an index has more pages than tuples.
>
> But if you run VACUUM reasonably frequently, this shouldn't be
> necessary...
I vacuum analyze every hour, however after reindexdb
my (currently) 2.47 GB db is reduced to 2.37 GB,
thus helping both db-wise and freebsd_cache-wise boost performance.
All the above in 7.4.6.
I am not arguing that i have done detailed analysis of the
situation, i'm just saying that i have witnessed a performance gain after
running contrib/reindexdb every month (or so).
>
--
-Achilleus
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-07-26 17:14 Chris Browne <cbbrowne@acm.org>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 11+ messages in thread
From: Chris Browne @ 2005-07-26 17:14 UTC (permalink / raw)
To: pgsql-sql
achill@matrix.gatewaynet.com (Achilleus Mantzios) writes:
> O Christopher Browne Ýãñáøå óôéò Jul 26, 2005 :
>
>> > Hello
>> >
>> > Would you like to advice to use REINDEX DATABASE on regular basis ?
>> >
>> > if (yes)
>> > how it should be connected with VACUUM FULL ANALYZE which is run
>> > regularly ? (reindex before vacuum or vacuum before reindex?)
>> >
>> > else
>> > haw to determine _when_ to run REINDEX ?
>>
>> If you are doing ordinary VACUUM ANALYZE frequently enough, it
>> shouldn't be necessary to either VACUUM FULL or REINDEX.
>>
>> Back in the 7.2 days, there were sorts of update patterns that would
>> mandate reindexing every so often, as you could get cases where index
>> pages would be very sparsely populated. That was alleviated in
>> version 7.3, I believe, and was clearly evident in 7.4.
>>
>> You know you need to REINDEX if analysis of an index shows that it is
>> sparsely populated. This generally shows up if you do an analyze on
>> the table and find an index has more pages than tuples.
>>
>> But if you run VACUUM reasonably frequently, this shouldn't be
>> necessary...
>
> I vacuum analyze every hour, however after reindexdb
> my (currently) 2.47 GB db is reduced to 2.37 GB,
> thus helping both db-wise and freebsd_cache-wise boost performance.
>
> All the above in 7.4.6.
>
> I am not arguing that i have done detailed analysis of the
> situation, i'm just saying that i have witnessed a performance gain after
> running contrib/reindexdb every month (or so).
That doesn't strike me as being a material improvement, and it comes
at a pretty high cost.
You can get a savings of about 4% of the space, but at the cost of
taking an appreciable outage during which the database is not usable.
I wouldn't expect the 4% savings in space to lead to a particularly
measurable improvement in performance, certainly not one worth the
outage.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-07-27 06:08 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2005-07-27 06:08 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
O Chris Browne Ýãñáøå óôéò Jul 26, 2005 :
> achill@matrix.gatewaynet.com (Achilleus Mantzios) writes:
> > O Christopher Browne Ýãñáøå óôéò Jul 26, 2005 :
> >
> >> > Hello
> >> >
> >> > Would you like to advice to use REINDEX DATABASE on regular basis ?
> >> >
> >> > if (yes)
> >> > how it should be connected with VACUUM FULL ANALYZE which is run
> >> > regularly ? (reindex before vacuum or vacuum before reindex?)
> >> >
> >> > else
> >> > haw to determine _when_ to run REINDEX ?
> >>
> >> If you are doing ordinary VACUUM ANALYZE frequently enough, it
> >> shouldn't be necessary to either VACUUM FULL or REINDEX.
> >>
> >> Back in the 7.2 days, there were sorts of update patterns that would
> >> mandate reindexing every so often, as you could get cases where index
> >> pages would be very sparsely populated. That was alleviated in
> >> version 7.3, I believe, and was clearly evident in 7.4.
> >>
> >> You know you need to REINDEX if analysis of an index shows that it is
> >> sparsely populated. This generally shows up if you do an analyze on
> >> the table and find an index has more pages than tuples.
> >>
> >> But if you run VACUUM reasonably frequently, this shouldn't be
> >> necessary...
> >
> > I vacuum analyze every hour, however after reindexdb
> > my (currently) 2.47 GB db is reduced to 2.37 GB,
> > thus helping both db-wise and freebsd_cache-wise boost performance.
> >
> > All the above in 7.4.6.
> >
> > I am not arguing that i have done detailed analysis of the
> > situation, i'm just saying that i have witnessed a performance gain after
> > running contrib/reindexdb every month (or so).
>
> That doesn't strike me as being a material improvement, and it comes
> at a pretty high cost.
The numbers above are just the numbers reported after doing
a live reindex on the spot after deciding to answer to this email.
In the past (when the db was smaller) and for the same version,
i have come across much "striking" numbers,
and much more striking boost gain.
(e.g. a 2+ GB db directory turning to a ~1.5 GB dir).
BTW whats the limit of kern.ipc.shmmax for 32-bit intel?
~ 2^31 = ~ 2 GB.
In those cases i noticed severe boost gain.
(And of course the 1 hour vacuum analyze schedule was always there)
>
> You can get a savings of about 4% of the space, but at the cost of
> taking an appreciable outage during which the database is not usable.
1st not all database is unusable, during the whole reindexdb run,
2nd outage outside office hours is acceptable for those who apply.
>
> I wouldn't expect the 4% savings in space to lead to a particularly
> measurable improvement in performance, certainly not one worth the
> outage.
>
I am just saying that the common saying "reindex is not needed for 7.4+"
maybe is not true in all circumstances.
--
-Achilleus
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-07-27 15:31 Chris Browne <cbbrowne@acm.org>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 11+ messages in thread
From: Chris Browne @ 2005-07-27 15:31 UTC (permalink / raw)
To: pgsql-sql
achill@matrix.gatewaynet.com (Achilleus Mantzios) writes:
> O Chris Browne Ýãñáøå óôéò Jul 26, 2005 :
>> You can get a savings of about 4% of the space, but at the cost of
>> taking an appreciable outage during which the database is not usable.
>
> 1st not all database is unusable, during the whole reindexdb run,
> 2nd outage outside office hours is acceptable for those who apply.
I'm not willing to assume that.
I help support some applications where "outages outside office hours"
are acceptable; I help support some other applications for which
"office hours" are 24 hours per day, 7 days per week, and such an
outage would be deemed decidedly unacceptable.
>> I wouldn't expect the 4% savings in space to lead to a particularly
>> measurable improvement in performance, certainly not one worth the
>> outage.
> I am just saying that the common saying "reindex is not needed for
> 7.4+" maybe is not true in all circumstances.
Let me quote the release notes for 7.4.1:
"Make free space map efficiently reuse empty index pages, and other
free space management improvements
In previous releases, B-tree index pages that were left empty
because of deleted rows could only be reused by rows with index
values similar to the rows originally indexed on that page. In
7.4, VACUUM records empty index pages and allows them to be reused
for any future index rows."
<http://www.postgresql.at/pg/pgsql/doc/html/release-7-4.html;
In versions earlier than 7.4, running a REINDEX periodically was
*essential* if you had update patterns consistent with the (remarkably
common) scenario described above.
This reason to reindex (which was the main reason we required
reindexing when using 7.2) has been resolved and gone away in 7.4.
There may be other factors that could mandate REINDEX; as far as I can
tell, the main such factor that remains would be where a table sees
enormous numbers of updates but is not VACUUMed often enough.
_That_ scenario isn't consistent with what you describe, as it would
be expected to involve a whole lot more than 4% growth in the size of
the database.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-07-28 05:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2005-07-28 05:08 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
Chris Browne <cbbrowne@acm.org> writes:
> achill@matrix.gatewaynet.com (Achilleus Mantzios) writes:
>> I am just saying that the common saying "reindex is not needed for
>> 7.4+" maybe is not true in all circumstances.
> ...
> In versions earlier than 7.4, running a REINDEX periodically was
> *essential* if you had update patterns consistent with the (remarkably
> common) scenario described above.
> This reason to reindex (which was the main reason we required
> reindexing when using 7.2) has been resolved and gone away in 7.4.
> There may be other factors that could mandate REINDEX; as far as I can
> tell, the main such factor that remains would be where a table sees
> enormous numbers of updates but is not VACUUMed often enough.
I think the case that 7.4 resolved is where you have a
continually-moving window of index values; for example, an index on a
timestamp column in a table where you delete entries older than 30 days.
Before 7.4, index pages for timestamps older than 30 days would become
empty and then just sit there, with no other way to reclaim them than
REINDEX.
The case that isn't resolved yet is where you have a usage pattern that
causes a lot of index pages to become mostly but not entirely empty.
For example, your entries are timestamps, and you have a cleanup process
that removes just 99 out of every 100 successive entries. This'll leave
you with just a couple of index entries per page, which might not be
infinite bloat but it's surely not too efficient.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-07-28 06:32 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Chris Browne <cbbrowne@acm.org>
1 sibling, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2005-07-28 06:32 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
O Chris Browne Ýãñáøå óôéò Jul 27, 2005 :
> achill@matrix.gatewaynet.com (Achilleus Mantzios) writes:
> > O Chris Browne Ýãñáøå óôéò Jul 26, 2005 :
> >> You can get a savings of about 4% of the space, but at the cost of
> >> taking an appreciable outage during which the database is not usable.
> >
> > 1st not all database is unusable, during the whole reindexdb run,
> > 2nd outage outside office hours is acceptable for those who apply.
>
> I'm not willing to assume that.
>
> I help support some applications where "outages outside office hours"
> are acceptable; I help support some other applications for which
> "office hours" are 24 hours per day, 7 days per week, and such an
> outage would be deemed decidedly unacceptable.
>
> >> I wouldn't expect the 4% savings in space to lead to a particularly
> >> measurable improvement in performance, certainly not one worth the
> >> outage.
>
> > I am just saying that the common saying "reindex is not needed for
> > 7.4+" maybe is not true in all circumstances.
>
> Let me quote the release notes for 7.4.1:
>
> "Make free space map efficiently reuse empty index pages, and other
> free space management improvements
>
> In previous releases, B-tree index pages that were left empty
> because of deleted rows could only be reused by rows with index
> values similar to the rows originally indexed on that page. In
> 7.4, VACUUM records empty index pages and allows them to be reused
> for any future index rows."
>
> <http://www.postgresql.at/pg/pgsql/doc/html/release-7-4.html;
>
> In versions earlier than 7.4, running a REINDEX periodically was
> *essential* if you had update patterns consistent with the (remarkably
> common) scenario described above.
>
> This reason to reindex (which was the main reason we required
> reindexing when using 7.2) has been resolved and gone away in 7.4.
>
> There may be other factors that could mandate REINDEX; as far as I can
> tell, the main such factor that remains would be where a table sees
> enormous numbers of updates but is not VACUUMed often enough.
I'll try to postpone the next reindexdb at the end of august,
and get some numbers then.
However the big difference in performance as i told was near the 2GB
"threshold", and at *that* point (and maybe for different reasons)
performance gain was remarkable.
>
> _That_ scenario isn't consistent with what you describe, as it would
> be expected to involve a whole lot more than 4% growth in the size of
> the database.
>
--
-Achilleus
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: REINDEX DATABASE
@ 2005-08-30 11:53 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 11+ messages in thread
From: Achilleus Mantzios @ 2005-08-30 11:53 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
O Achilleus Mantzios Ýãñáøå óôéò Jul 28, 2005 :
> O Chris Browne Ýãñáøå óôéò Jul 27, 2005 :
>
>
> I'll try to postpone the next reindexdb at the end of august,
> and get some numbers then.
> However the big difference in performance as i told was near the 2GB
> "threshold", and at *that* point (and maybe for different reasons)
> performance gain was remarkable.
>
Well, today i run reindexdb and i noticed a decrease in database size,
from 2890148K to 2527552K, about
12.54% decrease in size.
However i have not an estimation of the distribution
of the SQL commands (INSERT,UPDATE,DELETE) over August.
I am running 7.4.6.
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 11+ messages in thread
* reindex database
@ 2007-03-14 12:44 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 11+ messages in thread
From: Sabin Coanda @ 2007-03-14 12:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
It would be so nice to use REINDEX DATABASE command without name, for the
current database.
Now the command is useless when a database schema script refers just itself,
and the database name is not establish. I have to use REINDEX TABLE name
instead searching all the database tables.
What is your opinion ?
Regards,
Sabin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: reindex database
@ 2007-03-14 13:12 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 0 replies; 11+ messages in thread
From: Achilleas Mantzios @ 2007-03-14 13:12 UTC (permalink / raw)
To: pgsql-sql
Óôéò ÔåôÜñôç 14 ÌÜñôéïò 2007 14:44, ï/ç Sabin Coanda Ýãñáøå:
> Hi,
>
> It would be so nice to use REINDEX DATABASE command without name, for the
> current database.
> Now the command is useless when a database schema script refers just
> itself, and the database name is not establish. I have to use REINDEX TABLE
> name instead searching all the database tables.
>
> What is your opinion ?
<path to your db sources>/contrib/reindexdb/reindexdb -a
reindexes everything.
Also your script would do a
#!/bin/csh
set mydb=`psql -q -t -c "SELECT current_database()" | tr -d " \n"`
<path to your db sources>/contrib/reindexdb/reindexdb -d $mydb
>
> Regards,
> Sabin
>
>
>
> ---------------------------(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
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-03-14 13:12 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-26 07:41 REINDEX DATABASE Eugene E. <sad@bankir.ru>
2005-07-26 13:19 ` Christopher Browne <cbbrowne@acm.org>
2005-07-26 14:17 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-07-26 17:14 ` Chris Browne <cbbrowne@acm.org>
2005-07-27 06:08 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-07-27 15:31 ` Chris Browne <cbbrowne@acm.org>
2005-07-28 05:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-28 06:32 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-08-30 11:53 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2007-03-14 12:44 reindex database Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-03-14 13:12 ` Re: reindex database Achilleas Mantzios <achill@matrix.gatewaynet.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