原创

MySQL数据库性能测试监控

1、目的：

1、发现数据库相关的所有性能瓶颈

2、范围：

1、sql语句-慢查询等

2、资源使用率

3、数据库架构合理性

4、数据库性能指标

1、常用数据库架构：

       1、一主多从，一台master数据库（主写）多台slave（从读）的，读写分离，分担了单台服务器的压力，但可能会存在主从延时

       2、双机热备，keepAlived -> [VIP] -> master -> [复制] -> slave，减少了主从延时

2、主从同步工作原理：

       1、master将改变记录到二进制日志（binary log）中

2、slave将master的binary log events拷贝到它的中继日志（relay log）

3、slave重做中继日志中的事件，将改变反应它自己的数据

拆分的原因：

1、单表或库数据量太大

2、硬件不能升级或无法升级

分库分表方案：

1、业务拆分（按照不同模块或业务进行拆分）

2、垂直拆分 （比如把商品中不同商品类型进行表的拆分）

3、水平拆分 （一致性哈西算法）

1、QBS：queries per seconds 每秒钟查询数量

show global status like 'Question%';

Queries / seconds

2、TPS：tranaction per seconds

show global status like 'Com_commit';

show global status like 'Com_rollback';

TPS = (Com_commit + Com_rollback) / seconds

3、线程连接数：

show global status like 'Max_used_connections';

show global status like 'Thredds%';

4、最大连接数：show variables like 'max_connections';

5、Query Cache：查询缓存用于缓存select 查询结果

当下次接收到相同查询请求时，不再执行实际查询处理而直接返回结果，适用于大量查询，很少改变表中数据

修改my.cnf：将query_cache_size 设置为具体的大小，具体大小是多少取决于查询的实际情况，但最好设置为1024的倍数，参考值为32M

增加一行：query_cache_type = 0/1/2

如果设置为1，将会缓存所有的结果，除非你的select 语句使用SQL_NO_CACHE禁用了查询缓存

如果设置为2，则只缓存再select 语句中通过SQL_CACHE指定需要缓存的查村

6、Query Cache命中率：show status like 'Qcache%';

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts)) * 100%

7、锁定状态：show global status like '%lock%';

Table_locks_waited/Table_locks_immediate 值越大代表表锁照成的阻塞越严重

Innodb_row_lock_waits innodb行锁，太大可能时间隙锁照成的

8、主从延时：查询主从延时时间：show slave status

1、执行速度超过定义的时间的查询；不同的系统定义不容的慢查询指标

2、编辑/etc/my.cnf ，再[mysqld]域中添加，开启慢查询：slow_query_log = 1

3、慢查询日志路径：show_query_log_file = /data/mysql/showq.log

4、慢查询的时长：long_query_time = 1

5、未使用索引的查询也被记录到慢查询日志中

6、log_queries_not_using_indexs = 1

7、mysqldumpslow命令：

      mysqldumpslow -s r -t 10 showq.log，得到返回记录集最多的10个SQL

      mysqldumpslow -s c -t 10 showq.log，得到访问次数集最多的10个SQL

      mysqldumpslow -s c t -t 10 -g "left join" showq.log，得到按照时间排序的前10条里面含有左链接的查询语句

-s 是表示按照何种方式排序

-t 是top n的意思，即为返回前面多少条数据

-g 后边可以写一个正则匹配模式，大小写不敏感

命令【orzdba】：下载附件后请进行安装，安装方式请百度即可

1、编辑orzdba文件配置连接到mysql（没多大用，也可以在代码160行左右处自行配置）

     orzdba调用默认是没有地方输入mysql用户名和密码的，因此建议将密码写到/etc/my.cnf 的client段里面

2、命令测试，如下图，代表命令安装成功

可控制输出间隔和次数，以及是否输出颜色，输入日志等

3、监控命令示例一：

监控mysql server 性能：./orzdba -mysql -lazy

分析：load-avg---- ---cpu-usage--- ---swap，说的是linux本身资源的一些监控情况，如：load-avg三个值的含义，cpu-usage资源的利用情况，swap虚拟内存等

下面：-QPS- -TPS--Hit%---threads-----bytes，说的是mysql的监控情况，分别如下：

          bytes：指的是接收和发送的字节大小

          QBS：queries per seconds 每秒钟查询数量,指的是sel

         TPS：tranaction per seconds 每秒钟事务数量，包含ins,upd,del,但不包含sel

         Hit%：Query Cache命中率，针对查询语句sql，有多少次命中了

         threads：分别代表运行、连接、创建

4、监控命令示例二：

查看Linux主机指标：./orzdba -sys -C 10 -i 1 -t -d xvda1

-sys：打印系统信息，包括-t(打印当前时间)、-l(打印负载信息，分1分钟、5分钟、15分钟)、-c(打印cpu信息)、-s(打印交换分区信息)

-d：打印磁盘信息，需要指点磁盘设备名

-n：打印网络信息，接收和发送大小，需要指点网卡设备名

5、监控命令示例三：

查看mysql响应时间：./orzdba -rt -C 10 -i 1 -t -d xvda1

报错：Warning: Using a password on the command line interface can be insecure.

解决：在mysql更目录，新建 .my.cnf 隐藏文件，然后配置如下信息

[client]

user=root

password=root

然后更改：orzdba脚本的第160行处为：my $MYSQL    = qq{mysql -s –skip-column-names -uroot -P$port };

安装地址：http://www.lepus.cc/manual/index/

1、启动Lepus 

此时，你可以执行启动命令启动lepus采集进程

[root@ebs-cloud lepus]# lepus start

nohup: 把输出追加到"nohup.out"

lepus server start success!

日志：/usr/local/lepus/logs

2、web管理台

访问：http://192.168.0.172/php

用户名/密码：admin/admin123

正文到此结束

所属分类：性能测试