-- 获取所有连接的session级别变量信息 
select * from PERFORMANCE_SCHEMA.session_connect_attrs limit 10;

-- 统计Placement Rule个数
cat rules.json | jq -c '.[] | {"group_id": .group_id, "id": .rules[].id}' | grep -c "TiDB_DDL"

-- 统计TiFlash副本的表(分区)个数
cat rules.json | jq -c '.[] | {"group_id": .group_id, "id": .rules[].id}' | grep -c "tiflash"

-- 统计TiFlash表在TiKV的数据量是多少
select t.table_schema, round(sum(t.data_length)/pow(1024,3), 2) as size_G from information_schema.tables t join information_schema.tiflash_replica r on t.table_schema=r.table_schema and t.table_name = r.table_name  group by table_schema order by size_G;

-- 查看tiflash迁移进度
select t.table_schema, t.table_name, r.progress, round(t.data_length/pow(1024,3), 2) as size_G from information_schema.tables t join information_schema.tiflash_replica r on t.table_schema=r.table_schema and t.table_name = r.table_name where r.progress != 1 order by size_G;

-- 7.5版本不支持DISABLE_PRECHECK参数，8.1才支持
IMPORT INTO ying99_asset.service_account_info_latest FROM 's3://mysql-dts-migrate/asset-tidb-20240714-new/ying99_asset.service_account_info_latest.*.sql?access-key=LTAI5tMVKDBdFTLEsQ6afMdV&secret-access-key=kpjA0LED8BfKAraqzqnqbOZdNdSWy6&endpoint=https://oss-cn-shenzhen-internal.aliyuncs.com&force-path-style=false' FORMAT 'SQL' WITH  CLOUD_STORAGE_URI="s3://mysql-dts-migrate/import-sort-dir?access-key=LTAI5tMVKDBdFTLEsQ6afMdV&secret-access-key=kpjA0LED8BfKAraqzqnqbOZdNdSWy6&endpoint=https://oss-cn-shenzhen-internal.aliyuncs.com&force-path-style=false", DETACHED;

tiup br backup full   --pd="127.0.0.1:2379"   --storage="s3://mysql-dts-migrate/perf-asset-20240131?access-key=LTAI5tFC8f1atdUwCEEofaTx&secret-access-key=ZKxzGvGzsLlLgA1QpQTylbVw3gDWQ7"   --s3.endpoint="https://oss-cn-shenzhen-internal.aliyuncs.com"   --s3.provider "alibaba"   --log-file backup.log --checksum=false --ignore-stats=false  --redact-info-log

# 平均是1G/s的备份速度
[2024/01/31 14:51:01.649 +08:00] [INFO] [collector.go:78] ["Full Backup success summary"] [total-ranges=102028] [ranges-succeed=102028] [ranges-failed=0] [backup-checksum=3m36.346765761s] [backup-total-ranges=4192] [total-take=1h41m18.96030521s] [total-kv-size=6.258TB] [average-speed=1.029GB/s] [backup-data-size(after-compressed)=762.8GB] [Size=762778780135] [BackupTS=447395340771131394] [total-kv=27529433621]

-- 获取正在执行的慢 SQL 
select instance,user,time,id,left(info, 100) from information_schema.cluster_processlist where info is not null  and user not in ('tableau-research11') and time > 1;

-- 按表统计，在dw_ssd机器上的表大小(已经剔除on_data_new上的分区)
select 
    p.table_schema, p.TABLE_NAME, sum(p.TABLE_ROWS) table_rows, round(sum(p.DATA_LENGTH+p.INDEX_LENGTH)/1024/1024/1024 ,2) as size, count(1) as partition_num
from 
    information_schema.partitions p join information_schema.tables t on p.table_schema = t.table_schema and p.table_name = t.table_name
where 
    p.table_schema in ( select SCHEMA_NAME from information_schema.SCHEMATA where TIDB_PLACEMENT_POLICY_NAME='on_dw_ssd' ) 
    and (p.TIDB_PLACEMENT_POLICY_NAME != 'on_sata_new' or p.TIDB_PLACEMENT_POLICY_NAME is null) 
    and (t.TIDB_PLACEMENT_POLICY_NAME = 'on_dw_ssd')
    and p.table_rows >= 100
group by table_schema, table_name 
order by size desc 
limit 30;


-- 按表统计，在oltp-tikv机器上的表大小(已经剔除on_data_new上的分区)
select 
    p.table_schema, p.TABLE_NAME, sum(p.TABLE_ROWS) table_rows, round(sum(p.DATA_LENGTH+p.INDEX_LENGTH)/1024/1024/1024 ,2) as size, count(1) as partition_num
from 
    information_schema.partitions p join information_schema.tables t on p.table_schema = t.table_schema and p.table_name = t.table_name
where 
    p.table_schema in ( select SCHEMA_NAME from information_schema.SCHEMATA where TIDB_PLACEMENT_POLICY_NAME is null ) 
    and (p.TIDB_PLACEMENT_POLICY_NAME != 'on_sata_new' or p.TIDB_PLACEMENT_POLICY_NAME is null) 
    and (t.TIDB_PLACEMENT_POLICY_NAME is null)
    and p.table_rows >= 100
group by table_schema, table_name 
order by size desc 
limit 30;


-- 按表统计，在dw-tidb.gz机器上的表大小(已经剔除on_data上的分区)
select 
    p.table_schema, p.TABLE_NAME, sum(p.TABLE_ROWS) table_rows, round(sum(p.DATA_LENGTH+p.INDEX_LENGTH)/1024/1024/1024 ,2) as size, count(1) as partition_num
from 
    information_schema.partitions p join information_schema.tables t on p.table_schema = t.table_schema and p.table_name = t.table_name
where 
    p.table_schema in ( select SCHEMA_NAME from information_schema.SCHEMATA where TIDB_PLACEMENT_POLICY_NAME is null ) 
    and (p.TIDB_PLACEMENT_POLICY_NAME != 'on_sata' or p.TIDB_PLACEMENT_POLICY_NAME is null) 
    and (t.TIDB_PLACEMENT_POLICY_NAME is null)
    and p.table_rows >= 100
group by table_schema, table_name 
order by size desc 
limit 30;

-- 查看是否有长时间存在的事务
select INSTANCE,START_TIME,SESSION_ID,USER,STATE from INFORMATION_SCHEMA.CLUSTER_TIDB_TRX order by START_TIME;


-- 统计 ying99_asset3 数据分布情况
select 
    p.table_schema, p.TIDB_PLACEMENT_POLICY_NAME, sum(p.TABLE_ROWS) table_rows, round(sum(p.DATA_LENGTH+p.INDEX_LENGTH)/1024/1024/1024 ,2) as size
from 
    information_schema.partitions p join information_schema.tables t on p.table_schema = t.table_schema and p.table_name = t.table_name
where 
    p.table_schema = "ying99_asset3"
group by p.TIDB_PLACEMENT_POLICY_NAME
;


-- TiFlash 定时 Compact 历史分区，释放 Write 节点空间
-- 语法介绍： https://docs.pingcap.com/zh/tidb/stable/sql-statement-alter-table-compact/

-- 获取 DELTA 数据量大小
SELECT 
	TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, 
    ROUND(TOTAL_DELTA_SIZE/POWER(1024,3), 2) TOTAL_DELTA_SIZE_G, 
    ROUND(TOTAL_STABLE_SIZE/POWER(1024,3), 2) TOTAL_STABLE_SIZE_G
FROM 
	INFORMATION_SCHEMA.TIFLASH_TABLES t JOIN 
	INFORMATION_SCHEMA.PARTITIONS p ON t.TABLE_ID = p.TIDB_PARTITION_ID
WHERE t.IS_TOMBSTONE = 0 AND TOTAL_DELTA_SIZE > 10000
ORDER BY 
	TOTAL_DELTA_SIZE DESC LIMIT 100
;

ALTER TABLE yingmi_dw.dwd_portfolio_manager_info_snapshot COMPACT PARTITION p2024 TIFLASH REPLICA;

pt-online-schema-change -h fund-mysql.yingmi-inc.com -P 3306 -u fund_basic_root --charset=utf8 --max-load="Threads_running:25" --critical-load="Threads_running:100" --progress="time,1" --nocheck-replication-filters --retries=10 --alter="${ALTER_SQL}" D=ying99_fdp,t=ads_fund_metrics --recursion-method=none --drop-old-table=false --exec --print --ask-pass