Re: Performance
> want to be proactive
The best thing:
Turn on slowlog with
long_query_time = 1
and periodically use pt_query_digest to see what the worst one or two queries are. Then consider improving indexes, rewriting queries, rethinking application, etc.
If you provide these, I will run another analysis:
SHOW VARIABLES;
SHOW GLOBAL STATUS; -- (with uptime of at least a day)
How much RAM? -- (apparently 16GB?)
> [--] Data in InnoDB tables: 220M (Tables: 320)
Not a lot of data.
But a lot of tables. Possibly you have "over normalized"?
> [!!] Maximum possible memory usage: 20.0G (124% of installed RAM)
> MySQL's maximum memory usage is dangerously high ***
Their formula is pessimistic; you are very unlikely to threaten the RAM size.
> [OK] Slow queries: 0% (0/10M)
Decrease long_query_time so you can proactively see queries that are beginning to be slow (and are likely to be slower as the data size grows).
> [!!] Temporary tables created on disk: 33% (99K on disk / 294K total)
> tmp_table_size (> 16M)
> max_heap_table_size (> 16M)
Once you find some slow queries in the slowlog, we can identify why there are so many temp tables and who so many of them have to be on-disk. Common causes: TEXT/BLOB field unnecessarily being fetched, lack of indexes, VARCHAR(255) when something smaller would do, SELECTs that could be reformulated, etc.
You would be better off spending time figuring why there are temp tables than blindly adjusting those settings.
> [OK] Table cache hit rate: 20% (256 open / 1K opened)
Linux, not Windows, correct? What is the value of table_open_cache?
> [!!] InnoDB data size / buffer pool: 220.5M/200.0M
This does not deserve a "!!" since it is a "cache". Still, if you have 16GB of RAM and this machine is mostly used by MySQL, then increase innodb_buffer_pool_size to 11G. (However, only 300M is needed for the small amount of data you have.) Increasing this is the most obvious and straightforward "proactive" thing to do.
> innodb_buffer_pool_size (>= 220M)
Their advice is not quite right -- it should say 10% more than 220.5M because of some miscellany kept in the buffer_pool.
> Run OPTIMIZE TABLE to defragment tables for better performance
Don't bother; it rarely helps, especially for InnoDB tables.
> Reduce your overall MySQL memory footprint for system stability
No, increase the footprint (as mentioned above).
> When making adjustments, make tmp_table_size/max_heap_table_size equal
OK, but it is no big deal.
> Reduce your SELECT DISTINCT queries without LIMIT clauses
Who ever has such? There are a lot of other query patterns that suck. Certain subqueries suck. (But mysqltuner was probably written before subqueries were supported!) My favorite naughty pattern is "pagination via OFFSET and LIMIT".
> Add RAM before increasing MySQL buffer variables ***
You have plenty of RAM to allow increasing them.
> query_cache_limit (> 1M, or use smaller result sets)
No. The Query cache becomes inefficient if you give it too much RAM. However, 50M is my recommended max. But, without looking at certain STATUS values, one cannot say whether the QC is being efficient.
Sorry, but I find some of these tools (eg, mysqltuner) to be less that great. I get especially irritated with them for not distinguishing between MyISAM issues (for which it was originally written over a decade ago) and InnoDB issues (which is default now). I'm working on a better one, but it is 'not ready for primetime'. However, I will apply your STATUS and VARIABLES, then clean up its output for you.