MySQLTuner CentOS7

MySQLTunerインストール

# wget -O /usr/local/src/MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip

# unzip /usr/local/src/MySQLTuner.zip -d /usr/local/src/

# rm -f /usr/local/src/MySQLTuner.zip

実行

# /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl -user root



 >>  MySQLTuner 1.7.9 - 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
[!!] Attempted to use login credentials, but they were invalid
[root@localhost MySQLTuner-perl-master]# /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl
 >>  MySQLTuner 1.7.9 - 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:※パスワード入力



[OK] Currently running supported MySQL version 5.7.22-log
[OK] Operating on 64-bit architecture

(略)

設定例

LAMP環境
CentOS7
MySQL5.7
メモリ32GB
ストレージエンジン：InnoDB

# vi /etc/my.cnf


[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock



# General Setting
log_timestamps = SYSTEM
validate-password = OFF
character-set-server = utf8
default_password_lifetime = 0
skip-name-resolve
max_allowed_packet= 20M


# Binlog
server-id=1
log_bin=/var/lib/mysql/mysql-bin
binlog-format = MIXED
expire_logs_days=10
binlog_do_db = sampledb


# Global Buffer
innodb_buffer_pool_size = 20G
innodb_log_file_size = 5G
innodb_log_files_in_group = 2

# InnoDB
default-storage-engine = innodb
innodb_data_home_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql
innodb_file_per_table = ON
innodb_lock_wait_timeout = 10


# Thread Buffer
thread_cache_size = 342
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 2M
sort_buffer_size = 4M


# Query Cache
query_cache_limit = 16M
query_cache_size = 512M #Global Buffer
query_cache_type = 1


# Heap
max_heap_table_size = 300M
tmp_table_size = 300M


# Connection
max_connections = 1024
wait_timeout = 60
back_log = 1024