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
ポイント
- innodb_buffer_pool_size 物理メモリ8割 or 2年後のDBサイズ * 1.2
- innodb_log_file_size = innodb_buffer_pool_size/4
- innodb_log_file_size * innodb_log_files_in_group < innodb_buffer_pool_size
- thread_cache_size = max_connections/3
- max_heap_table_size = tmp_table_size