MySQL5.7.31性能最適化記録

一、構成の最適化
1、innodb_buffer_pool_instances
範囲は1(the default)up to 64(the maximum)である.
innodb_buffer_pool_instancesの個数はbuffer pool sizeの10分の1に設定されています.例えばinnodb_buffer_pool_sizeは30 gでinnodb_buffer_pool_instancesは3に設定されています.
2、innodb_buffer_pool_size 
追加メモリの使用があるためinnodb_を指定するとbuffer_pool_sizeが12 gの実際の消費メモリは14 g+である可能性があります
二、事務ログの構成
innodb_log_file_size
デフォルト値48 MBの設定が小さすぎます.たとえば、デフォルト値48 MBでは、1つのログ・ファイルがいっぱいになると、innodbが自動的に別のログ・ファイルに切り替わり、データベースのチェックポイントがトリガーされます.これにより、innodbキャッシュの汚れたページの小ロット・リフレッシュが発生し、innodbのパフォーマンスが著しく低下します.
設定が大きすぎる:設定が大きくなるとcheckpointが減少し、redo logが順次I/Oであるため、I/O性能が大幅に向上する.しかし、データベースに予期せぬダウンタイムなどの問題が発生した場合、ログを再生し、コミットされたトランザクションをリカバリする必要があります.ログが大きい場合、リカバリ時間が長くなります.私たちが受け入れられないほどだ.
まとめ:innodb_log_file_sizeの設定が小さすぎてデータベースのパフォーマンスを解放できず、設定が大きすぎると、ダウンタイム後のログ再生のリカバリ時間が増加します.
innodb_log_files_in_group
REDOログ・グループのログ数、デフォルトは2です.一般的にはデフォルトでもかまいません.
innodb_log_buffer_size
ディスク・ログ・ファイルにログを書き込む前のバッファ・サイズは、デフォルト値8 MBで、一般的にはデフォルト値でもかまいません.
三、読み書きスレッドの増加
innodb_write_io_threads innodb_read_io_threads innodb_thread_concurrency
四、例

[mysql]
#   mysql        
default-character-set=utf8
[mysqld]

bind-address = 193.193.193.41

skip-name-resolve
#  3306  
port = 3306
#   mysql     
basedir=/opt/soft/mysql57
#   mysql           
datadir=/opt/soft/mysql57/data
#        
max_connections=1000
#             8     latin1   
character-set-server=utf8
#                
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M

#       ，      ：8G
innodb_buffer_pool_size=4096M
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2

#   ssl
skip-ssl

#    
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow_query2.log
long_query_time=0.2 #   

注:mysqlを再起動しても遅いsqlが有効にならない場合は、一時的に次のコマンドを有効にすることもできます.

mysql> set global slow_query_log=1;

#            
mysql> show variables  like '%query%';