#485 Schedule Koji DB vacuum
Closed: Fixed None Opened 15 years ago by toshio.

We need to schedule a vacuum of the koji database as a whole to prevent transaction overflow.

This is not something that needs to cause database downtime but it could slow queries up a bit and it will take longer if we're running things against koji (because the vacuum may have to wait for long running transactions to exit.)

  • Note: this is a vacuum of the database as a whole, not a "vacuum full".

{{{
Traceback (most recent call last):
File "/var/lib/pgsql/vacstat.py", line 650, in ?
Commandscommand
File "/var/lib/pgsql/vacstat.py", line 150, in test_all
test_transactions(opts)
File "/var/lib/pgsql/vacstat.py", line 147, in test_transactions
raise XIDOverflowWarning, '\n'.join(overflows)
main.XIDOverflowWarning: Used over half the transaction ids for koji. Please schedule a vacuum of that entire database soon:
sudo -u postgres vacuumdb -zvd koji
}}}


{{{
(07:57:59 AM) abadger1999: mbonnet: Do you think we'll see much slow down to regular koji operations if we run the vacuum concurrent with normal koji work?
(07:59:25 AM) mbonnet: abadger1999: I suspect it'll get a little sluggish yes. What'll really kill us though is when the sessions table grows to 50G and logins start timing out (the sessions table gets hit on every authenticated connection to koji)
(08:00:28 AM) abadger1999: Will we be able to run vacuums of individual tables while the whole db vacuum is taking place or will the whole db vacuum lock the others out?
(08:01:18 AM) mbonnet: abadger1999: I think we might as well disable the other vacuums, since the long-running big-vacuum txn will keep the other vacuums from really doing anything
}}}

We'll be starting the vacuum on Saturday. Plan is to:
1. Edit cron scripts to disable the automatic koji vacuuming
2. Push that out via puppet
3. screen in on db2
4. sudo -u postgres vacuumdb -d koji
5. hope it finishes in our lifetime :-)

This has been started. Minor alterations:
1. Edit cron scripts to disable the automatic koji vacuuming
2. Push that out via puppet
3. screen in on db2
4. export PGOPTIONS='-c maintenance_work_mem=1048576'
5. sudo -u postgres vacuumdb -v -d koji

Vaccum has completed. Activating the cron scripts for koji tables again.

And we should be done.

Login to comment on this ticket.

Metadata