序
先日どこかの◯berというサービスがpostgresからmysqlに変えたという話を小耳に挟み、なんやかんやと話を聞いていると、postgresは不変の行データという設計に問題がありmysqlに変えただの、いやいやそれは◯berの勉強不足であってpostgresはHOTが導入されており、そんなことない、みたいな話をしておりました。
何かそんな話あったなぁでも結局調べなかったんよね、という思いを胸に少し調べてみようかと軽い調査を本日始めました。結論から言うと調べてみても(ソースは追ってない)個人的には分かりませんでした 後日効果は確認できました。
事の発端
どこかの◯berというサービスがMySQLに変えたのは実は2016年の話です。今では大分違うかもしれません。元ネタは以下のようです。
当時このニュースを解説した日本語の記事などは以下な感じでした。
そしてPostgreSQLのHOTについての指摘(説明不足)は以下のようです。
簡単に解説すると、PostgreSQLは不変の行データで管理する設計になっているので、カラムのデータを書き換えると新しい行データを追加するため、インデックスの更新が必要になる場合があるという話のようでした。論点はレプリケーションなど他にもあるのですが、今回は触れません。
そしてHOTについては、8.2以前は新しい行データを追加する度に全インデックスの更新が必ず必要になっていたが、8.3以降行データを間接的に参照する仕組みであるHOTを導入したことにより、必ずしもインデックスの更新が必要なわけでなくなっており、◯berがこのことを説明していないのは片手落ちではないか？という話のようでした。
HOTについて
PostgreSQL派の皆さんはHOTでどんな問題があるのか？という観点で、錯綜している話のようです。なのでHOTについて調べたところ、日本語での解説記事では以下が分かりやすそうでした。
私もソースコードまでは追っていません。行データを参照するためにワンクッション置くことで、古い行データから新しい行データを参照できるようにする仕組みのようです。こうすることで、インデックスのないカラムの更新ではインデックスの更新が発生しないようになるようです。
実際に確認してみる
試してみないと分かりません。
ソースコード
dockerとpythonとbashかdashが必要です
set -eu
#if false; then
BITS=20
mkdir -p postgres
cd postgres
cat >docker-compose.yml <<EOF
services:
db:
image: postgres
restart: always
volumes:
- ./data:/var/lib/postgresql/data
ports:
- "5432:5432"
environment:
POSTGRES_PASSWORD: example
EOF
docker compose up -d
while ! docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres </dev/null 2>/dev/null; do
sleep 1
done
docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres <<EOF
SELECT 'CREATE DATABASE test'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'test')\\gexec
EOF
rm -f time.log
for tgt_index in 0 1;do
for i in $(seq 0 8); do
cat >hoge.sql <<EOF
create table hoge(
id numeric(20,2),
EOF
j=$((8 - $i))
for k in $(seq $i);do
echo "val_idx$k numeric(20,2),"
done >>hoge.sql
for k in $(seq $j);do
echo "val$k numeric(20,2),"
done >>hoge.sql
offset=1
cat >>hoge.sql <<EOF
tgt_val numeric(20,2),
primary key(id)
);
insert into hoge values(1,1,1,1,1,1,1,1,1,1);
EOF
for l in $(seq $BITS); do
echo "insert into hoge select id+$offset," >>hoge.sql
for k in $(seq $i);do
echo "val_idx$k+$offset,"
done >>hoge.sql
for k in $(seq $j);do
echo "val$k+$offset,"
done >>hoge.sql
cat >>hoge.sql <<EOF
tgt_val+$offset from hoge;
EOF
offset=$((offset + offset))
done
echo "select count(*) from hoge;" >>hoge.sql
for k in $(seq $i);do
echo "create index idx_hoge_val_idx$k on hoge(val_idx$k);" >>hoge.sql
done
if [ $tgt_index -eq 1 ]; then
echo "create index idx_hoge_tgt_val on hoge(tgt_val);" >>hoge.sql
fi
docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres -d test <hoge.sql
docker compose exec -T db psql -v ON_ERROR_STOP=1 -a -U postgres -d test >>time.log <<EOF
\\timing on
update hoge set tgt_val=$offset - tgt_val;
EOF
docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres -d test <<EOF
drop table hoge cascade;
EOF
done
done
docker compose down
grep ^Time time.log | awk '{print $2;}' > onlytime.log
awk '{print "postgres," (int((NR-1)/9)?"index":"no_index") "," (NR-1)%9 "," $1/1000;}' onlytime.log >time.csv
cd ..
mkdir -p mysql
cd mysql
cat >docker-compose.yml <<EOF
services:
db:
image: mysql
restart: always
environment:
MYSQL_ROOT_PASSWORD: example
volumes:
- ./data:/var/lib/mysql
ports:
- "3306:3306"
EOF
docker compose up -d
while ! docker compose exec -T db mysql -uroot -pexample </dev/null 2>/dev/null; do
sleep 1
done
docker compose exec -T db mysql -uroot -pexample <<EOF
CREATE DATABASE if not exists test;
EOF
rm -f time.log
for tgt_index in 0 1;do
for i in $(seq 0 8); do
cat >hoge.sql <<EOF
create table hoge(
id numeric(20,2),
EOF
j=$((8 - $i))
for k in $(seq $i);do
echo "val_idx$k numeric(20,2),"
done >>hoge.sql
for k in $(seq $j);do
echo "val$k numeric(20,2),"
done >>hoge.sql
offset=1
cat >>hoge.sql <<EOF
tgt_val numeric(20,2),
primary key(id)
);
insert into hoge values(1,1,1,1,1,1,1,1,1,1);
EOF
for l in $(seq $BITS); do
echo "insert into hoge select id+$offset," >>hoge.sql
for k in $(seq $i);do
echo "val_idx$k+$offset,"
done >>hoge.sql
for k in $(seq $j);do
echo "val$k+$offset,"
done >>hoge.sql
cat >>hoge.sql <<EOF
tgt_val+$offset from hoge;
EOF
offset=$((offset + offset))
done
echo "select count(*) from hoge;" >>hoge.sql
for k in $(seq $i);do
echo "create index idx_hoge_val_idx$k on hoge(val_idx$k);" >>hoge.sql
done
if [ $tgt_index -eq 1 ]; then
echo "create index idx_hoge_tgt_val on hoge(tgt_val);" >>hoge.sql
fi
docker compose exec -T db mysql -uroot -pexample test <hoge.sql
docker compose exec -T db mysql -uroot -pexample test >>time.log <<EOF
set profiling=1;
update hoge set tgt_val=$offset - tgt_val;
show profiles;
EOF
docker compose exec -T db mysql -uroot -pexample test <<EOF
drop table hoge cascade;
EOF
done
done
docker compose down
grep -E '^1' time.log | awk '{print $2}' >onlytime.log
awk '{print "mysql," (int((NR-1)/9)?"index":"no_index") "," (NR-1)%9 "," $1;}' onlytime.log >time.csv
cd ..
echo "db,tgt_idx,idx_num,time" >time.csv
cat */time.csv >>time.csv
#fi
if [ ! -d env ]; then
python3 -m venv env
fi
. env/bin/activate
pip install -U pip setuptools
pip install pandas matplotlib
cat >plot.py <<EOF
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_csv('time.csv')
print(df)
df = df.groupby(['db', 'tgt_idx', 'idx_num']).mean()
df = df.unstack(level=['db','tgt_idx'])
df = df.stack(level=0)
df = df.reset_index(drop=True)
print(df)
ax = df.plot()
ax.set_xlabel('Number of indexes (excluding primary keys and the changed column)')
ax.set_ylabel('time[sec]')
ax.set_title('Time taken to update one column in 1M rows')
plt.savefig('hoge.svg')
EOF
python plot.py
deactivate
グラフ
上のソースコードを実行したら作成されるものです。100万行のテストデータをMySQL 9.0.1とPostgresSQL 16.4で1列だけupdateした際の時間です。横軸には変更した列と主キーを除いたインデックスの数、縦軸はかかった時間[秒]です。
考察
MySQLはインデックスのないカラムの変更時は、横軸に対して時間が変わらない傾向が見て取れます。インデックスのあるカラムの変更時は、他のカラムの横軸に対して緩い上昇傾向を確認できます。PostgreSQLだと変更したカラムにインデックスがない方が若干速いようですが、横軸に対して比較的急に上昇する傾向があるようです。
HOTのあるPostgreSQLではMySQLのような傾向を示すのではないかと考えていたのですが、このコード＋私の環境だとそうではないようです。
まとめ
- 8年経過した今も○berの主張の真偽は個人的に分からない
- 試してみても分からない
【追記】その1
まずは統計情報を追ってみました。
--- hoge.sh.org 2024-09-10 18:50:22.486892150 +0900
+++ hoge.sh 2024-09-10 18:43:39.513877458 +0900
@@ -1,6 +1,6 @@
set -eu
#if false; then
-BITS=20
+BITS=16
mkdir -p postgres
cd postgres
cat >docker-compose.yml <<EOF
@@ -67,8 +67,12 @@ EOF
fi
docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres -d test <hoge.sql
docker compose exec -T db psql -v ON_ERROR_STOP=1 -a -U postgres -d test >>time.log <<EOF
+select pg_sleep(1);
+select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
\\timing on
update hoge set tgt_val=$offset - tgt_val;
+select pg_sleep(1);
+select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
EOF
docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres -d test <<EOF
drop table hoge cascade;
時間測定が目的ではないので、行数を20BITS=1Mから16BTIS=64Kまで落として、postgresのupdate前後でpg_stat_user_tablesテーブルから統計情報を取得してみました。出力はpostgres/time.logに出ています。
postgres/time.log
select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_tup_newpage_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+-------------------+------------+------------
65536 | 0 | 0 | 0 | 0 | 65536 | 0
(1 row)
...
update hoge set tgt_val=65536 - tgt_val;
...
select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_tup_newpage_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+-------------------+------------+------------
65536 | 65536 | 0 | 5 | 65531 | 65536 | 65536
(1 row)
一目瞭然ですが、n_tup_hot_updが僅かに5回。n_tup_newpage_updが65531回ということで、ほとんどHOTでupdateされていないのが原因のようです。
良く考えたら当たり前なのですが、全行更新しているので、ほとんどのページは充填率100%になり新しく作成されるため、HOTが機能しないのではないか？と予想しています。ただupdateをwhere id % 100 = 0としてもn_tup_hot_updが5のままなので、やっぱりよく分かりません。
select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_tup_newpage_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+-------------------+------------+------------
65536 | 655 | 0 | 5 | 650 | 65536 | 655
(1 row)
その後充填率について調べてみたところ、
テーブルの充填率が100%にならないと新しいページが確保されないらしく、末尾に追加するケースしかなければいいのですが、updateなどがあるケースでは常に新しいページが確保されることになり、テーブル構築直後などはほとんどのページが100%状態になっていることが分かりました。
create table時にwith句でfillfactorを70にしてあげることで、ようやくHOTによる更新を確認出来ました。
select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_tup_newpage_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+-------------------+------------+------------
65536 | 655 | 0 | 655 | 0 | 65536 | 655
(1 row)
今現在はスクリプトをフルに流せないので、夜中に流した結果のグラフを朝にでも載せる予定です。
で、流したスクリプトが以下(差分のみ)です。上では1/100の範囲になっていますが、元のものと区間が違っていて比較が微妙になるので、1/10の範囲を10回計測することで同じ範囲にしています。auto commitのまま実行しているので、トランザクションを10分割したような形になります。
またMySQL側はset profiling=1を使った計測が非推奨だったので、performance_schemaを使った計測に変えたなどの変更があります。
--- hoge.sh.org 2024-09-10 18:50:22.486892150 +0900
+++ hoge.sh 2024-09-11 02:33:14.412942681 +0900
@@ -42,7 +42,8 @@ EOF
cat >>hoge.sql <<EOF
tgt_val numeric(20,2),
primary key(id)
-);
+)
+with (fillfactor=70);
insert into hoge values(1,1,1,1,1,1,1,1,1,1);
EOF
for l in $(seq $BITS); do
@@ -66,10 +67,20 @@ EOF
echo "create index idx_hoge_tgt_val on hoge(tgt_val);" >>hoge.sql
fi
docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres -d test <hoge.sql
- docker compose exec -T db psql -v ON_ERROR_STOP=1 -a -U postgres -d test >>time.log <<EOF
+ cat >time.sql <<EOF
+select pg_sleep(1);
+select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
\\timing on
-update hoge set tgt_val=$offset - tgt_val;
EOF
+ for m in $(seq 0 9);do
+ echo "update hoge set tgt_val=$offset - tgt_val where id % 10 = $m;"
+ done >>time.sql
+ cat >>time.sql <<EOF
+\\timing off
+select pg_sleep(1);
+select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='hoge';
+EOF
+ docker compose exec -T db psql -v ON_ERROR_STOP=1 -a -U postgres -d test >>time.log <time.sql
docker compose exec -T db psql -v ON_ERROR_STOP=1 -U postgres -d test <<EOF
drop table hoge cascade;
EOF
@@ -77,7 +88,7 @@ EOF
done
docker compose down
grep ^Time time.log | awk '{print $2;}' > onlytime.log
-awk '{print "postgres," (int((NR-1)/9)?"index":"no_index") "," (NR-1)%9 "," $1/1000;}' onlytime.log >time.csv
+awk '{print "postgres," (int((NR-1)/90)?"index":"no_index") "," int((NR-1)/10)%9 "," $1/100;}' onlytime.log >time.csv
cd ..
mkdir -p mysql
cd mysql
@@ -143,19 +154,25 @@ EOF
echo "create index idx_hoge_tgt_val on hoge(tgt_val);" >>hoge.sql
fi
docker compose exec -T db mysql -uroot -pexample test <hoge.sql
- docker compose exec -T db mysql -uroot -pexample test >>time.log <<EOF
-set profiling=1;
-update hoge set tgt_val=$offset - tgt_val;
-show profiles;
+ cat >time.sql <<EOF
+update performance_schema.setup_instruments set enabled='YES', timed='YES' where name='stage/sql/update';
+update performance_schema.setup_consumers set ENABLED='YES' where NAME LIKE 'events_statements_%';
+EOF
+ for m in $(seq 0 9);do
+ echo "update hoge set tgt_val=$offset - tgt_val where id % 10 = $m;"
+ done >>time.sql
+ cat >>time.sql <<EOF
+select truncate(timer_wait/1000000000000,6) as duration, sql_text from performance_schema.events_statements_history;
EOF
+ docker compose exec -T db mysql -uroot -pexample test >>time.log <time.sql
docker compose exec -T db mysql -uroot -pexample test <<EOF
drop table hoge cascade;
EOF
done
done
docker compose down
-grep -E '^1' time.log | awk '{print $2}' >onlytime.log
-awk '{print "mysql," (int((NR-1)/9)?"index":"no_index") "," (NR-1)%9 "," $1;}' onlytime.log >time.csv
+grep -E '^[0-9]' time.log | awk '{print $1}' >onlytime.log
+awk '{print "mysql," (int((NR-1)/90)?"index":"no_index") "," int((NR-1)/10)%9 "," $1*10;}' onlytime.log >time.csv
cd ..
echo "db,tgt_idx,idx_num,time" >time.csv
cat */time.csv >>time.csv
その結果が以下です(想定より時間がかかり、今しがた終わった)。
PostgreSQL側は想定どおりのグラフになりましたが、MySQLは10分割がいけなかったのかperformance_schemaの影響か、随分と測定結果が変わってしまいました。MySQL側はまた今晩測り直します。
軽い作業が多かったので今しがた裏で走らせました。前回からの差分が以下です。
--- hoge2.sh 2024-09-11 16:44:39.653310996 +0900
+++ hoge3.sh 2024-09-11 16:47:00.444217849 +0900
@@ -154,24 +154,17 @@ EOF
echo "create index idx_hoge_tgt_val on hoge(tgt_val);" >>hoge.sql
fi
docker compose exec -T db mysql -uroot -pexample test <hoge.sql
- cat >time.sql <<EOF
-update performance_schema.setup_instruments set enabled='YES', timed='YES' where name='stage/sql/update';
-update performance_schema.setup_consumers set ENABLED='YES' where NAME LIKE 'events_statements_%';
-EOF
for m in $(seq 0 9);do
echo "update hoge set tgt_val=$offset - tgt_val where id % 10 = $m;"
- done >>time.sql
- cat >>time.sql <<EOF
-select truncate(timer_wait/1000000000000,6) as duration, sql_text from performance_schema.events_statements_history;
-EOF
- docker compose exec -T db mysql -uroot -pexample test >>time.log <time.sql
+ done >time.sql
+ docker compose exec -T db mysql -uroot -pexample -vvv test >>time.log <time.sql
docker compose exec -T db mysql -uroot -pexample test <<EOF
drop table hoge cascade;
EOF
done
done
docker compose down
-grep -E '^[0-9]' time.log | awk '{print $1}' >onlytime.log
+grep -E '^Query' time.log | sed 's/^[^(]*(//;s/)[^)]*$//' | perl -ne 'print eval(join("", map {if (/\d+/){"+".$_}elsif(/sec/){"*1"}elsif(/min/){"*60"}} split(" ")))."\n";' >onlytime.log
awk '{print "mysql," (int((NR-1)/90)?"index":"no_index") "," int((NR-1)/10)%9 "," $1*10;}' onlytime.log >time.csv
cd ..
echo "db,tgt_idx,idx_num,time" >time.csv
これにより、performance_schemaを使わずmysqlコマンドだけで測るようになるので、PostgreSQL側とも同じような測り方になったはずです。結果のグラフが以下になります。
結果のグラフが以下になります。無風状態ではなかったにも関わらず、マシな形になったのですが、およそ変わらない結果になっています。とりあえずチューニングをしていないMySQLでは、10分割すると、およそ10倍のコストがかかるようです。本稿ではpostgresのHOTの効果を確認するのが主目的なので、一旦これで終わりにします。
追記その1のまとめ
- HOTの効果を出すにはページ内に空きが必要
- 空きを作るにはcreate table時にwith句でfillfactorを指定する(既定は100)
- HOTの効果が出ればupdateのコストは格段に下がる
【追記】その2
分割した結果、MySQLで芳しくない結果が出た件について、調べてみました。簡単のため、調査は65536行で、主キー以外インデックスのないもので行います。
テーブル
mysql> describe hoge;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id | decimal(20,2) | NO | PRI | NULL | |
| val1 | decimal(20,2) | YES | | NULL | |
| val2 | decimal(20,2) | YES | | NULL | |
| val3 | decimal(20,2) | YES | | NULL | |
| val4 | decimal(20,2) | YES | | NULL | |
| val5 | decimal(20,2) | YES | | NULL | |
| val6 | decimal(20,2) | YES | | NULL | |
| val7 | decimal(20,2) | YES | | NULL | |
| val8 | decimal(20,2) | YES | | NULL | |
| tgt_val | decimal(20,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
10 rows in set (0.02 sec)
mysql>
元のグラフで言えば、mysql,no_indexという線の横軸が0のときのテーブルが該当します。ここにこんなデータが入った状態から更新をかけるわけです。
mysql> select * from hoge limit 10;
+-------+-------+-------+-------+-------+-------+-------+-------+-------+---------+
| id | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8 | tgt_val |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+---------+
| 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 |
| 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 |
| 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 |
| 5.00 | 5.00 | 5.00 | 5.00 | 5.00 | 5.00 | 5.00 | 5.00 | 5.00 | 5.00 |
| 6.00 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 | 6.00 |
| 7.00 | 7.00 | 7.00 | 7.00 | 7.00 | 7.00 | 7.00 | 7.00 | 7.00 | 7.00 |
| 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 | 8.00 |
| 9.00 | 9.00 | 9.00 | 9.00 | 9.00 | 9.00 | 9.00 | 9.00 | 9.00 | 9.00 |
| 10.00 | 10.00 | 10.00 | 10.00 | 10.00 | 10.00 | 10.00 | 10.00 | 10.00 | 10.00 |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+---------+
10 rows in set (0.00 sec)
mysql>
※limitをかけていますが、この調査では65536行分のデータが入っています。
クエリ
実際に時間を計測したときに投げられたクエリはこんなものになります(スクリプトから生成されているファイルです)。
mysql/time.sql
update performance_schema.setup_instruments set enabled='YES', timed='YES' where name='stage/sql/update';
update performance_schema.setup_consumers set ENABLED='YES' where NAME LIKE 'events_statements_%';
update hoge set tgt_val=65536 - tgt_val where id % 10 = 0;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 1;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 2;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 3;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 4;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 5;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 6;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 7;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 8;
update hoge set tgt_val=65536 - tgt_val where id % 10 = 9;
select truncate(timer_wait/1000000000000,6) as duration, sql_text from performance_schema.events_statements_history;
最初の2行と最後の1行は時間計測用のオマジナイなので、気にしなくてもいいです。見て頂きたいのはupdate文になります。この文はどこを切っても10%程度の行を更新するよう、意図的に分割した行で全行更新かけています。つまりこれはpostgresqlでHOTが効きやすいよう、意図的に選んだ分割方法でupdateしているのであり、実運用の際に必ずしもこうなるであろう分割というわけではありません。
計測
上記クエリを計測に使ったマシンよりさらに非力なVM上で動かすと、
$ docker compose exec -T db mysql -uroot -pexample test <time.sql |grep -E '^[0-9]' | awk 'BEGIN{sum=0;}{print $0;sum+=$1;}END{print sum;}'
mysql: [Warning] Using a password on the command line interface can be insecure.
0.1608 update hoge set tgt_val=65536 - tgt_val where id % 10 = 6
0.5094 update hoge set tgt_val=65536 - tgt_val where id % 10 = 7
0.1700 update hoge set tgt_val=65536 - tgt_val where id % 10 = 8
0.3638 update hoge set tgt_val=65536 - tgt_val where id % 10 = 9
0.1722 update hoge set tgt_val=65536 - tgt_val where id % 10 = 0
0.1639 update hoge set tgt_val=65536 - tgt_val where id % 10 = 1
0.3105 update hoge set tgt_val=65536 - tgt_val where id % 10 = 2
0.2251 update hoge set tgt_val=65536 - tgt_val where id % 10 = 3
0.1500 update hoge set tgt_val=65536 - tgt_val where id % 10 = 4
0.5349 update hoge set tgt_val=65536 - tgt_val where id % 10 = 5
2.7606
$
合計で2.8秒かかることが分かります。ではクエリを分割しない場合はどうなるでしょう？
分割しないときの計測
time2.sql
update performance_schema.setup_instruments set enabled='YES', timed='YES' where name='stage/sql/update';
update performance_schema.setup_consumers set ENABLED='YES' where NAME LIKE 'events_statements_%';
update hoge set tgt_val=65536 - tgt_val;
select truncate(timer_wait/1000000000000,6) as duration, sql_text from performance_schema.events_statements_history order by event_id desc limit 1;
$ docker compose exec -T db mysql -uroot -pexample test <time2.sql |grep -E '^[0-9]' | awk 'BEGIN{sum=0;}{print $0;sum+=$1;}END{print sum;}'
mysql: [Warning] Using a password on the command line interface can be insecure.
0.7451 update hoge set tgt_val=65536 - tgt_val
0.7451
$
0.7秒と随分少なくなりました。傾向として、分割すると数倍の時間がかかることが分かります。
実行計画に問題はあるか？
分割すると実行計画に問題があるのでしょうか？
mysql> explain update hoge set tgt_val=65536 - tgt_val where id % 10 = 0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | UPDATE | hoge | NULL | index | NULL | PRIMARY | 9 | NULL | 63491 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.03 sec)
mysql>
主キーを使って走査してフルスキャンするだけのようです。これは想定通りの挙動なので、問題ありません。ただ、全行フルスキャンしながらupdateしていくので、行数が多ければそれなりの時間かかるし、分割数が多いほどフルスキャンの回数は増えるでしょう。
分割方法を変えてみたらどうか？
updateを以下のクエリに変えてみます。
time3.sql
update performance_schema.setup_instruments set enabled='YES', timed='YES' where name='stage/sql/update';
update performance_schema.setup_consumers set ENABLED='YES' where NAME LIKE 'events_statements_%';
update hoge set tgt_val=65536 - tgt_val where id >= 0 * (65536/10) + 1 and id < (0+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 1 * (65536/10) + 1 and id < (1+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 2 * (65536/10) + 1 and id < (2+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 3 * (65536/10) + 1 and id < (3+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 4 * (65536/10) + 1 and id < (4+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 5 * (65536/10) + 1 and id < (5+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 6 * (65536/10) + 1 and id < (6+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 7 * (65536/10) + 1 and id < (7+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 8 * (65536/10) + 1 and id < (8+1) * (65536/10)+1;
update hoge set tgt_val=65536 - tgt_val where id >= 9 * (65536/10) + 1 and id < (9+1) * (65536/10)+1;
select truncate(timer_wait/1000000000000,6) as duration, sql_text from performance_schema.events_statements_history order by event_id;
愚直な重複区間のない範囲分割です。実行すると…
$ docker compose exec -T db mysql -uroot -pexample test <time3.sql |grep -E '^[0-9]' | awk 'BEGIN{sum=0;}{print $0;sum+=$1;}END{print sum;}'
mysql: [Warning] Using a password on the command line interface can be insecure.
0.0922 update hoge set tgt_val=65536 - tgt_val where id >= 0 * (65536/10) + 1 and id < (0+1) * (65536/10)+1
0.0885 update hoge set tgt_val=65536 - tgt_val where id >= 1 * (65536/10) + 1 and id < (1+1) * (65536/10)+1
0.0957 update hoge set tgt_val=65536 - tgt_val where id >= 2 * (65536/10) + 1 and id < (2+1) * (65536/10)+1
0.1780 update hoge set tgt_val=65536 - tgt_val where id >= 3 * (65536/10) + 1 and id < (3+1) * (65536/10)+1
0.0915 update hoge set tgt_val=65536 - tgt_val where id >= 4 * (65536/10) + 1 and id < (4+1) * (65536/10)+1
0.0935 update hoge set tgt_val=65536 - tgt_val where id >= 5 * (65536/10) + 1 and id < (5+1) * (65536/10)+1
0.0868 update hoge set tgt_val=65536 - tgt_val where id >= 6 * (65536/10) + 1 and id < (6+1) * (65536/10)+1
0.0912 update hoge set tgt_val=65536 - tgt_val where id >= 7 * (65536/10) + 1 and id < (7+1) * (65536/10)+1
0.0904 update hoge set tgt_val=65536 - tgt_val where id >= 8 * (65536/10) + 1 and id < (8+1) * (65536/10)+1
0.0896 update hoge set tgt_val=65536 - tgt_val where id >= 9 * (65536/10) + 1 and id < (9+1) * (65536/10)+1
0.9974
$
1秒程度になりました。これなら許容範囲ですね。
つまりHOT用に範囲内の10%の行だけupdateがかかるように調整した結果、思いのほかMySQLには不利な条件になってしまっていたということのようです。
MySQLはこれ以上速くできないのか？
検索系ならキャッシュを増やせば…みたいなことはありそうなのですが、更新系なのでそれも無理そうです。トランザクションのディスク反映を遅らせるみたいな設定は可能ですが、安全性に難があります。一言で言えばPostgreSQLよりMySQLの方が、ディスク書き込み量が多いということなのだと思います。
なお、この計測PCのディスクはHDDです。SSDより何桁も遅いディスクになります。
メモリ上で比較したらどうなるのか？
というわけで、今度はtmpfs(所謂RAMディスク)上でDBを構築してみます。実行スクリプトをhoge.shとして以下のような操作をするということです。
$ mkdir mnt
$ sudo mount -t tmpfs -o size=10g /dev/shm mnt
$ cd mnt
$ sh ../hoge.sh
今回は10GBほどtmpfsに割り当ててます。結果は以下のとおりです。
完全に想定したとおりの結果が得られました。
○berは正しかったのか？(ポエム)
個人的な意見です。ある意味正しかったのかと思います。HOTは万能ではなく、ページがあふれるようなトランザクションに効果がありません。HOTが効かないケースでは従来どおりインデックスの多い行ほど遅くなります。規模が大きくなるほどこれは無視できないハンデになるのではないかと…
ただ特性が分かっていれば、対応も出来そうで、個人的には、定性的にはバッチ更新のような一括更新はMySQLの方が向いている気がします。同様に少量なリアルタイムな更新ならPostgreSQLの方が向いている気がします。
まあどちらでも現実的にはそこまで大きな差があるわけではないと思いますが…
追記その2のまとめ
- MySQLが分割時に遅くなるのは全行更新に近いディスク書き込みを毎回実施することになるため
- PostgreSQLより遅くなるのはディスク書き込み量がより多いからと推測
- tmpfsを使用して比較したところ、想定通りの結果が得られた