INSPECTION_RESULT

TiDB has some built-in diagnostic rules for detecting faults and hidden issues in the system.

The INSPECTION_RESULT diagnostic table can help you quickly find problems and reduce your repetitive manual work. You can use the select * from information_schema.inspection_result statement to trigger the internal diagnostics.

The structure of the information_schema.inspection_result diagnostic result table information_schema.inspection_result is as follows:

USE information_schema;
DESC inspection_result;

+----------------+--------------+------+------+---------+-------+
| Field          | Type         | Null | Key  | Default | Extra |
+----------------+--------------+------+------+---------+-------+
| RULE           | varchar(64)  | YES  |      | NULL    |       |
| ITEM           | varchar(64)  | YES  |      | NULL    |       |
| TYPE           | varchar(64)  | YES  |      | NULL    |       |
| INSTANCE       | varchar(64)  | YES  |      | NULL    |       |
| STATUS_ADDRESS | varchar(64)  | YES  |      | NULL    |       |
| VALUE          | varchar(64)  | YES  |      | NULL    |       |
| REFERENCE      | varchar(64)  | YES  |      | NULL    |       |
| SEVERITY       | varchar(64)  | YES  |      | NULL    |       |
| DETAILS        | varchar(256) | YES  |      | NULL    |       |
+----------------+--------------+------+------+---------+-------+
9 rows in set (0.00 sec)

Field description:

• RULE: The name of the diagnostic rule. Currently, the following rules are available:
  • config: Checks whether the configuration is consistent and proper. If the same configuration is inconsistent on different instances, a warning diagnostic result is generated.
  • version: The consistency check of version. If the same version is inconsistent on different instances, a warning diagnostic result is generated.
  • node-load: Checks the server load. If the current system load is too high, the corresponding warning diagnostic result is generated.
  • critical-error: Each module of the system defines critical errors. If a critical error exceeds the threshold within the corresponding time period, a warning diagnostic result is generated.
  • threshold-check: The diagnostic system checks the thresholds of key metrics. If a threshold is exceeded, the corresponding diagnostic information is generated.
• ITEM: Each rule diagnoses different items. This field indicates the specific diagnostic items corresponding to each rule.
• TYPE: The instance type of the diagnostics. The optional values are tidb, pd, and tikv.
• INSTANCE: The specific address of the diagnosed instance.
• STATUS_ADDRESS: The HTTP API service address of the instance.
• VALUE: The value of a specific diagnostic item.
• REFERENCE: The reference value (threshold value) for this diagnostic item. If VALUE exceeds the threshold, the corresponding diagnostic information is generated.
• SEVERITY: The severity level. The optional values are warning and critical.
• DETAILS: Diagnostic details, which might also contain SQL statement(s) or document links for further diagnostics.

Diagnostics example

Diagnose issues currently existing in the cluster.

SELECT * FROM information_schema.inspection_result\G

***************************[ 1. row ]***************************
RULE      | config
ITEM      | log.slow-threshold
TYPE      | tidb
INSTANCE  | 172.16.5.40:4000
VALUE     | 0
REFERENCE | not 0
SEVERITY  | warning
DETAILS   | slow-threshold = 0 will record every query to slow log, it may affect performance
***************************[ 2. row ]***************************
RULE      | version
ITEM      | git_hash
TYPE      | tidb
INSTANCE  |
VALUE     | inconsistent
REFERENCE | consistent
SEVERITY  | critical
DETAILS   | the cluster has 2 different tidb version, execute the sql to see more detail: select * from information_schema.cluster_info where type='tidb'
***************************[ 3. row ]***************************
RULE      | threshold-check
ITEM      | storage-write-duration
TYPE      | tikv
INSTANCE  | 172.16.5.40:23151
VALUE     | 130.417
REFERENCE | < 0.100
SEVERITY  | warning
DETAILS   | max duration of 172.16.5.40:23151 tikv storage-write-duration was too slow
***************************[ 4. row ]***************************
RULE      | threshold-check
ITEM      | rocksdb-write-duration
TYPE      | tikv
INSTANCE  | 172.16.5.40:20151
VALUE     | 108.105
REFERENCE | < 0.100
SEVERITY  | warning
DETAILS   | max duration of 172.16.5.40:20151 tikv rocksdb-write-duration was too slow

The following issues can be detected from the diagnostic result above:

• The first row indicates that TiDB's log.slow-threshold value is configured to 0, which might affect performance.
• The second row indicates that two different TiDB versions exist in the cluster.
• The third and fourth rows indicate that the TiKV write delay is too long. The expected delay is no more than 0.1 second, while the actual delay is far longer than expected.

You can also diagnose issues existing within a specified range, such as from "2020-03-26 00:03:00" to "2020-03-26 00:08:00". To specify the time range, use the SQL Hint of /*+ time_range() */. See the following query example:

select /*+ time_range("2020-03-26 00:03:00", "2020-03-26 00:08:00") */ * from information_schema.inspection_result\G

***************************[ 1. row ]***************************
RULE      | critical-error
ITEM      | server-down
TYPE      | tidb
INSTANCE  | 172.16.5.40:4009
VALUE     |
REFERENCE |
SEVERITY  | critical
DETAILS   | tidb 172.16.5.40:4009 restarted at time '2020/03/26 00:05:45.670'
***************************[ 2. row ]***************************
RULE      | threshold-check
ITEM      | get-token-duration
TYPE      | tidb
INSTANCE  | 172.16.5.40:10089
VALUE     | 0.234
REFERENCE | < 0.001
SEVERITY  | warning
DETAILS   | max duration of 172.16.5.40:10089 tidb get-token-duration is too slow

The following issues can be detected from the diagnostic result above:

• The first row indicates that the 172.16.5.40:4009 TiDB instance is restarted at 2020/03/26 00:05:45.670.
• The second row indicates that the maximum get-token-duration time of the 172.16.5.40:10089 TiDB instance is 0.234s, but the expected time is less than 0.001s.

You can also specify conditions, for example, to query the critical level diagnostic results:

select * from information_schema.inspection_result where severity='critical';

Query only the diagnostic result of the critical-error rule:

select * from information_schema.inspection_result where rule='critical-error';

Diagnostic rules

The diagnostic module contains a series of rules. These rules compare the results with the thresholds after querying the existing monitoring tables and cluster information tables. If the results exceed the thresholds, the diagnostics of warning or critical is generated and the corresponding information is provided in the details column.

You can query the existing diagnostic rules by querying the inspection_rules system table:

select * from information_schema.inspection_rules where type='inspection';

+-----------------+------------+---------+
| NAME            | TYPE       | COMMENT |
+-----------------+------------+---------+
| config          | inspection |         |
| version         | inspection |         |
| node-load       | inspection |         |
| critical-error  | inspection |         |
| threshold-check | inspection |         |
+-----------------+------------+---------+

config diagnostic rule

In the config diagnostic rule, the following two diagnostic rules are executed by querying the CLUSTER_CONFIG system table:

• Check whether the configuration values of the same component are consistent. Not all configuration items has this consistency check. The allowlist of consistency check is as follows:

  // The allowlist of the TiDB configuration consistency check
  port
  status.status-port
  host
  path
  advertise-address
  status.status-port
  log.file.filename
  log.slow-query-file
  tmp-storage-path
  
  // The allowlist of the PD configuration consistency check
  advertise-client-urls
  advertise-peer-urls
  client-urls
  data-dir
  log-file
  log.file.filename
  metric.job
  name
  peer-urls
  
  // The allowlist of the TiKV configuration consistency check
  server.addr
  server.advertise-addr
  server.status-addr
  log-file
  raftstore.raftdb-path
  storage.data-dir
  storage.block-cache.capacity
  

• Check whether the values of the following configuration items are as expected.

  Component	Configuration item	Expected value
  TiDB	log.slow-threshold	larger than 0
  TiKV	raftstore.sync-log	true

version diagnostic rule

The version diagnostic rule checks whether the version hash of the same component is consistent by querying the CLUSTER_INFO system table. See the following example:

SELECT * FROM information_schema.inspection_result WHERE rule='version'\G

***************************[ 1. row ]***************************
RULE      | version
ITEM      | git_hash
TYPE      | tidb
INSTANCE  |
VALUE     | inconsistent
REFERENCE | consistent
SEVERITY  | critical
DETAILS   | the cluster has 2 different tidb versions, execute the sql to see more detail: SELECT * FROM information_schema.cluster_info WHERE type='tidb'

critical-error diagnostic rule

In critical-error diagnostic rule, the following two diagnostic rules are executed:

• Detect whether the cluster has the following errors by querying the related monitoring system tables in the metrics schema:

  Component	Error name	Monitoring table	Error description
  TiDB	panic-count	tidb_panic_count_total_count	Panic occurs in TiDB.
  TiKV	critical-error	tikv_critical_error_total_count	The critical error of TiKV.
  TiKV	scheduler-is-busy	tikv_scheduler_is_busy_total_count	The TiKV scheduler is too busy, which makes TiKV temporarily unavailable.
  TiKV	coprocessor-is-busy	tikv_coprocessor_is_busy_total_count	The TiKV Coprocessor is too busy.
  TiKV	channel-is-full	tikv_channel_full_total_count	The "channel full" error occurs in TiKV.
  TiKV	tikv_engine_write_stall	tikv_engine_write_stall	The "stall" error occurs in TiKV.

• Check whether any component is restarted by querying the metrics_schema.up monitoring table and the CLUSTER_LOG system table.

threshold-check diagnostic rule

The threshold-check diagnostic rule checks whether the following metrics in the cluster exceed the threshold by querying the related monitoring system tables in the metrics schema:

In addition, this rule also checks whether the CPU usage of the following threads in a TiKV instance is too high:

• scheduler-worker-cpu
• coprocessor-normal-cpu
• coprocessor-high-cpu
• coprocessor-low-cpu
• grpc-cpu
• raftstore-cpu
• apply-cpu
• storage-readpool-normal-cpu
• storage-readpool-high-cpu
• storage-readpool-low-cpu
• split-check-cpu

The built-in diagnostic rules are constantly being improved. If you have more diagnostic rules, welcome to create a PR or an issue in the tidb repository.