In this guide, we will optimize the MySQL Database Server performance and implement various optimization parameters on AlmaLinux, Rocky Linux, and Amazon Linux. When it comes to database performance of relational databases such as MySQL. As data volumes, increase and performance become a crucial factor to deliver a better experience to users. Performance optimization needs to be done to deliver a quick response. We are here for providing solutions to optimize the database performance and you have to follow all the steps to do this.

Introduction

The MySQL is most widely used open-source relational database management solution. It is launched by oracle and currently, it's an open-source and free-to-use RDS database. Most of the applications are running using MySQL. It uses port number 3306 and has various managed hosting provider support.

Prerequisites

We required given prerequisites to proceed further:

LIST

  • Need virtual machine or cloud instance.
  • MySQL server should be installed on the machine.
  • MySQL should be in running state.

Contents

  • Step 1 — Checking MySQL Server Status
  • Step 2 — Modify MySQL Configuration File
  • Step 3 — Apply Optimization to MySQL Server

Step 1 — Checking MySQL Server Status

Before proceeding further we have to check the MySQL Server is in a running state. For that, we have to run the given commands. 

# systemctl status mysql.service

If MySQL server status is in the failed state then we have to start the MySQL service. 

# systemctl start mysql.service

Step 2 — Modify MySQL Configuration File

We will modify the MySQL configuration file, for making changes and adding performance optimization parameters. 

# vi /etc/mysql/mysql.conf.d/mysqld.cnf

We need to modify or add given lines to optimize the DB as per our requirements and resources availability. 

bind-address         = 0.0.0.0  
lower_case_table_names = 1
#############################################################################################
## Ideal standard non changable values
############################################################################################
max_allowed_packet   =1024M
innodb_read_io_threads=8
innodb_buffer_pool_load_at_startup=ON
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_log_buffer_size=1024M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=16
innodb_buffer_pool_instances=16
#Required for import database
default-authentication-plugin=mysql_native_password
default_storage_engine='innodb'
sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
###########################################################################################
## Changable values change as per system configuration
###########################################################################################
innodb_write_io_threads=8          # To stress the double write buffer
innodb_buffer_pool_size=5G         # 70-80% available Memory
innodb_log_file_size =64M          # Small log files, more page flush
skip-innodb_doublewrite            #commented or not depending on test
################### set this parameter only for MySQL 8.0.19 ##############################
#optimizer_switch='block_nested_loop=off'
###########################################################################################
####################### Parameteres to increase the performance ###########################
skip-log-bin
sync_binlog=0
########################################################################################

Once done just save and exit from the text editor.

Step 3 — Apply Optimization to MySQL Server

In order to apply those changes in our MySQL Server. We have to restart the service to make changes in effect. 

# systemctl restart mysql.service

Conclusion

Hopefully, now you have applied the performance optimization to your MySQL databases. Now, you can check the optimization you feel is loading faster than ever and now you're ready to launch.