[TiDBer Chat Session 48] Unofficial TiDB-related SQL Script Contest!

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 【TiDBer 唠嗑茶话会 48】非正式 TiDB相关 SQL 脚本征集大赛！

| username: TiDB社区小助手

I believe that as operation and maintenance masters & programming experts, everyone has their own set of memos to record some commonly used scripts. Now! It’s time! To show your cards!

In this informal script collection contest, we welcome all TiDBers to showcase their treasured TiDB-related SQL scripts :sparkles: If you have any practical/convenient/highly recommended scripts, feel free to show them off together!

Contest Rewards: A comprehensive collection of TiDB community SQL scripts & 100 points for those who make it to the list!

This Topic:

SQL script collection, we welcome all TiDBers to showcase the TiDB-related SQL scripts they find practical/convenient/highly recommended!

For example, from @I am Coffee Brother:

View table metadata
show stats_meta where db_name like ‘%sbtest%’;

Event Rewards:

TiDBers who showcase their recommended TiDB-related SQL scripts will receive a reward of 100 points!

Event Duration:

2022.11.25-2022.12.02

img_v2_c10a54f8-e56c-4a70-9719-3e5799e664bg
img_v2_c10a54f8-e56c-4a70-9719-3e5799e664bg755×446 23 KB

2
| username: waeng | Original post link

show stats_healthy;
analyze table table-name;

3
| username: muyubaby | Original post link

show stats_meta where db_name like ‘%sbtest%’;
Favorite :grinning:

4
| username: ShawnYan | Original post link

:heart: :heart: :heart:

-- Cache table:
alter table xxx cache|nocache;
5
| username: 我是咖啡哥 | Original post link

If you don’t have a few SQLs in hand, you’re embarrassed to call yourself a DBA :grinning:
Come on, everyone is welcome to add, like, and bookmark.

Commonly Used SQL in TiDB

6
| username: 我是咖啡哥 | Original post link

Add one more:

TSO Time Conversion

Method 1: Using the function TIDB_PARSE_TSO

SELECT TIDB_PARSE_TSO(437447897305317376);
+------------------------------------+
| TIDB_PARSE_TSO(437447897305317376) |
+------------------------------------+
| 2022-11-18 08:28:17.704000         |
+------------------------------------+
1 row in set (0.25 sec)

Method 2: Using pd-ctl

~$ tiup ctl:v6.4.0 pd -i -u http://pdip:2379
Starting component `ctl`: /Users/xxx/.tiup/components/ctl/v6.4.0/ctl pd -i -u http://pdip:2379
» tso 437447897305317376
system:  2022-11-18 08:28:17.704 +0800 CST
logic:   0
7
| username: Myth | Original post link

purge binary logs to ‘binlog.xxxx’;

8
| username: YuchongXU | Original post link

OK, please provide the text you need translated.

9
| username: TiDBer_m6V1BalM | Original post link

Query the default duration of tikv_gc_life_time and tikv_gc_safe_point.

10
| username: jxgahxs | Original post link

The most commonly used.

11
| username: Hacker007 | Original post link

The most used command is “show create table”, I’m not a DBA.

12
| username: fanruinet | Original post link

Search for a user’s TopN slow queries

select query_time, query, user
from information_schema.slow_query
where is_internal=false -- Exclude TiDB internal slow query SQL
and user = "user1" -- The username to search for
order by query_time desc
limit 2;
13
| username: ti-tiger | Original post link 
SELECT time, instance, LEFT(message, 150) 
FROM cluster_log 
WHERE message LIKE '%ddl%job%ID.80%' 
AND type='tidb'
14
| username: forever | Original post link

Recently, I’ve been using this to get data at 5-minute intervals:

SELECT concat(date_format(create_time,'%Y-%m-%d %H:'),floor(date_format(create_time,'%i')/5)), count(*)
FROM jcxx
GROUP BY 1;
15
| username: hey-hoho | Original post link

Reverse parse digest into SQL text

SELECT tidb_decode_sql_digests('["xxxxx"]');
16
| username: 裤衩儿飞上天 | Original post link

show stats_healthy where table_name=‘XXXX’;
show stats_meta where table_name=‘XXXX’;

17
| username: xfworld | Original post link

Contribution Excerpt

If partition tables are not involved, use the following method to check the usage of the table:

select TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS,
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 as table_size from tables order by table_size 
desc limit 20;

Additionally, partition tables provide resource usage information for both partitioned and non-partitioned tables (we use partitioned tables more frequently):

select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, TABLE_ROWS,
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 as table_size from 
information_schema.PARTITIONS order by table_size desc limit 20;
18
| username: Kongdom | Original post link

In the query analyzer, view the configuration file parameters: show config

The SHOW CONFIG statement is used to display the current configuration being applied by each component of TiDB. Please note that configurations and system variables operate on different dimensions, so do not confuse them. If you want to obtain information about system variables, please use the SHOW VARIABLES syntax.

19
| username: BraveChen | Original post link

Find the top 10 hotspot regions by read traffic

SELECT DISTINCT region_id FROM INFORMATION_SCHEMA.tikv_region_status ORDER BY READ_BYTES DESC LIMIT 10