mysql 参数对于mysql性能调优至关重要,我们可以使用 show VARIABLES 查看mysql参数。可以使用以下语句进行查询
show VARIABLES like '%bin%'
show VARIABLES where variable_name like '%bin%'
select @@sync_binlog
更新操作:
set global slow_query_log = 1 ## 这种方法仅限于当次数据库,如果重启的话会失效
set session slow_query_log = 1 ## 这种方法仅限于当次回话,回话结束会失效
找到mysql 安装目录下的my.ini 添加 session slow_query_log = 1 ,重启mysql,是永久的
一般为1为开启,0位关闭
wait_timeout
当一个数据库连接成功后,如果不进行任何操作,连接器会在固定的时间后将连接断开,这个时间是由参数wait_timeout控制,默认为8小时。
innodb_flush_log_at_trx_commit
表示redo log 写入策略,一般设置为1 ,表示事务的每次提交,redo log 都持久化磁盘
-
设置为 0 的时候,表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
-
设置为 1 的时候,表示每次事务提交时都将 redo log 直接持久化到磁盘;
-
设置为 2 的时候,表示每次事务提交时都只是把 redo log 写到 page cache。
sync_binlog
表示binlog写入策略,一般设置为1,表示事务的每次提交,bin 都持久化磁盘
- write,指的就是指把日志写入到文件系统的 page cache,并没有把数据持久化到磁盘,所以速度比较快。
- fsync,才是将数据持久化到磁盘的操作。一般情况下,我们认为 fsync 才占磁盘的 IOPS。
-
sync_binlog=0 的时候,表示每次提交事务都只 write,不 fsync;
-
sync_binlog=1 的时候,表示每次提交事务都会执行 fsync;
-
sync_binlog=N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync。
非双1 情况
我目前知道的场景,有以下这些:
-
业务高峰期。一般如果有预知的高峰期,DBA 会有预案,把主库设置成“非双 1”。
-
备库延迟,为了让备库尽快赶上主库。@永恒记忆和 @Second Sight 提到了这个场景。
-
用备份恢复主库的副本,应用 binlog 的过程,这个跟上一种场景类似。
-
批量导入数据的时候。
一般情况下,把生产库改成“非双 1”配置,是设置 innodb_flush_logs_at_trx_commit=2、sync_binlog=1000。
binlog 格式:
binlog_format
值:statement,row,mixed
日志缓存大小:
innodb_log_buffer_size
transaction_isolation
事务隔离级别,在mysql 5.6以前,该参数为tx_isolation
autocommit
是否自动提交,为0的时候,即关闭自动提交,那么我们执行一个select的时候,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句,或者断开连接。容易造成长事务。建议设置为1,自动提交\
慢查询;https://www.cnblogs.com/saneri/p/6656161.html
innodb io能力 (建议和磁盘iops一致)
innodb_io_capacity
脏页比例
innodb_max_dirty_pages_pct
表数据存放地址:
innodb_file_per_table 为 on
这个参数设置为 OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
这个参数设置为 ON 表示的是,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
我建议你不论使用 MySQL 的哪个版本,都将这个值设置为 ON。因为,一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过 drop table 命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。
排序内存空间
sort_buffer_size,如果超出这个大小,排序需要借助磁盘临时文件来进行排序。
排序的行的数据最大值:
max_length_for_sort_data,如果超过这个大小,排序会使用rowid排序。
内存临时表大小:
tmp_table_size ,
如果内存临时表放不下,就需要创建磁盘临时表,但是这样比较耗费性能
最大连接数
max_connections
备库并行复制线程数
slave_parallel_workers
sql 语句安全监测
sql_safe_updates 设置为1 为开启,0关闭
如果是开启状态,那么像delete,update忘记加where条件会报错
mysql内存大小
innodb_buffer_pool_size
join_buffer 大小
join_buffer_size
上次查询结果返回页的个数
last_query_cost
查看上次查询的具体耗时:
profiling
可以通过show profiles 查看多个
show profile 查看上次查询
show profile for query queryid 可以查看指定查询的耗时