前言
数据库正常运行离不开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能力。
- 使用率 KingbaseESV8R6 KingbaseESV8 KingbaseESV 8R使用率kingbaseesv8r6 kingbaseesv8 kingbaseesv kingbaseesv8r6 kingbaseesv8r6 kingbaseesv8 kingbaseesv权限 kingbaseesv8r6 kingbaseesv8 kingbaseesv kbbench kingbaseesv8r6 kingbaseesv8 kingbaseesv pageinspect kingbaseesv8r6 kingbaseesv8 kingbaseesv字母 kingbaseesv8r6全局kingbaseesv8 kingbaseesv 末端kingbaseesv8r6 kingbaseesv8 kingbaseesv kingbaseesv8r6 kingbaseesv8 kingbaseesv参数 kingbaseesv8r6 kingbaseesv8 kingbaseesv索引