Database Server Administration
データベースサーバー管理 - Linux システム管理完全ガイド
作成日: 2026-04-10
対象読者: Linux システム管理者、DBA、インフラエンジニア
対象バージョン: MySQL 8.0 / MariaDB 10.11 / PostgreSQL 16
目次
- はじめに
- MySQL / MariaDB 管理
- 2.1 インストールと初期設定
- 2.2 設定ファイル mycnf の詳細
- 2.3 ユーザー管理・権限制御
- 2.4 バックアップと復元
- 2.5 レプリケーション
- 2.6 InnoDB チューニング
- 2.7 スロークエリログと分析
- 2.8 監視とモニタリング
- PostgreSQL 管理
- 3.1 インストールと初期設定
- 3.2 postgresql.conf の詳細
- 3.3 pg_hba.conf 認証設定
- 3.4 ロールと権限管理
- 3.5 バックアップと復元
- 3.6 ストリーミングレプリケーション
- 3.7 WAL 管理
- 3.8 VACUUM / ANALYZE
- 3.9 PgBouncer コネクションプーリング
- 3.10 監視とモニタリング
- MySQL vs PostgreSQL 比較
- トラブルシューティング
- ベストプラクティス
- 参考資料
1. はじめに
データベースサーバーは現代のITインフラにおいて最も重要なコンポーネントの一つです。Web アプリケーション、業務システム、分析基盤など、あらゆるシステムのデータ永続化の中核を担います。Linux システム管理者として、データベースサーバーの適切な設計・構築・運用・監視の知識は不可欠です。
本ガイドでは、Linux 環境における二大 RDBMSである MySQL/MariaDB と PostgreSQL の管理手法を詳細に解説します。単なるコマンドリファレンスにとどまらず、内部アーキテクチャの理解に基づいた運用設計、パフォーマンスチューニング、障害対応まで体系的にカバーします。
データベースサーバーの役割とアーキテクチャ概要
┌─────────────────────────────────────────────────────────────┐
│ Application Layer │
│ (Web App / API Server / Analytics Tool) │
└─────────────────────────┬───────────────────────────────────┘
│ SQL / Protocol
┌─────────────────────────▼───────────────────────────────────┐
│ Connection Layer │
│ MySQL: Thread per connection / Connection Pool │
│ PostgreSQL: Process per connection / PgBouncer │
└─────────────────────────┬───────────────────────────────────┘
│
┌─────────────────────────▼───────────────────────────────────┐
│ Query Processing │
│ Parser → Optimizer → Executor │
└─────────────────────────┬───────────────────────────────────┘
│
┌─────────────────────────▼───────────────────────────────────┐
│ Storage Engine │
│ MySQL: InnoDB / MyISAM / etc. │
│ PostgreSQL: Unified Storage (Heap + Index) │
└─────────────────────────┬───────────────────────────────────┘
│
┌─────────────────────────▼───────────────────────────────────┐
│ OS / File System │
│ ext4 / XFS / ZFS + Direct I/O / Buffered I/O │
└─────────────────────────────────────────────────────────────┘
主要な違いの概略
| 項目 | MySQL/MariaDB | PostgreSQL |
|---|---|---|
| ライセンス | GPL v2 (MySQL Community) / GPL (MariaDB) | PostgreSQL License (BSD 系) |
| アーキテクチャ | スレッドベース | プロセスベース |
| ストレージエンジン | プラガブル (InnoDB, MyISAM 等) | 統合ストレージ |
| MVCC 実装 | InnoDB: Undo Log ベース | Heap ベース (可視性マップ) |
| JSON サポート | JSON 型 (MySQL 5.7+) | JSONB 型 (高度な演算子) |
| 拡張性 | プラグイン | Extension システム |
| 主な用途 | Web アプリ、OLTP | OLTP、分析、GIS |
2. MySQL / MariaDB 管理
2.1 インストールと初期設定
RHEL/AlmaLinux 9 へのインストール (MySQL 8.0)
# MySQL 公式リポジトリの追加
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
# GPG キーのインポート
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
# インストール
sudo dnf install -y mysql-community-server
# サービス起動と自動起動設定
sudo systemctl enable --now mysqld
# 初期パスワードの確認
sudo grep 'temporary password' /var/log/mysqld.log
# 出力例:
# 2026-04-10T03:22:15.123456Z 6 [Note] [MY-010454] [Server] A temporary password
# is generated for root@localhost: Abc#12345xyz
# セキュリティ初期化スクリプト
sudo mysql_secure_installation
Ubuntu/Debian へのインストール (MySQL 8.0)
# APT リポジトリの追加
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
sudo apt update
# インストール
sudo apt install -y mysql-server
# サービス確認
sudo systemctl status mysqld
# 出力例:
# ● mysql.service - MySQL Community Server
# Loaded: loaded (/lib/systemd/system/mysql.service; enabled)
# Active: active (running) since Thu 2026-04-10 12:00:00 JST
# Main PID: 1234 (mysqld)
# Status: "Server is operational"
MariaDB のインストール (RHEL/AlmaLinux 9)
# MariaDB 公式リポジトリ設定
sudo curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup \
| sudo bash -s -- --mariadb-server-version="mariadb-10.11"
# インストール
sudo dnf install -y MariaDB-server MariaDB-client
# 起動
sudo systemctl enable --now mariadb
# 初期設定
sudo mariadb-secure-installation
mysql_secure_installation の設定項目
- VALIDATE PASSWORD コンポーネントの設定
- root パスワードの変更
- 匿名ユーザーの削除
- root のリモートログイン禁止
- test データベースの削除
- 権限テーブルのリロード
データディレクトリの初期化 (カスタムパス)
# データディレクトリの準備
sudo mkdir -p /data/mysql
sudo chown -R mysql:mysql /data/mysql
sudo chmod 750 /data/mysql
# SELinux コンテキストの設定 (RHEL 系)
sudo semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
sudo restorecon -Rv /data/mysql
# mysqld の初期化
sudo mysqld --initialize --user=mysql --datadir=/data/mysql
2.2 設定ファイル my.cnf の詳細
MySQL/MariaDB の設定ファイルは /etc/my.cnf または /etc/mysql/my.cnf に配置されます。設定は複数のファイルに分割することも可能です。
設定ファイルの読み込み順序確認
mysql --help | grep "Default options" -A 1
# 出力例:
# Default options are read from the following files in the given order:
# /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
本番環境向け my.cnf の包括的設定例
# /etc/my.cnf
[mysqld]
# ============================================================
# 基本設定
# ============================================================
user = mysql
port = 3306
bind-address = 0.0.0.0
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
tmpdir = /tmp
# サーバーID (レプリケーション用に各サーバーで一意)
server-id = 1
# 文字コード設定
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
# ============================================================
# InnoDB 設定 (最重要)
# ============================================================
# バッファプールサイズ: 物理メモリの 60-80% を目安
innodb_buffer_pool_size = 8G
# バッファプールインスタンス数: バッファプール 1GB ごとに 1
innodb_buffer_pool_instances = 8
# ログファイルサイズ: バッファプールの 25% 程度
innodb_log_file_size = 2G
# ログバッファサイズ
innodb_log_buffer_size = 64M
# フラッシュ方式: fsync が最も安全
innodb_flush_log_at_trx_commit = 1
# I/O 方式: SSD なら O_DIRECT 推奨
innodb_flush_method = O_DIRECT
# I/O スレッド数
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# 並列 I/O キャパシティ: IOPS に応じて調整
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 行ロックのウェイトタイムアウト (秒)
innodb_lock_wait_timeout = 50
# デッドロック検出
innodb_deadlock_detect = ON
# ファイルフォーマット (MySQL 8.0 ではデフォルト)
innodb_file_per_table = ON
# ページ圧縮 (任意)
# innodb_page_size = 16K
# ============================================================
# 接続設定
# ============================================================
max_connections = 500
max_connect_errors = 1000000
wait_timeout = 600
interactive_timeout = 600
net_read_timeout = 60
net_write_timeout = 60
# 接続ごとのバッファ (max_connections と乗算されるため注意)
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M
# ============================================================
# クエリキャッシュ (MySQL 8.0 では廃止)
# ============================================================
# query_cache_type = 0
# query_cache_size = 0
# ============================================================
# バイナリログ設定 (レプリケーション・PITR 用)
# ============================================================
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
# MySQL 8.0 では binlog_expire_logs_seconds を使用
binlog_expire_logs_seconds = 604800
max_binlog_size = 128M
sync_binlog = 1
# GTID (グローバルトランザクションID)
gtid_mode = ON
enforce_gtid_consistency = ON
# ============================================================
# スロークエリログ
# ============================================================
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_slow_admin_statements = ON
# ============================================================
# エラーログ
# ============================================================
log_error = /var/log/mysql/error.log
log_error_verbosity = 2
# ============================================================
# パフォーマンススキーマ
# ============================================================
performance_schema = ON
performance_schema_instrument = 'wait/%=ON'
performance_schema_consumer_events_waits_history_long = ON
# ============================================================
# その他最適化
# ============================================================
# オープンファイル数
open_files_limit = 65535
table_open_cache = 4000
table_definition_cache = 2000
# スレッドキャッシュ
thread_cache_size = 50
# タイムゾーン
default_time_zone = '+09:00'
# SQL モード
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
prompt = "\\u@\\h [\\d]> "
[mysqldump]
single-transaction
quick
max_allowed_packet = 128M
設定値の動的変更と確認
# 現在の設定値の確認
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# +-------------------------+------------+
# | Variable_name | Value |
# +-------------------------+------------+
# | innodb_buffer_pool_size | 8589934592 |
# +-------------------------+------------+
# 動的変更 (再起動不要)
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 16*1024*1024*1024;"
# ステータスの確認
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"
# +-------------------------------------------+------------------+
# | Variable_name | Value |
# +-------------------------------------------+------------------+
# | Innodb_buffer_pool_pages_data | 524288 |
# | Innodb_buffer_pool_pages_dirty | 1024 |
# | Innodb_buffer_pool_pages_free | 0 |
# | Innodb_buffer_pool_pages_total | 524288 |
# | Innodb_buffer_pool_read_requests | 98523456 |
# | Innodb_buffer_pool_reads | 12345 |
# | Innodb_buffer_pool_wait_free | 0 |
# | Innodb_buffer_pool_write_requests | 45678901 |
# +-------------------------------------------+------------------+
# バッファプールヒット率の計算
# ヒット率 = (read_requests - reads) / read_requests * 100
# 上記の例: (98523456 - 12345) / 98523456 * 100 ≈ 99.99%
2.3 ユーザー管理・権限制御
ユーザーの作成と管理
-- データベースへの接続
mysql -u root -p
-- ユーザー一覧の確認
SELECT user, host, authentication_string, account_locked
FROM mysql.user;
-- +------------------+-----------+--------+----------------+
-- | user | host | plugin | account_locked |
-- +------------------+-----------+--------+----------------+
-- | root | localhost | | N |
-- | mysql.infoschema | localhost | | Y |
-- | mysql.session | localhost | | Y |
-- | mysql.sys | localhost | | Y |
-- +------------------+-----------+--------+----------------+
-- アプリケーション用ユーザーの作成
-- ローカルのみアクセス可能なユーザー
CREATE USER 'appuser'@'localhost'
IDENTIFIED BY 'SecureP@ssw0rd!';
-- 特定のサブネットからアクセス可能なユーザー
CREATE USER 'appuser'@'192.168.1.%'
IDENTIFIED BY 'SecureP@ssw0rd!';
-- どこからでもアクセス可能なユーザー (非推奨、セキュリティリスク)
CREATE USER 'reportuser'@'%'
IDENTIFIED BY 'AnotherP@ss!';
-- パスワードポリシーを指定したユーザー作成
CREATE USER 'adminuser'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'StrongP@ss123!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 2;
-- ユーザーのパスワード変更
ALTER USER 'appuser'@'localhost'
IDENTIFIED BY 'NewSecureP@ss!';
-- ユーザーの削除
DROP USER 'olduser'@'localhost';
GRANT による権限付与
-- データベース全体への権限付与
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
-- 読み取り専用権限
GRANT SELECT ON myapp.* TO 'reportuser'@'%';
-- 特定テーブルへの特定権限
GRANT SELECT, INSERT, UPDATE ON myapp.orders TO 'appuser'@'192.168.1.%';
-- 特定カラムへの権限
GRANT SELECT (id, name, email) ON myapp.users TO 'readonly'@'localhost';
-- ストアドプロシージャの実行権限
GRANT EXECUTE ON PROCEDURE myapp.calculate_total TO 'appuser'@'localhost';
-- レプリケーション用権限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%'
IDENTIFIED BY 'ReplP@ss!';
-- バックアップ用権限
GRANT SELECT, SHOW DATABASES, LOCK TABLES, RELOAD,
REPLICATION CLIENT, EVENT, TRIGGER
ON *.* TO 'backup'@'localhost';
-- 権限をさらに他のユーザーに委譲できる WITH GRANT OPTION
GRANT SELECT ON analytics.* TO 'teamlead'@'localhost'
WITH GRANT OPTION;
-- 付与した権限の確認
SHOW GRANTS FOR 'appuser'@'localhost';
-- +--------------------------------------------------------------+
-- | Grants for appuser@localhost |
-- +--------------------------------------------------------------+
-- | GRANT USAGE ON *.* TO `appuser`@`localhost` |
-- | GRANT ALL PRIVILEGES ON `myapp`.* TO `appuser`@`localhost` |
-- +--------------------------------------------------------------+
-- 権限の即時反映 (通常は自動)
FLUSH PRIVILEGES;
REVOKE による権限剥奪
-- 特定権限の剥奪
REVOKE INSERT, UPDATE ON myapp.* FROM 'reportuser'@'%';
-- 全権限の剥奪
REVOKE ALL PRIVILEGES ON myapp.* FROM 'appuser'@'localhost';
-- GRANT OPTION の剥奪
REVOKE GRANT OPTION ON analytics.* FROM 'teamlead'@'localhost';
-- すべての権限と GRANT OPTION を一度に剥奪
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'olduser'@'localhost';
ロールを使った権限管理 (MySQL 8.0 / MariaDB 10.0.5+)
-- ロールの作成
CREATE ROLE 'app_developer';
CREATE ROLE 'app_readonly';
CREATE ROLE 'db_admin';
-- ロールへの権限付与
GRANT ALL PRIVILEGES ON myapp.* TO 'app_developer';
GRANT SELECT ON myapp.* TO 'app_readonly';
GRANT ALL PRIVILEGES ON *.* TO 'db_admin' WITH ADMIN OPTION;
-- ユーザーへのロール割り当て
GRANT 'app_developer' TO 'alice'@'localhost';
GRANT 'app_readonly' TO 'bob'@'%';
-- デフォルトロールの設定
SET DEFAULT ROLE 'app_developer' TO 'alice'@'localhost';
-- ロールのアクティブ化 (セッション内)
SET ROLE 'app_developer';
-- 現在のロールの確認
SELECT CURRENT_ROLE();
-- +----------------+
-- | current_role() |
-- +----------------+
-- | `app_developer`|
-- +----------------+
パスワードポリシーとアカウントロック
-- パスワード検証プラグインの確認
SHOW VARIABLES LIKE 'validate_password%';
-- +--------------------------------------+--------+
-- | Variable_name | Value |
-- +--------------------------------------+--------+
-- | validate_password.check_user_name | ON |
-- | validate_password.dictionary_file | |
-- | validate_password.length | 8 |
-- | validate_password.mixed_case_count | 1 |
-- | validate_password.number_count | 1 |
-- | validate_password.policy | MEDIUM |
-- | validate_password.special_char_count| 1 |
-- +--------------------------------------+--------+
-- アカウントのロック
ALTER USER 'suspicious_user'@'localhost' ACCOUNT LOCK;
-- アカウントのロック解除
ALTER USER 'suspicious_user'@'localhost' ACCOUNT UNLOCK;
-- ログイン失敗でロックされたアカウントの確認
SELECT user, host, account_locked,
password_expired, password_last_changed
FROM mysql.user
WHERE account_locked = 'Y';
2.4 バックアップと復元
mysqldump による論理バックアップ
# 単一データベースのバックアップ
mysqldump -u root -p \
--single-transaction \
--routines \
--triggers \
--events \
--master-data=2 \
myapp > /backup/myapp_$(date +%Y%m%d_%H%M%S).sql
# 全データベースのバックアップ
mysqldump -u root -p \
--single-transaction \
--all-databases \
--routines \
--triggers \
--events \
--master-data=2 \
--flush-logs \
> /backup/all_databases_$(date +%Y%m%d).sql
# 圧縮バックアップ
mysqldump -u root -p --single-transaction myapp \
| gzip > /backup/myapp_$(date +%Y%m%d).sql.gz
# 特定テーブルのバックアップ
mysqldump -u root -p myapp orders customers \
> /backup/myapp_orders_customers.sql
# スキーマのみ (データなし)
mysqldump -u root -p --no-data myapp > /backup/myapp_schema.sql
# データのみ (スキーマなし)
mysqldump -u root -p --no-create-info myapp > /backup/myapp_data.sql
# バックアップの復元
mysql -u root -p myapp < /backup/myapp_20260410.sql
# 圧縮バックアップの復元
gunzip -c /backup/myapp_20260410.sql.gz | mysql -u root -p myapp
# 進捗確認付きの復元
pv /backup/myapp_20260410.sql | mysql -u root -p myapp
Percona XtraBackup による物理バックアップ
# インストール (RHEL 系)
sudo dnf install -y percona-xtrabackup-80
# フルバックアップの実行
xtrabackup --backup \
--user=backup \
--password='BackupP@ss!' \
--target-dir=/backup/full \
--parallel=4 \
--compress \
--compress-threads=4
# 出力例:
# xtrabackup: Transaction log of lsn (1234567) to (1234890) was copied.
# 210410 12:00:00 completed OK!
# 増分バックアップ (フルバックアップ後)
xtrabackup --backup \
--user=backup \
--password='BackupP@ss!' \
--target-dir=/backup/inc1 \
--incremental-basedir=/backup/full
# バックアップの準備 (prepare)
# フルバックアップの準備
xtrabackup --prepare \
--apply-log-only \
--target-dir=/backup/full
# 増分バックアップの適用
xtrabackup --prepare \
--apply-log-only \
--target-dir=/backup/full \
--incremental-dir=/backup/inc1
# 最終準備 (最後の増分の場合は --apply-log-only を省略)
xtrabackup --prepare \
--target-dir=/backup/full
# 復元 (MySQL を停止してから)
sudo systemctl stop mysqld
sudo mv /var/lib/mysql /var/lib/mysql.old
xtrabackup --copy-back \
--target-dir=/backup/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysqld
バイナリログを使ったポイントインタイムリカバリ (PITR)
# バイナリログの一覧確認
mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001 \
| head -50
# 特定時刻までのリカバリ
mysqlbinlog --no-defaults \
--start-datetime="2026-04-10 00:00:00" \
--stop-datetime="2026-04-10 10:30:00" \
/var/log/mysql/mysql-bin.000001 \
/var/log/mysql/mysql-bin.000002 \
| mysql -u root -p
# 特定ポジションまでのリカバリ
mysqlbinlog --no-defaults \
--start-position=4 \
--stop-position=12345 \
/var/log/mysql/mysql-bin.000003 \
| mysql -u root -p
# GTID を使ったリカバリ
mysqlbinlog --no-defaults \
--include-gtids="3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100" \
/var/log/mysql/mysql-bin.000001 \
| mysql -u root -p
2.5 レプリケーション
ソース (マスター) の設定
# /etc/my.cnf (ソースサーバー)
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# レプリケーション対象DBの指定 (省略時は全DB)
# binlog_do_db = myapp
# レプリケーション除外DBの指定
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
-- レプリケーション用ユーザーの作成
CREATE USER 'repl'@'192.168.1.%'
IDENTIFIED WITH caching_sha2_password BY 'ReplP@ss123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;
-- ソースのステータス確認
SHOW MASTER STATUS\G
-- *************************** 1. row ***************************
-- File: mysql-bin.000001
-- Position: 1234
-- Binlog_Do_DB:
-- Binlog_Ignore_DB: information_schema,performance_schema
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100
レプリカ (スレーブ) の設定
# /etc/my.cnf (レプリカサーバー)
[mysqld]
server-id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON
# レプリカ用のバイナリログ (カスケードレプリケーション用)
log_bin = /var/log/mysql/mysql-bin.log
log_replica_updates = ON
# リレーログ設定
relay_log = /var/log/mysql/relay-bin.log
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON
-- ソースの初期データをレプリカに投入後、レプリケーション開始
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.10',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='ReplP@ss123!',
SOURCE_AUTO_POSITION=1;
-- レプリケーションの開始
START REPLICA;
-- レプリケーションステータスの確認
SHOW REPLICA STATUS\G
-- *************************** 1. row ***************************
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Source_Host: 192.168.1.10
-- Source_Port: 3306
-- Source_Log_File: mysql-bin.000005
-- Read_Source_Log_Pos: 98765
-- Relay_Log_File: relay-bin.000003
-- Relay_Log_Pos: 4321
-- Relay_Source_Log_File: mysql-bin.000005
-- Exec_Source_Log_Pos: 98765
-- Relay_Log_Space: 10240
-- Seconds_Behind_Source: 0
-- Replica_SQL_Running_State: Slave has read all relay log;
-- waiting for more updates
レプリケーションの監視とトラブルシューティング
-- レプリカの遅延確認
SELECT
CHANNEL_NAME,
SERVICE_STATE,
LAST_ERROR_NUMBER,
LAST_ERROR_MESSAGE,
LAST_ERROR_TIMESTAMP
FROM performance_schema.replication_connection_status;
-- レプリカの遅延秒数
SELECT
CHANNEL_NAME,
COUNT_TRANSACTIONS_IN_QUEUE,
COUNT_TRANSACTIONS_CHECKED,
COUNT_CONFLICTS_DETECTED
FROM performance_schema.replication_group_member_stats;
-- エラーをスキップして続行 (緊急時のみ)
-- GTID モードの場合
SET GTID_NEXT='3E11FA47-71CA-11E1-9E33-C80AA9429562:101';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START REPLICA;
Semi-Synchronous Replication の設定
-- ソース側でプラグインの有効化
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 10000; -- 10秒タイムアウト
-- レプリカ側でプラグインの有効化
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;
-- ステータス確認
SHOW STATUS LIKE 'Rpl_semi_sync%';
-- +--------------------------------------------+-------+
-- | Variable_name | Value |
-- +--------------------------------------------+-------+
-- | Rpl_semi_sync_source_clients | 1 |
-- | Rpl_semi_sync_source_net_avg_wait_time | 1200 |
-- | Rpl_semi_sync_source_status | ON |
-- | Rpl_semi_sync_source_tx_avg_wait_time | 1300 |
-- | Rpl_semi_sync_source_yes_tx | 1234 |
-- +--------------------------------------------+-------+
2.6 InnoDB チューニング
バッファプールの最適化
-- バッファプールのヒット率確認
SELECT
FORMAT(((1 - (Innodb_buffer_pool_reads /
Innodb_buffer_pool_read_requests)) * 100), 2) AS hit_rate_pct
FROM (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_reads
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r, (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;
-- +---------------+
-- | hit_rate_pct |
-- +---------------+
-- | 99.99 |
-- +---------------+
-- ダーティページの比率確認
SELECT
ROUND(Innodb_buffer_pool_pages_dirty /
Innodb_buffer_pool_pages_total * 100, 2) AS dirty_pct
FROM (
SELECT
SUM(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'
THEN VARIABLE_VALUE END) AS Innodb_buffer_pool_pages_dirty,
SUM(CASE WHEN VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'
THEN VARIABLE_VALUE END) AS Innodb_buffer_pool_pages_total
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
'Innodb_buffer_pool_pages_dirty',
'Innodb_buffer_pool_pages_total'
)
) t;
InnoDB I/O チューニング
# /etc/my.cnf - InnoDB I/O 設定
# ネイティブ AIO の使用 (Linux)
innodb_use_native_aio = ON
# フラッシュ手法 (SSD 推奨設定)
innodb_flush_method = O_DIRECT_NO_FSYNC
# ダブルライトバッファ (データ損傷防止、SSD では無効化検討)
innodb_doublewrite = ON
# アダプティブフラッシュ
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 10
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 10
# ページクリーナースレッド数
innodb_page_cleaners = 4
# チェックポイントとログ
innodb_log_file_size = 2G
innodb_log_files_in_group = 2 # MySQL 8.0.30 以降は非推奨
# MySQL 8.0.30+ での redo log 設定
# innodb_redo_log_capacity = 4G
InnoDB の重要なパラメータ一覧
| パラメータ | デフォルト値 | 推奨設定 | 説明 |
|---|---|---|---|
innodb_buffer_pool_size | 128M | RAM の 70-80% | 最重要パラメータ |
innodb_buffer_pool_instances | 8 | RAM/1GB 個 | 並列アクセス改善 |
innodb_log_file_size | 48M | 1-4GB | REDO ログサイズ |
innodb_flush_log_at_trx_commit | 1 | 1 (安全性優先) | フラッシュタイミング |
innodb_flush_method | fsync | O_DIRECT | I/O 方式 |
innodb_io_capacity | 200 | 2000-10000 | I/O スループット |
innodb_read_io_threads | 4 | 8-16 | 読み取りスレッド数 |
innodb_write_io_threads | 4 | 8-16 | 書き込みスレッド数 |
innodb_lock_wait_timeout | 50 | 30-50 | ロック待機タイムアウト |
innodb_deadlock_detect | ON | ON | デッドロック検出 |
2.7 スロークエリログと分析
スロークエリログの設定と有効化
-- 動的に有効化
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1; -- 1秒以上のクエリを記録
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_slow_admin_statements = ON;
SET GLOBAL log_slow_extra = ON; -- MySQL 8.0.14+
-- 確認
SHOW VARIABLES LIKE 'slow%';
-- +---------------------+-----------------------------+
-- | Variable_name | Value |
-- +---------------------+-----------------------------+
-- | slow_launch_time | 2 |
-- | slow_query_log | ON |
-- | slow_query_log_file | /var/log/mysql/slow.log |
-- +---------------------+-----------------------------+
スロークエリログの内容例
# Time: 2026-04-10T12:34:56.789012Z
# User@Host: appuser[appuser] @ [192.168.1.100] Id: 12345
# Query_time: 5.234567 Lock_time: 0.000123
# Rows_sent: 1234 Rows_examined: 9876543
# Errno: 0 Killed: 0 Bytes_sent: 123456
# Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
use myapp;
SET timestamp=1712720096;
SELECT * FROM orders
WHERE status = 'pending'
AND created_at > '2026-01-01'
ORDER BY amount DESC;
mysqldumpslow によるスロークエリの集計
# 実行時間でソートして上位10件
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 出力例:
# Count: 245 Time=5.23s (1281s) Lock=0.00s (0s)
# Rows=1234.0 (302330), appuser@192.168.1.100
# SELECT * FROM orders WHERE status = 'S'
# AND created_at > 'S' ORDER BY amount DESC
# 出現回数でソート
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# クエリを正規化してカウント
mysqldumpslow -s at /var/log/mysql/slow.log
pt-query-digest による高度な分析
# インストール
sudo dnf install -y percona-toolkit
# スロークエリの分析
pt-query-digest /var/log/mysql/slow.log
# 出力例 (要約部分):
# # 1.1s user time, 20ms system time, 27.01M rss, 211.21M vsz
# # Current date: Thu Apr 10 12:00:00 2026
# # Hostname: db-server-01
# # Files: /var/log/mysql/slow.log
# # Overall: 5.91k total, 23 unique, 9.85 QPS, 0.01x concurrency
# # Time range: 2026-04-10 00:00:01 to 2026-04-10 00:10:00
# # Attribute total min max avg 95% stddev median
# # ============ ======= ======= ======= ======= ======= ======= =======
# # Exec time 10262s 10ms 21s 1734ms 4284ms 1828ms 1238ms
# # Lock time 34s 0 165ms 6ms 12ms 12ms 2ms
# # Rows sent 9.60M 0 68.98k 1.66k 6.83k 4.44k 395.65
# # Rows examine 87.23G 0 23.88M 15.08M 23.88M 8.73M 23.88M
# 特定の期間のみ分析
pt-query-digest \
--since '2026-04-10 00:00:00' \
--until '2026-04-10 06:00:00' \
/var/log/mysql/slow.log
# 結果をレポートファイルに出力
pt-query-digest /var/log/mysql/slow.log \
--output slowlog \
> /tmp/slow_report_$(date +%Y%m%d).txt
EXPLAIN による実行計画の分析
-- 実行計画の確認
EXPLAIN SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 100;
-- +----+-------+-------+--------+---------------------+------------------+
-- | id | type | table | key | rows | Extra |
-- +----+-------+-------+--------+------+---------------+
-- | 1 | ALL | o | NULL |1000k | Using where; |
-- | | | | | | Using filesort|
-- | 1 | ref | c | PRIMARY| 1 | NULL |
-- +----+-------+-------+--------+------+---------------+
-- インデックスの追加
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
ALTER TABLE orders ADD INDEX idx_amount (amount);
-- 再度 EXPLAIN で確認
EXPLAIN SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 100;
-- +----+-------+-------+--------------------+------+-----------+
-- | id | type | table | key | rows | Extra |
-- +----+-------+-------+--------------------+------+-----------+
-- | 1 | range | o | idx_status_created | 500 | Using ... |
-- | 1 | ref | c | PRIMARY | 1 | NULL |
-- +----+-------+-------+--------------------+------+-----------+
-- 詳細な EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending'\G
2.8 監視とモニタリング
Performance Schema の活用
-- 最も遅いクエリの Top 10
SELECT
DIGEST_TEXT,
COUNT_STAR AS executions,
ROUND(AVG_TIMER_WAIT/1000000000000, 3) AS avg_latency_sec,
ROUND(MAX_TIMER_WAIT/1000000000000, 3) AS max_latency_sec,
ROUND(SUM_TIMER_WAIT/1000000000000, 3) AS total_latency_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- テーブルごとの I/O 統計
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_FETCH,
SUM_TIMER_FETCH/1000000000000 AS fetch_sec,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('performance_schema', 'information_schema', 'mysql')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
-- ロック待機の確認
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
sys スキーマを使った監視
-- CPU を最も使っているステートメント
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;
-- インデックスを使っていないテーブル
SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 10;
-- 使われていないインデックス
SELECT * FROM sys.schema_unused_indexes LIMIT 20;
-- 重複したインデックス
SELECT * FROM sys.schema_redundant_indexes LIMIT 10;
-- ホストごとの接続統計
SELECT * FROM sys.host_summary;
-- テーブルサイズの確認
SELECT
table_schema AS `Database`,
table_name AS `Table`,
ROUND(data_length / 1024 / 1024, 2) AS `Data(MB)`,
ROUND(index_length / 1024 / 1024, 2) AS `Index(MB)`,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Total(MB)`
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY (data_length + index_length) DESC
LIMIT 20;
Prometheus + mysqld_exporter による監視
# mysqld_exporter のインストール
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter /usr/local/bin/
# 専用ユーザーの作成
mysql -u root -p << 'EOF'
CREATE USER 'exporter'@'localhost'
IDENTIFIED BY 'ExporterP@ss!' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
EOF
# 設定ファイル
cat > /etc/mysql/exporter.cnf << 'EOF'
[client]
user=exporter
password=ExporterP@ss!
EOF
chmod 600 /etc/mysql/exporter.cnf
# systemd サービス設定
cat > /etc/systemd/system/mysqld_exporter.service << 'EOF'
[Unit]
Description=MySQL Exporter
After=network.target
[Service]
User=mysql
ExecStart=/usr/local/bin/mysqld_exporter \
--config.my-cnf=/etc/mysql/exporter.cnf \
--collect.global_status \
--collect.global_variables \
--collect.info_schema.processlist \
--collect.info_schema.tables \
--collect.info_schema.innodb_metrics \
--collect.perf_schema.eventswaits \
--collect.perf_schema.file_events \
--collect.perf_schema.tableiowaits \
--collect.slave_status \
--web.listen-address=:9104
[Install]
WantedBy=multi-user.target
EOF
sudo systemctl enable --now mysqld_exporter
3. PostgreSQL 管理
3.1 インストールと初期設定
RHEL/AlmaLinux 9 へのインストール (PostgreSQL 16)
# PostgreSQL 公式リポジトリの追加
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# システムデフォルト PostgreSQL の無効化
sudo dnf -qy module disable postgresql
# インストール
sudo dnf install -y postgresql16-server postgresql16-contrib
# データベースクラスタの初期化
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
# サービス起動と自動起動設定
sudo systemctl enable --now postgresql-16
# 確認
sudo systemctl status postgresql-16
# ● postgresql-16.service - PostgreSQL 16 Database Server
# Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled)
# Active: active (running) since Thu 2026-04-10 12:00:00 JST
# Process: 1234 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir
# Main PID: 1235 (postmaster)
Ubuntu/Debian へのインストール (PostgreSQL 16)
# PostgreSQL APT リポジトリの追加
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
--fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
> /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install -y postgresql-16
# サービス確認
sudo systemctl status postgresql
データディレクトリのカスタマイズ
# カスタムデータディレクトリの準備
sudo mkdir -p /data/postgresql/16/main
sudo chown -R postgres:postgres /data/postgresql
sudo chmod 700 /data/postgresql/16/main
# SELinux コンテキスト設定 (RHEL 系)
sudo semanage fcontext -a -t postgresql_db_t "/data/postgresql(/.*)?"
sudo restorecon -Rv /data/postgresql
# postgres ユーザーで初期化
sudo -u postgres /usr/pgsql-16/bin/initdb \
--pgdata=/data/postgresql/16/main \
--encoding=UTF8 \
--locale=ja_JP.UTF-8 \
--auth-local=peer \
--auth-host=scram-sha-256
# /etc/systemd/system/postgresql-16.service.d/override.conf を作成
sudo mkdir -p /etc/systemd/system/postgresql-16.service.d/
cat << 'EOF' | sudo tee /etc/systemd/system/postgresql-16.service.d/override.conf
[Service]
Environment=PGDATA=/data/postgresql/16/main
EOF
sudo systemctl daemon-reload
sudo systemctl restart postgresql-16
psql の基本操作
# postgres ユーザーとして接続
sudo -u postgres psql
# 特定データベースへの接続
psql -h localhost -U appuser -d myapp
# パスワードファイルの設定 (~/.pgpass)
echo "localhost:5432:myapp:appuser:SecureP@ss!" >> ~/.pgpass
chmod 600 ~/.pgpass
# 便利な psql コマンド
\l -- データベース一覧
\c myapp -- データベースの切り替え
\dt -- テーブル一覧
\d orders -- テーブルの詳細
\du -- ロール一覧
\df -- 関数一覧
\x -- 拡張表示モードの切り替え
\timing on -- クエリ実行時間の表示
\i /path/to/script.sql -- SQLファイルの実行
\o /tmp/output.txt -- 出力をファイルへ
\q -- 終了
3.2 postgresql.conf の詳細
PostgreSQL の主要設定ファイルは $PGDATA/postgresql.conf です。設定変更後、pg_reload_conf() または pg_ctl reload で再読み込みが必要なパラメータと、再起動が必要なパラメータがあります。
本番環境向け postgresql.conf の包括的設定例
# $PGDATA/postgresql.conf
# ============================================================
# 接続設定
# ============================================================
listen_addresses = '*' # 全インターフェースでリッスン
port = 5432
max_connections = 200 # PgBouncer 使用時は少なく設定
superuser_reserved_connections = 3
# ============================================================
# メモリ設定
# ============================================================
# 共有バッファ: RAM の 25% (OS キャッシュも使うため)
shared_buffers = 4GB
# ワークメモリ: 複雑なクエリのソートやハッシュ結合に使用
# max_connections * work_mem が最大使用量 (×3-4倍の可能性あり)
work_mem = 64MB
# メンテナンス操作用メモリ (VACUUM, CREATE INDEX 等)
maintenance_work_mem = 512MB
# PostgreSQL 13+ の autovacuum ワーカーの最大メモリ
autovacuum_work_mem = -1 # maintenance_work_mem を使用
# Huge Pages の使用 (OS 設定も必要)
huge_pages = try
# 効果的なキャッシュサイズ (プランナの見積もり用、実際には確保しない)
effective_cache_size = 12GB # RAM の 75% 程度
# ============================================================
# WAL (Write-Ahead Log) 設定
# ============================================================
wal_level = replica # minimal/replica/logical
max_wal_size = 4GB
min_wal_size = 1GB
# WAL バッファサイズ
wal_buffers = 64MB # -1 で自動 (shared_buffers の 1/32)
# WAL の同期方式
synchronous_commit = on # off にすると高速化するがデータ損失リスク
wal_sync_method = fdatasync # fsync/fdatasync/open_sync/open_datasync
# チェックポイント設定
checkpoint_timeout = 15min # チェックポイントの最大間隔
checkpoint_completion_target = 0.9 # チェックポイント完了の目標時間比率
# WAL アーカイブ設定
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 300 # 最大アーカイブ間隔 (秒)
# ============================================================
# レプリケーション設定
# ============================================================
max_wal_senders = 10 # WAL 送信プロセスの最大数
wal_keep_size = 1GB # レプリカのために保持する WAL サイズ
max_replication_slots = 10
# 同期レプリケーション
# synchronous_standby_names = 'replica1'
# ============================================================
# クエリプランナー設定
# ============================================================
# ランダム I/O のコスト (SSD では 1.1-2.0 程度が適切)
random_page_cost = 1.5 # HDD: 4.0, SSD: 1.1-2.0
# シーケンシャル I/O のコスト
seq_page_cost = 1.0
# CPU コスト
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
# 並列クエリ設定
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 16
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
# JIT コンパイル (PostgreSQL 11+)
jit = on
jit_above_cost = 100000
# ============================================================
# ログ設定
# ============================================================
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = off
# ログレベル
log_min_messages = warning
log_min_error_statement = error
# スロークエリログ
log_min_duration_statement = 1000 # 1000ms (1秒) 以上のクエリを記録
# その他のログ設定
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0 # 一時ファイルを全て記録 (-1 で無効)
log_autovacuum_min_duration = 1000 # 1秒以上の autovacuum を記録
# ログフォーマット
log_line_prefix = '%m [%p] %q%u@%d '
# %m: タイムスタンプ, %p: PID, %u: ユーザー名, %d: データベース名
# ============================================================
# Autovacuum 設定
# ============================================================
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 1min
# VACUUM のトリガー閾値
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05 # テーブルの 5% が対象
# ANALYZE のトリガー閾値
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02 # テーブルの 2% が対象
# autovacuum のスロットリング
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 400 # 高い値 = より積極的な VACUUM
# ============================================================
# 接続プーリング
# ============================================================
# PgBouncer 使用時は接続数を減らす
# max_connections = 100
# ============================================================
# 統計情報コレクター
# ============================================================
track_activities = on
track_counts = on
track_io_timing = on # I/O 統計 (わずかなオーバーヘッドあり)
track_wal_io_timing = on # WAL I/O 統計
track_functions = all # none/pl/all
# ============================================================
# 拡張機能
# ============================================================
shared_preload_libraries = 'pg_stat_statements,auto_explain,pg_prewarm'
# pg_stat_statements 設定
pg_stat_statements.max = 10000
pg_stat_statements.track = all
# auto_explain 設定
auto_explain.log_min_duration = 5000 # 5秒以上で実行計画をログに出力
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_format = json
設定の反映方法
# 設定ファイルの再読み込み (再起動不要なパラメータ)
sudo -u postgres pg_ctl reload -D /var/lib/pgsql/16/data
# または SQL から
sudo -u postgres psql -c "SELECT pg_reload_conf();"
# 出力:
# pg_reload_conf
# ----------------
# t
# (1 row)
# 再起動が必要なパラメータの確認
SELECT name, setting, unit, context
FROM pg_settings
WHERE context IN ('postmaster', 'sighup')
AND name IN ('shared_buffers', 'max_connections', 'wal_level')
ORDER BY context, name;
-- +-----------------+---------+------+------------+
-- | name | setting | unit | context |
-- +-----------------+---------+------+------------+
-- | max_connections | 200 | | postmaster |
-- | shared_buffers | 524288 | 8kB | postmaster |
-- | wal_level | replica | | postmaster |
-- +-----------------+---------+------+------------+
# 設定変更が反映済みかを確認
SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;
3.3 pg_hba.conf 認証設定
pg_hba.conf は PostgreSQL のクライアント認証を制御します。各行の書式は:
# TYPE DATABASE USER ADDRESS METHOD [OPTIONS]
pg_hba.conf の設定例
# $PGDATA/pg_hba.conf
# ============================================================
# 書式: TYPE DATABASE USER ADDRESS METHOD [OPTIONS]
# ============================================================
# TYPE:
# local - Unix ドメインソケット
# host - TCP/IP (SSL あり・なし両方)
# hostssl - SSL のみ
# hostnossl - SSL なし
# hostgssenc - GSSAPI 暗号化のみ
# METHOD:
# trust - パスワードなしで接続可 (非推奨)
# reject - 接続を拒否
# scram-sha-256 - SCRAM-SHA-256 認証 (推奨)
# md5 - MD5 パスワード認証 (非推奨)
# peer - OS のユーザー名と照合 (ローカルのみ)
# ident - ident サーバーで照合
# gss - GSSAPI/Kerberos
# ldap - LDAP サーバーで認証
# cert - SSL クライアント証明書
# ============================================================
# ローカル接続
# ============================================================
# postgres ユーザーはローカルから peer 認証
local all postgres peer
# ローカルのすべてのユーザーは SCRAM-SHA-256
local all all scram-sha-256
# ============================================================
# ループバック接続
# ============================================================
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
# ============================================================
# レプリケーション接続
# ============================================================
host replication repl 192.168.1.0/24 scram-sha-256
# ============================================================
# アプリケーション接続
# ============================================================
# 特定の IP からアプリユーザーのみ myapp データベースに接続可
host myapp appuser 192.168.2.0/24 scram-sha-256
# 読み取り専用ユーザーはレポートサーバーから接続可
host myapp readonly 192.168.3.10/32 scram-sha-256
# ============================================================
# 管理者接続 (SSL 必須)
# ============================================================
hostssl all admin 10.0.0.0/8 scram-sha-256
# ============================================================
# その他は全て拒否
# ============================================================
host all all 0.0.0.0/0 reject
認証方式の設定と確認
# pg_hba.conf の変更後に再読み込み
sudo -u postgres psql -c "SELECT pg_reload_conf();"
# 現在の接続状況確認
sudo -u postgres psql -c "
SELECT
pid,
usename,
application_name,
client_addr,
state,
wait_event_type,
wait_event,
left(query, 50) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY state, wait_event_type;
"
# 接続数の統計
sudo -u postgres psql -c "
SELECT
datname,
numbackends,
max_conn_config
FROM pg_stat_database
JOIN (SELECT setting::int AS max_conn_config FROM pg_settings
WHERE name = 'max_connections') mc ON true
ORDER BY numbackends DESC;
"
3.4 ロールと権限管理
PostgreSQL では「ユーザー」と「グループ」の区別がなく、すべてが「ロール」として扱われます。
ロールの作成と管理
-- ログイン可能なロール (ユーザー) の作成
CREATE ROLE appuser
WITH LOGIN
PASSWORD 'SecureP@ss!'
CONNECTION LIMIT 50;
-- スーパーユーザーの作成 (慎重に)
CREATE ROLE dbadmin
WITH LOGIN SUPERUSER PASSWORD 'AdminP@ss!';
-- 読み取り専用ユーザー
CREATE ROLE readonly
WITH LOGIN PASSWORD 'ReadOnlyP@ss!'
VALID UNTIL '2027-04-10';
-- グループロールの作成 (LOGIN 権限なし)
CREATE ROLE app_developers;
CREATE ROLE app_operators;
CREATE ROLE data_analysts;
-- ロールへのメンバー追加
GRANT app_developers TO alice, bob;
GRANT app_operators TO carol;
GRANT data_analysts TO dave, eve;
-- ロールの確認
\du
-- List of roles
-- Role name | Attributes | Member of
-- -------------+------------------------+--------------------
-- alice | Cannot login | {app_developers}
-- app_developers| Cannot login | {}
-- appuser | 50 connections | {}
-- dbadmin | Superuser | {}
-- postgres | Superuser, Create role | {}
-- readonly | Password valid until 2027-04-10 | {}
-- より詳細な確認
SELECT
r.rolname,
r.rolsuper,
r.rolinherit,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin,
r.rolreplication,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON m.roleid = b.oid
WHERE m.member = r.oid) AS member_of
FROM pg_catalog.pg_roles r
ORDER BY r.rolname;
データベースと スキーマの権限設定
-- データベースの作成
CREATE DATABASE myapp
OWNER appuser
ENCODING 'UTF8'
LC_COLLATE 'ja_JP.UTF-8'
LC_CTYPE 'ja_JP.UTF-8'
TEMPLATE template0;
-- データベースへの接続権限
GRANT CONNECT ON DATABASE myapp TO app_developers, app_operators;
GRANT CONNECT ON DATABASE myapp TO readonly;
-- スキーマの作成と権限
\c myapp
CREATE SCHEMA app AUTHORIZATION appuser;
-- スキーマの使用権限
GRANT USAGE ON SCHEMA app TO app_developers, app_operators, readonly;
-- テーブルへの権限付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app
TO app_developers;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app
TO app_operators;
GRANT SELECT ON ALL TABLES IN SCHEMA app
TO readonly, data_analysts;
-- シーケンスへの権限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app
TO app_developers, app_operators;
-- 将来作成されるオブジェクトへのデフォルト権限
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_developers;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_developers;
-- 特定テーブルへの行レベルセキュリティ (RLS)
ALTER TABLE app.sensitive_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_isolation ON app.sensitive_data
USING (owner_id = current_user_id());
-- ポリシーの確認
SELECT * FROM pg_policies WHERE tablename = 'sensitive_data';
権限の確認と監査
-- テーブル権限の確認
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders' AND table_schema = 'app'
ORDER BY grantee, privilege_type;
-- スキーマ権限の確認
\dn+ app
-- List of schemas
-- Name | Owner | Access privileges
-- -----+---------+--------------------------------
-- app | appuser | appuser=UC/appuser +
-- | | app_developers=UC/appuser +
-- | | app_operators=UC/appuser +
-- | | readonly=U/appuser
-- ロールのメンバーシップ確認
SELECT
r.rolname AS role,
m.rolname AS member
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
ORDER BY r.rolname, m.rolname;
3.5 バックアップと復元
pg_dump による論理バックアップ
# 単一データベースのバックアップ (カスタム形式 - 推奨)
pg_dump -U postgres -Fc -v \
-f /backup/myapp_$(date +%Y%m%d_%H%M%S).dump \
myapp
# テキスト形式 (可読性が高いが圧縮率低)
pg_dump -U postgres -v \
-f /backup/myapp_$(date +%Y%m%d).sql \
myapp
# 圧縮テキスト形式
pg_dump -U postgres -v myapp | gzip > /backup/myapp_$(date +%Y%m%d).sql.gz
# 並列バックアップ (ディレクトリ形式のみ)
pg_dump -U postgres -Fd -j 4 -v \
-f /backup/myapp_parallel_$(date +%Y%m%d) \
myapp
# 特定テーブルのみ
pg_dump -U postgres -t 'app.orders' -t 'app.customers' \
-f /backup/orders_customers.dump -Fc myapp
# スキーマのみ
pg_dump -U postgres --schema-only \
-f /backup/myapp_schema.sql myapp
# データのみ
pg_dump -U postgres --data-only \
-f /backup/myapp_data.sql myapp
# 全データベースのバックアップ (グローバルオブジェクト含む)
pg_dumpall -U postgres \
-f /backup/all_$(date +%Y%m%d).sql
# ロールとテーブルスペースのみ
pg_dumpall -U postgres --globals-only \
-f /backup/globals_$(date +%Y%m%d).sql
# バックアップの確認
pg_restore --list /backup/myapp_20260410.dump | head -30
pg_dump バックアップの復元
# カスタム形式の復元
pg_restore -U postgres -d myapp -v \
/backup/myapp_20260410.dump
# 新しいデータベースに復元
createdb -U postgres myapp_restored
pg_restore -U postgres -d myapp_restored -v \
/backup/myapp_20260410.dump
# 特定テーブルのみ復元
pg_restore -U postgres -d myapp \
-t orders /backup/myapp_20260410.dump
# テキスト形式の復元
psql -U postgres -d myapp < /backup/myapp_20260410.sql
# 並列復元 (-j はジョブ数)
pg_restore -U postgres -d myapp -j 4 -v \
/backup/myapp_parallel_20260410/
# 全データベースの復元
psql -U postgres < /backup/all_20260410.sql
pg_basebackup による物理バックアップ
# フルバックアップの実行
pg_basebackup -h localhost -U repl \
-D /backup/base/$(date +%Y%m%d) \
-Ft -z -Xs -P -v
# オプション説明:
# -Ft: tar 形式
# -z: gzip 圧縮
# -Xs: WAL をストリーミングで含める
# -P: 進捗表示
# 出力例:
# pg_basebackup: initiating base backup, waiting for checkpoint to complete
# pg_basebackup: checkpoint completed
# pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
# pg_basebackup: starting background WAL receiver
# 24576/24576 kB (100%), 1/1 tablespace
# pg_basebackup: write-ahead log end point: 0/2000100
# pg_basebackup: waiting for background process to finish streaming ...
# pg_basebackup: syncing data to disk ...
# pg_basebackup: renaming backup_manifest.tmp to backup_manifest
# pg_basebackup: base backup completed
# レプリケーションスロットを使ったバックアップ
pg_basebackup -h localhost -U repl \
-D /backup/base/$(date +%Y%m%d) \
-Ft -z -Xs -S slot_name \
-P -v
# バックアップの確認
ls -la /backup/base/20260410/
# 出力例:
# total 4096K
# drwx------ 2 postgres postgres 4096 Apr 10 12:00 .
# drwx------ 3 postgres postgres 4096 Apr 10 11:00 ..
# -rw------- 1 postgres postgres 2048K Apr 10 12:00 base.tar.gz
# -rw------- 1 postgres postgres 87 Apr 10 12:00 backup_manifest
# -rw------- 1 postgres postgres 512K Apr 10 12:00 pg_wal.tar.gz
PITR (ポイントインタイムリカバリ)
# 1. バックアップを復元先へ展開
mkdir -p /data/postgresql_recovered
cd /data/postgresql_recovered
tar -xzf /backup/base/20260410/base.tar.gz
tar -xzf /backup/base/20260410/pg_wal.tar.gz -C pg_wal/
# 2. WAL アーカイブのセットアップ
mkdir -p /archive_recovered
cp /archive/* /archive_recovered/ # アーカイブされた WAL をコピー
# 3. recovery.conf (PostgreSQL 12 以前) または postgresql.conf に設定
# PostgreSQL 12 以降は postgresql.conf に直接記述
cat >> /data/postgresql_recovered/postgresql.conf << 'EOF'
# PITR 設定
restore_command = 'cp /archive_recovered/%f %p'
# 特定時刻まで復元
recovery_target_time = '2026-04-10 10:30:00'
# タイムライン設定
recovery_target_timeline = 'latest'
# ターゲット到達後の動作
recovery_target_action = 'promote'
EOF
# 4. recovery signal ファイルの作成 (PostgreSQL 12+)
touch /data/postgresql_recovered/recovery.signal
# 5. 復元の開始
sudo chown -R postgres:postgres /data/postgresql_recovered
sudo -u postgres /usr/pgsql-16/bin/pg_ctl start \
-D /data/postgresql_recovered \
-l /tmp/recovery.log
# 6. 復元の進捗確認
tail -f /tmp/recovery.log
# 出力例:
# LOG: starting point-in-time recovery to 2026-04-10 10:30:00 JST
# LOG: restored log file "000000010000000000000001" from archive
# ...
# LOG: recovery stopping before commit of transaction 1234,
# time 2026-04-10 10:30:05 JST
# LOG: pausing at the end of recovery
# HINT: Execute pg_wal_replay_resume() to promote.
3.6 ストリーミングレプリケーション
プライマリサーバーの設定
# $PGDATA/postgresql.conf (プライマリ)
# WAL レベルをレプリカに設定 (再起動必要)
wal_level = replica
# WAL 送信プロセスの最大数
max_wal_senders = 10
# レプリカのために保持する WAL の最小サイズ
wal_keep_size = 1GB
# レプリケーションスロット
max_replication_slots = 10
# Hot Standby への統計情報フィードバック (クエリキャンセル防止)
hot_standby_feedback = on
# 同期レプリケーション (任意)
# synchronous_standby_names = 'FIRST 1 (replica1, replica2)'
# synchronous_commit = on
# pg_hba.conf にレプリケーション接続を追加
echo "host replication repl 192.168.1.0/24 scram-sha-256" \
>> $PGDATA/pg_hba.conf
# レプリケーションユーザーの作成
sudo -u postgres psql << 'EOF'
CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD 'ReplP@ss123!';
EOF
# 設定の再読み込み
sudo -u postgres pg_ctl reload -D $PGDATA
# レプリケーションスロットの作成 (任意)
sudo -u postgres psql -c "
SELECT pg_create_physical_replication_slot('replica1_slot');
"
-- 出力:
-- pg_create_physical_replication_slot
-- -------------------------------------
-- (replica1_slot,)
スタンバイサーバーの設定
# 1. プライマリからベースバックアップを取得
sudo -u postgres pg_basebackup \
-h 192.168.1.10 \
-U repl \
-D /var/lib/pgsql/16/data \
-P -Xs -R -v
# -R: recovery.conf の自動生成 (PostgreSQL 12+は standby.signal +
# postgresql.auto.conf への設定追加)
# 出力例:
# pg_basebackup: initiating base backup, waiting for checkpoint to complete
# pg_basebackup: checkpoint completed
# pg_basebackup: write-ahead log start point: 0/5000028 on timeline 1
# pg_basebackup: starting background WAL receiver
# 49152/49152 kB (100%), 1/1 tablespace
# pg_basebackup: write-ahead log end point: 0/5000138
# pg_basebackup: waiting for background process to finish streaming ...
# pg_basebackup: syncing data to disk ...
# pg_basebackup: base backup completed
# pg_basebackup -R によって追加された設定確認
cat /var/lib/pgsql/16/data/postgresql.auto.conf
# primary_conninfo = 'host=192.168.1.10 port=5432 user=repl
# password=ReplP@ss123!'
# standby.signal ファイルの存在確認
ls /var/lib/pgsql/16/data/standby.signal
# 2. スタンバイ固有の設定
cat >> /var/lib/pgsql/16/data/postgresql.conf << 'EOF'
# スタンバイ設定
hot_standby = on # 読み取りクエリを許可
hot_standby_feedback = on
wal_receiver_status_interval = 10s
recovery_min_apply_delay = 0 # 遅延レプリケーションの場合は設定
EOF
# 3. スタンバイの起動
sudo systemctl start postgresql-16
レプリケーションの監視
-- プライマリでのレプリケーション状態確認
SELECT
pid,
usename,
application_name,
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
write_lag,
flush_lag,
replay_lag,
sync_state
FROM pg_stat_replication;
-- +-----+---------+----------+---------------+----------+----------+
-- | pid | usename | app_name | client_addr | state | sync_... |
-- +-----+---------+----------+---------------+----------+----------+
-- |5678 | repl | replica1 | 192.168.1.11 |streaming | async |
-- +-----+---------+----------+---------------+----------+----------+
-- スタンバイでの受信状態確認
SELECT * FROM pg_stat_wal_receiver\G
-- レプリケーション遅延の確認
SELECT
now() - pg_last_xact_replay_timestamp() AS replication_delay,
pg_is_in_recovery() AS is_standby;
-- +--------------------+------------+
-- | replication_delay | is_standby |
-- +--------------------+------------+
-- | 00:00:00.123456 | t |
-- +--------------------+------------+
-- LSN 差による遅延確認 (プライマリで実行)
SELECT
application_name,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag
FROM pg_stat_replication;
フェイルオーバーとフェイルバック
# 手動フェイルオーバー: スタンバイをプライマリに昇格
sudo -u postgres pg_ctl promote -D /var/lib/pgsql/16/data
# 出力:
# waiting for server to promote.... done
# server promoted
# または SQL から
sudo -u postgres psql -c "SELECT pg_promote();"
# フェイルバック手順:
# 1. 旧プライマリを新スタンバイとして再設定
# 2. pg_rewind を使って差分を同期
sudo -u postgres pg_rewind \
--target-pgdata=/var/lib/pgsql/16/data \
--source-server='host=192.168.1.11 user=repl dbname=postgres' \
-P
# 3. standby.signal の作成と primary_conninfo の更新
touch /var/lib/pgsql/16/data/standby.signal
cat >> /var/lib/pgsql/16/data/postgresql.auto.conf << 'EOF'
primary_conninfo = 'host=192.168.1.11 port=5432 user=repl'
EOF
# 4. 旧プライマリをスタンバイとして起動
sudo systemctl start postgresql-16
Patroni による HA クラスター (概要)
# /etc/patroni/config.yml
scope: postgres-cluster
namespace: /service/
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.10:8008
etcd3:
hosts:
- etcd1:2379
- etcd2:2379
- etcd3:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576 # 1MB
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.10:5432
data_dir: /var/lib/pgsql/16/data
bin_dir: /usr/pgsql-16/bin
parameters:
wal_level: replica
max_wal_senders: 10
max_replication_slots: 10
hot_standby: on
3.7 WAL 管理
WAL アーカイブの設定
# postgresql.conf
# WAL アーカイブの有効化
archive_mode = on
# アーカイブコマンド (ローカルディスクへ)
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# WAL-G を使ったアーカイブ (S3 等への外部ストレージ)
# archive_command = 'wal-g wal-push %p'
# restore_command = 'wal-g wal-fetch %f %p'
# 定期的なアーカイブの強制 (アクティビティが少ない場合)
archive_timeout = 300 # 5分ごとにアーカイブを強制
# WAL セグメントの手動切り替えとアーカイブの強制
sudo -u postgres psql -c "SELECT pg_switch_wal();"
# 出力:
# pg_switch_wal
# ---------------
# 0/5000168
# (1 row)
# 現在の WAL 位置確認
sudo -u postgres psql -c "SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());"
# +--------------------+---------------------------+
# | pg_current_wal_lsn | pg_walfile_name |
# +--------------------+---------------------------+
# | 0/50001A0 | 000000010000000000000005 |
# +--------------------+---------------------------+
# アーカイブの状態確認
sudo -u postgres psql -c "SELECT * FROM pg_stat_archiver;"
# +---------------+---------------+-------------------+---+-----+
# | archived_count| last_archived | last_failed_time | ..| ... |
# +---------------+---------------+-------------------+---+-----+
# | 1234 | 000000...0005 | NULL | | |
# +---------------+---------------+-------------------+---+-----+
# WAL ファイルの一覧
ls -la $PGDATA/pg_wal/ | head -20
# pg_waldump で WAL の内容を確認
/usr/pgsql-16/bin/pg_waldump \
/var/lib/pgsql/16/data/pg_wal/000000010000000000000001 \
| head -30
# rmgr: Heap len (rec/tot): 59/ 59, tx: 123,
# lsn: 0/01234567, prev 0/01234500, desc: INSERT off 1, blkref #0: rel 1663/16384/1259
WAL ファイルの管理
# pg_archivecleanup で不要な WAL を削除
# (PITR 不要なアーカイブを削除する場合)
/usr/pgsql-16/bin/pg_archivecleanup /archive 000000010000000000000010
# バックアップ保持ポリシーの例 (スクリプト)
#!/bin/bash
ARCHIVE_DIR=/archive
KEEP_DAYS=7
# 7日以上前のアーカイブを削除
find $ARCHIVE_DIR -name "*.gz" -mtime +$KEEP_DAYS -delete
# 最新のバックアップ以前の WAL を削除
LATEST_BACKUP=$(ls -t /backup/base | head -1)
if [ -f "/backup/base/$LATEST_BACKUP/backup_manifest" ]; then
START_WAL=$(pg_restore --list /backup/base/$LATEST_BACKUP/backup_manifest \
| grep "start WAL" | awk '{print $NF}')
pg_archivecleanup $ARCHIVE_DIR $START_WAL
fi
3.8 VACUUM / ANALYZE
PostgreSQL の MVCC 実装では、更新・削除されたタプルが物理的に残ります (デッドタプル)。VACUUM はこれを回収してスペースを再利用可能にします。
VACUUM の種類と使い方
-- 通常の VACUUM (デッドタプルの回収、OS へは返却しない)
VACUUM app.orders;
-- VERBOSE オプションで詳細表示
VACUUM VERBOSE app.orders;
-- INFO: vacuuming "app.orders"
-- INFO: scanned index "orders_pkey" to remove 12345 row versions
-- INFO: "orders": removed 12345 row versions in 456 pages
-- INFO: index "orders_pkey" now contains 987654 row versions in 4567 pages
-- INFO: "orders": found 12345 removable, 987654 nonremovable row versions
-- in 456 out of 10000 pages
-- DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1234567
-- There were 0 unused item identifiers.
-- Skipped 0 pages due to buffer pins, 0 frozen pages.
-- 0 pages are entirely empty.
-- CPU: user: 0.12 s, system: 0.03 s, elapsed: 0.45 s.
-- VACUUM FULL (テーブルを完全に書き直し、OS へスペース返却)
-- 注意: 排他ロックが必要で長時間かかる。通常は不要
VACUUM FULL app.orders;
-- VACUUM ANALYZE (VACUUM + 統計情報の更新)
VACUUM ANALYZE app.orders;
-- データベース全体の VACUUM
VACUUM;
-- FREEZE オプション (トランザクション ID の周回防止)
VACUUM FREEZE app.orders;
テーブルの肥大化確認
-- デッドタプルの割合確認
SELECT
relname,
n_live_tup,
n_dead_tup,
ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2)
AS dead_tup_pct,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;
-- +---------+------------+-----------+-----------+
-- | relname | n_live_tup | n_dead_tup| dead_tup% |
-- +---------+------------+-----------+-----------+
-- | orders | 1000000 | 50000 | 4.76 |
-- +---------+------------+-----------+-----------+
-- テーブルの物理サイズとデッドタプルによる膨張
SELECT
relname,
pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
pg_size_pretty(pg_relation_size(oid)) AS table_size,
pg_size_pretty(pg_indexes_size(oid)) AS indexes_size
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'app')
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;
-- Autovacuum の実行状況確認
SELECT
schemaname,
relname,
last_autovacuum,
autovacuum_count,
last_autoanalyze,
autoanalyze_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST
LIMIT 20;
ANALYZE と統計情報
-- ANALYZE の実行 (クエリプランナー用統計情報の更新)
ANALYZE app.orders;
ANALYZE VERBOSE app.orders;
-- 統計情報の確認
SELECT
tablename,
attname,
n_distinct,
correlation
FROM pg_stats
WHERE tablename = 'orders'
AND schemaname = 'app'
ORDER BY attname;
-- +---------+-----------+-------------+-------------+
-- |tablename| attname | n_distinct | correlation |
-- +---------+-----------+-------------+-------------+
-- | orders | amount | -0.95 | 0.123 |
-- | orders | status | 5 | 0.789 |
-- | orders | created_at| -1 | 0.995 |
-- +---------+-----------+-------------+-------------+
-- 列の統計サンプル数を増やす (精度向上)
ALTER TABLE app.orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE app.orders(status);
-- トランザクション ID の周回防止の確認
SELECT
datname,
age(datfrozenxid) AS xid_age,
(2^31 - age(datfrozenxid)) AS xids_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- +---------+----------+----------------+
-- | datname | xid_age | xids_remaining |
-- +---------+----------+----------------+
-- | myapp | 45678901 | 2101308747 |
-- | postgres| 12345 | 2147471303 |
-- +---------+----------+----------------+
-- 注: xid_age が 2^31 (約21億) に近づいたら警告!
Autovacuum のチューニング
# postgresql.conf - テーブル別の autovacuum 設定は ALTER TABLE で行う
# グローバル設定
autovacuum_vacuum_scale_factor = 0.02 # 2%
autovacuum_analyze_scale_factor = 0.01 # 1%
autovacuum_vacuum_cost_delay = 2ms # I/O スロットリング
autovacuum_vacuum_cost_limit = 400
-- 特定テーブルの autovacuum をチューニング (大きなテーブル向け)
ALTER TABLE app.orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- 1% に変更
autovacuum_vacuum_threshold = 100,
autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 10, -- スロットリングを緩める
autovacuum_vacuum_cost_limit = 800
);
-- 現在実行中の autovacuum を確認
SELECT
pid,
datname,
relid::regclass AS table_name,
phase,
heap_blks_scanned,
heap_blks_total,
ROUND(heap_blks_scanned::numeric / NULLIF(heap_blks_total, 0) * 100, 1) AS pct
FROM pg_stat_progress_vacuum;
3.9 PgBouncer コネクションプーリング
PostgreSQL はプロセスベースアーキテクチャのため、多数の接続は大量のメモリを消費します。PgBouncer はコネクションプーリングによりこの問題を解決します。
PgBouncer のインストールと設定
# インストール
sudo dnf install -y pgbouncer # RHEL 系
sudo apt install -y pgbouncer # Debian/Ubuntu 系
pgbouncer.ini の設定
# /etc/pgbouncer/pgbouncer.ini
[databases]
# データベースセクション: 仮想DB名 = 接続先の設定
myapp = host=127.0.0.1 port=5432 dbname=myapp
myapp_ro = host=192.168.1.11 port=5432 dbname=myapp # スタンバイ向け
* = host=127.0.0.1 port=5432 # ワイルドカード (全DBに対応)
[pgbouncer]
# ============================================================
# 接続設定
# ============================================================
listen_addr = 0.0.0.0
listen_port = 6432
# Unix ドメインソケット
unix_socket_dir = /var/run/postgresql
# 認証ファイル
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
# ============================================================
# プーリングモード
# ============================================================
# session: クライアントが切断するまでサーバー接続を保持
# transaction:トランザクション終了時にサーバー接続を解放 (推奨)
# statement: ステートメント終了時に解放 (制限あり)
pool_mode = transaction
# ============================================================
# 接続プールサイズ
# ============================================================
# デフォルトのプールサイズ (データベース+ユーザーごと)
default_pool_size = 25
# 最大クライアント接続数
max_client_conn = 1000
# 最小プールサイズ (常時維持)
min_pool_size = 5
# 予備接続 (過負荷時用)
reserve_pool_size = 5
reserve_pool_timeout = 5
# ============================================================
# タイムアウト設定
# ============================================================
# サーバー接続の再利用を待つタイムアウト
pool_timeout = 30
# アイドルサーバー接続のタイムアウト
server_idle_timeout = 600
# クライアント接続のタイムアウト
client_idle_timeout = 0
# クライアントのログインタイムアウト
client_login_timeout = 60
# ============================================================
# ログ設定
# ============================================================
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
# ============================================================
# 管理設定
# ============================================================
admin_users = postgres, pgbouncer_admin
stats_users = pgbouncer_stats
ユーザーリストの設定
# userlist.txt の形式: "username" "scram-sha-256-hash"
# パスワードハッシュの取得
sudo -u postgres psql -t -c "
SELECT '\"' || rolname || '\" \"' || rolpassword || '\"'
FROM pg_authid
WHERE rolcanlogin;
" > /etc/pgbouncer/userlist.txt
chmod 640 /etc/pgbouncer/userlist.txt
chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
PgBouncer の起動と管理
# サービスの起動
sudo systemctl enable --now pgbouncer
# 管理コンソールへの接続
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
# 管理コマンド
SHOW POOLS; -- プール状態の確認
SHOW CLIENTS; -- クライアント接続一覧
SHOW SERVERS; -- サーバー接続一覧
SHOW STATS; -- 統計情報
SHOW CONFIG; -- 設定確認
RELOAD; -- 設定の再読み込み
SUSPEND; -- 接続の一時停止
RESUME; -- 接続の再開
SHUTDOWN; -- シャットダウン
# SHOW POOLS の出力例
SHOW POOLS;
-- +-------+---------+---------+-------+--------+-------+--------+----+
-- |database| user |cl_active|cl_wait|sv_active|sv_idle|sv_login|....|
-- +-------+---------+---------+-------+--------+-------+--------+----+
-- |myapp |appuser | 10 | 0 | 10 | 15 | 0 | |
-- |myapp |readonly | 2 | 0 | 2 | 3 | 0 | |
-- +-------+---------+---------+-------+--------+-------+--------+----+
# SHOW STATS の出力例
SHOW STATS;
-- +--------+-------------+------------+----------------+--+
-- |database|total_requests|total_sent |total_received |..|
-- +--------+-------------+------------+----------------+--+
-- |myapp | 125678 | 1234567890 | 9876543210 | |
-- +--------+-------------+------------+----------------+--+
3.10 監視とモニタリング
pg_stat_statements の活用
-- 拡張機能の有効化 (postgresql.conf に shared_preload_libraries 設定後)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 最も時間のかかるクエリ Top 10
SELECT
LEFT(query, 100) AS query,
calls,
ROUND(total_exec_time::numeric / 1000, 2) AS total_sec,
ROUND(mean_exec_time::numeric / 1000, 4) AS mean_sec,
ROUND(stddev_exec_time::numeric / 1000, 4) AS stddev_sec,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- キャッシュヒット率の確認
SELECT
sum(blks_hit) AS cache_hits,
sum(blks_read) AS disk_reads,
ROUND(sum(blks_hit)::numeric /
NULLIF(sum(blks_hit) + sum(blks_read), 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');
-- +------------+------------+-----------------+
-- | cache_hits | disk_reads | cache_hit_ratio |
-- +------------+------------+-----------------+
-- | 98765432 | 12345 | 99.99 |
-- +------------+------------+-----------------+
-- データベース統計
SELECT
datname,
numbackends AS connections,
xact_commit AS commits,
xact_rollback AS rollbacks,
blks_hit AS cache_hits,
blks_read AS disk_reads,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
deadlocks,
conflicts
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY numbackends DESC;
Prometheus + postgres_exporter の設定
# postgres_exporter のインストール
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xzf postgres_exporter-0.15.0.linux-amd64.tar.gz
sudo mv postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/
# 監視用ユーザーの作成
sudo -u postgres psql << 'EOF'
CREATE ROLE postgres_exporter LOGIN PASSWORD 'ExporterP@ss!';
GRANT pg_monitor TO postgres_exporter;
GRANT SELECT ON pg_stat_statements TO postgres_exporter;
EOF
# 環境変数設定ファイル
cat > /etc/postgres_exporter.env << 'EOF'
DATA_SOURCE_NAME="postgresql://postgres_exporter:ExporterP@ss!@localhost:5432/postgres?sslmode=disable"
EOF
chmod 600 /etc/postgres_exporter.env
# systemd サービス
cat > /etc/systemd/system/postgres_exporter.service << 'EOF'
[Unit]
Description=PostgreSQL Exporter
After=network.target
[Service]
User=postgres
EnvironmentFile=/etc/postgres_exporter.env
ExecStart=/usr/local/bin/postgres_exporter \
--collector.stat_bgwriter \
--collector.stat_database \
--collector.stat_user_tables \
--collector.stat_statements \
--web.listen-address=:9187
[Install]
WantedBy=multi-user.target
EOF
sudo systemctl enable --now postgres_exporter
重要な監視メトリクスと閾値
| メトリクス | 正常値 | 警告閾値 | 危険閾値 |
|---|---|---|---|
| キャッシュヒット率 | > 99% | < 95% | < 90% |
| デッドタプル割合 | < 5% | > 10% | > 20% |
| レプリケーション遅延 | < 1秒 | > 30秒 | > 5分 |
| ロック待機数 | 0 | > 5 | > 20 |
| アクティブ接続数 | < 70% max | > 80% max | > 90% max |
| XID age | < 1億 | > 15億 | > 19億 |
| チェックポイント間隔 | 設定値以内 | > 2倍 | > 5倍 |
4. MySQL vs PostgreSQL 比較
4.1 アーキテクチャの比較
MySQL アーキテクチャ:
┌─────────────────────────────────────┐
│ Connection Layer │
│ Thread per connection (共有スレッド) │
│ Connection Pool: ProxySQL 等 │
└─────────────────┬───────────────────┘
│
┌─────────────────▼───────────────────┐
│ SQL Layer │
│ Parser / Optimizer / Cache │
└─────────────────┬───────────────────┘
│
┌─────────────────▼───────────────────┐
│ Pluggable Storage Engine │
│ InnoDB / MyISAM / NDB / etc. │
└─────────────────────────────────────┘
PostgreSQL アーキテクチャ:
┌─────────────────────────────────────┐
│ Postmaster Process │
│ 新接続ごとに子プロセスを fork() │
│ Connection Pool: PgBouncer │
└─────────────────┬───────────────────┘
│ fork()
┌─────────────────▼───────────────────┐
│ Backend Process (接続ごと) │
│ Parser / Planner / Executor │
└─────────────────┬───────────────────┘
│
┌─────────────────▼───────────────────┐
│ Shared Memory │
│ Buffer Pool / WAL Buffers │
└─────────────────────────────────────┘
4.2 機能比較表
| 機能 | MySQL 8.0 | MariaDB 10.11 | PostgreSQL 16 | 備考 |
|---|---|---|---|---|
| ACID 準拠 | ◎ (InnoDB) | ◎ (InnoDB/Aria) | ◎ | 全て完全対応 |
| MVCC | ◎ (Undo Log) | ◎ (Undo Log) | ◎ (Heap) | 実装方式が異なる |
| トリガー | ○ | ○ | ◎ | PostgreSQL が高機能 |
| ストアドプロシージャ | ○ | ○ | ◎ | PostgreSQL は多言語対応 |
| ウィンドウ関数 | ◎ | ◎ | ◎ | 全て完全対応 |
| CTE (WITH句) | ◎ | ◎ | ◎ | PostgreSQL は再帰 CTE が高機能 |
| JSON サポート | ○ (JSON型) | ○ (JSON型) | ◎ (JSONB) | PostgreSQL の JSONB が高性能 |
| 配列型 | △ (JSON で代替) | △ | ◎ (ネイティブ) | PostgreSQL のみ |
| hstore | ✗ | ✗ | ◎ | PostgreSQL のみ |
| 全文検索 | ○ (FULLTEXT) | ○ (FULLTEXT) | ○ (tsvector) | 両方対応 |
| GIS/地理情報 | ○ (Spatial) | ○ (Spatial) | ◎ (PostGIS) | PostgreSQL + PostGIS が業界標準 |
| 外部テーブル | ✗ | ◎ (CONNECT) | ◎ (FDW) | PostgreSQL の FDW が豊富 |
| テーブルパーティション | ◎ | ◎ | ◎ | 全て対応 |
| 並列クエリ | △ | △ | ◎ | PostgreSQL が高機能 |
| マテリアライズドビュー | ✗ | ✗ | ◎ | PostgreSQL のみ |
| ロール管理 | ◎ (v8.0+) | ◎ | ◎ | 全て対応 |
| 行レベルセキュリティ | ✗ | ✗ | ◎ | PostgreSQL のみ |
| 論理レプリケーション | ◎ | ◎ | ◎ | 全て対応 |
| 双方向レプリケーション | △ (グループレプリケーション) | △ (Galera) | △ (pglogical 等) | 外部ツール必要 |
| 接続プーリング | ProxySQL/MaxScale | ProxySQL/MaxScale | PgBouncer/Pgpool-II | |
| 拡張機能 | プラグイン | プラグイン | Extension | PostgreSQL が豊富 |
| スキーマ | Database = Schema | Database = Schema | Database > Schema | 概念が異なる |
4.3 パフォーマンス比較
| シナリオ | MySQL/InnoDB | PostgreSQL | 推奨 |
|---|---|---|---|
| シンプルな OLTP (読み取り) | ◎ 高速 | ◎ 高速 | 同等 |
| シンプルな OLTP (書き込み) | ◎ 高速 | ○ 良好 | MySQL |
| 複雑な集計クエリ | ○ 良好 | ◎ 高速 | PostgreSQL |
| 全文検索 | ○ 良好 | ○ 良好 | 同等 |
| JSON 操作 | ○ 良好 | ◎ 高速 (JSONB) | PostgreSQL |
| 地理情報クエリ | ○ 良好 | ◎ 高速 (PostGIS) | PostgreSQL |
| 多数の並列接続 | ◎ 高速 (スレッド) | ○ PgBouncer 必須 | MySQL |
| 大量データの集計 | ○ 良好 | ◎ 高速 (並列) | PostgreSQL |
| レプリケーション遅延 | ◎ 低遅延 | ◎ 低遅延 | 同等 |
| VACUUM オーバーヘッド | なし | あり | MySQL |
4.4 運用コストの比較
| 項目 | MySQL/MariaDB | PostgreSQL |
|---|---|---|
| 学習コスト | 低い (広く普及) | やや高い |
| DBA スキル要求 | 中程度 | 高め |
| VACUUM 管理 | 不要 | 必要 (autovacuum) |
| XID 周回 | 不要 | 監視必要 |
| 接続プーリング | オプション | 推奨 (必須に近い) |
| バックアップツール | mysqldump/XtraBackup | pg_dump/pg_basebackup |
| 監視ツール | 豊富 | 豊富 |
| クラウドサポート | RDS/Aurora/Cloud SQL | RDS/Cloud SQL/AlloyDB |
| ドキュメント | 充実 | 充実 |
| コミュニティ | 大きい | 大きい |
4.5 ユースケース別推奨
| ユースケース | 推奨 DB | 理由 |
|---|---|---|
| WordPress/Drupal 等 CMS | MySQL/MariaDB | エコシステム、実績 |
| ECサイト (中規模) | MySQL/MariaDB | 高速な書き込み、シンプル |
| SaaS アプリケーション | PostgreSQL | スキーマ柔軟性、JSON、RLS |
| 分析・BI システム | PostgreSQL | 複雑なクエリ、並列処理 |
| 地理情報システム | PostgreSQL + PostGIS | PostGIS が業界標準 |
| 金融システム | PostgreSQL | 高い ACID 準拠、型安全性 |
| ゲームバックエンド | MySQL/MariaDB | 高速な OLTP |
| IoT データ収集 | TimescaleDB (PostgreSQL 拡張) | 時系列データ最適化 |
5. トラブルシューティング
5.1 MySQL / MariaDB のトラブルシューティング
起動失敗の診断
# サービスのステータス確認
sudo systemctl status mysqld
# エラーログの確認
sudo tail -100 /var/log/mysql/error.log
# 一般的な起動エラーと対処
# 1. ポートが使用中
# ERROR: Can't start server: Bind on TCP/IP port: Address already in use
sudo ss -tlnp | grep 3306
sudo lsof -i :3306
# 2. データディレクトリの権限エラー
# ERROR: mysqld: Can't read dir of '/var/lib/mysql/'
sudo ls -la /var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql
# 3. InnoDB ファイルの破損
# ERROR: InnoDB: Corruption in the InnoDB tablespace
# innodb_force_recovery で強制起動 (データ損失のリスクあり)
cat >> /etc/my.cnf << 'EOF'
[mysqld]
innodb_force_recovery = 1
EOF
sudo systemctl start mysqld
# データのエクスポート後、innodb_force_recovery を削除して再構築
接続問題の診断
# 接続テスト
mysql -u root -p -h localhost -e "SELECT 1;"
# 最大接続数の確認
mysql -u root -p -e "
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';
"
# 現在の接続一覧
mysql -u root -p -e "SHOW PROCESSLIST;" | head -20
# 長時間実行クエリの確認
mysql -u root -p -e "
SELECT id, user, host, db, command, time, state, left(info, 100) AS query
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep' AND time > 30
ORDER BY time DESC;
"
# 長時間クエリの強制終了
mysql -u root -p -e "KILL QUERY 12345;"
mysql -u root -p -e "KILL 12345;" # 接続自体を終了
レプリケーションエラーの対処
# レプリケーション停止の確認
mysql -u root -p -e "SHOW REPLICA STATUS\G" | grep -E "(Running|Error)"
# よくあるエラーと対処
# 1. Duplicate entry エラー
# Error: Duplicate entry '123' for key 'PRIMARY'
mysql -u root -p << 'EOF'
STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; -- 古い構文
-- または GTID モードの場合:
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:101';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;
EOF
# 2. テーブルが存在しないエラー
# Error: Table 'myapp.new_table' doesn't exist
# ソースから該当テーブルの構造をコピー
mysqldump -u root -p --no-data myapp new_table | mysql -u root -p myapp
# 3. バイナリログファイルが見つからない
# Fatal error: The slave I/O thread stops because master and slave have equal
# MySQL server UUIDs
# → server_id または auto.cnf の UUID が重複している場合
cat /var/lib/mysql/auto.cnf
# 解決: auto.cnf を削除して再起動すると新しい UUID が生成される
ディスク容量の管理
# バイナリログの手動削除
mysql -u root -p -e "SHOW BINARY LOGS;"
mysql -u root -p -e "PURGE BINARY LOGS BEFORE '2026-04-03 00:00:00';"
mysql -u root -p -e "PURGE BINARY LOGS TO 'mysql-bin.000010';"
# テーブルの最適化 (断片化の解消)
mysql -u root -p -e "OPTIMIZE TABLE myapp.orders;"
# 出力:
# +-------------+----------+----------+-------------------------------------------------------------------+
# | Table | Op | Msg_type | Msg_text |
# +-------------+----------+----------+-------------------------------------------------------------------+
# | myapp.orders| optimize | status | OK |
# +-------------+----------+----------+-------------------------------------------------------------------+
# テーブルサイズの確認
mysql -u root -p -e "
SELECT
table_name,
ROUND(data_length/1024/1024, 2) AS data_mb,
ROUND(index_length/1024/1024, 2) AS index_mb,
ROUND(data_free/1024/1024, 2) AS free_mb
FROM information_schema.TABLES
WHERE table_schema = 'myapp'
ORDER BY (data_length + index_length) DESC
LIMIT 10;
"
5.2 PostgreSQL のトラブルシューティング
起動失敗の診断
# PostgreSQL ログの確認
sudo tail -100 /var/lib/pgsql/16/data/log/postgresql-*.log
# 権限問題の確認
ls -la /var/lib/pgsql/16/data/
sudo -u postgres /usr/pgsql-16/bin/pg_ctl status -D /var/lib/pgsql/16/data
# ポートの確認
sudo ss -tlnp | grep 5432
# ロックファイルの確認
ls /var/lib/pgsql/16/data/postmaster.pid
# 古いロックファイルが残っている場合 (クラッシュ後)
sudo rm /var/lib/pgsql/16/data/postmaster.pid
sudo systemctl start postgresql-16
接続問題の診断
# pg_hba.conf の確認
sudo -u postgres psql -c "
SELECT * FROM pg_hba_file_rules;
"
# よくある接続エラー
# 1. pg_hba.conf での拒否
# FATAL: no pg_hba.conf entry for host "192.168.1.100", user "appuser"
# → pg_hba.conf に適切なエントリを追加して reload
# 2. 認証失敗
# FATAL: password authentication failed for user "appuser"
# → パスワードの確認、または認証方式の確認
sudo -u postgres psql -c "ALTER ROLE appuser PASSWORD 'NewP@ss!';"
# 3. 接続数超過
# FATAL: remaining connection slots are reserved for non-replication
# superuser connections
sudo -u postgres psql -c "SHOW max_connections;"
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"
# 接続のキル (緊急時)
sudo -u postgres psql -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state = 'idle'
AND state_change < NOW() - INTERVAL '10 minutes';
"
ロックの診断と解消
-- ロック待機の確認
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
blocked.wait_event_type,
blocked.wait_event
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE NOT blocked.granted;
-- ロックの詳細確認
SELECT
l.pid,
l.relation::regclass AS table_name,
l.mode,
l.granted,
a.query,
a.query_start,
now() - a.query_start AS duration
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;
-- デッドロックのログ確認
grep -i deadlock /var/lib/pgsql/16/data/log/postgresql-*.log
-- ブロッキングプロセスの終了
SELECT pg_terminate_backend(blocking_pid);
VACUUM 問題の診断
-- Autovacuum が追いつかないテーブルの確認
SELECT
n.nspname AS schema,
c.relname AS table_name,
s.n_dead_tup,
s.n_live_tup,
s.last_autovacuum,
s.last_autoanalyze,
c.reloptions
FROM pg_stat_user_tables s
JOIN pg_class c ON s.relid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE s.n_dead_tup > 10000
ORDER BY s.n_dead_tup DESC;
-- 手動 VACUUM の実行 (メンテナンスウィンドウで)
VACUUM (VERBOSE, ANALYZE) app.orders;
-- トランザクション ID 周回の確認 (緊急度の高い問題)
SELECT
datname,
age(datfrozenxid),
current_setting('autovacuum_freeze_max_age') AS max_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- age が 200,000,000 を超えたら早急に対処
-- VACUUM FREEZE を実行
VACUUM FREEZE app.orders;
パフォーマンス問題の診断
-- 実行中の遅いクエリ
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > INTERVAL '5 minutes'
AND state != 'idle'
ORDER BY duration DESC;
-- 実行計画のキャッシュ確認
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
CASE WHEN seq_scan + idx_scan > 0
THEN ROUND(idx_scan::numeric / (seq_scan + idx_scan) * 100, 2)
END AS idx_scan_pct
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;
-- 未使用インデックスの確認
SELECT
schemaname || '.' || tablename AS table,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan
FROM pg_stat_user_indexes
JOIN pg_indexes USING (schemaname, tablename, indexname)
WHERE idx_scan = 0
AND indexname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;
6. ベストプラクティス
6.1 セキュリティ
1. 最小権限の原則
- アプリケーション用ユーザーには必要最小限の権限のみ付与
- root/postgres ユーザーのリモートアクセスを禁止
- 接続元 IP アドレスを制限
2. 暗号化
- TLS/SSL による通信の暗号化
- 保存データの暗号化 (TDE: Transparent Data Encryption)
- バックアップの暗号化
3. 認証
- 強力な認証方式の使用 (SCRAM-SHA-256)
- 定期的なパスワードローテーション
- 多要素認証 (可能な場合)
4. 監査
- ログの適切な設定と保管
- 権限変更の記録
- 異常アクセスの検知
TLS 接続の設定 (MySQL)
# SSL 証明書の生成
sudo mysql_ssl_rsa_setup --uid=mysql
# または Let's Encrypt / 自己署名証明書を使用
openssl req -newkey rsa:2048 -days 3650 -nodes -x509 \
-subj "/CN=MySQL Server" \
-keyout /etc/mysql/ssl/server-key.pem \
-out /etc/mysql/ssl/server-cert.pem
# my.cnf に SSL 設定を追加
cat >> /etc/my.cnf << 'EOF'
[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON
EOF
TLS 接続の設定 (PostgreSQL)
# 証明書のコピー
sudo cp /path/to/server.crt $PGDATA/server.crt
sudo cp /path/to/server.key $PGDATA/server.key
sudo chown postgres:postgres $PGDATA/server.crt $PGDATA/server.key
sudo chmod 600 $PGDATA/server.key
# postgresql.conf の設定
cat >> $PGDATA/postgresql.conf << 'EOF'
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'
ssl_min_protocol_version= 'TLSv1.2'
EOF
# pg_hba.conf で SSL 必須に設定
# hostssl all all 0.0.0.0/0 scram-sha-256
6.2 バックアップ戦略
バックアップの 3-2-1 ルール:
- 3 つのコピー (本番 + バックアップ 2 つ)
- 2 つの異なるメディア (ローカル + クラウド)
- 1 つはオフサイト (遠隔地)
バックアップスケジュール例:
- 毎日: フルバックアップ (mysqldump / pg_dump)
- 継続的: バイナリログ / WAL のアーカイブ
- 週次: バックアップの整合性テスト
- 月次: リストアテスト (実際に復元して確認)
RTO / RPO の目標設定:
- RTO (Recovery Time Objective): 目標復旧時間
- RPO (Recovery Point Objective): 目標復旧時点
# バックアップスクリプトの例 (MySQL)
#!/bin/bash
set -euo pipefail
BACKUP_DIR=/backup
RETENTION_DAYS=7
DB_USER=backup
DB_PASS_FILE=/etc/mysql/backup.cnf
LOG_FILE=/var/log/mysql_backup.log
DATE=$(date +%Y%m%d_%H%M%S)
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a $LOG_FILE; }
log "バックアップ開始"
# フルバックアップ
if ! mysqldump --defaults-file=$DB_PASS_FILE \
--single-transaction --all-databases \
--master-data=2 --routines --triggers --events \
| gzip > "$BACKUP_DIR/full_$DATE.sql.gz"; then
log "ERROR: バックアップ失敗"
exit 1
fi
log "バックアップ完了: full_$DATE.sql.gz"
# 古いバックアップの削除
find $BACKUP_DIR -name "full_*.sql.gz" -mtime +$RETENTION_DAYS -delete
log "古いバックアップを削除 (${RETENTION_DAYS}日以前)"
# バックアップファイルの確認
BACKUP_SIZE=$(du -sh "$BACKUP_DIR/full_$DATE.sql.gz" | cut -f1)
log "バックアップサイズ: $BACKUP_SIZE"
log "バックアップ完了"
6.3 パフォーマンスチューニングのチェックリスト
MySQL/MariaDB:
□ innodb_buffer_pool_size を物理メモリの 70-80% に設定
□ innodb_log_file_size を適切なサイズに設定 (1-4GB)
□ innodb_flush_method = O_DIRECT (SSD の場合)
□ スロークエリログを有効化し定期的に分析
□ インデックスの最適化 (EXPLAIN で確認)
□ max_connections を適切に設定
□ バイナリログの定期的なパージ
PostgreSQL:
□ shared_buffers を RAM の 25% に設定
□ effective_cache_size を RAM の 75% に設定
□ work_mem を適切に設定
□ random_page_cost を SSD に合わせて調整 (1.1-2.0)
□ autovacuum のチューニング
□ pg_stat_statements で遅いクエリを特定
□ インデックスの最適化
□ PgBouncer の設定
□ checkpoint_completion_target = 0.9 に設定
6.4 高可用性設計
MySQL の HA オプション:
1. MySQL Replication (非同期/半同期)
- シンプル、低遅延
- フェイルオーバーは手動または MHA/Orchestrator で自動化
2. MySQL Group Replication / InnoDB Cluster
- 自動フェイルオーバー
- Paxos ベースの合意形成
- マルチプライマリモード対応
3. ProxySQL / MaxScale
- ロードバランシング
- 自動フェイルオーバー検知
- クエリルーティング
PostgreSQL の HA オプション:
1. Streaming Replication + pg_ctl promote
- シンプルだが手動フェイルオーバー
2. Patroni + etcd/Consul
- 自動フェイルオーバー
- クラスター管理 REST API
- Kubernetes 対応
3. Pgpool-II
- 接続プーリング + ロードバランシング
- 自動フェイルオーバー
- オンラインリカバリ
4. Citus
- 分散 PostgreSQL
- シャーディング対応
- 大規模分析向け
6.5 定期メンテナンスタスク
# MySQL の定期メンテナンススクリプト
#!/bin/bash
# 1. テーブルの確認と修復
mysqlcheck --auto-repair --all-databases -u root -p
# 2. 統計情報の更新
mysql -u root -p -e "
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND table_type = 'BASE TABLE'
" | grep "ANALYZE" | mysql -u root -p
# 3. バイナリログの古いファイルを削除
mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"
# PostgreSQL の定期メンテナンス
#!/bin/bash
# 1. 全データベースの VACUUM ANALYZE
sudo -u postgres vacuumdb --all --analyze --verbose
# 2. 古いログファイルの削除
find /var/lib/pgsql/16/data/log -name "postgresql-*.log" -mtime +30 -delete
# 3. 統計情報のリセット (任意)
# sudo -u postgres psql -c "SELECT pg_stat_reset();"
# 4. インデックスの再構築 (断片化が激しい場合)
# sudo -u postgres psql -d myapp -c "REINDEX DATABASE myapp;"
7. 参考資料
公式ドキュメント
- MySQL 8.0 Reference Manual: https://dev.mysql.com/doc/refman/8.0/
- MariaDB Server Documentation: https://mariadb.com/kb/en/documentation/
- PostgreSQL 16 Documentation: https://www.postgresql.org/docs/16/
- Percona XtraBackup Documentation: https://docs.percona.com/percona-xtrabackup/
- PgBouncer Documentation: https://www.pgbouncer.org/config.html
- Patroni Documentation: https://patroni.readthedocs.io/
チューニングツール
- MySQLTuner: https://github.com/major/MySQLTuner-perl
- Percona Toolkit: https://www.percona.com/software/database-tools/percona-toolkit
- PgTune: https://pgtune.leopard.in.ua/ (PostgreSQL 設定の自動最適化)
- pgBadger: https://github.com/darold/pgbadger (PostgreSQL ログ分析)
- pt-query-digest: Percona Toolkit に含まれる
監視ツール
- Prometheus + mysqld_exporter: https://github.com/prometheus/mysqld_exporter
- Prometheus + postgres_exporter: https://github.com/prometheus-community/postgres_exporter
- Grafana Dashboard for MySQL: Grafana ID 7362
- Grafana Dashboard for PostgreSQL: Grafana ID 9628
- PMM (Percona Monitoring and Management): https://www.percona.com/software/pmm
参考書籍
- 「MySQL 徹底入門 第4版」翔泳社
- 「PostgreSQL 徹底入門 第4版」翔泳社
- 「High Performance MySQL, 4th Edition」O'Reilly
- 「PostgreSQL: Up and Running, 3rd Edition」O'Reilly
- 「Database Internals」Alex Petrov 著 (データベース内部構造の詳解)
コミュニティリソース
- MySQL Community Forum: https://forums.mysql.com/
- PostgreSQL Mailing Lists: https://www.postgresql.org/list/
- Percona Blog: https://www.percona.com/blog/
- Planet PostgreSQL: https://planet.postgresql.org/
- Use The Index, Luke: https://use-the-index-luke.com/ (インデックス設計ガイド)
本ドキュメントは 2026-04-10 時点の情報を基に作成されました。
MySQL 8.0 / MariaDB 10.11 / PostgreSQL 16 を対象としています。