I'm trying to do a pg_dump of a database, and it more-or-less just sits
there doing nothing. "vmstat 2" looked like this during pg_dump:
procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 1 51392 1299708 122088 9980572 0 0 1024 0 96 190 2 0
81 18
0 1 51392 1299584 122084 9980692 0 0 1216 0 99 190 1 0
88 11
1 1 51392 1299316 122092 9980712 0 0 1088 6 100 197 1 0
83 16
0 1 51392 1298960 122092 9981408 0 0 1472 0 93 202 1 0
88 11
0 1 51392 1298756 122084 9980804 22 0 534 108 132 264 1 0
86 13
0 1 51392 1300700 122088 9978796 0 0 128 6 62 131 0 0
87 13
0 1 51392 1300756 122084 9979136 0 0 1728 1336 135 223 1 0
86 13
0 1 51392 1300772 122088 9978748 0 0 960 18 97 189 1 0
87 12
There was no significant CPU usage. top(1) shows pg_dump using about 14%
CPU and postmaster using about 4% CPU, and nothing else going on.
I suspected many things (bad battery on 3WARE RAID, rogue process, etc.),
but everything reported OK. bonnie++ reported excellent results, exactly
the same as when the server was installed. If I stop the pg_dump, restart
Postgres just for good measure, and run pg_bench, it reports good results:
pgbench -U test -c 5 -t 20000
tps = 2270
pgbench -U test -c 10 -t 10000
tps = 3329
pgbench -U test -c 20 -t 5000
ps = 4766
pgbench -U test -c 30 -t 3333
tps = 7309
pgbench -U test -c 40 -t 2500
tps = 7539
pgbench -U test -c 50 -t 2000
tps = 8618
But if I restart pg_dump, it's as slow as before.
Furthermore, when I do a pg_dump on the second identically-configured
server (with the same database schema and almost the same data), and run
"vmstat 2", I get very high througput of the dump, and "vmstat 2" shows
much more reasonable results:
procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy id
wa
1 0 303824 49892 521596 10637436 0 0 16706 19654 1078 1013 17 1
81 1
1 0 303824 46808 521584 10641048 0 0 16320 19628 1117 1099 17 1
81 1
2 0 303824 45480 521560 10643572 0 0 17088 19620 869 1015 17 1
81 1
2 0 303824 53540 521564 10639788 0 0 15680 19664 836 975 18 1
80 1
2 0 303824 63524 521500 10632188 0 0 16192 19630 963 1029 16 1
82 0
2 0 303824 64112 521508 10632160 0 0 13126 24570 946 965 16 1
82 1
On both servers, I'm sending the output of pg_dump to /tmp to eliminate
possible network problems. The command is:
pg_dump --format=c --verbose --blobs -U postgres emolecules \
>/tmp/emolecules-$server-$date.pg_dump
2>/emi/logs/backup_$server_emolecules.log &
Both servers:
Data: 8-disk RAID10
WAL: 2-disk RAID1
Linux: 2-disk RAID1
2x4-Core Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
"Good" server:
8GB RAM
Postgres 8.4.17
"Bad" server
12 GB RAM
Postgres 9.2.1
I don't even know where to look next. What could be making pg_dump so slow?
Thanks,
Craig
