8 个不稳定版本 (3 个重大更改)
| 0.3.2 | 2024年1月28日 |
|---|---|
| 0.3.1 | 2024年1月26日 |
| 0.2.0 | 2024年1月24日 |
| 0.1.0 | 2024年1月23日 |
| 0.0.2 | 2023年12月30日 |
在 数据库接口 中排名第 522
每月下载量 23
105KB
2K SLoC
rust-pg-extras 
Rust 版本的 Heroku PG Extras，增加了几个功能和改进。该项目旨在为不使用 Heroku PostgreSQL 插件的 Rust 应用程序提供对 PostgreSQL 数据库的强大洞察力。
可以使用查询来获取有关 Postgres 实例的信息，这在分析性能问题时可能很有用。这包括有关锁定、索引使用、缓冲区缓存命中率以及清理统计信息的信息。Rust API 使开发人员能够轻松地将工具集成到例如自动监控任务中。
您可以查看这篇博客文章，了解如何使用 PG Extras 库优化 PostgreSQL的详细分步教程。
替代版本
安装
在您的 Cargo.toml 中
pg-extras = "0.3"
calls 和 outliers 查询需要 pg_stat_statements 扩展。
您可以通过运行以下命令来检查您的数据库是否已启用
use pg_extras::{render_table, extensions}
render_table(extensions()?);
您应该在输出中看到类似的行
| pg_stat_statements | 1.7 | 1.7 | track execution statistics of all SQL statements executed |
ssl_used 需要 sslinfo 扩展，而 buffercache_usage/buffercache_usage 查询需要 pg_buffercache。您可以通过运行以下 SQL 启用它们
CREATE EXTENSION IF NOT EXISTS sslinfo;
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
用法
包期望以下格式的 ENV['PG_EXTRAS_DATABASE_URL'] 或 ENV['DATABASE_URL'] 值
ENV["DATABASE_URL"] = "postgresql://postgres:secret@localhost:5432/database_name"
您可以使用Rust API运行查询以显示ASCII表的结果
use pg_extras::{render_table, cache_hit}
render_table(cache_hit(None).await?);
+----------------+------------------------+
| Index and table hit rate |
+----------------+------------------------+
| name | ratio |
+----------------+------------------------+
| index hit rate | 0.97796610169491525424 |
| table hit rate | 0.96724294813466787989 |
+----------------+------------------------+
或者，您可以直接与返回的结构体一起工作
use pg_extras::{render_table, cache_hit, CacheHit}
let cache_hit_res: Vec<CacheHit> = cache_hit(None).await?;
println!("{:?}", cache_hit_res);
// [CacheHit { name: "index hit rate", ratio: 0.9779... }, CacheHit { name: "table hit rate", ratio: 0.9672... }]
一些方法接受参数，允许您自定义查询
cache_hit(Some("other_schema".to_string)).await?;
您可以通过设置 ENV['PG_EXTRAS_SCHEMA'] 来自定义默认的 public 模式。
命令行
运行 cargo install pg-extras 后，您可以使用 pg_extras 壳命令。
$ pg_extras cache_hit
+----------------+------------------------+
| /* Index and table hit rate */ |
+================+========================+
| name | ratio |
+----------------+------------------------+
| index hit rate | 0.99138647287107053837 |
+----------------+------------------------+
| table hit rate | 0.99984856854492081787 |
+----------------+------------------------+
可用方法
cache_hit
struct CacheHit {
name: String,
ratio: Decimal,
}
cache_hit(schema: Option<String>) -> Result<Vec<CacheHit>, PgExtrasError>
name | ratio
----------------+------------------------
index hit rate | 0.99957765013541945832
table hit rate | 1.00
(2 rows)
此命令提供有关缓冲区缓存效率的信息，包括索引读取（索引命中率）和表读取（表命中率）。低缓冲区缓存命中率可能是Postgres实例对工作负载过小的迹象。
index_cache_hit
struct IndexCacheHit {
name: String,
buffer_hits: i64,
block_reads: i64,
total_read: i64,
ratio: String,
}
index_cache_hit(schema: Option<String>) -> Result<Vec<IndexCacheHit>, PgExtrasError>
| name | buffer_hits | block_reads | total_read | ratio |
+-----------------------+-------------+-------------+------------+-------------------+
| teams | 187665 | 109 | 187774 | 0.999419514948821 |
| subscriptions | 5160 | 6 | 5166 | 0.99883855981417 |
| plans | 5718 | 9 | 5727 | 0.998428496595076 |
(truncated results for brevity)
与 cache_hit 相同，但每个表的索引缓存命中率信息分别显示。
table_cache_hit
struct TableCacheHit {
name: String,
buffer_hits: i64,
block_reads: i64,
total_read: i64,
ratio: String,
}
table_cache_hit() -> Result<Vec<TableCacheHit>, PgExtrasError>
| name | buffer_hits | block_reads | total_read | ratio |
+-----------------------+-------------+-------------+------------+-------------------+
| plans | 32123 | 2 | 32125 | 0.999937743190662 |
| subscriptions | 95021 | 8 | 95029 | 0.999915815172211 |
| teams | 171637 | 200 | 171837 | 0.99883610631005 |
(truncated results for brevity)
与 cache_hit 相同，但每个表的缓存命中率信息分别显示。
db_settings
struct DbSettings {
name: String,
setting: String,
unit: String,
short_desc: String,
}
db_settings() -> Result<Vec<DbSettings>, PgExtrasError>
name | setting | unit |
------------------------------+---------+------+
checkpoint_completion_target | 0.7 | |
default_statistics_target | 100 | |
effective_cache_size | 1350000 | 8kB |
effective_io_concurrency | 1 | |
(truncated results for brevity)
此方法显示选定PostgreSQL设置的值。您可以将其与PGTune 推荐的设置进行比较，并调整值以提高性能。
ssl_used
struct SslUsed {
ssl_used: bool,
}
ssl_used() -> Result<Vec<SslUsed>, PgExtrasError>
| ssl_is_used |
+---------------------------------+
| t |
返回一个布尔值，指示是否当前使用了加密的SSL。通过未加密的连接连接到数据库是严重的安全风险。
index_usage
struct IndexUsage {
relname: String,
percent_of_times_index_used: String,
rows_in_table: i64,
}
index_usage(schema: Option<String>) -> Result<Vec<IndexUsage>, PgExtrasError>
relname | percent_of_times_index_used | rows_in_table
---------------------+-----------------------------+---------------
events | 65 | 1217347
app_infos | 74 | 314057
app_infos_user_info | 0 | 198848
user_info | 5 | 94545
delayed_jobs | 27 | 0
(5 rows)
此命令提供有关索引效率的信息，表示为总扫描中索引扫描的百分比。低百分比可能表明索引不足，或者索引了错误的数据。
locks
struct Locks {
pid: i32,
relname: String,
transactionid: String,
granted: bool,
mode: String,
query_snippet: String,
age: String,
application: String,
}
locks() -> Result<Vec<Locks>, PgExtrasError>
procpid | relname | transactionid | granted | query_snippet | mode | age | application |
---------+---------+---------------+---------+-----------------------+------------------------------------------------------
31776 | | | t | <IDLE> in transaction | ExclusiveLock | 00:19:29.837898 | bin/rails
31776 | | 1294 | t | <IDLE> in transaction | RowExclusiveLock | 00:19:29.837898 | bin/rails
31912 | | | t | select * from hello; | ExclusiveLock | 00:19:17.94259 | bin/rails
3443 | | | t | +| ExclusiveLock | 00:00:00 | bin/sidekiq
| | | | select +| | |
| | | | pg_stat_activi | | |
(4 rows)
此命令显示已对关系施加独占锁的查询。独占锁通常防止对该关系的其他操作，可能是导致“挂起”的查询等待锁授予的原因。
all_locks
struct AllLocks {
pid: String,
relname: String,
transactionid: String,
granted: String,
mode: String,
query_snippet: String,
age: String,
application: String,
}
all_locks() -> Result<Vec<AllLocks>, PgExtrasError>
此命令显示所有当前锁，无论其类型如何。
outliers
struct Outliers {
total_exec_time: PgInterval,
prop_exec_time: String,
ncalls: String,
sync_io_time: PgInterval,
query: String,
}
outliers() -> Result<Vec<Outliers>, PgExtrasError>
query | exec_time | prop_exec_time | ncalls | sync_io_time
-----------------------------------------+------------------+----------------+-------------+--------------
SELECT * FROM archivable_usage_events.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
COPY public.archivable_usage_events (.. | 50:38:33.198418 | 23.6% | 13 | 13:34:21.00108
COPY public.usage_events (id, reporte.. | 02:32:16.335233 | 1.2% | 13 | 00:34:19.784318
INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
SELECT * FROM usage_events WHERE (alp.. | 01:18:10.754354 | 0.6% | 102,114,301 | 00:00:00
UPDATE usage_events SET reporter_id =.. | 00:52:35.683254 | 0.4% | 23,786,348 | 00:00:00
INSERT INTO usage_events (id, retaine.. | 00:49:24.952561 | 0.4% | 21,988,201 | 00:00:00
(truncated results for brevity)
此命令显示来自 pg_stat_statements 的语句，按执行时间总和排序。这包括语句本身、该语句的总执行时间、该语句占所有语句总执行时间的比例、该语句被调用的次数以及该语句在同步I/O（从文件系统读取/写入）上花费的时间。
通常，高效查询将具有适当的调用与总执行时间的比率，并且尽可能少地花费时间在I/O上。总执行时间长但调用次数少的查询应进行调查以改进其性能。花费大量执行时间在同步I/O上的查询也应进行调查。
calls
struct Calls {
qry: String,
exec_time: PgInterval,
prop_exec_time: String,
ncalls: String,
sync_io_time: PgInterval,
}
calls(limit: Option<String>) -> Result<Vec<Calls>, PgExtrasError>
qry | exec_time | prop_exec_time | ncalls | sync_io_time
-----------------------------------------+------------------+----------------+-------------+--------------
SELECT * FROM usage_events WHERE (alp.. | 01:18:11.073333 | 0.6% | 102,120,780 | 00:00:00
BEGIN | 00:00:51.285988 | 0.0% | 47,288,662 | 00:00:00
COMMIT | 00:00:52.31724 | 0.0% | 47,288,615 | 00:00:00
SELECT * FROM archivable_usage_event.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
UPDATE usage_events SET reporter_id =.. | 00:52:35.986167 | 0.4% | 23,788,388 | 00:00:00
INSERT INTO usage_events (id, retaine.. | 00:49:25.260245 | 0.4% | 21,990,326 | 00:00:00
INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
(truncated results for brevity)
此命令类似于 pg:outliers，但按语句被调用的次数排序。
blocking
struct Blocking {
blocked_pid: i32,
blocking_statement: String,
blocking_duration: PgInterval,
blocking_pid: i32,
blocked_statement: String,
blocked_duration: PgInterval,
blocked_sql_app: String,
blocking_sql_app: String,
}
blocking(limit: Option<String>) -> Result<Vec<Blocking>, PgExtrasError>
blocked_pid | blocking_statement | blocking_duration | blocking_pid | blocked_statement | blocked_duration
-------------+--------------------------+-------------------+--------------+------------------------------------------------------------------------------------+------------------
461 | select count(*) from app | 00:00:03.838314 | 15682 | UPDATE "app" SET "updated_at" = '2013-03-04 15:07:04.746688' WHERE "id" = 12823149 | 00:00:03.821826
(1 row)
此命令显示当前持有锁的语句，其他语句正在等待释放。这可以与 pg:locks 结合使用，以确定哪些语句需要终止以解决锁竞争。
total_index_size
struct TotalIndexSize {
size: String,
}
total_index_size() -> Result<Vec<TotalIndexSize>, PgExtrasError>
size
-------
28194 MB
(1 row)
此命令显示数据库中所有索引的总大小，以MB为单位。它是通过将页面数（在 relpages 中报告）乘以页面大小（8192字节）来计算的。
index_size
struct IndexSize {
name: String,
size: String,
schema: String,
}
index_size() -> Result<Vec<IndexSize>, PgExtrasError>
name | size | schema |
---------------------------------------------------------------+-------------------
idx_activity_attemptable_and_type_lesson_enrollment | 5196 MB | public |
index_enrollment_attemptables_by_attempt_and_last_in_group | 4045 MB | public |
index_attempts_on_student_id | 2611 MB | custom |
enrollment_activity_attemptables_pkey | 2513 MB | custom |
index_attempts_on_student_id_final_attemptable_type | 2466 MB | custom |
attempts_pkey | 2466 MB | custom |
index_attempts_on_response_id | 2404 MB | public |
index_attempts_on_enrollment_id | 1957 MB | public |
index_enrollment_attemptables_by_enrollment_activity_id | 1789 MB | public |
enrollment_activities_pkey | 458 MB | public |
(truncated results for brevity)
此命令显示数据库中每个索引的大小，以MB为单位。它是通过将页面数（在 relpages 中报告）乘以页面大小（8192字节）来计算的。
table_size
struct TableSize {
name: String,
size: String,
schema: String,
}
table_size() -> Result<Vec<TableSize>, PgExtrasError>
name | size | schema |
---------------------------------------------------------------+-------------------
learning_coaches | 196 MB | public |
states | 145 MB | public |
grade_levels | 111 MB | custom |
charities_customers | 73 MB | public |
charities | 66 MB | public |
(truncated results for brevity)
此命令显示数据库中每个表和物化视图的大小，以MB为单位。它通过使用系统管理函数 pg_table_size() 来计算，该函数包括主数据分区的尺寸、空闲空间图、可见性图和TOAST数据的大小。
table_indexes_size
TableIndexesSize {
table: String,
index_size: String,
}
table_indexes_size(schema: Option<String>) -> Result<Vec<TableIndexesSize>, PgExtrasError>
table | indexes_size
---------------------------------------------------------------+--------------
learning_coaches | 153 MB
states | 125 MB
charities_customers | 93 MB
charities | 16 MB
grade_levels | 11 MB
(truncated results for brevity)
此命令显示每个表和物化视图的索引总大小，以MB为单位。它通过使用系统管理函数 pg_indexes_size() 来计算。
total_table_size
struct TotalTableSize {
name: String,
size: String,
}
total_table_size() -> Result<Vec<TotalTableSize>, PgExtrasError>
name | size
---------------------------------------------------------------+---------
learning_coaches | 349 MB
states | 270 MB
charities_customers | 166 MB
grade_levels | 122 MB
charities | 82 MB
(truncated results for brevity)
此命令显示数据库中每个表和物化视图的总大小，以MB为单位。它通过使用系统管理函数 pg_total_relation_size() 来计算，该函数包括表大小、总索引大小和TOAST数据。
unused_indexes
struct UnusedIndexes {
table: String,
index: String,
index_size: String,
index_scans: i64,
}
unused_indexes(schema: Option<String>) -> Result<Vec<UnusedIndexes>, PgExtrasError>
table | index | index_size | index_scans
---------------------+--------------------------------------------+------------+-------------
public.grade_levels | index_placement_attempts_on_grade_level_id | 97 MB | 0
public.observations | observations_attrs_grade_resources | 33 MB | 0
public.messages | user_resource_id_idx | 12 MB | 0
(3 rows)
此命令显示小于50次扫描记录的索引，并且大小大于5页，按索引扫描数量排序。此命令通常用于删除未使用的索引，这些索引可能会影响写入性能，以及如果它们占用内存空间，还会影响读取性能。
duplicate_indexes
struct DuplicateIndexes {
size: String,
idx1: String,
idx2: String,
idx3: String,
idx4: String,
}
duplicate_indexes() -> Result<Vec<DuplicateIndexes>, PgExtrasError>
| size | idx1 | idx2 | idx3 | idx4 |
+------------+--------------+----------------+----------+-----------+
| 128 k | users_pkey | index_users_id | | |
此命令显示具有相同列集、相同操作类、表达式和谓词的多个索引，它们是等效的。通常可以安全地删除它们中的一个。
null_indexes
struct NullIndexes {
oid: String,
index: String,
index_size: String,
unique: bool,
indexed_column: String,
table: String,
null_frac: String,
expected_saving: String,
schema: String,
}
null_indexes(min_relation_size_mb: Option<String>) -> Result<Vec<NullIndexes>, PgExtrasError>
oid | index | index_size | unique | indexed_column | null_frac | expected_saving
---------+--------------------+------------+--------+----------------+-----------+-----------------
183764 | users_reset_token | 1445 MB | t | reset_token | 97.00% | 1401 MB
88732 | plan_cancelled_at | 539 MB | f | cancelled_at | 8.30% | 44 MB
9827345 | users_email | 18 MB | t | email | 28.67% | 5160 kB
此命令显示包含 NULL 值的索引。高比例的 NULL 值意味着在搜索时不包括它们的部分索引将是有益的。
seq_scans
struct SeqScans {
name: String,
count: i64,
}
seq_scans(schema: Option<String>) -> Result<Vec<SeqScans>, PgExtrasError>
name | count
-----------------------------------+----------
learning_coaches | 44820063
states | 36794975
grade_levels | 13972293
charities_customers | 8615277
charities | 4316276
messages | 3922247
contests_customers | 2915972
classroom_goals | 2142014
(truncated results for brevity)
此命令显示对所有表记录的顺序扫描次数，按顺序扫描次数降序排列。具有非常高的顺序扫描数的表可能未充分索引，调查从这些表读取的查询可能是有价值的。
long_running_queries
struct LongRunningQueries {
pid: String,
duration: String,
query: String,
}
long_running_queries() -> Result<Vec<LongRunningQueries>, PgExtrasError>
pid | duration | query
-------+-----------------+---------------------------------------------------------------------------------------
19578 | 02:29:11.200129 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1450645 LIMIT 1
19465 | 02:26:05.542653 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1889881 LIMIT 1
19632 | 02:24:46.962818 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1581884 LIMIT 1
(truncated results for brevity)
此命令显示当前正在运行的查询，它们已经运行了超过5分钟，按持续时间降序排列。非常长的查询可能成为多个问题的来源，例如阻止DDL语句完成或vacuum无法更新 relfrozenxid。
records_rank
struct RecordsRank {
name: String,
esiimated_count: i64,
}
records_rank(schema: Option<String>) -> Result<Vec<RecordsRank>, PgExtrasError>
name | estimated_count
-----------------------------------+-----------------
tastypie_apiaccess | 568891
notifications_event | 381227
core_todo | 178614
core_comment | 123969
notifications_notification | 102101
django_session | 68078
(truncated results for brevity)
此命令显示每个表的估计行数，按估计数降序排列。估计数是从 n_live_tup 中得出的，它由vacuum操作更新。由于 n_live_tup 的填充方式，稀疏页与密集页可能会导致估计值与实际行数有显著差异。
bloat
struct Bloat {
typefield: String,
schemaname: String,
object_name: String,
bloat: Decimal,
waste: String,
}
bloat() -> Result<Vec<Bloat>, PgExtrasError>
type | schemaname | object_name | bloat | waste
-------+------------+-------------------------------+-------+----------
table | public | bloated_table | 1.1 | 98 MB
table | public | other_bloated_table | 1.1 | 58 MB
index | public | bloated_table::bloated_index | 3.7 | 34 MB
table | public | clean_table | 0.2 | 3808 kB
table | public | other_clean_table | 0.3 | 1576 kB
(truncated results for brevity)
此命令显示表“膨胀”的估计值——分配给关系的空间充满了尚未回收的死元组。具有高膨胀比（通常是10或更高）的表应进行调查，以查看是否真空操作足够激进，并且可能是高表转换的迹象。
vacuum_stats
struct VacuumStats {
schema: String,
table: String,
last_vacuum: String,
last_autovacuum: String,
rowcount: String,
dead_rowcount: String,
autovacuum_threshold: String,
expect_autovacuum: String,
}
vacuum_stats() -> Result<Vec<VacuumStats>, PgExtrasError>
schema | table | last_vacuum | last_autovacuum | rowcount | dead_rowcount | autovacuum_threshold | expect_autovacuum
--------+-----------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
public | log_table | | 2013-04-26 17:37 | 18,030 | 0 | 3,656 |
public | data_table | | 2013-04-26 13:09 | 79 | 28 | 66 |
public | other_table | | 2013-04-26 11:41 | 41 | 47 | 58 |
public | queue_table | | 2013-04-26 17:39 | 12 | 8,228 | 52 | yes
public | picnic_table | | | 13 | 0 | 53 |
(truncated results for brevity)
此命令显示每个表的真空操作相关统计数据，包括死行估计、上次自动真空和当前的自动真空阈值。此命令在确定当前真空阈值是否需要调整以及确定表上次何时进行真空时非常有用。
buffercache_stats
struct BuffercacheStats {
relname: String,
buffered: String,
buffer_percent: Decimal,
percent_of_relation: Decimal,
}
buffercache_stats() -> Result<Vec<BuffercacheStats>, PgExtrasError>
此命令显示按百分比占用排序的数据库共享缓冲区中缓存的关联。它还显示整个关联中有多少被缓存的。
buffercache_usage
struct BuffercacheUsage {
relname: String,
buffers: i64,
}
buffercache_usage() -> Result<Vec<BuffercacheUsage>, PgExtrasError>
此命令计算当前缓存的哪些表的块数量。
extensions
struct Extensions {
name: String,
default_version: String,
installed_version: String,
comment: String,
}
extensions() -> Result<Vec<Extensions>, PgExtrasError>
此命令列出所有已安装和可用的 PostgreSQL 扩展。
connections
struct Connections {
username: String,
pid: i32,
client_addr: String,
}
connections() -> Result<Vec<Connections>, PgExtrasError>
+----------------------------------------------------------------+
| Returns the list of all active database connections |
+------------------+--------------------------+------------------+
| username | pid | client_address | application_name |
+------------------+--------------------------+------------------+
| postgres | 15962 | 172.31.69.166/32 | sidekiq |
| postgres | 16810 | 172.31.69.166/32 | bin/rails |
+------------------+--------------------------+------------------+
此命令返回所有活动数据库连接的列表。
mandelbrot
struct Mandelbrot {
array_to_string: String,
}
mandelbrot() -> Result<Vec<Mandelbrot>, PgExtrasError>
此命令通过 SQL 计算输出曼德布罗特集。
测试
cp docker-compose.yml.sample docker-compose.yml
docker compose up -d
cargo test -- --nocapture
查询来源
依赖关系
~21–33MB
~589K SLoC