[HACKERS] Re: [GENERAL] Odd VACUUM behavior when it is expected to truncate last empty pages
Thank you very much, your explanation helped a lot. This is the tool I needed the solution for http://code.google.com/p/pc-tools/ if you are interested. On 4 August 2011 01:10, Pavan Deolasee pavan.deola...@gmail.com wrote: On Wed, Aug 3, 2011 at 12:33 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: The only problem, other than a surprising behavior that you noted, that I see with this approach is that we might repeatedly try to truncate a relation which in fact does not have anything to truncate. The worst thing is we might unnecessarily take an exclusive lock on the table. So it seems we tried to fix this issue sometime back http://archives.postgresql.org/pgsql-hackers/2008-12/msg01994.php But I don't quite understand how the fix would really work. nonempty_pages would most likely be set at a value lower than relpages if the last page in the relation is all-visible according to the visibility map. Did we mean to test (nonempty_pages 0) there ? But even that may not work except for the case when there are no dead tuples in the relation. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] Odd VACUUM behavior when it is expected to truncate last empty pages
(moving this to hackers since I suspect we got an issue to fix here) On Wed, Aug 3, 2011 at 6:35 AM, Sergey Konoplev gray...@gmail.com wrote: Hi all, I have PostgreSQL 9.0.3 installed on my Gentoo Linux box. The configuration is default. There is no any activity in the database but the described below. What I am trying to achieve is the effect described in this article http://blog.endpoint.com/2010/09/reducing-bloat-without-locking.html. In short I am updating last pages of a table to move the tuples to the earlier pages to make VACUUM able to truncate the empty tail. However I faced a strange VACUUM behavior. So the situation is: 1. do some UPDATEs on the table so it has several last pages free, 2. perform VACUUM of this table the 1st time, no tail pages will be truncated (why?), 3. perform VACUUM the 2nd time straight after the 1st one and it will truncate the tail pages (why this time?). There is a check to truncate only if minimum 1000 or relpages/16 pages (compile time constants) can be truncated and attempt truncation only if thats true, otherwise we assume that the cost of attempting truncation is not worth the cost of pages salvaged. With that logic, we should have never truncated just 3 pages in a relation with 3000+ pages. But I can see that happening because of visibility maps. So if we stop scanning beyond a certain percentage of pages, we might be fooled into believing that the rest of the pages can possibly be truncated and then do a hard check to find that out. The first vacuum probably scans even the last few pages because you just updated the tuples of those pages and then updates the visibility map for those pages. The second vacuum then stops much before because the visibility map tells us that all remaining pages are visible (and thus set nonempty_pages to a lower number) and so the check I mentioned earlier succeeds and we attempt the truncation. Now, this is just a theory and a reproducible case will help to confirm this. The only problem, other than a surprising behavior that you noted, that I see with this approach is that we might repeatedly try to truncate a relation which in fact does not have anything to truncate. The worst thing is we might unnecessarily take an exclusive lock on the table. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] Odd VACUUM behavior when it is expected to truncate last empty pages
On Wed, Aug 3, 2011 at 12:33 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: The only problem, other than a surprising behavior that you noted, that I see with this approach is that we might repeatedly try to truncate a relation which in fact does not have anything to truncate. The worst thing is we might unnecessarily take an exclusive lock on the table. So it seems we tried to fix this issue sometime back http://archives.postgresql.org/pgsql-hackers/2008-12/msg01994.php But I don't quite understand how the fix would really work. nonempty_pages would most likely be set at a value lower than relpages if the last page in the relation is all-visible according to the visibility map. Did we mean to test (nonempty_pages 0) there ? But even that may not work except for the case when there are no dead tuples in the relation. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
