db_007
http://manpages.ubuntu.com/manpages/xenial/man1/sysbench.1.html
https://www.percona.com/blog/2012/05/09/new-distribution-of-random-generator-for-sysbench-zipf/
[InnoDB INFORMATION_SCHEMA Tables]https://dev.mysql.com/doc/refman/5.5/en/innodb-i_s-tables.html
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM\G
*************************** 1. row ***************************
page_size: 1024
...
*************************** 4. row ***************************
page_size: 8192
compress_ops: 199755
compress_ops_ok: 112015
compress_time: 83
uncompress_ops: 74253
uncompress_time: 13
*************************** 5. row ***************************
page_size: 16384
...
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP\G
*************************** 1. row ***************************
...
*************************** 4. row ***************************
page_size: 8192
buffer_pool_instance: 0
pages_used: 9043
pages_free: 1
relocation_ops: 2457
relocation_time: 0
*************************** 5. row ***************************
page_size: 16384
...
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS\G
与 SHOW ENGINE INNODB STATUS 输出的信息类似。
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8192
FREE_BUFFERS: 0
DATABASE_PAGES: 8014
OLD_DATABASE_PAGES: 2938
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 7380
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 2723
NUMBER_PAGES_CREATED: 12657
NUMBER_PAGES_WRITTEN: 16181
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 28952710
HIT_RATE: 1000
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 2469
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G
*************************** 1. row ***************************
POOL_ID: 0
LRU_POSITION: 0
SPACE: 0
PAGE_NUMBER: 7485
PAGE_TYPE: INDEX
FLUSH_TYPE: 2
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 216319316
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3376846384
TABLE_NAME: employees/salaries
INDEX_NAME: emp_no
NUMBER_RECORDS: 1300
DATA_SIZE: 15600
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 0
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE BLOCK_ID=9\G
*************************** 1. row ***************************
POOL_ID: 0
BLOCK_ID: 9
SPACE: 0
PAGE_NUMBER: 8019
PAGE_TYPE: INDEX
FLUSH_TYPE: 2
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 226918754
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3376847655
TABLE_NAME: employees/salaries
INDEX_NAME: PRIMARY
NUMBER_RECORDS: 468
DATA_SIZE: 14976
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 8
[SHOW ENGINE Syntax]https://dev.mysql.com/doc/refman/5.5/en/show-engine.html
[InnoDB Standard Monitor and Lock Monitor Output]https://dev.mysql.com/doc/refman/5.5/en/innodb-standard-monitor.html
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 34340864; in additional pool allocated 0
Dictionary memory allocated 58286
Buffer pool size 2048
Free buffers 1
Database pages 2020
Old database pages 725
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1040, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2872, created 276, written 2471
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2020, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
[Server Status Variables]https://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html
mysql> SHOW GLOBAL STATUS where Variable_name like '%Innodb_buffer_pool%';
通过Innodb_buffer_pool_read_requests 和 Innodb_buffer_pool_reads可以算出命中率
+---------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------+----------+
| Innodb_buffer_pool_pages_data | 2020 |
| Innodb_buffer_pool_bytes_data | 33095680 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 2471 |
| Innodb_buffer_pool_pages_free | 1 |
| Innodb_buffer_pool_pages_misc | 27 |
| Innodb_buffer_pool_pages_total | 2048 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 56852 |
| Innodb_buffer_pool_reads | 2609 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 12862 |
+---------------------------------------+----------+
- 常用配置
# 指定数据库的参数
--mysql-db=[db_name]
# 指定host, port, 用户名, 密码, 尽量连写这几个个参数，否则可能出现问题
--mysql-host=localhost --mysql-port=3306 --mysql-user=XXXX --mysql-password=XXXX
#如果不指定数据库名和用户名，应该在mysql终端创建一个sbtest数据库和一个sbtest用户名
mysql> CREATE DATABASE sbtest;
mysql> CREATE USER 'sbtest'@'localhost';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'sbtest'@'localhost';
mysql> FLUSH PRIVILEGES;
# 指定创建表的时候的参数, 比如这里是指定创建一个页大小为8k的压缩形式的表
--mysql-table-options="ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8"
# 指定测试的分布
--oltp-dist-type=gaussian[normol, special]
- 注意：
-
对于默认情况下安装的mysql，如果需要用sysbench测得oltp数据，需要指明用户名、密码（否则默认用sbtest登录，可能不存在这个用户导致无法登陆）和用于测试的数据库（否则sysbench默认的sbtest数据库在mysql并未默认创建）。
-
prepare时
sysbench --max-requests=2000 --test=oltp --mysql-user=root --mysql-password=1234 --oltp-table-size=10000000 prepare就可以成功创建10000000个records，但是sysbench --max-requests=2000 --test=oltp --mysql-user=root --oltp-table-size=10000000 --mysql-password=1234 prepare一个record也不会创建，只是创建了table，可能是因为--oltp-table-size选项不可以夹在mysql账号和密码之间。。。 -
0.4版本是ubuntu apt会安装的版本，但是从0.5开始，sysbench才支持多表测试，需要从github下载源码编译安装。
- yum install bzr
- bzr branch lp:~percona-dev/perconatools/tpcc-mysql
- cd tpcc-mysql/src/; make
如果make出错，可能是因为没有找到安装mysql的路径，可以在/etc/profile加入：
#set mysql_home and tpcc
export MYSQL_HOME=/PATH/TO/MYSQLHOME/
export C_INLUDE_PATH=$MYSQL_HOME/include
export LD_LIBRARY_PATH=$MYSQL_HOME/lib
export PATH=$MYSQL_HOME/bin:$PATH
cd PATH_TO_TPCC
mysql -u root -p[PASSWORD] -e "CREATE DATABASE tpcc;"
mysql -u root -p[PASSWORD] tpcc < create_table.sql
mysql -u root -p[PASSWORD] tpcc < add_fkey_idx.sql
./tpcc_load localhost tpcc root 1234 [WAREHOUSE_NUM]
./tpcc_start -hlocalhost -d tpcc -u[UESR] -p[PASSWORD] -w [WAREHOUSE_NUM] -c [CONNECT_NUM] -r [WARMUPTIME_IN_SECONDS] -l [TESTTIME_IN_SECONDS] -f [REPORTING_FILENAME]
# e.g.
./tpcc_start -hlocalhost -d tpcc -u root -p 1234 -w $WH -c 4 -r $WARM -l $TEST -fjcreporttest0
最后一行就是TPS值
Wiki: wiki.jcix.top ~聚沙成塔~ Blog: blog.jcix.top