Dnia 6 sierpnia 2012 17:00 Adrian Klaver <adrian.klaver@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
