目录
Evaluating Tools
使用
说明：
- 启动一次全新的测试时一定要删除（cleanup）之前创建的测试生成的数据库，否则无法创建测试，以及其它报错。如下列举一此可能报错。
- 执行sysbench压测的系统应安装好mysql-client ，因为测试时需要连接目标数据库。限于篇幅，推荐安装mysql8版本。
- ssh 公钥登陆认证需要可以完成配置。
- 本篇测试环境：centos76+mysql-client8.0+sysbench20200214master-git+python2?
准备数据
[root@centos76 ~]# sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=100.64.88.102 --mysql-port=3306 --mysql-user=root --mysql-password='Mysql_57' --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=3000 --report-interval=10 --threads=128 --time=120 prepare sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2) Initializing worker threads... Creating table 'sbtest4'...Creating table 'sbtest8'...Creating table 'sbtest2'...Creating table 'sbtest3'...Creating table 'sbtest1'...Creating table 'sbtest10'...Creating table 'sbtest5'...Creating table 'sbtest9'...Creating table 'sbtest7'...Creating table 'sbtest6'... #提示：主机配置低时，适当调低参数，否则无法执行完成。（参数 --tables=10 --table-size=3000 ） Inserting 3000 records into 'sbtest8' Inserting 3000 records into 'sbtest7' Inserting 3000 records into 'sbtest4' Inserting 3000 records into 'sbtest3' Inserting 3000 records into 'sbtest9' Inserting 3000 records into 'sbtest5' Inserting 3000 records into 'sbtest10' Inserting 3000 records into 'sbtest1' Inserting 3000 records into 'sbtest2' Inserting 3000 records into 'sbtest6' Creating a secondary index on 'sbtest8'... Creating a secondary index on 'sbtest4'... Creating a secondary index on 'sbtest7'... Creating a secondary index on 'sbtest3'... Creating a secondary index on 'sbtest1'... Creating a secondary index on 'sbtest5'... Creating a secondary index on 'sbtest9'... Creating a secondary index on 'sbtest6'... Creating a secondary index on 'sbtest2'... Creating a secondary index on 'sbtest10'... [root@centos76 ~]#
#提前准备好测试数据库的连接信息，特别是提前创建好一个空的测试库，如sbtest，数据库可以是本地也可以是远程数据库。 # FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed FATAL: unable to connect to MySQL server on host '100.64.88.102', port 3306, aborting... FATAL: error 1049: Unknown database 'sbtest' #安装myql-devel等依赖组件的安装。如果是自动化脚本安装可以完成自动安装（建议）。
测试
[root@centos76 ~]# sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=100.64.88.102 --mysql-port=3306 --mysql-user=root --mysql-password='Mysql_57' --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=3000 --report-interval=10 --threads=128 --time=120 run
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 128
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 128 tps: 629.19 qps: 10199.68 (r/w/o: 8928.51/0.00/1271.18) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 128 tps: 683.54 qps: 10929.89 (r/w/o: 9563.02/0.00/1366.87) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 128 tps: 660.06 qps: 10562.52 (r/w/o: 9242.31/0.00/1320.22) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 128 tps: 646.82 qps: 10356.82 (r/w/o: 9063.18/0.00/1293.64) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 128 tps: 629.78 qps: 10073.90 (r/w/o: 8814.55/0.00/1259.35) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 128 tps: 647.64 qps: 10364.09 (r/w/o: 9068.61/0.00/1295.49) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 128 tps: 662.60 qps: 10602.77 (r/w/o: 9277.46/0.00/1325.31) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 128 tps: 627.00 qps: 10036.17 (r/w/o: 8782.17/0.00/1254.00) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 128 tps: 632.59 qps: 10120.19 (r/w/o: 8855.01/0.00/1265.19) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 128 tps: 653.15 qps: 10445.88 (r/w/o: 9139.69/0.00/1306.20) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 128 tps: 660.97 qps: 10578.86 (r/w/o: 9256.83/0.00/1322.03) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 128 tps: 663.49 qps: 10612.36 (r/w/o: 9285.78/0.00/1326.58) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1093344
write: 0
other: 156192
total: 1249536
transactions: 78096 (649.74 per sec.)
queries: 1249536 (10395.90 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1938s
total number of events: 78096
Latency (ms):
min: 16.12
avg: 196.83
max: 4323.18
95th percentile: 297.92
sum: 15371522.95
Threads fairness:
events (avg/stddev): 610.1250/18.94
execution time (avg/stddev): 120.0900/0.06
[root@centos76 ~]#
主要关注指标
| 指标 | 说明 |
|---|---|
response time avg | 平均响应时间（后面的95%的大小可以通过–percentile=98的方式去更改）。 |
transactions | 精确的说是这一项后面的TPS，但如果使用了–skip-trx=on，这项事务数为0，需要用total number of events去除以总时间，得到tps（其实还可以分为读tps和写tps）。 |
queries | 用它除以总时间，得到吞吐量QPS。 |
当然还有一些系统层面的cpu，io，mem相关指标。
清理与删除测试数据
[root@centos76 ~]# sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=100.64.88.102 --mysql-port=3306 --mysql-user=root --mysql-password='Mysql_57' --mysql-db=sbtest --db-driver=mysql --tables=10 --table-size=3000 --report-interval=10 --threads=128 --time=120 cleanup sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2) Dropping table 'sbtest1'... Dropping table 'sbtest2'... Dropping table 'sbtest3'... Dropping table 'sbtest4'... Dropping table 'sbtest5'... Dropping table 'sbtest6'... Dropping table 'sbtest7'... Dropping table 'sbtest8'... Dropping table 'sbtest9'... Dropping table 'sbtest10'... [root@centos76 ~]#
Quote & Appendix
参考以下，并修改相关完成测试，修改细节详见本文。
- https://www.jianshu.com/p/4a37a6a452d9 张伟科 基准测试工具Sysbench 2018.03.16 10:38:26 [ 推荐 ]
Step By Step
sysbench install
[root@centos76 ~]# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash Detected operating system as centos/7. Checking for curl... Detected curl... Downloading repository file: https://packagecloud.io/install/repositories/akopytov/sysbench/config_file.repo?os=centos&dist=7&source=script done. Installing pygpgme to verify GPG signatures... Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * centos-sclo-rh: mirrors.huaweicloud.com * centos-sclo-sclo: mirrors.huaweicloud.com * remi: mirrors.tuna.tsinghua.edu.cn * remi-safe: mirrors.tuna.tsinghua.edu.cn akopytov_sysbench-source/signature | 833 B 00:00:00 Retrieving key from https://packagecloud.io/akopytov/sysbench/gpgkey Importing GPG key 0x04DCFD39: Userid : "https://packagecloud.io/akopytov/sysbench-prerelease (https://packagecloud.io/docs#gpg_signing) <support@packagecloud.io>" Fingerprint: 9789 8d69 f99e e5ca c462 a0f8 cf10 4890 04dc fd39 From : https://packagecloud.io/akopytov/sysbench/gpgkey akopytov_sysbench-source/signature | 1.0 kB 00:00:00 !!! base | 3.6 kB 00:00:00 centos-sclo-rh | 3.0 kB 00:00:00 centos-sclo-sclo | 2.9 kB 00:00:00 docker-ce-stable | 3.5 kB 00:00:00 epel | 5.3 kB 00:00:00 extras | 2.9 kB 00:00:00 nginx-mainline | 2.9 kB 00:00:00 remi | 3.0 kB 00:00:00 remi-safe | 3.0 kB 00:00:00 updates | 2.9 kB 00:00:00 (1/3): remi/primary_db | 2.6 MB 00:00:01 (2/3): remi-safe/primary_db | 1.7 MB 00:00:01 (3/3): centos-sclo-rh/x86_64/primary_db | 4.2 MB 00:00:05 akopytov_sysbench-source/primary | 1.9 kB 00:00:04 akopytov_sysbench-source 14/14 Package pygpgme-0.3-9.el7.x86_64 already installed and latest version Nothing to do Installing yum-utils... Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * centos-sclo-rh: mirrors.huaweicloud.com * centos-sclo-sclo: mirrors.huaweicloud.com * remi: mirrors.tuna.tsinghua.edu.cn * remi-safe: mirrors.tuna.tsinghua.edu.cn Resolving Dependencies --> Running transaction check ---> Package yum-utils.noarch 0:1.1.31-52.el7 will be installed --> Processing Dependency: python-kitchen for package: yum-utils-1.1.31-52.el7.noarch --> Processing Dependency: libxml2-python for package: yum-utils-1.1.31-52.el7.noarch --> Running transaction check ---> Package libxml2-python.x86_64 0:2.9.1-6.el7_2.3 will be installed ---> Package python-kitchen.noarch 0:1.1.1-5.el7 will be installed --> Processing Dependency: python-chardet for package: python-kitchen-1.1.1-5.el7.noarch --> Running transaction check ---> Package python-chardet.noarch 0:2.2.1-3.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================================================================================== Package Arch Version Repository Size ===================================================================================================================================================================== Installing: yum-utils noarch 1.1.31-52.el7 base 121 k Installing for dependencies: libxml2-python x86_64 2.9.1-6.el7_2.3 base 247 k python-chardet noarch 2.2.1-3.el7 base 227 k python-kitchen noarch 1.1.1-5.el7 base 267 k Transaction Summary ===================================================================================================================================================================== Install 1 Package (+3 Dependent packages) Total download size: 862 k Installed size: 4.3 M Downloading packages: (1/4): python-chardet-2.2.1-3.el7.noarch.rpm | 227 kB 00:00:00 (2/4): libxml2-python-2.9.1-6.el7_2.3.x86_64.rpm | 247 kB 00:00:00 (3/4): yum-utils-1.1.31-52.el7.noarch.rpm | 121 kB 00:00:00 (4/4): python-kitchen-1.1.1-5.el7.noarch.rpm | 267 kB 00:00:00 --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 1.1 MB/s | 862 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : python-chardet-2.2.1-3.el7.noarch 1/4 Installing : python-kitchen-1.1.1-5.el7.noarch 2/4 Installing : libxml2-python-2.9.1-6.el7_2.3.x86_64 3/4 Installing : yum-utils-1.1.31-52.el7.noarch 4/4 Verifying : python-kitchen-1.1.1-5.el7.noarch 1/4 Verifying : yum-utils-1.1.31-52.el7.noarch 2/4 Verifying : libxml2-python-2.9.1-6.el7_2.3.x86_64 3/4 Verifying : python-chardet-2.2.1-3.el7.noarch 4/4 Installed: yum-utils.noarch 0:1.1.31-52.el7 Dependency Installed: libxml2-python.x86_64 0:2.9.1-6.el7_2.3 python-chardet.noarch 0:2.2.1-3.el7 python-kitchen.noarch 0:1.1.1-5.el7 Complete! Generating yum cache for akopytov_sysbench... Importing GPG key 0x04DCFD39: Userid : "https://packagecloud.io/akopytov/sysbench-prerelease (https://packagecloud.io/docs#gpg_signing) <support@packagecloud.io>" Fingerprint: 9789 8d69 f99e e5ca c462 a0f8 cf10 4890 04dc fd39 From : https://packagecloud.io/akopytov/sysbench/gpgkey Generating yum cache for akopytov_sysbench-source... The repository is setup! You can now install packages. [root@centos76 ~]# sudo yum -y install sysbench Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * centos-sclo-rh: mirrors.huaweicloud.com * centos-sclo-sclo: mirrors.huaweicloud.com * remi: mirrors.tuna.tsinghua.edu.cn * remi-safe: mirrors.tuna.tsinghua.edu.cn Resolving Dependencies --> Running transaction check ---> Package sysbench.x86_64 0:1.0.19-1.el7 will be installed --> Processing Dependency: libpq.so.5()(64bit) for package: sysbench-1.0.19-1.el7.x86_64 --> Running transaction check ---> Package postgresql-libs.x86_64 0:9.2.24-2.el7_7 will be installed --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================================================================================== Package Arch Version Repository Size ===================================================================================================================================================================== Installing: sysbench x86_64 1.0.19-1.el7 akopytov_sysbench 430 k Installing for dependencies: postgresql-libs x86_64 9.2.24-2.el7_7 updates 234 k Transaction Summary ===================================================================================================================================================================== Install 1 Package (+1 Dependent package) Total download size: 664 k Installed size: 1.8 M Downloading packages: (1/2): postgresql-libs-9.2.24-2.el7_7.x86_64.rpm | 234 kB 00:00:00 sysbench-1.0.19-1.el7.x86_64.r FAILED https://packagecloud.io/akopytov/sysbench/el/7/x86_64/sysbench-1.0.19-1.el7.x86_64.rpm: [Errno 12] Timeout on https://d28dx6y1hfq314.cloudfront.net/2210/4703/el/7/package_files/578926.rpm?t=1581638595_72acf7e2cbcf381473268e803b9d7645c82b4b7e: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds') Trying other mirror. (2/2): sysbench-1.0.19-1.el7.x86_64.rpm | 430 kB 00:00:02 --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 14 kB/s | 664 kB 00:00:48 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql-libs-9.2.24-2.el7_7.x86_64 1/2 Installing : sysbench-1.0.19-1.el7.x86_64 2/2 Verifying : postgresql-libs-9.2.24-2.el7_7.x86_64 1/2 Verifying : sysbench-1.0.19-1.el7.x86_64 2/2 Installed: sysbench.x86_64 0:1.0.19-1.el7 Dependency Installed: postgresql-libs.x86_64 0:9.2.24-2.el7_7 Complete! [root@centos76 ~]#
use benchmark scripts
| use mysql57_vs_8-benchmark_scripts |
|---|
| download from here mysql57_vs_8-benchmark_scripts-sysbench.zip |
#!/bin/bash #sh sb-prepare.sh host=100.64.88.102 port=3306 user='root' password='password' table_size=3000 rate=20 ps_mode='disable' sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --threads=1 --max-requests=0 --time=3600 --mysql-host=$host --mysql-user=$user --mysql-password=$password --mysql-port=$port --tables=10 --report-interval=1 --skip-trx=on --table-size=$table_size --rate=$rate --db-ps-mode=$ps_mode prepare参考章节Evaluating Tools 准备数据
sb-run.sh
sb-run.sh调用脚本cpu-checker.sh和innodb-ops-parser.py
#!/usr/bin/env bash
host=100.64.88.102
port=3306
user="root"
password="password"
table_size=3000
tables=10
rate=20
ps_mode='disable'
threads=1
events=0
time=5
trx=100
path=$PWD
counter=1
echo "thread,cpu" > $host-cpu.csv
for i in 16 32 64 128 256 512 1024 2048;
do
threads=$i
#mysql -h $host -e "SHOW GLOBAL STATUS" >> $host-global-status.log
/usr/bin/ssh -i /root/.ssh/id_rsa root@$host "mysql -h 100.64.88.102 -pMysql_57 -e 'SHOW GLOBAL STATUS'" >> $host-global-status.log
tmpfile=$path/${host}-tmp${threads}
touch $tmpfile
/bin/bash cpu-checker.sh $tmpfile $host $threads &
/usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --events=$events --threads=$threads --time=$time --mysql-host=$host --mysql-user=$user --mysql-password=$password --mysql-port=$port --report-interval=1 --skip-trx=on --tables=$tables --table-size=$table_size --rate=$rate --delete_inserts=$trx --order_ranges=$trx --range_selects=on --range-size=$trx --simple_ranges=$trx --db-ps-mode=$ps_mode --mysql-ignore-errors=all run | tee -a $host-sysbench.log
#cp ${tmpfile} ${tmpfile}-bak
echo "${i},"`cat ${tmpfile} | sort -nr | head -1` >> ${host}-cpu.csv
unlink ${tmpfile}
/usr/bin/ssh -i /root/.ssh/id_rsa_mysqlmha root@$host "mysql -h 100.64.88.102 -pMysql_57 -e 'SHOW GLOBAL STATUS'" >> $host-global-status.log
#mysql -h $host -e "SHOW GLOBAL STATUS" >> $host-global-status.log
done
python $path/innodb-ops-parser.py $host
/usr/bin/ssh -i /root/.ssh/id_rsa_mysqlmha root@$host "mysql -h 100.64.88.102 -pMysql_57 -e 'SHOW GLOBAL VARIABLES'" >> $host-global-vars.log
*运行示例
[root@centos76 mysql57_vs_8-benchmark_scripts-master]# sh sb-run.sh mysql: [Warning] Using a password on the command line interface can be insecure. sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 16 Target transaction rate: 20/sec Report intermediate results every 1 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 1s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 12516.09 reconn/s: 0.00 [ 1s ] queue length: 0, concurrency: 12 [ 2s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 23235.42 reconn/s: 0.00 [ 2s ] queue length: 19, concurrency: 16 [ 3s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 23326.45 reconn/s: 0.00 [ 3s ] queue length: 36, concurrency: 16 [ 4s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 24228.50 reconn/s: 0.00 [ 4s ] queue length: 56, concurrency: 16 [ 5s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 24262.34 reconn/s: 0.00 [ 5s ] queue length: 79, concurrency: 16 [ 6s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 23657.51 reconn/s: 0.00 [ 6s ] queue length: 80, concurrency: 16 [ 7s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 23674.74 reconn/s: 0.00 [ 7s ] queue length: 80, concurrency: 16 [ 8s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 24326.26 reconn/s: 0.00 [ 8s ] queue length: 80, concurrency: 16 [ 9s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 24284.94 reconn/s: 0.00 [ 9s ] queue length: 80, concurrency: 16 [ 10s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 23963.44 reconn/s: 0.00 [ 10s ] queue length: 80, concurrency: 16 [ 11s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 23818.52 reconn/s: 0.00 [ 11s ] queue length: 80, concurrency: 16 [ 12s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 22866.30 reconn/s: 0.00 [ 12s ] queue length: 80, concurrency: 16 ... ...
cpu-checker.sh
#!/usr/bin/env bash
tmpfile=$1
host=100.64.88.102
thread=$3
while [ -f $tmpfile ];
do
/usr/bin/ssh -i /root/.ssh/id_rsa_mysqlmha root@$host "sleep 1; top -p \$(pidof mysqld) -b -n1"|grep '%CPU' -A1|awk 'NR>1{print $9}' >> $tmpfile
done
innodb-ops-parser.py
import os, sys, subprocess
import csv
from subprocess import check_call
#import array as arr
def create_csv(ops_v, hostname_ip):
#print ops_v
path = os.getcwd() + "/"
csvfile = path + hostname_ip + '-inno-ops.csv'
with open(csvfile, "w") as output:
for row in ops_v:
writer = csv.writer(output, lineterminator='\n')
writer.writerow(row)
output.close()
def generate_sysbench_csv(hostname_ip ):
path = os.getcwd() + "/"
csv_filename = path + hostname_ip + "-tps-sysbench.csv"
sysbench_filename = path + hostname_ip + "-sysbench.log"
cmd = ''.join(['cat ', sysbench_filename, '| egrep " cat|threads:|transactions:" | tr -d "\\n" | sed "s/Number of threads: /\\n/g"',
'| sed "s/\[/\\n/g" | sed "s/[A-Za-z\/]\{1,\}://g"| sed "s/ \.//g"',
'| awk {\'if(NR > 1){print $1 $3} else {print "threads,tps"}\'}|',
'sed "s/(/,/g" > ', csv_filename])
#check_call(cmd, shell=True, executable='/bin/bash')
os.system(cmd)
csv_filename = path + hostname_ip + "-transactions-sysbench.csv"
cmd = ''.join(['cat ', sysbench_filename, '| egrep " cat|threads:|transactions:" | tr -d "\\n" | sed "s/Number of threads: /\\n/g"',
'| sed "s/\[/\\n/g" | sed "s/[A-Za-z\/]\{1,\}://g"| sed "s/ \.//g"',
'| awk {\'if(NR > 1){print $1 "," $2} else {print "threads,transactions"}\'} |',
'sed "s/(/,/g" > ', csv_filename])
os.system(cmd)
csv_filename = path + hostname_ip + "-read-write-sysbench.csv"
cmd = ''.join(['cat ', sysbench_filename, '| egrep " cat|threads:|read:|write:|other:|total:" | tr -d "\\n" | sed "s/Number of threads: /\\n/g"',
'| sed "s/\[/\\n/g" | sed "s/[A-Za-z\/]\{1,\}://g"| sed "s/ \.//g"',
'| awk {\'if(NR > 1){print $1",",$2,","$3,",",$4,",",$5} else {print "threads,read,write,other,total"}\'}|',
'sed "s/(/,/g" > ', csv_filename])
os.system(cmd)
def innodb_ops_list_to_csv(a, h):
#print a
inno_ops_tbl = []
row = []
l_deleted = []
l_inserted = []
l_read = []
l_updated = []
for csv in sorted(a.iterkeys()):
thd = a[csv]
row.append(csv)
#print csv
if "Innodb_rows_deleted" in thd:
tmp = thd["Innodb_rows_deleted"]
l_deleted.append({csv: tmp[1] - tmp[0]})
if "Innodb_rows_inserted" in thd:
tmp = thd["Innodb_rows_inserted"]
l_inserted.append({csv: tmp[1] - tmp[0]})
if "Innodb_rows_read" in thd:
tmp = thd["Innodb_rows_read"]
l_read.append({csv: tmp[1] - tmp[0]})
if "Innodb_rows_updated" in thd:
tmp = thd["Innodb_rows_updated"]
l_updated.append({csv:tmp[1] - tmp[0]})
i = 0
arr = []
inno_ops_tbl.append(["tps", "Innodb_rows_deleted", "Innodb_rows_inserted", "Innodb_rows_read", "Innodb_rows_updated"])
for r in row:
#arr.append(r)
##print inno_ops_tbl, r, l_deleted, l_inserted
#print i, r, l_deleted[i][r]
arr.append(r)
arr.append(l_deleted[i][r])
arr.append(l_inserted[i][r])
arr.append(l_read[i][r])
inno_ops_tbl.append(arr)
arr = []
i += 1
create_csv(inno_ops_tbl, h)
def main(host_ip):
status_log=host_ip + "-global-status.log"
a = []
b = {16:[],32:[],64:[],128:[],256:[],512:[],1024:[],2048:[]}
ndex = 0
cur_thd = 16
inc_deleted = 1
#innodb_ops=os.system("cat %(status_log)s |grep 'Innodb_rows_[deleted|inserted|read|updated]' -i | tr '\t' ','" % locals(), "r")
p = subprocess.Popen("cat %(status_log)s |grep 'Innodb_rows_[deleted|inserted|read|updated]' -i | tr '\t' ','" % locals(), shell=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
for line in p.stdout.readlines():
a = line.strip().split(',')
a[1] = int(a[1])
if a[0] == "Innodb_rows_deleted":
if "Innodb_rows_deleted" in b[cur_thd]:
b[cur_thd]["Innodb_rows_deleted"].append(a[1])
else:
if len(b[cur_thd]) > 0 and "Innodb_rows_deleted" in b[cur_thd]:
b[cur_thd]["Innodb_rows_deleted"].append(a[1])
else:
#print "only once", cur_thd, b[cur_thd]
b[cur_thd] = {a[0]: [a[1]]}
elif a[0] != "Innodb_rows_deleted":
if a[0] not in b[cur_thd]:
b[cur_thd].update({a[0]: [a[1]]})
else:
b[cur_thd][a[0]].extend([a[1]])
if inc_deleted == 8:
cur_thd *= 2
inc_deleted = 1
else:
inc_deleted += 1
#retval = p.wait()
p.stdout.close()
return b
if __name__ == '__main__':
if (len(sys.argv) < 2):
raise ValueError("Hostname IP is needed")
host_ip = sys.argv[1];
innodb_ops = main(host_ip)
#print "innodb_ops:", innodb_ops
innodb_ops_list_to_csv(innodb_ops, host_ip)
generate_sysbench_csv(host_ip)