- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: System Configuration File & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
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:
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
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.
- Reevaluate all buffers and directives that have been modified previously. This includes the changes discussed and recommended in the entirety of this article series.
- Reassess MySQL Query Statistics to determine workload behaviors.
- Reassess tablespace data, rate of growth or other trends in data consumption.
- Archive old data from large or heavy trafficked tables to ease the burden of read/write requests to those tables.
- 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.
