On 24.05.11 14:17, Bergquist, Brett wrote:
Okay, so the " automatic update doesn't kick in until the table contains at least
100 rows" was the information that I was missing. Is that somewhere I the release
notes or documentation?
Hi Brett,
No, by choice, this hasn't been documented properly yet.
Getting this feature right is kind of hard, as you have to balance the
usage of resources for statistics updates and user load. Different users
may also have very different expectations.
The release notes only mention how to disable the feature and how to
turn on logging. The community felt it would be useful to get some
feedback from users before writing more documentation [1]. You have now
given us an indication that users may want more information about how
the statistics updates are triggered. Thanks!
In addition to the undocumented knob Knut Anders mentioned, there are
three more:
o derby.storage.indexStats.trace (default="none")
o derby.storage.indexStats.debug.absdiffThreshold (default=1000)
o derby.storage.indexStats.debug.lndiffThreshold (default=1.0)
These may very well be removed in the next Derby release. If people find
that they need to use the undocumented knobs above, please let the
community know.
All kinds of feedback regarding this feature is welcome.
FYI, the current triggering mechanism is based on prepared statements
query compilation (only SELECTs and for queries using indexes) and row
estimates. Using row estimates alone may be insufficient for some types
of loads. If you don't prepare your statements, you probably should ;)
Regards,
--
Kristian
[1] May seem strange, but this is supposed to be a ~zero admin feature.
In any case, this feature is greatly appreciated! It will make the database
much more zero administration which my situation requires. Thanks!
Brett
-----Original Message-----
From: Knut Anders Hatlen [mailto:[email protected]]
Sent: Tuesday, May 24, 2011 3:30 AM
To: [email protected]
Subject: Re: Question on automatic statistics feature in 10.8.2.1
"Bergquist, Brett"<[email protected]> writes:
I have been testing with the new automatic statistics feature in
10.8.2.1 and turned on the logging and see it being triggered as I
make changes to my database. So this appears to be working. What I
am surprised at however, is that I have tables in my database that
have out of date statistics (none) as reported by this query:
[...]
The tables start empty but with indexes and then data gets added
later. I thought that probably the new automatic statistics feature
would be triggered on a query of these tables but it does not seem to
be. If the table already has statistics they seem to be updated. I
could be wrong however and maybe my query is not sufficient to trigger
statistics update but I did do a query for a specific value of primary
key and saw nothing in derby.log.
What's the size of the table? The automatic update doesn't kick in until
the table contains at least 100 rows (the threshold can be tuned with
the undocumented property derby.storage.indexStats.debug.createThreshold).
There's also a possibility that the query you executed wasn't actually
compiled, but just fetched from the statement cache. Derby doesn't check
that the statistics are up to date on every execution. By default, it
checks after 100 executions whether a recompile is necessary, and it's
during the recompile the statistics update is scheduled. To eliminate
this as the cause, you could call
syscs_util.syscs_empty_statement_cache() before you execute the query
against the table, and see if the statistics get created then.
