The following tutorial describes the steps to optimize the performance of a MySQL database with the mysqltuner script.
Login to your server on the shell, then execute the following commands:
Download the MySQL tuner script:
cd /usr/local/bin wget http://mysqltuner.pl/mysqltuner.pl chmod +x mysqltuner.pl
Run mysqltuner:
/usr/local/bin/mysqltuner.pl
Then enter root as the username and the MySQL root password.
You will get a output similar to this:
root@v221:/usr/local/bin# /usr/local/bin/mysqltuner.pl
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny2
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 26M (Tables: 215)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 33
-------- Performance Metrics -------------------------------------------------
[--] Up for: 96d 23h 3m 41s (10M q [1.239 qps], 686K conn, TX: 701M, RX: 1B)
[--] Reads / Writes: 44% / 56%
[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 320.5M (12% of installed RAM)
[OK] Slow queries: 0% (20/10M)
[OK] Highest usage of available connections: 33% (33/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/8.5M
[OK] Key buffer hit rate: 99.9% (57M cached / 30K reads)
[OK] Query cache efficiency: 78.6% (5M cached / 6M selects)
[!!] Query cache prunes per day: 483
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 408K sorts)
[!!] Temporary tables created on disk: 36% (269K on disk / 745K total)
[OK] Thread cache hit rate: 99% (427 created / 686K connections)
[!!] Table cache hit rate: 2% (64 open / 3K opened)
[OK] Open file limit used: 11% (120/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)
The script recommends to adjust or add the following variables in the mysql my.cnf file. The location of my.cnf is normally /etc/my.cnf or /etc/mysql/my.cnf depending on the Linux distribution that is installed on your server.
Open the my.cnf file:
vi /etc/mysql/my.cnf
and increase or set the variables in the [mysqld] section of the file. Mine looks now like this:
[mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking # # 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 # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 100 table_cache = 128 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 32M tmp_table_size = 64M max_heap_table_size = 32M
Then save the file and restart mysql. After a few hours, rerun mysqltuner and check again if the values are fine now or if the 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