Re: DB files, sizes and cleanup

On Fri, Dec 17, 2010 at 12:31 PM, Gauthier, Dave
<dave(dot)gauthier(at)intel(dot)com> wrote:
> When I restart the DB, it reports... "LOG: autovacuum launcher started".
>
> "ps aux | grep postgres" yields this...
>
> dfg_suse> ps aux | grep postgres
> pgdbadm 22656  0.0  0.0  21296  2616 pts/7    S+   Dec16   0:00
> /usr/intel/pkgs/postgresql/8.3.4/bin/psql -h fcadsql3.fc.intel.com hsxreuse
> pgdbadm  9135  0.0  0.0  50000  5924 pts/10   S    12:22   0:00
> /nfs/hd/itools/em64t_linux26/pkgs/postgresql/8.3.4/bin/postgres -D
> /app/PG/v83
> pgdbadm  9146  0.0  0.0  50000  1360 ?        Ss   12:22   0:00 postgres:
> writer
> process
> pgdbadm  9147  0.0  0.0  50000  1156 ?        Ss   12:22   0:00 postgres:
> wal writer
> process
> pgdbadm  9148  0.0  0.0  50000  1316 ?        Ss   12:22   0:00 postgres:
> autovacuum launcher
> process
> pgdbadm  9149  0.0  0.0  18904  1308 ?        Ss   12:22   0:00 postgres:
> stats collector
> process
> pgdbadm  9354  0.0  0.0   2896   760 pts/9    S+   12:27   0:00 grep
> postgres
>
>
> TSo I assu,e it's running?
>
> This is PG v 8.3.4 on linux.
>
>
>
> -----Original Message-----
> From: Bill Moran [mailto:wmoran(at)potentialtech(dot)com]
> Sent: Friday, December 17, 2010 12:17 PM
> To: Gauthier, Dave
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] DB files, sizes and cleanup
>
> In response to "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>:
>
>> Hi:
>>
>> I'm trying to justify disk space for a new linux server they're going to
>> give me for my Postgres instance.  When I do a "du" of the place I installed
>> the older instance on the system that is to be replaced, I see that the
>> vast, vast majorityof the space goes to the contents of the "base" dir.  In
>> there are a bunch of files with integers for names (iod's ?).  And some of
>> those have millions of files inside.
>>
>> Is this normal?  Should there be millions of files in some of these "base"
>> directories?
>> Is this indicative of some sort of problem or lack of cleanup that I
>> should have been doing?
>>
>> The "du" shows that I'm using 196G (again, mostly in "base") but
>> pg_database_size shows something like 1/4 that amount, around 50G.  I'd like
>> to know if there's something I'm supposed to be doing to cleanup old
>> (possibly deleted) data.
>>
>> Also, I was running pg_size_pretty(pg_database_size('mydb')) on all the
>> dbs.  It runs very fast for most, but just hangs for two of the databases.
>> Is this indicative of some sort of problem?  (BTW, the 2 it hangs on are
>> very much like others that it doesn't hang on, so I used those numbers to
>> estimate the 50G)
>
> 1) Do you have autovacuum running, or do you have a regular vacuum
>    scheduled?  Because this seems indicative of no vacuuming, or errors
>    in vacuuming, or significantly insufficient vacuuming.
> 2) Unless your databases contain close to 100G of actual data, that size
>    seems unreasonable.
> 3) pg_database_size() is probably not "hanging", it's probably just taking
>    a very long time to stat() millions of files.
>
> Overall, I'm guessing you're not vacuuming your databases on a proper
> schedule and that most of that 196G is bloat that doesn't need to be
> there.  When bloat gets really bad, you're generally better off dumping
> the datbases and restoring them, as a vacuum full might take a very,
> very long time.
>
> If you can demonstrate that the cause of this is table bloat, then I
> would go through all your databases and do a vacuum full/reindex or
> do a dump/restore if the problem is very bad.  Once you have done that,
> your du output should be more realistic and more helpful.
>
> Then, take some time to set up appropriate autovacuum settings so the
> problem doesn't come back.

Check your logs for warnings about the free space map. what are
max_fsm_pages and max_fsm_relations set to? how many tables and
indexes do you have approximately? do you truly have 'millions' of
files?

go into base folder and do:
find | wc -l

merlin