MySQL Tuning methodology?

I really want to have a solid tuning methodology. That is partly why I wrote the post on the 12 Steps to MySQL Query Tuning. Whenever I do internet searches on this topic, I get so many random slide presentations.

For several years I've thought of database tuning as more of an art than a science. I say this because each database installation can be so different. Some database are meant for OLTP while others are meant for OLAP or some are treated like queues. Every application has different usage patterns for how it may or may not stress a database, every database is going to have different data structures, have different hardware resources available, different network constraints, different hosting (virtual, SAN, on-premise), and on an on. You cannot say that what works with one systems is going to work for another system. Not all systems fit easily into a cookie cutter approach where you can use the same settings on each database instance and expect the same results.

While chatting with a Principal MySQL consultant at Pythian, he said he definitely had a tuning methodology and that my belief that database tuning is more of an art with trial an error was not good enough to him.

My tuning approach has been this:

Step 1:
Review the database parameters, compare them against what my "baseline" of recommend parameters is. For MySQL this means review the my.cnf file. If I don't have file system access, I compare what is in the "SHOW GLOBAL VARIABLES". If I find anything obviously out of place then I will see if I can make incremental changes. Sometimes I think a variable needs to be increased and waste time incrementally increasing it when it actually worked better being decreased.

Step 2:
Next I'll review the results of real-time profiling. I'll be looking for low hanging fruit. Are there more threads running on the instance than CPU available? Too many connections being created for this system to handle? If there is replication, is it lagging? Specific queries with poor execution plans? Full table scans, full index scans, poorly written queries? Are the queries ones that were written by humans or generated by an ORM tool? Are tables lacking indexes? Are there specific DELETES or UPDATES causing excessive locking or blocking?

Step 3:
Then I'll review what my trend history tool shows me. That could be Cacti, Zabbix, Ganglia or whatever you use. Is this server experiencing a higher trend than previously? Did I/O significantly increase recently? Was there any change or spike that stands out? If I see something concerning, I'll dig further into that.

Step 4:
If I have access to the  system, I'll look at MySQL wait states, memory usage, events happening, error logs, slow query logs.

Step 5:
If that hasn't proven useful I'll look into the table structures to see if the data model has obvious points of contention. Since I'm usually not an expert in the application that is using this database, I'll go ask the developers, what is the purpose of such and such query, why is it running so frequently, is is really necessary to do such and such? Sometimes the answer is, "We actually don't need to run that query 50,000 times per minute, once every minute would be fine." And then the code or scheduler or whatever is adjusted to fit that new discovery.

I don't have a ton of experience in MySQL command level tools for in depth trace analysis and I haven't had the opportunity or time to go deeper. Up to know, with decent data modeling, good my.cnf parameters, proper memory settings, load balancing with Master-Slave setups, I've been able to achieve "good enough" performance to meet SLAs. If I can get the queries to run less than 100 ms, I'm pretty happy and the business is happy.

Since my contact at Pythian wasn't kind enough to educate me on his own tuning methodology I started poking around for one and came upon this blog entry: http://www.pythian.com/blog/optimizing-your-mysql-tuning-methodology/

It stills seems "artsy" with plenty of trial and error to me.

I like having a list of tips like this but frequently all of these tips have already been applied and I still need a tuning methodology:
http://www.monitis.com/blog/2011/07/12/101-tips-to-mysql-tuning-and-optimization/

I also like this long list from Dual, this is probably the most "procedural based" tuning list I've seen:
http://www.fromdual.com/mysql-performance-tuning-key

What about you? Do you have methodology to share?