Extremely slow server?

From: Craig James <cjames(at)emolecules(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Extremely slow server?
Date: 2013-09-14 18:28:36
Message-ID: CAFwQ8reG6MqYR1eMKYnEZCkbJ81+-YdkCWKACeT-9te2Ybgq7w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message bricklen 2013-09-14 18:36:04 Re: Extremely slow server?
Previous Message David Whittaker 2013-09-13 15:05:24 Re: Intermittent hangs with 9.2