Обсуждение: index keeps on growing
Hello all,
I've a hard working pgsql db running on 7/7x365 server. Each night
(when
activity is less intensive) I run vacuum and vacuum analyze on tables where
principal activity is performed.
Tables are cleaned (files sizes are decreasing), but indices files
keeps on
growing in size....
I run PgSQL 7.1.3, I cannot upgrade (immediately) to 7.2.x... So what
can I
do to keep these indices' size at a constant and reasonnable size?
Thanks a lot
--
Jean-Christophe ARNU
s/w developer
Paratronic France
I have a perl script which rebuilds index files "hot". It looks up the pertinent information about the index you specify on a table or for ALL indexes for a table. It then builds a new index exactly like the one you specify named "<index>_new". After the index has been created, it removes the original index and renames <index>_new to <index>. Just so noone thinks I am reckless, the perl script actually just creates a SQL script of what work is to be done, and then the user has to feed the commands to 'psql'. I always run the program once and look at the script that it wants to use. Then, if I like what I see, I run the program again and pipe the output to 'psql'. I don't care how good the program is, I prefer a manual inspection before I allow it to mess with my database. The perl script will allow you to rebuild your indexes (indices?) without having to kick out all of your users. It reclaims all of the "holes" in the index files. It does NOT preserve the "primary key" setting for an index, because as far as I can tell this doesn't actually make any difference, primary keys are just an UNIQUE index. I have used it since 7.0.3, and it has worked fine for me, but I guarantee nothing about your database (lawyers told me I have to say that ;). The only caveat I can think of is that it places a read lock on the table, so while you are rebuilding the index noone can write to the table (I am not sure on this might wanna ask Tom). I will put this and a couple of other scripts that I have written for maintenance on PostgreSQL on my anonymous FTP server. They will be at: ftp://china.maxbaud.net/pub/PostgreSQL/ BTW, the script also fixes triggers. I had a problem once upon a time where I did some tinkering with tables and their names, and when I was done my triggers no longer pointed at the correct table. The name in the trigger definition was correct, but the oid (or whatever the field in pg_trigger is), pointed to the old database oid. So, the triggers all failed to run. I don't know if this is still possible, but it will still fix it. - brian On Wed, 19 Jun 2002, Jean-Christophe ARNU wrote: > > Hello all, > I've a hard working pgsql db running on 7/7x365 server. Each night > (when > activity is less intensive) I run vacuum and vacuum analyze on tables where > principal activity is performed. > Tables are cleaned (files sizes are decreasing), but indices files > keeps on > growing in size.... > > I run PgSQL 7.1.3, I cannot upgrade (immediately) to 7.2.x... So what > can I > do to keep these indices' size at a constant and reasonnable size? > > Thanks a lot > > > -- > Jean-Christophe ARNU > s/w developer > Paratronic France > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 > Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
Brian McCane <bmccane@mccons.net> writes:
> The perl script will allow you to rebuild your indexes (indices?) without
> having to kick out all of your users. It reclaims all of the "holes" in
> the index files. It does NOT preserve the "primary key" setting for an
> index, because as far as I can tell this doesn't actually make any
> difference, primary keys are just an UNIQUE index.
The primary-key marker does actually make a difference in just one case
(AFAIK): when you create a foreign key reference from another table,
the primary key serves to identify the default columns to reference.
If you drop the marker then subsequent attempts to create referencing
columns will have to explicitly identify the referenced columns.
regards, tom lane
Okay, thanks for that information. I will have to dig through my pgsql-admin archive here and find the code for setting the primary key flag. Unless of course someone would like to offer me the information *HINT* :). After I get the information, I will make the appropriate changes to my script and place them up on the ftp server. - brian On Wed, 19 Jun 2002, Tom Lane wrote: > > Brian McCane <bmccane@mccons.net> writes: > > The perl script will allow you to rebuild your indexes (indices?) without > > having to kick out all of your users. It reclaims all of the "holes" in > > the index files. It does NOT preserve the "primary key" setting for an > > index, because as far as I can tell this doesn't actually make any > > difference, primary keys are just an UNIQUE index. > > The primary-key marker does actually make a difference in just one case > (AFAIK): when you create a foreign key reference from another table, > the primary key serves to identify the default columns to reference. > If you drop the marker then subsequent attempts to create referencing > columns will have to explicitly identify the referenced columns. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
Brian McCane <bmccane@mccons.net> writes:
> Okay, thanks for that information. I will have to dig through my
> pgsql-admin archive here and find the code for setting the primary key
> flag. Unless of course someone would like to offer me the information
> *HINT* :).
If you'd like to be backwards-compatible with older backends, I think
you need to reach in and set the indisprimary field of the pg_index
row for the index.
As of 7.2 or so there is an ALTER TABLE ADD PRIMARY KEY syntax that you
could use instead of creating the index directly. This would be better
since it doesn't require superuser privileges...
regards, tom lane
On Wed, Jun 19, 2002 at 09:09:00AM +0000, Jean-Christophe ARNU wrote: > Hello all, > I've a hard working pgsql db running on 7/7x365 server. Each night > (when > activity is less intensive) I run vacuum and vacuum analyze on tables where > principal activity is performed. > Tables are cleaned (files sizes are decreasing), but indices files > keeps on > growing in size.... > > I run PgSQL 7.1.3, I cannot upgrade (immediately) to 7.2.x... So what > can I > do to keep these indices' size at a constant and reasonnable size? > > Thanks a lot As far as I know there are 2 ways to do this: 1: the command "reindex table_name" to rebuild all your indexes on a table, I think it locks the table 2: drop and recreate the indexes by hand marc ps the first time you reindex it can take a long time. > > > -- > Jean-Christophe ARNU > s/w developer > Paratronic France > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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
Brian McCane <bmccane@mccons.net> writes:
> I assume that if I do it in a BEGIN..COMMIT block I won't lose
> anything, but I am not sure if a "DROP INDEX" can be rolled back.
DROP INDEX can be rolled back in the same releases that allow DROP
TABLE to be rolled back. I think we allowed that beginning in
7.0, but check the release notes.
So basically you'd want
BEGIN;
DROP INDEX foo;
either CREATE INDEX ...
or ALTER TABLE ADD PRIMARY KEY ...;
COMMIT;
Note this will imply peak disk usage equal to size of old index
plus size of new, since the old file can't physically be removed
till commit.
regards, tom lane
Since BEGIN/COMMIT should work, that would give the original poster what they wanted (they have 7.1.3). Usually on a fairly active database (like mine) running my 'fixtable.pl' script gives me back around 10-20% of my disk space (and improves my performance for about a week). The longer it has been, the greater the return on disk space. However, I do around 300,000 insert and 100,000 updates a day in one of my tables. When I get to the end of the internet I'll let everyone know ;). The biggest problem that I have with PostgreSQL nowadays is that I have my files spread across 6 separate SCSI "drives" attached to my database server (a couple are 36GB RAID 5+0). When I run this script on my largest table I need about 20GB of free space in my PGDATA directory for the index files that get created during the recreate and don't end up where they used to be. Also, the symlinks get removed by the "DROP INDEX" call, but the actual file is still out on the drive where I keep them and I have to remove them manually, which is very nerve wracking. I have now started doing that table 1 index at a time, then I use my 'moveindex.pl' script to put the index back where it came from :). Incidentally, how long has the "pg_indexes" view existed? I didn't find it when I originally started writing this script back on 7.0.x. I am rewriting my script to use the view instead of 'pg_dump' which seems much cleaner to me. Finally, is there something like 'pg_indexes' for triggers? It would be nice to not have to call pg_dump at all, but I don't want to try and figure out the query needed to generate the 'CREATE TRIGGER' rules. necessity is a mother, - brian On Wed, 19 Jun 2002, Tom Lane wrote: > > Brian McCane <bmccane@mccons.net> writes: > > I assume that if I do it in a BEGIN..COMMIT block I won't lose > > anything, but I am not sure if a "DROP INDEX" can be rolled back. > > DROP INDEX can be rolled back in the same releases that allow DROP > TABLE to be rolled back. I think we allowed that beginning in > 7.0, but check the release notes. > > So basically you'd want > > BEGIN; > DROP INDEX foo; > either CREATE INDEX ... > or ALTER TABLE ADD PRIMARY KEY ...; > COMMIT; > > Note this will imply peak disk usage equal to size of old index > plus size of new, since the old file can't physically be removed > till commit. > > regards, tom lane > Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"