TiDB常用运维SQL

最新推荐文章于 2023-10-27 17:32:24 发布

夏克

最新推荐文章于 2023-10-27 17:32:24 发布

阅读量1.6k

点赞数 2

分类专栏： tidb 文章标签： sql 运维数据库

本文链接：https://blog.csdn.net/xk_xx/article/details/122886736

版权

tidb 专栏收录该内容

3 篇文章 2 订阅

订阅专栏

本文介绍了如何在 TiDB 数据库中查询 DDL 操作时间戳、设置和使用快照、数据恢复、SQL 脚本执行、内存及查询时间阈值管理、Block-Cache 调整以及 PD 配置。内容涵盖了数据库管理、性能优化和故障恢复等多个方面。

摘要由CSDN通过智能技术生成

-- 查询DDL操作时间戳
admin SHOW ddl jobs;

-- 设置tidb_snapshot
SET @@tidb_snapshot="2022-01-10 20:36:50"
SET @@tidb_snapshot="" --设置成当前时间

-- 设置session的快照时间tidb_snapshot
set session tidb_snapshot = "2022-01-10 20:36:50";

-- 执行Flashback 命令回复数据（适用于drop与truncate）
FLASHBACK TABLE target_table_name[TO new_table_name]

-- 适用于Drop操作
RECOVER TABLE table_name;

-- 执行sql脚本文件
source /xx/xx/xxx.sql

-- 将t1表上一次truncate的数据恢复到t2表中
FLASHBACK TABLE t1 TO T2;

-- 查询tidb_expensive_query_time_threshold变量，如果sql执行查询时间大于60秒，就会被记录到tidb.log中
mysql 5.7.25-TiDB-v5.3.0
mycli 1.20.1
Chat: https://gitter.im/dbcli/mycli
Mail: https://groups.google.com/forum/#!forum/mycli-users
Home: http://mycli.net
Thanks to the contributor - jweiland.net
mysql root@localhost:(none)> show variables like '%tidb_expensive_query_time_threshold%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| tidb_expensive_query_time_threshold | 60    |
+-------------------------------------+-------+
1 row in set
Time: 0.017s
-- 为了让更多的语句被记录，可以缩短这个时间，反之则增大这个时间
mysql root@localhost:(none)> set tidb_expensive_query_time_threshold=10;
Query OK, 0 rows affected
Time: 0.009s
mysql root@localhost:(none)> show variables like '%tidb_expensive_query_time_threshold%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| tidb_expensive_query_time_threshold | 10    |
+-------------------------------------+-------+
1 row in set
Time: 0.017s

more tidb.log | grep expensivequery

-- 查询tidb_mem_quota_query，其值表示，限制sql查询可以使用的最大内存1G，该参数是session级别的，只影响当前会话
mysql root@localhost:(none)> show variables like '%tidb_mem_quota_query%';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| tidb_mem_quota_query | 1073741824 |
+----------------------+------------+
1 row in set
Time: 0.012s
-- 调小限制，防止OOM，内存会被限制，但可以使用磁盘
mysql root@localhost:(none)> set tidb_mem_quota_query=100000000;
Query OK, 0 rows affected
Time: 0.000s
mysql root@localhost:(none)> show variables like '%tidb_mem_quota_query%';
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| tidb_mem_quota_query | 100000000 |
+----------------------+-----------+
1 row in set
Time: 0.014s
mysql root@localhost:(none)>


more tidb.log | grep 'memory exceeds quota,spill to disk now'

-- 查看block-cache大小，建议范围总内存的（45%~60%）
MySQL root@localhost:mysql> SHOW config WHERE NAME='storage.block-cache.capacity';
+------+-----------------+------------------------------+---------+
| Type | Instance        | Name                         | Value   |
+------+-----------------+------------------------------+---------+
| tikv | 127.0.0.1:20160 | storage.block-cache.capacity | 7274MiB |
+------+-----------------+------------------------------+---------+
1 row in set
Time: 0.010s
MySQL root@localhost:mysql> SHOW config WHERE NAME='storage.block-cache.capacity';
+------+-----------------+------------------------------+---------+
| Type | Instance        | Name                         | Value   |
+------+-----------------+------------------------------+---------+
| tikv | 127.0.0.1:20160 | storage.block-cache.capacity | 7000MiB |
+------+-----------------+------------------------------+---------+
1 row in set
Time: 0.009s
MySQL root@localhost:mysql>

frank@DESKTOP-6NF3B9K:~$ tiup ctl:v5.3.0 pd -u http://127.0.0.1:2379 config show scheduler
Starting component `ctl`: /home/frank/.tiup/components/ctl/v5.3.0/ctl pd -u http://127.0.0.1:2379 config show scheduler
{
  "replication": {
    "enable-placement-rules": "true",
    "enable-placement-rules-cache": "false",
    "isolation-level": "",
    "location-labels": "",
    "max-replicas": 1,
    "strictly-match-label": "false"
  },
  "schedule": {
    "enable-cross-table-merge": "true",
    "enable-joint-consensus": "true",
    "high-space-ratio": 0.7,
    "hot-region-cache-hits-threshold": 3,
    "hot-region-schedule-limit": 4,
    "hot-regions-reserved-days": 0,
    "hot-regions-write-interval": "10m0s",
    "leader-schedule-limit": 4,
    "leader-schedule-policy": "count",
    "low-space-ratio": 0.99,
    "max-merge-region-keys": 200000,
    "max-merge-region-size": 20,
    "max-pending-peer-count": 64,
    "max-snapshot-count": 64,
    "max-store-down-time": "30m0s",
    "merge-schedule-limit": 8,
    "patrol-region-interval": "10ms",
    "region-schedule-limit": 2048,
    "region-score-formula-version": "v2",
    "replica-schedule-limit": 64,
    "split-merge-interval": "1h0m0s",
    "tolerant-size-ratio": 0
  }
}

tiup ctl:v5.3.0 pd -u http://127.0.0.1:2379 config set enable-cross-table-merge false