MySQL Tuner Settings for Question – Answer Dynamic Website

MySQLmysql-5.7mysqltuner

I ran the mysqltuner script on my server that has been working continuously for 1 week.

I share the values I got and the existing mysql configuration file.
Could you help me? When I looked on the internet, I couldn't see a very detailed solution.

MySQL Tuner Output

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 5.7.32-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[!!] log_error is set to stderr MT can't read stderr

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 173.9K (Tables: 5)
[--] Data in InnoDB tables: 1.0G (Tables: 110)
[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
[!!] User 'root@localhost' has no password set.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 6h 34m 46s (40M q [143.364 qps], 346K conn, TX: 133G, RX: 8G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory     : 3.9G
[--] Max MySQL memory    : 51.7G
[--] Other process memory: 0B
[--] Total buffers: 1.4G global + 257.8M per thread (200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 4.7G (121.05% of installed RAM)
[!!] Maximum possible memory usage: 51.7G (1342.95% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/40M)
[OK] Highest usage of available connections: 6% (13/200)
[OK] Aborted connections: 0.00%  (0/346968)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 72.5% (27M cached / 38M selects)
[!!] Query cache prunes per day: 105070
[OK] Sorts requiring temporary tables: 0% (720 temp sorts / 421K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 18% (35K on disk / 188K total)
[OK] Thread cache hit rate: 99% (13 created / 346K connections)
[OK] Table cache hit rate: 95% (1K open / 1K opened)
[OK] table_definition_cache(2048) is upper than number of tables(394)
[OK] Open file limit used: 0% (48/5K)
[OK] Table locks acquired immediately: 100% (789 immediate / 789 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (48M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/102.0K
[OK] Read Key buffer hit rate: 99.8% (8K cached / 16 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 4
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.0G/1.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 8 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: 99.97% (2850068636 hits/ 2850872999 total)
[!!] InnoDB Write Log efficiency: 23.15% (238067 hits/ 1028533 total)
[OK] InnoDB log waits: 0.00% (0 waits / 790466 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:
    Set up a Secure Password for root@localhost user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password');
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Increasing the query_cache size over 128M may reduce performance
    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:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 128M) [see warning above]
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 1.0G) if possible.
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

MySQL Configuration File

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# 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

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[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      = /var/tmpfs
tmp_table_size=2K
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 127.0.0.1

performance_schema=off
skip-networking
tmpdir=/tmp
max_connections=200
max_user_connections=200
key_buffer_size=256M
myisam_sort_buffer_size=128M
join_buffer_size=1M
read_buffer_size=128K
sort_buffer_size=128K
table_open_cache=3072
thread_cache_size=1024
table_definition_cache=2048
open_files_limit=52000
wait_timeout=120
connect_timeout=120
max_heap_table_size=128M
max_allowed_packet=268435456
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
interactive_timeout=120
max_connect_errors=15
local-infile=0
#innodb_additional_mem_pool_size=64M
innodb_flush_method=O_DIRECT
default-storage-engine=InnoDB
innodb_buffer_pool_size=1000M
innodb_buffer_pool_instances=2  
innodb_log_buffer_size=16M
innodb_file_per_table=1
innodb_file_format = barracuda
innodb_thread_concurrency=4
innodb_flush_log_at_trx_commit = 1
innodb_write_io_threads=2
innodb_read_io_threads=2

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

[isamchk]
key_buffer=512M
sort_buffer_size=2M
read_buffer=2M
write_buffer=2M

[myisamchk]
tmpdir=/tmp
key_buffer=512M
sort_buffer=2M
read_buffer=2M
write_buffer=2M

I would be glad if you help. What settings should I change?

Best Answer

What is the problem? If it is "slow" queries, then we need to look at them. Some tunables are higher than needed, so here are some recommended changes:

key_buffer_size = 50M
max_connections = 100
table_open_cache = 1000
table_definition_cache = 400
temp_table_size = 40M
max_heap_table_size = 40M
innodb_buffer_pool_instances = 1
query_cache_size = 40M
thread_cache_size = 10
innodb_buffer_pool_size = 1200M

For a deeper analysis: http://mysql.rjweb.org/doc.php/mysql_analysis