By using this site, you agree to the Privacy Policy, Cookie Policy and Terms of Service.
Accept
TRENDING
  • Linux
  • Shell Script
  • Cloud
  • DevOps
  • About US
SUBSCRIBE
  • CLOUD
  • DEVOPS
  • LINUX
  • SHELL SCRIPT
Reading: How to Optimize MySQL Performance on AlmaLinux / Rocky Linux
Share
TechBrownTechBrown
Aa
  • CLOUD
  • DEVOPS
  • LINUX
  • SHELL SCRIPT
Search
  • CLOUD
  • DEVOPS
  • LINUX
  • SHELL SCRIPT
Follow US
© 2014 - 2022 TechBrown. All Rights Reserved.
Linux

How to Optimize MySQL Performance on AlmaLinux / Rocky Linux

Published October 19, 2021
Share
3 Min Read
SHARE

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:

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

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.

You Might Also Like

How to Enable TLS 1.3 on Apache and Nginx

5 Security Tips to Protect your WordPress Website from Hackers

How to Enable AES-128 Bit SSL Cipher with TLS 1.3 on Nginx Web Server

How to Set Up NGINX Web Server on AlmaLinux / Rocky Linux

How to Configure ISCSI Target Server on AlmaLinux / Rocky Linux

Pranay Nimkar October 19, 2021
Share this Article
Facebook Twitter Email Print
Leave a comment Leave a comment

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

TRENDING

Shell Script

Bash Script to Install WordPress with LEMP Stack on Ubuntu / Debian

Linux

How to Configure Lets Encrypt SSL with Apache on AlmaLinux / Rocky Linux

Linux

How to Extract tar.gz / tar.bz2 / tar.xz Files on Linux

AWS

How to Encrypt Amazon EBS Volumes using AWS KMS Customer Managed Keys

You Might Also Like

Linux

How to Enable TLS 1.3 on Apache and Nginx

March 14, 2021
Linux

5 Security Tips to Protect your WordPress Website from Hackers

November 8, 2020
Linux

How to Enable AES-128 Bit SSL Cipher with TLS 1.3 on Nginx Web Server

November 7, 2020
Linux

How to Set Up NGINX Web Server on AlmaLinux / Rocky Linux

April 28, 2020

Follow US on Social Media

Facebook Twitter Youtube Rss

© 2014 - 2022 TechBrown. All Rights Reserved.

  • About Us
  • Terms of Service
  • Cookie Policy
  • Privacy Policy

Removed from reading list

Undo
Go to mobile version
Welcome Back!

Sign in to your account

Lost your password?