VACUUM and ANALYZE With Empty Tables
We use a development environment that works with Postgres via ODBC and
uses cursors to insert and update rows in Postgres tables. I'm using
Postgres version 7.4.5.
I have a test program that reads 34,000 rows from an external file and
inserts them into a Postgres table. Under normal circumstances, it
takes about 1 minute to complete the test. In troubleshooting a
performance problem, I have discovered the following:
A. If I TRUNCATE or DELETE all of the rows in the table and then run
VACUUM or ANALYZE on the empty table, the test program takes over 15
minutes to complete (i.e., 15X performance drop).
B. If I drop and create the table without using VACUUM or ANALYZE, it
takes about 1 minute to complete the test program.
C. If I insert 94 or more rows into the table and then run VACUUM or
ANALYZE, it takes 1 minute to complete the test program.
D. If I insert 93 or fewer rows into the table and then run VACUUM or
ANALYZE, it takes over 15 minutes to complete the test.
D. If the test program is running slowly, I can speed it up to normal
speed by running ANALYZE <table> from another database session.
My concern about this is as follows. Our application uses a number of
"work" tables that will have many rows inserted into them during a
process (e.g., a user running a report) and then the rows will be
deleted once the process is over. (I don't think we can use TEMPORARY
tables because the tables need to be seen outside of the current
database session.)
If we routinely run VACUUM or VACUUM ANALYZE (e.g., nightly), these work
tables will normally be empty when the VACUUM is run. So it would
appear from the testing above that they will experience performance
problems when inserting large numbers of rows through our application.
Is there some easy way around this problem? If there a way to force
VACUUM or ANALYZE to optimize for a set number of rows even if the table
is empty when it is run? Thanks for your help. Mark
Mark Dexter wrote:
We use a development environment that works with Postgres via ODBC and
uses cursors to insert and update rows in Postgres tables. I'm using
Postgres version 7.4.5.
A. If I TRUNCATE or DELETE all of the rows in the table and then run
VACUUM or ANALYZE on the empty table, the test program takes over 15
minutes to complete (i.e., 15X performance drop).
If we routinely run VACUUM or VACUUM ANALYZE (e.g., nightly), these work
tables will normally be empty when the VACUUM is run. So it would
appear from the testing above that they will experience performance
problems when inserting large numbers of rows through our application.
Yep - it's a known issue. The analyse is doing what you asked, it's just
not what you want.
Is there some easy way around this problem? If there a way to force
VACUUM or ANALYZE to optimize for a set number of rows even if the table
is empty when it is run? Thanks for your help. Mark
There are only two options I know of:
1. Vaccum analyse each table separately (tedious, I know)
2. Try pg_autovacuum in the contrib/ directory
The autovacuum utility monitors activity for you and targets tables when
they've seen a certain amount of activity. Even if it hasn't got the
tunability you need, it should be a simple patch to add a list of
"excluded" tables.
--
Richard Huxton
Archonet Ltd
Thanks very much for the information. It would appear that our best option might be to vacuum analyze these tables in our application at a point in time when they contain rows instead of doing it at night. Needlesst to say, it would nice to have an option to analyze with a target number of rows instead of the number presently in the table.
I suppose another option would be to keep a small number of rows permanently in these tables. In my testing, 100 rows (94 to be exact) did the trick. Is this number going to vary from table to table?
Thanks again for your help. Mark
________________________________
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wed 11/24/2004 1:26 AM
To: Mark Dexter
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] VACUUM and ANALYZE With Empty Tables
Mark Dexter wrote:
We use a development environment that works with Postgres via ODBC and
uses cursors to insert and update rows in Postgres tables. I'm using
Postgres version 7.4.5.
A. If I TRUNCATE or DELETE all of the rows in the table and then run
VACUUM or ANALYZE on the empty table, the test program takes over 15
minutes to complete (i.e., 15X performance drop).
If we routinely run VACUUM or VACUUM ANALYZE (e.g., nightly), these work
tables will normally be empty when the VACUUM is run. So it would
appear from the testing above that they will experience performance
problems when inserting large numbers of rows through our application.
Yep - it's a known issue. The analyse is doing what you asked, it's just
not what you want.
Is there some easy way around this problem? If there a way to force
VACUUM or ANALYZE to optimize for a set number of rows even if the table
is empty when it is run? Thanks for your help. Mark
There are only two options I know of:
1. Vaccum analyse each table separately (tedious, I know)
2. Try pg_autovacuum in the contrib/ directory
The autovacuum utility monitors activity for you and targets tables when
they've seen a certain amount of activity. Even if it hasn't got the
tunability you need, it should be a simple patch to add a list of
"excluded" tables.
--
Richard Huxton
Archonet Ltd
Import Notes
Resolved by subject fallback
From: "Mark Dexter" <MDEXTER@dexterchaney.com>
Thanks very much for the information. It would appear that our best
option might be to vacuum analyze these tables in our
application at a point in time when they contain rows instead of doing it
at night. Needlesst to say, it would nice to have > an option to analyze
with a target number of rows instead of the number presently in the table.
I suppose another option would be to keep a small number of rows
permanently in these tables. In my testing, 100 rows (94 to > be exact) did
the trick. Is this number going to vary from table to table?
or, you could add this procedure to your nightly vacuum job:
(after regular vacuum analyzes)
insert a representative dummy row set into the empty table
analyze the table
remove the rows again
this way the dummy rows wont interfere with your regular operations.
if the table is not always empty at vacuum time, you need to be
able to differentiate the dummy rows from the regular ones to be able
to remove only the dummy ones, of course.
gnari