Syndicate content

Auto-Vacuum not working? (and solution)

Since I manage multiple computers and each run a PostgreSQL database system, I can see discrepancies between versions. (i.e. newer versions fix problems in older versions.)

One is in regard to the VACUUM FULL function. A properly installed PostgreSQL system is expected to run a VACUUM command every now and then on each of your database system. That function is a defragmenter, if you wish. It goes through your clusters and remove empty space (i.e. deleted nodes, logs, aggregator items, etc.) and moves data that is accessed more to the start of your file which can be accessed faster than the end of the file.

Somehow, older versions of PostgreSQL did not properly run VACUUM and I wondered why that would be. Today I noticed that the configuration was using "auto_vacuum = yes" instead of the expected "auto_vacuum = on". (i.e. all the other variables use the keyword ON (or OFF) and not YES and NO, and newer system have that variable fixed.)

This being said, there is nothing I could find about that mistake. It sounds to me as if YES or ON should work alike. It is clearly defined that way in SQLite.

Although, notice that I have no clue whether that made any difference, looking at the output of ps -ef I still could not see anything such as the expected postgres VACUUM process:

   postgres: autovacuum launcher process

So I guess I will still have to search for the real cause of the non-starting process...

Solution

Okay, my computer got bugged down again so I looked further into this one.

First of all, if you use a Drupal website (or similar) and have an Aggregator turn on (i.e. a tool to get RSS feed data to your site) it generates a lot of database accesses without anyone touching your site. Those numerous accesses will generate a lot of dirt that definitively need to be vacuumed.

The solution is to make sure that at least ALL of the following variables are turned ON. Note that applies to version 8.1 and 8.2 of PostgreSQL. Since version 8.3, it is better handled and works by default (at least under Ubuntu it does.)

superuser_reserved_connections = 4
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 300
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = 0.4

The values can be whatever you think is appropriate on your system. The defaults are not bad but some values may either be too large or too small on your system.

FYI, I found this website http://bricolagecms.org/docs/1.10/api/Bric/DBA.html with information on how to optimize your PostgreSQL database for Bricolage CMS, useful tool to organize your enterprise documents.

See also: PostgreSQL VACUUM not working...

Syndicate content

Diverse Realty

Diverse Realty Team

Want a New Home?
Want to Sell Your House?

Call Alex at
+1 (916)
220 6482

Alexis Wilke, Realtor
Salesperson
Lic. # 02024063

Cory Marcus, Broker
Lic. # 01079165

     

Terms of Site Index

Find the page/content you are looking for with our index.

  • OpenRecordset
  • collection
  • language

    In computing, a language is a lexicon and grammar enabling a programmer to write software. There are interpreted and compiled languages. When compiling the computer transform the instructions of a program into instructions that the processor can execute natively.

  • rsync
  • system