GA

2023/02/08

インデックス vs InnoDBログ書き込みのサイズ観測

TL;DR

  • セカンダリーキーは意外とログ書き込みに響く。無駄なやつをなるべく消したりマージできたりするといいかも。

  • ナチュラルキーを使うのは書き込み面では誤差か。

    • 読み取りで差が出るといいなと思うけれど今日はやらない。
tablesizesize_vs_t1lsnlsn_vs_t1comment
t113,631,4881.0031,977,6821.00auto_increment PK + 0 key
t217,825,7921.3135,158,2931.10auto_increment PK + 1 key
t319,922,9441.4638,051,5391.19auto_increment PK + 2 key
t415,728,6401.1533,413,6341.04natural PK + 0 key
t519,922,9441.4637,070,7121.16natural PK + 1 key


MySQL 8.0.23を用意。


$ bin/mysqld --no-defaults --initialize-insecure --datadir=/tmp/data --lower_case_table_names=1 --log-error-verbosity=3

$ bin/mysqld --no-defaults --datadir=/tmp/data --lower_case_table_names=1 --log-error-verbosity=3 --daemonize
mysqld will log errors to /tmp/data/host.err
mysqld is running as pid 14865

テスト用のテーブルその1。AUTO_INCREMENTがPRIMARY KEYで他のインデックスなし。

mysql> CREATE TABLE d1.t1 (
    ->   num BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   user_id INT UNSIGNED NOT NULL,
    ->   something_id INT UNSIGNED NOT NULL,
    ->   something_count INT UNSIGNED NOT NULL,
    ->   updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY(num)
    -> );
Query OK, 0 rows affected (0.03 sec)

データロード後にチェンジバッファのマージでLSNが進むのが嫌なのでチェンジバッファはOFFに。

mysql> SET GLOBAL innodb_change_buffering = none;
Query OK, 0 rows affected (0.00 sec)

i_s.innodb_metrics の log_lsn_current が有効になってなかったので有効化。

mysql> SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current';
+-----------------+-------+----------+
| name            | count | status   |
+-----------------+-------+----------+
| log_lsn_current |     0 | disabled |
+-----------------+-------+----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_monitor_enable = 'log_lsn_current';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current';
+-----------------+----------+---------+
| name            | count    | status  |
+-----------------+----------+---------+
| log_lsn_current | 18040637 | enabled |
+-----------------+----------+---------+
1 row in set (0.00 sec)

で、データをロード。

$ perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/tmp/mysql.sock", "root", ""); for (my $something_id= 1; $something_id <= 100; $something_id++) { for (my $user_id= 1; $user_id <= 1000; $user_id++) { $conn->do("INSERT INTO t1 VALUES (NULL, ?, ?, 1, NOW())", undef, $user_id, $something_id) } }'

$ bin/mysql -e "SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current'"
+-----------------+----------+---------+
| name            | count    | status  |
+-----------------+----------+---------+
| log_lsn_current | 50018319 | enabled |
+-----------------+----------+---------+

進んだLSNは 31,977,682 。次。

mysql> CREATE TABLE d1.t2 (
    ->   num BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   user_id INT UNSIGNED NOT NULL,
    ->   something_id INT UNSIGNED NOT NULL,
    ->   something_count INT UNSIGNED NOT NULL,
    ->   updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY(num),
    ->   KEY idx_userid (user_id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current';
+-----------------+----------+---------+
| name            | count    | status  |
+-----------------+----------+---------+
| log_lsn_current | 50037236 | enabled |
+-----------------+----------+---------+
1 row in set (0.00 sec)

セカンダリーキーを1個増やしたバージョン。

$ perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/tmp/mysql.sock", "root", ""); for (my $something_id= 1; $something_id <= 100; $something_id++) { for (my $user_id= 1; $user_id <= 1000; $user_id++) { $conn->do("INSERT INTO t2 VALUES (NULL, ?, ?, 1, NOW())", undef, $user_id, $something_id) } }'

$ bin/mysql -e "SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current'"
+-----------------+----------+---------+
| name            | count    | status  |
+-----------------+----------+---------+
| log_lsn_current | 85195529 | enabled |
+-----------------+----------+---------+

35,158,293 次。

mysql> CREATE TABLE d1.t3 (
    ->   num BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   user_id INT UNSIGNED NOT NULL,
    ->   something_id INT UNSIGNED NOT NULL,
    ->   something_count INT UNSIGNED NOT NULL,
    ->   updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY(num),
    ->   KEY idx_userid (user_id),
    ->   KEY idx_updated (updated)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current';
+-----------------+----------+---------+
| name            | count    | status  |
+-----------------+----------+---------+
| log_lsn_current | 85218863 | enabled |
+-----------------+----------+---------+
1 row in set (0.00 sec)

更に追加でもう一つセカンダリーキー。

$ perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/tmp/mysql.sock", "root", ""); for (my $something_id= 1; $something_id <= 100; $something_id++) { for (my $user_id= 1; $user_id <= 1000; $user_id++) { $conn->do("INSERT INTO t3 VALUES (NULL, ?, ?, 1, NOW())", undef, $user_id, $something_id) } }'

$ bin/mysql -e "SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current'"
+-----------------+-----------+---------+
| name            | count     | status  |
+-----------------+-----------+---------+
| log_lsn_current | 123270402 | enabled |
+-----------------+-----------+---------+

38,051,539 。少なくともファイルサイズほど大きくは増えない。

$ ll d1
total 50188
-rw-r----- 1 yoku0825 yoku0825 13631488 Feb  8 00:33 t1.ibd
-rw-r----- 1 yoku0825 yoku0825 17825792 Feb  8 00:39 t2.ibd
-rw-r----- 1 yoku0825 yoku0825 19922944 Feb  8 00:44 t3.ibd

4つめ。 (user_id, something_id) のナチュラルキー風。カラムの数を変えないために num は自分で詰める。 $user_id のループを内側にしてあるのでPRIMARY KEYのランダムインサート状態になる。
PKが user_id の検索に使えるので実質 t2 プラスアルファ状態。

mysql> CREATE TABLE d1.t4 (
    ->   num BIGINT UNSIGNED NOT NULL,
    ->   user_id INT UNSIGNED NOT NULL,
    ->   something_id INT UNSIGNED NOT NULL,
    ->   something_count INT UNSIGNED NOT NULL,
    ->   updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY(user_id, something_id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current';
+-----------------+-----------+---------+
| name            | count     | status  |
+-----------------+-----------+---------+
| log_lsn_current | 123287410 | enabled |
+-----------------+-----------+---------+
1 row in set (0.00 sec)

$ perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/tmp/mysql.sock", "root", ""); my $n= 1; for (my $something_id= 1; $something_id <= 100; $something_id++) { for (my $user_id= 1; $user_id <= 1000; $user_id++) { $conn->do("INSERT INTO t4 VALUES (?, ?, ?, 1, NOW())", undef, $n++, $user_id, $something_id) } }'

$ bin/mysql -e "SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current'"
+-----------------+-----------+---------+
| name            | count     | status  |
+-----------------+-----------+---------+
| log_lsn_current | 156701044 | enabled |
+-----------------+-----------+---------+

33,413,634 。セカンダリーキーを足してラスト。

mysql> CREATE TABLE d1.t5 (
    ->   num BIGINT UNSIGNED NOT NULL,
    ->   user_id INT UNSIGNED NOT NULL,
    ->   something_id INT UNSIGNED NOT NULL,
    ->   something_count INT UNSIGNED NOT NULL,
    ->   updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    ->   PRIMARY KEY(user_id, something_id),
    ->   KEY idx_updated(updated)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current';
+-----------------+-----------+---------+
| name            | count     | status  |
+-----------------+-----------+---------+
| log_lsn_current | 156720509 | enabled |
+-----------------+-----------+---------+
1 row in set (0.00 sec)

$ bin/mysql -e "SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'log_lsn_current'"
+-----------------+-----------+---------+
| name            | count     | status  |
+-----------------+-----------+---------+
| log_lsn_current | 193791221 | enabled |
+-----------------+-----------+---------+

37,070,712 。実質 t3 相当で似たようなLSN。

で、冒頭の表再掲。

table size size_vs_t1 lsn lsn_vs_t1 comment
t1 13,631,488 1.00 31,977,682 1.00 auto_increment PK + 0 key
t2 17,825,792 1.31 35,158,293 1.10 auto_increment PK + 1 key
t3 19,922,944 1.46 38,051,539 1.19 auto_increment PK + 2 key
t4 15,728,640 1.15 33,413,634 1.04 natural PK + 0 key
t5 19,922,944 1.46 37,070,712 1.16 natural PK + 1 key
​
投稿者 時刻:
ラベル:

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )