MySQL Performance: System Configuration File & Routine Maintenance

Reading Time: 3 minutes

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:

MySQL Configuration Files:

MySQL Configuration Files:

1./etc/my.cnf

2./etc/mysql/my.cnf

3.SYSCONFDIR/my.cnf

4.$MYSQL_HOME/my.cnf

5.~/.my.cnf

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

Note
The trailing d character after mysql is required to apply settings to the MySQL service daemon. The [mysql] header without a trailing d character is incorrect and applies only to MySQL client.

 

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

Important:
Refrain from making several optimization changes to your existing MySQL config at one time. This is especially true when adjusting production level servers. Making small incremental changes will make it easier to identify problems with individual changes and isolate settings that may not work well for your unique setup.

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.

What are some tasks to perform routinely:
  1. Reevaluate all buffers and directives that have been modified previously. This includes the changes discussed and recommended in the entirety of this article series.
  2. Reassess MySQL Query Statistics to determine workload behaviors.
  3. Reassess tablespace data, rate of growth or other trends in data consumption.
  4. Archive old data from large or heavy trafficked tables to ease the burden of read/write requests to those tables.
  5. Reevaluate indexes and their performance. Create new, better indexes and remove old, unused indexes.

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.

Series Navigation<< MySQL Performance: Converting MySQL to MariaDBMySQL Performance: InnoDB Buffers & Directives >>

Author Bio

About the Author: J. Potter

A veteran of the IT Support field, I have more than a decade of experience in systems administration, web hosting & specifically cPanel servers. I enjoy writing and providing complex technical concepts in plain terms. In my free time, I enjoy playing several types of video games, scripting and just living life with my wife and two kids.

Get 33% off the first 3 months on a new VPS! Find out why 30,000 customers have chosen our Best-in-Class Performance & 24x7x365 Support