| Hmm. Bringing in a consultant is probably your best bet. Database optimization needs to generally take into account YOUR data and usage patterns. If you want to learn a bit yourself, pick up a copy of "High Performance MySQL." I've found that to be the most useful MySQL scaling book I own. Figure out your slow queries. Run "SHOW PROCESSLIST" on the server and see what's typically running. Look at the actual queries - if you're frequently running a query that looks through most rows of your biggest table, that's going to be a tough query to optimize. Instead, look to either caching or regular pre-computation of the results. I've found that an unfortunate number of people aren't aware of "EXPLAIN" and its use to help figure out query issues. Learn and use it. You're on a VPS. That may be fine, but it may have horrible I/O throughput. If you're writing a lot of data, or having queries that hitting some sort of mysql or OS cache, IO will be your bottleneck. Make sure it's fast enough. Look at average IO wait times. Test max. throughput. MyISAM tables may be an issue. MyISAM tables use table level locking - only one session at a time will be able to update the table. This is quite possibly a problem if you're doing any updates or inserts to a table that is also frequently read from. Look to move to InnoDB soon. See: http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html |
I'd say this is quite true.
>I've found that an unfortunate number of people aren't aware of "EXPLAIN" and its use to help figure out query issues. Learn and use it.
Yes, we discovered this some years ago.
Thanks for you input.