Обсуждение: VACUUM ANALYZE

Hi

I have a postgresql database with about 5 tables. On a nightly basis, all
the records in all 5 tables are deleted and repopulated with fresh data.
Some of the tables have between 30 000 and 80 000 records. I have been
looking through the docs to try and work out how best to optimize the
database as I have noticed a definite lag in response as the database grows.

1. I have placed indexes on the columns that are used in searching
2. when deleting all the records from a table I use TRUNCATE instead of
DELETE
3. the repopulation occurs via a perl script and at the end of the script I
run VACUUM ANALYZE on the database.

Any other things I should look at? Also, is there a way to check when the
last time VACUUM ANALYZE was run on the db. I am unsure whether the perl
script is running the command successfully.

Thanks
Dave

On Mon, Jan 14, 2002 at 04:25:12PM +0200, David Anthony wrote:
> Hi
>
> I have a postgresql database with about 5 tables. On a nightly basis, all
> the records in all 5 tables are deleted and repopulated with fresh data.
> Some of the tables have between 30 000 and 80 000 records. I have been
> looking through the docs to try and work out how best to optimize the
> database as I have noticed a definite lag in response as the database grows.
>
> 1. I have placed indexes on the columns that are used in searching
> 2. when deleting all the records from a table I use TRUNCATE instead of
> DELETE
> 3. the repopulation occurs via a perl script and at the end of the script I
> run VACUUM ANALYZE on the database.
>
> Any other things I should look at? Also, is there a way to check when the
> last time VACUUM ANALYZE was run on the db. I am unsure whether the perl
> script is running the command successfully.
>

Well one thing to keep in mind is that pg 7.1.3 and befor does not
automaticly reclaim index space that is no longer in use.  one way
to handle this is to use the reindex command on each table every so
often, daily, weekly, monthly to get your disk back and speed up
searches.  Another way to do it is as part of your data import
drop and recreate the database, you delete all records anyway.

The reindexing command is reindex:

\dstb_tracking=# \h reindex
Command:     REINDEX
Description: Recover corrupted system indexes under stand-alone Postgres
Syntax:
REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

good luck

marc

> Thanks
> Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

One thing you might want to look at, if you're using the DBI module with
Perl, is:

DBI->trace(2, '/tmp/dbitrace.out');


This will tell you for sure if your vacuum analyze is being run.  Careful
though, you'll want to check the usage to get the desired about of
verbosity.  2, is the most I believe.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Marc Spitzer
Sent: Monday, January 14, 2002 11:46 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] VACUUM ANALYZE


On Mon, Jan 14, 2002 at 04:25:12PM +0200, David Anthony wrote:
> Hi
>
> I have a postgresql database with about 5 tables. On a nightly basis,
all
> the records in all 5 tables are deleted and repopulated with fresh data.
> Some of the tables have between 30 000 and 80 000 records. I have been
> looking through the docs to try and work out how best to optimize the
> database as I have noticed a definite lag in response as the database
grows.
>
> 1. I have placed indexes on the columns that are used in searching
> 2. when deleting all the records from a table I use TRUNCATE instead of
> DELETE
> 3. the repopulation occurs via a perl script and at the end of the
script I
> run VACUUM ANALYZE on the database.
>
> Any other things I should look at? Also, is there a way to check when
the
> last time VACUUM ANALYZE was run on the db. I am unsure whether the perl
> script is running the command successfully.
>

Well one thing to keep in mind is that pg 7.1.3 and befor does not
automaticly reclaim index space that is no longer in use.  one way
to handle this is to use the reindex command on each table every so
often, daily, weekly, monthly to get your disk back and speed up
searches.  Another way to do it is as part of your data import
drop and recreate the database, you delete all records anyway.

The reindexing command is reindex:

\dstb_tracking=# \h reindex
Command:     REINDEX
Description: Recover corrupted system indexes under stand-alone Postgres
Syntax:
REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

good luck

marc

> Thanks
> Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

David Anthony <DavidA@tradeworld.net> writes:
> Any other things I should look at?

Are your queries actually using the indexes?  See EXPLAIN.

> Also, is there a way to check when the
> last time VACUUM ANALYZE was run on the db. I am unsure whether the perl
> script is running the command successfully.

Look to see if there's up-to-date info in pg_statistic for the table,
eg with

select attname,attdispersion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'WHATEVER';

This isn't timestamped in any way, but you could try doing a manual
"delete from pg_statistic" and then see if your script repopulates it.

            regards, tom lane