Re: Clogging problem

On 08/06/2012 05:08 AM, Marek Kielar wrote:
> Hi,
>
> to complement information from the previous message:
>
>
> Dnia 29 lipca 2012 12:29 Marek Kielar <mkielar(at)go2(dot)pl> napisał(a):
>
>> Hi,
>>
>>
>> Dnia 28 lipca 2012 1:10 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> napisał(a):
>>
>>> What where the deleted files?
>>> WAL, Logs, other?
>>
>>
>> at this time - a couple days after restart, the clog hasn't re-formed yet. Thus, I am unable to tell you what files they were, we didn't pay that much attention to it then - there were some WAL files but I can't tell what the actual structure was. I'll provide this information whenever possible.
>
>
> The clog has somewhat re-formed - the full listing of lsof (filtered for unique files) for postmaster(s) on the database mount is here:
> http://BillionUploads.com/ya9kjv78t9es/postmaster_files_sorted.csv.html

FYI you might to consider using some other site for uploads. The above
is sort of scary and leads you down all sorts of false paths.

>
> Consecutive commands were issued in a matter of minutes and differ slightly.
>
> Some totals / aggregates:
> df – /data 83 141 382 144
> du – /data 29 170 365 801
> lsof – /data 75 348 037 632
> lsof – /data/base 74 975 969 280
> lsof – /data/base (deleted) 53 769 936 896
> lsof – /data/pg_xlog 369 098 752
> lsof – /data/pg_xlog (deleted) 201 326 592
> lsof – /data/global 2 965 504
>
> It is clear that the server processes are keeping most of the files from being actually deleted.

Well the nature of database data files is they expand and/or contract as
needed. Unless you are getting rid of the actual object they refer to
they will not be deleted. The files WAL files in pg_xlog are a different
matter, but in the listing you sent they seem to be reasonable. There
are a couple of things off the top of my head that can cause data files
to expand unnecessarily:
1) Autovacuum is not aggressive enough.
2) There are open transactions keeping old tuples from being removed.

From previous posts, you mentioned a 'permanent' connection to the
database. Are you sure it is not holding an open transaction?
The pg_locks view would be a good place to start:
http://www.postgresql.org/docs/9.1/interactive/view-pg-locks.html

>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com