“Mysql 高性能笔记”的版本间的差异
来自Alex's wiki
（→3.3.2 剖析单条性能） |
（→使用SHOW STATUS） |
||
| 第213行： | 第213行： | ||
order by total_r desc; | order by total_r desc; | ||
</pre> | </pre> | ||
| + | |||
| + | |||
| + | |||
====使用SHOW STATUS==== | ====使用SHOW STATUS==== | ||
| 第229行： | 第232行： | ||
select TABLE_NAME, TABLE_ROWS,data_length/1024/1024 AS DATA_MB,index_length/1024/1024 as INDEX_MB ,(data_length+index_length)/1024/1024 as TOTAL_MB,TABLE_COLLATION from information_schema.tables where table_schema='sakila' order by TABLE_ROWS desc; | select TABLE_NAME, TABLE_ROWS,data_length/1024/1024 AS DATA_MB,index_length/1024/1024 as INDEX_MB ,(data_length+index_length)/1024/1024 as TOTAL_MB,TABLE_COLLATION from information_schema.tables where table_schema='sakila' order by TABLE_ROWS desc; | ||
</pre> | </pre> | ||
| − | |||
| − | |||
| − | |||
| − | |||
2016年8月31日 (三) 08:22的版本
前言
书中代码
http://www.highperfmysql.com/#downloads
mysql示例数据库
http://dev.mysql.com/doc/index-other.html
mysqlreport
#安装 yum install mysqlreport yum install perl-DBD-MySQL #运行 mysqlreport --user root --password Master.123 --socket=/tmp/mysql.sock
第二章 MySQL基准测试
2.3 基准测试方法
2.3.3 获取系统性能和状态
收集MySQL测试数据的Shell脚本（P43） gather.sh：
#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
file=$(date +%F_%I)
sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
sleep $sleep
ts="$(date +"TS %s.%N %F %T")"
loadavg="$(uptime)"
echo "$ts $loadavg" >> $PREFIX-${file}-status
mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exist.
2.3.5 运行基准测试并分析结果
以下脚本实现了从前脚本输出的数据中抽取时间维度的信息 analyze.sh： ./analyze.sh 5 -sec-status-2016-08-31
#!/bin/sh
# This script converts SHOW GLOBAL STATUS into a tabulated format, one line
# per sample in the input, with the metrics divided by the time elapsed
# between samples.
awk '
BEGIN {
printf "#ts date time load QPS";
fmt = " %.2f";
}
/^TS/ { # The timestamp lines begin with TS.
ts = substr($2, 1, index($2, ".") - 1);
load = NF - 2;
diff = ts - prev_ts;
prev_ts = ts;
printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, length($load)-1);
}
/Queries/ {
printf fmt, ($2-Queries)/diff;
Queries=$2
}
' "$@"
2.4 MySQL的BENCHMARK()函数(P52)
set @input = 'hello world'; select benchmark(1000000, md5(@input)); select benchmark(1000000, sha1(@input));
2.5基准测试案例
2.5.1 http_load
安装： install_http_load.sh
wget http://www.acme.com/software/http_load/http_load-12mar2006.tar.gz tar xzvf http_load-12mar2006.tar.gz cd http_load-12mar2006/ make && make install
测试：
http_load -p 1 -seconds 10 urls.txt http_load -parallel 1 -seconds 10 -f 10 urls.txt #其中urls.txt如下： http://www.jb51.net/category/php/ http://www.jb51.net/php/iterate-algorithm.html http://www.jb51.net/tag/web-safe/
2.5.2 MySQL基准测试套件
cd /usr/local/mysql/sql-bench/ ./run-all-tests --user=root --password=Master.123 --socket=/tmp/mysql.sock
以下是输出(/usr/local/mysql/sql-bench/output-20160824)：
-rw-r--r-- 1 root root 772 8月 24 10:11 alter-table-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64 -rw-r--r-- 1 root root 1.1K 8月 24 10:12 ATIS-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64 -rw-r--r-- 1 root root 854 8月 24 10:13 big-tables-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64 -rw-r--r-- 1 root root 1.7K 8月 24 10:14 connect-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64 -rw-r--r-- 1 root root 900 8月 24 10:24 create-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64 -rw-r--r-- 1 root root 6.7K 8月 24 13:06 insert-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64
2.5.3 sysbench命令
#CPU test sysbench --test=cpu --cpu-max-prime=20000 run #IO test sysbench --test=fileio --file-total-size=15G prepare sysbench --test=fileio --file-total-size=15G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run sysbench --test=fileio --file-total-size=15G cleanup #OLTP test sysbench --test=oltp --mysql-db=test --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=Master.123 --mysql-table-engine=innodb --oltp-table-size=1000000 prepare sysbench --test=oltp --mysql-db=test --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=Master.123 --mysql-table-engine=innodb --oltp-table-size=1000000 --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
2.5.5 Persona 的TCPP-MySQL测试工具
安装(在shell中执行)
wget https://github.com/Percona-Lab/tpcc-mysql unzip master.zip #准备环境变量 export MYSQL_HOME=/usr/local/mysql/ export C_INCLUDE_PATH=$MYSQL_HOME/include export PATH=$MYSQL_HOME/bin:$PATH export LD_LIBRARY_PATH=$MYSQL_HOME/lib cd tpcc-mysql-master/src make all cd ..
测试(在自己的测试服务器上运行了3个小时)
mysqladmin -pMaster.123 create tpcc1000 mysql -pMaster.123 tpcc1000 < create_table.sql mysql -pMaster.123 tpcc1000 < add_fkey_idx.sql ./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "Master.123" -w 10 #可以备个份，方便下次测试 /usr/local/mysql/bin/mysqldump -uroot -pMaster.123 tpcc1000> tpcc1000.sql ./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w10 -c32 -r10 -l10800 -pMaster.123
查看数据库统计情况（自己总结的语句）
select TABLE_NAME, TABLE_ROWS,data_length/1024/1024 AS DATA_MB,index_length/1024/1024 as INDEX_MB ,(data_length+index_length)/1024/1024 as TOTAL_MB,TABLE_COLLATION from information_schema.tables where table_schema='tpcc1000' order by TABLE_ROWS desc;
第三章 服务器性能剖析
安装Persona Toolkit
#参考 http://www.cnblogs.com/qiyebao/p/4766416.html #参考 http://blog.chinaunix.net/uid-20639775-id-3206802.html #安装 yum install perl-DBI yum install perl-DBD-MySQL yum install perl-Time-HiRes yum install perl-IO-Socket-SSL #yum install perl-TermReadKey rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm
3.3 剖析MySQL查询
如果要长期开启慢查询日志，注意要部署日志轮换（log rotation）工具
#导出网络数据包 pt-query-digest --type=tcpdump #慢查询 pt-query-digest slow.log
3.3.2 剖析单条性能
使用SHOW PROFILE
在会话级别使用
set profiling = 1; show profiles; show profile for query 1;
以上是默认输出，若要灵活显示则执行以下语句（结果如右图--> ）：
select state ,sum(duration) as total_r ,
round(
100 * sum(duration) /
(select sum(duration)
from information_schema.profiling
where query_id = @query_id
),2 ) as pct_r ,
count(*) as calls,
sum(duration) / count(*) as "R/Call"
from information_schema.profiling
where query_id = @query_id
group by state
order by total_r desc;
使用SHOW STATUS
SHOW STATUS示例
FLUSH STATUS; SELECT * FROM sakila.nicer_but_slower_film_list; SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%';
查看表行数
select TABLE_NAME, TABLE_ROWS,data_length/1024/1024 AS DATA_MB,index_length/1024/1024 as INDEX_MB ,(data_length+index_length)/1024/1024 as TOTAL_MB,TABLE_COLLATION from information_schema.tables where table_schema='sakila' order by TABLE_ROWS desc;