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