分析生产环境中 MySQL 写入速度慢的问题




在 MySQL 中执行 SQL 语句,比如 insert,贼慢,明明可能也就只是一行数据的插入,数据量很小,但是耗费的时间却很多,为什么?



存储结构分析


MySQL 存储结构图:



解析:

  • 1、读操作:内存读 --> cache 缓存读 --> 磁盘物理读读取到的数据会按上述顺序往回送。
  • 2、写操作:内存数据直接写入 cache 缓存 (非常快) --> 写入disk

由上可知,MySQL 之所以读写速度快,cache 在其中起到了关键作用。


Cache 缓存特点


1、速度快

2、掉电数据丢失  

3、容量有限


保护数据安全 (防止意外掉电的数据丢失) 应对措施

在存储中,增加 BBU (电池备份单元,就是电池),在掉电后,可以把缓存中的数据写到磁盘中,保证数据不会丢失。


如果没有提供 BBU 或者 BBU 坏了,内存数据就不会写入 cache 缓存中,就会直接写入 disk 中;相较于写入缓存,写入磁盘的速度就大打折扣 (万倍之差)。与此同时,因为慢下来的 “写” 占了绝大部分 “读” 的带宽。所以 BBU 问题是读写性能差的一很大的影响因素。


cache 缓存容量有限


(4G、8G、16G、32G),系统为了保持 cache 有用,会周期性的将 cache 缓存的数据写入磁盘中,避免 cache 被占满。



存储写入速度慢分析


如何判断写入速度慢?


怀疑 BBU 问题

监控 BBU 的 bug,解决:重启 BBU


cache 被占满 (类同于 BBU 坏了的情况)

海量的写入数据占满 cache 缓存,判断:

shell> iostat -x
mysql> show global status like 'handler_write';


cache 写入 disk 的速度慢了 

(排水速度远小于注水速度)

硬盘 I/O 异常,负载过高:数据库海量的物理读 (异常 SQL),判断:

mysql> show status like 'Innodb_buffer_pool_reads';


存储性能差问题


存储设备差,更新设备


关于 BBU


英文简称:BBU

英文全称:Battery Backup Unit

中文全称:电池备份单元,是电池


作用


在掉电后,把缓存中的数据写到硬盘中,保证数据不会丢失;

是为了意外掉电刷脏数据的一种保护措施;

能够在系统外部供电失效的情况下,提供后备电源支持,以保证存储阵列中业务数据的安全性。


许多存储设备都会配备 BBU


BBU 在电源供应出现问题的时候,为 RAID 控制器缓存提供电源。当电源断电时,BBU 电力可以使控制器内缓存中的数据可以保存一定时间(根据 BBU 的型号而决定)。用户只需要在 BBU 电力耗尽 (电池有限) 之前恢复正常供电,缓存中的数据即可被完整的写回 RAID中, 避免断电导致数据丢失。


和服务器电池有点不一样,服务器中,在配置 RAID 卡的时候可以配一个电池,在系统掉电后,能维持内存中的数据不丢失,但时间有限,大约 12 个小时左右,假如是 12 小时,如果在 12 内没有恢复,内存中的数据就会丢失;


超级电容


服务器还有一种保护方式,叫超级电容,也是和 RAID 卡配套的,他能在服务器掉电后把缓存中的数据写入到电容中,而且会永久保存,类似写入硬盘,实际上是写入电容,你就理解成写入U盘把。他比电池好,即使服务器在12个小时后没有恢复电源,也不会造成数据丢失。



MySQL 写入慢优化


通过配置 MySQL 参数提高写入速度




sync_binlog 


MySQL 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上去。


默认,sync_binlog=0,表示 MySQL 不控制 binlog 的刷新,由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,但是风险也是最大的。因为一旦系统 Crash,在 binlog_cache 中的所有 binlog 信息都会被丢失。


如果 sync_binlog>0,表示每 sync_binlog 次事务提交,MySQL 调用文件系统的刷新操作将缓存刷下去。最安全的就是 sync_binlog=1 了,表示每次事务提交,MySQL 都会把binlog 刷写到磁盘,是最安全但是性能损耗最大的设置。这样的话,在数据库所在的主机操作系统损坏或者突然掉电的情况下,系统才有可能丢失 1 个事务的数据。但是 binlog 虽然是顺序 IO,但是设置 sync_binlog=1,多个事务同时提交,同样很大的影响 MySQL 和 IO 性能。虽然可以通过 group commit 的补丁缓解,但是刷新的频率过高对 IO 的影响也非常大。对于高并发事务的系统来说,“sync_binlog” 设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。


所以很多 MySQL DBA 设置的 sync_binlog 并不是最安全的 1,而是 100 或者是 0。这样牺牲一定的一致性,可以获得更高的并发和性能。


查询设置

SHOW VARIABLES LIKE ‘sync_binlog’;


修改设置

set global sync_binlog=100



innodb_buffer_pool_size


如果用 Innodb,那么这是一个重要变量。相对于 MyISAM 来说,Innodb 对于 buffer size 更敏感。MySIAM 可能对于大数据量使用默认的 key_buffer_size 也还好,但 Innodb 在大数据量时用默认值就感觉在爬了。 Innodb 的缓冲池会缓存数据和索引,所以不需要给系统的缓存留空间,如果只用 Innodb,可以把这个值设为内存的 70%-80%。和  key_buffer 相同,如果数据量比较小也不怎么增加,那么不要把这个值设太高也可以提高内存的使用率。


innodb_additional_pool_size


这个的效果不是很明显,至少是当操作系统能合理分配内存时。但你可能仍需要设成 20M 或更多一点以看Innodb会分配多少内存做其他用途。


innodb_log_file_size


对于写很多尤其是大数据量时非常重要。要注意,大的文件提供更高的性能,但数据库恢复时会用更多的时间。我一般用 64M-512M,具体取决于服务器的空间。


innodb_log_buffer_size


默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做更新或者使用了很多 blob 数据,应该增大这个值。但太大了也是浪费内存,因为 1 秒钟总会 flush(这个词的中文怎么说呢?)一次,所以不需要设到超过 1 秒的需求。8M-16M 一般应该够了。小的运用可以设更小一点。


innodb_flush_log_at_trx_commit(这个很管用)


抱怨 Innodb 比 MyISAM 慢 100 倍?那么你大概是忘了调整这个值。


默认为 1,这是数据库的事务提交设置参数,可选值如下:

  • 0: 日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何操作。
  • 1:在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新。
  • 2:在每个提交,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新。对日志文件每秒刷新一次。


默认值 1 的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电池供电缓存(Battery backed up cache)时。

设成 2 对于很多运用,特别是从 MyISAM 表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒 flush 到硬盘,所以你一般不会丢失超过 1-2 秒的更新。

设成 0 会更快一点,但安全方面比较差,即使 MySQL 挂了也可能会丢失事务的数据。而值 2 只会在整个操作系统 挂了时才可能丢数据。


针对同一个表通过c#代码按照系统业务流程进行批量插入,性能比较如下所示:

  • (a. 相同条件下:innodb_flush_log_at_trx_commit=0,插入50W行数据所花时间25.08秒;
  • (b. 相同条件下:innodb_flush_log_at_trx_commit=1,插入50W行数据所花时间17分21.91秒;
  • (c. 相同条件下:innodb_flush_log_at_trx_commit=2,插入50W行数据所花时间1分0.35秒。

结论:设置为 0 的情况下,数据写入是最快的,能迅速提升数据库的写入性能, 但有可能丢失上1秒的数据。


temp_table_size, heap_table_size

这两个参数主要影响临时表temporary table 以及内存数据库引擎memory engine表的写入,设置太小,甚至会出现table is full的报错信息.


要根据实际业务情况设置大于需要写入的数据量占用空间大小才行。






reference

https://www.cnblogs.com/geaozhang/p/7157659.html

https://blog.csdn.net/weixin_42578316/article/details/121698878