## 1. 环境准备
$ for host in tiflash-1 tiflash-2; do
ssh $host “hostnamectl set-hostname $host”
ssh $host “yum update -y”
ssh $host “yum install -y curl tar openssh-clients sudo python3”
ssh $host “systemctl stop firewalld && systemctl disable firewalld”
ssh $host “setenforce 0 && sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/g’ /etc/selinux/config”
ssh $host “mkdir -p /tidb/app /tidb/fgdata /tidb/log”
ssh $host “chown -R tidb:tidb /tidb”
ssh $host “echo ‘vm.swappiness = 0’ >> /etc/sysctl.conf”
ssh $host “echo ‘vm.overcommit_memory = 1’ >> /etc/sysctl.conf”
ssh $host “echo ‘net.core.somaxconn = 32768’ >> /etc/sysctl.conf”
ssh $host “sysctl -p”
done

## 2. 网络配置
$ cat >> /etc/hosts << EOF 192.168.1.101 pd-1 192.168.1.102 pd-2 192.168.1.103 pd-3 192.168.1.201 tikv-1 192.168.1.202 tikv-2 192.168.1.203 tikv-3 192.168.1.301 tiflash-1 192.168.1.302 tiflash-2 EOF ## 3. 部署TiFlash $ cat > tiflash-topology.yaml << EOF global: user: "tidb" ssh_port: 22 deploy_dir: "/tidb/app" data_dir: "/tidb/fgdata" pd_servers: - host: 192.168.1.101 - host: 192.168.1.102 - host: 192.168.1.103 tikv_servers: - host: 192.168.1.201 - host: 192.168.1.202 - host: 192.168.1.203 tiflash_servers: - host: 192.168.1.301 - host: 192.168.1.302 tidb_servers: - host: 192.168.1.401 - host: 192.168.1.402 EOF $ tiup cluster deploy fgedu-tidb-cluster v7.5.0 tiflash-topology.yaml --user root ## 4. 启动集群 $ tiup cluster start fgedu-tidb-cluster ## 5. 验证TiFlash状态 $ tiup cluster display fgedu-tidb-cluster $ tiup cluster exec fgedu-tidb-cluster -R tiflash --command "curl -s http://localhost:20290/status"

## 1. 连接到TiDB
$ mysql -h 192.168.1.401 -P 4000 -u root -p

## 2. 创建测试表
CREATE DATABASE fgedudb;
USE fgedudb;
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100),
created_at DATETIME
);

## 3. 为表添加TiFlash副本
ALTER TABLE fgedu_users SET TIFLASH REPLICA 1;

## 4. 检查TiFlash副本状态
SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = ‘fgedudb’ AND TABLE_NAME = ‘fgedu_users’;

## 5. 插入测试数据
INSERT INTO fgedu_users VALUES
(1, ‘fgedu01’, 25, ‘fgedu01@example.com’, NOW()),
(2, ‘fgedu02’, 30, ‘fgedu02@example.com’, NOW()),
(3, ‘fgedu03’, 35, ‘fgedu03@example.com’, NOW()),
(4, ‘fgedu04’, 40, ‘fgedu04@example.com’, NOW()),
(5, ‘fgedu05’, 45, ‘fgedu05@example.com’, NOW());

## 6. 执行分析查询
EXPLAIN ANALYZE SELECT age, COUNT(*) FROM fgedu_users GROUP BY age;

## 7. 验证查询使用了TiFlash
EXPLAIN SELECT age, COUNT(*) FROM fgedu_users GROUP BY age;

## 1. 连接到TiDB
$ mysql -h 192.168.1.401 -P 4000 -u root -p

## 2. 创建测试表
USE fgedudb;
CREATE TABLE fgedu_orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
status VARCHAR(20),
created_at DATETIME
);

## 3. 为表添加TiFlash副本
ALTER TABLE fgedu_orders SET TIFLASH REPLICA 1;

## 4. 插入大量测试数据
DELIMITER //
CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000000 DO INSERT INTO fgedu_orders VALUES (i, (i % 1000) + 1, RAND() * 1000, 'completed', NOW() - INTERVAL (i % 365) DAY); SET i = i + 1; END WHILE; END // DELIMITER ; CALL insert_test_data(); ## 5. 执行复杂分析查询 EXPLAIN ANALYZE SELECT DATE(created_at) AS order_date, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM fgedu_orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY order_date ORDER BY order_date; ## 6. 比较TiKV和TiFlash查询性能 -- 强制使用TiKV EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIKV[orders]) */ DATE(created_at) AS order_date, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM fgedu_orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY order_date ORDER BY order_date; -- 强制使用TiFlash EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */ DATE(created_at) AS order_date, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM fgedu_orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY order_date ORDER BY order_date;

## 问题现象
– TiFlash节点状态为Down
– 集群告警：TiFlashDown
– 分析查询性能下降

## 解决方案
1. 检查节点状态
$ ssh tiflash-1 “systemctl status tiflash”

2. 检查TiFlash日志
$ tiup cluster logs fgedu-tidb-cluster -R tiflash

3. 检查硬件状态
$ ssh tiflash-1 “df -h”
$ ssh tiflash-1 “free -m”
$ ssh tiflash-1 “iostat -x 1 10”

4. 重启TiFlash节点
$ tiup cluster start fgedu-tidb-cluster -R tiflash

5. 验证节点状态
$ tiup cluster display fgedu-tidb-cluster

## 问题现象
– 集群告警：TiFlashReplicaLag
– 分析查询结果可能不是最新的
– TiFlash同步速度慢

## 解决方案
1. 检查同步状态
$ tiup cluster exec fgedu-tidb-cluster -R tiflash –command “curl -s http://localhost:20290/status”

2. 检查网络连接
$ ping -c 10 tikv-1
$ iperf3 -c tikv-1

3. 检查TiKV状态
$ tiup cluster exec fgedu-tidb-cluster -R tikv –command “curl -s http://localhost:20180/status”

4. 调整TiFlash配置
$ tiup cluster edit-config fgedu-tidb-cluster

# 在配置文件中添加或修改以下内容
server_configs:
tiflash:
flash:
replica:
apply_batch_size: 1024
apply_thread_count: 8

5. 应用配置变更
$ tiup cluster reload fgedu-tidb-cluster -R tiflash