When or Why to ANALYZE TABLE ? - Source

Revision: 119492
User: James Cross
Date: 2022-10-03 08:12

We have migrated a few heavy databases from some earlier version of 10.2  to 10.6 - the latest provided by ubuntu repo for 22.04.
All was well for a few weeks and then we started seeing long queries and some that actually never return.
These are all fixed immediately by calling ANALYZE table on the offending table.  At least, then, we can be certain that the problem lies squarely within whatever ANALYZE fixes.

I'm no expert in exactly how the statistics help with optimization, but I can google it like anyone else - after which I'm still no expert.
So, 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 dont.  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.

The upgrade consisted off shutting down cleanly - copying to new server then starting up, at which point it upgraded the tables automatically and was seemingly trouble free.

Just in the case that we 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:

aria_stats_method........................ nulls_unequal
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

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

When or Why to ANALYZE TABLE ? - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB