KingbaseESV8R6识别IO使用率过高
前言
数据库正常运行离不开I/O的使用，在操作系统上，I/O又离不开存储的性能及使用方式，我们可以在存储层利用raid条带化技术使IOPS达到最佳性能。
本篇文章有助于确认数据库I/O使用率过高的原因 ，以及解决方法。
确定I/O使用率过高的原因
1.需要数据库内置的sys_stat_statements扩展
并需要开启
track_io_timing = on
track_wait_timing = on
使用以下语句查看使用 I/O 消耗前五 SQL 语句：
SELECT userid::regrole, dbid, query
FROM sys_stat_statements
ORDER BY blk_read_time + blk_write_time desc
LIMIT 5;
2.长时间运行的事务
长时间运行的事务可能导致 I/O 使用率过高。
以下sql有助于查看运行时间最长的连接：
SELECT pid, usename, datname, query, now() - xact_start as duration
FROM sys_stat_activity
WHERE pid <> sys_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration desc;
3.检查点消耗的I/O
数据库生成检查点会将脏块写入磁盘，为了数据安全而持久化数据。这个过程需要消耗I/O资源。有时可以从sys_log日志中看到，“检查点过于频繁的字样”，或者从数据库日志中看到checkpoint_write耗时过长。
通过sys_stat_bgwriter 显示有关后台写进程的活动的统计信息。可以计算已经被执行的计划中检查点的数量，在文件被写入磁盘的检查点处理部分花费的总时间等，详情可参考官方文档 https://help.kingbase.com.cn/v8/admin/reference/ref-database-parameter/ref-dynamic-performance-views/sys_stat_bgwriter.html?highlight=sys_stat_bgwriter
4.vacuum消耗I/O
vacuum是数据库中的重I/O操作，由于MVCC特性，vacuum操作不可避免。
以下语句帮助查看vacuum的发生频率。
SELECT schemaname, relname, n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count
FROM sys_stat_all_tables WHERE n_live_tup > 0;
last_autovacuum：上次 autovacuum 在表上运行的日期和时间。autovacuum_count：autovacuum表的次数。autoanalyze_count：autoanalyze表的次数。
解决 I/O 使用率过高的问题
1.EXPLAIN ANALYZE 命令进一步查询sql的执行机会，并对其进行优化。
2.终止长时间运行的事务
若要终止会话的进程 PID，需要使用以下查询：
SELECT pid, usename, datname, query, now() - xact_start as duration
FROM sys_stat_activity
WHERE pid <> sys_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;
还可以按其他条件进行筛选，例如 usename（用户名）或 datname（数据库名称）。
获取会话的 PID 后，可以使用以下语句终止它：
SELECT pg_terminate_backend(pid);
3.参数优化
如果观察到检查点发生频率过高，可以增大 max_wal_size 参数。两个检查点之间的间隔应该接近服务器上设置的 checkpoint_timeout 值。
通过以下方法设置max_wal_size一个合理的值：
运行以下查询以获取当前的 WAL LSN，然后记下结果：
select pg_current_wal_lsn();
等待 checkpoint_timeout 设置的秒数。 然后运行以下查询以获取当前的 WAL LSN，然后记下结果：
select pg_current_wal_lsn();
运行以下查询，计算两个lsn结果差值（以 GB 为单位）：
select round (sys_wal_lsn_diff ('LSN value when run second time', 'LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;
-
checkpoint_completion_target：建议将值设置为 0.9。 例如，为 10 分钟的checkpoint_timeout使用值 0.9 表示完成检查点的目标是 540 秒（0.9*600 秒）。 值 0.9 可作为 I/O 负载的评估值。 使用更激进的checkpoint_completion_target值可能导致服务器上的 I/O 负载增加。 -
checkpoint_timeout：可以增大checkpoint_timeout值，而不是使用默认值。 增大该值时，虽然I/O负载会降低，但也请考虑到会增大故障恢复时间。
4.优化 autovacuum 进程
持续运行自动清理任务可能会影响服务器上的 CPU 和 IO 使用率。 可能的原因如下：
如果 maintenance_work_mem 较低，则可将此参数增加到 2 GB。 一般的经验是，为每 1 GB 的 RAM 分配50MB的maintenance_work_mem 。
自动清理任务每隔 autovacuum_naptime 秒尝试在每个数据库上启动一个worker。
例如，如果一个服务器有 60 个数据库并且 autovacuum_naptime 设置为 60 秒，那么自动清理woker每秒启动一次 [autovacuum_naptime/DB 数]。
如果有最老事务阻止了vacuum的进行，那么vacuum worker会在表上做无用功，这也是需要消耗I/O的，所以对于未结束的长事务我们需要重点监控。
5.使用高性能存储，扩展磁盘IOPS能力。
