RDSの負荷を意図的に埋み出してみよう

2024年2月13日 00:41

まーたインフラっすか。。前に作ったEC2とRDSが使えそうだ。

admin@ip-172-31-10-144:~$ mysql -uadmin -ppassword -huploader-demo.cmxstlofflyf.ap-northeast-1.rds.amazonaws.com uploader_demo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 51337
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 [uploader_demo]>

まこんな感じで使えるとしようこれはdb.t4g.micro という非常に小さい性能のDBである

table作るのとデーターの挿入

CREATE TABLE test_table1 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(255)
);

CREATE TABLE test_table2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(255)
);

CREATE TABLE test_table3 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(255)
);

まあこんな構造にしとくか。リレーションをなんとかIDみたいなPKで組むと何だかんだ早くなっちゃうのでdataとかいう単なるvarcharを探すようにする

今回はperlスクリプトでも使ってみよう(何でもやるぞ〜、って書いてるのはchatgptだけど)。

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# データベース設定
my $dsn = 'DBI:mysql:database=uploader_demo;host=uploader-demo.cmxstlofflyf.ap-northeast-1.rds.amazonaws.com';
my $user = 'admin';
my $password = 'password';

# データベース接続
my $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit => 1 });

# test_table2 と test_table3 へのデータ挿入
foreach my $table (qw(test_table2 test_table3)) {
    my $sth = $dbh->prepare("INSERT INTO $table (data) VALUES (?)");
    for (my $i = 0; $i < 100; $i++) {
        my $data = "test data $i";
        $sth->execute($data);
    }
    $sth->finish;
}

$dbh->disconnect;

print "master Data insertion completed.\n";


# test_table1 へのデータ挿入
my $sth = $dbh->prepare('INSERT INTO test_table1 (data) VALUES (?)');
for (my $i = 0; $i < 1000; $i++) {
    my $data = "test data $i";
    $sth->execute($data);
}

$sth->finish;
$dbh->disconnect;

でまあいろいろ足りない。debianの場合はCPANから入れないからね！

$ perl dummydata.pl
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (you may need to install the DBD::mysql module) (@INC contains: /etc/perl /usr/local/lib/aarch64-linux-gnu/perl/5.36.0 /usr/local/share/perl/5.36.0 /usr/lib/aarch64-linux-gnu/perl5/5.36 /usr/share/perl5 /usr/lib/aarch64-linux-gnu/perl-base /usr/lib/aarch64-linux-gnu/perl/5.36 /usr/share/perl/5.36 /usr/local/lib/site_perl) at (eval 6) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, MariaDB, Mem, Proxy, Sponge.
 at dummydata.pl line 12.

$ sudo apt install libclass-dbi-mysql-perl
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libb-hooks-endofscope-perl libb-hooks-op-check-perl libclass-accessor-perl
  libclass-data-inheritable-perl libclass-dbi-abstractsearch-perl
  libclass-dbi-perl libclass-method-modifiers-perl libclass-trigger-perl
  libclass-xsaccessor-perl libclone-choose-perl libclone-perl
  libdbd-mysql-perl libdbix-contextualfetch-perl libdevel-callchecker-perl
  libdynaloader-functions-perl libhash-merge-perl libima-dbi-perl
  libimport-into-perl libio-stringy-perl liblingua-en-inflect-perl
  libmodule-implementation-perl libmodule-runtime-perl libmoo-perl
  libnamespace-clean-perl libpackage-stash-perl libpackage-stash-xs-perl
  libparams-classify-perl librole-tiny-perl libsql-abstract-limit-perl
  libsql-abstract-perl libsub-exporter-progressive-perl libsub-identify-perl
  libsub-name-perl libsub-quote-perl libtest-deep-perl
  libtime-piece-mysql-perl libtry-tiny-perl libuniversal-moniker-perl
  libvariable-magic-perl libxstring-perl

libclass-dbi-mysql-perl をinstallしたら再度実行しよう。

$ perl dummydata.pl

放置しとく。

待ってる間cloudwatchのdashboardでもこさえとくか…

クエリーを打ってみる

MySQL [uploader_demo]> select count(id) from test_table1;
+-----------+
| count(id) |
+-----------+
|      1000 |
+-----------+
1 row in set (0.003 sec)

MySQL [uploader_demo]> select count(id) from test_table2;
+-----------+
| count(id) |
+-----------+
|       100 |
+-----------+
1 row in set (0.003 sec)

MySQL [uploader_demo]> select count(id) from test_table3;
+-----------+
| count(id) |
+-----------+
|       100 |
+-----------+
1 row in set (0.003 sec)

で

SELECT tt1.*, tt2.data AS data2, tt3.data AS data3
FROM test_table1 AS tt1
JOIN test_table2 AS tt2 ON tt2.data LIKE CONCAT('%', tt1.data, '%')
JOIN test_table3 AS tt3 ON tt3.data LIKE CONCAT('%', tt1.data, '%');
1180 rows in set (3.860 sec)

まあこれはほとんど意味のないクエリーであるが、とにかく遅ぇことがポイントだ。3.8秒もかかっている。しかもこのパターンはインデックスが使えないからこのクエリーを改善をしようというのはこのトピックではしませんよ(そもそも意味がねえし)。

スロークエリーログの設定

これまあ結局DBのパラメーターを変更せにゃならん。mysql8-php5-customってのを前に作っといたのでそれをつかう。パラメーターグループの作り方はここでは解説しないよーん

あとlong_query_timeの設定も必要。この秒数を越えるとslow queryと判定される。

同期中に変更されるのを待つ

一応確認しとく

slow query logはデフォルトで実はtableに書かれて保存されるんだよね。

MySQL [uploader_demo]> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.014 sec)

MySQL [uploader_demo]> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.003 sec)

MySQL [uploader_demo]> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.003 sec)

(log_outputがTABLEになっとる)

適当に例のおっそいクエリーをなげると

SELECT * FROM mysql.slow_log \G
*************************** 1. row ***************************
    start_time: 2024-02-12 07:52:38.340110
     user_host: admin[admin] @  [172.31.10.144]
    query_time: 00:00:04.075385
     lock_time: 00:00:00.000003
     rows_sent: 1180
 rows_examined: 1200
            db: uploader_demo
last_insert_id: 0
     insert_id: 0
     server_id: 1447881797
      sql_text: SELECT tt1.*, tt2.data AS data2, tt3.data AS data3 FROM test_table1 AS tt1 JOIN test_table2 AS tt2 ON tt2.data LIKE CONCAT('%', tt1.data, '%') JOIN test_table3 AS tt3 ON tt3.data LIKE CONCAT('%', tt1.data, '%')
     thread_id: 10
*************************** 2. row ***************************
    start_time: 2024-02-12 07:53:28.998768
     user_host: admin[admin] @  [172.31.10.144]
    query_time: 00:00:03.925291
     lock_time: 00:00:00.000003
     rows_sent: 1180
 rows_examined: 1200
            db: uploader_demo
last_insert_id: 0
     insert_id: 0
     server_id: 1447881797
      sql_text: SELECT tt1.*, tt2.data AS data2, tt3.data AS data3 FROM test_table1 AS tt1 JOIN test_table2 AS tt2 ON tt2.data LIKE CONCAT('%', tt1.data, '%') JOIN test_table3 AS tt3 ON tt3.data LIKE CONCAT('%', tt1.data, '%')
     thread_id: 10
2 rows in set (0.002 sec)

こんな感じである程度ちゃんと記録されてるんだけど、tableに記録されるといろいろ面倒なのでファイルに追い出していくぞい。

これはlog_outputのパラメーターをFILEにする

そしたらばこのログをcloudwatch logsにエクスポートするぞい。RDSの設定変更より

この後、遅いクエリーを3回くらい繰り返した。ちゃんと送れてるっぽい。

ってなわけでスローなクエリーも調査しておくといいこともあるぞ。あんま溜まりすぎると問題やけどな(開発段階でなんとかしときたいもんだよな〜)