Comments on: Fun with innodb stats

By: Big DBA Head! - Database Brain Power! » Patch to Disable Innodb’s Dynamic Stat Generation

Sat, 28 Feb 2009 03:43:14 +0000

[...] Fun with innodb stats [...]

By: matt

matt — Thu, 26 Feb 2009 19:20:45 +0000

I have got a hack that shuts down the background stats collection ( with the exception of when you run analyze ) … but its sort of flaky, so I am trying to figure out why before I push out to launchpad.

By: KevinF

KevinF — Thu, 26 Feb 2009 19:17:59 +0000

yes please, a table allowing us to set the stats the way we want them would be wonderful. Imagine being able to set stats to reflect a terabyte DB when you have a 100MB test environment? App Devlopers could much better gauge their code’s performance.

Maybe the XtraDB folk want to take on something like this. But yeah, shutting off the background threads would be nice. I hate surprises in the night when stats go awry.

By: Shantanu Oak

Shantanu Oak — Thu, 26 Feb 2009 07:56:28 +0000

Try using “select straight_join” syntax to help the optimizer select the best index plan while joining tables.

By: Mark Callaghan

Mark Callaghan — Tue, 24 Feb 2009 18:13:09 +0000

In the big Google patch we have code to set the number of samples via a SET command. But I prefer a better ugly hack.

By: matt

matt — Tue, 24 Feb 2009 18:07:01 +0000

I agree a table or a way to shutoff stats per table would be great… Been looking at this, an easy but not as useful patch here would be to allow you globally shutoff stats collection ( would have to allow it if no stats are set ) and then allow a global var to set the page depth used by the stats update. I think that could be relatively easy to put in.

By: Mark Callaghan

Mark Callaghan — Tue, 24 Feb 2009 17:35:55 +0000

The hack that I want is a stats table in the mysql database into which table-index stats can optionally be added. Then a background job can update the table as needed and changes will propagate to slaves.

Bad stats cause bad query plans for us all of the time. This is a huge problem and today’s workaround is to put hints into too many queries to force a good join order.