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
