1

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
Improve this question
6
  • Why do I need to call ANALYZE table now when I didn't need to call it previously? When server builds execution plan it uses tables statistic as a base. When the data changes the statistic is updated. More the changes more the statistic updates and less the statistic accuracy. And somewhen the statistic is so inaccurate that the server builds non-optimal execution plan. In this time you must renew/refresh the statistic with ANALYZE. From the practice - the typical time after which ANALYZE needed is ~2-3 months in average (depends on the intensity of the data change).
    – Akina
    Oct 3, 2022 at 12:28
  • Thanks - this I know from the manual - but it states that it should automatically do this based on data change rates, and the previous version never had a problem. Finally, it doesn't explain why a query from yesterday that took 5 seconds to run overnight starts taking 8000 seconds or never returns. You'd think it would just get progressively worse. Analyze table- then restores it back to 5 seconds - so its not the query per-se because thats stays the same, so does the data and the indices.
    – James Cross
    Oct 3, 2022 at 14:07
  • Please provide the slow query.
    – Rick James
    Oct 3, 2022 at 17:40
  • This isn't what we're seeing 10.6.7 thus reason for asking. Queries that haven't changed for years, reasonably optimized, indices chosen carefully. Upgrade to 10.6.7 those queries started having a problem largest and busiest tables first. Its tempting to start looking at changing all your queries, fiddling with indices etc, but a smarter approach is looking at what last changed which is the database version. I have found two bugs in MariaDB dev: Persistent stats never being updated post upgrade and bug with the optimizer exponential permutations. Analyze fixes the queries straight away.
    – James Cross
    Oct 4, 2022 at 9:43
  • An example of a query that is misbehaving is simply select id from test where timestamp between current_date - interval 1 day and current_date. Where there is an index on timestamp. Table only has about 30,000 rows. It usually takes 0.02 seconds to return 1000 rows. But suddenly its taking 300 seconds and it doesn't gradually get like that it just happens. Then when you run analyze its back to 0.02. Its silly because you can hold the entire dataset let alone the tiny index in RAM. Explain looks the same before and after its using the index.
    – James Cross
    Oct 4, 2022 at 9:52

1 Answer 1

Reset to default
0

ANALYZE TABLE is rarely needed. Fortunately, it is quite fast, so it does not hurt to run occasionally. (This is unlike OPTIMIZE TABLE, which is even less useful and much slower.)

Often a better index is the 'real' cure for a poorly performing query.

Sometimes a large DELETE can trigger the need for ANALYZE. If that is the case, let's discuss big deletes

glue the recovered partition back on. So I have data for complete weeks, I only want last 26 weeks but if we need the data from 30 weeks ago for some legal reason

Plan A: If you used "transportable tablespaces" to, instead, turn the partition into a "table", that table could be saved and later loaded and SELECTed. Or it could be turned back from a table to a partition. Or that table could be dumped for any of several other purposes of saving.

Plan B: If you have dumped the partition before dropping it, you could use REORGANIZE to add a new partition (of suitable parameters) and then load the data back in.

Improve this answer
7
  • For big deletes its usually whole (old) weeks we delete so we usually have it partitioned on week and we drop the partition with the week we want to delete. We found that was the best way for replication, backup etc. I would not have thought that would need analyze as I thought that a partition was usually partitioned, index and stats alike?
    – James Cross
    Oct 4, 2022 at 9:55
  • @JamesCross - ANALYZE TABLE gathers statistics for subsequent queries. So, it is (potentially) useful for Partitioned tables, too. Still, I don't see much need for it.
    – Rick James
    Oct 4, 2022 at 16:09
  • @JamesCross - And, yes, DROP PARTITION is excellent. See also: Partitioning
    – Rick James
    Oct 4, 2022 at 16:09
  • my only issue with partitioning is that its a pig to re-add a dropped partition if you need to. So if I'm asked I usually say it can't be done.
    – James Cross
    Oct 7, 2022 at 7:58
  • @JamesCross - DROP PARTITION also "deletes" all the data that was in it. Did you have data that you need to recover? Was it PARTITION BY RANGE? Was the partition at the start or end or middle of the range? (There are too many possible answers for the question as it is phrased; please elaborate. That is, I say "maybe it can be re-added", but let's discuss the specifics. (In 20 years, I don't remember the question being asked!)
    – Rick James
    Oct 7, 2022 at 15:59

Your Answer

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

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