Reported slowness fixed.
In the past two days we have been performing fixes to the search index database and the usual pruning of our Graveyard. We were faced with the task of pruning 200,000 topics and many more replies. This process resulted in either script crashes with the prune script itself (now fixed) or lag in loading pages. Because we would prune 100,000 posts (not topics) at a time, this caused INNODB to constantly flush the delete updates to disk constantly.
This flushing causes dips in performance where for a second, MySQL will stop responding to requests. This performance phenomena is illustrated below:
Previously we were the orange colored line (XtraDB5). This seemingly small single second penalty (illustrated by the dips on the graph) results in notable lag because the number of requests that needs to be served within that second adds up.
With the fix detailed in this post, we enforced constant predictable flushing updates behavior to disk. This results in less maximum performance but higher average performance as illustrated by XtraDB5-adaptive (yellow).
Ultimately, this means the performance of the site will not lag in the future when faced with intensive updates maintenance such as pruning.
