Обсуждение: index keeps on growing

Поиск
Список
Период
Сортировка

index keeps on growing

От
Jean-Christophe ARNU (JX)
Дата:
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

Re: index keeps on growing

От
Brian McCane
Дата:
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"


Re: index keeps on growing

От
Tom Lane
Дата:
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

Re: index keeps on growing

От
Brian McCane
Дата:
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"


Re: index keeps on growing

От
Tom Lane
Дата:
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

Re: index keeps on growing

От
Marc Spitzer
Дата:
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

Re: index keeps on growing

От
Tom Lane
Дата:
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

Re: index keeps on growing

От
Brian McCane
Дата:
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"