Re: Unexpectedly high disk space usage

On Mon, Nov 5, 2012 at 8:01 PM, Lists <lists(at)benjamindsmith(dot)com> wrote:
> 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 does du -sh have to say about it? Use unix tools to examine your
file system and see where the usage is going. For instance, I can do
this:

cd /var/lib/postgresql/8.4/main/
du -s *|sort -n
0 server.crt
0 server.key
4 pg_tblspc
4 pg_twophase
4 PG_VERSION
4 postmaster.opts
4 postmaster.pid
12 pg_clog
12 pg_stat_tmp
12 pg_subtrans
28 pg_multixact
460 global
16392 pg_xlog
16396 base

which tells me that I'm using about 16MB for each pg_xlog and base. I
can then do cd into base and look around:

cd base
du -s *|sort -n
5416 1
5416 11563
5560 11564

Which shows me using about 5MB each for three different dbs.

And so on.

On an off guess, did you go from a SQL_ASCII encoding to UTF8? That
might increase disk space usage a bit.