"PostgreSQL 12: 新增 log_statement_sample_rate 参数控制数据库日志中慢SQL百分比"

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: PostgreSQL 提供的 log_min_duration_statement 参数设置后,数据库中执行时间超出设置值的SQL将记录到数据库中,此参数对所有库所有SQL都有效。维护PostgreSQL生产库时,数据库日志出现高频慢SQL实属正常,若其中一条比较繁忙的SQL若执行时间超过 log_min_duration_statement 设置值,那么数据库日志中将存在大量此条SQL的日志,这个日志量是很惊人的,多则一天上百GB。

PostgreSQL 提供的 log_min_duration_statement 参数设置后,数据库中执行时间超出设置值的SQL将记录到数据库中,此参数对所有库所有SQL都有效。维护PostgreSQL生产库时,数据库日志出现高频慢SQL实属正常,若其中一条比较繁忙的SQL若执行时间超过 log_min_duration_statement 设置值,那么数据库日志中将存在大量此条SQL的日志,这个日志量是很惊人的,多则一天上百GB。

PostgreSQL 12 提供了一个新的参数,能够有效的控制超出 log_min_duration_statement 参数设置值的SQL的日志量,这个参数为 log_statement_sample_rate,本文简单测试下。

发行说明

Allow logging of only a percentage of statements and transactions meeting log_min_duration_statement criteria (Adrien Nayrat)

The parameters log_statement_sample_rate and log_transaction_sample_rate control this.

本文后续仅介绍 log_statement_sample_rate, log_transaction_sample_rate 参数使用场景不是很多,不做介绍。

关于 log_statement_sample_rate

Determines the fraction of statements that exceed log_min_duration_statement to be logged. The default is 1.0, meaning log all such statements. Setting this to zero disables logging by duration, same as setting log_min_duration_statement to -1. log_statement_sample_rate is helpful when the traffic is too high to log all queries.

备注: 参数 log_statement_sample_rate 控制执行时间超出 log_min_duration_statement 参数设置值的 SQL 写数据库日志的百分比,默认值 1 ,表示比例为 100%,0 表示不记录,对于比较繁忙的生产库,此参数能有效缓解数据库日志量,减少无效日志。

环境准备

计划使用 pgbench 对数据库进行压力测试,执行大量SQL。

创建测试表并插入 100 万数据,如下:

CREATE TABLE log_sample(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());    
INSERT INTO  log_sample(id,name)  select n,n||'_test'  from generate_series(1,1000000) n;  
ALTER TABLE log_sample ADD PRIMARY KEY (id);  
VACUUM ANALYZE log_sample;

编写脚本 select_id.sql ,如下:

\set v_id random(1,1000000)

select name from log_sample where id=:v_id;

场景一: log_statement_sample_rate = 1.0

postgresql.conf 设置 log_statement_sample_rate 值 1.0,log_min_duration_statement 设置为 0,并执行 pg_ctl reload 使参数生效,如下:

log_statement_sample_rate = 1.0
log_min_duration_statement = 0

log_min_duration_statement 设置为 0 表示记录执行时间超出 0 毫秒的SQL都记录,方便测试。

清空 $PGDATA/pg_log 目录下的数据库日志,之后执行 pgbench,如下:

[pg12@pghost1 pgbench]$ pgbench -n -M prepared -c 2 -j 2 -T 60 -U pguser mydb -f select_id.sql
transaction type: select_id.sql
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 60 s
number of transactions actually processed: 498949
latency average = 0.241 ms
tps = 8315.763633 (including connections establishing)
tps = 8316.384134 (excluding connections establishing)

查看 pg_log 目录的数据库日志量为 242 MB 左右,如下:

[pg12@pghost1 pgbench]$ du -sm $PGDATA/pg_log
242     /pgdata/pg12/pg_root/pg_log

场景二: log_statement_sample_rate = 0.2

postgresql.conf 设置 log_statement_sample_rate 值 0.2,log_min_duration_statement 设置为 0,并执行 pg_ctl reload 使参数生效,如下:

log_statement_sample_rate = 0.2
log_min_duration_statement = 0

清空 $PGDATA/pg_log 目录下的数据库日志,之后执行 pgbench,如下:

[pg12@pghost1 pgbench]$ pgbench -n -M prepared -c 2 -j 2 -T 60 -U pguser mydb -f select_id.sql
transaction type: select_id.sql
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 60 s
number of transactions actually processed: 640501
latency average = 0.187 ms
tps = 10674.933384 (including connections establishing)
tps = 10675.548084 (excluding connections establishing)

查看 pg_log 目录的数据库日志量为 62 MB 左右,如下:

[pg12@pghost1 pgbench]$ du -sm $PGDATA/pg_log
62      /pgdata/pg12/pg_root/pg_log

总结

测试场景二的日志量确实只有场景一的 25% 左右,大幅减少了无效日志,同时 tps 也有一小幅度提升,可能是因为少写了大量数据库日志,缓解了CPU、IO资源压力。

参考

原文链接: https://postgres.fun/20190717141000.html

新书推荐

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

_5_PostgreSQL_

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
11天前
|
XML 安全 Java
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
本文介绍了Java日志框架的基本概念和使用方法,重点讨论了SLF4J、Log4j、Logback和Log4j2之间的关系及其性能对比。SLF4J作为一个日志抽象层,允许开发者使用统一的日志接口,而Log4j、Logback和Log4j2则是具体的日志实现框架。Log4j2在性能上优于Logback,推荐在新项目中使用。文章还详细说明了如何在Spring Boot项目中配置Log4j2和Logback,以及如何使用Lombok简化日志记录。最后,提供了一些日志配置的最佳实践,包括滚动日志、统一日志格式和提高日志性能的方法。
116 30
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
|
6天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
6天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤,您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。
|
7天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第7天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统要求检查与准备、配置安装源、安装 SQL Server 2019、配置 SQL Server 以及数据库初始化(可选)。通过这些步骤,你可以成功安装并初步配置 SQL Server 2019,进行简单的数据库操作。
|
17天前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
15 2
|
22天前
|
SQL JSON Java
没有数据库也能用 SQL
SPL(Structured Process Language)是一款开源软件,允许用户直接对CSV、XLS等文件进行SQL查询,无需将数据导入数据库。它提供了标准的JDBC驱动,支持复杂的SQL操作,如JOIN、子查询和WITH语句,还能处理非标准格式的文件和JSON数据。SPL不仅简化了数据查询,还提供了强大的计算能力和友好的IDE,适用于多种数据源的混合计算。
|
24天前
|
SQL 数据库
SQL数据库基础语法入门
[link](http://www.vvo.net.cn/post/082935.html)
|
1月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
45 0
|
1月前
|
Python
log日志学习
【10月更文挑战第9天】 python处理log打印模块log的使用和介绍
30 0
|
1月前
|
SQL 存储 监控
串口调试助手连接SQL数据库的技巧与方法
串口调试助手是电子工程师和软件开发人员常用的工具,它能够帮助用户进行串口通信的调试和数据分析