“Mysql 高性能笔记”的版本间的差异
来自Alex's wiki
（→第三章 服务器性能剖析） |
（→2.5.5 Persona 的TCPP-MySQL测试工具） |
||
| 第21行： | 第21行： | ||
===2.5.5 Persona 的TCPP-MySQL测试工具=== | ===2.5.5 Persona 的TCPP-MySQL测试工具=== | ||
| − | + | 安装 | |
<pre> | <pre> | ||
wget https://github.com/Percona-Lab/tpcc-mysql | wget https://github.com/Percona-Lab/tpcc-mysql | ||
| 第37行： | 第37行： | ||
</pre> | </pre> | ||
| − | + | 测试 | |
<pre> | <pre> | ||
| 第43行： | 第43行： | ||
| − | + | 查看数据库统计情况（自己总结） | |
<pre style='white-space: pre-wrap; word-wrap: break-word;'> | <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; | 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; | ||
2016年8月31日 (三) 04:01的版本
目录
第二章 MySQL基准测试
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=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
2.5.5 Persona 的TCPP-MySQL测试工具
安装
wget https://github.com/Percona-Lab/tpcc-mysql unzip master.zip #准备环境变量 [root@lex src]# export MYSQL_HOME=/usr/local/mysql/ [root@lex src]# export C_INCLUDE_PATH=$MYSQL_HOME/include [root@lex src]# export PATH=$MYSQL_HOME/bin:$PATH [root@lex src]# export LD_LIBRARY_PATH=$MYSQL_HOME/lib cd tpcc-mysql-master/src make all cd ..
测试
查看数据库统计情况（自己总结）
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;
第三章 服务器性能剖析
3.3.2 剖析单条性能
使用SHOW PROFILE
在会话级别使用
set profiling = 1; show profiles; show profile for query 1;
以上是默认输出，若要手动则执行以下语句：
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;