We are currently running backups of the database starting at 7:30 UTC on Sunday and Wednesday mornings. In order to allow them to finish relatively quickly while we wait for the additional RAM, we are stopping the backend processes (i.e. validation) while the backup runs.

This means that a large backlog of work needing validation will build up during that timeframe. It will resume around 15:00 UTC and take 12-18 hours to catch up.

We have to modify one of the indexes on the 'result' table. As a result we will have to stop the schedulers for about 45 75 minutes.

Ok - I'm overdue for an update here, but I finally have some good news.

All processes are running again and we have put the system under max load and everything is behaving well. Here are some of the things that we had to change:

1) The bufferpool we have for MySQL is 44GB. However, we had innodb_buffer_pool_instances set to 1. This resulted in heavy contention for locks. MySQL has threads use spin locks for a short period of time while waiting for a lock. This caused very high cpu use on the server which resulted in a degradation in performance for all database activity. We are now using innodb_buffer_pool_instances=8 which has significantly reduced contention for locks which has lowered the cpu use. This is allowing better performance for all transactions.

2) We lowered the transaction isolation level for some backend daemons. There was contention in particular on some of the indexes for the result table (it is a 96GB table on disk). By changing the isolation level, fewer locks are held on the indexes which has increased throughput.

3) We have change the way the jobs that load work compute how much work needs to be loaded. In particular, there was one query that computes the average jobs completed per day over the past 4 days. This value is part of the calculation used to determine how much work needs to be loaded. This query was executed repeatedly when work was being loaded and is particularly resource intensive. We have replaced the dynamically computed value with a manually computed value for now. Sometime in the future we will replace this with a job that runs once per day.

4) In a few cases we have improved the SQL used to make the queries more efficient.

5) We have added additional instances of daemons for the very backend processes in order to ensure that data is deleted from the database and filesystems promptly so that we don't fall behind again.

At this time the backend processes are catching up quickly. There is only a backlog of 98,000 workunits for hcc1 validation. It has been catching up at a rate of 20,000 workunits/hour so we will almost be caught up by end of day.

Unfortunately we did not build up enough work for Help Conquer Cancer before the daemons were stopped for the database backup. As a result we ran out of work ready to send about 3 hours ago. The daemons are running now and there will be work ready to send within an hour.