查看“Mysql 高性能笔记”的源代码
←
Mysql 高性能笔记
跳转至:
导航
、
搜索
因为以下原因,你没有权限编辑本页:
该页面已被保护以防止编辑和其他操作。
您可以查看并复制此页面的源代码:
[[category:MySQL]] =前言= ==书中代码== 下载:[[文件:Mysql高性能 随书源码.zip|缩略图|mysql高性能_随书源码]] 官网链接: http://www.highperfmysql.com/#downloads ==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 YourPassword --socket=/tmp/mysql.sock </pre> [[文件:Mysqlreport20160901.log|缩略图|mysqlreport20160901.log]] ==准备== 环境变量(我的LINUX服务器),若要长期有效可将以下脚本追加在 /etc/profile中 <pre> 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 </pre> 我的MAC: export PATH=/Applications/XAMPP/xamppfiles/bin:$PATH =第二章 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=YourPassword --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=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 </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 -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 </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.2对应用程序进行性能剖析=== * New Relic * xhprof ==3.3 剖析MySQL查询== ===打开慢查询=== 编辑 my.conf 在[mysqld]下方加入慢查询的配置语句: <pre> slow_query_log = true long_query_time = 1 slow_query_log_file = /data/mysql.slow.log </pre> 注: 在我的MAC上则是如下路径:slow_query_log_file = /Applications/XAMPP/xamppfiles/var/mysql/mysql_slow.log 在MySQL5.6之前还可用如下方式(5.6以后废弃) <pre> log-slow-queries = /data/mysql.slow.log long_query_time = 1 </pre> 参考 http://jingyan.baidu.com/article/0aa223755476db88cc0d6492.html http://blog.csdn.net/lwprain/article/details/8802379 如果要长期开启慢查询日志,注意要部署'''日志轮换(log rotation)'''工具 分析慢查询 pt-query-digest slow.log === tcpdump=== <pre> tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt pt-query-digest --type tcpdump mysql.tcp.txt </pre> [[文件:Pt-query-digest--tcpdump20160901.log|缩略图|居中|pt-query-digest--tcpdump20160901.log]] ===3.3.2 剖析单条性能=== ====使用SHOW PROFILE (未来可能被Performance Schema取代)==== [[文件: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> ====使用Performance Schema (>= MySQL5.5)==== 以下显示系统等待的主要原因 <pre style='white-space: pre-wrap; word-wrap: break-word;'> 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; </pre> [[文件:Performance schema result.png|center|450px|缩略图|结果]] ==3.4诊断间歇性问题== ===3.4.1 单条问题还是服务器问题=== ====使用SHOW GLOBAL STATUS 可用于监控==== [[文件:ShowGlobalStatusStatResult.png|150px|缩略图|right|结果]] 以下脚本没个一秒执行一次,第一列为每秒查询数,第二列为Threads_connected,第三列为Threads_running(正在执行的查询数): <pre> 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}' </pre> 这三个数据对数据库偶尔停顿敏感度比较高。 ====使用SHOW PROCESSLIST==== 以下命令用来计算State列值出现的次数 如果大量的线程处于 “freeing items” 状态是出现了大量有问题查询的很明显的特征和指示 mysql -uroot -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -nr [[文件:ShowProcessListResult.png|450px|缩略图|居中|结果]] ====使用查询日志==== 以下命令也可以通过 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 [[文件:SlowLogStatResult.png|450px|缩略图|居中|结果]] ===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-mysql-summary20160901]]<br /> [[文件:Pt-summary20160901.log|缩略图|pt-summary20160901.log]] #pt-sift - Browses files created by pt-stalk. 这个工具会轮流导航到所有的样本数据,得到每个样本的汇总信息 #用法 pt-sift FILE|PREFIX|DIRECTORY ====oprofile==== <pre> 安装 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库显示检测结果 </pre> [[文件:OpfileExample.png|500px|居中|缩略图|结果]] ====gdb==== [[文件:GdbBtOfMysqldExample.png|600px|缩略图|右|结果]] gdb命令参考连接 http://blog.chinaunix.net/uid-20696246-id-1892093.html <pre> pidof mysqld #得到mysqld的pid为 1504 gdb (gdb) attach 1504 (gdb) bt (gdb) c (gdb) detach (gdb) q </pre> ====pt-pmp 堆栈信息==== 比上面gdb的要强大 <pre> pt-pmp -l 5 或 pt-pmp -l 5 stacktraces.txt </pre> ===统计临时表=== 其中lsof.txt是lsof每5秒采集一次的汇总数据。 以下命令对以上文件中临时表的数据进行了加总输出 <pre> $ awk ' /mysqld.*tmp/ { total += $7; } /^Sun Mar 28/ && total { printf "%s %7.2f MB\n", $4, total/1024/1024; total = 0; }' lsof.txt </pre> ==3.5其它工具 strace == 通过strace统计mysql系统调用的情况 $ strace -cfp $(pidof mysqld) pt-ioprofile 这个工具依赖于strace =第四章 Schema与数据类型优化= ==4.1选择要优化的数据类型== 更小的通常更好、简单就好、尽量避免NULL<br /> 整形:TINYINT 8位 , SMALLINT 16, MEDIUMINT 24, INT 32, BIGINT 64<br /> 布尔类型的技巧:用CHAR(0) 表示,true/false分别用 NULL/空字符串 来表示<br /> 要避免使用BIT类型<br /> 几个常用的函数 <pre> FROM_UNIXTIME() #把Unix时间戳转换成日期 P122 UNIX_TIMESTAMP() #日期转换成时间戳 #存储UUID的一个技巧:移除“-”号,用UNHEX()转换成16字节的数字,并存储在BINARY(16)的列中。 UNHEX() #转换成10进制P126 HEX() #转换成16进制 #IPv4和INT间的转换 INET_NTOA() INET_ATON() </pre> #存储UUID的一个技巧:移除“-”号, 然后转换成10进制 select conv(replace(uuid(),'-',''),16,10); ==4.4缓存表和汇总表== ===4.4.1物化视图 TODO:练习Flexviews P134=== ===4.4.2计数器表=== 使用独立的表可以帮助避免查询缓存失效 例1:网站计数,思路添加100行,每次随机找一行将cnt++ <pre> #建表: 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; </pre> 例2:按天计数。这里更新计数时用到了一个技巧: '''ON DUPLICATE KEY UPDATE''' <pre> #建表: 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; </pre> ==4.5ALTER TABLE提速== 假设要改变一个列的默认值,则可用ALTER COLUMN,此方法可以防止表重建: ALTER TABLE sakila.film '''ALTER COLUMN''' rental_duration SET DEFAULT 5; =第五章创建高性能索引= ==索引基础== ===创建自定义哈希索引,示例:=== <pre> #创建表 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/"); </pre> 自己定一个HA64位函数: SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AS HASH64; ==关于三星索引 P152== <big>一星索引:索引将相关的记录放到一起<br /> 二星索引:索引中的数据顺序和查找中的排列顺序一致<br /> 三星索引:索引中的列包含了查询中需要的全部列</big> ==5.3高性能索引策略== ===前缀索引和索引选择性=== 缺点:MySQL无法使用前缀索引做ORDER BY 和 GROUP BY ,也无法使用前缀索引做覆盖扫描 <pre> #计算不同的选择性 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)); </pre> 而对于'''后缀索引''',可以把字符串反转后做成前缀索引,并用触发器来维护。 ===选择合适的索引顺序=== 经验法则:将选择性最高的列放到索引的最前列
返回
Mysql 高性能笔记
。
导航菜单
个人工具
创建账户
登录
命名空间
页面
讨论
变种
视图
阅读
查看源代码
查看历史
更多
搜索
相关站点
站长博客
分类
DB
WEB
iOS
Android
深度学习
技术收集
素材收集
历史
常用网址
导航
首页
最近更改
随机页面
帮助
常用管理页面
五笔字根表
工具
链入页面
相关更改
特殊页面
页面信息