欢迎访问我的博客,你的支持,是我最大的动力!

MySQL王者晋级之路(九)优化、Lepus监控、升级、参考

Mysql-笔记 小马奔腾 5994℃ 评论
目录:
[显示]

MySQL5.7新特性

InnoDB存储引擎的增强

1、Online ALTER TABLE
支持索引重命名
alter table talbe_name rename index old_index_name to new_index_name;
支持在线调整varchar列大小
alter table t1 algorithm=inplace, change column c1 c1 varchar(255);
#不支持缩减长度,分区间0~255,256~更大,不能跨区间调整,不支持的条件使用copy算法

2、innodb_buffer_pool 在线修改
set global innodb_buffer_pool_size=1073741824;
#128MB的整数倍

帐户密码自动过期策略,密码过期必须强制进行修改
default_password_lifetime =90 #90天后过期

sys库用来诊断问题
了解哪些语句使用了临时表,哪个用户请求了最多的IO,哪个线程占用了最多的内存,哪些索引是无用索引等
以host开头的视图,记录主机相关统计信息
以innodb开头的视图,记录innodb buffer相关信息
以io开头的视图,记录IO相关信息,如等待IO、IO使用等
以memory开头的视图,记录各个维度的内存使用情况
metrics视图,记录数据库内部统计数值
processlist和session视图,记录连接会话相关信息
以schema开头的视图,记录表统计信息
以statement开头的视图,统计用户执行语句、IO使用等信息
以user开头的视图,统计用户执行语句、IO使用等信息
以wait开头的视图,记录等待事件相关情况

查看数据库中索引使用情况
select index_name,rows_selected,rows_inserted,rows_deleted,rows_updated from schema_index_statistics where table_schema='DB_name' and table_name='**' and index_name='**';
查看数据库中冗余索引
select * from sys.schema_redundant_indexes;
查看数据库中未使用到的索引
select * from sys.schema_unused_indexes;
查看IO使用最多的表
select * from io_global_by_file_by_bytes limit 1;
查看实例消耗的内存
select * from sys.memory_global_tatal;
查看每个库占用多少buffer pool
select * from innodb_buffer_stats_by_schema;
前5位占用最多buffer pool表
select * from innodb_buffer_stats_by_table order by pages desc limit 5;

设置查询SQL超时
max_execution_time 防止因一条SQL语句的长时间执行,导致数据库雪崩
show variables like '%max_execution_time%';

执行计划增强
查看正在执行的SQL线程号
show full processlist
查看当前语句的执行计划
explain for connection **

in语句子查询优化,in查询能够使用index range scan方式
union all不再产生临时表

MySQL全面优化

不要存图片、附件,不要在数据库上进行大量复杂运算,不要当成全文检索工具

每个SQL语句只能用一个CPU核心

每个连接会话连接到数据库时,都会为其分配内存,分配的内存大小一定要控制好,不宜过大,防止业务高峰时,连接数过高,出现OOM现象

关闭query cache需要同时设置query_cache_size=0和query_cache_type=0

硬件优化

影响数据库最大的性能问题是磁盘IO,可以使用SSD或PCIE-SSD,可以获得上百倍或上万倍的IOPS性能提升
当数据库TPS过高或者业务量较大时,要配置阵列卡,且要使用cache模块,并要配置BBU模块持续供电,防止出现数据丢失的现象
选择阵列卡cache策略时,建设设置为Write Back(WB),千万不要使用Write Through,不经过cache直接写盘
阵列卡的cache不是太大,建立关闭预读功能,仅使用cache
阵列级别,RAID 1+0

BIOS层面,CPU选择performance per watt optimized最大性能模式,关闭C1E和C stats这类节能选项
内存也选择最大性能模式maximum performance
禁掉NUMA功能,将Node Interleaving设置为Enabled模式,让内存在多个CPU节点之间进行循环使用,且无论OS层面numa是否打开,都不影响数据库性能

配置参数优化

MySQL参数:
innodb_buffer_pool_size,若是单实例且绝大多数是InnoDB引擎表,可设置为物理内存的50%~80%左右
innodb_flush_log_at_trx_commit和sync_binlog,分别是redo log刷新和binlog刷新参数,若要求数据不丢失,可都设置为1(双一概念,可保证主从架构中数据一致性)。若要保证强一致性,主从故障切换中不丢失数据,可使用增强半同步功能
innodb_max_dirty_pages_pct,脏页占innodb buffer pool的比例,不建议设置太大,脏页过多会影响数据库TPS,建议调整为25%~50%
innodb_io_capacity,InnoDB后台进程最大IO性能指标,影响脏页和插入缓冲数量,默认值为200,在高转速磁盘下,可适当增大该值。SSD可调整为5000~20000,PCIE-SSD更高50000+
innodb_data_file_path=ibdata1:1G:autoextend ,不要使用默认的10MB大小,通常设置为1G,防止高并发下,数据库受到影响
long_query_time,0.1~0.5秒,记录较慢的SQL,方便后续优化
binlog_format,设置为row模式,复制过程中不会丢失数据
interactive_timeout,wait_timeout,交互式等待时间和非交互式等待时间,两参数要设置一致,且必须同时修改,建议300~500秒,不要使用默认的8小时
max_connections,不应该一味调大连接数,易发生OOM现象,在调高该值的同时,应调低interactive_timeout和wait_timeout的值
innodb_log_file_size,redo log值不要太大,若值太大,当实例恢复时,会消耗大量时间;值太小也会造成日志切换过于频繁
general log,全量日志,建议关闭,否则该日志文件会越来越大,造成磁盘空间紧张,MySQL性能也会下降

操作系统层面的优化

IO调度,建议选择deadline或noop模式,不能使用cfq,会严重影响数据库性能
#查看当前IO调度方式,中括号中是当前使用方式
#cat /sys/block/sda/queue/scheduler

方件系统选择,优选XFS,其次ext4,放弃ext3

内核参数vm.swappiness
表示使用swap的意向,值较高代表当内存快用尽时,倾向使用swap,而不是特意释放内存中的数据
参数值建议在1~10范围内,不建议设置为0,为0容易导致系统内存溢出OOM,从而MySQL被意外Kill掉
#查看swappiness参数
#cat /proc/sys/vm/swappiness
#要修改swappiness值,编辑/etc/sysctl.conf,加入vm.swappiness值即可
#为0时,禁用swap,1时,尽量不使用swap,60(30)默认,100,积极使用swap,很影响性能

vm.dirty_backgroud_ratio,指定当文件系统缓存脏页数量达到系统内存百分比多少时,触发后台回写进程运行,将一定缓存脏页异步刷入磁盘,建议不要超过10

vm.dirty_ratio,指定当文件系统缓存脏页达到系统内存比例多少时,系统不得不开始处理缓存脏页,建议不超过20

表设计及其他优化

18个建议:
1)创建业务表时,库名、表名、字段名必须使用小写字母,采用_分割
2)lower_case_table_names参数区分表名大小写,默认0,大小写敏感,若为1,大小写不敏感,以小写存储。为字段选择数据类型时,简单、够用的原则,表中字段和索引数量不宜过多
3)表存储引擎选择InnoDB
4)要显式为表创建使用自增列int或bigint类型为主键,保证写入顺序是自增的,和B+树叶子节点分裂顺序一致,写入更高效
5)金钱、日期时间、IPv4尽量使用int存储。int存储金钱,单位为分,避免四舍五入,更精确。日期时间可以使用datetime,范围比timestamp大,物理存储上仅多占一个字节,整体性能消耗差别不大。IPv4使用inet_aton和inet_ntoa实现
6)text和blob要尽量避免使用,不要与业务表放在一起。sql中避免使用or子句,避免使用union,尽量采用union all,减少去重和排序工作
7)select查询时只获取必要字段,避免使用select *,可减少网络带宽消耗,还有可能利用到覆盖索引。建立索引时,不要在选择性低的字段上创建
#索引选择性计算方法select count(distinct coll)/count(*) from table_name;值越接近1,选择性越高
8)很长的字符串可以考虑建前缀索引,提高索引利用率。单表索引数不要太多,不建议超过4~5个,执行DML语句操作时,会更新索引,若索引数太多,会造成索引树分裂,性能会下降
9)所有字段定义中,默认都加上not null约束,避免出现null,在对字段进行select count()统计时,可以让统计结果更准确,因为值为null的数据不会被计算进去
10)表字符集默认使用UTF8,汉字占3字节,遇到表情存储可使用UTF8mb4
11)模糊查询尽量少用,可以使用搜索引擎sphinx代替
12)索引字段上不要使用函数,否则使用不到索引,也不要创建函数索引
13)join列类型要保持一致,包括长度、字符集都要一致
14)当在执行计划的extra看到Using filesort或者Using temporary时,优先考虑创建排序索引和分组索引。排序、分组字段上都需要创建索引
15)limit语句上的优化,建议使用主键进行范围检索,缩短结果集大小,便查询更高效
16)可使用mysqldumpslow或pt-query-digest来分析影响业务的慢查询
17)还可以使用MySQL提供的可用来分析当前会话中语句执行资源消耗的命令show profile
show variables like 'profiling';
set profiling=1;#注意不要设置成全局的,仅针对当前会话设置,查看完成后关闭会话会还原为默认值,不要set global profiling=1;
create index name_idx on user(name);
select count(*) from user;
show profiles; 可以查看每条语句的执行时间
通过show profile for query 7;可以查看每条查询的细节时间
通过show profile block io,cpu for query 7;可以查看IO和CPU情况
18)可以使用show global status查看数据库运行状态,通过得出的数值优化MySQL运行效率
重点参数项:
Aborted_client,因客户端未正确关闭连接导致的客户端终止而中断的连接数,连接时间超过超时时间就会退出,该值加1
Aborted_connects,试图连接到MySQL服务器而失败的连接数,如密码错误等
Binlog_cache_disk_use,使用临时二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中语句的事务数量
Binlog_stmt_cache_disk_use,非事务语句使用二进制日志缓存,但超出binlog_stmt_cache_size大小时,会使用一个临时文件来保存这些语句
Binlog_stmt_cache_use,使用二进制日志缓存文件的非事务语句数量
Created_tmp_disk_tables,服务器执行语句时,在磁盘上自动创建的临时表的数量
#排序中,内存不够用,就会在磁盘上创建临时表完成排序工作
Created_tmp_files,创建在内存中的临时表数量,若Created_tmp_disk_tables值较大,可能要增加tmp_table_size值,使临时表基于内存而不基于磁盘
Handler_commit,内部提交语句数量,执行update或delete语句该值加2,执行select语句加1
Handler_rollback,内部rollback语句数量
Handler_read_rnd,根据固定位置读一行的请求数,若正执行大量查询并需要排序,则该值较高
Handler_read_rnd_next,在数据文件中读取下一行的请求数,若正在进行大量表扫描操作,该值较高,通常说明表索引不正确或写入的查询没有利用索引
Handler_read_first,索引中第一条被读取的次数,若较高,说明服务器正执行大量全索引扫描
Handler_read_key,根据索引读一行的请求数,若较高,说明查询和表的索引正确

MySQL InnoDB存储引擎中三个重要等待事件
Innodb_buffer_pool_wait_free,若已适当设置缓冲池大小,该值应小,若值大于0,需要添加innodb buffer pool大小
Innodb_log_waits,若不为0,证明当前redo log buffer size太小,需要增大
Innodb_row_lock_waits,若值较高,需通过show engine innodb status\G或information_schema库下INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS进一步分析锁等待过程
Open_tables,当前打开表数量
Opened_tables,已经打开表的数量,若该值非常大,说明table_open_cache值太小,导致频繁地open table
#show variables like '%table_open_cache%';
Threads_cached,线程缓存内的线程数量
Threads_connected,当前打开的连接数量
Threads_created,创建的连接数量,若该值较大,可能要增加thread_cache_size值
Threads_running,非睡眠的线程数
Select_full_join,没有使用索引的连接的数量,若值不为0,应仔细检查表索引
Select_full_range_join,在引用的表中使用范围搜索的连接的数量
Select_range,在第一个表中使用范围的连接的数量,一般情况下即使值相当大,也不是关键问题
Select_range_check,在每一行数据后对键值进行检查,检查不带键值的连接的数量,若不为0,应仔细检查表索引
Select_scan,对第一个表进行完全扫描的连接的数量
Sort_merge_passes,排序算法已经执行的全并的数量,若值较大,应考虑增加sort_buffer_size值
Sort_range,在范围内执行的排序的数量
Sort_range,已经排序的行数
Sort_scan,通过扫描表完成的排序的数量

Lepus监控MySQL

实际并不好用,版本太低,SQL语法问题

Lepus天兔,Python+PHP开发的数据库企业级监控系统,生产环境经常使用

Lepus支持MySQL、Oracle、MongoDB、Redis、SQLServer数据库的全面监控,提供报警支持

Lepus无须Agent,远程监控云中的数据库,可视化慢查询在线分析、慢查询自动推送;实时Redis健康监控和报警、性能监控;对主机CPU/内存/磁盘/网络/IO监控;可视化告警系统,邮件、短信接口,严格权限认证

官网:http://www.lepus.cc/
文档:http://www.dbarun.com/docs/lepus/

部署

数据库:mysql
php:5.3以上,不支持7
python:2.6以上,不支持3
python数据库驱动模块:
MySQLdb,cx_oracle,Pymongo,redis-py,pymssql

Nginx+PHP环境,略

安装Python基础模块

安装MySQLdb for python
yum install mysql-devel
curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
python get-pip.py
yum install gcc python-devel
pip install mysql-python

安装Redis驱动

pip install redis

测试驱动是否正常
python test_driver_mysql.py
python test_driver_redis.py

安装Lepus采集器

mv Lepus_v3.8_beta/ /opt/lepus
cd /opt/lepus/python
chmod +x /opt/lepus/python/lepus
chmod +x /opt/lepus/python/lepus_monitor
ln /opt/lepus/python/lepus /usr/local/sbin/lepus
ln /opt/lepus/python/lepus_monitor /usr/local/sbin/lpeus_monitor

初始化数据库(监控主机)

create database lepus default character set utf8;
create user 'lepus_user'@'%' identified by 'lepus123';
grant select,insert,update,delete,create on lepus.* to 'lepus_user'@'%';
flush privileges;

/usr/local/mysql/bin/mysql -uroot -proot123 lepus < lepus_table.sql
/usr/local/mysql/bin/mysql -uroot -proot123 lepus < lepus_data.sql

vi /opt/lepus/python/etc/config.ini
填写数据库连接参数

启动采集进程

修改/opt/lepus/python/lepus内基目录basedir="/opt/lepus/python"

lepus start
ps -ef|grep lepus
查看日志
/opt/lepus/python/logs/lepus.log

安装WEB管理台

站点目录
/opt/lepus/php
修改配置文件
/opt/lepus/php/application/config/database.php #修改数据库连接参数
默认登录帐号admin/Lepusadmin

启动和关闭监控系统

lepus start
lepus stop
lepus status

注意事项
sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
被监控库所需权限 select,super,process,reload,show databases,replication client
慢查询分析依赖percona-toolkit工具

vi中执行set fileformat=unix

MySQL版本升级

升级方式

In-Place Upgrade,直接替换安装目录和my.cnf文件,利用mysql_upgrade脚本完成系统表升级,一般适用于跨小版本的升级
Logical Upgrade,利用mysqldump直接导出SQL文件,然后导入到新库中,相对安全,并能整理表中碎片,时间上消耗很大,效率较低

流程

In-Place Upgrade方式

第一步,将innodb_fast_shutdown参数设置为0
set global innodb_fast_shutdown=0;
show variables like '%fast%';
innodb_fast_shutdown有0,1,2三个值
为0时,MySQL关闭时,InnoDB需要完成所有full purge和merge insert buffer操作,这个过程要一定时间,有时甚至会花上几个小时
为1时,默认,MySQL关闭时,不完成full purge和merge insert buffer操作,但是缓冲池中的脏页还是会写到磁盘中
为2时,什么也不做,仅将日志写入日志文件中

第二步,关闭MySQL服务
mysqladmin -uroot -proot123 shutdown

第三步,替换MySQL安装文件
执行unlink mysql命令
解压新版本软件包,重新做连接并赋予mysql权限

第四步,替换配置文件my.cnf,并启动MySQL实例
在启动过程中加上--skip-grant-tables和--skip-networking参数,保证没有任何应用连接,让升级更安全
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &

第五步,升级系统表数据字典信息
/usr/local/mysql/bin/mysql_upgrade

第六步,正常启动MySQL服务

Logical Upgrade方式

第一步,mysqldump导出数据
/usr/local/mysql/bin/mysqldump -uroot -proot123 --single-transaction --master-data=2 -A >all.sql

第二步,将旧版本MySQL数据库关闭且备份原来的数据目录
建立新版本的数据目录并授权
/usr/local/mysql/bin/mysqladmin -uroot -proot123 shutdown
mv /data/mysql /data/mysql_bak
mkdir -p /data/mysql
chown mysql:mysql -R /data

第三步,替换MySQL安装文件
执行unlink mysql命令
解压新版本软件包,重新做连接并赋予mysql权限

第四步,替换my.cnf,并初始化数据库
/usr/local/mysql/bin/mysqld --default-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --initialize

第五步,启动新版本MySQL
启动过程中添加--skip-grant-tables 和 --skip-networking参数,保证没有任何应用连接,让升级更安全
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &

第六步,导入之前导出的文件到数据库
/usr/local/mysql/bin/mysql < all.sql

第七步,需要升级数据字典信息
/usr/local/mysql/bin/mysql_upgrade

第八步,正常启动MySQL数据库
关闭数据库,重新正常启动数据库

参考

文章:阿里云MySQL及Redis灵异断连现象:安全组静默丢包解决方法

wait_timeout、interactive_timeout两个超时参数只对闲置的数据库连接生效
对于正在执行sql的连接,最大执行时间受max_execution_time限制

为防止在超过一定时间后连接被回收,在内核层面可这样设置
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv3.tcp_keepalive_time = 1800
解释:tcp连接闲置1800s后,每隔30s给对方发一个ack包,最多发3次
若在此期间对方回复,则计时器重置,再等待1800s

 

 

 

转载请注明:轻风博客 » MySQL王者晋级之路(九)优化、Lepus监控、升级、参考

喜欢 (0)or分享 (0)