3

MySQL sometimes generates bad execution plan irregularly.

2 years ago, I have increased innodb_stats_sample_pages(default:8) up to 64 and recently changed into 128 because Data Volume has been increased for 2 years.

I think 128 is still small. (128 pages * 16KB = Only 2MB. Am I right?)

Is it ok to increase it up to 1024 or higher? What are the points to consider?

How large value of pages are you using?

4
  • Can you examine some specific query and good/bad plan? Maybe some of your indexes are on low-cardinality columns. MariaDB can enable "histogram based statistics" but I don't know if MySQL has it maybe under some other name..
    – jkavalik
    Commented Jun 5, 2015 at 6:56
  • @user1786423 Thank you for the comment. InnoDB manages index statistics with random sampling approach (It read innodb_stat_sample_pages pages randomly). So, if randomly selected pages are poor, MySQL's optimizer generates different plan for a same query. That's why I would like to increase innodb_stats_sample_pages.
    – Jason Heo
    Commented Jun 5, 2015 at 7:04
  • 1
    Well, yes, but InnoDB statistics are only estimates "by design" so optimizer expects it to not be really exact. This was more about that maybe your set of indexes is not very good for gathering statistics - to find out where it happens, if it is single column on single table or some general problem. Just commenting because that is no answer to your question but different thing to try. About innodb_stat_sample_pages I can say that I tried something like 256 and it did not actually change anything on table with "bad" data. And if you make it too big, everything will slow down.
    – jkavalik
    Commented Jun 5, 2015 at 7:13
  • @user1786423 Thank you for the heads up ;)
    – Jason Heo
    Commented Jun 5, 2015 at 7:18

1 Answer 1

1

Changes to the statistics has plagued InnoDB since the beginning. Fortunately it affects only a small percentage of queries.

Upgrade to a recent 5.6. There is a significant improvement wherein the stats are no longer recomputed whenever you sneeze. It is now essentially possible to set them once and forget them. The number of "random dives" and which algorithm is used is now less important.

As mentioned, MariaDB (and eventually 5.7) has fancier statistics.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.