Indeed, I fixed some of that with them not being updated for SHOW /
I_S quieries, however there are other times when statistics are auto-
generated again internally:
o When the table is first opened (because of them not being persistent)
o When the total number of rows in the table changes by 1/16th since
the last analyze
o When the table size changes by 2 billion rows (I think?) (if before
the 1/16th rule)
I'd love to see the others dealt with as well - and I also wonder
whether a percentage based, rather than absolute value based variable
would be better for setting the number of dives to do as well..
Mark
On 31 Jul 2009, at 14:40, [email protected] wrote:
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
--
Mark Leith
MySQL Regional Support Manager, Americas
Sun Microsystems, Inc., http://www.sun.com/mysql/
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
