MySQL调优一些了解

一些常见的参数了解

key_buffer_size = 32M			//索引块的缓冲区大小
read_buffer_size = 2M      			//读入缓冲区大小
read_rnd_buffer_size = 4M			//sort查询后使用的读入缓存
table_open_cache = 1000			//表打开缓存
table_definition_cache = 1000  		//表定义文件缓存
table_open_cache_instances = 64		//表打开缓存实例数，默认是16
tmp_table_size = 96M		//临时表缓存
max_heap_table_size = 96M	//与tmp_table_size一起使用，以限制内部内存表的大小
thread_stack = 512K			//默认是64KB.如果线程堆栈太小,则会限制服务器可以处理的SQL语句的复杂性
max_allowed_packet = 1024M		//指mysql服务器端和客户端最大允许的数据包大小
sort_buffer_size = 4M			//排序缓存默认256K
join_buffer_size = 2M			//多表查询缓存，默认128K,1万并发需大概12M大小

query_cache_size = 0		//#查询缓存大小
query_cache_type = 0		//#查询缓存，默认值是0，关闭。高并发，写入量大的系统，建议把该功能禁掉。
query_cache_min_res_unit=		#单个查询能够使用的缓冲区大小，缺省为1M

external-locking = FALSE    //外部锁定,打开时服务器访问数据表时经常需要等待解锁,会让MySQL性能下降。
max_connections = 500			//允许的同时客户的数量，默认数值是100
max_connect_errors = 1000000   //连接不成功的最大尝试次数
thread_cache_size = 1200			//#线程缓存中线程数
back_log = 1024				//暂时停止回答新请求之前，短时间内可以被存在堆栈中连接数量
interactive_timeout = 600     //交互连接上等待行动的秒数。默认数值是28800
wait_timeout = 600			//链接等待时间

slow_query_log = 1			//是否启用慢查询日志，1为启用，0为禁用
long_query_time = 0.5		//慢查询时间  范围0-10
log-bin = /data/mysql/mybinlog
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
sync_binlog = 1
binlog_cache_size = 4M			//binlog缓存，在事务过程中用来存储二进制日志的缓存
max_binlog_cache_size = 2G		//执行多语句事务的时候 所有session的使用的最大内存
max_binlog_size = 1G		//默认大小是1G
expire_logs_days = 7    //超过保留时间的binlog日志会被自动删除

示例1

[mysqld]
user = mysql
server_id = 1
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql/data1
lower_case_table_names = 1    #表面区分大小写
character-set-server = utf8
default-storage-engine = INNODB
log-bin = bin.log
log-error = error.log
pid-file = /tmp/mysql.pid
#开启慢查询
long_query_time = 2
slow_query_log = 'on'
slow_query_log_file = /data/mysql/data1/slow.log
#binlog日志的缓存内存、格式、最大使用内存、日志大小、保留时间
binlog_cache_size = 4M
binlog_format = mixed
max_binlog_cache_size = 16M
max_binlog_size = 1G
expire_logs_days = 30
ft_min_word_len = 4  
back_log = 512    #在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中
#最大数据传输，最大连接数
max_allowed_packet = 64M
max_connections = 4096
max_connect_errors = 100		#网络异常而中止数据库连接
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
query_cache_size = 64M
table_open_cache = 10000
thread_cache_size = 256
max_heap_table_size = 64M
tmp_table_size = 64M
thread_stack = 192K
thread_concurrency = 24
local-infile = 0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout = 600
interactive_timeout = 600
wait_timeout = 600
#*** MyISAM
key_buffer_size = 512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
concurrent_insert = 2
myisam_recover
#*** INNODB
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 16
innodb_open_files = 1024
read-only
relay-log = relay.log
log-slave-updates
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
replicate-do-db =all

示例2

[mysqld]
binlog-ignore-db=mysql
binlog-ignore-db=test
#report_host=
skip-slave-start
#skip-grant-tables
#log_slave_updates
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
user    = mysql
port    = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket  = /data/mysql/mysql.sock
pid-file = mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024				//暂时停止回答新请求之前，短时间内可以被存在堆栈中连接数量
max_connections = 500			//允许的同时客户的数量，默认数值是100
max_connect_errors = 1000000   //连接不成功的最大尝试次数
table_open_cache = 1000			//表打开缓存
table_definition_cache = 1000  		//表定义文件缓存
table_open_cache_instances = 64		//表打开缓存实例数，默认是16
thread_stack = 512K			//默认是64KB.如果线程堆栈太小,则会限制服务器可以处理的SQL语句的复杂性
external-locking = FALSE    //外部锁定,打开时服务器访问数据表时经常需要等待解锁,会让MySQL性能下降。
max_allowed_packet = 1024M		//指mysql服务器端和客户端最大允许的数据包大小
sort_buffer_size = 4M			//排序缓存默认256K
join_buffer_size = 2M			//多表查询缓存，默认128K,1万并发需大概12M大小
thread_cache_size = 1200			//#线程缓存中线程数
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600     //交互连接上等待行动的秒数。默认数值是28800
wait_timeout = 600			//链接等待时间
tmp_table_size = 96M		//临时表缓存
max_heap_table_size = 96M			//同时具有读/写属性的系统变量，与tmp_table_size一起使用，以限制内部内存表的大小
slow_query_log = 1			//是否启用慢查询日志，1为启用，0为禁用
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.5		//慢查询时间  范围0-10
server-id = 3306
log-bin = /data/mysql/mybinlog
sync_binlog = 1
binlog_cache_size = 4M			//binlog缓存，在事务过程中用来存储二进制日志的缓存
max_binlog_cache_size = 2G		//执行多语句事务的时候 所有session的使用的最大内存
max_binlog_size = 1G		//默认大小是1G
expire_logs_days = 7    //超过保留时间的binlog日志会被自动删除
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M			//索引块的缓冲区大小
read_buffer_size = 2M      			//读入缓冲区大小
read_rnd_buffer_size = 4M			//sort查询后使用的读入缓存
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 35
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_status_file = 1
innodb_status_output = 1
innodb_status_output_locks = 1
innodb_stats_on_metadata = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

#半同步
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

[mysqldump]
quick
max_allowed_packet = 1024M