support explain analyze format = zip to collect metrics to investigate slow query

[zz-jason]

Feature Request

Is your feature request related to a problem? Please describe:

To investigate a slow query, typically we need the following information:

• SQL Query
• Slow Log
• create table statements for related tables
• explain analyze ..., usually contained in slow log
• TiDB, TiKV, PD related logs (more detail than slow log), usually used when the issue is hard to investigate
• CPU, heap, goroutine, lock profile, etc

Collecting these metrics is not easy, it involves lots of discussion with users and explanations. If we could collect all the needed information, it would greatly save the time to collect useful information and reduce the time to find the slow reason.

Describe the feature you'd like:

explain analyze format = zip ... statement to collect all the needed information, putting them to a zip file, then users can provide us the zip file to help investigate the root cause.

Describe alternatives you've considered:

N/A

Teachability, Documentation, Adoption, Migration Strategy:

N/A

[ghost]

For slow query, maybe it is better to include the information from information_schema.statements_summary? I would also add bind_info to the list of sources.

[zz-jason]

both information_schema.statements_summary and bind_info is helpful for investigation.

[zz-jason]

@breeswish It would be better if we can support collecting these metrics in TiDB Dashboard.

[breezewish]

The idea is great. Tracing & metrics can be supplied as well. I only concern about the implementation for some parts. For example:

• It's very hard to identify "related logs" for non TiDB components now. Maybe supporting Print Txn ID and Query ID in log to trace the whole lifetime of a Txn / SQL #17845 can help a little. (but still pretty hard for region related things, since region operation in TiKV are not easy to be linked with a specific TiDB query). If the logs are known to be missing, then it may be simply regarded as "unreliable" so that the usefulness is reduced. DBAs may still need to collect logs in other ways, in order to not miss something.
• AFAIK CPU / Heap / etc can not be collected in parallel for multiple queries. It may cause EXPLAIN ANALYZE statement wait for non execution related things.

As a reference, CockroachDB may have something similar:
https://www.cockroachlabs.com/docs/stable/admin-ui-statements-page.html#diagnostics