MySQL性能需要关注的参数有哪些

53次阅读
没有评论

共计 8277 个字符,预计需要花费 21 分钟才能阅读完成。

本篇内容介绍了“MySQL 性能需要关注的参数有哪些”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

1、innodb_flush_log_at_trx_commit 设置为 2

这参数是指 事务 log 怎样从 log buffer 写进日志文件(ib_logfile0、ib_logfile1)

=0 mysql crash 就丢失了,性能最好

buffer pool – log buffer 每秒 wirte os cache flush 磁盘

=1 不会丢失,效率低

每次 commit,buffer pool –  log buffer— write os cache flush 磁盘

=2 即使 mysql 崩溃也不会丢数据

每次 commit,buffer pool – os cache   然后每秒 flush 磁盘

注意:由于进程调度策略问题, 这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证 100% 的“每秒

可以根据业务的安全程度和对性能的要求来具体设置该参数,已经下面的 sync_binlog

2、sync_binlog

二进制日志(binary log)同步到磁盘的频率。binary log 每写入 sync_binlog 次后,刷写到磁盘。

如果 autocommit 开启,每个语句都写一次 binary log,否则每次事务写一次。

默认值是 0,不主动同步,而依赖操作系统本身不定期把文件内容 flush 到磁盘

设为 1 最安全,在每个语句或事务后同步一次 binary log,即使在崩溃时也最多丢失一个语句或事务的日志,但因此也最慢。

sync_binlog =  N:  控制的是从 binlog buffer 中刷新 binlog 到底层 binlog 文件(也就是刷新到底层磁盘)

N 0     每向二进制日志文件写入 N 条 SQL 或 N 个事务后,则把二进制日志文件的数据刷新到磁盘上; 

N=0     不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定; 

大多数情况下,对数据的一致性并没有很严格的要求,所以并不会把 sync_binlog 配置成 1,为了追求高并发,提升性能,可以设置为 100 或直接用 0

注意 MySQL 5.6 开始引入 Group Commit 后

sync_binlog 的含义就变了,假定设为 1000,表示的不是 1000 个事务后做一次 fsync,而是 1000 个事务组。也就是说,当设置 sync_binlog=1,binlog 还未落盘,此时系统 crash,会丢失对应的最后一个事务组;如果这个事务组内有 10 个事务,那么这 10 个事务都会丢失。

如何查看是否属于一个事务组

通过 mysqlbinlog 可以查看 binlog 日志中 last_committed 值,如果值一样,表明是在同一事务组内。

### INSERT INTO `wukong_test`.`wukong`

### SET

### @1=3 /* INT meta=0 nullable=1 is_null=0 */

### @2= ccccc /* VARSTRING(80) meta=80 nullable=1 is_null=0 */

# at 496468

#170527 4:17:35 server id 12001 end_log_pos 496499 CRC32 0xd6e7f69f Xid = 5556

COMMIT/*!*/;

# at 496499

#170527 4:17:35 server id 12001 end_log_pos 496564 CRC32 0x28816d5c GTIDlast_committed=1845sequence_number=1846

SET @@SESSION.GTID_NEXT= 0a646c88-36e2-11e7-937d-fa163ed7a7b1:3624 /*!*/;

# at 496564

#170527 4:17:35 server id 12001 end_log_pos 496632 CRC32 0x03150d48 Query thread_id=1852 exec_time=0 error_code=0

SET TIMESTAMP=1495873055/*!*/;

BEGIN

3、write/read thread

异步 IO 线程数

innodb_write_io_threads=16

innodb_read_io_threads=16

(该参数需要在配置文件中添加,重启 mysql 实例起效)脏页写的线程数,加大该参数可以提升写入性能

4、innodb_max_dirty_pages_pct

最大脏页百分数,当系统中脏页所占百分比超过这个值,INNODB 就会进行写操作以把页中的已更新数据写入到磁盘文件中。默认 75,一般现在流行的 SSD 硬盘很难达到这个比例。可依据实际情况在 75-80 之间调节,

这个参数太大,导致实例恢复需要很长时间,太小的话会频繁刷新,增加 page_cleaner_thread 以及 innodb_write_io_threads 和 cpu 的负担,一般就用默认值;

5、innodb_io_capacity=5000

从缓冲区刷新脏页时,一次刷新脏页的数量。根据磁盘 IOPS 的能力一般建议设置如下:

SAS 200

SSD 5000

PCI-E 10000-50000

6、innodb_flush_method=O_DIRECT(该参数需要重启 mysql 实例起效)

控制 innodb 数据文件和 redo log 的打开、刷写模式。有三个值:fdatasync(默认),O_DSYNC,O_DIRECT。

fdatasync 模式:写数据时,write 这一步并不需要真正写到磁盘才算完成(可能写入到操作系统 buffer 中就会返回完成),真正完成是 flush 操作,buffer 交给操作系统去 flush, 并且文件的元数据信息也都需要更新到磁盘。

O_DSYNC 模式:写日志操作是在 write 这步完成(不通过 os  buffer),而数据文件的写入是在 flush 这步通过 fsync 完成。

O_DIRECT 模式:数据文件的写入操作是直接从 mysql innodb buffer 到磁盘的,并不用通过操作系统的缓冲,而真正的完成也是在 flush 这步, 日志还是要经过 OS 缓冲。

通过图可以看出 O_DIRECT 相比 fdatasync 的优点是避免了双缓冲,本身 innodb buffer pool 就是一个缓冲区,不需要再写入到系统的 buffer,但是有个缺点是由于是直接写入到磁盘,所以相比 fdatasync 的顺序读写的效率要低些。所以如果磁盘 io 压力不大的话,并且如果系统使用了 swap 空间,可以考虑 innodb_flush_method=O_DIRECT;

在大量随机写的环境中 O_DIRECT 要比 fdatasync 效率更高些,顺序写多的话,还是默认的 fdatasync 更高效,因为咱们现在使用了 insert buffer cache 的使用(转化成了顺序写),个人认为还是默认值比较好,

7、innodb_adaptive_flushing 设置为 ON(使刷新脏页更智能)

影响每秒刷新脏页的数目

规则由原来的“大于 innodb_max_dirty_pages_pct 时刷新 100 个脏页到磁盘”变为“通过 buf_flush_get_desired_flush_reate 函数判断重做日志产生速度确定需要刷新脏页的最合适数目”,即使脏页比例小于 innodb_max_dirty_pages_pct 时也会刷新一定量的脏页。
8.innodb_page_cleaners

MySQL 5.7 开启并发刷新线程,innodb_page_cleaners 控制刷新线程数

mysql show variables like i%cleaners

+———————-+——-+

| Variable_name        | Value |

+———————-+——-+

| innodb_page_cleaners | 1     |

+———————-+——-+

1 row in set (0.05 sec)

配置文件 my.cnf 中添加 innodb_page_cleaners=num 值

默认是 1;最大可以是 64,也就是会有 64 个 page cleaner 线程并发工作清理脏页

9.innodb_flush_ neighbors 刷新临近页的参数;

当刷新一个脏页时,Innodb 存储引擎会检测该页所在区(extent)的所有的页,如果是脏页,那么一起进行刷新。这样做的好处显而易见,通过 AIO 可以将多个 IO 写入操作合并为一个 IO 操作,故该工作机制在传统机械磁盘下有着显著的优势。至于固态硬盘来说,因为它有着超高的 IOPS 性能,则建议将该参数设置为 0,也就是关闭这个特性;个人觉得如果 io 并不是性能瓶颈,不建议开启这个功能,因为它可能将不怎么脏的页进行刷新,而该页之后又会很快变成脏页;

10、innodb_adaptive_flushing_method 设置为 keep_average

影响 checkpoint,更平均的计算调整刷脏页的速度,进行必要的 flush.(该变量为 mysql 衍生版本 Percona Server 下的一个变量,原生 mysql 不存在)

11、innodb_stats_on_metadata=OFF

关掉一些访问 information_schema 库下表而产生的索引统计。

当重启 mysql 实例后,mysql 会随机的 io 取数据遍历所有的表来取样用于统计数据,这个实际使用中用的不多,建议关闭.

11、innodb_change_buffering=all

change  buffer 可以认为是 insert buffer 的升级,可以对 dml 操作 —insert、delete、update 都进行缓冲,他们分别是:insert  buffer、delete buffer、purge buffer;

该参数用来开启各种 buffer 的选项,可选择的值为:inserts、delete、purges、changes、all、none; 其中 changes 代表启用了 inserts 和 delete,all 表示启用所有,none 表示都不启用,默认为 all;

当更新 / 插入的非聚集非唯一索引的数据所对应的页不在内存中时(对非聚集非唯一索引的更新操作通常会带来随机 IO),会将其放到一个 insert buffer 中,当随后页面被读到内存中时,会将这些变化的记录 merge 到页中。当服务器比较空闲时,后台线程也会做 merge 操作。

由于主要用到 merge 的优势来降低 io,但对于一些场景并不会对固定的数据进行多次修改,此处则并不需要把更新 / 插入操作开启 change_buffering,如果开启只是多余占用了 buffer_pool 的空间和处理能力。这个参数要依据实际业务环境来配置。

12、innodb_change_buffer_max_size

从 Innodb 1.2.X 版本开始,可以通过参数 innodb_change_buffer_max_size 来控制 change buffer 最大使用内存的数量;默认值为 25,表示最多使用 25% 的缓冲池内存空间,该参数最大有效值为 50%。

如果使用太多,那么当 MySQL server crash,会进行很长时间的恢复工作(进行 merger 操作)

13、innodb_old_blocks_pct 初始化默认是 37,
(innodb 体系架构 27 页)注意因为新来的 page 是放到了尾部 3 / 8 的位置 (也就是属于 sublist of old blocks) 所以 sublist of new blocks 只能来自于 sublist of old blocks 的移动

innodb 缓存池有 2 个区域一个是 sublist of old blocks 存放不经常被访问到的数据,另外一个是 sublist of new blocks 存放经常被访问到的数据

innodb_old_blocks_pct 参数是控制进入到 sublist of old blocks 区域的数量,初始化默认是 37.

innodb_old_blocks_time 参数是在访问到 sublist of old blocks 里面数据的时候控制数据不立即转移到 sublist of new blocks 区域,而是在多少微秒之后才会真正进入到 new 区域,这也是防止 new 区域里面的数据不会立即被踢出。

所以就有 2 种情况:

1、如果在业务中做了大量的全表扫描,那么你就可以将 innodb_old_blocks_pct 设置减小,增到 innodb_old_blocks_time 的时间,不让这些无用的查询数据进入 old 区域,尽量不让缓存再 new 区域的有用的数据被立即刷掉。(这也是治标的方法,大量全表扫描就要优化 sql 和表索引结构了)

2、如果在业务中没有做大量的全表扫描,那么你就可以将 innodb_old_blocks_pct 增大,减小 innodb_old_blocks_time 的时间,让有用的查询缓存数据尽量缓存在 innodb buffer pool 中,减小磁盘 io,提高性能。

21、binlog_cache_size

二进制日志缓冲大小:一个事务,在没有提交(uncommitted)的时候,产生的日志,记录到 Cache 中;等到事务提交(committed)需要提交的时候,则把日志持久化到磁盘。

设置太大的话,会比较消耗内存资源(Cache 本质就是内存),更加需要注意的是:binlog_cache 是不是全局的,是按 SESSION 为单位独享分配的,也就是说当一个线程开始一个事务的时候,Mysql 就会为这个 SESSION 分配一个 binlog_cache

怎么判断我们当前的 binlog_cache_size 设置的没问题呢?

mysql show status like binlog_%

+———————–+———–+|

Variable_name | Value |

Binlog_cache_disk_use | 1425 |

| Binlog_cache_use | 126945718 |

2 rows in set (0.00 sec)

mysql select @@binlog_cache_size;

+———————–+———–+|

@@binlog_cache_size

1048576

1 row in set (0.00 sec)

运行情况 Binlog_cache_use 表示 binlog_cache 内存方式被用上了多少次,Binlog_cache_disk_use 表示 binlog_cache 临时文件方式被用上了多少次, 当对应的 Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

22.innodb_file_per_table 两个取值:

1:开启独立表空间;

0:不开启,也就使用共享表空间;

优点:

1)每个表的数据和索引都会存在自已的表空间中,

2)可以实现单表在不同的数据库中移动

3)空间可以回收(除 drop table 操作)

4)删除大量数据后可以通过:alter table TableName engine=innodb; 回缩不用的空间

使用 turncate table 也会使空间收缩

5)对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能

缺点:

1)单表增加过大,如超过 100 个 G,使用共享表空间可能会更好!

结论:共享表空间在 Insert 操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整一 下:innodb_open_files,因为每个表对应一个文件,需要打开的文件个数比共享表空间要多,所以需要适当调大 innodb_open_files 的参数,并且调高 linux 内核参数 open files 的限制 1

想要将共享表空间转化为独立表空间有两种方法:

1. 先逻辑备份,然后修改配置文件 my.cnf 中的参数 innodb_file_per_table 参数为 1,重启服务后将逻辑备份导入即可。

2. 修改配置文件 my.cnf 中的参数 innodb_file_per_table 参数为 1,重启服务后将需要修改的所有 innodb 表都执行一遍:alter table table_name engine=innodb;

使用第二种方式修改后,原来库中的表中的数据会继续存放于 ibdata1 中,新添加的数据才会使用独立表空间

23.sync_relay_log:

sync_relay_log:默认为 10000,即每 10000 次 sync_relay_log 事件会刷新到磁盘。为 0 则表示不刷新,交由 OS 的 cache 控制,为 N 就是 n 次次 sync_relay_log 事件会刷新到磁盘;

If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log events are written to the relay log. Setting this variable takes effect for all replication channels immediately, including running channels

当设置为 1 时,slave 的 I / O 线程每次接收到 master 发送过来的 binlog 日志都要写入系统缓冲区,然后刷入 relay log 中继日志里,这样是最安全的,因为在崩溃的时候,你最多会丢失一个事务,但会造成磁盘的大量 I /O。

当设置为 0 时,并不是马上就刷入中继日志里,而是由操作系统决定何时来写入,虽然安全性降低了,但减少了大量的磁盘 I / O 操作。这个值默认是 10000,可动态修改;

24. 然后介绍参数 sync_binlog:

sync_binlog =  N:控制的是从 binlog buffer 中刷新 binlog 到底层 binlog 文件(也就是刷新到底层磁盘)

N 0     每向二进制日志文件写入 N 条 SQL 或 N 个事务后(组提交的时候,实际上是 n 个组事务),则把二进制日志文件的数据刷新到磁盘上;

N=0     不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定;

25. 增加本地端口,以应对大量连接

1

echo‘1024 65000′ /proc/sys/net/ipv4/ip_local_port_range

该参数指定端口的分配范围,该端口是向外访问的限制。mysql 默认监听的 3306 端口即使有多个请求链接,也不会有影响。但是由于 mysql 是属于高内存、高 cpu、高 io 应用,不建议把多个应用于 mysql 混搭在同一台机器上。即使业务量不大,也可以通过降低单台机器的配置,多台机器共存来实现更好。

26. 增加队列的链接数

1

echo‘1048576 /proc/sys/net/ipv4/tcp_max_syn_backlog

建立链接的队列的数越大越好,但是从另一个角度想,实际环境中应该使用连接池更合适,避免重复建立链接造成的性能消耗。使用连接池,链接数会从应用层面更可控些。

27. 设置链接超时时间

1

echo 10 /proc/sys/net/ipv4/tcp_fin_timeout

该参数主要为了降低 TIME_WAIT 占用的资源时长。尤其针对 http 短链接的服务端或者 mysql 不采用连接池效果比较明显。

28.linux 内核信号量

来 linux 内核信号量默认设置太小,造成大量等待,

默认值如下:

# cat /proc/sys/kernel/sem

250     32000   32      128

说明:

第一列,表示每个信号集中的最大信号量数目。

第二列,表示系统范围内的最大信号量总数目。

第三列,表示每个信号发生时的最大系统操作数目。

第四列,表示系统范围内的最大信号集总数目。

将第三列调大一点,参考网上的数据

echo kernel.sem=250 32000 100 128″ /etc/sysctl.conf

然后 sysctl -p

重启 mysql

如果设置过小,当大量并发的时候,会在错误日志中报错:InnoDB: Warning: a long semaphore wait!!!!

“MySQL 性能需要关注的参数有哪些”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站,丸趣 TV 小编将为大家输出更多高质量的实用文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计8277字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)