Re: Clogging problem

Dnia 6 sierpnia 2012 17:00 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> napisał(a):

> > 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.
>

Sorry about that, it's the first time I had used a hosting service and they didn't require creating an account (as in a list on Wikipedia). I guess using NoScript spoils with saving from trouble, but makes one come at wrong assumptions. I'll try to choose better next time.

> >
> > 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
>

1) Running, through pgAdmin3, an ordinary VACUUM FULL ANALYZE and REINDEX on all our databases plus the "postgres" database that are in the cluster, didn't release a substantial amount of disk space - it might add up to maybe a few percent of the overall.

2) It doesn't seem there are any long-running transactions even though the PIDs do repeat during some time (but since the connections are kept open this seems reasonable):

postgres=# SELECT * FROM pg_locks ORDER BY pid;
locktype;database;relation;page;tuple;virtualxid;transactionid;classid;objid;objsubid;virtualtransaction;pid;mode;granted
relation;11874;10985;;;;;;;;85/101738;24367;AccessShareLock;t
virtualxid;;;;;85/101738;;;;;85/101738;24367;ExclusiveLock;t
virtualxid;;;;;20/788838;;;;;20/788838;24505;ExclusiveLock;t
virtualxid;;;;;14/923780;;;;;14/923780;24621;ExclusiveLock;t
virtualxid;;;;;76/139304;;;;;76/139304;24699;ExclusiveLock;t
virtualxid;;;;;55/199999;;;;;55/199999;24703;ExclusiveLock;t
virtualxid;;;;;59/363780;;;;;59/363780;24926;ExclusiveLock;t
(7 rows)

And after some time with a different invocation of psql (to let go of the PID):

postgres=# SELECT * FROM pg_locks ORDER BY pid;
locktype;database;relation;page;tuple;virtualxid;transactionid;classid;objid;objsubid;virtualtransaction;pid;mode;granted
virtualxid;;;;;56/410614;;;;;56/410614;25105;ExclusiveLock;t
virtualxid;;;;;3/667499;;;;;3/667499;25145;ExclusiveLock;t
relation;11874;10985;;;;;;;;85/101817;25171;AccessShareLock;t
virtualxid;;;;;85/101817;;;;;85/101817;25171;ExclusiveLock;t
(4 rows)

We are again approaching slowly the point that the server restart will be needed. If / when this happens, I'll provide statistics again.

Best regards,
Marek Kielar