用户工具

站点工具

您的足迹: • db_mysql01_performance_vs

侧边栏123123

最近发布

随机推荐

docs:evaluating:db_mysql01_performance_vs

目录

Evaluating Tools

https://github.com/akopytov/sysbench

使用

说明：

  • 启动一次全新的测试时一定要删除（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

参考以下，并修改相关完成测试，修改细节详见本文。

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)

docs/evaluating/db_mysql01_performance_vs.txt · 最后更改: 2020/09/25 21:49 (modify by Amos)

页面工具

您的足迹: • db_mysql01_performance_vs