Re: Locally Managed Tablespaces ... again!!!

<ctcgag_at_hotmail.com> wrote in message
news:20030121211936.349$h1_at_newsreader.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:
> > <ctcgag_at_hotmail.com> wrote in message news:20030120195527.382> Actually,
> > it appears it would be for AGRINDEX and AGRINDEX4, as this whole
> > > threadlet has been on the topic of write contention, not read
> > > contention.
> > >
> >
> > Nope.
>
> I joined this discussion when you said "As was done to death here a while
> back, it's simply not true. When a table is updated, the index maintenance
> activities are *serialized*, so that they take place after the table
> update," and that is what this subthread has been about. While I have
> seen separation of indices and tables done to death several month ago,
> including bits in the inherently serial access to tables and indices, I
> have seen nothing on the inherently serial writing of data and indices,
> which is what my questions revolve around.
>
>
> > This thread has been about a simple question, which you won't
> > answer. Why should tables and indexes have some kind of special
> > relationship regarding read OR write contention?
>
> Because blocks from any random pair of segments are dirtied together only
> by accident, while blocks from a table and index are dirtied together by
> design. p(accident) < p(design).

Step back a bit. Consider a multi-user system. I dirty a block of EMP, you dirty a block of SALES. That's not an accident: that's what databases *do*! Those are 'random' segments. Now you update EMP and also update PK_EMP, the index. That's another two segments. What does DBWR now do (assuming there's now a flush of some sort?). It writes EMP's dirty buffers to disk, SALES dirty buffer, and PK_EMP's dirty buffer. So it writes four dirty buffers. Big deal: did DBWR say to itself 'now this buffer is related to that one'? No it didn't. So unless you can explain why 4 dirty buffers being flushed to disk should give rise to a special contention issue, the fact that one's an index buffer and 3 aren't is irrelevant.

Now, of course: there *might* be contention because you're using naff old Quantum hard disks that rotate at about 1 spin an hour, and it can't cope with four dirty buffers at once. But that's a generic contention issue, resolvable by spreading I/O across more disks, better disks, or multiple DBWRs on multiple disks. It's still nothing to do with the fact that one of those buffers belongs to an index.

DBWR doesn't *know* what it's flushing. A dirty buffer is a dirty buffer.
>
>
> > Tell me. Explain to me the mechanism.
>
> I have. Over and over again.

No you haven't. You just keep asserting that the fact that a dirty of the table buffer implies or requires a dirty of the index buffer somehow causes write contention, and there's no reason that is so. If DBWR can cope with writing two dirty buffers at once, no special contention arises.

A table and its index is no more specially related, fundamentally, than a parent table and its child.

>
> > Show me how that which is
> > inherently serial can suddenly engender especial amounts of parallelized
> > contention.
>
> This is what you have refused to answer. I accept that the access of
> index and data is inherently serial from within any one query. I do not
> yet accept that the writing of blocks from index an data is inherently
> serial. Why is the writing of blocks from data and index inherently
serial?

You have a dirty queue. DBWR flushes it. Whatever is on it. To wherever it needs to go. If they all end up on the same disk, then with naff hardware and other configuration disasters, you might well get contention at that point. But that's a function of writing lots of buffers to disk (the same disk), not a result of some of those buffers being index ones and some being table ones.

>
> > You admitted DBWR doesn't give a damn what sort of buffer it flushes. So
> > how the hell do index buffers and table buffers suddenly start to
contend
> > on the write?
>
> Because they are there.

I simply don't understand that response. What's there? Dirty buffers? Yup. And DBWR flushes them. Might there be contention? Yup, certainly. Does it arise from some intrinsic nature of those buffers? No. A buffer is a buffer.

> > I'll shut up if you explain one plausible mechanism to explain what you
> > are pushing as a read/write anomaly: reads don't contend. But writes
> > suddenly do.
>
> The DBWR has to write what is in the dirty list and/or found at the LRU.
>
> I submit 960 samples to the DNA sequencing facility, so I supply the
> application with all the necessary data, and I hit the "submit" button.
> So 300 seqfac_todo blocks get dirtied, and 500 seq_fac_todo_idx_* blocks
> get dirtied, and these are next to each other in the cache list with maybe
> a few blocks from HR thrown in. No?

No. I don't know what 'next to each other in the cache list' means, but it is not intrinsically so in any case. You mean the LRU? So what? You've 800 buffers that need to be flushed. Either DBWR and your hardware copes, or it doesn't. I could argue that my application updates 300 customer records and 500 sales records for those customers. I've got 800 buffers too. Either my DBWR copes, or it doesn't.

>
> A second later, someone hits "Submit" on 80 samples they set up for the
> NMR facility. 300 nmrfac_todo blocks and 240 nmrfac_todo_idx_* blocks are
> dirtied, and next to each other in the cache list with maybe a few from
> requisition thrown in.
>
> Now a memory hungry process comes along, runs through whatever else we
> had at LRU and knocks up 700 blocks of the dirty bloack to the dirty list.
> These are almost all going to seqfac_todo and seqfac_todo_idx_*, and so
> DBWR has to put them there. If that's on the same spindle, I don't see
how
> you avoid the contention. A few seconds later, another memory hungry
> process knocks up more blocks to the dirty list, and these are mostly
going
> to nmrfac_todo and nmrfac_todo_idx_*.

But you've missed the point again. You're talking about a lot of writes to one spindle causing contention. Absolutely. But does that arise, inherently, from the fact that one's an index and one's a table. You just happen to have two segments being modified. I could show you an application where two tables get modified at the same time.

You are correct that slamming lots of dirty buffers onto one spindle could cause contention. I've never argues otherwise. So you use RAID, or you use multi-file tablespace, or you create separate tablespaces for your different segments (tables from tables, tables from rollback segments, tables from indexes if you like). Point is, if you have contention, there are dozens of ways of dealing with it. But you need to know what is contending with what, and separate accordingly. Just saying 'indexes and tables' doesn't cut it. It's broadbrush stuff that isn't generally true.
>
> > ...Because you have a database to manage, and you don't
> > want to admit that maybe, just maybe, you've been doing it wrong all
> > these years. Me- I'm "only" theory. So what do I know?? Nothing really.
>
> Wow. I am amazed at how wrong you are here. It makes me wonder if you
> actually read any of my posts you have been responding to.

And where would I have found what follows in any of your previous posts? Incidentally, there should have been a question mark after the words "all these years".

And it would appear that I was directing my ire at the wrong person in any case, since the last two clauses were in response to an earlier post in this thread from someone completely different, where he wrote "Accept that sometimes your view is bound to be ivory-tower." I apologise for directing at you what should have been directed elsewhere.

Regards
HJR
> I don't have
> a database to manage, except a lap-top demo version. I haven't been doing
> it wrong, because I haven't been doing it all. The storage for the
> real databases are behind the IT curtain, I wouldn't know what spindle
> or how many spindles any thing is on, and even the DBA of record wouldn't
> know this. I'm almost only theory, and new to this, and as I have said I
> my interest in how to tune a database that isn't SAME is only pedagogical.
> I say *almost* only theory because every now and then I run into a
problem,
> take a theoretical look at it, recommend a change, and--if I can convince
> the DBA with years of experience to listen to me--usually solve the
> problem.
>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service New Rate! $9.95/Month 50GB
Received on Wed Jan 22 2003 - 01:48:16 CST