pgbench でカスタムクエリを実行する

準備

• pgbench(PostgreSQL)をインストールする。

$ sudo yum -y install postgresql
$ sudo yum -y install postgresql-contrib

• pgbench でデータを登録する。

$ pgbench -i -s 10000 -U awsuser -h aurora-postgres107.cluster-************.ap-northeast-1.rds.amazonaws.com -d postgres

• 登録したデータを確認する。

$ psql "host=aurora-postgres107.cluster-************.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=postgres port=5432”

aurora-postgres107 awsuser 23:54 => select count(1) from pgbench_accounts;
   count
------------
 1000000000
(1 row)

Time: 90621.247 ms

aurora-postgres107 awsuser 23:57 => \d pgbench_accounts
   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

• ベンチマーク用SQLスクリプトを作成する。

$ vi sort1.sql
select * from pgbench_accounts order by filler desc;

負荷をかける

$ export PGPASSWORD=********
$ nohup pgbench -r -c 1000 -j 100 -n -t 100 -f sort1.sql -U awsuser -h aurora-postgres107.cluster-************.ap-northeast-1.rds.amazonaws.com -d postgres -p 5432
$ nohup pgbench -r -c 1000 -j 100 -n -t 100 -f sort1.sql -U awsuser -h aurora-postgres107.cluster-************.ap-northeast-1.rds.amazonaws.com -d postgres -p 5432

• ログをダウンロードする

$ aws rds describe-db-log-files --db-instance-identifier aurora-postgres107-instance-1|jq -r '.DescribeDBLogFiles[].LogFileName'|while read LINE
do
BASE_NAME=$(basename ${LINE})
aws rds download-db-log-file-portion --db-instance-identifier aurora-postgres107-instance-1 --log-file-name ${LINE} > ${BASE_NAME}
done
$ aws rds describe-db-log-files --db-instance-identifier aurora-postgres107-instance-1|jq -r '.DescribeDBLogFiles[].LogFileName'|while read LINE
do
BASE_NAME=$(basename ${LINE})
aws rds download-db-log-file-portion --db-instance-identifier aurora-postgres107-instance-1 --log-file-name ${LINE} > ${BASE_NAME}
done

• CloudWatchメトリクスを取得する

$ aws cloudwatch get-metric-statistics \
    --namespace "AWS/RDS" \
    --dimensions Name="DBInstanceIdentifier,Value=aurora-postgres106-instance-1" \
    --metric-name "FreeStorageSpace" \
    --statistics "Average" \
    --period 300 \
    --start-time "2019-10-05T00:00:00Z" \
    --end-time "2019-10-08T23:00:00Z" \
    --region ap-northeast-1

環境

• 変更したDBクラスターパラメータグループ

$ aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name aurora-postgres10-cluster --source user|jq -r '.Parameters[]|@text "\(.ParameterName):\(.ParameterValue):\(.Description)"'

autovacuum:1:Starts the autovacuum subprocess.
log_autovacuum_min_duration:0:(ms) Sets the minimum execution time above which autovacuum actions will be logged.
log_destination:csvlog:Sets the destination for server log output.
log_statement:all:Sets the type of statements logged.
log_statement_stats:1:Writes cumulative performance statistics to the server log.
rds.force_autovacuum_logging_level:debug5:See log messages related to autovacuum operations.

• 変更したDBパラメータグループ

$ aws rds describe-db-parameters --db-parameter-group-name aurora-postgres10 --source user|jq -r '.Parameters[]|@text "\(.ParameterName):\(.ParameterValue):\(.Description)"'

log_connections:1:Logs each successful connection.
log_destination:csvlog:Sets the destination for server log output.
log_disconnections:1:Logs end of a session, including duration.
log_duration:1:Logs the duration of each completed SQL statement.
log_error_verbosity:verbose:Sets the verbosity of logged messages.
log_lock_waits:1:Logs long lock waits.
log_min_duration_statement:0:(ms) Sets the minimum execution time above which statements will be logged.
log_statement:all:Sets the type of statements logged.
log_statement_stats:1:Writes cumulative performance statistics to the server log.
log_temp_files:0:(kB) Log the use of temporary files larger than this number of kilobytes.