This short tutorial describes the steps to optimize the performance of a MySQL database with the mysqltuner script. This tool can be used for MySQL and MariaDB.
Install mysqltuner
The mysqltuner High-Performance MySQL Tuning Script is available as a package on Debian 10 and Ubuntu 20.04, install it with at:
sudo apt install mysqltuner
For other Distributions, download and install mysqltuner as described below:
cd /usr/local/bin wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl mv mysqltuner.pl mysqltuner chmod +x mysqltuner
Optimize MySQL Performance
Run mysqltuner:
mysqltuner
Then enter root as the username and the MySQL root password.
You will get an output similar to this:
root@server1:/# mysqltuner >> MySQLTuner 1.7.13 - 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 [OK] Logged in using credentials from Debian maintenance account. [!!] Currently running unsupported MySQL version 8.0.23-0ubuntu0.20.04.1 [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/log/mysql/error.log(4K) [OK] Log file /var/log/mysql/error.log exists [OK] Log file /var/log/mysql/error.log is readable. [OK] Log file /var/log/mysql/error.log is not empty [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb [!!] /var/log/mysql/error.log contains 6 warning(s). [!!] /var/log/mysql/error.log contains 3 error(s). [--] 4 start(s) detected in /var/log/mysql/error.log [--] 1) 2021-04-28T08:51:46.032696Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu). [--] 2) 2021-04-28T08:51:45.475316Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock [--] 3) 2021-04-28T08:51:41.150692Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060 [--] 4) 2021-04-28T08:51:37.470204Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/tmp/tmp.q7NxVUnzSo/mysqld.sock' port: 0 (Ubuntu). [--] 2 shutdown(s) detected in /var/log/mysql/error.log [--] 1) 2021-04-28T08:51:42.544794Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23-0ubuntu0.20.04.1) (Ubuntu). [--] 2) 2021-04-28T08:51:38.956051Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23-0ubuntu0.20.04.1) (Ubuntu). -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 16.0K (Tables: 1) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Security Recommendations ------------------------------------------------------------------ [--] Skipped due to unsupported feature for MySQL 8 -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 5m 1s (15 q [0.050 qps], 13 conn, TX: 56K, RX: 1K) [--] Reads / Writes: 100% / 0% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 1.9G [--] Max MySQL memory : 349.4M [--] Other process memory: 162.7M [--] Total buffers: 176.0M global + 1.1M per thread (151 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 177.1M (8.94% of installed RAM) [OK] Maximum possible memory usage: 349.4M (17.64% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/15) [OK] Highest usage of available connections: 0% (1/151) [OK] Aborted connections: 0.00% (0/13) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [--] Query cache have been removed in MySQL 8 [OK] No Sort requiring temporary tables [OK] No joins without indexes [OK] Temporary tables created on disk: 0% (0 on disk / 4 total) [OK] Thread cache hit rate: 92% (1 created / 13 connections) [OK] Table cache hit rate: 56% (104 open / 185 opened) [OK] Open file limit used: 0% (2/10K) [OK] Table locks acquired immediately: 100% (4 immediate / 4 locks) [OK] Binlog cache memory access: 0% (0 Memory / 0 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (3M used / 16M cache) [!!] Cannot calculate MyISAM index size - re-run script as root user -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 128.0M/16.0K [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 94.53% (13840 hits/ 14641 total) [OK] InnoDB Write log efficiency: 98.44% (632 hits/ 642 total) [OK] InnoDB log waits: 0.00% (0 waits / 10 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: ROW [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysql/error.log file Control error line(s) into /var/log/mysql/error.log file MySQL was started within the last 24 hours - recommendations may be inaccurate Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. query_cache_size (> 16M) tmp_table_size (> 32M) max_heap_table_size (> 16M) table_cache (> 64)
The script recommends adjusting or adding the following variables in the MySQL configuration file. On current Debian and Ubuntu versions, the file is /etc/mysql/mysql.conf.d/mysql.cnf for MySQL and /etc/mysql/mariadb.conf.d/50-server.cnf for MariaDB.Other common paths are just /etc/my.cnf and /etc/mysql/my.cnf, these are commonly used on older Linux versions and on CentOS.
Adjust MySQL configuration
Open my.cnf file:
nano /etc/mysql/mysql.conf.d/mysql.cnf
and increase or set the variables in the [mysqld] section of the file. Mine looks now like this:
# # The MySQL database server configuration file. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # Here is entries for some specific programs # The following values assume you have at least 32M ram [mysqld] # # * Basic Settings # user = mysql # pid-file = /var/run/mysqld/mysqld.pid # socket = /var/run/mysqld/mysqld.sock # port = 3306 # datadir = /var/lib/mysql # If MySQL is running as a replication slave, this should be # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir # tmpdir = /tmp # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 mysqlx-bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 16M # max_allowed_packet = 64M # thread_stack = 256K # thread_cache_size = -1 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP # max_connections = 151 # table_open_cache = 4000 # # * Logging and Replication # # Both location gets rotated by the cronjob. # # Log all queries # Be aware that this log type is a performance killer. # general_log_file = /var/log/mysql/query.log # general_log = 1 # # Error log - should be very few entries. # log_error = /var/log/mysql/error.log # # Here you can see queries with especially long duration # slow_query_log = 1 # slow_query_log_file = /var/log/mysql/mysql-slow.log # long_query_time = 2 # log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. # server-id = 1 # log_bin = /var/log/mysql/mysql-bin.log # binlog_expire_logs_seconds = 2592000 max_binlog_size = 100M # binlog_do_db = include_database_name # binlog_ignore_db = include_database_name query_cache_limit = 1M query_cache_size = 32M tmp_table_size = 64M max_heap_table_size = 32M
Then save the file and restart MySQL.
systemctl restart mysql
Run mysqltuner again after a few hours. Check if the values are fine now or if they have to be increased to a higher value.