Run ANALYZE TABLE after table copy

[avit]

From the MySQL docs on the matter:

You might run this statement in your setup scripts after representative data has been loaded into the table, and run it periodically after DML operations significantly change the contents of indexed columns, or on a schedule at times of low activity.

Because it involves a table read-lock, I would propose it's a good opportunity to do this after copying all the data into the new table, before swapping the names back. Thoughts?

[arthurnn]

I guess we will need this indeed.. Wondering if we were running that manually. @camilo do you know?

[camilo]

No manual run, however is this even needed after an LHM? My understanding is that the LHM pretty much creates a new table, @sroysen will know for sure if it is of any benefit.

One thing to keep in mind regardless on any kind of high troughput table is that the LHM lock as is already can cause problems, this might increase the lock time, if implemented I'd say opt-in is the way to go. cc @shuhaowu

[avit]

I think you are right that it's not needed by default: if innodb_stats_auto_recalc is enabled then index stats are automatically recalculated whenever the table changes more than 10%. It looks like it's enabled by default, so that's fine.

I've seen some recommendations to temporarily disable auto recalc while running a large import, since it makes sense that it would slow things down to recalculate continuously while adding new records. I don't remember now, but I might have been playing with that as an optimization around LHM, hence this ticket.

Do you think it's worth investigating this as an optimization? Otherwise feel free to close this ticket.

[camilo]

I think it could be an opt-in feature but never the default. My 2 cents.

[sroysen]

@camilo is right and I share his opinion: innodb_stats_auto_recalc is enabled by default, so under normal circumstances there is no need to run an ANALYZE TABLE at all.