MySQL配置文件调优

配置文件查找请参照：https://blog.csdn.net/fdipzone/article/details/52705507

修改前：

[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
user=mysql
port=3306
lower_case_table_names=1
character-set-server=utf8
symbolic-links=0
skip-name-resolve
net_write_timeout=600
wait_timeout=31536000
interactive_timeout=31536000
collation-server=utf8_general_ci
skip-external-locking
key_buffer=512M
max_allowed_packet=16M
thread_stack=192K
thread_cache_size=8
read_rnd_buffer_size=2560M
sort_buffer_size=2560M
join_buffer_size=2560M
innodb_buffer_pool_size=6G
read_buffer_size=2560M
myisam-recover=BACKUP
tmp_table_size=512M
max_connections=1500
query_cache_limit=256M
query_cache_size=1024M
expire_logs_days=10
max_binlog_size=100M
[client]
port=3306
default-character-set=utf8mb4
socket=/home/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
nice=0
[mysqldump]
quick
quote-names
max_allowed_packet=1024M
[mysql]

 

修改后：

[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
user=mysql
port=3306
lower_case_table_names=1
character-set-server=utf8
symbolic-links=0
skip-name-resolve
net_write_timeout=600
wait_timeout=31536000
interactive_timeout=31536000
collation-server=utf8_general_ci
skip-external-locking
max_allowed_packet=16M
thread_stack=1M
sort_buffer_size=16M
join_buffer_size=16M
innodb_buffer_pool_size=64G
innodb_buffer_pool_instances=64
innodb_log_file_size=4G
innodb_log_buffer_size=4G
innodb_purge_threads=16
innodb_read_io_threads=16
innodb_write_io_threads=16
read_buffer_size=2560M
myisam-recover=BACKUP
tmp_table_size=512M
max_connections=1500
query_cache_limit=256M
query_cache_size=1024M
[client]
port=3306
default-character-set=utf8mb4
socket=/home/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
nice=0
[mysqldump]
quick
quote-names
max_allowed_packet=1024M
[mysql]
default-character-set=utf8mb4
[isamchk]
key_buffer=16M