Re: [GENERAL] Clogging problem
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 – /data75 348 037 632 lsof – /data/base 74 975 969 280 lsof – /data/base (deleted) 53 769 936 896 lsof – /data/pg_xlog369 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;1098585/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/19;55/19;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;1098585/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
Re: [GENERAL] Clogging problem (was: Schema-only dump dumps no constraints, no triggers)
Hi, to complement information from the previous message: Dnia 29 lipca 2012 12:29 Marek Kielar mkie...@go2.pl napisał(a): Hi, Dnia 28 lipca 2012 1:10 Adrian Klaver adrian.kla...@gmail.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 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 – /data75 348 037 632 lsof – /data/base 74 975 969 280 lsof – /data/base (deleted) 53 769 936 896 lsof – /data/pg_xlog369 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 mkie...@go2.pl napisał(a): Hi, Dnia 28 lipca 2012 1:10 Adrian Klaver adrian.kla...@gmail.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 – /data75 348 037 632 lsof – /data/base 74 975 969 280 lsof – /data/base (deleted) 53 769 936 896 lsof – /data/pg_xlog369 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
