MySQL Performance: System Config & Routine Maintenance

The majority of work needed when adjusting the MySQL server is editing the applicable directives within a MySQL configuration file. There are multiple, optional configuration files that MySQL looks for when starting up. They are read in the following order:

Any of these files can contain MySQL server directives. For the sake of simplicity, this article assumes the default file /etc/my.cnf is being used for all examples. This is the most widely known file path in the majority of cases. However, some Linux distributions favor others than the ones listed from above. Though the file location is changed in these other distributions, the syntax and recommendations in this article series apply equally to all Linux based systems.

MySQL Configuration File Syntax

Each entry inside the configuration file applies to the most recent section header. Section headers are made up of a single line with the name of the header encapsulated within square brackets (e.g. [name]). When optimizing the MySQL service daemon,  changes will need to be within in the [mysqld] section header. The example below illustrates how this should look.

Example: Configuration File Syntax

[mysqld]
 innodb_buffer_pool_size=2G
 innodb_buffer_pool_instances=2

Applying MySQL Config Changes

The vast majority of directive changes merely require a restart of the MySQL service. There are a handful of other directives which may require an additional task to be undertaken while the MySQL service is offline. These tasks will be outlined in their specific section later in the article.

Restart MySQL Service:

service mysql restart

Note that with Mariadb 10.1+, servers using systemd (CentOS 7) --- `service mysql start/stop/restart` can cause issues as it calls the init script which differs from the systemd file for the service. This can cause MySQLsql services to start up resulting in dataloss. For these servers with systemd and MariaDB 10.1+ `systemctl stop/start/restart mysql` should be used.

Routine Maintenance

In the follow-up articles to this part of the MySQL Performance series, we will be outlining several directives and some suggested techniques to use for configuring those directives. It is imperative to understand that MySQL Optimization is an ongoing, ever-evolving configuration. As sites grow, so do data sets and workload behavior. The settings you configure today, will eventually become obsolete and probably sooner than you would like. Because of this eventuality, it is vitally important that routine maintenance is conducted on your configuration.

Performance Expectations

Although, the changes recommended in this article series aim to squeeze the best performance out of MySQL, the performance increase you may see is entirely subjective. Following these recommendations should help smooth the edges on any servers hitting the bottlenecks they are designed to target. However, there is no guarantee that these changes will have a positive or noticeable impact on the application level.

The biggest issues facing application performance are often on the coding level and not the server level. The underlying MySQL server configuration only carries application performance so far. Identifying problematic queries and inefficient coding practices that serialize your workload are problems that even a finely tuned MySQL server configuration just cannot correct. If you are in doubt, or your application performance seems to suffer despite all the server level optimization in the world, then you’re probably hitting code level performance problems, and we recommend contacting a qualified Database Administrator (DBA) to evaluate your application performance properly.