mysql 服务器参数调优_mysql优化参考(五)-服务器参数

可以用客户端连接后通过命令来查看：

show variables;

show variables like '%connection%';

参考地址：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

一、通用(General)

datadir=/var/data/mysql     数据文件存储目录

socket=/var/lib/mysql/mysql.sock　　服务器和客户端在同机会使用这个socket文件进行连接

pid_file=/var/lib/mysql/mysql.pid　　  mysql的进程ID

default_storage_engine=InnoDB　　    默认存储引擎

skip-grant-tables　　　　                 如果忘记密码可以通过这个跳过验证，免密登录

二、编码(Character)

character_set_client    客户端数据的编码集，可以动态连接指定

character_set_connection  客户端连接传输信息的字符集，可以动态指定

character_set_results　　服务端返回客户端结果的字符集

character_database　　数据库默认字符集

character_set_server　　mysql服务器的默认字符集

三、连接(Connection)

max_connections=151        mysql的最大连接数

max_user_connections=0   mysql指定单个用户的最大连接数，默认是0，表示不限制

connect_timeout               连接超时时间

back_log　　                    表示连接数满了之后，新的请求有多少可以暂存在堆栈中，超出的部分则不再接受请求

wait_timeout                     关闭一个非交互连接需要等待的时间

interactive_timeout　　      关闭一个交互连接需要等待的时间

四、日志(log)

log_error　　      错误日志存储地址

log_bin               二进制日志存储地址

binlog_do_db       指定需要存储到二进制日志的数据库

binlog_ignore_db 指定不需要存储到二进制日志的数据库

sync_binlog         多少次写日志后同步磁盘

general_log         ON|OFF 是否开启查询日志

general_log_file   查询日志的存储地址

slow_query_log    ON|OFF是否开启慢查询日志

slow_query_log_file 慢查询日志的存储文件地址

long_query_time   慢日志中需要的一个参考值，多少查询时间算是慢查询

------------恢复内容开始------------

可以用客户端连接后通过命令来查看：

show variables;

show variables like '%connection%';

参考地址：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

一、通用(General)

datadir=/var/data/mysql     数据文件存储目录

socket=/var/lib/mysql/mysql.sock　　服务器和客户端在同机会使用这个socket文件进行连接

pid_file=/var/lib/mysql/mysql.pid　　  mysql的进程ID

default_storage_engine=InnoDB　　    默认存储引擎

skip-grant-tables　　　　                 如果忘记密码可以通过这个跳过验证，免密登录

二、编码(Character)

character_set_client    客户端数据的编码集，可以动态连接指定

character_set_connection  客户端连接传输信息的字符集，可以动态指定

character_set_results　　服务端返回客户端结果的字符集

character_database　　数据库默认字符集

character_set_server　　mysql服务器的默认字符集

三、连接(Connection)

max_connections=151        mysql的最大连接数

max_user_connections=0   mysql指定单个用户的最大连接数，默认是0，表示不限制

connect_timeout               连接超时时间

back_log　　                    表示连接数满了之后，新的请求有多少可以暂存在堆栈中，超出的部分则不再接受请求

wait_timeout                     关闭一个非交互连接需要等待的时间

interactive_timeout　　      关闭一个交互连接需要等待的时间

四、日志(log)

log_error　　      错误日志存储地址

log_bin               二进制日志存储地址，避免数据丢失(恢复)

binlog_do_db       指定需要存储到二进制日志的数据库

binlog_ignore_db 指定不需要存储到二进制日志的数据库

sync_binlog         多少次写日志后同步磁盘

general_log         ON|OFF 是否开启查询日志

general_log_file   查询日志的存储地址

slow_query_log    ON|OFF是否开启慢查询日志

slow_query_log_file 慢查询日志的存储文件地址

long_query_time   慢日志中需要的一个参考值，多少查询时间算是慢查询

log_slow_admin_statements

五、缓存(cache)

key_buffer_size     索引缓存区的大小，只对MyIsAM生效

query cache　　8之后废弃

query_cache_size  查询缓存的大小

show status like '%Qcache%';

Qcache_free_blocks：缓存中相邻内存块的个数，数值比较大表示内存碎片比较多

Qcache_free_memory：查询缓存中剩余的内存大小

Qcache_hits：命中缓存的次数(总统计)

Qcache_inserts：没命中多少次(没命中会导致插入)

Qcache_lowmem_prunes：多少条内存因为内存不足而被清除

Qcache_not_cached：没有被cache的只读查询数量

Qcache_queries_in_cache：当前缓存的query数量

Qcache_total_blocks：当前cache中block的数量

query_cache_limit  超出此大小的数据将不被缓存

query_cache_min_res_unit 缓存块最小大小

query_cache_type

0 or OFF ：不缓存,query_cache_size最好对应设置为0

1 or ON：除了SELECT SQL_NO_CACHE外，其他都缓存

2 or DEMAND：仅缓存SELECT SQL_CACHE

sort_buffer_size   每个需要排序的线程分配该大小的缓冲区

max_allowed_packet=32M  限制server接受的数据包大小

join_buffer_size=2M  表示关联缓存的大小

thread_cahce_size

(show status) Threads_cached  代表当前此时此刻线程缓存有多少空闲线程

(show status) Threads_connected

(show status) Threads_created

(show status) Threads_running

六、InnoDB

日志机制

redo log： 一致性、持久性。  先写入日志，再提交；相当于两阶段提交，或者二次持久化(先持久化到日志【磁盘】，再持久化到数据库【磁盘】。也就是说日志持久化作为数据库持久化的担保)。

undo log：原子性支持。相当于命令回滚，会对应事务的一个反向操作，比如delete一条记录，undo log就会对应生成一条insert记录。在事务回滚的时候执行undo log，来回滚操作。

innodb_buffer_pool_size  指定大小的内存来缓冲数据和索引，最大可以设置物理内存的80%

innodb_flush_log_trx_commit  主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0 1 2(dml commit->log buffer->oss buffer->file system；一种直接读入oss buffer并触发异步同步 应该是1，一种是直接读入oss buffer，并每秒触发fsync，应该是0；还有一种是读取log buffer，并每秒读入oss buffer并触发fsync，应该是2)

innodb_thread_concurrency  设置innodb的线程并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍

innodb_log_buffer_size 此参数确定日志文件所用的内存大小，以M为单位

innodb_log_file_size 此参数确定日志文件的大小，以M为单位

innodb_log_files_in_group 以循环方式(redo log)将日志写入到多个文件

read_buffer_size         mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区

read_rnd_buffer_size   mysql随机读的缓冲区大小

innodb_file_per_table  此参数确定为每张表分配一个新的文件，不配置的话，一个表只有一个文件(表结构文件，数据文件统一存储)

注1  binlog：通用的操作日志，可用于数据恢复，保存的是原始的操作内容，与存储引擎无关。

注2ACID：

原子性(atomic)：MVCC(多版本高并发控制) -undo机制

一致性(Consistency)：redo log一致性，二次提交

隔离性(Isolation)：锁机制

持久性(Durability)：redo log最终也是要从内存(日志)->硬盘(日志)->内存(数据)->硬盘(数据)

注3 DEMAND：需求