Cloud9環境の準備

Amazon Linux 2023へのsysbenchインストールがうまくいかなかっため、Amazon Linux 2を使用

(変数設定)アベイラビリティゾーンの指定

コマンド (CloudShellで実施)

AVAILABILITY_ZONE=ap-northeast-1d \
&& echo ${AVAILABILITY_ZONE}

出力

[cloudshell-user@ip-10-130-56-156 ~]$ AVAILABILITY_ZONE=ap-northeast-1d \
> && echo ${AVAILABILITY_ZONE}
ap-northeast-1d

(変数設定)サブネットID

コマンド (CloudShellで実施)

SUBNET_ID=$( \
  aws ec2 describe-subnets \
    --filters "Name=availabilityZone,Values=${AVAILABILITY_ZONE}" \
    --query "Subnets[].SubnetId" \
    --output text
)\
&& echo ${SUBNET_ID}

出力

[cloudshell-user@ip-10-130-56-156 ~]$ SUBNET_ID=$( \
>   aws ec2 describe-subnets \
>     --filters "Name=availabilityZone,Values=${AVAILABILITY_ZONE}" \
>     --query "Subnets[].SubnetId" \
>     --output text
> )\
> && echo ${SUBNET_ID}
subnet-06dac05f30d70fadf

Cloud9環境の作成

コマンド (CloudShellで実施)

CLOUD9_ID=$( \
  aws cloud9 create-environment-ec2 \
    --name "Amazon Linux 2" \
    --instance-type t2.micro \
    --image-id resolve:ssm:/aws/service/cloud9/amis/amazonlinux-2-x86_64 \
    --region ap-northeast-1 \
    --connection-type CONNECT_SSM \
    --automatic-stop-time-minutes 30 \
    --subnet-id ${SUBNET_ID} \
    --output text
) \
&& echo ${CLOUD9_ID}

出力

[cloudshell-user@ip-10-130-49-235 ~]$ CLOUD9_ID=$( \
>   aws cloud9 create-environment-ec2 \
>     --name "Amazon Linux 2" \
>     --instance-type t2.micro \
>     --image-id resolve:ssm:/aws/service/cloud9/amis/amazonlinux-2-x86_64 \
>     --region ap-northeast-1 \
>     --connection-type CONNECT_SSM \
>     --automatic-stop-time-minutes 30 \
>     --subnet-id ${SUBNET_ID} \
>     --output text
> ) \
> && echo ${CLOUD9_ID}
97c5cad080634193b708d9e57fe7512b

(変数設定)EC2セキュリティグループ ID取得

コマンド (CloudShellで実施)

EC2_SG_ID=$( \
  aws ec2 describe-instances \
    --query 'Reservations[*].Instances[*].SecurityGroups[*].GroupId' \
    --filters "Name=tag:Name,Values=*${CLOUD9_ID}" \
    --output text
) \
&& echo ${EC2_SG_ID}

出力

[cloudshell-user@ip-10-130-49-235 ~]$ EC2_SG_ID=$( \
>   aws ec2 describe-instances \
>     --query 'Reservations[*].Instances[*].SecurityGroups[*].GroupId' \
>     --filters "Name=tag:Name,Values=*${CLOUD9_ID}" \
>     --output text
> ) \
> && echo ${EC2_SG_ID}
sg-01171dd479a769507

RDSインスタンス作成

(変数設定)RDSインスタンス識別子

コマンド (CloudShellで実施)

DB_IDENTITY="test-mysql-instance" \
&& echo ${DB_IDENTITY}

出力

[cloudshell-user@ip-10-130-49-235 ~]$ DB_IDENTITY="test-mysql-instance" \
> && echo ${DB_IDENTITY}
test-mysql-instance

RDSインスタンス作成

コマンド (CloudShellで実施)

aws rds create-db-instance \
    --db-instance-identifier ${DB_IDENTITY} \
    --db-instance-class db.t3.micro \
    --engine mysql \
    --master-username admin \
    --master-user-password secret99 \
    --no-publicly-accessible \
    --allocated-storage 20 \
    --backup-retention-period 0 \
    --no-auto-minor-version-upgrade \
    --availability-zone ${AVAILABILITY_ZONE}

出力

[cloudshell-user@ip-10-130-49-235 ~]$ aws rds create-db-instance \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --db-instance-class db.t3.micro \
>     --engine mysql \
>     --master-username admin \
>     --master-user-password secret99 \
>     --no-publicly-accessible \
>     --allocated-storage 20 \
>     --backup-retention-period 0 \
>     --no-auto-minor-version-upgrade \
>     --availability-zone ${AVAILABILITY_ZONE}
{
    "DBInstance": {
        "DBInstanceIdentifier": "test-mysql-instance",
        "DBInstanceClass": "db.t3.micro",
        "Engine": "mysql",
        "DBInstanceStatus": "creating",
        "MasterUsername": "admin",
        "AllocatedStorage": 20,
        "PreferredBackupWindow": "15:14-15:44",
        "BackupRetentionPeriod": 0,
        "DBSecurityGroups": [],
        "VpcSecurityGroups": [
            {
                "VpcSecurityGroupId": "sg-0109570e93f4a220f",
                "Status": "active"
            }
        ],
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "default.mysql8.0",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "AvailabilityZone": "ap-northeast-1d",
        "DBSubnetGroup": {
            "DBSubnetGroupName": "default",
            "DBSubnetGroupDescription": "default",
            "VpcId": "vpc-090c14ab4d18c8e0b",
            "SubnetGroupStatus": "Complete",
            "Subnets": [
                {
                    "SubnetIdentifier": "subnet-06dac05f30d70fadf",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1d"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-0e58505daeffcca69",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1a"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-024f020ed79b42984",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1c"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                }
            ]
        },
        "PreferredMaintenanceWindow": "wed:17:20-wed:17:50",
        "PendingModifiedValues": {
            "MasterUserPassword": "****"
        },
        "MultiAZ": false,
        "EngineVersion": "8.0.35",
        "AutoMinorVersionUpgrade": false,
        "ReadReplicaDBInstanceIdentifiers": [],
        "LicenseModel": "general-public-license",
        "OptionGroupMemberships": [
            {
                "OptionGroupName": "default:mysql-8-0",
                "Status": "in-sync"
            }
        ],
        "PubliclyAccessible": false,
        "StorageType": "gp2",
        "DbInstancePort": 0,
        "StorageEncrypted": false,
        "DbiResourceId": "db-FRWCBZE7FYZLLJ23EZZWUVYEEU",
        "CACertificateIdentifier": "rds-ca-rsa2048-g1",
        "DomainMemberships": [],
        "CopyTagsToSnapshot": false,
        "MonitoringInterval": 0,
        "DBInstanceArn": "arn:aws:rds:ap-northeast-1:999999999999:db:test-mysql-instance",
        "IAMDatabaseAuthenticationEnabled": false,
        "PerformanceInsightsEnabled": false,
        "DeletionProtection": false,
        "AssociatedRoles": [],
        "TagList": [],
        "CustomerOwnedIpEnabled": false,
        "BackupTarget": "region",
        "NetworkType": "IPV4",
        "StorageThroughput": 0,
        "CertificateDetails": {
            "CAIdentifier": "rds-ca-rsa2048-g1"
        },
        "DedicatedLogVolume": false
    }
}

(変数設定)RDSセキュリティグループ ID取得

コマンド (CloudShellで実施)

RDS_SG_ID=$( \
  aws rds describe-db-instances \
    --db-instance-identifier ${DB_IDENTITY} \
    --query 'DBInstances[].VpcSecurityGroups[].VpcSecurityGroupId' \
    --output text
) \
&& echo ${RDS_SG_ID}

出力

[cloudshell-user@ip-10-130-49-235 ~]$ RDS_SG_ID=$( \
>   aws rds describe-db-instances \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --query 'DBInstances[].VpcSecurityGroups[].VpcSecurityGroupId' \
>     --output text
> ) \
> && echo ${RDS_SG_ID}
sg-0109570e93f4a220f

RDSセキュリティグループにCloud9 EC2からの許可ルール追加

コマンド (CloudShellで実施)

SG_RULE_ID=$( 
  aws ec2 authorize-security-group-ingress \
    --group-id ${RDS_SG_ID} \
    --protocol tcp \
    --port 3306 \
    --source-group ${EC2_SG_ID} \
    --query 'SecurityGroupRules[].SecurityGroupRuleId' \
    --output text
) \
&& echo ${SG_RULE_ID}

出力

[cloudshell-user@ip-10-130-49-235 ~]$ SG_RULE_ID=$( 
>   aws ec2 authorize-security-group-ingress \
>     --group-id ${RDS_SG_ID} \
>     --protocol tcp \
>     --port 3306 \
>     --source-group ${EC2_SG_ID} \
>     --query 'SecurityGroupRules[].SecurityGroupRuleId' \
>     --output text
> ) \
> && echo ${SG_RULE_ID}
sgr-0b4670dcb53253134

Cloud9環境からRDSへの接続

(変数設定)RDSインスタンス識別子

コマンド (Cloud9で実施)

DB_IDENTITY="test-mysql-instance" \
&& echo ${DB_IDENTITY}

出力

admin:~/environment $ DB_IDENTITY="test-mysql-instance" \
> && echo ${DB_IDENTITY}
test-mysql-instance

(変数設定)RDSエンドポイントアドレス取得

コマンド (Cloud9で実施)

RDS_ENDPOINT_ADR=$( \
  aws rds describe-db-instances \
    --db-instance-identifier ${DB_IDENTITY} \
    --query 'DBInstances[].Endpoint[].Address' \
    --output text
) \
&& echo ${RDS_ENDPOINT_ADR}

出力

admin:~/environment $ RDS_ENDPOINT_ADR=$( \
>   aws rds describe-db-instances \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --query 'DBInstances[].Endpoint[].Address' \
>     --output text
> ) \
> && echo ${RDS_ENDPOINT_ADR}
test-mysql-instance.clacqicsiqrt.ap-northeast-1.rds.amazonaws.com

SQL接続

コマンド (Cloud9で実施)

mysql -h ${RDS_ENDPOINT_ADR} -P 3306 -u admin -p

出力

admin:~/environment $ mysql -h ${RDS_ENDPOINT_ADR} -P 3306 -u admin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.35 Source distribution

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)]>

データーベース一覧確認

コマンド (Cloud9で実施)

SHOW DATABASES;

出力

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.002 sec)

テスト用データーベース一作成

コマンド (Cloud9で実施)

CREATE DATABASE test;

出力

MySQL [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.002 sec)

データーベース一覧確認(作成後)

コマンド (Cloud9で実施)

SHOW DATABASES;

出力

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.001 sec)

SQL切断

コマンド (Cloud9で実施)

exit

出力

MySQL [(none)]> exit
Bye

sysbench

READMEの通り実施で問題なくインストール可能

インストール準備

コマンド (Cloud9で実施)

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

出力

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.
Attempting to install pygpgme if required (only for older OSes) to verify GPG signatures...
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
akopytov_sysbench-source/signature                                                                              |  819 B  00:00:00     
Retrieving key from https://packagecloud.io/akopytov/sysbench/gpgkey
akopytov_sysbench-source/signature                                                                              |  951 B  00:00:00 !!! 
amzn2-core                                                                                                      | 3.6 kB  00:00:00     
akopytov_sysbench-source/primary                                                                                |  175 B  00:00:00     
236 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
236 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.

sysbenchインストール

コマンド (Cloud9で実施)

sudo yum -y install sysbench

出力

admin:~/environment $ sudo yum -y install sysbench
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
236 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package sysbench.x86_64 0:1.0.17-2.el7 will be installed
--> Processing Dependency: libck.so.0()(64bit) for package: sysbench-1.0.17-2.el7.x86_64
--> Processing Dependency: libluajit-5.1.so.2()(64bit) for package: sysbench-1.0.17-2.el7.x86_64
--> Running transaction check
---> Package ck.x86_64 0:0.5.2-2.el7 will be installed
---> Package luajit.x86_64 0:2.0.5-1.20220913.46e62cd.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================
 Package                     Arch                      Version                                           Repository               Size
=======================================================================================================================================
Installing:
 sysbench                    x86_64                    1.0.17-2.el7                                      epel                    152 k
Installing for dependencies:
 ck                          x86_64                    0.5.2-2.el7                                       epel                     26 k
 luajit                      x86_64                    2.0.5-1.20220913.46e62cd.el7                      epel                    344 k

Transaction Summary
=======================================================================================================================================
Install  1 Package (+2 Dependent packages)

Total download size: 522 k
Installed size: 1.7 M
Downloading packages:
(1/3): ck-0.5.2-2.el7.x86_64.rpm                                                                                |  26 kB  00:00:00     
(2/3): luajit-2.0.5-1.20220913.46e62cd.el7.x86_64.rpm                                                           | 344 kB  00:00:01     
(3/3): sysbench-1.0.17-2.el7.x86_64.rpm                                                                         | 152 kB  00:00:00     
---------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                  346 kB/s | 522 kB  00:00:01     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : luajit-2.0.5-1.20220913.46e62cd.el7.x86_64                                                                          1/3 
  Installing : ck-0.5.2-2.el7.x86_64                                                                                               2/3 
  Installing : sysbench-1.0.17-2.el7.x86_64                                                                                        3/3 
  Verifying  : ck-0.5.2-2.el7.x86_64                                                                                               1/3 
  Verifying  : luajit-2.0.5-1.20220913.46e62cd.el7.x86_64                                                                          2/3 
  Verifying  : sysbench-1.0.17-2.el7.x86_64                                                                                        3/3 

Installed:
  sysbench.x86_64 0:1.0.17-2.el7                                                                                                       

Dependency Installed:
  ck.x86_64 0:0.5.2-2.el7                                 luajit.x86_64 0:2.0.5-1.20220913.46e62cd.el7                                

Complete!

負荷テスト準備

コマンド (Cloud9で実施)

PASSWORD=secret99
sysbench --db-driver=mysql \
  --mysql-host=${RDS_ENDPOINT_ADR} \
  --mysql-user=admin \
  --mysql-password=${PASSWORD} \
  --mysql-db=test \
  oltp_read_write \
  prepare

出力

admin:~/environment $ PASSWORD=secret99
admin:~/environment $ sysbench --db-driver=mysql \
>   --mysql-host=${RDS_ENDPOINT_ADR} \
>   --mysql-user=admin \
>   --mysql-password=${PASSWORD} \
>   --mysql-db=test \
>   oltp_read_write \
>   prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

負荷テスト

コマンド (Cloud9で実施)

sysbench --db-driver=mysql \
  --mysql-host=${RDS_ENDPOINT_ADR} \
  --mysql-user=admin \
  --mysql-password=${PASSWORD} \
  --mysql-db=test \
  oltp_read_write \
  run

出力

admin:~/environment $ sysbench --db-driver=mysql \
>   --mysql-host=${RDS_ENDPOINT_ADR} \
>   --mysql-user=admin \
>   --mysql-password=${PASSWORD} \
>   --mysql-db=test \
>   oltp_read_write \
>   run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

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:                            10808
        write:                           3088
        other:                           1544
        total:                           15440
    transactions:                        772    (77.12 per sec.)
    queries:                             15440  (1542.37 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0084s
    total number of events:              772

Latency (ms):
         min:                                    9.85
         avg:                                   12.96
         max:                                   61.67
         95th percentile:                       18.28
         sum:                                10003.74

Threads fairness:
    events (avg/stddev):           772.0000/0.00
    execution time (avg/stddev):   10.0037/0.00

後片付け

RDSインスタンスの削除

コマンド (CloudShellで実施)

aws rds delete-db-instance \
    --db-instance-identifier ${DB_IDENTITY} \
    --skip-final-snapshot \
    --delete-automated-backups

出力

[cloudshell-user@ip-10-132-94-234 ~]$ aws rds delete-db-instance \
>     --db-instance-identifier ${DB_IDENTITY} \
>     --skip-final-snapshot \
>     --delete-automated-backups
{
    "DBInstance": {
        "DBInstanceIdentifier": "test-mysql-instance",
        "DBInstanceClass": "db.t3.micro",
        "Engine": "mysql",
        "DBInstanceStatus": "deleting",
        "MasterUsername": "admin",
        "Endpoint": {
            "Address": "test-mysql-instance.clacqicsiqrt.ap-northeast-1.rds.amazonaws.com",
            "Port": 3306,
            "HostedZoneId": "Z24O6O9L7SGTNB"
        },
        "AllocatedStorage": 20,
        "InstanceCreateTime": "2024-05-05T09:51:22.502000+00:00",
        "PreferredBackupWindow": "15:14-15:44",
        "BackupRetentionPeriod": 0,
        "DBSecurityGroups": [],
        "VpcSecurityGroups": [
            {
                "VpcSecurityGroupId": "sg-0109570e93f4a220f",
                "Status": "active"
            }
        ],
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "default.mysql8.0",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "AvailabilityZone": "ap-northeast-1d",
        "DBSubnetGroup": {
            "DBSubnetGroupName": "default",
            "DBSubnetGroupDescription": "default",
            "VpcId": "vpc-090c14ab4d18c8e0b",
            "SubnetGroupStatus": "Complete",
            "Subnets": [
                {
                    "SubnetIdentifier": "subnet-06dac05f30d70fadf",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1d"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-0e58505daeffcca69",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1a"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-024f020ed79b42984",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1c"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                }
            ]
        },
        "PreferredMaintenanceWindow": "wed:17:20-wed:17:50",
        "PendingModifiedValues": {},
        "MultiAZ": false,
        "EngineVersion": "8.0.35",
        "AutoMinorVersionUpgrade": false,
        "ReadReplicaDBInstanceIdentifiers": [],
        "LicenseModel": "general-public-license",
        "OptionGroupMemberships": [
            {
                "OptionGroupName": "default:mysql-8-0",
                "Status": "in-sync"
            }
        ],
        "PubliclyAccessible": false,
        "StorageType": "gp2",
        "DbInstancePort": 0,
        "StorageEncrypted": false,
        "DbiResourceId": "db-FRWCBZE7FYZLLJ23EZZWUVYEEU",
        "CACertificateIdentifier": "",
        "DomainMemberships": [],
        "CopyTagsToSnapshot": false,
        "MonitoringInterval": 0,
        "DBInstanceArn": "arn:aws:rds:ap-northeast-1:999999999999:db:test-mysql-instance",
        "IAMDatabaseAuthenticationEnabled": false,
        "PerformanceInsightsEnabled": false,
        "DeletionProtection": false,
        "AssociatedRoles": [],
        "TagList": [],
        "CustomerOwnedIpEnabled": false,
        "BackupTarget": "region",
        "NetworkType": "IPV4",
        "StorageThroughput": 0,
        "DedicatedLogVolume": false
    }
}

セキュリティグループからCloud9 EC2からの許可ルール削除

コマンド (CloudShellで実施)

aws ec2 revoke-security-group-ingress \
  --group-id ${RDS_SG_ID} \
  --security-group-rule-ids ${SG_RULE_ID}

出力

[cloudshell-user@ip-10-132-94-234 ~]$ aws ec2 revoke-security-group-ingress \
>   --group-id ${RDS_SG_ID} \
>   --security-group-rule-ids ${SG_RULE_ID}
{
    "Return": true
}

Cloud9環境の削除

コマンド (CloudShellで実施)

aws cloud9 delete-environment --environment-id ${CLOUD9_ID}

出力

[cloudshell-user@ip-10-132-94-234 ~]$ aws cloud9 delete-environment --environment-id ${CLOUD9_ID}