Re: [Info-Ingres] Lock escalation caused by bad page estimate

Hi John,

relpages is definitly not set by an optimizedb, it is set by a modify of the table.

The relpages is printed out by the statdump as the number of pages.

I tried a modify table to table_debug with table_option=2 and counted 116 pages (ie. a lot more than 40) flagged with either a D or an A. I also examined the tids in the table to check for page number and came up with 116 distinct values. The sequence of page numbers and D's and A's matches as well.

I don't know what an 'A' page is - they clearly have data on them - any ideas?

But of course the optimiser is a *LOT* smarter than I am. It realised that the query didn't rely on data just on keys, so it only need to scan the L pages holding the Key details to do the outer join. And guess what - theres 40 of them, and those are the pages that the lock trace show are being opened.

I suspect that my lock escalation is coming when sufficient activity on the table has screwed enough with the index of the btree table to cause an overflow. I'll start tracing the activity on the table leading up to the start of lock escalation and see what happens.

Marty

________________________________

From: info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx on behalf of John Dennis
Sent: Fri 21/09/2007 03:16
To: info-ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [Info-Ingres] Lock escalation caused by bad page estimate



Marty,

As far as I can see for "normal" tables (not partitioned) although the
relpages attribute on iirelation is updated, it is updated to the
value it previously held. I just set relpages on a table to a dummy
value (1111), ran optimizedb, and the statdump showed the page count
to be 1111.

Are you able to run this again, printing off relpages from iirelation
before and after the optimizedb?

John

On Sep 20, 5:29 pm, "Martin Bowes" <martin.bo...@xxxxxxxxxxxxx> wrote:
Hi John,

II 9.0.4 (a64.lnx/105)NPTL + patch 12343

Marty

-----Original Message-----
From: info-ingres-boun...@xxxxxxxxxxxxxxxxxxxxxxxxx

[mailto:info-ingres-boun...@xxxxxxxxxxxxxxxxxxxxxxxxx] On Behalf Of John
Dennis
Sent: 20 September 2007 05:11
To: info-ing...@xxxxxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [Info-Ingres] Lock escalation caused by bad page estimate

Standard support question - what version of Ingres?

John

On Sep 20, 12:57 am, "Martin Bowes" <martin.bo...@xxxxxxxxxxxxx>
wrote:
Hi Everyone,

I have a uniquely keyed btree table with 158pages. Ithas all the
defaullt fillfactors etc. I have freshly modified it, I have rebuilt
the
stats on it.

Why does the optimiser think there are only 40 pages in it?

Sadly, because it doesn't think there are more than maxlocks (50)
pages,
it goes into page reading and then has to escalate. Which is filling
the
errlog with escalation messages.

Marty

_______________________________________________
Info-Ingres mailing list
Info-Ing...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx://www.kettleriverconsulting.com/mailman/listinfo/info-ingres


_______________________________________________
Info-Ingres mailing list
Info-Ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres