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.

Without, of course, attempting to be a full MySQL guide (or even a full optimization guide), this tutorial will provide a few guidelines, rules-of-thumb, and settings suggestions so that a MySQL non-expert can still make his/her server run a lot more smoothly.

A vital tool:

MySQLTuner is an essential tool for MySQL administration, and, indeed, you should run it on your MySQL system before any configuration changes (whether suggested here or not), and also after implementing them. Its results, however, can be a bit intimidating, and (as its documentation warns) its recommendations should never be implemented blindly 2.

So, *do* install and use MySQLTuner (and, by following this guide, you should be seeing a lot more “green” and less “red” in its results), but don’t blindly follow its suggestions without understanding their point.

Oh, and keep your MySQLTuner script mostly up-to-date (assuming you installed it from its site, instead of your distro’s packaging system); don’t just keep using the version you downloaded some 4 years ago. Software evolves, you know. 🙂

About memory:

Apart from other “always-a-good-idea” settings that we’ll see later, the amount of memory you set MySQL to use is probably the most important configuration.

Before we begin: I still keep seeing co-workers (both at my job and the previous one) thinking of free memory as something that is desirable for a production server to have a lot of. They see a machine with, say, 90% of its RAM in use and get worried.

Guys and girls, Linux is not MS-DOS! There’s such a thing as virtual memory, and also another thing called disk caching! Linux actually uses most of its free memory to cache disk access (and recent versions of top actually show that as still “available”, but for decades it said “cached”). Memory that’s truly free is simply being wasted (and a server with a large amount of it even after a week of uptime is a server that should have some of its RAM removed to be better used elsewhere).

Having more than half of a machine’s RAM as disk cache is certainly not as bad as having it unused/wasted, but it would have been far better to let applications (in this case, MySQL) actually, you know, use it. Which we’ll do here.

OK, end of rant. 🙂

So, what’s a good rule of thumb for how much memory to give to MySQL? I’d say 80% of the system’s unused RAM.

Note the “unused”. If the server just has MySQL on it, then, sure, give MySQL 80% of the free memory after loading up the OS. But if it runs other services (e.g. a web server, some other database, etc.), then see how much RAM you have after all of them are running, and give MySQL 80% of that.

An exception to this rule might be a very, very small database, say, with a single table with just a couple of entries, and rarely or never updated. In this case, giving it so much memory might be overkill… but in such a case, you wouldn’t be here reading an optimization guide, right?

Memory configuration:

(Note: there are other memory-related settings, such as join_buffer_size or innodb_log_buffer_size, but their defaults should be good enough. Also note that defaults may change (they’re usually increased) between MySQL/MariaDB versions, which is one more reason to keep your database server updated as much as possible.)

(Note 2: if you’re using very old versions of MySQL or MariaDB, it’s possible that some settings below don’t “exist” yet. If the server refuses to (re)start and complains about an unknown setting, simply remove it/comment it out. Better yet, start planning a software update…)

First, back up your database. Really. Nothing here should be dangerous, but better safe than sorry. And, if possible, try out these changes on a test machine before moving to the real, production one.

For extra fun, run MySQLTuner now and save the results (e.g. mysqltuner.pl > ~/mysql-pre-optimization.txt), so you can compare them later…

So, edit (after backing it up, of course) your /etc/my.cnf (or /etc/mysql/my.cnf in Ubuntu, or /etc/my.cnf.d/server.cnf in Fedora), and look for the [mysqld] section.

You can add the following entries to the end of that section, they’ll replace their settings if they’ve already been previously set.

If you’re using InnoDB (and, between it and MyISAM, you certainly should):

innodb_buffer_pool_size = MEM1
innodb_log_file_size = MEM2
innodb_buffer_pool_instances = NUMBER
  • MEM1 should be about 80% of your available RAM (see “About memory” above). You can specify it as a number followed by “M” for megabytes, “G” for gigabytes, etc.;
  • MEM2 should be one eighth (1/8) of MEM1. For example, for 1 GB of MEM1, set MEM2 to 128M;
  • NUMBER should be the number of gigabytes (rounded down) of MEM1. For instance, for 4 GB, set NUMBER to 4. Note that this value doesn’t have a unit (M, G, etc.)

If you’re not using InnoDB at all (why?), then don’t add any of the above, of course.

For MyISAM:

key_buffer_size = MEM3

If your databases are all InnoDB, then leave this value small (e.g. 4M), as the MySQL user tables and such are still MyISAM. If you have many and/or large MyISAM tables, on the other hand, set MEM3 to 25% of the available RAM (see “About memory” above), and reduce the InnoDB memory by that amount (see MEM1 above). If you have only MyISAM tables, then you can raise MEM3 to 50%-80% of available memory.

If you’re using Aria tables, the setting is aria_pagecache_buffer_size; follow the key_buffer_size recommendations.

Other settings:

I find the following settings to work pretty well in most cases (and MySQLTuner seems to agree):

query_cache_type = 0 # recommended to turn off, these days
query_cache_size = 0
thread_cache_size = 128
table_open_cache = 2048
low_priority_updates = 1 # MyISAM only, but no harm
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
tmp_table_size = 64M
max_heap_table_size = 64M

And that’s it! Restart MySQL, and you should enjoy much better performance, both for MySQL itself and for the rest of the server.