— Sysbench测试
Sysbench的安装请参考：http://www.voidcn.com/article/p-dbniavbb-bao.html
下载地址：wget http://down1.chinaunix.net/distfiles/sysbench-0.4.10.tar.gz
**1 CPU测试
**sysbench
采用寻找最大素数的方式来测试CPU
的性能
[root@xx sysbench-0.4.12]# sysbench –test=cpu –cpu-max-prime=2000 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing CPU performance benchmark
Threads started!
WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.000000
WARNING: Percentile statistics will be inaccurate
Done.
Maximum prime number checked in CPU test: 2000
Test execution summary:
total time: 1.5034s
total number of events: 10000
total time taken by event execution: 1.4998
per-request statistics:
min: 0.00ms
avg: 0.15ms
max: 0.57ms
approx. 95 percentile: 0.31ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 1.4998/0.00
2 线程测试
sysbench –test=threads –num-threads=64 –thread-yields=100 –thread-locks=2 run
[root@xx sysbench-0.4.12]# sysbench –test=threads –num-threads=64 –thread-yields=100 –thread-locks=2 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 64
Doing thread subsystem performance test
Thread yields per test: 100 Locks used: 2
Threads started!
WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.000000
WARNING: Percentile statistics will be inaccurate
Done.
Test execution summary:
total time: 2.0189s
total number of events: 10000
total time taken by event execution: 128.2852
per-request statistics:
min: 0.00ms
avg: 12.83ms
max: 108.17ms
approx. 95 percentile: 42.09ms
Threads fairness:
events (avg/stddev): 156.2500/12.16
execution time (avg/stddev): 2.0045/0.00
**3 文件IO性能测试
**
首先生成需要的测试文件，文件总大小1000M
，16
个并发线程，随机读写模式。执行完后会在当前目录下生成一堆小文件。
**3.1 准备测试文件：sysbench –test=fileio –num-threads=16 –file-total-size=1000M –file-test-mode=rndrw prepare
**[root@xx sysbench-0.4.12]# sysbench –test=fileio –num-threads=16 –file-total-size=1000M –file-test-mode=rndrw prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark
128 files, 8000Kb each, 1000Mb total
Creating files for the test…
3.2 执行测试
sysbench –test=fileio –num-threads=16 –file-total-size=1000M –file-test-mode=rndrw run
[root@xx sysbench-0.4.12]# sysbench –test=fileio –num-threads=16 –file-total-size=1000M –file-test-mode=rndrw run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Extra file open flags: 0
128 files, 7.8125Mb each
1000Mb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Threads started!
WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.000000
WARNING: Percentile statistics will be inaccurate
Done.
Operations performed: 6007 Read, 4005 Write, 12675 Other = 22687 Total
**Read 93.859Mb Written 62.578Mb Total transferred 156.44Mb (214.4Mb/sec) 看到这里发现write很高，我这里是
SSD，普通机械磁盘可能就没有这么高了。
**13721.48 Requests/sec executed
Test execution summary:
total time: 0.7297s
total number of events: 10012
total time taken by event execution: 1.3667
per-request statistics:
min: 0.00ms
avg: 0.14ms
max: 20.50ms
approx. 95 percentile: 0.41ms
Threads fairness:
events (avg/stddev): 625.7500/159.20
execution time (avg/stddev): 0.0854/0.02
**3.3 清理测试生成的临时文件
**sysbench –test=fileio –num-threads=16 –file-total-size=1000M –file-test-mode=rndrw cleanup
sysbench 0.4.12: multi-threaded system evaluation benchmark
Removing test files…
** 4. mutex测试
sysbench –test=mutex –num-threads=16 –mutex-num=1024 –mutex-locks=10000 –mutex-loops=5000 run
**[root@xx sysbench-0.4.12]# sysbench –test=mutex –num-threads=16 –mutex-num=1024 –mutex-locks=10000 –mutex-loops=5000 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Doing mutex performance test
Threads started!
Done.
Test execution summary:
total time: 0.1167s
total number of events: 16
total time taken by event execution: 1.7472
per-request statistics:
min: 96.94ms
avg: 109.20ms
max: 114.26ms
approx. 95 percentile: 114.19ms
Threads fairness:
events (avg/stddev): 1.0000/0.00
execution time (avg/stddev): 0.1092/0.01
** 5，内存测试
** sysbench –test=memory –num-threads=512 –memory-block-size=262144 –memory-total-size=32G run
[root@xx sysbench-0.4.12]# sysbench –test=memory –num-threads=512 –memory-block-size=262144 –memory-total-size=32G run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 512
Doing memory operations speed test
Memory block size: 256K
Memory transfer size: 32768M
Memory operations type: write
Memory scope type: global
Threads started!
WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.000000
(last message repeated 1 times)
WARNING: Percentile statistics will be inaccurate
(last message repeated 1 times)
Done.
Operations performed: 131072 (60730.95 ops/sec)
32768.00 MB transferred (15182.74 MB/sec)
Test execution summary:
total time: 2.1582s
total number of events: 131072
total time taken by event execution: 643.2354
per-request statistics:
min: 0.00ms
avg: 4.91ms
max: 1173.07ms
approx. 95 percentile: 0.42ms
Threads fairness:
events (avg/stddev): 256.0000/84.51
execution time (avg/stddev): 1.2563/0.32
**6 MySQL数据库测试 select
**
首先需要创建默认的test
数据库,
或者使用�mysql-db
指定一个已经存在的数据库生成测试数据，引擎为innodb,
表大小为30000000
条记录
6.1 准备数据
–oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
–oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –mysql-db=test –oltp-table-size=300000000 –oltp-table-name=t1 –oltp-nontrx-mode=insert –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock prepare
报错如下：
FATAL: no database driver specified
FATAL: failed to initialize database driver!
test
库没有建立，去create database
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
sysbench 0.4.12: multi-threaded system evaluation benchmark
Creating table 't1'…
Creating 30000000 records in table 't1'…
real 13m49.102s
user 0m11.982s
sys 0m0.646s
**6.2 执行测试
time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –mysql-db=test –oltp-table-size=300000000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
**Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (350.28 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (6655.38 per sec.)
other operations: 20000 (700.57 per sec.)
Test execution summary:
total time: 28.5483s
total number of events: 10000
total time taken by event execution: 28.4897
per-request statistics:
min: 1.80ms
avg: 2.85ms
max: 47.35ms
approx. 95 percentile: 5.95ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 28.4897/0.00
real 0m28.646s
user 0m2.270s
sys 0m1.516s
**6.3 clean test data
**time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –mysql-db=test –oltp-table-size=30000000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock clearup
7 MySQL数据库测试 insert 19304W
**7.1 执行insert测试，数据准备参考6.1步骤
–oltp-nontrx-mode=insert
time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=insert –mysql-db=test –oltp-table-size=193040000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
**[root@xx bin]# time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=insert –mysql-db=test –oltp-table-size=193040000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (222.06 per sec.)
deadlocks: 0 (0.00 per sec.)
** read/write requests: 190000 (4219.22 per sec.)
** other operations: 20000 (444.13 per sec.)
Test execution summary:
total time: 45.0321s
total number of events: 10000
total time taken by event execution: 44.9398
per-request statistics:
min: 1.96ms
avg: 4.49ms
max: 37.26ms
approx. 95 percentile: 6.96ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 44.9398/0.00
real 0m45.063s
user 0m3.279s
sys 0m2.295s
**7.2 –oltp-nontrx-mode=update_key 修改测试 带index
time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=update_key –mysql-db=test –oltp-table-size=193040000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
**[root@xx bin]# time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=update_key –mysql-db=test –oltp-table-size=193040000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (304.16 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (5778.95 per sec.)
other operations: 20000 (608.31 per sec.)
Test execution summary:
total time: 32.8780s
total number of events: 10000
total time taken by event execution: 32.8053
per-request statistics:
min: 1.89ms
avg: 3.28ms
max: 28.03ms
approx. 95 percentile: 6.06ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 32.8053/0.00
real 0m32.909s
user 0m2.628s
sys 0m1.683s
**7.3 –oltp-nontrx-mode=delete 删除测试
**time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=delete –mysql-db=test –oltp-table-size=193040000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
[root@xx bin]# time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=delete –mysql-db=test –oltp-table-size=193040000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (330.13 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (6272.53 per sec.)
other operations: 20000 (660.27 per sec.)
Test execution summary:
total time: 30.2908s
total number of events: 10000
total time taken by event execution: 30.2212
per-request statistics:
min: 1.84ms
avg: 3.02ms
max: 13.14ms
approx. 95 percentile: 6.02ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 30.2212/0.00
real 0m30.328s
user 0m2.416s
sys 0m1.658s
**7.4 –oltp-nontrx-mode=update_nokey 修改测试 No Index
**–oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=update_nokey –mysql-db=test –oltp-table-size=193040000 –max-requests –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run **
**[root@xx bin]# time sysbench –test=oltp –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –oltp-nontrx-mode=update_nokey –mysql-db=test –oltp-table-size=193040000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (319.11 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (6063.01 per sec.)
other operations: 20000 (638.21 per sec.)
Test execution summary:
total time: 31.3376s
total number of events: 10000
total time taken by event execution: 31.2650
per-request statistics:
min: 1.83ms
avg: 3.13ms
max: 13.44ms
approx. 95 percentile: 6.11ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 31.2650/0.00
real 0m31.367s
user 0m2.422s
sys 0m1.680s
**7.4 总结
**–oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
–max-requests=N limit for total number of requests [10000]
如果这里不指定–max-requests，默认为
10000，就是执行1W次操作就结束了，如果要做压力测试的话，就需要手动设置此值。
–num-threads=N number of threads to use [1] (clients to access mysql db)
这里指的是有多少个mysql clents来访问mysql服务器，用show full processlist就可以看到大概的记录数。
7.5 接下来重新压力测试，设置2000W request测试。
**录入测试数据 2000W
**time sysbench –test=oltp –oltp-test-mode=nontrx –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –num-threads=500 –max-requests=20000000 –oltp-nontrx-mode=insert –mysql-db=test –oltp-table-size=20000000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock prepare
Creating table 't1'…
Creating 20000000 records in table 't1'…
real 8m46.287s
user 0m8.048s
sys 0m0.428s
**7.6开始测试 纯粹写
**nohup time sysbench –test=oltp –oltp-test-mode=nontrx –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –num-threads=500 –max-requests=20000000 –oltp-nontrx-mode=insert –mysql-db=test –oltp-table-size=20000000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run >3.log &
time sysbench –test=oltp –oltp-test-mode=nontrx –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –num-threads=500 –max-requests=20000000 –oltp-nontrx-mode=insert –mysql-db=test –oltp-table-size=20000000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run 1> 2.log
OLTP test statistics:
queries performed:
read: 0
write: 20007732
other: 0
total: 20007732
transactions: 20007732 (8556.00 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 20007732 (8556.00 per sec.)
other operations: 0 (0.00 per sec.)
Test execution summary:
total time: 2338.4451s
total number of events: 20007732
total time taken by event execution: 1169095.8829
per-request statistics:
min: 0.35ms
avg: 58.43ms
max: 317.35ms
approx. 95 percentile: 83.54ms
Threads fairness:
events (avg/stddev): 40015.4640/13.47
execution time (avg/stddev): 2338.1918/0.02
900.69user 667.19system 39:07.55elapsed 66%CPU (0avgtext+0avgdata 128176maxresident)k
0inputs+16outputs (0major+8251minor)pagefaults 0swaps
**7.7 混合读写测试
**nohup time sysbench –test=oltp –oltp-test-mode=complex –mysql-table-engine=innodb –mysql-user=root –db-driver=mysql –num-threads=500 –max-requests=20000000 –oltp-nontrx-mode=select –mysql-db=test –oltp-table-size=20000000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run >4.log &
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 500
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 20000000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 280001652
write: 100000590
other: 40000236
total: 420002478
transactions: 20000118 (1150.48 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 380002242 (21859.10 per sec.)
other operations: 40000236 (2300.96 per sec.)
Test execution summary:
total time: 17384.1703s
total number of events: 20000118
total time taken by event execution: 8691857.1553
per-request statistics:
min: 28.97ms
avg: 434.59ms
max: 14634.16ms
approx. 95 percentile: 856.09ms
Threads fairness:
events (avg/stddev): 40000.2360/119.14
execution time (avg/stddev): 17383.7143/0.06
5142.95user 4422.48system 4:49:50elapsed 55%CPU (0avgtext+0avgdata 256224maxresident)k
2440inputs+16outputs (15major+16290minor)pagefaults 0swaps
** avg: 434.59ms**
**看到这里，大leader说这个数值偏高了，在game领域，最好<10ms，并且他猜测我是local测试的，local500个线程也许会
** 对db server有一些压力的，建议我remote测试访问测试下看
7.8.1 混合读写测试 远程读写数据准备，远程跑后台进程准备数据。
nohup time sysbench –test=oltp –oltp-test-mode=nontrx –mysql-table-engine=innodb –mysql-host=eanprdnucmydbc048db01.eao.abn-iad.ea.com –mysql-user=chunman –mysql-password=hRhsYzbm –db-driver=mysql –num-threads=500 –max-requests=20000000 –oltp-nontrx-mode=select –mysql-db=test –oltp-table-size=20000000 –oltp-table-name=t1 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock prepare > 6.log &
sysbench 0.4.12: multi-threaded system evaluation benchmark
Creating table 't1'…
Creating 20000000 records in table 't1'…
8.48user 1.96system 40:23.30elapsed 0%CPU (0avgtext+0avgdata 12224maxresident)k
0inputs+8outputs (0major+854minor)pagefaults 0swaps
**7.8.2 混合读写测试 远程读写
**nohup time sysbench –test=oltp –oltp-test-mode=complex –mysql-table-engine=innodb –mysql-host=eanprdnucmydbc048db01.eao.abn-iad.ea.com –mysql-user=chunman –mysql-password=hRhsYzbm –db-driver=mysql –mysql-db=test –oltp-table-name=t1 –num-threads=500 –max-requests=20000000 –oltp-nontrx-mode=select –oltp-table-size=20000000 –mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run >5.log &
ALERT: Error: failed to determine table 't1' type!
ALERT: MySQL error:
FATAL: failed to get database capabilities!
0.00user 0.00system 0:00.01elapsed 64%CPU (0avgtext+0avgdata 9856maxresident)k
0inputs+8outputs (0major+706minor)pagefaults 0swaps
报这个错误，是因为数据没有，7.8.1
没有执行，需要重新录入测试数据。
后续准备做mysql的性能监控工具。
mysqlslap 性能测试工具
2013-12-06 14:41:08
分类：
MySQL
— ===================
— mysqlslap 性能测试工具
— ===================
**常用参数
**–concurrency #代表并发数量，多个可以用逗号隔开。例如：–concurrency=50,200,500
–engines #代表要测试的引擎，可以有多个，用分隔符隔开。例如：–engines=myisam,innodb,memory
–iterations #代表要在不同并发环境下，各自运行测试多少次。
–auto-generate-sql #代表用mysqlslap工具自己生成的SQL脚本来测试并发压力。
–auto-generate-sql-add-auto-increment #代表对生成的表自动添加auto_increment列，从5.1.18版本开始，
–auto-generate-sql-load-type #代表要测试的环境是读操作还是写操作还是两者混合的（read,write,update,mixed）
–number-of-queries #代表总共要运行多少条查询。
–debug-info #代表要额外输出CPU以及内存的相关信息。
–number-int-cols #代表示例表中的INTEGER类型的属性有几个。
–number-char-cols #代表示例表中的vachar类型的属性有几个。
–create-schema #代表自定义的测试库名称。
–query #代表自定义的测试SQL脚本。
#测试同时不同的存储引擎的性能进行对比：并发50-100，1000次查询
**– default
**mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info
mysqlslap -a –concurrency=50,100 –number-of-queries 3000 –iterations=5 –auto-generate-sql –auto-generate-sql-add-auto-increment –engine=ndbcluster –debug-info
**– mixed
**mysqlslap –defaults-file=/etc/my.cnf –concurrency=100,200,400 –iterations=1 –number-int-cols=4 –number-char-cols=35 –auto-generate-sql –auto-generate-sql-add-autoincrement –auto-generate-sql-load-type=mixed –engine=ndbcluster –number-of-queries=3000000 –debug-info
mysqlslap –defaults-file=/etc/my.cnf –concurrency=500 –iterations=1 –number-int-cols=4 –number-char-cols=35 –auto-generate-sql –auto-generate-sql-add-autoincrement –auto-generate-sql-load-type=mixed –engine=ndbcluster –number-of-queries=3000000 –debug-info
**– write
**mysqlslap –defaults-file=/etc/my.cnf –concurrency=500 –iterations=1 –number-int-cols=4 –number-char-cols=35 –auto-generate-sql –auto-generate-sql-add-autoincrement –auto-generate-sql-load-type=write –engine=ndbcluster –number-of-queries=3000000 –debug-info
mysqlslap –defaults-file=/etc/my.cnf –concurrency=500,600,700,800,900 –iterations=1 –number-int-cols=4 –number-char-cols=35 –auto-generate-sql –auto-generate-sql-add-autoincrement –auto-generate-sql-load-type=write –engine=ndbcluster –number-of-queries=3000000 –debug-info
MySQL DB Server 上面安装 sysbench 作压力测试
2013-12-06 14:42:01
分类：
MySQL
down tar.gz
wget http://downloads.sourceforge.net/project/sysbench/sysbench/0.4.12/sysbench-0.4.12.tar.gz
**1 login
**Tools -> SSH File Transfer — input the config and transfer into base server.
**2 base -> db control server
**scp sysbench-0.4.12.tar.gz cm@xxxxbase.com:/tmp/
ssh eanprdmydbctl.eao.abn-iad.ea.com
**3 db control server -> db
**scp sysbench-0.4.12.tar.gz cm@xxxxdbcrol.com:/tmp/
4 tar -xvf sysbench-0.4.12.tar.gz
以上方法适用于 MySQL 安装在标准默认目录下的情况，如果 MySQL 并不是安装在标准目录下的话，那么就需要自己指定 MySQL 的路径了。比如我的 MySQL 喜欢自己安装在 /usr/local/mysql 下，则按照以下方法编译：
**5 configure
**./configure –with-mysql-includes=/opt/mysql/product/mysql/include –with-mysql-libs=/opt/mysql/product/mysql/lib
[root@sysbench-0.4.12]# ./configure –with-mysql-includes=/opt/mysql/product/mysql/include –with-mysql-libs=/opt/mysql/product/mysql/lib
checking build system type… Invalid configuration
1 | 1` |
x86_64-unknown-linux-': machine
1 | 1` |
x86_64-unknown-linux' not recognized
configure: error: /bin/sh config/config.sub x86_64-unknown-linux- failed
[root@sysbench-0.4.12]#
reference: http://wangwei.cao.blog.163.com/blog/static/102362526201110255102215/
[]yum install libtool
OK,success
checking for cc_r… gcc
checking for sqrt in -lm… yes
configure: error: invalid MySQL root directory: /opt/mysql/product/mysql
[root@sysbench-0.4.12]# ./configure –with-mysql=/opt/mysql/product/5.5.25a –with-mysql-libs=/opt/mysql/product/5.5.25a/lib64/mysql
&& make && make install
checking build system type… x86_64-unknown-linux-gnu
checking host system type… x86_64-unknown-linux-gnu
。。。。。
config.status: executing depfiles commands
config.status: executing libtool commands
6 make
— make命令报错
-bash: make: command not found， make没有安装，需要重新安装
yum install make 搞定
drv_mysql.c:879: error: ???MYSQL_RES??? undeclared (first use in this function)
drv_mysql.c:879: error: expected expression before ???)??? token
make[3]: *** [libsbmysql_a-drv_mysql.o] Error 1
make[3]: Leaving directory
1 | 1` |
/root/sysbench-0.4.12/sysbench/drivers/mysql''
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory
1 | 1` |
/root/sysbench-0.4.12/sysbench/drivers''
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory
1 | 1` |
/root/sysbench-0.4.12/sysbench''
make: *** [all-recursive] Error 1
[]**./configure –with-mysql-libs=/usr/local/mysql/lib/mysql/ –with-mysql-includes=/usr/local/mysql/include/mysql/
OK, success !**
7 make install
OK, success !
**8 如果万一安装失败,还有最后一招: yum install sysbench 可以尝试尝试。 **
****Loaded plugins: product-id, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package sysbench.x86_64 0:0.4.12-5.el6 will be installed
–> Processing Dependency: libpq.so.5()(64bit) for package: sysbench-0.4.12-5.el6.x86_64
–> Running transaction check
—> Package postgresql-libs.x86_64 0:8.4.13-1.el6_3 will be installed
–> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================
Package Arch Version Repository Size
Installing:
sysbench x86_64 0.4.12-5.el6 uxdepot-epel-latest-rhel6-server-x86_64 74 k
Installing for dependencies:
postgresql-libs x86_64 8.4.13-1.el6_3 uxdepot-latest-rhel6-server-x86_64 200 k
Transaction Summary
Install 2 Package(s)
Total download size: 274 k
Installed size: 792 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql-libs-8.4.13-1.el6_3.x86_64.rpm | 200 kB 00:00
(2/2): sysbench-0.4.12-5.el6.x86_64.rpm | 74 kB 00:00
Total 7.7 MB/s | 274 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : postgresql-libs-8.4.13-1.el6_3.x86_64 1/2
Installing : sysbench-0.4.12-5.el6.x86_64 2/2
Verifying : postgresql-libs-8.4.13-1.el6_3.x86_64 1/2
Verifying : sysbench-0.4.12-5.el6.x86_64 2/2
Installed:
sysbench.x86_64 0:0.4.12-5.el6
Dependency Installed:
postgresql-libs.x86_64 0:8.4.13-1.el6_3
Complete!
===============================
安装失败1：
报错 /usr/bin/ld: cannot find -lmysqlclient_r
查找库文件存在，于是建立链接：
ln -s /usr/local/mysql/lib/libmysqlclient.so.18 libmysqlclient.so.18.0.0
如下面内容：
[root@master lib]# pwd
/usr/local/mysql/lib
[root@master lib]# ls -lh
total 12M
-rw-r–r– 1 mysql mysql 7.8M Feb 14 09:54 libmysqlclient.a
lrwxrwxrwx 1 mysql mysql 16 Feb 14 09:56 libmysqlclient_r.a -> libmysqlclient.a
lrwxrwxrwx 1 mysql mysql 17 Feb 14 09:56 libmysqlclient_r.so -> libmysqlclient.so
lrwxrwxrwx 1 mysql mysql 17 Feb 14 09:56 libmysqlclient_r.so.18 -> libmysqlclient.so
lrwxrwxrwx 1 mysql mysql 17 Feb 14 09:56 libmysqlclient_r.so.18.0.0 -> libmysqlclient.so
lrwxrwxrwx 1 mysql mysql 20 Feb 14 09:56 libmysqlclient.so -> libmysqlclient.so.18
lrwxrwxrwx 1 mysql mysql 24 Feb 14 09:56 **libmysqlclient.so.18 -> libmysqlclient.so.18.0.0
**-rwxr-xr-x 1 mysql mysql 4.3M Feb 14 09:54 libmysqlclient.so.18.0.0
-rw-r–r– 1 mysql mysql 9.3K Feb 14 09:53 libmysqlservices.a
drwxr-xr-x 3 mysql mysql 4.0K Feb 14 09:56 plugin
做一些软链接即可， 我做的如下：
total 20656
lrwxrwxrwx 1 root root 24 Nov 28 18:35 libmysqlclient.a -> libperconaserverclient.a
lrwxrwxrwx 1 root root 16 Nov 28 18:36 libmysqlclient_r.a -> libmysqlclient.a
lrwxrwxrwx 1 root root 17 Nov 28 18:35 libmysqlclient_r.so -> libmysqlclient.so
lrwxrwxrwx 1 root root 26 Nov 28 18:28 libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0
-rwxr-xr-x 1 root root 1595208 Nov 28 18:27 libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 root root 17 Nov 28 18:35 libmysqlclient_r.so.18 -> libmysqlclient.so
lrwxrwxrwx 1 root root 17 Nov 28 18:34 libmysqlclient_r.so.18.0.0 -> libmysqlclient.so
lrwxrwxrwx 1 root root 20 Nov 28 18:34 libmysqlclient.so -> libmysqlclient.so.18
lrwxrwxrwx 1 root root 27 Aug 28 17:27 libmysqlclient.so.18 -> libmysqlclient.so.18.0.0bak
lrwxrwxrwx 1 root root 32 Aug 28 17:27 libmysqlclient.so.18.0.0 -> libperconaserverclient.so.18.0.0
-rwxr-xr-x 1 101 48 6844990 Nov 23 2011 libmysqlclient.so.18.0.0bak
-rw-r–r– 1 root root 9576 Apr 9 2014 libmysqlservices.a
-rw-r–r– 1 root root 8186944 Apr 9 2014 libperconaserverclient.a
lrwxrwxrwx 1 root root 24 Aug 28 17:27 libperconaserverclient_r.a -> libperconaserverclient.a
lrwxrwxrwx 1 root root 25 Aug 28 17:27 libperconaserverclient_r.so -> libperconaserverclient.so
lrwxrwxrwx 1 root root 25 Aug 28 17:27 libperconaserverclient_r.so.18 -> libperconaserverclient.so
lrwxrwxrwx 1 root root 25 Aug 28 17:27 libperconaserverclient_r.so.18.0.0 -> libperconaserverclient.so
lrwxrwxrwx 1 root root 28 Aug 28 17:27 libperconaserverclient.so -> libperconaserverclient.so.18
lrwxrwxrwx 1 root root 32 Aug 28 17:27 libperconaserverclient.so.18 -> libperconaserverclient.so.18.0.0
-rwxr-xr-x 1 root root 4498328 Apr 9 2014 libperconaserverclient.so.18.0.0
软件下载地址：
wget http://down1.chinaunix.net/distfiles/sysbench-0.4.10.tar.gz
