在 postgres 的 pg_dump 中限制 I/O？

Question

因此，我们在具有 16GB RAM 的机器上拥有一个 32GB 的生产数据库。由于缓存，这通常根本不是问题。但是每当我启动数据库的 pg_dump 时，来自应用程序服务器的查询就会开始排队，几分钟后队列就会消失，我们的应用程序就会停止运行。

我将第一个承认我们存在查询性能问题，并且我们正在解决这些问题。同时，我希望能够每晚运行 pg_dump，以一种从数据库中获取数据并且不会导致我们的应用程序崩溃的方式。我不在乎是否需要几个小时。我们的应用程序不运行任何 DDL，因此我不担心锁争用。

为了解决这个问题，我正在使用 Nice 和 ionice 运行 pg_dump。不幸的是，这并不能解决问题。

nice ionice -c2 -n7 pg_dump -Fc production_db -f production_db.sql

即使使用 ionice 我仍然看到上面的问题。看来 i/o 等待和大量搜索导致了该问题。

vmstat 1 

显示 iowait 徘徊在 20-25% 左右，有时会飙升至 40%。实际 CPU % 在 2-5% 之间波动，有时会飙升至 70%。

我不认为锁是可能的罪魁祸首。当我运行此查询时：

select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;

我只看到标记为 grant = 't' 的锁。我们通常不会在生产中运行任何 DDL——因此锁似乎不是问题。

以下是启用了 WCHAN 列的 ps 的输出：

PID WIDE               S TTY          TIME COMMAND
3901 sync_page         D ?        00:00:50 postgres: [local] COPY
3916 -                 S ?        00:00:01 postgres:  SELECT
3918 sync_page         D ?        00:00:07 postgres:  INSERT
3919 semtimedop        S ?        00:00:04 postgres:  SELECT
3922 -                 S ?        00:00:01 postgres:  SELECT
3923 -                 S ?        00:00:01 postgres:  SELECT
3924 -                 S ?        00:00:00 postgres:  SELECT
3927 -                 S ?        00:00:06 postgres:  SELECT
3928 -                 S ?        00:00:06 postgres:  SELECT
3929 -                 S ?        00:00:00 postgres:  SELECT
3930 -                 S ?        00:00:00 postgres:  SELECT
3931 -                 S ?        00:00:00 postgres:  SELECT
3933 -                 S ?        00:00:00 postgres:  SELECT
3934 -                 S ?        00:00:02 postgres:  SELECT
3935 semtimedop        S ?        00:00:13 postgres:  UPDATE waiting
3936 -                 R ?        00:00:12 postgres:  SELECT
3937 -                 S ?        00:00:01 postgres:  SELECT
3938 sync_page         D ?        00:00:07 postgres:  SELECT
3940 -                 S ?        00:00:07 postgres:  SELECT
3943 semtimedop        S ?        00:00:04 postgres:  UPDATE waiting
3944 -                 S ?        00:00:05 postgres:  SELECT
3948 sync_page         D ?        00:00:05 postgres:  SELECT
3950 sync_page         D ?        00:00:03 postgres:  SELECT
3952 sync_page         D ?        00:00:15 postgres:  SELECT
3964 log_wait_commit   D ?        00:00:04 postgres:  COMMIT
3965 -                 S ?        00:00:03 postgres:  SELECT
3966 -                 S ?        00:00:02 postgres:  SELECT
3967 sync_page         D ?        00:00:01 postgres:  SELECT
3970 -                 S ?        00:00:00 postgres:  SELECT
3971 -                 S ?        00:00:01 postgres:  SELECT
3974 sync_page         D ?        00:00:00 postgres:  SELECT
3975 -                 S ?        00:00:00 postgres:  UPDATE
3977 -                 S ?        00:00:00 postgres:  INSERT
3978 semtimedop        S ?        00:00:00 postgres:  UPDATE waiting
3981 semtimedop        S ?        00:00:01 postgres:  SELECT
3982 -                 S ?        00:00:00 postgres:  SELECT
3983 semtimedop        S ?        00:00:02 postgres:  UPDATE waiting
3984 -                 S ?        00:00:04 postgres:  SELECT
3986 sync_buffer       D ?        00:00:00 postgres:  SELECT
3988 -                 R ?        00:00:01 postgres:  SELECT
3989 -                 S ?        00:00:00 postgres:  SELECT
3990 -                 R ?        00:00:00 postgres:  SELECT
3992 -                 R ?        00:00:01 postgres:  SELECT
3993 sync_page         D ?        00:00:01 postgres:  SELECT
3994 sync_page         D ?        00:00:00 postgres:  SELECT

Answer 1

1. 最简单的：
   您可以使用 pv 来限制 pg_dump。

2. 更难的是：
   更改您的备份过程。使用例如：
   <前> psql -c 'pg_start_backup()'
   rsync --checksum --archive /var/lib/pgsql /backups/pgsql
   psql -c 'pg_stop_backup()'
   
   但请注意，您还需要设置连续归档为此，备份期间创建的所有 WAL 文件都会与数据文件备份一起保存。

3. 更难的是：
   您可以设置复制数据库（例如使用日志传送 ）在额外的廉价磁盘上，而不是备份生产数据库备份副本。即使它会落后于一些交易，它最终也会赶上。但在开始备份之前请检查副本是否是最新的。

Answer 2

您的 PS 输出有多个处于“等待”状态的 UPDATE 语句，这仍然对我说锁定（您的锁定测试查询除外）。我很确定您不会在 PS 输出中看到“等待”。您能否检查一下此查询在问题期间是否显示任何内容：（

SELECT * FROM pg_stat_activity WHERE waiting;

您没有说您正在运行什么版本的 PostgreSQL，所以我不确定这是否有效。）

如果有任何内容（也就是说，等待= TRUE），那么这是一个锁/事务问题。