MySQL/MariaDB easy optimization tutorial

Introduction:

MySQL and MariaDB (note: for brevity, I’ll be referring to “MySQL” from now on, but everything here applies to both and, indeed, these days I use MariaDB exclusively on my own servers) are very popular as database servers, but in my experience are rarely properly configured to take advantage of the server’s resources. While I’m not a database administrator (DBA), as a sysadmin I often have to diagnose performance problems in servers I maintain, and MySQL is a common culprit; it seems that most MySQL DBAs are well-versed in SQL, but the my.cnf configuration file seems to be a mostly unexplored mystery to them: either it’s untouched from the default configuration (which is conservatively set to work on the barest of machines), or they limit the memory usage to some 3% of the server’s available RAM(!) 1, or else they go in the other direction and try to use more resources than are available.

Continue reading