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.

Optimize MySQL performance with mysqltuner
Tagged on:

9 thoughts on “Optimize MySQL performance with mysqltuner

  • Permalink

    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?

    Reply
    • Permalink

      tmp_table_sizee = 64M
      max_heap_table_sizee = 32M

      remove extra e from “size”

      Reply
  • Permalink

    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

    Reply
    • Permalink

      [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

      Reply
  • Permalink

    there’s a similar tool/app very handy and cheap for mac users mysql optimizer goo.gl/mt8VWj

    Reply
  • Permalink

    >> 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

    Reply

Leave a Reply

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

*

Ad #native_company# — #native_desc# #native_cta#