查看“Mysql 高性能笔记”的源代码

=前言=
==书中代码==
 http://www.highperfmysql.com/#downloads
[[文件:Mysql高性能 随书源码.zip|缩略图|mysql高性能_随书源码]]

==mysql示例数据库==
 http://dev.mysql.com/doc/index-other.html

==mysqlreport==
<pre  style='white-space: pre-wrap; word-wrap: break-word;'>
#安装
yum install mysqlreport
yum install perl-DBD-MySQL
#运行
mysqlreport --user root --password Master.123 --socket=/tmp/mysql.sock
</pre>

=第二章 MySQL基准测试=
==2.3 基准测试方法==
===2.3.3 获取系统性能和状态===
收集MySQL测试数据的Shell脚本（P43） gather.sh：
<pre>
#!/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.
</pre>
===2.3.5 运行基准测试并分析结果===
以下脚本实现了从前脚本输出的数据中抽取时间维度的信息 analyze.sh：
'''./analyze.sh 5 -sec-status-2016-08-31'''
<pre>
#!/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
  }
  ' "$@"
</pre>

==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
<pre>
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
</pre>
测试：
<pre>
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/
</pre>

===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)：
<pre>
-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
</pre>

===2.5.3 sysbench命令===
<pre style='white-space: pre-wrap; word-wrap: break-word;'>
#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
</pre>

===2.5.5 Persona 的TCPP-MySQL测试工具===
安装(在shell中执行)
<pre> 
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 ..
</pre>
测试(在自己的测试服务器上运行了3个小时)
<pre>
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
</pre>


查看数据库统计情况（自己总结的语句）
<pre style='white-space: pre-wrap; word-wrap: break-word;'> 
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;
</pre>

=第三章 服务器性能剖析=

[http://dev.mysql.com/doc/index-other.html mysql示例数据库]

===安装Persona Toolkit===
<pre>
#参考 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
</pre>

==3.3 剖析MySQL查询==
如果要长期开启慢查询日志，注意要部署'''日志轮换（log rotation）'''工具
<pre>
#导出网络数据包
pt-query-digest --type=tcpdump
#慢查询
pt-query-digest slow.log
</pre>
打开慢查询。编辑 my.conf 在[mysqld]下方加入慢查询的配置语句：
''在MySQL5.6.*之后只能如下：''' 参考
 http://jingyan.baidu.com/article/0aa223755476db88cc0d6492.html 
 http://blog.csdn.net/lwprain/article/details/8802379
<pre>
slow_query_log = true
long_query_time = 1
slow_query_log_file = /Applications/XAMPP/xamppfiles/var/mysql/mysql_slow.log
</pre>
在MySQL5.6之前还可用如下方式（5.6以后废弃）：
<pre>
log-slow-queries = /data/mysql.slow.log
long_query_time = 1
</pre>
===3.3.2 剖析单条性能===
====使用SHOW PROFILE====
[[文件:ShowProfileExample_new.png|缩略图|ShowProfile示例|right|500px]]
在会话级别使用
<pre>
set profiling = 1;
SELECT * FROM sakila.nicer_but_slower_film_list;
show profiles;
show profile for query 2;
</pre>

以上是默认输出，若要灵活显示则执行以下语句（结果如右图--> ）：
<pre>
set profiling = 1;
SELECT * FROM sakila.nicer_but_slower_film_list;
set  @query_id = 2;
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;

</pre>

====使用SHOW STATUS====

[[文件:Show status example.jpeg|350px|缩略图|有框|右|show status 示例]]
SHOW STATUS示例（见右图-->）
<pre>
FLUSH STATUS;
SELECT * FROM sakila.nicer_but_slower_film_list;
SHOW STATUS WHERE Variable_name LIKE 'Handler%'
OR Variable_name LIKE 'Created%';
</pre>
注：show status 也会创建一个临时表，从而影响结果中的数字（而且不同版本可能的行为也不尽相同）。比较前面通过show profiles获得的查询的执行计划的结果来看，至少临时表的计数器多加了2.


附：查看sakila数据库中各表行数
<pre  style='white-space: pre-wrap; word-wrap: break-word;'>
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>