- About TiDB
- Quick Start
- Software and Hardware Requirements
- Environment Configuration Checklist
- Topology Patterns
- Install and Start
- Verify Cluster Status
- Benchmarks Methods
- Backup and Restore
- Read Historical Data
- Configure Time Zone
- Daily Checklist
- Maintain TiFlash
- Maintain TiDB Using TiUP
- Maintain TiDB Using Ansible
- Modify Configuration Online
- Monitor and Alert
- TiDB Troubleshooting Map
- Identify Slow Queries
- Analyze Slow Queries
- SQL Diagnostics
- Identify Expensive Queries
- Statement Summary Tables
- Troubleshoot Hotspot Issues
- Troubleshoot Increased Read and Write Latency
- Troubleshoot Cluster Setup
- Troubleshoot High Disk I/O Usage
- Troubleshoot Lock Conflicts
- Troubleshoot TiFlash
- Troubleshoot Write Conflicts in Optimistic Transactions
- Performance Tuning
- System Tuning
- Software Tuning
- SQL Tuning
- Understanding the Query Execution Plan
- SQL Optimization Process
- Logic Optimization
- Physical Optimization
- Prepare Execution Plan Cache
- Control Execution Plans
- Multiple Data Centers in One City Deployment
- Three Data Centers in Two Cities Deployment
- Best Practices
- Use Placement Rules
- Use Load Base Split
- Use Store Limit
- TiDB Tools
- Use Cases
- TiDB Operator
- Backup & Restore (BR)
- TiDB Binlog
- TiDB Lightning
- TiDB Data Migration
- Cluster Architecture
- Key Monitoring Metrics
- SQL Language Structure and Syntax
- SQL Statements
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
CREATE [GLOBAL|SESSION] BINDING
CREATE TABLE LIKE
DROP [GLOBAL|SESSION] BINDING
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW [GLOBAL|SESSION] BINDINGS
SHOW CHARACTER SET
SHOW [FULL] COLUMNS FROM
SHOW CREATE SEQUENCE
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DRAINER STATUS
SHOW [FULL] FIELDS FROM
SHOW INDEX [FROM|IN]
SHOW INDEXES [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW MASTER STATUS
SHOW [FULL] PROCESSSLIST
SHOW PUMP STATUS
SHOW TABLE NEXT_ROW_ID
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [FULL] TABLES
SHOW [GLOBAL|SESSION] VARIABLES
- Data Types
- Functions and Operators
- Type Conversion in Expression Evaluation
- Control Flow Functions
- String Functions
- Numeric Functions and Operators
- Date and Time Functions
- Bit Functions and Operators
- Cast Functions and Operators
- Encryption and Compression Functions
- Information Functions
- JSON Functions
- Aggregate (GROUP BY) Functions
- Window Functions
- Miscellaneous Functions
- Precision Math
- List of Expressions for Pushdown
- Generated Columns
- SQL Mode
- Garbage Collection (GC)
- Character Set and Collation
- System Tables
- TiDB Dashboard
- Overview Page
- Cluster Info Page
- Key Visualizer Page
- Metrics Relation Graph
- SQL Statements Analysis
- Slow Queries Page
- Cluster Diagnostics
- Search Logs Page
- Profile Instances Page
- Session Management and Configuration
- Command Line Flags
- Configuration File Parameters
- System Variables
- Storage Engines
- Errors Codes
- Table Filter
- Schedule Replicas by Topology Labels
- Release Notes
- All Releases
This document introduces the possible causes of read and write latency and jitters, and how to troubleshoot these issues.
The execution plan of queries is unstable and might select the incorrect index, which causes higher latency.
- If the query execution plan is output in the slow log, you can directly view the plan. Execute the
select tidb_decode_plan('xxx...')statement to parse the detailed execution plan.
- The number of scanned keys in the monitor abnormally increases; in the slow log, the number of
Scan Keysare large.
- The SQL execution duration in TiDB is greatly different than that in other databases such as MySQL. You can compare the execution plan of other databases (for example, whether
Join Orderis different).
The statistics is inaccurate.
- Update the statistical information
analyze tablemanually and execute
analyzeperiodically with the
crontabcommand to keep the statistics accurate.
auto analyzeautomatically. Lower the threshold value of
analyze ratio, increase the frequency of information collection, and set the start and end time of the execution. See the following examples:
set global tidb_auto_analyze_ratio=0.2;
set global tidb_auto_analyze_start_time='00:00 +0800';
set global tidb_auto_analyze_end_time='06:00 +0800';
- Bind the execution plan
- Modify the application SQL statements and execute
use indexto consistently use the index of the column.
- In 3.0 versions, you do not need to modify the application SQL statements. Use
create global bindingto create the binding SQL statement of
- In 4.0 versions, SQL Plan Management is supported, which avoids the performance decrease caused by unstable execution plans.
- Modify the application SQL statements and execute
There is an abnormal increase of the
wait duration metric for the PD TSO. This metric represents the duration of waiting for PD to return requests.
Disk issue. The disk where the PD node is located has full I/O load. Investigate whether PD is deployed with other components with high I/O demand and the health of the disk. You can verify the cause by viewing the monitor metrics in Grafana -> disk performance -> latency/load. You can also use the FIO tool to run a check on the disk if necessary.
Network issues between PD peers. The PD log shows
lost the TCP streaming connection. You need to check whether there is a problem with the network between PD nodes and verify the cause by viewing
round tripin the monitor Grafana -> PD -> etcd.
High server load. The log shows
server is likely overloaded.
PD cannot elect a Leader: The PD log shows
lease is not expired. This issue has been fixed in v3.0.x and v2.1.19.
The leader election is slow. The Region loading duration is long. You can check this issue by running
grep "regions cost"in the PD log. If the result is in seconds, such as
load 460927 regions cost 11.77099s, it means the Region loading is slow. You can enable the
region storagefeature in v3.0 by setting
true, which significantly reduce the Region loading duration.
The network issue between TiDB and PD. Check whether the network from TiDB to PD Leader is running normally by accessing the monitor Grafana -> blackbox_exporter -> ping latency.
PD reports the
FATALerror, and the log shows
range failed to find revision pair. This issue has been fixed in v3.0.8 (#2040).
/api/v1/regionsinterface is used, too many Regions might cause PD OOM. This issue has been fixed in v3.0.8 (#1986).
PD OOM during the rolling upgrade. The size of gRPC messages is not limited, and the monitor shows that
TCP InSegsis relatively large. This issue has been fixed in v3.0.6 (#1952).
PD panics. Report a bug.
Other causes. Get goroutine by running
curl http://127.0.0.1:2379/debug/pprof/goroutine?debug=2and report a bug.
KV Cmd Duration metric in the monitor increases abnormally. This metric represents the duration between the time that TiDB sends a request to TiKV and the time that TiDB receives the response.
gRPC durationmetric. This metric represents the total duration of a gRPC request in TiKV. You can find out the potential network issue by comparing
gRPC durationof TiKV and
KV durationof TiDB. For example, the gRPC duration is short but the KV duration of TiDB is long, which indicates that the network latency between TiDB and TiKV might be high, or that the NIC bandwidth between TiDB and TiKV is fully occupied.
Re-election because TiKV is restarted.
- After TiKV panics, it is pulled up by
systemdand runs normally. You can check whether panic has occurred by viewing the TiKV log. Because this issue is unexpected, report a bug if it happens.
- TiKV is stopped or killed by a third party and then pulled up by
systemd. Check the cause by viewing
dmesgand the TiKV log.
- TiKV is OOM, which causes restart.
- TiKV is hung because of dynamically adjusting
- After TiKV panics, it is pulled up by
Check monitor: TiKV RocksDB encounters write stall and thus results in re-election. You can check if the monitor Grafana -> TiKV-details -> errors shows
server is busy.
Re-election because of network isolation.
block-cacheconfiguration is too large, it might cause TiKV OOM. To verify the cause of the problem, check the
block cache sizeof RocksDB by selecting the corresponding instance in the monitor Grafana -> TiKV-details. Meanwhile, check whether the
[storage.block-cache] capacity = # "1GB"parameter is set properly. By default, TiKV's
block-cacheis set to
45%of the total memory of the machine. You need to explicitly specify this parameter when you deploy TiKV in the container, because TiKV obtains the memory of the physical machine, which might exceed the memory limit of the container.
Coprocessor receives many large queries and returns a large volume of data. gRPC fails to send data as quickly as the coprocessor returns data, which results in OOM. To verify the cause, you can check whether
response sizeexceeds the
network outboundtraffic by viewing the monitor Grafana -> TiKV-details -> coprocessor overview.
There are some single threads in TiKV that might become the bottleneck.
- Too many Regions in a TiKV instance causes a single gRPC thread to be the bottleneck (Check the Grafana -> TiKV-details -> Thread CPU/gRPC CPU Per Thread metric). In v3.x or later versions, you can enable
Hibernate Regionto resolve the issue.
- For versions earlier than v3.0, when the raftstore thread or the apply thread becomes the bottleneck (Grafana -> TiKV-details -> Thread CPU/raft store CPU and Async apply CPU metrics exceed
80%), you can scale out TiKV (v2.x) instances or upgrade to v3.x with multi-threading.
The usage of CPU resources becomes the bottleneck.
- Hotspot issue
- High overall load. Check the slow queries and expensive queries of TiDB. Optimize the executing queries by adding indexes or executing queries in batches. Another solution is to scale out the cluster.
Most of each online cluster has three or five nodes. If the machine to be maintained has the PD component, you need to determine whether the node is the leader or the follower. Disabling a follower has no impact on the cluster operation. Before disabling a leader, you need to switch the leadership. During the leadership change, performance jitter of about 3 seconds will occur.
By default, each TiDB cluster has three replicas, so each Region has three replicas in the cluster. These Regions elect the leader and replicate data through the Raft protocol. The Raft protocol ensures that TiDB can still provide services without data loss even when the nodes (that are fewer than half of replicas) fail or are isolated. For the cluster with three replicas, the failure of one node might cause performance jitter but the usability and correctness in theory are not affected.
Creating indexes consumes a huge amount of resources when TiDB scans tables and backfills indexes. Index creation might even conflict with the frequently updated fields, which affects the application. Creating indexes on a large table often takes a long time, so you must try to balance the index creation time and the cluster performance (for example, creating indexes at the off-peak time).
Currently, you can use
tidb_ddl_reorg_batch_size to dynamically adjust the speed of index creation. Usually, the smaller the values, the smaller the impact on the system, with longer execution time though.
In general cases, you can first keep their default values (
256), observe the resource usage and response speed of the cluster, and then increase the value of
tidb_ddl_reorg_worker_cnt to increase the concurrency. If no obvious jitter is observed in the monitor, increase the value of
tidb_ddl_reorg_batch_size. If the columns involved in the index creation are frequently updated, the many resulting conflicts will cause the index creation to fail and be retried.
In addition, you can also set the value of
PRIORITY_HIGH to prioritize the index creation and speed up the process. But in the general OLTP system, it is recommended to keep its default value.
The transaction of TiDB adopts the Multi-Version Concurrency Control (MVCC) mechanism. When the newly written data overwrites the old data, the old data is not replaced, and both versions of data are stored. Timestamps are used to mark different versions. The task of GC is to clear the obsolete data.
- In the phase of Resolve Locks, a large amount of
scan_lockrequests are created in TiKV, which can be observed in the gRPC-related metrics. These
scan_lockrequests call all Regions.
- In the phase of Delete Ranges, a few (or no)
unsafe_destroy_rangerequests are sent to TiKV, which can be observed in the gRPC-related metrics and the GC tasks panel.
- In the phase of Do GC, each TiKV by default scans the leader Regions on the machine and performs GC to each leader, which can be observed in the GC tasks panel.
- Common causes
- Other causes