Performance on queries does not degrade linearly as data grows. You might suddenly find load a lot worse because some queries are now going to the disk than memory. If its not too expensive to add more memory and better disks to your database servers, you should consider this - this makes the biggest difference in performance.
You should be monitoring everything your DB's do - important variables, slow queries, query logs. We have a lot of daily reports that identify queries on every server
- Know which queries are run most often and use up most system time. Its useful to monitor your most frequent queries - so even while a query may not be slow, it might be getting run a million times an hour and it might be useful to cache it etc.
- You should know your slow queries and the top slow queries. MySQL 5.x allows comments in queries. Insert your stack trace into the query as a comment. This lets you figure out which action or url is calling that query and speeds up fixes. We cannot live without this now.
- Beyond the easy optimizations for some system variables - like wait_timeout, buffers etc., we have usually found more ROI on improving queries.
Love to work on large scale systems and challenging problems? - Come join us