mysql 限速_mysql日常运维与参数调优

为什么要调整参数

不同服务器之间的配置，性能不一样

不同业务场景对数据的需求不一样

mysql的默认参数只是个参考值，并不适合所有的应用场景

优化之前我们需要知道什么

服务器相关的配置

业务相关的情况

mysql相关的配置

服务器相关的配置

硬件情况

操作系统版本

CPU,网卡节电模式

服务器numa设置---内存分片，cpu对应内存；

RAID卡缓存

磁盘调度策略--write back

数据写入cache即返回，数据异步的从cache刷入存储介质

磁盘调度策略--write through

数据同时写入cache和存储介质才返回写入成功

write back 性能高于 write through

而write through  的安全性更高。

RAID

RAID --廉价的存储阵列

RAID0

简单就是将多块盘当做一块盘来使用；容量是多盘的和，性能也是多盘之和；

问题，就是当其中一块盘损坏后，无法保证其数据的安全性；

RAID1

指两块盘做相互的镜像--达到高可用

问题，只能使用两块盘来做，存储空间 有限制

RAID5

至少使用三块盘，总存储空间只有两块；因为它需要存储校验数据块；

高可用的实现，是通过校验数据块，来恢复数据；

局限，只能坏一块盘，才能通过另外两块盘的 存储校验数据块，进行数据恢复，如果坏了两块盘则不能进行数据恢复

RAID10

先对两块盘做RAID1，再做RAID0

RAID1保证数据安全性，RAID0保证数据扩展性；

局限，做RAID1的两块盘同时坏了，则也不能保证数据安全性；

RAID如何保证数据安全

BBU(Backup  Battery Unit)

保证在电池有电的情况下，即使服务器发生掉电或者宕机，也能够将缓存中的数据写入到磁盘，从而保证数据的安全

注意事项

mysql有哪些注意事项

mysql的部署安装

mysql的监控

mysql参数调优

部署mysql的要求

推荐的mysql版本：>=mysql5.5

推荐的mysql存储引擎：innodb

系统调优的依据：监控

实时监控mysql的SLOW log

实时监控数据服务器的负载情况

实时监控mysql内部状态值

网易内部监控的参数：

binlog文件大小(MB)

BufferPool命中率(%)

cpu利用率(%)

磁盘读操作延时(ms/op)

磁盘读取字节数(KB/s)

磁盘读取次数(次/秒)

占用磁盘存储空间(MB)

磁盘写入操作延时(ms/op)

磁盘写入字节数(KB/S)

磁盘写入次数(次/秒)

磁盘IO利用率(%)

占用内存量(%)

内存使用率(%)

一般事务提交操作(次/秒)

删除操作(次/秒)

插入操作(次/秒)

查询操作(次/秒)

更新操作(次/秒)

二阶段事务提交操作(次/秒)

通常关注哪些mysql  status

com_select/update/delete/insert

看数据库的请求是否变多

Bytes_received/Bytes_sent

看 mysql总的吞吐量

Buffer Pool Hit Rate

innodb内存的命中率决定了性能

Threads_connected/Threads_created/Threads_running

前两个多的话， 可以判断 应用是否使用连接池，或者连接池使用是否合理

活跃连接很多，说明数据库很忙，可能是被人恶意攻击；

为什么要调整mysql的参数：

需要根据业务区动态调整这个通用的mysql数据库，使其变成专用数据库

有些参数，很可能是老版本做的，可能是为了限流和保护用的，但是随着机器的性能提高这些参数，显然是不合适的。

读优化

合理利用索引对mysql查询性能至关重用

适当的调整mysql参数也能提升查询性能

innodb_buffer_pool_size：

缓存池大小，innodb自己维护一块内存区域完成新老数据的替换

innodb_thread_concurrency:

innodb内部并发控制参数，设置为0代表不做控制

如果并发请求较多，餐宿设置较小，后进来的请求将会排队

写优化

表结构设计上使用自增字段作为表的主键

只对合适的字段加索引，索引太多影响写入性能

监控服务器磁盘IO情况，如果写延迟较大则需要扩容

选择正确的mysql版本，合理设置参数

哪些参数有助于提高写入性能

innodb_flush_log_at_trx_commit&&sync_binlog

控制redo log 刷新

控制二进制日志的刷新

innodb log file size

innodb_io_capacity

innodb insert buffer

innodb_flush_log_at_trx_commit：0,1,2

n = 0(高效，但不安全--无论服务器宕机或者mysql宕机都会丢数据)

每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上

n = 1 (低效，非常安全--都不会丢数据)

每个事务提交时候，把事务日志从缓存区写到日志文件中，并且，刷新日志文件的数据到磁盘上，优化使用此模式保证数据安全性

n = 2(高效，但不安全--服务器宕机会丢数据)

每个事务提交的时候，把事务日志数据从缓存区写到日志文件中，每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；

sync_binlog

控制每次写入binlog，是否都需要进行一次持久化

如何保证事务安全

innodb_flush_log_at_trx_commit&&sync_binlog 都设为1

事务要和binlog保证一致性---才不会导致主从不一致

事务提交过程

串行有哪些问题

SAS盘每秒只能有150--200个Fsync

换算到数据每秒只能执行50--60个事务

社区和官方的改进

redo log 的作用

在数据库 崩溃后的数据恢复；

redo log的问题

如果写入频繁导致redo log里对应的最老的数据脏页还没有刷新到磁盘，此时数据库将卡住，强制刷新脏页到磁盘

mysql默认配置文件才10M，非常容易写满，生成环境中应该提高redo log 的大小

innodb_io_capacity

innodb每次刷多少个脏页，决定innodb存储引擎的吞吐能力。

在SSD等高性能存储介质下，应该提高该参数以提高数据库的性能。

insert buffer

顺序读写 VS 随机读写

随机请求性能远小于顺序请求

将尽可能多的随机请求合并为顺序请求才是提高数据库性能的关键

insert buffer 对二级索引，的增删改，的操作缓存到 insert buffer中，然后将这些随机请求合并成顺序请求；

小结：

服务器配置要合理(内核版本，磁盘调度策略，RAID卡缓存)

完善的监控系统，提前发现问题

数据库版本要跟上，不要太新，也不要太老

数据性能优化：

查询优化：索引优化为主，参数优化为辅

写入优化：业务优化为主，参数优化为辅