数据库性能衡量

性能指标Baseline
Sysbench性能压测
Zabbix性能监控
性能指标详解
load cpu run max connection conn io net/in net/out sel ins upd del tps deleay 倍数
50 80 50 2000 200 100 1000 2000 10000 6000 6000 6000 6000 60 1
Sysbench压测
下载：
https://codeload.github.com/akopytov/sysbench/zip/0.5
安装：
./autogen.sh
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysqllibs=/usr/local/mysql/lib && make && make install
压测：
/data/soft/sysbench-0.5/sysbench/sysbench --test=/data/soft/sysbench-0.5/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 \
--mysql-table-engine=innodb --mysql-user=system --mysql-password=mysql --mysql-port=3306 --mysql-host=127.0.0.1 --mysql-db=sbtest \
--max-requests=0 --max-time=600 --oltp-tables-count=10 --reportinterval=10 --oltp-read-only=off --num_threads=8 prepare

/data/soft/sysbench-0.5/sysbench/sysbench --test=/data/soft/sysbench-0.5/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 \
--mysql-table-engine=innodb --mysql-user=system --mysql-password=mysql --mysql-port=3306 --mysql-host=127.0.0.1 --mysql-db=sbtest \
--max-requests=0 --max-time=600 --oltp-tables-count=10 --reportinterval=10 --oltp-read-only=off --num_threads=8 --percentile=99 run >> /home/mysql/sysbench_2018080601.log

--mysql-socket=/data/mysqldata/3306/mysql.sock
Zabbix性能监控
性能指标详解
1.CPU Loader
/usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k system.cpu.load[,avg1]
cat /proc/loadavg
cat /proc/cpuinfo|grep processor |wc -l

2.CPU Usage
/usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k system.cpu.util[,idle]
cat /proc/uptime

3.MySQL CPU
#!/bin/bash
ver=` grep -Po '\d{1}\.' /etc/redhat-release`
if [ "$ver" == "6." ]
then
echo `ps aux | grep mysql$1.sock|grep mysqld | grep -v 'grep' | awk '{print $2}' | xargs pidstat 3 1 -p | tail -n2 | head -n1 | awk '{print $7}'`
else
echo `ps aux | grep mysql$1.sock|grep mysqld | grep -v 'grep' | awk '{print $2}' | xargs top -c -b -n 1 -p | tail -2|grep -Ev "^$"|awk '{print $9}'`
fi

4.socket
cat /proc/net/sockstat |grep sockets|awk '{print $3}'
cat /proc/sys/net/ipv4/ip_local_port_range

5.run
/usr/bin/php /usr/local/zabbix/scripts/mysql/ss_get_mysql_stats_3306.php --host 127.0.0.1 --items hk | awk -F: '{ print $2 }'

show status;
Threads_running

6 CONNECTIONS
/usr/bin/php /usr/local/zabbix/scripts/mysql/ss_get_mysql_stats_3306.php --host 127.0.0.1 --items hn | awk -F: '{ print $2 }'
show processlist;

--zabbix

UserParameter=mysql.mysql_conn_counts,/usr/bin/mysql -h 127.0.0.1 -P3306  -uzabbix -p'zabbix' -N -e "select count(*)  from information_schema.PROCESSLIST;"  2>/dev/null

 

7.BigDML
set long_query_time=10;
select count(*) from information_schema.processlist where state='Updating' and time>=100;

8.IO await
/usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k disk.performance[await,avg]

9.SYS IO await
/usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k disk.performance_sys[await,avg]

10.datasize
du -sm /apps/dbdat/{mysql5,mysql57,mariadb5,mariadb10}*3306 2>/dev/null | awk '{print $1}' | paste -s -d + | bc;

11.Free disk space

12.CPU IO Wait Time
/usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k system.cpu.util[,iowait]

13.Incoming/Outcoming network traffic

14.Free swap space
/usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k system.swap.size[,pfree]

15.QPS(SELECT)
TPS(DML)
UserParameter=mysql_3306.Com_update,/usr/bin/php /usr/local/zabbix/scripts/mysql/ss_get_mysql_stats_3306.php --host 127.0.0.1 --items i3 | awk -F: '{ print $2 }'
UserParameter=mysql_3306.Com_insert,/usr/bin/php /usr/local/zabbix/scripts/mysql/ss_get_mysql_stats_3306.php --host 127.0.0.1 --items i4 | awk -F: '{ print $2 }'
UserParameter=mysql_3306.Com_select,/usr/bin/php /usr/local/zabbix/scripts/mysql/ss_get_mysql_stats_3306.php --host 127.0.0.1 --items i5 | awk -F: '{ print $2 }'
UserParameter=mysql_3306.Com_delete,/usr/bin/php /usr/local/zabbix/scripts/mysql/ss_get_mysql_stats_3306.php --host 127.0.0.1 --items ib | awk -F: '{ print $2 }'

/usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k mysql_3306.Com_select

==ss_get_mysql_stats_3306.php 在percona官网的监控

16.Slow Log
/usr/bin/php /usr/local/zabbix/scripts/mysql/ss_get_mysql_stats_3306.php --host 127.0.0.1 --items h6 | awk -F: '{ print $2 }'

17.slave/IO
if [ "$(sh /usr/local/zabbix/scripts/mysql/sql_relay_3306.sh| grep Slave_IO_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi

18.slave/SQL
if [ "$(sh /usr/local/zabbix/scripts/mysql/sql_relay_3306.sh| grep 'Slave_SQL_Running:'|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi

19.Slave/Delay
/apps/svr/mysql5/bin/mysql -h 127.0.0.1 -P 3306 -u$user -p"$pass" -e "show slave status\G" 2> /dev/null
sh /usr/local/zabbix/scripts/mysql/sql_relay_3306.sh | grep Seconds_Behind_Master | awk '{print $2}'`

--zabbix

UserParameter=mysql.mysql_3306_slave_delay,/usr/local/mysql/bin/mysql -uzabbix -pzabbix -P3306 -h127.0.0.1  2>/dev/null -e "show slave status\G" |grep -E "Seconds_Behind_Master|Seconds_Behind_Master"|awk '{print $2}'