What are some guidelines for performance tuning MySQL databases?
Sometimes, it's just that your schema is crap. You're doing too many joins, or you have too much duplicated information. Dba modeling is an art in itself . A redesign might cause a lot of time, but it might save a lot of headaches in the long run.
Optimization depends on the sort of operations the database is supporting.
In general, you can divide databases into General purpose, Data Warehouse or Highly transactional. Warehouse generally need a ton of disk space but small computation power. Highly transactional general have high CPU and RAM but not that much disk space. General purpose are in the middle depending on each case.
Queries.
Run your queries on a sample dataset with cache disable and the explain plan flag. This will help you appreciate what the Db is executing when resolving your information for the first time the operation arrives.
Full scan operations and cross joins are candidates for optimization. In general, it's a good practice to build indexes over the fields you have in your where clauses so the db doesn't have to go to disk to know which records matter. Sometimes you can even add the select clause columns to the index and still make the index fit in memory.
Query rewriting is another option, like only including in the select projection the columns that are important to the information requirement. Sometimes using UNION is faster that wheres with ORs and bitmap indexes are better that equals expressions on enums. These are just some techniques, and they always require some degree of analysis. So it really comes down to studying what are your queries causing the db system to do.
Additional structures like materialized views can help you keep good statistics about the queries you're running and they cache execution plans better than plain indexes. So if it is not too much to maintain them you can use them.
Caching and parametrizing caching might also help for cases when you run the same query every time just with different parameters. I am not a big fan of this strategy , since caches in the db are usually temporary and quickly flushable. Indexes, in my opinion are the real solution to query slowness.
Read with no lock by default. Use lock for update when it is strictly needed.
Also, try to have a decent updated set of statistics on your columns. Like your high selectivity operators, count your nulls. This will help your structure your queries more efficiently. For example: If you know for sure the max value of a number in your domain is 200 would you reserve 10 digits or 3 to store it?. That's the beauty of good statistics, they make you think about resources.
Resources
Don't make your database compete with other processes for resources. Memory and CPU specially should be dedicated in the server so that the DB can use them. Depending on the type of database, you might want more or less RAM.
A decent estimator for RAM is, for your predicted database size let's say 20Gb of data in datafiles, calculate your indexes size added let's say 2GB plus leave like 1GB of just administrative space and the data dictionary and some more for cache. Then all that space should be 60%-65% of your actual RAM.
CPU usage is a whole different story but I would dare to say anything more than 2–4 cores clocked at 2.2 Ghz or more with at least level 2 cache is decent for a mid size db.
Of course, if it is a mission critical db, you should killer hardware. If it is really mission critical, probably you're already in Oracle in some engineering system.
Miscellaneous
Use mysql for the things Mysql is good at. InnoDB is the closest you can get to Oracle DB without paying. Trust that engine. It works.
Use the slow queries log to start catching candidates queries for optimization. You can even put configuration to say what level of tolerance the slow query log has.
Check how your are handling nulls. In general, for tables with lots of nulls it's counter productive to use fixed size columns. Also beware that bitmaps run faster that equality or in clauses over fixed values (constants).
Particularly in Mysql avoid heavy operations like full text searches (consider elastic search) and function application over tables in a where clause (no index support, it's better to use materialized views).
For highly transactional tables use a bigger log size since you'll be expecting more transactions per unit of time.
For a Warehouse I'd say the most important thing is to optimize your queries with a redundancy strategy and have a strong incremental backup policy.
The log size also depends on how often are transactions happening and how big in size to disk are the operations done by transactions.
Schema
Sometimes, it's just that your schema is crap. You're doing too many joins, or you have too much duplicated information. Dba modeling is an art in itself . A redesign might cause a lot of time, but it might save a lot of headaches in the long run.
Outside the Db
I did not write about network lag, libraries and frameworks that connect to the db, connection pools, versions of the software, the application code and many other factors that can affect how you perceive “performance". It's such a broad but interesting discussion.
