Re: MVCC overheads

On 7 July 2016 at 20:50, Pete Stevenson <etep(dot)nosnevets(at)gmail(dot)com> wrote:

> Hi Simon -
>
> Thanks for the note. I think it's fair to say that I didn't provide enough
> context, so let me try and elaborate on my question.
>
> I agree, MVCC is a benefit. The research angle here is about enabling MVCC
> with hardware offload. Since I didn't explicitly mention it, the offload I
> refer to will respect all consistency guarantees also.
>
> It is the case that for the database to implement MVCC it must provide
> consistent read to multiple different versions of data, i.e. depending on
> the version used at transaction start. I'm not an expert on postgresql
> internals, but this must have some cost. I think the cost related to MVCC
> guarantees can roughly be categorized as: creating new versions (linking
> them in), version checking on read, garbage collecting old versions, and
> then there is an additional cost that I am interested in (again not
> claiming it is unnecessary in any sense) but there is a cost to generating
> the log.
>
> Thanks, by the way, for the warning about lab vs. reality. That's why I'm
> asking this question here. I want to keep the hypothetical tagged as such,
> but find defensible and realistic metrics where those exist, i.e. in this
> instance, we do have a database that can use MVCC. It should be possible to
> figure out how much work goes into maintaining that property.
>

PostgreSQL uses a no overwrite storage mechanism, so any additional row
versions are maintained in the same table alongside other rows. The MVCC
actions are mostly mixed in with other aspects of the storage, so not
isolated much for offload.

Oracle has a different mechanism that does isolate changed row versions
into a separate data structure, so would be much more amenable to offload
than PostgreSQL, in its current form.

Maybe look at SLRUs (clog etc) as a place to offload something?

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services