PostgreSQL导入SLS,从业务到监控数据

本文涉及的产品
对象存储 OSS,20GB 3个月
对象存储 OSS,恶意文件检测 1000次 1年
对象存储 OSS,内容安全 1000 次 1年
简介: PostgreSQL是一款免费的对象-关系数据服务器,在互联网和物联网领域都有广泛的应用场景,PostgreSQL也自称是最强大的开源关系型数据库系统,SLS也在近期上线了PostgreSQL数据源导入功能。本文将介绍如何把PostgreSQL的数据导入SLS,并且从可观测性的角度来介绍下非业务类数据导入的场景。

日志服务SLS数据导入简介

日志服务SLS是云原生观测和分析平台,为Log、Metric、Trace等数据提供大规模、低成本、实时的平台化服务。日志服务是提供一站式数据采集、加工、查询与分析、可视化、告警、消费与投递等功能。全面提升在研发、运维、运营、安全等场景的数字化能力。SLS在数据导入方面提供了丰富的数据源,如OSS导入、MaxCompute、MySQL、SQL Server等数据源。

PostgreSQL是一款免费的对象-关系数据服务器,在互联网和物联网领域都有广泛的应用场景,PostgreSQL也自称是最强大的开源关系型数据库系统,SLS也在近期上线了PostgreSQL数据源导入功能。本文将介绍如何把PostgreSQL的数据导入SLS,并且从可观测性的角度来介绍下非业务类数据导入的场景。

PostgreSQL业务数据导入

这里以电商系统的订单表为例,如下是订单表orders的结构,存储了用户ID、价格、优惠券之类的字段,接下来可以通过简单的配置将订单数据导入到SLS。

CREATETABLE IF NOT EXISTS orders (  id serial primary key,  user_id BIGINTNOTNULL,  sku_id BIGINT,  sku_category VARCHAR(100),  sku_count INT,  sku_price INT,  total_price INT,  supplier_type VARCHAR(100),  pay_id VARCHAR(50),  pay_channel VARCHAR(20),  pay_amount INT,  coupon_id INT DEFAULT 0,  buy_channel VARCHAR(50) DEFAULT '',  state INTNOTNULL,  client_ip VARCHAR(15) DEFAULT '',  create_time timestamp(6) DEFAULT current_timestamp,  update_time timestamp(6) DEFAULT current_timestamp 
);

数据导入

根据已经创建好的Project、Logstore,展开Logstore的配置,选择数据导入,选择 + 按钮

PostgreSQL数据导入

快速数据导入页面,选择PostgreSQL - 数据导入

数据源配置

在数据源设置页面,填写配置名称、数据库连接地址等,如果使用VPC地址,需要勾选VPC地址,输入数据库所在的VPC实例ID。

下述示例中查询语句使用了SELECT * FROM orders WHERE id > $1 ORDER BY id,其中$1表示占位符,用来做CheckPoint使用。

高级配置

高级配置中,选择CheckPoint字段idCheckPoint字段初始值0,在初始执行SQL的过程中,第一次会将查询语句中的$1换成0,即:

SELECT * FROM orders WHERE id > 0 ORDER BY id

每次导入最大行数0,表示不限制拉取行数,在第一次执行后会将最后一条数据的id替换掉$1,这样就可以做到下次执行时和上次的数据不重复,两次执行的时间间隔即导入间隔

导入间隔配置

导入间隔,可以选择特定的时间间隔或者自定义时间间隔。

导入完成

在日志服务的Logstore查询控制台,可以看到导入后的数据,根据数据库字段配置索引,对数据进行查询分析,做分析订单趋势、订单分类、下单客户区域分布等分析,在SLS控制台可以创建仪表盘,对于分析一目了然,同时对于异常数据还可以支持告警等配置。

配置索引

查询数据

仪表盘展示

关于仪表盘的配置和查询分析,在上一篇文章《MySQL到SLS:一站式查询分析可视化场景介绍》有过比较详细的介绍,这里不再赘述。可以看下仪表盘的示例。

除了业务数据,还可以导哪些数据?

除了业务数据存储在PostgreSQL外,我们往往也需要关注PostgreSQL服务器本身的运行状态,对服务本身的监控,Prometheus社区也提供了postgres_exporter,其原理也是通过拉取PostgreSQL的系统表或者视图来进行数据的观测,其主要关注在指标层面,PostgreSQL还有一些元数据表,对于数据治理也是不可缺少的部分,接下来来看下哪些指标或者元数据可以导入到SLS来进行分析。

PostgreSQL可观测性

和MySQL类似,PostgreSQL也包含各类系统表,系统表中记录这数据库的一些数据库元信息和诊断信息,通过这些表或视图可以监控系统的状态,查询执行计划的状态和服务器管理状态,从某种程度上来说,这些表的信息可以反映PostgreSQL的可观测性。

在PostgreSQL的Monitoring Database Activity,对如何监控PostgreSQL进行了介绍,主要包括

  • 标准的Unix工具
  • 统计数据收集器
  • 查看锁
  • 进度报告
  • 动态追踪

其中统计数据收集器是收集和报告服务行为的子系统,可以对表和索引的访问次数进行计数,也可以追踪每张表的行数,和表的垃圾回收信息,统计用户自定义函数的调用次数和调用时间。同样支持PostgreSQL上正在执行的动态信息,包括服务器中正在执行的确切命令等。

开启统计数据采集,可以有postgresql.conf中的四个参数来控制:

  • track_activities:用来跟踪每个会话执行的命令和开始执行的时间。
  • track_counts:统计表和索引的访问次数。
  • track_functions:统计自定义函数调用。
  • track_io_timing:统计块IO的读写次数。

除了以上的参数外,还有其他的参数可以用来开启统计监控。

统计视图(Statistics Views)

PostgreSQL内置了一些统计视图可供直接使用,主要包括两部分:

  • 动态统计视图(Dynamic Statistics Views)
  • pg_stat_activity
  • pg_stat_replication
  • pg_stat_wal_receiver
  • pg_stat_subscription
  • pg_stat_ssl
  • ....
  • 已收集统计信息视图(Collected Statistics Views)
  • pg_stat_archiver
  • pg_stat_bgwriter
  • pg_stat_wal
  • pg_stat_database
  • pg_stat_database_conflicts
  • pg_stat_all_tables
  • ....

这些统计视图对于数据库的运行状态诊断有很重要的作用,比如慢SQL查询诊断,活跃的客户端查看,举个例子,我们通过SQL语句来查询执行时间长的语句:

select       current_timestamp - query_start as runtime,       datname,       usename,       query
from pg_stat_activity
where query <>''orderby runtime desc

结果如下,可以看到某些执行时间较长的语句。

runtime        | datname  |  usename   |                                            query                                            
----------------------+----------+------------+---------------------------------------------------------------------------------------------1 day 13:27:48.46965|| replicator | START_REPLICATION 0/3000000 TIMELINE 100:00:56.034221| postgres | aurora     |select slot_name client, pg_walfile_name(restart_lsn) wal, active from pg_replication_slots
(2 rows)

通过将这些结果导入到日志服务,可以进行分析统计、仪表盘配置和告警。

系统表

Postgresql系统表扮演着数据字典的角色,保存了数据库的元数据,包含所有对象和属性的描述信息,对象之间的关系信息。常用的系统表包括:

  • pg_namespace:存储命名空间,属性包括nspname、nspowner、nspacl等。
  • pg_tablespace:存储表空间信息,属性包括spcname、spcowner、spclocation、spcacl等。
  • pg_database:存储数据库信息,属性包括datname、datdba、encoding、datcollate等。
  • pg_class:存储数据库对象信息,包括索引、序列、视图等。
  • pg_type:存储数据类型信息。
  • pg_index:存储索引的具体信息。

以上数据系统表记录的数据库元信息,对于数据治理中有很重要的作用,将这些数据导出到数据分析平台就可以进行分析和持续的观测,比如需要观测数据字段的变更,举个例子:

SELECT relname,attname,typname,attnum FROM pg_class c,pg_attribute a,pg_type t 
WHERE c.relname='orders'AND c.oid= attrelid AND atttypid = t.oidAND attnum >0;

我们可以通过pg_class、pg_attribute、pg_type三个表的连接,获取数据库表的字段信息,这里示例中以orders表为例,可以得到结果如下:

relname |    attname    |  typname  | attnum 
---------+---------------+-----------+-------- orders  | id            |int4|1 orders  | user_id       |int8|2 orders  | sku_id        |int8|3 orders  | sku_category  |varchar|4 orders  | sku_count     |int4|5 orders  | sku_price     |int4|6 orders  | total_price   |int4|7 orders  | supplier_type |varchar|8 orders  | pay_id        |varchar|9 orders  | pay_channel   |varchar|10 orders  | pay_amount    |int4|11 orders  | coupon_id     |int4|12 orders  | buy_channel   |varchar|13 orders  | state         |int4|14 orders  | client_ip     |varchar|15 orders  | create_time   |timestamp|16 orders  | update_time   |timestamp|17(17 rows)

通过将这个查询的数据导入到SLS,并设置定时每5分钟导入一次,这样就可以看到整个时间线上表结构的变更,比如字段是否增加或减少

导入SLS结果查看

通过将数据导入SLS可以对元数据进行统计分析,按照时间定时导入,可以分析元数据随时间的变化趋势。对于数据库审计和数据治理都有很多场景。

扩展(EXTENSION)

PostgreSQL还有一个重要特性,支持扩展插件,通过扩展插件也可以进行一些数据的收集和统计。例如比较有名的插件pg_stat_statements,通过这个插件,可以统计数据库的资源开销和分析TOP SQL,阿里云RDS也提供了类似的案例

首先创建pg_stat_statements插件:CREATE EXTENSION pg_stat_statements;通过pt_stat_statements视图,可以查询数据库资源开销的统计信息,主要包括信息如下:

  • SQL 的调用次数,总耗时,最快执行时间,最慢执行时间,平均执行时间,执行时间的方差(看出抖动),总共扫描、返回或处理了多少行。
  • shared buffer 的使用情况:命中、未命中、产生脏块、驱逐脏块。
  • local buffer 的使用情况:命中、未命中、产生脏块、驱逐脏块。
  • temp buffer 的使用情况:读了多少脏块、驱逐脏块。
  • 数据块的读写时间。

例如查询耗IO的Top 5的SQL:

SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time)/calls DESC LIMIT 5;

最耗时的TOP 5 SQL

SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;

类似的还有查询响应时间抖动、共享内存消耗、临时空间消耗等指标的SQL,通过pg_stat_statements将指标信息导出,就可以对数据进行分析。

总结

本文首先介绍了PostgreSQL的数据导入功能,以电商系统中的订单表为例介绍了如何将PostgreSQL的数据导入SLS,同时介绍了如何使用SLS进行查询分析和仪表盘展示等。第二部分主要介绍了PostgreSQL的可观测性数据的导入场景,主要通过PostgreSQL的统计视图、系统表和扩展进行数据的导入来完成。

SLS的PostgreSQL导入作为一个工具,对于导入的数据场景没有偏见,业务数据和性能数据都可以导入,在使用的过程中可以挖掘出更多的场景。

参考

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
4月前
|
存储 缓存 Apache
StarRocks+Paimon 落地阿里日志采集:万亿级实时数据秒级查询
本文介绍了阿里集团A+流量分析平台的日志查询优化方案,针对万亿级日志数据的写入与查询挑战,提出基于Flink、Paimon和StarRocks的技术架构。通过Paimon存储日志数据,结合StarRocks高效计算能力,实现秒级查询性能。具体包括分桶表设计、数据缓存优化及文件大小控制等措施,解决高并发、大数据量下的查询效率问题。最终,日志查询耗时从分钟级降至秒级,显著提升业务响应速度,并为未来更低存储成本、更高性能及更多业务场景覆盖奠定基础。
|
20天前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL数据库的WAL日志与数据写入的过程
PostgreSQL中的WAL(预写日志)是保证数据完整性的关键技术。在数据修改前,系统会先将日志写入WAL,确保宕机时可通过日志恢复数据。它减少了磁盘I/O,提升了性能,并支持手动切换日志文件。WAL文件默认存储在pg_wal目录下,采用16进制命名规则。此外,PostgreSQL提供pg_waldump工具解析日志内容。
|
21天前
|
数据采集 运维 监控
|
5月前
|
监控 测试技术 Go
告别传统Log追踪!GOAT如何用HTTP接口重塑代码监控
本文介绍了GOAT(Golang Application Tracing)工具的使用方法,通过一个Echo问答服务实例,详细展示了代码埋点与追踪技术的应用。内容涵盖初始化配置、自动埋点、手动调整埋点、数据监控及清理埋点等核心功能。GOAT适用于灰度发布、功能验证、性能分析、Bug排查和代码重构等场景,助力Go项目质量保障与平稳发布。工具以轻量高效的特点,为开发团队提供数据支持,优化决策流程。
348 89
|
5月前
|
SQL 监控 数据挖掘
SLS 重磅升级:超大规模数据实现完全精确分析
SLS 全新推出的「SQL 完全精确」模式,通过“限”与“换”的策略切换,在快速分析与精确计算之间实现平衡,满足用户对于超大数据规模分析结果精确的刚性需求。标志着其在超大规模日志数据分析领域再次迈出了重要的一步。
465 117
|
3月前
|
存储 NoSQL MongoDB
Docker中安装MongoDB并配置数据、日志、配置文件持久化。
现在,你有了一个运行在Docker中的MongoDB,它拥有自己的小空间,对高楼大厦的崩塌视而不见(会话丢失和数据不持久化的问题)。这个MongoDB的数据、日志、配置文件都会妥妥地保存在你为它精心准备的地方,天旋地转,它也不会失去一丁点儿宝贵的记忆(即使在容器重启后)。
301 4
|
5月前
|
消息中间件 运维 监控
智能运维,由你定义:SAE自定义日志与监控解决方案
通过引入 Sidecar 容器的技术,SAE 为用户提供了更强大的自定义日志与监控解决方案,帮助用户轻松实现日志采集、监控指标收集等功能。未来,SAE 将会支持 istio 多租场景,帮助用户更高效地部署和管理服务网格。
384 52
|
6月前
|
数据采集 运维 监控
数据采集监控与告警:错误重试、日志分析与自动化运维
本文探讨了数据采集技术从“简单采集”到自动化运维的演进。传统方式因反爬策略和网络波动常导致数据丢失,而引入错误重试、日志分析与自动化告警机制可显著提升系统稳定性与时效性。正方强调健全监控体系的重要性,反方则担忧复杂化带来的成本与安全风险。未来,结合AI与大数据技术,数据采集将向智能化、全自动方向发展,实现动态调整与智能识别反爬策略,降低人工干预需求。附带的Python示例展示了如何通过代理IP、重试策略及日志记录实现高效的数据采集程序。
271 7
数据采集监控与告警:错误重试、日志分析与自动化运维
|
6月前
|
存储 监控 算法
基于 PHP 语言的滑动窗口频率统计算法在公司局域网监控电脑日志分析中的应用研究
在当代企业网络架构中,公司局域网监控电脑系统需实时处理海量终端设备产生的连接日志。每台设备平均每分钟生成 3 至 5 条网络请求记录,这对监控系统的数据处理能力提出了极高要求。传统关系型数据库在应对这种高频写入场景时,性能往往难以令人满意。故而,引入特定的内存数据结构与优化算法成为必然选择。
124 3

相关产品

  • 日志服务
  • 推荐镜像

    更多