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:
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.
Post a Comment