MYSQL8.0优化

最新推荐文章于 2024-05-09 22:32:27 发布

沉默的飞鱼

最新推荐文章于 2024-05-09 22:32:27 发布

阅读量1.7k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/yangshuai518/article/details/103896150

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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;

查看从库的状态

沉默的飞鱼

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MYSQL8.0优化

MYSQL8.0优化tmp_table_size优化数据库连接突然增多到1000的问题，查看了一下，未有LOCK操作语句。但是明显有好多copy to tmp table的SQL语句，这条语读的时间比较长，且这个表会被加读锁，相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句，会造成更多的语句被阻塞。连接太多造成mysql处理慢。copy to...
复制链接

扫一扫