CODEWITHSUNDEEP
.netpostgresql
Ian
Ian

Reputation: 67

Vacuum analyze is running very slow and causes timeouts for other systems

I'm running PostgresSQL 8.3.3. Every night a vacuum analyze is performed against the database. Every table takes around 5 minutes to complete (large or small). Initially the process took 1/3 of the time it does now. While the vacuum is performed other data is still being inserted through a .net windows service. This service started logging connection timeouts recently.

Is there a way of figuring out what is causing the slow running of the vacuum analyze and/or what is causing the timeouts? I suspect they are related.

Upvotes: 3

Views: 4101

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

Well, version 8.3 is end-of-life, so you'll want to upgrade as soon as is convenient anyway.

That version does support autovacuum, as far as I can see from the documentation and if configured correctly that should tend to keep things under control. However, it's possible that bursty activity and/or index bloat has caused you some problems.

The simplest solution is to dump the database and restore. If you can afford the time to do so, it will pack the whole database in that process. Then, put in some regular (daily/weekly) monitoring of table/index sizes. Also add some basic monitoring of general activity on the machine (cpu / memory / disk).

You can take corrective action with a REINDEX or CLUSTER if it's required.

If you run VACUUM VERBOSE on one table at a time you'll see some statistics on what disk pages are used / can be freed / can't be freed. It should be obvious if there is a problem.

Finally, do take the time to plan an upgrade at a convenient point. The latest release is 9.2 and contains many performance and management improvements.

Upvotes: 4

Related Questions