We upgraded to 9.1 from 8.4 over the summer a few months ago, to new DB
servers with more disk space and memory. Unexpectedly, the DB servers
have steadily increased their disk space usage since. Reported system
load doesn't seem to be affected. It's happening to all our DB servers
running 9.1.
When we reload all pg_dumps from our worst-affected server into an
offline server, the disk space usage is about 26 GB, but the production
database is using 166 GB. (# df /var/lib/pgsql;)
To resolve this, we've tried:
1) reindexed everything (cut about 10% of disk usage temporarily)
2) tried vacuum full, and vacuum analyze on all databases. (to minimal
effect)
3) Restarting PG (no discernable effect) including a full stop/start.
4) We've looked for stale prepared transactions (none found)
5) instructions from the wiki to try to determine what the cause of all
the disk usage is: http://wiki.postgresql.org/wiki/Disk_Usage but when
we add up all the results for all the different databases, tables,
indexes, etc. in a script, we get a number very close to the usage of
the freshly loaded server. (24 GB)
What is Postgres doing with ~ 80% of its disk space usage? This is not
normal, is it? I would hate to have to take the servers off line just to
dump/restore in order to bring disk usage back to normal...
SYSTEM SPECS:
I've attached the postgresql.conf on a RHEL6/64 Linux server with 128 GB
Of RAM and 16 real CPU cores. (HT turned on, 32 CPUs according to the O/S)
#FROM: sysctl.conf:
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 136365211648
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
-Ben