16 April 2007

PostgreSQL housekeeping

The Edinburgh dCache recently started to show increased CPU usage (a few days after an upgrade to 1.7.0-34) as shown in the top plot. The culprit was a postgres process:

$ ps aux|grep 4419
postgres 4419 24.8 0.6 20632 12564 ? R Mar27 6091:11 postgres: pnfsserver atlas [local] PARSE

After performing a VACUUM ANALYSE on the atlas database (in fact, on the all of the databases), the CPU usage dropped back to normal, as can be seen in the bottom. I had thought auto-vacuuming was enabled by default in v8.1 of postgres, but I was mistaken. This has now been enabled by modifying the relevant entries in postgresql.conf.

stats_start_collector = on
stats_row_level = on
autovacuum = on

I also changed these parameters after the VACUUM process sent out a notice:

max_fsm_pages = 300000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000 # min 100, ~70 bytes each

The server requires a restart after modifying the last two parameters.

1 comment:

Greig A Cowan said...

Just to say that there wasn't an obvious way to run the vacuum analyse on all of the postgres databases so I looped over the names in /pnfs/epcc.ed.ac.uk/data/ since these correspond to the DB names. You also need to do the vo.generated DBs if you have them. Something like this should do it.

for i in `ls /pnfs/epcc.ed.ac.uk/data/`; do psql $i -c "vacuum verbose analyse";psql $i.generated -c "vacuum verbose analyse";done

You then have to do the admin, data1 and template databases by hand.