title: How to predict MySQL tipping points?
body: <p>I work on a big web application that uses a MySQL 5.0 database with InnoDB tables. Twice over the last couple of months, we have experienced the following scenario: </p>
  
  <ol>
  <li>The database server runs fine for weeks, with low load and few slow queries.</li>
  <li>A frequently-executed query that previously ran quickly will suddenly start running very slowly.</li>
  <li>Database load spikes and the site hangs.</li>
  </ol>
  
  <p>The solution in both cases was to find the slow query in the slow query log and create a new index on the table to speed it up. After applying the index, database performance returned to normal.</p>
  
  <p>What's most frustrating is that, in both cases, we had no warning about the impending doom; all of our monitoring systems (e.g., graphs of system load, CPU usage, query execution rates, slow queries) told us that the database server was in good health. </p>
  
  <p>Question #1: <strong>How can we predict these kinds of tipping points or avoid them altogether?</strong></p>
  
  <p>One thing we are not doing with any regularity is running OPTIMIZE TABLE or ANALYZE TABLE. We've had a hard time finding a good rule of thumb about how often (if ever) to manually do these things. (Since these commands LOCK tables, we don't want to run them indiscriminately.) Do these scenarios sound like the result of unoptimized tables? </p>
  
  <p>Question #2: <strong>Should we be manually running OPTIMIZE or ANALYZE? If so, how often?</strong></p>
  
  <p>More details about the app: database usage pattern is approximately 95% reads, 5% writes; database executes around 300 queries/second; the table used in the slow queries was the same in both cases, and has hundreds of thousands of records. </p>