Clickhouse 性能瓶颈排查 IO过高
前几天公司clickhouse 有个查询很慢。经理一直追问为什么慢 是cpu 不够 还是IO 占用太高，还是其他的原因。于是有了以下的排查 执行该条，在不考虑优化sql 的情况下 进行性能排查
1、首先便是万能的 top第三行CPU信息统计数据：
%Cpu(s): 0.3 us, 0.2 sy, 0.0 ni, 99.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
```
Cpu(s)：
0.3% us： 用户空间占用CPU百分比
0.2% sy： 内核(系统)空间占用CPU百分比
0.0% ni： 用户进程空间内改变过优先级的进程占用CPU百分比
99.7% id： 空闲CPU百分比
0.0% wa： 等待输入输出的CPU时间百分比
0.0%hi： 硬件CPU中断占用百分比
0.0%si： 软中断占用百分比
0.0%st： 虚拟机(虚拟化技术)占用百分比
KiB Mem : 13142010+total, 54558996 free, 49383624 used, 27477484 buff/cache
1004768k total： 物理内存总量
463092k used： 使用的物理内存总量
541676k free： 空闲内存总量
64316k buffers： 用作内核缓存的内存量
KiB Swap: 67108860 total, 64400272 free, 2708588 used. 80281728 avail Mem
694268k total： 交换区总量
0 k used： 使用的交换区总量
694268k free： 空闲交换区总量
224884k cached：缓冲的交换区总量
```
未执行sql 
执行sql 时
对比上面的，可以看出clickhouse 这个进程虽然cpu占用是200%，占比几乎用到 2core，但是服务器总cpu使用率只有 4.4（服务器48core）。内存空闲也比较高。
然后还有一个就是wa 比较高，wa 对应的是 等待输入输出的CPU时间百分比。
于是猜测是io 较高（其实早就猜到io 高）于是有了重头戏 pidstat iostat
2、pidstat
先清楚下clickhouse的缓存。
# clickhouse缓存是linux 的 page cache中 sync echo 3 >/proc/sys/vm/drop_caches # 命令的含义：展示I/O统计，每秒更新一次 pidstat -d 1
未执行sql时
11时24分33秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时24分34秒 990 9048 26025.93 0.00 0.00 java 11时24分34秒 990 442735 10540.74 0.00 0.00 du 11时24分34秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时24分35秒 990 9048 446176.00 0.00 0.00 java 11时24分35秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时24分36秒 990 9048 22112.00 0.00 0.00 java 11时24分36秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时24分37秒 990 9048 14436.00 0.00 0.00 java 11时24分37秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时24分38秒 990 9048 20964.00 0.00 0.00 java 11时24分38秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时24分39秒 990 9048 19136.00 0.00 0.00 java 11时24分39秒 0 247761 0.00 8.00 0.00 presto-server ``` 在执行sql 的时候 执行了pidstat，命令显示结果如下 ``` 11时21分47秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时21分48秒 990 9048 12381.48 0.00 0.00 java 11时21分48秒 983 124101 0.00 3.70 0.00 java 11时21分48秒 963 348889 153166.67 3.70 0.00 clickhouse-serv 11时21分48秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时21分49秒 990 9048 22996.00 0.00 0.00 java 11时21分49秒 962 78518 356.00 0.00 0.00 zabbix_agentd 11时21分49秒 963 348889 93948.00 0.00 0.00 clickhouse-serv 11时21分49秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时21分50秒 0 1323 0.00 16.00 0.00 jbd2/sdb-8 11时21分50秒 0 4251 0.00 12.00 0.00 java 11时21分50秒 990 9048 17576.00 0.00 0.00 java 11时21分50秒 963 348889 150992.00 4.00 0.00 clickhouse-serv 11时21分50秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时21分51秒 990 9048 24484.00 0.00 0.00 java 11时21分51秒 963 348889 151164.00 0.00 0.00 clickhouse-serv 11时21分51秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command 11时21分52秒 990 9048 16096.00 0.00 0.00 java 11时21分52秒 1000 148747 0.00 8.00 0.00 mysqld 11时21分52秒 963 348889 175228.00 0.00 0.00 clickhouse-serv
很明显clickhouse 读磁盘io最大到了 175228.kB/s 换算成 171M/s。但这边的clickhouse 数据目录我是分到7块磁盘，7块磁盘的读速度 171M/s 并不是很高，单块磁盘表现好可以又120M/s。但此时性能的瓶颈差不多可以确定为IO
3、iostat
先清楚缓存
使用命令“iostat -x 1“确定哪个磁盘设备IO负载高: iostat -x 1
我这边挂在的时 data1 2 3 4 5 6 7
df -l ev/sda1 1038336 145704 892632 15% /boot /dev/sdf 2306651404 460825460 1728631192 22% /data5 /dev/sdh 2306651404 1097399924 1092056728 51% /data7 /dev/sdb 2306651404 1086978144 1102478508 50% /data1 /dev/sde 2306651404 796331792 1393124860 37% /data4 /dev/sdd 2306651404 899894556 1289562096 42% /data3 /dev/sdg 2306651404 628524548 1560932104 29% /data6 /dev/sdc 2306651404 1796066508 393390144 83% /data2
未执行sql时
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 5.00 8.00 13.00 56.00 10.62 0.01 0.69 1.80 0.00 0.69 0.90
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.37 46.75 46.75 0.00 6.38 5.10
sdd 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.51 64.00 64.00 0.00 8.62 6.90
sde 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.17 21.75 21.75 0.00 5.38 4.30
sdf 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.08 9.62 9.62 0.00 2.00 1.60
sdh 0.00 0.00 64.00 0.00 1812.00 0.00 56.62 0.14 2.12 2.12 0.00 1.28 8.20
sdg 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.12 15.00 15.00 0.00 2.00 1.60
sdi 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.13 16.75 16.75 0.00 2.25 1.80
sdj 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdl 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.16 19.88 19.88 0.00 2.62 2.10
sdn 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdm 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdp 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdq 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdr 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.20 25.62 25.62 0.00 6.12 4.90
sds 0.00 0.00 1.00 0.00 24.00 0.00 48.00 0.02 16.00 16.00 0.00 16.00 1.60
sdt 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 5.00 8.00 13.00 56.00 10.62 0.01 0.69 1.80 0.00 0.69 0.90
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.52 0.00 0.29 0.06 0.00 99.12
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.19 23.50 23.50 0.00 3.00 2.40
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdf 0.00 0.00 1.00 0.00 128.00 0.00 256.00 0.00 0.00 0.00 0.00 0.00 0.00
sdh 0.00 0.00 13.00 0.00 2432.00 0.00 374.15 0.12 9.31 9.31 0.00 3.46 4.50
sdg 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdi 0.00 0.00 1.00 0.00 128.00 0.00 256.00 0.00 0.00 0.00 0.00 0.00 0.00
sdj 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.14 17.12 17.12 0.00 2.25 1.80
sdk 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.15 19.00 19.00 0.00 2.50 2.00
sdl 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdn 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.17 21.38 21.38 0.00 2.88 2.30
sdo 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.33 40.75 40.75 0.00 5.88 4.70
sdm 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.14 18.00 18.00 0.00 2.38 1.90
sdp 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.11 13.75 13.75 0.00 1.88 1.50
sdq 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.09 11.25 11.25 0.00 1.62 1.30
sdr 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sds 0.00 0.00 9.00 0.00 1828.00 0.00 406.22 0.20 22.78 22.78 0.00 2.67 2.40
sdt 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.01 1.50 1.50 0.00 0.38 0.30
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
执行sql时：
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 448.00 2.00 15168.00 8.00 67.45 0.85 1.88 1.89 0.00 1.82 81.90
sdb 0.00 0.00 1481.00 0.00 190052.00 0.00 256.65 42.70 23.77 23.77 0.00 0.68 100.30
sdc 0.00 0.00 1.00 0.00 128.00 0.00 256.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.07 8.75 8.75 0.00 4.12 3.30
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdh 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdg 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.16 19.75 19.75 0.00 5.12 4.10
sdi 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdj 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.37 46.38 46.38 0.00 7.62 6.10
sdk 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.19 24.00 24.00 0.00 3.12 2.50
sdl 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdn 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdo 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.13 16.12 16.12 0.00 5.50 4.40
sdm 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.01 1.50 1.50 0.00 0.25 0.20
sdp 0.00 0.00 10.00 0.00 2096.00 0.00 419.20 0.21 21.10 21.10 0.00 5.00 5.00
sdq 0.00 0.00 3869.00 0.00 15476.00 0.00 8.00 2.98 0.77 0.77 0.00 0.20 77.50
sdr 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.27 33.25 33.25 0.00 6.88 5.50
sds 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.20 24.50 24.50 0.00 3.12 2.50
sdt 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 448.00 2.00 15156.00 8.00 67.40 0.85 1.89 1.90 0.00 1.82 81.80
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
9.43 0.00 2.47 29.14 0.00 58.96
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 699.00 0.00 9424.00 0.00 26.96 0.50 0.72 0.72 0.00 0.59 40.90
sdb 0.00 4.00 1415.00 4.00 182672.00 32.00 257.51 49.25 37.00 37.11 0.00 0.71 100.30
sdc 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.02 2.50 2.50 0.00 0.50 0.40
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.17 21.12 21.12 0.00 2.88 2.30
sdf 0.00 0.00 19.00 0.00 4116.00 0.00 433.26 0.32 16.84 16.84 0.00 3.11 5.90
sdh 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.34 42.00 42.00 0.00 5.88 4.70
sdg 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdi 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.41 50.62 50.62 0.00 6.88 5.50
sdj 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdk 0.00 0.00 1.00 0.00 128.00 0.00 256.00 0.00 0.00 0.00 0.00 0.00 0.00
sdl 0.00 0.00 8.00 0.00 2048.00 0.00 512.00 0.31 38.88 38.88 0.00 5.25 4.20
sdn 0.00 0.00 6.00 0.00 1508.00 0.00 502.67 0.25 42.00 42.00 0.00 7.67 4.60
sdo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdm 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdp 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdq 0.00 0.00 3989.00 0.00 17968.00 0.00 9.01 1.58 0.40 0.40 0.00 0.19 77.40
sdr 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
很明显 sdb 对于的磁盘 使用率达到了 100% 读的速度为 155756kB/s 对于 152M/s
/dev/sdb 2306651404 1086978144 1102478508 50% /data1
data1 就是我们的一块数据盘。
这下就时很明显，改sql 查询慢的瓶颈就是IO
