MySQL restartStore can be a bottleneck

[jamadden]

When the system is under especially heavy load, such that transactions are retrying a large number of times in short succession, we've observed that restarting the store connection, specifically MySQLObject.Mover._on_store_opened to take a surprisingly large amount of elapsed time.

This is probably because it makes a minimum of four round trips to the database to truncate temp tables. It may have something to do with the non-MVCC/transactional way that 'TRUNCATE' works in MySQL (extra checkpointing causing issues when we're already leading the database?) but that's just a guess. We can eliminate all but one of those round trips pretty trivially.

[jamadden]

According to https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_open_cache_instances , DDL statements, of which TRUNCATE is one, acquire a server global lock. Obviously they interfere with each other, but hopefully not too many other statements, though it’s not clear, and I really hope that wouldn’t apply to session specific temp tables (but seriously MySQL, WHAT YEAR IS THIS that global locks are acceptable?). On the pessimistic side that could explain some observe contention.