Mysql参数整理

Mysql 参数整理

general:
user：启动mysql domain的用户
port：数据库端口号
socket：数据库socket文件路径
pid_file：数据库启动进程文件的路径
datadir：数据文件路径
tmpdir：临时文件路径
log_bin：配置二进制文件名称、路径（不写绝对路径就在datadir下）

relay_log：配置中继日志名称、路径（不写绝对路径就在datadir下）
log_error：配置错误日志名称、路径（不写绝对路径就在datadir下）
show_query_log_file：配置慢查询日志名称、路径（不写绝对路径就在datadir下）
long_query_time：是否开启慢查询

sync_binlog：
参数为0时，像操作系统刷其他文件的机制一样，mysql不会同步到磁盘中去而是依赖操作系统来刷新binary log。备注：sync()
如果该参数值大于0时，mysql在每写N次二进制日志binary log时使用fdatasync()将binlog实时写入至磁盘。
备注：如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

OS介绍：
sync函数只是将所有修改过的块缓存区排入写队列，然后就返回，它并不等待实际写磁盘结束。
fsync函数只对有文件描述符filedes指定的单一文件起作用，并且等该写磁盘操作结束，然后返回。
(metadata、包括size、访问时间st_atime & st_mtime等等)，因为文件的数据和metadata通常存在磁盘的不同地方，
因此fsync至少需要两次写I/O操作）
fdatasync函数类似于fsync，但它只影响文件的数据部分。而除数据外，fsync还会同步更新文件的属性。

expire_log_days：自动清理binlog的天数
back_log：back_log值指出在mysql暂时停止回答(TCP三次握手)新请求之前的短时间内多少请求可以被存在堆栈中；
在linux下这个参数的值不能大于系统参数tcp_max_syn_backlog的值(位于/proc/sys/net/ipv4/tcp_max_syn_backlog)

skip-name-resolve：跳过域名解析的正向、反向DNS解析，默认mysql认证远程客户端时，不只是验证用户名、密码还需要验证主机名在DNS上是否能够解析

skip-external-locking：跳过MyISAM表级别的外部锁，用于多个线程访问同一个数据库目录的锁机制

skip-character-set-client-handshake：忽略客户端字符集，而是用服务器端字符集

explicit_default_for_timestamp：限制创建timestamp类型的字段指定默认行为

default_storage_engine：设置Mysql默认使用的存储引擎

bind-address：用于mysql允许TCP/IP连接的网段，0.0.0.0代表任意主机

lower_case_table_names：设置为1时，表示存储表名是不区分大小写

myisam_recover：自动恢复myisay表

transaction-isolation：
事务隔离级别
read uncommitted、read committed、repeatable read、serializable

table_definition_cache：存放表的定义信息。是frm文件在内存中的映射。Mysql需要打开frm文件，并将其内容初始化为table share对象。

这里存放于存储引擎无关的，独立的表定义相关信息。

table_open_cache：为所有线程打开表的缓存，存放当前已经打开的表句柄，与表创建时指定的存储引擎相关。
performance_schema：启动性能统计信息。
max_connections：最大允许的同时客户数量
max_user_connections：root权限以外用户最大连接数
max_connect_error：对于链接中断的次数进行统计，超过配置次数，要通过flush hosts重置计数器。

timeout
wait_timeout：服务器在关闭它之前在一个连接上等待行动的秒数
interactive_timeout：服务器在关闭它前在一个交互连接上等待行动的秒数
lock_wait_timeout：事务获得资源超时时间
connect_timeout：Mysql Server等待客户端TCP链接的时间
slave_net_timeout：slave和master网络超时的时间，单位：秒

character
character-set-server：Mysql Server字符集
init-connect：指定每个客户端在链接mysql后，执行的strings

query cache
query_cache_type：是否启用查询缓存
query_cache_size：查询缓存大小

replication
server_id：要求尽量唯一
gtid_node：是否开启GTID，如果是ON，则必须是log-bin、log-slave-uodates、enforce-gtid-consistency也必须启动
enforce-gtid-consistency：gtid_node=on，该参数必须开启，用于控制事务安全的记录到binlog中
log-slave-updates：slave会把复制的binarylog中记录，在级联复制中，用于记录元数据都被那些数据库应用过。
binlog-format=row：binlog日志格式：statement、row、mixed，可以动态修改，建议使用row
slave-paeallel-workers：Mysql5.6并行复制参数(库级别并行复制)
master-info-repository：服务器写master log info的存储方式是table还是file
replay-log-info-repository：服务器写relay log file的存储方式是table还是file
sync_master_info：和sync_binlog是一种参数，控制N此后刷新master.info
slave_sql_verify_checksum：SQL thread使用检验和验证数据从中继日志读取
skip-slave-start：跳过Mysql自动启动slave

MyIASM
key_buffer_size：(MyISAM)索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小。

session
read_buffer_size：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区
read_rnd_buffer_size：每个进行一个离散扫描的线程为其扫描的每张表分配分配这个大小的一个缓冲区
tmp_table_size：临时HEAP数据表的最大长度
max_heap_table_size：此变量设置允许用户创建的内存表的最大大小
max_allowed_packet：允许最大发送、接收数据表的大小
bulk_insert_buffer_size：insert、load data infile子句所需要的插入缓存区大小
sort_buffer_size：每个需要进行排序的线程分配该大小的一个缓冲区

thread_cache_size：
可以复用的保存在中的线程数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，
客户的线程在缓存中。一般来讲首先要确认一下“Threads_connected”的最大最小的值，一般来讲设置256就已经很大了，
因为保持大量等待连接的空间线程并没有什么作用。

thread_concurrency：运行的应用程序在同一时间并发的线程
thread_stack：每个线程的堆栈大小

INNODB
innodb_flush_method：O_DIRECT设置时，依然使用sync()来刷新文件到磁盘，但是会通知操作系统不要缓存数据，
也不要预读，这个选项完全的关闭操作系统缓存，避免了缓存双重写的问题，使用O_DIRECT选项时，通常需要带有写缓存
的RAID卡，并设置为write-back(写缓存，需要raid电源备份单元才可以启动该状态)策略，因为这是唯一能保持性能的方法

innodb_data_home_dir：innodb数据文件的目录
innodb_data_file_path：innodb共享表空间以及大小

redo log
innodb_log_group_home_dir：innodb redo log家目录
innodb_log_file_in_group：innodb redo log group数量
innodb_log_file_size：innodb redo log大小

innodb performance
innodb_flush_log_at_trx_commit：
如果innodb_flush_log_at_trx_commit设置为0时，log buffer将每秒一次写入log file中，并且log file的flush(刷到磁盘)操作同时进行，
该模式下，在事务提交的时候，不会主动触发写入磁盘操作
如果innodb_flush_log_at_trx_commit设置为1时，每次事务提交时mysql都会把log buffer的数据写入到log file，并且会flush(刷到磁盘)中去。
如果innodb_flush_log_at_trx_commit设置为2时，每次事务提交时mysql都会把log buffer的数据写入log file，但是flush并不会同时进行。
在该模式下，mysql会每秒执行一次flush(刷到磁盘)操作。

innodb_file_per_table：是否使用独立表空间
innodb_buffer_pool_instance：innodb buffer pool实例数量，innodb在管理大内存的buffer pool时，效率总不是能够利用充分，
因为内存大会造成便利buffer pool内的page时间会长，更重要的原因是mutex只有一个。
innodb_io_capacity：innodb曾经在代码里面写死了，每秒100个I/O操作的单硬盘上，现在通过innodb_io_capacity中指定innodb服务器有多大I/O能力
innodb_lock_wait_timeout：innodb事务等待row lock超时时间
binlog_error_action：控制服务器无法写入二进制日志时发生的动作

innodb_buffer_pool_size：innodb引擎最大的缓冲区大小
innodb_additional_mem_pool_size：innodb用来存储数据字典和其他内部数据的内存池大小
innodb_max_dirty_pages_pct：innodb最大脏页的百分比，超过这个参数值，就会触发checkpoint

innodb_support_xa：默认为true，如果关闭binlog记录事务的顺序可能与实际不符
1：prepare，然后将redo log持久化到磁盘
2：如果前面prepare成功，那么再继续讲事务日志持久化到binlog
3：如果前面成功，那么在redo log里面写上一个commit记录，那么假如在进行着三步时有任何一步失败，crash recovery是如何进行的？
此时会先从redo log讲最近一个检查点开始的事务读出来，然后参考binlog里面的事务进行恢复。
如果是在 1 crash，那么自然这个个事务都回滚，如果是在 2 crash，那么也会整个事务回滚；
如果是在 3 crash(仅仅是commit记录没写成功)，那么没有关系，因为2中已经记录了此次事务的binlog，所以将这个commit。
总结起来，就是redo log里面凡是prepare成功，但commit失败的事务都会先去binlog查找其是否存在(通过XID进行判断，是不是经常在binlog里面看到Xid=xxxx？这就是xa事务id)，
如果有，就将这个事务commit，否则rollback。

innodb_buffer_pool_dump_at_shutdown：
5.6的新特性，数据库启动或关闭时自动dump buffer pool数据。
问题：如果内存100G，关闭Mysql的时候需要dump出100G数据么？

innodb_buffer_pool_load_at_startup：5.6新特性，快速预热buffer pool热数据
innodb_sync_spin_loops：thread等待innodb metux的时间
innodb_thread_concurrency：线程调度器的最大并行度，0表示没有限制
innodb_thread_sleep_delay：如果已经进入内核的线程超过了允许的数量，新的线程就无法进入内核，innodb使用两阶段来尝试处理尽可能高效快速的进入内核，

(两阶段策略减少了因操作系统调度引起的上下文切换)

innodb_concurrency_tickets：
线程一旦进入内核就会得到一定数量的tickets(票据，由innodb_concurrency_tickets参数决定)，该参数限制了一个线程回答其他等待线程之间可以做多少事，
tickets的单位是查询的数量，而不是事务数量，一旦tickets用完就作废。

innodb_old_blocks_pct：
数据量通过LRU算法来进行管理，LRU列表中加入的midpoint位置。新读取到的页，并不是直接放到LRU链表的首部，而是midpoint位置。
默认情况下，在LRU链表长度的5/8处。由参数innodb_old_blocks_pct控制。

innodb_read_io_thread：read thread的数量，默认为4
innodb_write_io_thread：write thread的数量，默认为4
innodb_purge_thread：purge thread默认1
innodb_purge_batch_size：
innodb_change_buffering：该参数控制insert_change_buffer将 insert、update、delete合并到insert buffer thread中
innodb_stats_on_metadata：该参数控制当使用show index、table status and show [full] tables时，或者对应的从information schema.tables
和information schema.statistics表中查询时会自动更新统计信息。