We have migrated a few large databases from earlier version of 10.1.22 to 10.6.7 - the latest provided by ubuntu repo for 22.04. All was well for a few weeks and then we started seeing very slow queries and some that never return. These are all fixed immediately by calling ANALYZE table on the offending tables used by that query. At least, then, we can be certain that the problem lies squarely within whatever ANALYZE fixes.
I'm no expert in exactly how this helps. Why do I need to call ANALYZE table now when I didn't need to call it previously? Google says that the statistics are updated automatically when more than a set percentage of the table changes. Well, mine don't. In fact, if I call SELECT * FROM mysql.innodb_table_stats;
you can clearly see that it hasn't bothered to update anything since the migration unless I told it to. My config is largely on defaults and there are no entries related to stats.
The upgrade consisted of shutting down cleanly - copying to new server then starting up, at which point it upgraded the tables automatically and was seemingly trouble free. Its also running in a cluster of 3 nodes.
Just in the case that I have somehow borked the config - here are the variables related to 'stats' - these should be on default as our config doesn't have anything clever in it:
innodb_defragment_stats_accuracy......... 0 innodb_stats_auto_recalc................. ON innodb_stats_include_delete_marked....... OFF innodb_stats_method...................... nulls_equal innodb_stats_modified_counter............ 0 innodb_stats_on_metadata................. OFF innodb_stats_persistent.................. ON innodb_stats_persistent_sample_pages..... 20 innodb_stats_traditional................. ON innodb_stats_transient_sample_pages...... 8 myisam_stats_method...................... NULLS_UNEQUAL thread_pool_exact_stats.................. OFF