PostgreSQL・MySQLのストレージエンジンとロック機構の違い｜Linuxセルフホスト選定の実践基準

この記事のポイント

・MySQLのInnoDBはPK順にデータを物理配置するクラスタードインデックス構造
・PostgreSQLはヒープ構造でdead tupleが蓄積するためVACUUMが運用上の必須コスト
・ロック競合の調査はMySQL: SHOW ENGINE INNODB STATUS / PostgreSQL: pg_locksで行う
・書き込み多め・PK検索中心→MySQL、複雑クエリ・整合性重視→PostgreSQLが実務の目安

# InnoDBテーブルのインデックス情報確認
mysql> SHOW INDEX FROM orders\G
*************************** 1. row ***************************
        Table: orders
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: order_id
    Collation: A
  Cardinality: 128450
   Index_type: BTREE
*************************** 2. row ***************************
        Table: orders
   Non_unique: 1
     Key_name: idx_customer_id
 Seq_in_index: 1
  Column_name: customer_id
    Collation: A
  Cardinality: 18320
   Index_type: BTREE
2 rows in set (0.00 sec)

# 長時間実行トランザクションの確認
mysql> SELECT trx_id, trx_started, trx_query,
              TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS elapsed_sec
       FROM information_schema.INNODB_TRX
       ORDER BY trx_started\G
*************************** 1. row ***************************
        trx_id: 421832
   trx_started: 2026-09-15 10:23:41
     trx_query: NULL
   elapsed_sec: 3847
1 row in set (0.00 sec)

-- テーブルごとのdead tuple数確認
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;

 schemaname |  relname  | n_live_tup | n_dead_tup |        last_autovacuum
------------+-----------+------------+------------+-------------------------------
 public     | orders    |     128450 |      31204 | 2026-09-15 14:30:22.891423+09
 public     | sessions  |      42100 |      28300 | 2026-09-15 13:55:10.445123+09
 public     | products  |      18200 |       3240 | 2026-09-15 14:10:05.112345+09
(3 rows)

-- 手動VACUUMの詳細出力
testdb=# VACUUM VERBOSE orders;
INFO:  vacuuming "public.orders"
INFO:  scanned index "orders_pkey" to remove 31204 row versions
DETAIL:  CPU: user: 0.18 s, system: 0.04 s, elapsed: 0.62 s
INFO:  "orders": removed 31204 row versions in 412 pages
DETAIL:  CPU: user: 0.06 s, system: 0.02 s, elapsed: 0.18 s
INFO:  "orders": found 31204 removable, 128450 nonremovable row versions in 1830 out of 1830 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4217893
          Skipped 0 pages due to buffer pins, 0 frozen pages.
          CPU: user: 0.44 s, system: 0.10 s, elapsed: 1.82 s
VACUUM

-- デッドロック情報の確認（最後に発生したデッドロック1件を表示）
mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-09-15 11:42:38 140382923776000
*** (1) TRANSACTION:
TRANSACTION 421905, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 42, query id 18234 192.168.10.x appuser
INSERT INTO orders (customer_id, amount) VALUES (5012, 3500)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 312 page no 8 n bits 96 index PRIMARY of table .
trx id 421905 lock_mode X locks gap before rec insert intention waiting
...
*** WE ROLL BACK TRANSACTION (2)

-- 現在のロック待機を確認するクエリ
SELECT
  blocked.pid         AS blocked_pid,
  blocked.query       AS blocked_query,
  blocking.pid        AS blocking_pid,
  blocking.query      AS blocking_query,
  blocked.wait_event  AS wait_event
FROM  pg_stat_activity AS blocked
JOIN  pg_stat_activity AS blocking
  ON  blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

 blocked_pid |              blocked_query               | blocking_pid |         blocking_query         | wait_event
-------------+------------------------------------------+--------------+--------------------------------+------------
         284 | UPDATE orders SET status=2 WHERE id=1042 |          271 | UPDATE orders SET amount=8800  | relation
(1 row)

# ロック待ちタイムアウト秒数の確認
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

# ロック待ちが多い場合は短縮して早期エラー検知（my.cnf で設定）
# innodb_lock_wait_timeout = 10

-- 60秒以上実行中のクエリを確認
SELECT pid, now() - query_start AS duration, query, state, wait_event_type
FROM pg_stat_activity
WHERE state != 'idle'
  AND (now() - query_start) > interval '60 seconds'
ORDER BY duration DESC;

  pid  | duration  |                    query                     | state  | wait_event_type
-------+-----------+----------------------------------------------+--------+-----------------
  1842 | 00:08:23  | UPDATE orders SET status=3 WHERE customer_id | active | Lock
(1 row)

-- ブロッキングセッションを強制終了（データは失われない）
SELECT pg_terminate_backend(1842);
 pg_terminate_backend
----------------------
 t
(1 row)