Обсуждение: Reusing Dead Tuples:
Hi, I am doing some experiments on dead tuples, I am looking of reusing the dead tuples apace in a particular page during the "Update".This patch is meant for the tables which are heavily updated to avoid vacuum very frequently.By using it will arrest the size of table for heavily updated table. The algorithm works like this:1) During the update it check for the dead tuples in the current page(page that containthe tuple that need to be updated). If it finds any dead tuples it uses the dead tuple spaceby ovewriting on dead tuple. The checking of dead tuple is very similer to the task that of lazy vaccum.2) If it cannot find any dead tuple it proceed as usual by inserting at the end of table . Performance Effect: 1) The CPU processing will be slighly more for the update, but io processing is exactly same2) The size of table grows slower under heavy update , so vacuum is not required very frequently. The total processing for update is more or less same even after doing large number of updates without vacuum. Does it breaks anythings by overwriting the dead tuples ?. Comments?. jana
Janardhan <jana-reddy@mediaring.com.sg> writes: > Does it breaks anythings by overwriting the dead tuples ?. Yes. You cannot do that unless you've first removed index entries pointing at the dead tuples --- and jumped through the same locking hoops that lazy vacuum does while removing index entries. regards, tom lane
Tom Lane wrote:<br /><blockquote cite="mid8875.1039581335@sss.pgh.pa.us" type="cite"><pre wrap="">Janardhan <a class="moz-txt-link-rfc2396E"href="mailto:jana-reddy@mediaring.com.sg"><jana-reddy@mediaring.com.sg></a> writes: </pre><blockquotetype="cite"><pre wrap="">Does it breaks anythings by overwriting the dead tuples ?. </pre></blockquote><prewrap=""> Yes. You cannot do that unless you've first removed index entries pointing at the dead tuples --- and jumped through the same locking hoops that lazy vacuum does while removing index entries. regards, tom lane </pre></blockquote> if i am not wrong while updating a tuple, we are also creating a new index entry . so if the<br />tuple is dead then the index entry pointing it also a dead index tuple. so even if dead index tuple is not <br /> removedthen also it should not break thing, since the dead index tuple will not be used, am i correct?.<br /><br /> whatis reason why the dead heap tuples are maintained in a linked list ?. since for every dead heap tuple there<br /> isa corresponding dead index tuple.<br /><br /> Regards<br /> jana<br />
Janardhan <jana-reddy@mediaring.com.sg> writes: > if i am not wrong while updating a tuple, we are also creating a new > index entry . Yes. > so if the > tuple is dead then the index entry pointing it also a dead index tuple. Yes. > so even if dead index tuple is not > removed then also it should not break thing, since the dead index tuple > will not be used, am i correct?. No. A process running an indexscan will assume that the index tuple accurately describes the heap tuple it is pointing at. If the heap tuple is live then it will be returned as satisfying the indexscan. regards, tom lane
Tom Lane wrote:<br /><blockquote cite="mid8875.1039581335@sss.pgh.pa.us" type="cite"><pre wrap="">Janardhan <a class="moz-txt-link-rfc2396E"href="mailto:jana-reddy@mediaring.com.sg"><jana-reddy@mediaring.com.sg></a> writes: </pre><blockquotetype="cite"><pre wrap="">Does it breaks anythings by overwriting the dead tuples ?. </pre></blockquote><prewrap=""> Yes. You cannot do that unless you've first removed index entries pointing at the dead tuples --- and jumped through the same locking hoops that lazy vacuum does while removing index entries. regards, tom lane </pre></blockquote> Does it breaks any other things if all the index entries pointing the dead tuple are removed beforereusing the dead tuple?.<br /><br /> Regards<br /> jana<br />
Janardhan <jana-reddy@mediaring.com.sg> writes: > Does it breaks any other things if all the index entries pointing the > dead tuple are removed before reusing the dead tuple?. Possibly you could make that work, but I think you'll find the efficiency advantage you were chasing to be totally gone. The locking scheme is heavily biased against you, and the index AMs don't offer an API designed for efficient retail index-tuple deletion. Of course that just says that you're swimming against the tide of previous optimization efforts. But the thing you need to face up to is you are taking what had been background maintenance tasks (viz, VACUUM) and moving them into the foreground critical path. This *will* slow down your foreground applications. regards, tom lane
Tom Lane wrote:<br /><blockquote cite="mid5876.1039765041@sss.pgh.pa.us" type="cite"><pre wrap="">Janardhan <a class="moz-txt-link-rfc2396E"href="mailto:jana-reddy@mediaring.com.sg"><jana-reddy@mediaring.com.sg></a> writes: </pre><blockquotetype="cite"><pre wrap="">Does it breaks any other things if all the index entries pointing the dead tuple are removed before reusing the dead tuple?. </pre></blockquote><pre wrap=""> Possibly you could make that work, but I think you'll find the efficiency advantage you were chasing to be totally gone. The locking scheme is heavily biased against you, and the index AMs don't offer an API designed for efficient retail index-tuple deletion. Of course that just says that you're swimming against the tide of previous optimization efforts. But the thing you need to face up to is you are taking what had been background maintenance tasks (viz, VACUUM) and moving them into the foreground critical path. This *will* slow down your foreground applications. regards, tom lane </pre></blockquote> today i could able to complete the patch and it is working only for b-tree. i have added a new methodam_delete <br /> to the API and bt_delete to the B-tree index to delete a single entry. for the timebeing thisworks only with<br /> b-tree indexs.<br /><br /> Regarding the complexity of deleting a tuple from b-tree , it is sameor less then that of<br /> inserting a tuple into a B-tree( since delete does not require spliting the page). The approachis slightly <br /> different to that of lazy vacuum. lazy vacuum scan entire index table to delete the dead entries.<br/> here it search for the pariticilar entry similer to that of insert . <br /> here locking may not have muchimpact. It locks only single buffer to delete the index entry.<br /><br /> Regarding the efficiency, if the entireIndex table is in buffered then it does not require any <br /> additional IO , only extra CPU is required to deleteentries in index table.<br /> I am using postgres in a application where is there is heavy updates for group of tables(smallsize), before inserting<br /> a single record in huge table. this entire thing constitue single transaction.currently as time goes on the transaction <br /> processing speed decreases till the database is vacuumed. <br/><br /> Using this new patch i am hoping the trasaction processing time will remain constant irrespective of time. Onlyi need to do<br /> vaccum once i delete large number of entries from some of the tables.<br /><br /> regards, jana<br/><br />