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}