tag:blogger.com,1999:blog-1508669603650457962.post5594235038663080102..comments2022-11-09T11:53:03.614+01:00Comments on Øystein on MySQL Optimizer: Run ANALYZE TABLE — Do Not Rely on InnoDB's Automatic Recalculation of Statistics Øysteinhttp://www.blogger.com/profile/11287151295676613909noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-1508669603650457962.post-56170104806730733842021-09-09T20:43:41.296+02:002021-09-09T20:43:41.296+02:00Øystein, I do not think that anything was changed ...Øystein, I do not think that anything was changed about that part yet; in fact, I was unaware of these problems before your blog post. Feel free to submit a pull request. :-)Marko Mäkelähttps://www.blogger.com/profile/01918141282242941322noreply@blogger.comtag:blogger.com,1999:blog-1508669603650457962.post-61903003633477534492021-09-09T18:19:40.700+02:002021-09-09T18:19:40.700+02:00Hi Marko,
Thanks for the comment. I did not men...Hi Marko, <br /><br />Thanks for the comment. I did not mention FLUSH TABLES because, AFAIU, then you will get the same blocking behavior that used to be a problem with ANALYZE TABLE.<br /><br />It is not clear to me if/how the recent changes to MariaDB makes sure that the automatically updated index statistics is visible to the server.Øysteinhttps://www.blogger.com/profile/11287151295676613909noreply@blogger.comtag:blogger.com,1999:blog-1508669603650457962.post-14948880584135147012021-09-09T06:57:57.426+02:002021-09-09T06:57:57.426+02:00Thank you for a great article. One thing that you ...Thank you for a great article. One thing that you did not mention is that the FLUSH TABLES statement clears the table definition cache of the SQL layer (but not in InnoDB). In InnoDB, whenever the table reference count reaches 0, a flag will be cleared so that next time the table is going to be opened, the persistent statistics will be read from the tables. I understood that the intended use of this is to make the DBMS aware of manual changes to the InnoDB statistics tables.<br /><br />In the upcoming MariaDB Server 10.6.5 release, I hope that all hangs and race conditions related to InnoDB persistent statistics are fixed. MDEV-25919 was about a temporary shortcut that was taken when we implemented crash-safe DDL: https://mariadb.com/resources/blog/crash-safe-ddl-of-innodb-tables-in-mariadb-server-10-6/Marko Mäkelähttps://www.blogger.com/profile/01918141282242941322noreply@blogger.com