Pavan Deolasee wrote:
> - What do we do with the LP_DELETEd tuples at the VACUUM time ?
> In this patch, we are collecting them and vacuuming like
> any other dead tuples. But is that the best thing to do ?

Since they don't need index cleanups, it's a waste of
maintenance_work_mem to keep track of them in the dead tuples array.
Let's remove them in the 1st phase. That means trading the shared lock
for a vacuum-level lock on pages with LP_DELETEd tuples. Or if we want
to get fancy, we could skip LP_DELETEd tuples in the 1st phase for pages
that had dead tuples on them, and scan and remove them in the 2nd phase
when we have to acquire the vacuum-level lock anyway.

> - While searching for a LP_DELETEd tuple, we start from the
> first offset and return the first slot which is big enough
> to store the tuple. Is there a better search algorithm
> (sorting/randomizing) ? Should we go for best-fit instead
> of first-fit ?

Best-fit seems better to me. It's pretty cheap to scan for LP_DELETEd
line pointers, but wasting space can lead to cold updates and get much
more expensive.

You could also prune the chains on the page to make room for the update,
and if you can get a vacuum lock you can also defrag the page.

> - Should we have metadata on the heap page to track the
> number of LP_DELETEd tuples, number of HOT-update chains in the
> page and any other information that can help us optimize
> search/prune operations ?

I don't think the CPU overhead is that significant; we only need to do
the search/prune when a page gets full. We can add flags later if we
feel like it, but let's keep it simple for now.

> - There are some interesting issues in the VACUUMing area. How
> do we count the dead tuples ? Should we count HOT-updated
> tuples in the dead count ? If we do so, I noticed that
> VACUUM gets triggered on very small tables like "tellers"
> in pgbench and takes several minutes to finish because
> it waits very very long for VACUUM-strength lock on the
> index pages. Index is just a page or two in this case.

Yeah, that's not good. HOT updates shouldn't increase the n_dead_tuples
pgstat counter.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 2/27/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
>
> Pavan Deolasee wrote:
> > - What do we do with the LP_DELETEd tuples at the VACUUM time ?
> > In this patch, we are collecting them and vacuuming like
> > any other dead tuples. But is that the best thing to do ?
>
> Since they don't need index cleanups, it's a waste of
> maintenance_work_mem to keep track of them in the dead tuples array.
> Let's remove them in the 1st phase. That means trading the shared lock
> for a vacuum-level lock on pages with LP_DELETEd tuples. Or if we want
> to get fancy, we could skip LP_DELETEd tuples in the 1st phase for pages
> that had dead tuples on them, and scan and remove them in the 2nd phase
> when we have to acquire the vacuum-level lock anyway.


I liked the idea of not collecting the LP_DELETEd tuples in the first
pass. We also prune the HOT-update chains in the page in the first
pass, may be that can also be moved to second pass. We need to
carefully work on the race conditions involved in the VACUUM, pruning
and tuple reuse though.


> - While searching for a LP_DELETEd tuple, we start from the
> > first offset and return the first slot which is big enough
> > to store the tuple. Is there a better search algorithm
> > (sorting/randomizing) ? Should we go for best-fit instead
> > of first-fit ?
>
> Best-fit seems better to me. It's pretty cheap to scan for LP_DELETEd
> line pointers, but wasting space can lead to cold updates and get much
> more expensive.


Ok. I will give it a shot once the basic things are ready.


You could also prune the chains on the page to make room for the update,
> and if you can get a vacuum lock you can also defrag the page.


Yes, thats a good suggestion as well. I am already doing that in the
patch I am working on right now.


> - Should we have metadata on the heap page to track the
> > number of LP_DELETEd tuples, number of HOT-update chains in the
> > page and any other information that can help us optimize
> > search/prune operations ?
>
> I don't think the CPU overhead is that significant; we only need to do
> the search/prune when a page gets full. We can add flags later if we
> feel like it, but let's keep it simple for now.



I am making good progress with the line-pointer redirection stuff.
Its showing tremendous value in keeping the table and index size
in control. But we need to check for the CPU overhead as well
and if required optimize there.



Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com