记录一次mysql性能调优

记录一次mysql性能调优

二月 26, 2019

####userinfo to mysql 优化
主机配置: aws m4.xlarge 4核16G MySQL 5.6
优化原因: userinfo to db 任务时有超时的情况发生,并且任务执行时间特别长,最大的一个库导入MySQL是4个小时
优化过程: 通过配置mysql innodb参数提高写入速度
具体配置如下:
mysql.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1) innodb_buffer_pool_size
表示缓冲池字节大小,InnoDB缓存表和索引数据的内存区域。Innodb的缓冲池会缓存数据和索引,值越大,IO读写就越少,默认的值是128M,如果只用Innodb,可以把这个值设为内存的70%-80%

2) innodb_buffer_pool_instances
主要用于将innodb buffer pool进行划分,开启多个内存缓冲池,把需要缓冲的数据hash到不同的缓冲池中,这样可以并行的内存读写,可以提高并发能力,并且减少了不同线程读写造成的缓冲页。使用大的 buffer_pool 时,innodb_buffer_pool_instances=1 的表现最棒

3) innodb_log_file_size
表示在一个日志组每个日志文件的字节大小。该值越大,缓冲池中必要的检查点刷新活动就会越少,节省磁盘I/O。但是越大的日志文件,mysql的崩溃恢复就越慢

4) innodb_log_buffer_size
表示InnoDB写入到磁盘上的日志文件时使用的缓冲区的字节数,默认值为8M。一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此,如果你有很多事务的更新,插入或删除很操作,通过这个参数会大量的节省了磁盘I/O。

5) innodb_flush_log_at_trx_commit
用于设置这两个缓存的刷新,默认innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话数据库对IO的要求就非常高,如果底层的硬件提供的IOPS比较差,那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升,innodb_flush_log_at_trx_commit=0时,每隔一秒把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话可能丢失1秒的事务数据。innodb_flush_log_at_trx_commit=2时,在每次事务提交的时候会把log buffer刷到文件系统中去,但是每隔一秒调用文件系统的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。

调优结果: mysql调优后,任务进程时间缩短了将近一半