日常运维
DBA运维工作
日常
- 导数据,数据修改,表结构变更
- 加权限,问题处理
其它
- 数据库选型部署,设计,监控,备份,优化等
日常运维工作:
- 导数据及注意事项
- 数据修改及注意事项
- 表结构变更及注意事项
- 加权限及注意事项
- 问题处理,如数据库响应慢
导数据及注意事项
- 数据最终形式(csv,sql文本,还是直接导入某库中)
- 导数据方法(mysqldump,select into outfile,)
- 注意事项
- 导出为csv格式需要file权限,并且只能数据库本地导
- 避免锁库锁表(mysqldump使用--single-transaction选项不锁表)
- 避免对业务造成影响,尽量在镜像库做
数据修改及注意事项
- 修改前切记做好备份
- 开事务做,修改过完检查好了再提交
- 避免一次修改大量数据,可以分批修改
- 避免业务高峰期做
表结构变更注意事项
- 在低峰期做
- 表结构变更是否会有锁?(5.6包含online ddl 功能)
- 使用pt-online-schema-change完成,表结构变更
- 可以避免主从延时
- 可以避免负载过高,可以限速
percona维护了mysql dba 必看的博客
加权限及注意事项
- 只给符合需求的最低权限
- 避免授权时修改密码
- 避免给应用账号super权限
问题处理(数据库慢?)
- 数据库慢在哪里?
- 是查询慢还是写入慢
- 是秒级别慢,还是毫秒级别慢
- show processlist 查看mysql连接信息
- 查看系统状态(iostat,top,vmstat)
小结
- 日常工作比较简单,但是任何一个操作都可能影响线上服务
- 结合不同环境,不同要求选择最合适的方法处理
- 日常工作应该求稳不求快,保障线上稳定是DBA的最大责任
不求最快,但求最稳;
例子
1.修改t1表中id<5的数据
1)备份 select * from t1 where id<5 into outfile '/tmp/t1id5.txt'; 2)执行修改 begin; update t1 set b=100,c=100 where id<5; select * from t1 where id<5; rollback;/commit;
2.表结构变更
1)5.5版本: alter table t55 add c1 int; delete from t55 where id<100;(卡住一会后才执行) 2)5.6版本: use db1; alter table t55 add c1 int; delete from t55 where id<100;(执行顺畅) alter table t1 modify c1 varchar(90); delete from t55 where id<100;(卡住一会后才执行) 3)pt-online-schema-change工具 ./pt-online-schema-change --user=root --password=123456 --host=localhost --socket=/mysqldata/node4/mysqld.sock D=db55,t=t55 --alter "add c2 int" --print --dry-run
3.加权限
grant select,insert,delete on *.* to netease@'localhost' identified by '163'; #mysql -unetease -p163 --socet=/mysqldata/node3/mysqld.sock --port=4001(登陆成功) grant select,insert,delete on *.* to netease@'localhost' identified by '123'; #mysql -unetease -p163 --socet=/mysqldata/node3/mysqld.sock --port=4001(登陆失败)
4.导数据
use db1; select count(*) from t1;(先看一下数据量) 1)mysqldump导出 #mysqldump -uroot -p123456 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t1.sql grant select on *.* to netease@'localhost' identified by '163'; #mysqldump -unetease -p163 --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t2.sql(报错,没有锁表权限) #mysqldump -unetease -p163 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t2.sql(成功) #mysqldump -uroot -p123456 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 -T /tmp use db1; 2)以file权限into outfile导出数据 select * from t1 into outfile '/tmp/t1_2.txt'; select t1.c,t3,b from t1.id=t3.id into outfile '/tmp/t13.txt';
5.数据库慢问题
../tcpstat --port 4001 -t 1 -n 0(tcpstat,查看每一个tcp连接的响应时间,percona公司出品)
参数调优
为什么要调整参数
- 不同服务器之间的配置,性能不一样
- 不同业务场景对数据的需求不一样
- mysql的默认参数只是个参考值,并不适合所有的应用场景
优化之前我们需要知道什么
- 服务器相关的配置
- 业务相关的情况
- mysql相关的配置
服务器相关的配置
- 硬件情况
- 操作系统版本
- CPU,网卡节电模式
- 服务器numa设置---内存分片,cpu对应内存;
- RAID卡缓存
磁盘调度策略--write back
- 数据写入cache即返回,数据异步的从cache刷入存储介质
磁盘调度策略--write through
- 数据同时写入cache和存储介质才返回写入成功
write back 性能高于 write through
而write through 的安全性更高。
RAID
RAID --廉价的存储阵列
![](https://images2015.cnblogs.com/blog/820365/201607/820365-20160723110832826-1441170461.png)
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保证一致性---才不会导致主从不一致
事务提交过程
![](https://images2015.cnblogs.com/blog/820365/201607/820365-20160723110847560-977975319.png)
串行有哪些问题
- SAS盘每秒只能有150--200个Fsync
- 换算到数据每秒只能执行50--60个事务
社区和官方的改进
![](https://images2015.cnblogs.com/blog/820365/201607/820365-20160723110855763-1551699480.png)
redo log 的作用
在数据库 崩溃后的数据恢复;
redo log的问题
- 如果写入频繁导致redo log里对应的最老的数据脏页还没有刷新到磁盘,此时数据库将卡住,强制刷新脏页到磁盘
- mysql默认配置文件才10M,非常容易写满,生成环境中应该提高redo log 的大小
innodb_io_capacity
- innodb每次刷多少个脏页,决定innodb存储引擎的吞吐能力。
- 在SSD等高性能存储介质下,应该提高该参数以提高数据库的性能。
insert buffer
- 顺序读写 VS 随机读写
- 随机请求性能远小于顺序请求
![](https://images2015.cnblogs.com/blog/820365/201607/820365-20160723110912576-2013148772.png)
将尽可能多的随机请求合并为顺序请求才是提高数据库性能的关键
insert buffer 对二级索引,的增删改,的操作缓存到 insert buffer中,然后将这些随机请求合并成顺序请求;
小结:
- 服务器配置要合理(内核版本,磁盘调度策略,RAID卡缓存)
- 完善的监控系统,提前发现问题
- 数据库版本要跟上,不要太新,也不要太老
- 数据性能优化:
-
- 查询优化:索引优化为主,参数优化为辅
- 写入优化:业务优化为主,参数优化为辅
总结
- 日常运维工作:
- 导数据,
- mysqldump,select into outfile,
- 避免锁库锁表,mysqldump --single-transaction;
- 数据修改
- 做好备份,
- 开事务做,
- 分批修改,
- 避免高峰期
- 表结构变更
- 低峰做
- 5.6后包含online ddl,
- 使用pt-online-schema-change:避免主从延迟,限速;
- 加权限
- 最低权限,
- 避免授权时修改密码
- 不求最快,但求最稳;
- 导数据,
- 参数调优
- RAID0,RAID1,RAID5,RADI10,
- RAID如何保证数据安全:
- BBU,服务器掉电,使用电池电量将缓存内容刷新到磁盘
- 有助于提高写性能的参数:
- innodb_flush_log_at_trx_commit 控制redo log 刷新
- sync_binlog :控制二进制日志的刷新
- innodb log file size: 重做日志循环写,如果太小,当新的写入来的时候,原日志文件写完且还没有持久化到磁盘,这时候就要阻塞写入;所以,增大事务日志大小,可能提升写性能;
- innodb insert buffer:
- 插入缓冲,将随机读写,通过这个缓冲,合并成可能的顺序读写,以提高写性能。
- 只对二级且非唯一索引生效;
- innodb_io_capacity:
- innodb每次刷新多少个脏页,决定innodb存储引擎的吞吐能力
- 在SSD,下应该提高该参数以提高数据库性能;
- 读优化:
- innodb_buffer_pool_size:缓存池大小
- innodb_buffer_pool_size:并发控制;