Tracking issue for TiDB built-in SQL Diagnostics

[lonng]

The PR #13481 proposes a new method of acquiring diagnostic information in TiDB and exposing diagnostic information by the system tables so that users can query using SQL. The purpose of the proposal #13481 is aim to improve the efficiency of the cluster-based information query, state acquisition, log retrieval, one-click inspection, and fault diagnosis

Note

This issue is a TODO list catalog that is used to clarify the split details of the entire feature. If you are interested in a part, use the following workflow:

1. Select the module you are interested in.
2. Create a new issue to claim the relevant work and describe the rough implementation in the new issue.
3. File a new pull request.

Issues:

• Protocol Definition
  • Define the Diagnostics gRPC Service and the related message type in kvproto Define the Diagnostics gRPC Service and the relevent message type  #13581 @lonng
• Information Collection
  • Cluster Topology
    • Add a system table to provide cluster topology
      • TiDB infoschema: add information_schema.tidb_cluster_info to retrieve cluster topology. #13035 @lonng
    • The current implementation should be refined and the ID and NAME columns should be deleted Remove the ID and NAME columns in TIDB_CLUSTER_INFO #13586 @lonng
  • Cluster Configuration
    • Add a system table to provide cluster configuration
      • TiDB infoschema: add TIDB_CLUSTER_CONFIG virtual table to retrieve all instance config #13063 @lonng
      • TiKV infoschema: add TIDB_CLUSTER_CONFIG virtual table to retrieve all instance config #13063 @lonng
      • PD infoschema: add TIDB_CLUSTER_CONFIG virtual table to retrieve all instance config #13063 @lonng
      • TiDB: Predicates push down executor: refactor the way of retreiving remote component configuration #13832 @lonng
  • Cluster Performance Sampling
    • Add HTTP API for cluster components to get performance sample data
      • TiDB perfschema: support performance flame-graph by SQL #12986 @lonng
      • PD add profile CPU/memory/mutex/allocs/block/goroutine HTTP API tikv/pd#1965 @lonng
      • TiKV Generate flamegraph at runtime  tikv/tikv#5697 @YangKeao
    • Add system table to provide query via SQL
      • TiDB perfschema: support performance flame-graph by SQL #12986 @lonng
      • TiKV perfschema: support retrieve TiKV CPU profile via SQL #13711 @lonng
      • PD perfschema: support retrieve CPU/memory/mutex/block/allocs profile from PD via SQL #13717 @lonng
  • Information Collection Framework:
    • Pluggable Information Collection Framework to support extended information collection rules
      • TiDB @crazycs520 server: add rpcserver to get other tidb server info for diagnostics #13693 , https://github.com/pingcap/sysutil pkg.
      • PD server: add diagnostics service tikv/pd#2024 @lonng
      • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
    • Information Collection Rules
      • Hardware Information
        • CPU information: number of physical cores, number of logical cores, NUMA information, CPU frequency, CPU vendor, L1/L2/L3 cache size
          • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
          • TiDB/PD infoschema: add server hardware and system sysctl cluster table #13997 @crazycs520
        • NIC information: NIC device name, NIC enabled, manufacturer, model, bandwidth, driver version, interface queue number (optional)
          • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
          • TiDB/PD infoschema: add server hardware and system sysctl cluster table #13997 @crazycs520
        • Disk information: disk name, disk capacity, disk usage, disk partition, mount information
          • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
          • TiDB/PD infoschema: add server hardware and system sysctl cluster table #13997 @crazycs520
        • USB device list
        • Memory information
          • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
          • TiDB/PD infoschema: add server hardware and system sysctl cluster table #13997 @crazycs520
    • System Information
      • Kernel information: sysctl -a / ulimit -a
        • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
        • TiDB/PD
      • Process information: current process name, command line parameters, executable file path, pid, environment variables, memory, startup time, uid, gid, process status
        • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
        • TiDB/PD
      • File descriptor information: Available Quantity, Current Used Quantity
  • Load Information
    • CPU usage, 1/5/15 minute load
      • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
      • TiDB/PD @crazycs520 server: add rpcserver to get other tidb server info for diagnostics #13693
    • Memory: Total/Free/Available/Buffers/Cached/Active/Inactive/Swap
      • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
      • TiDB/PD @crazycs520 server: add rpcserver to get other tidb server info for diagnostics #13693
    • Disk IO:
      • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
        • tps: The number of transfers per second that were issued to the device.
        • rrqm/s: The number of read requests merged per second that were queued to the device.
        • wrqm/s: The number of write requests merged per second that were queued to the device.
        • r/s: The number (after merges) of read requests completed per second for the device.
        • w/s: The number (after merges) of write requests completed per second for the device.
        • rsec/s: The number of sectors (kilobytes, megabytes) read from the device per second.
        • wsec/s: The number of sectors (kilobytes, megabytes) written to the device per second.
        • await: The average time (in milliseconds) for I/O requests issued to the device to be served.
        • %util: Percentage of elapsed time during which I/O requests were issued to the device (bandwidth utilization for the device)
      • TiDB/PD @crazycs520 server: add rpcserver to get other tidb server info for diagnostics #13693
        • tps: The number of transfers per second that were issued to the device.
        • rrqm/s: The number of read requests merged per second that were queued to the device.
        • wrqm/s: The number of write requests merged per second that were queued to the device.
        • r/s: The number (after merges) of read requests completed per second for the device.
        • w/s: The number (after merges) of write requests completed per second for the device.
        • rsec/s: The number of sectors (kilobytes, megabytes) read from the device per second.
        • wsec/s: The number of sectors (kilobytes, megabytes) written to the device per second.
        • await: The average time (in milliseconds) for I/O requests issued to the device to be served.
        • %util: Percentage of elapsed time during which I/O requests were issued to the device (bandwidth utilization for the device)
    • Network IO
      • TiKV server: collect load/hardware/system information tikv/tikv#6135 @lonng
        • IFACE: name of the network interface for which statistics are reported.
        • rxpck/s: total number of packets received per second.
        • txpck/s: total number of packets transmitted per second.
        • rxkB/s: total number of kilobytes received per second.
        • txkB/s: total number of kilobytes transmitted per second.
        • rxcmp/s: number of compressed packets received per second.
        • txcmp/s: number of compressed packets transmitted per second.
        • rxmcst/s: number of multicast packets received per second.
      • TiDB/PD @crazycs520 server: add rpcserver to get other tidb server info for diagnostics #13693
        • IFACE: name of the network interface for which statistics are reported.
        • rxpck/s: total number of packets received per second.
        • txpck/s: total number of packets transmitted per second.
        • rxkB/s: total number of kilobytes received per second.
        • txkB/s: total number of kilobytes transmitted per second.
        • rxcmp/s: number of compressed packets received per second.
        • txcmp/s: number of compressed packets transmitted per second.
        • rxmcst/s: number of multicast packets received per second.
  • System Info Tables
    • Hardward Info
      • TiDB infoschema: add server hardware and system sysctl cluster table #13997 @crazycs520
    • Software Info
      • TiDB infoschema: add server hardware and system sysctl cluster table #13997 @crazycs520
• Cluster Memory Table
  • Memory table global view *: support read tidb cluster memory table #13065 @crazycs520
• Memory table refactor
  • Use virtual table framework manage information schema infoschema: place INFORMATION_SCHEMA in new virtual table framework #13696 @lonng
  • Extra the LogicalMemTable part from DataSource planner: extract a LogicalMemTable from DataSource to decouple memory/stored tables #13741 @lonng
  • Predication pushdown framework for virtual table planner: support push predicates down to the memory table #13821 @lonng
• Logging framework
  • Log predicate pushdown
    • TiDB infoschema,planner: implement the CLUSTER_LOG memory table predicate push down #14018 @lonng
  • Log LogReader executor implementation
    • TiDB executor: implement the CLUSTER_LOG retriever #14046 @lonng
  • gRPC Service implementation
    • TiKV Implement the Diagnostics gRPC service tikv/tikv#5980 @lonng
    • TiDB/PD implements the search log interface of diagnostics service sysutil#2 @lonng
    • PD server: add diagnostics service tikv/pd#2024 @lonng
  • Log system table
    • TiDB infoschema,planner: implement the CLUSTER_LOG memory table predicate push down #14018 @lonng
  • add seqID in TiKV slow log, This will help indicate which SQL in a big transaction, because the transaction start_ts is same.
• Metrics information framework
  • Basic metrics information system table query framework
    • TiDB infoschema: add metric database/table to query cluster metric table. #13757 @crazycs520
  • Add remote-metrics-storage configuration
    • PD *: add query metric http api tikv/pd#1957 @crazycs520
  • Implement the first version of the PromQL query interface based on Proxy
    • PD *: add query metric http api tikv/pd#1957 @crazycs520
  • Metrics predication pushdown
    • TiDB infoschema,planner: implement the metric table predicate push down #14169 @crazycs520
• Query expression mapping rules
  • Metric information table query framework.
    • TiDB query metric with promQL and present as table. infoschema: add metric database/table to query cluster metric table. #13757 @crazycs520
• Diagnostics Framework
  • Inspection schema *: implement the INSPECTION_SCHEMA to provide snapshot of inspection tables #14147 @lonng
  • Diagnostics framework executor *: implement the diagnostics inspection framework #14114 @lonng
  • Diagnostics common rules *: implement the diagnostics inspection framework #14114 @lonng

Teachability, Documentation, Adoption, Migration Strategy:

Proposal: #13481

[tisonkun]

closed as all tasks done.