aurora_stat_dml_activity - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

aurora_stat_dml_activity

报告 Aurora PostgreSQL 集群中数据库上每种类型的数据操作语言(DML)操作的累积活动。

Syntax

aurora_stat_dml_activity(database_oid)

返回类型

SETOF 记录

Arguments

database_oid

Aurora PostgreSQL 集群中数据库的对象 ID(OID)。

使用说明

aurora_stat_dml_activity 函数仅适用于 Aurora PostgreSQL 版本 3.1,与 PostgreSQL 引擎 11.6 及更高版本兼容。

在具有大量数据库的 Aurora PostgreSQL 集群上使用此函数,以确定哪些数据库具有更多或更慢的 DML 活动,或两者兼有。

aurora_stat_dml_activity 函数返回运行操作的次数以及选择、插入、更新和删除操作的累积延迟(以微秒为单位)。该报告仅包括成功的 DML 操作。

您可以使用 PostgreSQL 统计信息访问函数 pg_stat_reset 重置此统计信息。您可以使用 pg_stat_get_db_stat_reset_time 函数检查上次重置此统计信息的时间。有关 PostgreSQL 统计访问函数的更多信息,请参阅 PostgreSQL 文档中的统计收集器

Examples

以下示例说明如何报告连接的数据库的 DML 活动统计数据库。

-- Define the oid variable from connected database by using \gset => SELECT oid, datname FROM pg_database WHERE datname=(select current_database()) \gset => SELECT * FROM aurora_stat_dml_activity(:oid); select_count | select_latency_microsecs | insert_count | insert_latency_microsecs | update_count | update_latency_microsecs | delete_count | delete_latency_microsecs --------------+--------------------------+--------------+--------------------------+--------------+--------------------------+--------------+-------------------------- 178957 | 66684115 | 171065 | 28876649 | 519538 | 1454579206167 | 1 | 53027 -- Showing the same results with expanded display on => SELECT * FROM aurora_stat_dml_activity(:oid); -[ RECORD 1 ]------------+-------------- select_count | 178957 select_latency_microsecs | 66684115 insert_count | 171065 insert_latency_microsecs | 28876649 update_count | 519538 update_latency_microsecs | 1454579206167 delete_count | 1 delete_latency_microsecs | 53027

以下示例显示了 Aurora PostgreSQL 集群中所有数据库的 DML 活动统计信息。该集群有两个数据库 postgresmydb。逗号分隔的列表对应 select_countselect_latency_microsecsinsert_countinsert_latency_microsecsupdate_countupdate_latency_microsecsdelete_countdelete_latency_microsecs 字段。

Aurora PostgreSQL 创建并使用名为 rdsadmin 的系统数据库来支持管理操作,例如备份、还原、运行状况检查、复制等。这些 DML 操作对您的 Aurora PostgreSQL 集群没有任何影响。

=> SELECT oid, datname, aurora_stat_dml_activity(oid) FROM pg_database; oid | datname | aurora_stat_dml_activity -------+----------------+----------------------------------------------------------------- 14006 | template0 | (,,,,,,,) 16384 | rdsadmin | (2346623,1211703821,4297518,817184554,0,0,0,0) 1 | template1 | (,,,,,,,) 14007 | postgres | (178961,66716329,171065,28876649,519538,1454579206167,1,53027) 16401 | mydb | (200246,64302436,200036,107101855,600000,83659417514,0,0)

以下示例显示了所有数据库的 DML 活动统计信息,这些统计信息按列进行组织,以提高可读性。

SELECT db.datname, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 1), '()') AS select_count, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 2), '()') AS select_latency_microsecs, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 3), '()') AS insert_count, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 4), '()') AS insert_latency_microsecs, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 5), '()') AS update_count, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 6), '()') AS update_latency_microsecs, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 7), '()') AS delete_count, BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 8), '()') AS delete_latency_microsecs FROM (SELECT datname, aurora_stat_dml_activity(oid) AS asdmla FROM pg_database ) AS db; datname | select_count | select_latency_microsecs | insert_count | insert_latency_microsecs | update_count | update_latency_microsecs | delete_count | delete_latency_microsecs ----------------+--------------+--------------------------+--------------+--------------------------+--------------+--------------------------+--------------+-------------------------- template0 | | | | | | | | rdsadmin | 4206523 | 2478812333 | 7009414 | 1338482258 | 0 | 0 | 0 | 0 template1 | | | | | | | | fault_test | 66 | 452099 | 0 | 0 | 0 | 0 | 0 | 0 db_access_test | 1 | 5982 | 0 | 0 | 0 | 0 | 0 | 0 postgres | 42035 | 95179203 | 5752 | 2678832898 | 21157 | 441883182488 | 2 | 1520 mydb | 71 | 453514 | 0 | 0 | 1 | 190 | 1 | 152

以下示例显示具有 OID 16401 的数据库每个 DML 操作的平均累积延迟时间(累积延迟除以计数)。

=> SELECT select_count, select_latency_microsecs, select_latency_microsecs/NULLIF(select_count,0) select_latency_per_exec, insert_count, insert_latency_microsecs, insert_latency_microsecs/NULLIF(insert_count,0) insert_latency_per_exec, update_count, update_latency_microsecs, update_latency_microsecs/NULLIF(update_count,0) update_latency_per_exec, delete_count, delete_latency_microsecs, delete_latency_microsecs/NULLIF(delete_count,0) delete_latency_per_exec FROM aurora_stat_dml_activity(16401); -[ RECORD 1 ]------------+------------- select_count | 451312 select_latency_microsecs | 80205857 select_latency_per_exec | 177 insert_count | 451001 insert_latency_microsecs | 123667646 insert_latency_per_exec | 274 update_count | 1353067 update_latency_microsecs | 200900695615 update_latency_per_exec | 148478 delete_count | 12 delete_latency_microsecs | 448 delete_latency_per_exec | 37