Right. I wrote that doc myself :-)
The commenter below said:
there's any way to improve the sampling, or optionally disable it,
that'd be a big help
I was responding to that. There are really two different problems:
lack of persistence, and stats updated as acresult of implicit
actions. Analyze should (optionally) be the only way to update stats.
Ken
Sent from my iPhone
On Jul 31, 2009, at 7:28 AM, Jeremy Zawodny <[email protected]> wrote:
Ken,
I think the main issue here is described in the InnoDB docs:
"You should note that it does not make sense to increase the index
sample size, then run ANALYZE TABLE and decrease sample size to
attempt to obtain better statistics. This is because the statistics
are not persistent. They are automatically recalculated at various
times other than on execution of ANALYZE TABLE. Sooner or later the
“better” statistics calculated by ANALYZE running with a high
value of innodb_stats_sample_pages will be wiped away."
"Although it is not possible to specify the sample size on a per-
table basis, smaller tables generally would require fewer index
samples than larger tables require. If your database has many large
tables, you may want to consider using a higher value for
innodb_stats_sample_pages than if you have mostly smaller tables."
So we have control over how hard InnoDB tries when it does try but
we don't have any control over when it decides or how it decides to
try (though as of 1.0.2 we can disable it for interactive users--
does Drizzle even differentiate between them?). That can lead to
some uneasy situations when you've had to increase the variable
significantly (very large table) but you don't want that sampling to
occur
Jeremy
On Fri, Jul 31, 2009 at 12:55 AM, [email protected] <[email protected]
> wrote:
There us in the InnoDB Plugin a parameter to control the number of
index dives, and a bug fix for 64-bit machines regarding sampling.
Chech the doc on InnoDB.com.
Ken
Sent from my iPhone
On Jul 31, 2009, at 1:46 AM, dormando <[email protected]> wrote:
I tend to issue this about once a week against production... Usually
if
something flips around more than once we push out a USE INDEX hint and
bury it though.
If there's any way to improve the sampling, or optionally disable it,
that'd be a big help. Systems like TheSchwartz tend to go from empty
to
full in a weird way and back again often, which break the query plan
almost consistently.
-Dormando
On Thu, 30 Jul 2009, Brian Aker wrote:
Hi!
What experiences have people had with ANALYZE TABLE? Tinkering
around with it
I am wondering if anyone ever uses this with Innodb with any
satisfaction.
Cheers,
-Brian
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
