perfSONAR User Q&A and Other Discussion

[Mark Feit <>]

I wrote:

 

If you wouldn’t mind doing me a favor, grep the word “vacuum” out of the last few days of logs and send me that plus your postgresql.conf off-list.  We can do some tuning of the autovacuum behavior on a per-table basis.  Doing it globally works but is a little heavy-handed.

 

Andrej sent his logs and configuration.  Debian/Ubuntu’s configuration is identical to CentOS in all ways significant enough to matter, so this might have to do with usage.

 

I plotted the autovacuum aborts gathered in into 10-minute bins, which showed a cyclic pattern over the four days in the logs.  I pulled out a single day and have attached that plot.  (If the attachement doesn’t make it, see https://i.imgur.com/9o2hjlj.png.)  It seems to pick up abruptly in the early parts of the morning and drop off just as quickly by late afternoon, from which it could be inferred that pScheduler, and therefore the database, is under heavier load during those hours.  There is other data we can pull from the system to see what the actual workload is like.

 

The way concurrency control works in PostgreSQL works requires that the server create new copies of updated rows and mark the old ones as dead, with the dead ones cleaned up during vacuuming.  A usage pattern like the one here makes that the amount of dead-row bloat in the table increase when vacuuming fails and decrease when it completes.  Unless it’s taking up an inordinately-large amount of space, I wouldn’t worry too much about it.

 

Upping the deadlock time to five seconds is a decent temporary fix and shouldn’t cause problems with the operation of the system.  I’ll open an issue to look at this, probably in one of the patch releases after 4.2.0.

 

--Mark

 

Attachment: aborts.png
Description: aborts.png