mysql 基准测试和性能分析

• 安装sql-bench必需的perl包

sudo /usr/bin/perl -MCPAN -e 'install DBI'

export PATH=$PATH:/usr/local/webserver/mysql/bin

sudo apt-get install libdbd-mysql-perl

• sql-bench测试mysql

#/usr/local/webserver/mysql/sql-bench/README　#说明文档

mkdir ~/tmp/output

cd /usr/local/webserver/mysql/sql-bench/

sudo mkdir /usr/local/webserver/mysql/sql-bench/output

sudo chown ry:ry /usr/local/webserver/mysql/sql-bench/output -R

/usr/local/webserver/mysql/sql-bench/run-all-tests --user=ry --password=XXX --log --fast

• 安装基准测试工具dbt2

下载dbt2:  http://sourceforge.net/apps/mediawiki/osdldbt/index.php?title=Main_Page#Database_Test_5_.28DBT-5TM.29

sudo /usr/bin/perl -MCPAN -e 'install "YAML"'

sudo cpan Statistics::Descriptive

sudo cpan Test::Parser

sudo cpan Test::Reporter

sudo apt-get install gnuplot

sudo apt-get install build-essentials

./configure --prefix=/usr/local/webserver/dbt2 --with-mysql=/usr/local/webserver/mysql

make

sudo make install

• dbt2准备测试数据

#vim ~/tmp/dbt2-0.40/README-MYSQL#说明文档

sudo mkdir /usr/local/webserver/dbt2/data
sudo chown ry:ry /usr/local/webserver/dbt2/data

/usr/local/webserver/dbt2/bin/datagen -w 3 -d /usr/local/webserver/dbt2/data --mysql

~/tmp/dbt2-0.40/scripts/mysql/build_db.sh -w 3 -d dbt2 -f /usr/local/webserver/dbt2/data -s /tmp/mysql.sock -h localhost -u ry -p XXX -e INNODB

• dbt2测试mysql

export LD_LIBRARY_PATH=/usr/local/webserver/mysql/lib/mysql

~/tmp/dbt2-0.40/scripts/run_workload.sh -c 5 -d 300 -n -w 3 -s 10 -u ry -x XXX

• 分析慢查询日志

sudo /usr/local/webserver/mysql/bin/mysqldumpslow -t 100 /usr/local/webserver/mysql/data/mysql-slow

• 手动show status分析查询

flush status

#select SQL_NO_CACHE

show session status like 'Select%'

show session status like 'Handler%'

show session status like 'Sort%'

show session status like 'Created%'

• innotop分析查询

cd ~/tmp/
tar -zxvf innotop-1.8.0.tar.gz 
cd innotop-1.8.0/
perl Makefile.PL 
make
sudo make install

innotop -ury -prockyou -S /tmp/mysql.sock

• show profile分析查询的资源信息

set profiling=1

#sql

show profiles\G

show profile #(cpu for query 1)

• 查询优化

select SQL_NO_CACHE #

show status like 'last_query_cost'

explain #