CODEWITHSUNDEEP
postgresql
stanleyxu2005
stanleyxu2005

Reputation: 8231

PostgreSQL database size increasing

I have a strange problem. The size of my postgresql (8.3) is increasing. So I made a dump and then cleaned up the database and then re-imported the dump. The database size was reduced by roughly 50%.

Some infomation: (1) AUTOVACUUM and REINDEX are running regularly in background. (2) Database encoding is ASCII. (3) Database location: /database/pgsql/data (4) System: Suse-Ent. 10.

Any hints are appreciated

Upvotes: 2

Views: 10967

Answers (5)

Ryan Bair
Ryan Bair

Reputation: 2634

If the dead tuples have stacked up beyond what can be accounted for in max_fsm_pages, a regular VACUUM will not be able to free everything. The end result is that the database will grow larger and larger over time as dead space continues to accumulate. Running a VACUUM FULL should fix this problem. Unfortunately it can take a very long time on a large database.

If you're running into this problem frequently, you either need to vacuum more often (autovacuum can help here) or increase the max_fsm_pages setting. When running VACUUM VERBOSE it will tell you how many pages were freed and give you a warning if max_fsm_pages was exceeded, this can help you determine what this value should be. See the manual for more information. http://www.postgresql.org/docs/8.3/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Fortunately, 8.4's visibility map resolves this issue. Despesz has a great story on the subject as usual: http://www.depesz.com/index.php/2008/12/08/waiting-for-84-visibility-maps/

Upvotes: 5

Magnus Hagander
Magnus Hagander

Reputation: 25078

If you haven't already, check your system for long-running idle transactions. They will prevent VACUUM (both manual and auto) from clearing out space.

Upvotes: 1

bortzmeyer
bortzmeyer

Reputation: 35459

Did you try a VACUUM FULL, too? (Warning, it locks your database for a long time.) I am not sure that AUTOVACUUM is so eager...

Upvotes: 1

user80168
user80168

Reputation:

running reindex shouldn't be necessary.

run database wide vacuum with verbose, and check the last lines for fsm settings hint - maybe it is what is wrong.

Upvotes: 1

Jordan S. Jones
Jordan S. Jones

Reputation: 13883

Without knowing more specifics about your particular setup, a couple of things come to mind. When AUTOVACUUM runs, is it trying to reclaim disk space, and can you verify that it is through server logs?

Secondly, especially if the previous answer was no, your AUTOVACUUM values may be incorrect. I would highly recommend reading the following on the subject: http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#AUTOVACUUM

Upvotes: 1

Related Questions