Re: Maintenance question / DB size anomaly... - Mailing list pgsql-performance
From | Kurt Overberg |
---|---|
Subject | Re: Maintenance question / DB size anomaly... |
Date | |
Msg-id | 1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com Whole thread Raw |
In response to | Re: Maintenance question / DB size anomaly... (Chris Browne <cbbrowne@acm.org>) |
Responses |
Re: Maintenance question / DB size anomaly...
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-performance |
Chris, I took your advice, and I had found that sl_log_1 seems to be causing some of the problem. Here's the result of a VACUUM VERBOSE mydb # vacuum verbose _my_cluster.sl_log_1 ; INFO: vacuuming "_my_cluster.sl_log_1" INFO: index "sl_log_1_idx1" now contains 309404 row versions in 1421785 pages DETAIL: 455001 index row versions were removed. 1419592 index pages have been deleted, 1416435 are currently reusable. CPU 16.83s/5.07u sec elapsed 339.19 sec. ^@^@^@INFO: index "sl_log_1_idx2" now contains 312864 row versions in 507196 pages DETAIL: 455001 index row versions were removed. 506295 index pages have been deleted, 504998 are currently reusable. CPU 6.44s/2.27u sec elapsed 138.70 sec. INFO: "sl_log_1": removed 455001 row versions in 7567 pages DETAIL: CPU 0.56s/0.40u sec elapsed 6.63 sec. INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row versions in 13764 pages DETAIL: 0 dead row versions cannot be removed yet. There were 51972 unused item pointers. 0 pages are entirely empty. CPU 24.13s/7.85u sec elapsed 486.49 sec. INFO: vacuuming "pg_toast.pg_toast_955960155" INFO: index "pg_toast_955960155_index" now contains 9 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_955960155": found 0 removable, 9 nonremovable row versions in 3 pages DETAIL: 0 dead row versions cannot be removed yet. There were 3 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM ...I then checked the disk and those pages are still there. If I do a: select count(*) from _my_cluster.sl_log_1; count ------- 6366 (1 row) Would a VACUUM FULL take care of this? It seems to me that its not clearing up the indexes properly. You are correct in that I do see things getting much bigger on the master than on the subscriber nodes. Could this cause my slony replication to bog down? Also- I have a question about this comment: > > You don't forcibly have to take Slony-I down during this, but the > locks taken out on tables by CLUSTER/VACUUM FULL will block slons from > doing any work until those transactions complete. Thats because no writing will be done to the tables, thus, no slony triggers will get triggered, correct? I'd rather not shut down slony if I dont have to, but will if it "is safer/better/ more badass". For those playing along at home, > $ find $PGDATA/base -name "[0-9]+\.[0-9]+" > ...I had to use: find $PGDATA/base -name "[0-9]*\.[0-9]*" ...but the pluses should have worked too. Still a much better way than how I was doing it. Thanks again for helping me with this, its greatly appreciated! /kurt
pgsql-performance by date: