MYSQL8.0优化
1.tmp_table_size优化
数据库连接突然增多到1000的问题,查看了一下,未有LOCK操作语句。但是明显有好多copy to tmp table的SQL语句,这条语读的时间比较长,且这个表会被加读锁,相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句,会造成更多的语句被阻塞。连接太多造成mysql处理慢。copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时,参数中设置的临时表的大小小于结果集的大小时,就会将该表放在磁盘上,这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时,则系统会把max_heap_table_size的值作为最大的内存临时表的上限,大于这个时,改写硬盘。
我们的mysql这两个参数为:
tmp_table_size 33554432 (33.5M)
max_heap_table_size 16777216 (16.7M)
比较小。
建议增加到上百M。我们的内存应该够吧。
2.tmp_table_size优化
通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表。
如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,
#要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表。
tmp_table_size=160M
3.log-queries-not-using-indexes正式环境不建议开启
该系统变量指定未使用索引的查询也被记录到慢查询日志中(可选项)。
如果调优的话,建议开启这个选项。
另外,开启了这个参数,其实使用full index scan的SQL也会被记录到慢查询日志。
用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改。
当然我没开,因为会产生很多日志,导致分析时间找不到真正的慢查询。
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
my.cnf 设置 log_queries_not_using_indexes=1
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
4.慢查询,建议正式环境也开启,方便调优
my.cnf设置以下参数:
slow-query-log=1
slow_query_log_file=/mysql/data/sql001-slow.log
#高于多少秒就记录
long_query_time=1
5.慢管理语句
log_slow_admin_statements
这个系统变量表示,是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志。建议正式环境开启。
my.cnf设置以下参数:
log_slow_admin_statements=1
mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF |
+---------------------------+-------+
1 row in set (0.00 sec)
6.Slow_queries
这个参数可以直接显示慢SQL的数量:
下面结果中的2390表示慢SQL的数量。
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2390 |
+---------------+-------+
1 row in set (0.00 sec)
7.mysqldumpslow的使用
直接在centos命令行输入:mysqldumpslow --help
展示信息如下:
[root@DB-Server ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default(排序方式)
al: average lock time(平均锁定时间)
ar: average rows sent(平均返回记录数)
at: average query time(平均查询时间)
c: count(访问计数)
l: lock time(锁定时间)
r: rows sent(返回记录)
t: query time(查询时间)
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries(返回前面n条数据)
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string(正则匹配模式,大小写不敏感)
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
1>得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
2>得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
3>得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
4>另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
正常的应该是1,修改为0 作用:提高插入速度
当然INNODB要修改为1,如果单纯为了提高插入速度,修改为0 然后再改回来就可以了
innodb_flush_log_at_trx_commit = 0
配置由于默认1M 调整到 16M 作用:提高插入速度
此配置项作用设定innodb 数据库引擎写日志缓存区
将此缓存段增大可以减少数据库写数据文件次数。
innodb_log_buffer_size = 16M
innodb_log_file_size 作用:提高插入速度
配置由于默认 48M 调整到 128M
此配置项作用设定innodb 数据库引擎UNDO日志的大小;从而减少数据库checkpoint操作。
设置的太小:当一个日志文件写满后,innodb会自动切换到另外一个日志文件,而且会触发数据库的检查点(Checkpoint),这会导致innodb缓存脏页的小批量刷新,会明显降低innodb的性能。由于日志切换更频繁,也就直接导致更多的BUFFER FLUSH,由于日志切换的时候是不能BUFFER FLUSH的, BUFFER写不下去,导致没有多余的buffer 写redo, 那么整个MYSQL就HANG住,还有一种情况是如果有一个大的事务,把所有的日志文件写满了,还没有写完,这样就会导致日志不能切换(因为实例恢复还需要,不能被循环复写)这样mysql就hang住了。可以根据文件修改时间来判断日志文件的旋转频率,旋转频率太频繁,说明日志文件太小了。
设置的太大:设置很大以后减少了checkpoint,并且由于redo log是顺序I/O,大大提高了I/O性能。但是如果数据库意外出现了问题,比如意外宕机,那么需要重放日志并且恢复已经提交的事务(也就是实例恢复中的前滚, 利用redo从演变化来恢复buffer cache中的数据),如果日志很大,那么将会导致恢复时间很长。甚至到我们不能接受的程度。
如果对 Innodb 数据表有大量的写入操作,那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要
innodb_log_file_size = 128M
GTID 主从复制模式
master服务器 配置信息
####settings#######
[client]
port = 3306
[mysql]
no-beep
[mysqld]
port = 3306
character-set-server=utf8mb4
#centos
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#windows
#datadir = C:/ProgramData/MySQL/MySQL Server 8.0/Data
#secure-file-priv = C:/ProgramData/MySQL/MySQL Server 8.0/Uploads
default_authentication_plugin = mysql_native_password
default-storage-engine = INNODB
sql-mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
log-output = FILE
general-log = 0
general_log_file = LAPTOP-BLUTVESO.log
slow-query-log = 1
slow_query_log_file = sql0000001-slow.log
long_query_time = 1
log-error = h10sqlmaster0001.err
#############
server-id = 33301
#启动MySQ二进制日志系统
log-bin = mysql-bin
#选择row模式
binlog-format = row
#30天超时删除,防止内存被占满
binlog_expire_logs_seconds=2592000
#需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
binlog-do-db = tabcmcc
binlog-do-db = tabticket_cross
binlog-do-db = tabtpp
#不同步mysql系统数据库
binlog-ignore-db = mysql
#配置Mysql基于GTID复制,以下6个参数
gtid-mode = ON
enforce_gtid_consistency = ON
log-slave-updates = ON
binlog-checksum = CRC32
master-verify-checksum = 1
lower_case_table_names = 1
max_connections = 3000
#正常的应该是1,修改未0 是为了调高插入速度
innodb_flush_log_at_trx_commit = 1
#配置由于默认1M 调整到 16M
#此配置项作用设定innodb 数据库引擎写日志缓存区
#将此缓存段增大可以减少数据库写数据文件次数。
innodb_log_buffer_size = 16M
#将 innodb_log_file_size 配置由于默认 48M 调整到 128M
#此配置项作用设定innodb 数据库引擎UNDO日志的大小;从而减少数据库checkpoint操作。
innodb_log_file_size = 128M
table_open_cache = 2000
#该系统变量指定未使用索引的查询也被记录到慢查询日志中(可选项)。
#如果调优的话,建议开启这个选项。
#另外,开启了这个参数,其实使用full index scan的SQL也会被记录到慢查询日志。
log_queries_not_using_indexes = 0
#这个系统变量表示,是否将慢管理语句例如
#ANALYZE TABLE
#和ALTER TABLE等记入慢查询日志。
log_slow_admin_statements = 1
innodb_buffer_pool_size = 2048M
#通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表。
#如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,
#要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表。
tmp_table_size = 160M
thread_cache_size = 10
myisam_max_sort_file_size = 100G
myisam_sort_buffer_size = 8M
key_buffer_size = 8M
#16G建议设置为1M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
innodb_buffer_pool_size = 2048M
innodb_thread_concurrency = 9
innodb_autoextend_increment = 64
innodb_buffer_pool_instances = 8
innodb_concurrency_tickets = 5000
innodb_old_blocks_time = 1000
innodb_open_files = 300
innodb_stats_on_metadata = 0
innodb_file_per_table = 1
innodb_checksum_algorithm = 0
back_log = 80
flush_time = 0
join_buffer_size = 24M
max_allowed_packet = 999M
max_connect_errors = 100
open_files_limit = 4161
sort_buffer_size = 24M
table_definition_cache = 1400
binlog_row_event_max_size = 8K
sync_master_info = 10000
sync_relay_log = 10000
sync_relay_log_info = 10000
loose_mysqlx_port = 33060
GTID 主从复制模式
slave 服务器 配置信息
[mysqld]
port = 3306
lower_case_table_names = 1
character-set-server=utf8mb4
#centos
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#windows
#datadir = C:/ProgramData/MySQL/MySQL Server 8.0/Data
#secure-file-priv = C:/ProgramData/MySQL/MySQL Server 8.0/Uploads
default_authentication_plugin = mysql_native_password
#从库采用MYISAM,可以提高报表查询速度
default-storage-engine = MYISAM
#sql-mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
log-output = FILE
general-log = 0
general_log_file = general001.log
slow-query-log = 1
slow_query_log_file = sql0000001-slow.log
long_query_time = 1
log-error = h10sqlmaster0001.err
#################################
server-id = 33302
#启动MySQ二进制日志系统
log-bin = mysql-bin
#选择row模式
binlog-format = row
#30天超时删除,防止内存被占满
binlog_expire_logs_seconds=2592000
#需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
binlog-do-db = tabcmcc
binlog-do-db = tabticket_cross
binlog-do-db = tabtpp
#不同步mysql系统数据库
binlog-ignore-db = mysql
#配置Mysql基于GTID复制,以下6个参数
gtid-mode = ON
enforce_gtid_consistency = ON
log-slave-updates = ON
binlog-checksum = CRC32
master-verify-checksum = 1
lower_case_table_names = 1
max_connections = 3000
#正常的应该是1,修改未0 是为了调高插入速度
innodb_flush_log_at_trx_commit = 0
#配置由于默认1M 调整到 16M
#此配置项作用设定innodb 数据库引擎写日志缓存区
#将此缓存段增大可以减少数据库写数据文件次数。
innodb_log_buffer_size = 16M
#将 innodb_log_file_size 配置由于默认 48M 调整到 128M
#此配置项作用设定innodb 数据库引擎UNDO日志的大小;从而减少数据库checkpoint操作。
innodb_log_file_size = 128M
table_open_cache = 2000
#该系统变量指定未使用索引的查询也被记录到慢查询日志中(可选项)。
#如果调优的话,建议开启这个选项。
#另外,开启了这个参数,其实使用full index scan的SQL也会被记录到慢查询日志。
log_queries_not_using_indexes = 0
#这个系统变量表示,是否将慢管理语句例如
#ANALYZE TABLE
#和ALTER TABLE等记入慢查询日志。
log_slow_admin_statements = 1
innodb_buffer_pool_size = 2048M
#通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表。
#如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,
#要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表。
tmp_table_size = 160M
thread_cache_size = 10
myisam_max_sort_file_size = 100G
myisam_sort_buffer_size = 8M
key_buffer_size = 8M
#16G建议设置为1M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
innodb_buffer_pool_size = 2048M
innodb_thread_concurrency = 9
innodb_autoextend_increment = 64
innodb_buffer_pool_instances = 8
innodb_concurrency_tickets = 5000
innodb_old_blocks_time = 1000
innodb_open_files = 300
innodb_stats_on_metadata = 0
innodb_file_per_table = 1
innodb_checksum_algorithm = 0
back_log = 80
flush_time = 0
join_buffer_size = 24M
max_allowed_packet = 999M
max_connect_errors = 100
open_files_limit = 4161
sort_buffer_size = 24M
table_definition_cache = 1400
binlog_row_event_max_size = 8K
sync_master_info = 10000
sync_relay_log = 10000
sync_relay_log_info = 10000
loose_mysqlx_port = 33060
#skip-grant-tables
GTID 主从同步出错的解决
首先master执行命令
show global variables like '%gtid%';
将红色部分复制下来
在从库要建对应的数据库
stop SLAVE;
set @@global.gtid_purged='上面红色部分';
reset slave;
reset master;
CHANGE MASTER TO
MASTER_HOST = "192.168.1.138",
MASTER_PORT = 3306,
MASTER_USER = "rootslave",
MASTER_PASSWORD = "sv123456",
MASTER_AUTO_POSITION = 1;
start slave;
OK了,再更新主库,从库就会自动同步了。
SHOW SLAVE status;
查看从库的状态