mysql日常运维与参数调优

WindySky 2017-07-26

展开全文

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 --廉价的存储阵列

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卡缓存）
完善的监控系统，提前发现问题
数据库版本要跟上，不要太新，也不要太老
数据性能优化：
- 查询优化：索引优化为主，参数优化为辅
- 写入优化：业务优化为主，参数优化为辅

总结

日常运维工作：
- 导数据，
  - 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：并发控制；