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:
[email protected]:/# mysqltuner >> MySQLTuner 1.7.13 - Major Hayden <[email protected]> >> 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.
i did run this, and fixed the errors on the script, but the command service mysqld restart did not could start again the db, what could happen?
tmp_table_sizee = 64M
max_heap_table_sizee = 32M
remove extra e from “size”
got it, i used, for example, 1.6gb on one line, changed it by mb
I have 17gb of ram, which one can be my best config for high traffic? i would like to have an idea in case i change my amount of ram
Hi
What will be values for 2 GB ram VPS?
[OK] Logged in using credentials from debian maintenance account.
>> MySQLTuner 1.6.1 – Major Hayden
>> 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] Currently running supported MySQL version 5.5.46-0ubuntu0.14.04.2
[OK] Operating on 64-bit architecture
——– Storage Engine Statistics ——————————————-
[–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 37K (Tables: 12)
[–] Data in InnoDB tables: 301M (Tables: 249)
[!!] Total fragmented tables: 16
——– Security Recommendations ——————————————-
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
——– Performance Metrics ————————————————-
[–] Up for: 7m 15s (45K q [103.579 qps], 272 conn, TX: 88M, RX: 4M)
[–] Reads / Writes: 85% / 15%
[–] Binary logging is disabled
[–] Total buffers: 1.5G global + 10.4M per thread (200 max threads)
[OK] Maximum reached memory usage: 1.6G (79.60% of installed RAM)
[!!] Maximum possible memory usage: 3.5G (181.18% of installed RAM)
[OK] Slow queries: 0% (0/45K)
[OK] Highest usage of available connections: 2% (4/200)
[OK] Aborted connections: 0.00% (0/272)
[OK] Query cache efficiency: 59.8% (23K cached / 40K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5 sorts)
[!!] Temporary tables created on disk: 84% (997 on disk / 1K total)
[OK] Thread cache hit rate: 98% (4 created / 272 connections)
[OK] Table cache hit rate: 97% (307 open / 314 opened)
[OK] Open file limit used: 5% (72/1K)
[OK] Table locks acquired immediately: 100% (18K immediate / 18K locks)
——– MyISAM Metrics —————————————————–
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/138.0K
[!!] Read Key buffer hit rate: 80.0% (5 cached / 1 reads)
——– InnoDB Metrics —————————————————–
[–] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.0G/301.7M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 24.18% (15844 used/ 65535 total)
[OK] InnoDB Read buffer efficiency: 99.81% (7394483 hits/ 7408375 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2690 writes)
——– AriaDB Metrics —————————————————–
[–] AriaDB is disabled.
——– Replication Metrics ————————————————-
[–] No replication slave(s) for this server.
[–] This is a standalone server..
——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
What will be the settings for a 2GB RAM
Just a small correction:
It’s:
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
instead of
wget http://mysqltuner.pl/mysqltuner.pl
there’s a similar tool/app very handy and cheap for mac users mysql optimizer goo.gl/mt8VWj
>> MySQLTuner 1.7.14 – Major Hayden
>> 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] Currently running supported MySQL version 10.2.21-MariaDB
[OK] Operating on 64-bit architecture
——– Log file Recommendations ——————————————————————
[–] Log file: /var/lib/mysql/mvpiot.novalocal.err(513K)
[OK] Log file /var/lib/mysql/mvpiot.novalocal.err exists
[OK] Log file /var/lib/mysql/mvpiot.novalocal.err is readable.
[OK] Log file /var/lib/mysql/mvpiot.novalocal.err is not empty
[OK] Log file /var/lib/mysql/mvpiot.novalocal.err is smaller than 32 Mb
[!!] /var/lib/mysql/mvpiot.novalocal.err contains 1146 warning(s).
[!!] /var/lib/mysql/mvpiot.novalocal.err contains 2355 error(s).
[–] 37 start(s) detected in /var/lib/mysql/mvpiot.novalocal.err
[–] 1) 2019-01-12 8:25:46 140431271381184 [Note] /usr/sbin/mysqld: ready for connections.
[–] 2) 2019-01-12 7:29:03 140574858127552 [Note] /usr/sbin/mysqld: ready for connections.
[–] 3) 2019-01-11 18:39:26 140560611846336 [Note] /usr/sbin/mysqld: ready for connections.
[–] 4) 2019-01-11 17:00:17 140623437183168 [Note] /usr/sbin/mysqld: ready for connections.
[–] 5) 2019-01-11 16:30:08 140674500737216 [Note] /usr/sbin/mysqld: ready for connections.
[–] 6) 2019-01-11 14:55:17 140065307875520 [Note] /usr/sbin/mysqld: ready for connections.
[–] 7) 2019-01-11 13:34:41 140588138420416 [Note] /usr/sbin/mysqld: ready for connections.
[–] 8) 2019-01-11 13:01:12 139932833945792 [Note] /usr/sbin/mysqld: ready for connections.
[–] 9) 2019-01-11 12:27:23 140694443542720 [Note] /usr/sbin/mysqld: ready for connections.
[–] 10) 2019-01-11 12:08:55 139655472789696 [Note] /usr/sbin/mysqld: ready for connections.
[–] 9 shutdown(s) detected in /var/lib/mysql/mvpiot.novalocal.err
[–] 1) 2019-01-10 18:03:26 139706578528000 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 2) 2019-01-10 17:48:26 140143107737344 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 3) 2019-01-10 17:45:56 140094187407104 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 4) 2019-01-10 17:42:51 139985407059712 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 5) 2019-01-10 17:26:12 140542010558208 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 6) 2019-01-10 17:26:09 140119564142336 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 7) 2019-01-10 13:42:59 140154423613184 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 8) 2019-01-10 9:21:20 140596763801344 [Note] /usr/sbin/mysqld: Shutdown complete
[–] 9) 2019-01-10 9:21:18 139833937012480 [Note] /usr/sbin/mysqld: Shutdown complete
——– Storage Engine Statistics —————————————————————–
[–] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[–] Data in Aria tables: 192.0K (Tables: 6)
[–] Data in MyISAM tables: 15.5M (Tables: 85)
[–] Data in InnoDB tables: 140.7M (Tables: 87)
[OK] Total fragmented tables: 0
——– Analysis Performance Metrics ————————————————————–
[–] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
——– Security Recommendations ——————————————————————
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
——– CVE Security Recommendations ————————————————————–
[–] Skipped due to –cvefile option undefined
——– Performance Metrics ———————————————————————–
[–] Up for: 1h 26m 8s (432K q [83.607 qps], 20K conn, TX: 710M, RX: 32M)
[–] Reads / Writes: 97% / 3%
[–] Binary logging is disabled
[–] Physical Memory : 3.9G
[–] Max MySQL memory : 2.9G
[–] Other process memory: 0B
[–] Total buffers: 672.0M global + 4.5M per thread (500 max threads)
[–] P_S Max memory usage: 0B
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1016.7M (25.74% of installed RAM)
[OK] Maximum possible memory usage: 2.9G (74.41% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/432K)
[OK] Highest usage of available connections: 15% (76/500)
[OK] Aborted connections: 0.39% (82/20849)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 39.5% (237K cached / 602K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 129 sorts)
[!!] Joins performed without indexes: 23
[OK] Temporary tables created on disk: 21% (296 on disk / 1K total)
[OK] Thread cache hit rate: 99% (76 created / 20K connections)
[OK] Table cache hit rate: 94% (319 open / 339 opened)
[OK] Open file limit used: 1% (188/10K)
[OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)
——– Performance schema ————————————————————————
[–] Performance schema is disabled.
[–] Memory used by P_S: 0B
[–] Sys schema is installed.
——– ThreadPool Metrics ————————————————————————
[–] ThreadPool stat is enabled.
[–] Thread Pool Size: 2 thread(s).
[–] Using default value is good enough for your version (10.2.21-MariaDB)
——– MyISAM Metrics —————————————————————————-
[!!] Key buffer used: 18.4% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/9.4M
[OK] Read Key buffer hit rate: 99.4% (748K cached / 4K reads)
[!!] Write Key buffer hit rate: 1.3% (255K cached / 3K writes)
——– InnoDB Metrics —————————————————————————-
[–] InnoDB is enabled.
[–] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/140.7M
[!!] 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: 100.00% (532580940 hits/ 532588160 total)
[!!] InnoDB Write Log efficiency: 51.86% (13396 hits/ 25832 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12436 writes)
——– AriaDB Metrics —————————————————————————-
[–] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/96.0K
[!!] Aria pagecache hit rate: 79.6% (1K cached / 302 reads)
——– 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: MIXED
[–] 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/lib/mysql/mvpiot.novalocal.err file
Control error line(s) into /var/lib/mysql/mvpiot.novalocal.err 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
Adjust your join queries to always utilize indexes
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
join_buffer_size (> 1.0M, or always use indexes with JOINs)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 140.7M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
My Cpu :2core
Ram : 4Gb
people please help to suggest my.cnf
Thanks a lot