How to Tune and Optimize Performance of MySQL 8.0 on a CentOS 7 Server

Introduction

Before we start you’ve got to understand the factors affecting MySQL performance, so that you can learn to tune and optimize MySQL server in the right way and get the right results. The main factors include:

Volume of data being retrieved>
Resources available i.e. CPU, Memory (RAM)
Load run by MySQL server

Causes of poor MySQL server performance include:

Poor database design
Hardware bottlenecks
Poor coding
Poor indexing
Inefficient configuration

In this article, we illustrate how to tune and optimize performance of MySQL 8.0 on CentOS 7 Linux VPS.

Pre-requisites:

CentOS 7 VPS Setup
Installed MySQL 8.0 Server
Created user with sudo privileges

Installing mysqltuner and tuning-primer

Install mysqltuner

Mysqltuner is a high-performance MySQL tuning Perl script that gives a snapshot of the MySQL server’s health status and gives specific recommendations for improved, increased performance, stability and efficiency.

Run the commands below to install mysqltuner

First install epel-release since mysqltuner package is in epel-release repository

$ sudo yum install -y epel-release

Then install mysqltuner

$ sudo yum install -y mysqltuner

Install tuning-primer

Tuning Primer is a shell script that obtains information from MySQL server internals and makes recommendations on adjustments of the server variables.

Run the commands below to install tuning primer:

$ sudo yum install bc -y
$ git clone https://github.com/RootService/tuning-primer.git

Special Note: Incase git is not installed, you can install it using
$ sudo yum install git -y

Change into the tuning-primer directory and make the tuning-primer script executable as follows:

$ cd tuning-primer
$ sudo chmod +x tuning-primer.sh

Tuning and optimizing mysql server

The tuning of MySQL server is done on MySQL configuration file.

/etc/my.cnf

Run mysqltuner.

$ sudo mysqltuner

Enter the mysql user with all privileges (in our case “root”) and password upon prompt

[linuxuser@centos7-hostadvice4 ~]$ sudo mysqltuner
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:  >>  MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[!!] Currently running unsupported MySQL version 8.0.11
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 16K (Tables: 1)
[OK] Total fragmented tables: 0
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
 
-------- Security Recommendations  -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
[OK] There is no anonymous account in all database users
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'password' in 'where clause'
[OK] All database users have passwords assigned
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(user) OR CAST(password as Binary) = PASSWORD(UPPER(user)) OR CAST(password as B' at line 1
mysql: [Warning] Using a password on the command line interface can be insecure.
[!!] There is not basic password file list !
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Use of uninitialized value in addition (+) at /bin/mysqltuner line 1934, <>
    line 2 (#1)
  (W uninitialized) An undefined value was used as if it were already
  defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
  To suppress this warning assign a defined value to your variables.
 
  To help you figure out what was undefined, perl will try to tell you
  the name of the variable (if any) that was undefined.  In some cases
  it cannot do this, so it also tells you what operation you used the
  undefined value in.  Note, however, that perl optimizes your program
  anid the operation displayed in the warning may not necessarily appear
  literally in your program.  For example, "that $foo" is usually
  optimized into "that " . $foo, and the warning will refer to the
  concatenation (.) operator, even though there is no . in
  your program.
 
Use of uninitialized value in division (/) at /bin/mysqltuner line 1934, <>
    line 2 (#1)
Use of uninitialized value $mystat{"Qcache_lowmem_prunes"} in numeric eq (==)
    at /bin/mysqltuner line 1949, <> line 2 (#1)
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 37m 3s (12 q [0.002 qps], 20 conn, TX: 20K, RX: 2K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 168.0M global + 1.1M per thread (151 max threads)
[OK] Maximum reached memory usage: 169.1M (4.78% of installed RAM)
[OK] Maximum possible memory usage: 341.4M (9.65% of installed RAM)
[OK] Slow queries: 0% (0/12)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 40.00%  (8/20)
Use of uninitialized value $myvar{"query_cache_size"} in numeric lt (<) at
        /bin/mysqltuner line 2281, <> line 2 (#1)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 95% (1 created / 20 connections)
[OK] Table cache hit rate: 83% (118 open / 142 opened)
[OK] Open file limit used: 0% (2/10K)
[OK] Table locks acquired immediately: 100% (2 immediate / 2 locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)
 
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user
 
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 11.08% (908 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 94.07% (12243 hits/ 13015 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12 writes)
 
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
 
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
 
-------- Recommendations -----------------------------------------------------
General recommendations:
  MySQL started within last 24 hours - recommendations may be inaccurate
  Reduce or eliminate unclosed connections and network issues
Variables to adjust:
  query_cache_size (>= 8M)
[linuxuser@centos7-hostadvice4 ~]$

Focus on the last part of the output in section “Recommendations” and check the variables that need to be increased.

Create a backup of the mysql configuration file and edit the actual configuration file.

$ sudo cp /etc/my.cnf /etc/my.cnf.bak
$ sudo vim /etc/my.cnf

Adjust the file’s variable according to the recommendations. You can comment out the default variable values and increase the variable values to twice the default value. Actually, it may take a few days to come up with the best values for your server.

After making the changes, restart mysql server.

$ sudo systemctl restart mysql

Special Note: If it fails to restart, go back and comment out the new values and start adjusting one by one and see which variable adjustment causes the server failure.

If server successfully reloads, rerun mysqltuner and see if there are any more recommendations concerning the variable values adjustment.

Do similar process for tuning primer.

Run tuning primer

$ sudo tuning-primer

[linuxuser@centos7-hostadvice4 tuning-primer]$ sudo ./tuning-primer.sh
 -- MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 --
      - By: Matthew Montgomery -
      - By: Markus Kohlmeyer   -
 
MySQL Version 8.0.11 x86_64
 
Uptime = 0 days 0 hrs 2 min 52 sec
Avg. qps = 0
Total Questions = 4
Threads Connected = 1
 
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
 
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
 
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 25 that take longer than 10.000000 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.
 
BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html
 
WORKER THREADS
Current thread_cache_size = 9
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
 
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 1
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
 
INNODB STATUS
Current InnoDB index space = 240 K
Current InnoDB data space = 2 M
Current InnoDB buffer pool free = 88 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
 
MEMORY USAGE
Max Memory Ever Allocated : 153 M
Configured Max Per-thread Buffers : 178 M
Configured Max Global Buffers : 152 M
Configured Max Memory Limit : 330 M
Plus 16 M per temporary table created
Physical Memory : 3.45 G
Max memory limit seem to be within acceptable norms
 
KEY BUFFER
No key reads?!
Seriously look into using some indexes
Current MyISAM index space = 0 bytes
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
 
QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
 
SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
No sort operations have been performed
Sort buffer seems to be fine
 
JOINS
Current join_buffer_size = 260.00 K
You have had 3 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
 
OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_open_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
 
TABLE CACHE
Current table_open_cache = 4000 tables
Current table_definition_cache = 2000 tables
You have a total of 136 tables
You have 165 open tables.
The table_open_cache value seems to be fine
 
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 95 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine
 
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 793 : 1
read_buffer_size seems to be fine
 
TABLE LOCKING
Current Lock Wait ratio = 0 : 286
Your table locking seems to be fine
 
[linuxuser@centos7-hostadvice4 tuning-primer]$

Special Note: Focus more on the red and yellow highlighted text as it gives recommendations on how to optimize and improve mysql performance.

Adjust the file’s variable according to the recommendations. You can comment out the default variable values and adjust the variable values as suggested in the recommendations.

After making the changes, restart MySQL server.

$ sudo systemctl restart mysql

Special Note: If it fails to restart, go back and comment out the new values and start adjusting one by one and see which variable adjustment causes the server failure.

If server successfully restarts, rerun tuneprimer and see if there are any more recommendations concerning the variable values adjustment.

Conclusion

It is very important to note that the tuning tools mysqltuner or tuning-primer are not the solutions to poorly performing MySQL server, but rather scripts to guide you to fine tune the MySQL server.

For best performance,

Do a thorough review of queries sent to the server and optimize database structure/design and SQL query
Evaluate the factors affecting MySQL performance as explained in the introduction.
Consider upgrading hardware of the physical server for better performance and optimize MySQL configurations based on the hardware specifications. Consult HostAdvice for the best MySQL hosting options.
Do more research on how to tune MySQL configurations based on applications utilizing MySQL server.

It is highly recommended to regularly run mysqltuner or tuning-primer tool over 48 hr span after restarting MySQL server, so as to obtain meaningful usage statistic.