Description:
TRUNCATE TABLE is needed in the performance_schema to properly collect some statistics. For example, if we want to guarantee the data in performance_schema.events_statements_histogram_global is reliable for a given interval, we need to TRUNCATE that table at the start of the interval.
LOCK INSTANCE FOR BACKUP prevents us from issuing TRUNCATE TABLE on performance_schema tables that support it, probably because everything is an innodb table now. That's understandable, but we need a way to reset performance_schema stables when the backup lock is not in place.
How to repeat:
session1> LOCK TABLES FOR BACKUP;
session2> TRUNCATE TABLE performance_schema.events_statements_histogram_global;
BLOCKS
session1> SHOW PROCESSLIST;
+----+-----------------+-----------+--------------------+---------+------+-------------------------+----------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------------------+---------+------+-------------------------+----------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 201 | Waiting on empty queue | NULL |
| 9 | root | localhost | NULL | Query | 0 | init | show processlist |
| 10 | root | localhost | performance_schema | Query | 4 | Waiting for backup lock | TRUNCATE TABLE performance_schema.events_statements_histogram_global |
+----+-----------------+-----------+--------------------+---------+------+-------------------------+----------------------------------------------------------------------+
3 rows in set (0.00 sec)
session1> unlock instance;
Query OK, 0 rows affected (0.00 sec)
session2> (unblocks)
Query OK, 0 rows affected (2 min 23.47 sec)
Suggested fix:
either make the lock not apply to performance_schema tables, or provide an alternative command to reset these tables.
