db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <kristian.waa...@oracle.com>
Subject Re: Question on why indexStat deamon is being triggered
Date Mon, 02 Apr 2012 14:05:49 GMT
On 02.04.12 14:28, Bergquist, Brett wrote:
> I did debug this yesterday and see what happens but don't know how to fix it yet.   When
the problem occurs and "updateIndexStatsMinion" is invoked, there are 3 entries in the "statisticsDescriptorList"
property (two are valid and one is the invalid one) and 3 entries in "conglomerateDescriptorList"
of the TableDescriptor, but one of those is for the table conglomerate itself and skipped
in processing.   So the statistics are rewritten for the 2 index conglomerates, but the invalid
one is missed.
>
> So if the code were to detect this and remove the invalid one, the problem clears up.
  I did hack in forcefully removing all statistics for the table regardless of the index and
this in fact did clear up the problem, but it needs to be made more elegant that this.  I
believe that the "updateIndexStatsMinion" is only called with the conglomerates that needs
statistics updating, so removing all of the statistics at the start is probably not good.
 Maybe comparing the entries in  the "statisticsDescriptorList" with the index conglomerates
of the table and removing the statistics for any that don't have a corresponding index conglomerate
is a valid solution.

Hi Brett,

Just attached a prototype patch to DERBY-5680. It may need some more 
work. The approach is what you suggest above. I'm only dealing with the 
statistics for the base table being processed - a broader fix could 
maybe be added to the update phase if someone feels that is appropriate.

NOTE: I haven't properly tested this patch yet (I also did some 
last-minute changes before posting...).


-- 
Kristian

>
> Brett
> ________________________________________
> From: Kristian Waagan [kristian.waagan@oracle.com]
> Sent: Monday, April 02, 2012 3:52 AM
> To: derby-dev@db.apache.org
> Subject: Re: Question on why indexStat deamon is being triggered
>
> On 31.03.12 22:28, Mike Matrigali wrote:
>> Bergquist, Brett wrote:
>>> I hitched up the debugger and caught the indexStat return "49" for the
>>> number of rows. I walked the stack back and found this was for
>>> conglomeratename = "f3ec4922-011d-491f-3d8c-0000376d74d3"
>>>
>>> So I did this query:
>>>
>>> select
>>> c.TABLEID,
>>> c.CONGLOMERATENUMBER,
>>> c.CONGLOMERATENAME,
>>> c.ISINDEX,
>>> c.ISCONSTRAINT,
>>> c.CONGLOMERATEID,
>>> t.TABLEID,
>>> t.TABLENAME,
>>> t.TABLETYPE,
>>> s.STATID,
>>> s.REFERENCEID,
>>> s.TABLEID,
>>> s.CREATIONTIMESTAMP,
>>> s.TYPE,
>>> s.VALID,
>>> s.COLCOUNT,
>>> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
>>> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID =
>>> t.TABLEID join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID where
>>> c.CONGLOMERATENAME = 'f3ec4922-011d-491f-3d8c-0000376d74d3'
>>>
>>> Which is for one of the tables that I am having problems with. This
>>> returned:
>>>
>>> TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT
>>> CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID TABLEID
>>> CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 30768
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 false false
>>> 0e34c923-011d-491f-3d8c-0000376d74d3
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 BC01_CONFIGURATION_SET T
>>> 645c405f-0136-6999-c1b4-000065089f97
>>> 2c5f8294-012b-3c38-b55c-000043ea6398
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 2012-03-31 12:50:05.348 I true 1
>>> numunique= 20334 numrows= 20334
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 30768
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 false false
>>> 0e34c923-011d-491f-3d8c-0000376d74d3
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 BC01_CONFIGURATION_SET T
>>> 49fc4ea2-0129-489a-0a26-00000732b350
>>> 592fcc26-011d-491f-3d8c-0000376d74d3
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 2010-06-17 23:16:40.018 I true 1
>>> numunique= 49 numrows= 49
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 30768
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 false false
>>> 0e34c923-011d-491f-3d8c-0000376d74d3
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 BC01_CONFIGURATION_SET T
>>> 2c44c05e-0136-6999-c1b4-000065089f97
>>> e9a40921-011d-491f-3d8c-0000376d74d3
>>> f3ec4922-011d-491f-3d8c-0000376d74d3 2012-03-31 12:50:03.427 I true 1
>>> numunique= 20334 numrows= 20334
>>>
>>> So there seems to be 3 statistics rows for this table. And low and
>>> behold there is the "numrows = 49" which I see the indexStat when I
>>> turn on tracing.
>>>
>> First thought is I didn't think we needed any statistics at all
>> for a single column primary key.
>
> That's probably DERBY-3790 ([1]).
> I'll see if I can write a quick patch proposal and then we can take it
> from there.
>
>>
>> There should be entries in this catalog for every index on a table, so
>> it is not necessarily expected to be one per table. I think for each
>> index there is an entry for each column in the index (actually the stat
>> applies to multiple columns so in a 3 column index I think there is an
>> entry for (col1), (col1,col2), and (col1, col2, col3). But looking at
>> your ddl I would only expect 1 entry, as there is one
>> index on the table, and it has one column. I am not sure what the
>> expectation is for the foreign key that matches the primary key, maybe
>> it is 2 rows?
>>
>> I do wonder if the foreign key is causing some extra issues. I think
>> in the long past we would end up creating 2 indexes in this case and
>> they would be exactly the same. So functionality was added to just
>> "logically" create the index and it would rely on the underlying
>> physical index sort of "shared" by the primary key on id and the foreign
>> constraint on id. We have had bugs in this area in the past and wonder
>> if it left an "orphan" row in the statistics.
>
> As mentioned in another post, I think there's an easy way to avoid
> scanning the same index twice, but I need to make sure the check is
> valid first.
> I'm thinking of simply checking if we've seen the index conglomerate
> number already, and if so, ignore that index. The question is, can two
> truly different indexes be represented by the same conglomerate number?
>
>>
>> Interesting that the second column it looks like it was created in 2010.
>>
>> seems like there might be multiple problems here. It would be great to
>> figure out how the multiple rows got in there in the first place. I
>> would suggest seeing what the system does for ddl in a brand new
>> database, turn off index stat thread, run your ddl, add some rows to the
>> table, and hand run update statistics procedure.
>
> Just for information, the istat daemon and the system procedure share
> almost all of the core code at the moment. The addition of automatic
> updates of index cardinality statistics consisted mainly of adding the
> "daemon framework" and triggering logic.
>
>>
>> I would not be surprised if existing code does not expect the number of
>> rows you are seeing. If this is really a buggy orphan row, probably best
>> zero admin fix is to change the update statistics code to look for
>> other rows and delete them.
>> you can follow the update statistic code starting at:
>> java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction/updateStatistics
>>
>>
>> There may be a index stat daemon bug here too, but I think it might just
>> be getting confused by the bad data in the catalog. Maybe it just keeps
>> reading all the rows and queueing work, but calling update statistics
>> only update 2 of the 3 rows.
>
> Yes, the triggering code is driven by the existing statistics for the
> table, whereas the update code is driven by the list of indexes on the
> table.
>
>
> --
> Kristian
>
> [1] https://issues.apache.org/jira/browse/DERBY-3790
>
> <  snip>
>
>


Mime
View raw message