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.

  • Java
  • X3D
  • leak
  • paragraph
  • reboot

    The action of restarting a computer is called reboot. This reloads the operating system and your different auto-start software.

    There are two types of reboots: a cold reboot and a warm reboot.

    The cold reboot is the one where you turn your computer all the way off and back on. It is considered to be the ultimate reboot which ensures that everything is alright.

    The warm reboot is when you just reboot your operating system. This means some of the hardware parts may not be fully reset as expected in a cold reboot. There are several reasons for this, at times the BIOS of your system does not give the correct signals to all the parts, and at times the signal doesn't propagate to all the parts as it should.

    This is why a warm reboot does not always work (i.e. think when you change a driver and the new driver is not able to properly reset the state of a board or chip... turning the computer all the way off and back on may resolve the problem.)