The most impactful tuning on most servers.
THE KEY SETTING
For InnoDB, the buffer pool size determines how much data is held in memory. The default is far too small for any real workload.
Set it to a substantial portion of available memory, leaving room for everything else on the server. On a dedicated database server this can be most of the memory; on a shared web and database server, considerably less.
OTHER SETTINGS
Connection limits, matched to what your application actually opens Query cache, which in modern versions is generally best disabled Temporary table sizes, if you see many temporary tables written to disk Log file sizes
SLOW QUERIES
Enable the slow query log. It identifies queries taking longer than a threshold, which is usually where the real problem lies.
A single unindexed query running on every page load causes more trouble than any configuration setting.
INDEXES
Missing indexes are the most common database performance problem. Analysing slow queries usually reveals them.
MAINTENANCE
Optimise tables periodically, particularly after large deletions.
MEASURING
Change one setting, measure, then change another. Applying a list of recommended settings blind frequently makes things worse.
