Thread: On the usefulness of hint bits
Hi, I was wondering what is the advantage of having hint bits for OLAP -style workloads, that is when the number of transactions is not that high. If I got it right, in 10 pg_clog pages we can store the status for more than 320000 transactions. That's a lot, in a very small space (80KB?). So I was wondering what's the gain we get from hint bits in cases where pg_clog is "small" (that is, will be cached by postgresql/the OS). does somebody have some numbers on the effect of hint bits on first/second reads? I mean: create mytable as ..... select * from mytable -> this one will update hint bits select * from mytable -> this one will use them to test it I guess we should change the code to have: a version where they are never updated (that is, always look at pg_clog) (so that you don't have to write them the first time, and you'll get the "true" reading time + pg_clog reading time) a version that always set them to COMMITTED (so that you don't have to write them the first time, and you'll get the "true" reading time for the "second" read that would use them, regardless of any pg/OS cache) I'm asking because I don't like having all those writes on the first scan... and I would like to know what the real benefit is for the reads that come after the first one in case there are "few" transactions per second (for example, less than 1 transaction per second)
Leonardo Francalanci <m_lists@yahoo.it> writes:
> I was wondering what is the advantage of having hint bits for OLAP
> -style workloads, that is when the number of transactions is not
> that high.
> If I got it right, in 10 pg_clog pages we can store the status for more
> than 320000 transactions. That's a lot, in a very small space
> (80KB?).
> So I was wondering what's the gain we get from hint bits in cases
> where pg_clog is "small" (that is, will be cached by postgresql/the
> OS).
Reduction of contention for pg_clog access, for one thing. If you read
the archives, you'll find that pg_clog access contention has been shown
to be one cause of "context swap storms". Having to go to clog for
every single tuple access would make that orders of magnitude worse.
More generally, we're not going to give up hint bits even if there are
identifiable workloads where they don't buy much --- because there are
many others where they do.
regards, tom lane
> Reduction of contention for pg_clog access, for one thing. If you read > the archives, you'll find that pg_clog access contention has been shown > to be one cause of "context swap storms". Having to go to clog for > every single tuple access would make that orders of magnitude worse. Ok; is it the "Wierd context-switching issue on Xeon" thread? Or that has nothing to do with it? I tried "context swap storms pg_clog" but I didn't get anything... > More generally, we're not going to give up hint bits even if there are > identifiable workloads where they don't buy much --- because there are > many others where they do. Sure: I wasn't suggesting to give up them, just to make their usage "user selectable" (and "on" by default)
On Mon, Oct 11, 2010 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Reduction of contention for pg_clog access, for one thing. If you read > the archives, you'll find that pg_clog access contention has been shown > to be one cause of "context swap storms". I wonder if we could improve this with some sort of process-local cache - not to get rid of hint bits, just to reduce pg_clog contention. We might easily end up testing the same XID many times during the same table scan. Another idea that's been discussed before is to avoid writing out pages when only the hit bints have changed. Or perhaps to write them out from the background writer only, but not from backends and not when checkpointing - have a state BM_UNTIDY, which the background writer cleaning scan will treat as dirty, but which can otherwise be treated as "not dirty", so that if we start to run short of free buffers we don't hold things up writing out the hint bit updates. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> I wonder if we could improve this with some sort of process-local > cache - not to get rid of hint bits, just to reduce pg_clog > contention. We might easily end up testing the same XID many times > during the same table scan. I guess that's my scenario... not that many transactions, so even "copying" the whole pg_clog table in the per-process memory would be doable...
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Oct 11, 2010 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Reduction of contention for pg_clog access, for one thing. �If you read
>> the archives, you'll find that pg_clog access contention has been shown
>> to be one cause of "context swap storms".
> I wonder if we could improve this with some sort of process-local
> cache - not to get rid of hint bits, just to reduce pg_clog
> contention. We might easily end up testing the same XID many times
> during the same table scan.
There already is a one-entry cache --- see TransactionLogFetch. Not
sure if making it bigger would be a win in current usage, although
you'd likely have to if you were trying to not set hint bits.
> Another idea that's been discussed before is to avoid writing out
> pages when only the hit bints have changed.
Yeah.
regards, tom lane