tag:blogger.com,1999:blog-1508669603650457962.post8091655527100412193..comments2022-11-09T11:53:03.614+01:00Comments on Øystein on MySQL Optimizer: More Stable Query Execution Times by Improving InnoDB StatisticsØysteinhttp://www.blogger.com/profile/11287151295676613909noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-1508669603650457962.post-53577754630161035302011-05-05T13:17:44.786+02:002011-05-05T13:17:44.786+02:00@Philip Stoev: In order to further reduce the num...@Philip Stoev: In order to further reduce the number of plans, I switched to using InnoDB persistent statistics which is a new feature in the recent 5.6 milestone release. See http://oysteing.blogspot.com/2011/05/innodb-persistent-statistics-save-day.html for more details.Øysteinhttps://www.blogger.com/profile/11287151295676613909noreply@blogger.comtag:blogger.com,1999:blog-1508669603650457962.post-14053664585354302712011-04-21T19:34:29.586+02:002011-04-21T19:34:29.586+02:00Oysten, I was able to repeat your findings using D...Oysten, I was able to repeat your findings using DBT-3 scale 1 . Even with innodb_stats_sample_pages = 512, three query plans remain, out of which two have a 60/40 chance of being picked.<br /><br />What is your solution for further reducing the number of plans?Philip Stoevhttps://www.blogger.com/profile/02557232473343964722noreply@blogger.comtag:blogger.com,1999:blog-1508669603650457962.post-75300789900105870402011-04-19T21:25:00.932+02:002011-04-19T21:25:00.932+02:00@rshuddleston: No, there were no NULL values.
Pers...@rshuddleston: No, there were no NULL values.<br />Persistent InnoDB stats are available in the new MySQL 5.6.2 milestone release. See http://blogs.innodb.com/wp/2011/04/innodb-persistent-statistics-at-last/<br /><br />@Mark Callaghan: Thanks, for sharing your experience with InnoDB statistics. It is true that increasing the sampling size, may give some unwanted side effects. I think your issues are solved with the new persistent statistics in 5.6 since that gives the user full control over when the statistics are updated.Øysteinhttps://www.blogger.com/profile/11287151295676613909noreply@blogger.comtag:blogger.com,1999:blog-1508669603650457962.post-12140152093087277022011-04-18T04:15:23.380+02:002011-04-18T04:15:23.380+02:00Prior to the MDL changes in MySQL 5.5, sampling mo...Prior to the MDL changes in MySQL 5.5, sampling more pages can be a disaster on mysqld restart as sampling is done on table open and that is serialized by LOCK_open.<br /><br />Sampling after too many updates can still cause stalls for that table in 5.5+. The facebook patch has a change to let other queries on the table proceed while the updates stats are collected.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.comtag:blogger.com,1999:blog-1508669603650457962.post-45766259501917522292011-04-16T19:24:06.660+02:002011-04-16T19:24:06.660+02:00Did the column with the variable rows per key have...Did the column with the variable rows per key have a lot of NULL values? If so we currently get around this by using the innodb_stats_method=nulls_ignored in xtradb. The other approach we take is increase the number of samples taken and persist the stats between restarts with innodb_use_sys_stats_table (also an xtradb feature).Unknownhttps://www.blogger.com/profile/18362125097172509336noreply@blogger.com