Cloud9からAurora MySQLにsysbenchで負荷をかけるメモ。
DevOps Guru for RDSで何らかのインサイトを出したいがために実施。
参考リンク
手順
Cloud9からAuroraに接続できるようにする。セキュリティグループの設定を忘れずに。
Admin:~/environment $ ENDPOINT=<エンドポイント>
Admin:~/environment $ mysql -h ${ENDPOINT} -P 3306 -u admin -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.12 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
mydbデータベースを作っておく。
MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) MySQL [(none)]> create database mydb; Query OK, 1 row affected (0.01 sec) MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) MySQL [(none)]>
sysbenchをインストールする。
Admin:~/environment $ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash Detected operating system as amzn/2. Checking for curl... Detected curl... Downloading repository file: https://packagecloud.io/install/repositories/akopytov/sysbench/config_file.repo?os=amzn&dist=2&source=script done. Installing pygpgme to verify GPG signatures... Loaded plugins: extras_suggestions, langpacks, priorities, update-motd 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 !!! amzn2-core | 3.7 kB 00:00:00 akopytov_sysbench-source/primary | 2.0 kB 00:00:01 akopytov_sysbench-source 15/15 234 packages excluded due to repository priority protections Package pygpgme-0.3-9.amzn2.0.3.x86_64 already installed and latest version Nothing to do Installing yum-utils... Loaded plugins: extras_suggestions, langpacks, priorities, update-motd 234 packages excluded due to repository priority protections Package yum-utils-1.1.31-46.amzn2.0.1.noarch already installed and latest version Nothing to do 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. Admin:~/environment $ sudo yum -y install sysbench Loaded plugins: extras_suggestions, langpacks, priorities, update-motd 234 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package sysbench.x86_64 0:1.0.20-1.el7 will be installed --> Processing Dependency: libpq.so.5()(64bit) for package: sysbench-1.0.20-1.el7.x86_64 --> Running transaction check ---> Package postgresql-libs.x86_64 0:9.2.24-6.amzn2 will be installed --> Finished Dependency Resolution Dependencies Resolved =============================================================================================================================================================================================================================== Package Arch Version Repository Size =============================================================================================================================================================================================================================== Installing: sysbench x86_64 1.0.20-1.el7 akopytov_sysbench 430 k Installing for dependencies: postgresql-libs x86_64 9.2.24-6.amzn2 amzn2-core 232 k Transaction Summary =============================================================================================================================================================================================================================== Install 1 Package (+1 Dependent package) Total download size: 662 k Installed size: 1.8 M Downloading packages: (1/2): postgresql-libs-9.2.24-6.amzn2.x86_64.rpm | 232 kB 00:00:00 (2/2): sysbench-1.0.20-1.el7.x86_64.rpm | 430 kB 00:00:00 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 983 kB/s | 662 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : postgresql-libs-9.2.24-6.amzn2.x86_64 1/2 Installing : sysbench-1.0.20-1.el7.x86_64 2/2 Verifying : sysbench-1.0.20-1.el7.x86_64 1/2 Verifying : postgresql-libs-9.2.24-6.amzn2.x86_64 2/2 Installed: sysbench.x86_64 0:1.0.20-1.el7 Dependency Installed: postgresql-libs.x86_64 0:9.2.24-6.amzn2 Complete!
テーブルを作成する。
PASSWORD=<パスワード>
sysbench --db-driver=mysql \
--mysql-host=$ENDPOINT \
--mysql-user=admin \
--mysql-password=$PASSWORD \
--mysql-db=mydb \
oltp_read_write \
prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 10000 records into 'sbtest1' Creating a secondary index on 'sbtest1'...
負荷をかける。
sysbench --db-driver=mysql \
--mysql-host=$ENDPOINT \
--mysql-user=admin \
--mysql-password=$PASSWORD \
--mysql-db=mydb \
oltp_read_write \
run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 3486
write: 996
other: 498
total: 4980
transactions: 249 (24.80 per sec.)
queries: 4980 (496.10 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0364s
total number of events: 249
Latency (ms):
min: 37.91
avg: 40.30
max: 48.68
95th percentile: 43.39
sum: 10033.87
Threads fairness:
events (avg/stddev): 249.0000/0.00
execution time (avg/stddev): 10.0339/0.00
これだと同時実行数が1なのでDB Load（平均アクティブセッション数）をもとにインサイトを出すDevOps Guru for RDSは何も出さないと思われる。
実行時間を5分、同時実行数を20にしてみる。
sysbench --db-driver=mysql \
--mysql-host=$ENDPOINT \
--mysql-user=admin \
--mysql-password=$PASSWORD \
--mysql-db=mydb \
--threads=20 \
--time=300 \
oltp_read_write \
run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 1682296
write: 467487
other: 235659
total: 2385442
transactions: 115495 (384.92 per sec.)
queries: 2385442 (7950.15 per sec.)
ignored errors: 4669 (15.56 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0482s
total number of events: 115495
Latency (ms):
min: 38.30
avg: 51.95
max: 204.73
95th percentile: 75.82
sum: 6000173.52
Threads fairness:
events (avg/stddev): 5774.7500/15.97
execution time (avg/stddev): 300.0087/0.02
パフォーマンスインサイトで負荷がかかったことが確認できた。
さらにスレッドを100、300と増やしてみたところ、DevOps Guruのインサイトも出てきた。しかしこれはDB Loadではないので、DevOps Guru for RDSのインサイトではなく、普通のDevOps Guruのインサイトのように思える。
