2016年9月2日 (五) 08:59的版本

前言

书中代码

下载：文件:Mysql高性能随书源码.zip 官网链接： 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 YourPassword --socket=/tmp/mysql.sock

文件:Mysqlreport20160901.log

准备

环境变量（我的LINUX服务器），若要长期有效可将以下脚本追加在 /etc/profile中

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

我的MAC:

 export PATH=/Applications/XAMPP/xamppfiles/bin:$PATH

第二章 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=YourPassword --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=YourPassword --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=YourPassword --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 -pYourPassword create tpcc1000
mysql -pYourPassword tpcc1000 < create_table.sql
mysql -pYourPassword  tpcc1000 < add_fkey_idx.sql

./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "YourPassword" -w 10
#可以备个份，方便下次测试
/usr/local/mysql/bin/mysqldump -uroot -pYourPassword  tpcc1000> tpcc1000.sql

./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w10 -c32 -r10 -l10800 -pYourPassword

查看数据库统计情况（自己总结的语句）

 
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;

第三章服务器性能剖析

mysql示例数据库

安装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.2对应用程序进行性能剖析

New Relic
xhprof

3.3 剖析MySQL查询

打开慢查询

编辑 my.conf 在[mysqld]下方加入慢查询的配置语句：

slow_query_log = true
long_query_time = 1
slow_query_log_file = /data/mysql.slow.log

注：在我的MAC上则是如下路径：slow_query_log_file = /Applications/XAMPP/xamppfiles/var/mysql/mysql_slow.log

在MySQL5.6之前还可用如下方式（5.6以后废弃）

log-slow-queries = /data/mysql.slow.log
long_query_time = 1

参考

http://jingyan.baidu.com/article/0aa223755476db88cc0d6492.html 
http://blog.csdn.net/lwprain/article/details/8802379

如果要长期开启慢查询日志，注意要部署日志轮换（log rotation）工具

分析慢查询

pt-query-digest slow.log

tcpdump

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

文件:Pt-query-digest--tcpdump20160901.log

3.3.2 剖析单条性能

使用SHOW PROFILE （未来可能被Performance Schema取代）

ShowProfile示例

在会话级别使用

set profiling = 1;
SELECT * FROM sakila.nicer_but_slower_film_list;
show profiles;
show profile for query 2;

以上是默认输出，若要灵活显示则执行以下语句（结果如右图--> ）：

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;

使用SHOW STATUS

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%';

注：show status 也会创建一个临时表，从而影响结果中的数字（而且不同版本可能的行为也不尽相同）。比较前面通过show profiles获得的查询的执行计划的结果来看，至少临时表的计数器多加了2.

附：查看sakila数据库中各表行数

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;

使用Performance Schema (>= MySQL5.5)

以下显示系统等待的主要原因

SELECT event_name, count_star, sum_timer_wait  FROM `performance_schema`.events_waits_summary_global_by_event_name ORDER BY sum_timer_wait DESC LIMIT 5;

结果

3.4诊断间歇性问题

3.4.1 单条问题还是服务器问题

使用SHOW GLOBAL STATUS 可用于监控

结果

以下脚本没个一秒执行一次，第一列为每秒查询数，第二列为Threads_connected，第三列为Threads_running（正在执行的查询数）：

mysqladmin ext -i1 | awk '
BEGIN{print "Q/Sec  Thread_connected Thread_running"}
/Queries/{q=$4-qp;qp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

这三个数据对数据库偶尔停顿敏感度比较高。

使用SHOW PROCESSLIST

以下命令用来计算State列值出现的次数如果大量的线程处于 “freeing items” 状态是出现了大量有问题查询的很明显的特征和指示

mysql -uroot -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -nr

结果

使用查询日志

以下命令也可以通过 tcpdump 和 pt-query-digest来模拟替代

awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slow-query.log

#MAC上为：
sudo awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}'  /Applications/XAMPP/xamppfiles/var/mysql/mysql_slow.log

结果

3.4.2 捕获诊断数据

捕获freeing items状态的数目

$ mysql -e 'SHOW PROCESSLIST\G' | grep -c "State: freeing items"
36

几个PT工具及依赖关系

pt-collect  ->  pt-stalk  ->  gdb + profile
说明：
* 需要有root权限       
* 需要用nm mysqld|wc -l 检查一下是否有调试符号信息
* 要在 pt-stalk 中配置gdb 和 profile

pt-summary #输出操作系统和硬件的信息
pt-mysql-summary --user=root --password=YourPassword  #输出MySQL的状态和配置信息

文件:Pt-mysql-summary20160901.log
文件:Pt-summary20160901.log

#pt-sift - Browses files created by pt-stalk. 这个工具会轮流导航到所有的样本数据，得到每个样本的汇总信息
#用法
pt-sift FILE|PREFIX|DIRECTORY

oprofile

安装
yum install oprofile

初始化 
opcontrol --no-vmlinux # 指示oprofile启动检测后，不记录相关统计数据
opcontrol --init # 加载oprofile模块、oprofile驱动程序
检测控制
opcontrol --start # 指示oprofile启动检测
opcontrol --dump # 指示将oprofile检测到的数据写入文件
opcontrol --reset # 清空之前检测的数据记录
opcontrol -h # 关闭oprofile进程
查看检测结果
opreport # 以镜像(image)的角度显示检测结果，进程、动态库、内核模块属于镜像范畴
opreport -l # 以函数的角度显示检测结果
opreport -l test # 以函数的角度，针对test进程显示检测结果
opannotate -s test # 以代码的角度，针对test进程显示检测结果
opannotate -s /lib64/libc-2.4.so # 以代码的角度，针对libc-2.4.so库显示检测结果

结果

gdb

结果

gdb命令参考连接 http://blog.chinaunix.net/uid-20696246-id-1892093.html

pidof mysqld #得到mysqld的pid为 1504
gdb
(gdb) attach 1504
(gdb) bt
(gdb) c 


(gdb) detach
(gdb) q

pt-pmp 堆栈信息

比上面gdb的要强大

pt-pmp -l 5
或
pt-pmp -l 5 stacktraces.txt

统计临时表

其中lsof.txt是lsof每5秒采集一次的汇总数据。以下命令对以上文件中临时表的数据进行了加总输出

$ awk ' /mysqld.*tmp/ {
total += $7; }
/^Sun Mar 28/ && total {
printf "%s %7.2f MB\n", $4, total/1024/1024; total = 0;
}' lsof.txt

3.5其它工具 strace

通过strace统计mysql系统调用的情况

$ strace -cfp $(pidof mysqld)

pt-ioprofile 这个工具依赖于strace

第四章 Schema与数据类型优化

4.1选择要优化的数据类型

更小的通常更好、简单就好、尽量避免NULL

整形：TINYINT 8位 , SMALLINT 16, MEDIUMINT 24, INT 32, BIGINT 64
布尔类型的技巧：用CHAR(0) 表示，true/false分别用 NULL/空字符串来表示
要避免使用BIT类型

几个常用的函数

FROM_UNIXTIME()  #把Unix时间戳转换成日期 P122
UNIX_TIMESTAMP() #日期转换成时间戳

#存储UUID的一个技巧：移除“-”号，用UNHEX()转换成16字节的数字，并存储在BINARY(16)的列中。
UNHEX()  #转换成10进制P126
HEX()       #转换成16进制

#IPv4和INT间的转换
INET_NTOA()
INET_ATON()

#存储UUID的一个技巧：移除“-”号, 然后转换成10进制
select conv(replace(uuid(),'-',),16,10);

4.4缓存表和汇总表

4.4.1物化视图 TODO:练习Flexviews P134

4.4.2计数器表

使用独立的表可以帮助避免查询缓存失效

例1：网站计数，思路添加100行，每次随机找一行将cnt++


#建表：
CREATE TABLE hit_counter (
  slot tinyint unsigned not null primary key, 
  cnt int unsigned not null
) ENGINE=InnoDB;

#添加计数时如下：
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

#统计访问量时：
SELECT SUM(cnt) FROM hit_counter;

例2：按天计数。这里更新计数时用到了一个技巧： ON DUPLICATE KEY UPDATE

#建表： 
CREATE TABLE daily_hit_counter ( day date not null,
  slot tinyint unsigned not null,
  cnt int unsigned not null, 
  primary key(day, slot)
) ENGINE=InnoDB;

#添加计数时如下：
INSERT INTO daily_hit_counter(day, slot, cnt)
 VALUES(CURRENT_DATE, RAND() * 100, 1)
 ON DUPLICATE KEY UPDATE cnt = cnt + 1;
 

#如果希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并删除所有其它的槽
UPDATE daily_hit_counter as c 
   INNER JOIN (
      SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot FROM daily_hit_counter
      GROUP BY day
   ) AS x USING(day)
SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
c.slot = IF(c.slot = x.mslot, 0, c.slot);

DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

4.5ALTER TABLE提速

假设要改变一个列的默认值，则可用ALTER COLUMN，此方法可以防止表重建：

ALTER TABLE sakila.film ALTER COLUMN rental_duration SET DEFAULT 5;

第五章创建高性能索引

索引基础

创建自定义哈希索引，示例：

#创建表
CREATE TABLE pseudohash (
   id int unsigned NOT NULL auto_increment, url varchar(255) NOT NULL,
   url_crc int unsigned NOT NULL DEFAULT 0, 
   PRIMARY KEY(id)
);

#创建触发器，在插入和更新时维护url_crc列：
DELIMITER //
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN 
SET NEW.url_crc=crc32(NEW.url);
END;
//
CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN 
SET NEW.url_crc=crc32(NEW.url);
END;
//
DELIMITER ;

#验证触发器
INSERT INTO pseudohash (url) VALUES ('http://www.mysql.com'); 
SELECT * FROM pseudo hash;

UPDATE pseudohash SET url='http://www.mysql.com/' WHERE id=1;
SELECT * FROM pseudo hash;


#查询：
SELECT * FROM pseudohash WHERE url="http://www.mysql.com/"  AND url_crc=CRC32("http://www.mysql.com/");

自己定一个HA64位函数：

SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AS HASH64;

关于三星索引 P152

一星索引：索引将相关的记录放到一起
二星索引：索引中的数据顺序和查找中的排列顺序一致
三星索引：索引中的列包含了查询中需要的全部列

5.3高性能索引策略

前缀索引和索引选择性

缺点：MySQL无法使用前缀索引做ORDER BY 和 GROUP BY ，也无法使用前缀索引做覆盖扫描

#计算不同的选择性
SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
SELECT
  COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
  COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
  COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
  COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
  COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
FROM sakila.city_demo;

#创建前缀索引
ALTER TABLE sakila.city_demo ADD KEY (city(7));

而对于后缀索引，可以把字符串反转后做成前缀索引，并用触发器来维护。

5.3.4选择合适的索引顺序

经验法则：将选择性最高的列放到索引的最前列。但通常不如避免随机IO和排序那么重要

联合索引的选择性预测（从结果可见，应把customer_id放前面）：

 SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G
 *************************** 1. row ***************************
 SUM(staff_id = 2): 7992 
 SUM(customer_id = 584): 30

 #然后再看看针对这个customer_id的条件值对应的staff_id列的选择性如何：
 SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584\G
 *************************** 1. row ***************************
 SUM(staff_id = 2): 17

以上是个例（一般是从诸如pt-query-digest中提取出的最差查询），
一个通用的方法是计算选择性：

SELECT 
  COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, 
  COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, 
  COUNT(*)  FROM payment\G

@@ 第549行： / 第549行： @@
 而对于'''后缀索引'''，可以把字符串反转后做成前缀索引，并用触发器来维护。
-===选择合适的索引顺序===
+===5.3.4选择合适的索引顺序===
 经验法则：将选择性最高的列放到索引的最前列。但通常不如避免随机IO和排序那么重要

“Mysql 高性能笔记”的版本间的差异

2016年9月2日 (五) 08:59的版本

目录

前言

书中代码

mysql示例数据库

mysqlreport

准备

第二章 MySQL基准测试

2.3 基准测试方法

2.3.3 获取系统性能和状态

2.3.5 运行基准测试并分析结果

2.4 MySQL的BENCHMARK()函数(P52)

2.5基准测试案例

2.5.1 http_load

2.5.2 MySQL基准测试套件

2.5.3 sysbench命令

2.5.5 Persona 的TCPP-MySQL测试工具

第三章 服务器性能剖析

安装Persona Toolkit

3.2对应用程序进行性能剖析

3.3 剖析MySQL查询

打开慢查询

tcpdump

3.3.2 剖析单条性能

使用SHOW PROFILE （未来可能被Performance Schema取代）

使用SHOW STATUS

使用Performance Schema (>= MySQL5.5)

3.4诊断间歇性问题

3.4.1 单条问题还是服务器问题

使用SHOW GLOBAL STATUS 可用于监控

使用SHOW PROCESSLIST

使用查询日志

3.4.2 捕获诊断数据

捕获freeing items状态的数目

几个PT工具及依赖关系

oprofile

gdb

pt-pmp 堆栈信息

统计临时表

3.5其它工具 strace

第四章 Schema与数据类型优化

4.1选择要优化的数据类型

4.4缓存表和汇总表

4.4.1物化视图 TODO:练习Flexviews P134

4.4.2计数器表

4.5ALTER TABLE提速

第五章创建高性能索引

索引基础

创建自定义哈希索引，示例：

关于三星索引 P152

5.3高性能索引策略

前缀索引和索引选择性

5.3.4选择合适的索引顺序

导航菜单

搜索

第三章服务器性能剖析