5 Tips to MySQL Performance Tuning and Optimization

In this article, We are going to perform How to do MySQL Performance tuning and Optimization.

Introduction

MySQL is the world’s most popular open source relational database management system. It is widely used with web server like apache2,Nginx,IIS and used by many web based packages such as WordPress, Joomla and Magento.

Default configuration has reasonable performance and by doing changes in configuration we can optimize performance and fine tune to database.

Below are the main factors which affects MySQL Performance,

1. System Configuration i.e. CPU ,Memory(RAM),HDD
2. Volume of Data being retrieved.
3. Database Load
4. Poor Coding
5. Poor Indexing
6. Incomplete Configuration

First we are going to optimize by adding and modifying changes in mysql configuration on below OS and MySQL Version,

• OS:         Ubuntu 16.04 LTS
• MySQL:  MySQL 5.7

1. MySQL Error 1040 : “Too Many Connections” / Set MySQL Connection (max_connections)

If you are getting error “Too many connections” while trying to connect to a MySQL Database, that means it reached the maximum number of connections, or all available permitted are in use by other clients.

By default value set to 151 + 1 , extra is for only super previlege like root using variable max_connections.

Use below command to see current value of max_connections.

SHOW VARIABLES LIKE "max_connections"; 

Output:

mysql> SHOW VARIABLES LIKE "max_connections";
 +-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | max_connections | 151   |
 +-----------------+-------+
 1 row in set (0.00 sec)

To increase the max_connections value, let’s say 1000, enter this command:

 SET GLOBAL max_connections = 1000; 

Output:

mysql> SET GLOBAL max_connections = 1000;
 Query OK, 0 rows affected (0.00 sec)

Verify max_connections after increasing.

Output:

mysql> SHOW VARIABLES LIKE "max_connections";
 +-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | max_connections | 1000  |
 +-----------------+-------+
 1 row in set (0.00 sec)

above command takes effect right after you entered it, but it only applies to the current sessions,after restarting it shows default value i.e.151.

If you want it to be permanent , edit the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf

Under the [mysqld] section add the following line:

 max_connections = 1000 

To take effect restart the MySQL Server

$ sudo systemctl restart mysql

2. MySQL Error: “too many open files” and how to Solve it,

If you are getting above error means ,issue has to do with the present limitations given to the system and PAM system to open max 1024 files.

Check the files-open limits, run below command

SHOW VARIABLES LIKE 'open%';

Output:

mysql> SHOW VARIABLES LIKE 'open%';
 +------------------+-------+
 | Variable_name    | Value |
 +------------------+-------+
 | open_files_limit | 1024  |
 +------------------+-------+
 1 row in set (0.00 sec)

That means that Mysql server gets to open maximum 1024 which seems very less for MySQL 5.7

Copy the limits for mysql from the systemd config file to /etc/systemd using:

$ sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/ 

$ sudo nano /etc/systemd/system/mysql.service 

Then add the following lines to the bottom of the file:

LimitNOFILE=infinity
LimitMEMLOCK=infinity
       

Note: You could also use a finite number instead of infinity

then reload systemd config to take effect.

$ sudo systemctl daemon-reload 

3. Change MySQL wait timeout Variable

Sometime application fails to close connection which is no longer using. wait_timeout: is a variable which defines the amount of time that MySQL will wait before killing an idle connection.

We can change the wait_timeout variable, for a session or globally. If we change the wait_timeout variable for a session, it will be valid only for a particular session. But when we set the wait_timeout variable globally it will be valid for all the sessions.

Default wait_timeout value is 28.800 seconds. Run Below commands to check,

SHOW SESSION VARIABLES LIKE 'wait_timeout'; 

SHOW SESSION VARIABLES LIKE “%wait_timeout%”; 

Output:

mysql> SHOW SESSION VARIABLES LIKE 'wait_timeout';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | wait_timeout  | 28800 |
 +---------------+-------+
 1 row in set (0.01 sec)

To set timeout value for a session lets say 180 seconds, run below command,

SET session wait_timeout=180; 

Output:

mysql> SET session wait_timeout=300;
 Query OK, 0 rows affected (0.00 sec)

4. Sizing InnoDB Buffer Pool

InnoDB Buffer Pool is the memory space in which indexes, caches, buffers and row data are stored. innodb_buffer_pool_size is the MySQL configuration parameter that specifies the amount of memory allocated to the InnoDB buffer pool by MySQL,this is one of the most important variable settings for MySQL configuration and size should be depend on available VM RAM.

Commonly 60-70% of physical memory can be allocated to InnoDB buffer pool size, Default size is 128M, Connect to MySQL and run below command to check,

SELECT @@innodb_buffer_pool_size;

Output:

mysql> SELECT @@innodb_buffer_pool_size;
 +---------------------------+
 | @@innodb_buffer_pool_size |
 +---------------------------+
 |                 134217728 |
 +---------------------------+
 1 row in set (0.00 sec)

If you want change size permanantly , edit the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf, Under the [mysqld] section add the following line:

innodb_buffer_pool_size = 5G

To take effect restart the MySQL Server

$ sudo systemctl restart mysql 

5. Mysql Query Cache

MySQL Query Cache increase the loading performance of application and improves performance.there are various types of caching mechanisms , this is one of the method.

Note: The query cache is deprecated as of MySQL 5.7.20, and is removed from MySQL 8.0.

To Configure query cache , first to check if query cache is available, Login to MySQL and use below command to check,

SHOW VARIABLES LIKE 'have_query_cache';

If the Value is YES, means cache is available/working.

mysql> SHOW VARIABLES LIKE 'have_query_cache';
 +------------------+-------+
 | Variable_name    | Value |
 +------------------+-------+
 | have_query_cache | YES   |
 +------------------+-------+
 1 row in set (0.00 sec)

query_cache_size  is the MySQL configuration parameter ,default size is 40 KB and maximum size can be 32 MB.use below command to increase size.

mysql> SET GLOBAL query_cache_size = 40000;

If you want change size permanantly , edit the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf, Under the [mysqld] section add the following lines:

query_cache_size = 40000  
query_cache_type=1 
query_cache_limit=2000 

Global query cache size is applies for all sessions, we can disable for session using below command,

SET SESSION query_cache_type = OFF; 

To take effect restart the MySQL Server

$ sudo systemctl restart mysql

Conclusion

In this article ,We have performed how to do MySQL Performance tuning and optimization by changing in MySQL Configuration like increase mysql connection,increase open file limit,reduce wait timeout,increase buffer pool size and increase query cache. If you are facing any issues and need support, please share error message in below comment box.

Related Articles:

How to install MySQL 5.7 on Ubuntu 16.04