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

=前言=
书中代码
 http://www.highperfmysql.com/#downloads
mysql示例数据库
 http://dev.mysql.com/doc/index-other.html

=第二章 MySQL基准测试=
==2.4 MySQL的BENCHMARK()函数==
 set @input = 'hello world';
 select benchmark(1000000, md5(@input));
 select benchmark(1000000, sha1(@input));
===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.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>

测试
<pre> 
 
</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示例数据库]

===3.3.2 剖析单条性能===
====使用SHOW PROFILE====
在会话级别使用
<pre>
set profiling = 1;
show profiles;
show profile for query 1;
</pre>
以上是默认输出，若要手动则执行以下语句：
<pre>
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> 
[[文件:ShowProfileExample.png|缩略图|ShowProfile示例|left]]