Troubleshooting

SELECT query, query_time, mem_max, plan
FROM INFORMATION_SCHEMA.SLOW_QUERY
ORDER BY query_time DESC
LIMIT 10;

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

ANALYZE TABLE orders;

SHOW CONFIG WHERE name = 'mem-quota-query';

SELECT @@tidb_mem_quota_query;

SET SESSION tidb_mem_quota_query = 1073741824; -- 1 GiB

SELECT id, user, db, command, time, state, info, mem
FROM INFORMATION_SCHEMA.PROCESSLIST
ORDER BY mem DESC
LIMIT 10;

KILL TIDB <connection-id>;

[performance]
# Maximum memory available to TiDB in bytes. 0 means use total system memory.
server-memory-quota = 0

curl http://127.0.0.1:10080/status

mysql -h 127.0.0.1 -P 4000 -u root

curl http://127.0.0.1:10080/status | python3 -m json.tool

[server]
max-server-connections = 4096

curl http://127.0.0.1:10080/ddl/history

-- View running DDL jobs
ADMIN SHOW DDL JOBS;

-- View recently completed or failed DDL jobs
ADMIN SHOW DDL JOBS 20;

ADMIN CANCEL DDL JOBS <job-id>;

SELECT * FROM INFORMATION_SCHEMA.DATA_LOCK_WAITS;

SELECT * FROM INFORMATION_SCHEMA.DEADLOCKS;

SELECT * FROM INFORMATION_SCHEMA.TIDB_TRX;

-- Switch a session to pessimistic mode
SET SESSION tidb_txn_mode = 'pessimistic';

# View logs for a named cluster
tiup cluster display <cluster-name>
# Then SSH to the node and check the log path shown in the output

grep '"level":"error"' /path/to/tidb.log | tail -50

SET GLOBAL tidb_log_level = 'debug';
-- Remember to restore after diagnosis:
SET GLOBAL tidb_log_level = 'info';