CREATE OR REPLACE FUNCTION table_build_monitor(
IN table_or_schema_list TEXT[] DEFAULT NULL
, IN sample_period INT DEFAULT 10
)
RETURNS TABLE (
table_name TEXT
, table_size TEXT
, index_size TEXT
)
AS
$$
DECLARE
table_list TEXT[];
schema_list TEXT[];
BEGIN
DROP TABLE IF EXISTS table_sizes_loop;
CREATE TEMP TABLE table_sizes_loop (
table_name_loop TEXT
, table_size_bytes BIGINT
, indexes_size_bytes BIGINT
)
;
select
array_remove(array_agg(case when split_part(poo, '.',2) = '*' then split_part(poo, '.',1) else NULL end), NULL::TEXT)
, array_remove(array_agg(case when split_part(poo, '.',2) = '*' then NULL else poo end), NULL::TEXT)
FROM unnest(array[table_or_schema_list]) poo
INTO schema_list, table_list
;
INSERT INTO table_sizes_loop
SELECT
pg_tables.schemaname||'.'|| pg_tables.tablename as table_name
, pg_relation_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS table_size_bytes
, pg_indexes_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS indexes_size_bytes
FROM pg_tables
WHERE
pg_tables.schemaname = ANY(schema_list)
OR (pg_tables.schemaname||'.'|| pg_tables.tablename)::text = ANY(table_list)
UNION
SELECT
'temp_files'
, temp_bytes
, NULL
FROM pg_stat_database
WHERE
datname = current_database()
;
PERFORM pg_sleep(sample_period);
RETURN QUERY
with
base AS
(
SELECT
pg_tables.schemaname||'.'|| pg_tables.tablename as table_name_loop
, pg_relation_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS table_size_bytes
, pg_indexes_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS indexes_size_bytes
FROM pg_tables
WHERE
pg_tables.schemaname::text = ANY(schema_list)
OR (pg_tables.schemaname||'.'|| pg_tables.tablename)::text = ANY(table_list)
UNION
SELECT
'temp_files'
, temp_bytes
, NULL
FROM pg_stat_database
WHERE
datname = current_database()
)
SELECT
table_name_loop
, CASE WHEN table_name_loop = 'temp_files' THEN
pg_size_pretty((base.table_size_bytes - tsl.table_size_bytes)/sample_period) || '/s'
ELSE
base.table_size_bytes
|| ' (' || pg_size_pretty((base.table_size_bytes))
|| ') - ' || pg_size_pretty((base.table_size_bytes - tsl.table_size_bytes)/sample_period) || '/s'
END as table_size
, base.table_size_bytes
|| ' (' || pg_size_pretty((base.indexes_size_bytes))
|| ') - ' || pg_size_pretty((base.indexes_size_bytes - tsl.indexes_size_bytes)/sample_period) || '/s'
as table_size
FROM table_sizes_loop tsl
JOIN base USING (table_name_loop)
ORDER BY base.table_size_bytes DESC
;
END
$$
LANGUAGE plpgsql
;
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on test_table (cost=0.00..6901575.15 rows=300238015 width=4)
(1 row)
Time: 0.463 ms
7条答案
按热度按时间wfsdck301#
我在这里找到了一个很好的答案:Tracking progress of an update statement
诀窍是首先创建一个序列(随你喜欢命名):
然后附加到查询的WHERE部分:
现在可以查询进度了:
最后别忘了去掉序列:
请注意,这很可能会使查询运行得更慢,每次检查进度时,它都会额外增加该值。上面的链接建议创建一个临时序列,但PostgreSQL似乎没有让它们在会话中可见。
2nbm6dog2#
我想到了一个可能有用的方法。但是如果你想把它实现到你的代码中,比如Java等等,可能需要进一步的处理。
方法是检查页面内容以跟踪进度。
Postgresql有一个名为pageinspect的扩展,可以检查特定表的页面信息。
详情如下:https://www.postgresql.org/docs/current/pageinspect.html
在这里还要花一些时间来理解PostgreSQL的页面布局
https://www.postgresql.org/docs/current/storage-page-layout.html
请特别注意xmin、xmax和ctid
我假设表的行插入遵循一定的顺序。就像table的钥匙。而且任何长时间的更新都可能会附加新的页面。
我还假设主键id大部分是连续的,几乎没有间隙。既然只是估计,我觉得这个条件是可以的。
但是你不能通过
SELECT relname, relpages FROM pg_class
找到总页数,因为它没有更新。如果页面索引在strage中不存在,你会遇到一个异常(但是你会找到这个页面,即使它在pg_class中没有更新),所以在“page_index”上做一个小的“二进制搜索”来找到你拥有的最大页面。不需要精确。
使用
查找您当前的事务ID。
使用
在我正在研究的样本中,它看起来像这样
SELECT lp,t_xmin,t_xmax,t_ctid,t_bits,t_data FROM heap_page_items(get_raw_page('foo ',3407000));
LP|t_xmin|t_xmax|t_ctid|t比特|测试数据
1|592744|592744|(3407000,1)|110000000111000000000000|\xd11000000000000e4400000000000000540100000611b0000631b0000
二|592744|592744|(3407000,2)|110000000111000000000000|\xd110000000000001044000000000040010000611b0000631b0000
三|592744|592744|(3407000,3)|110000000111000000000000|\xd110000000000001144000000000007c010000611b0000631b0000
t_data是数据。lp是来自项目列表的元组索引。t_xmin和t_xmax是事务处理ID。t_ctid是指向元组本身内元组的点。如果元组中有空值,则t_bits是NULL位图。
注意Endian-ness和NULL位图。在我的例子中,它是big-endian(LSB优先)。
在我的示例中,第一行是有效的。第一个BIGINT(8字节16十六进制数)是我正在寻找的排序id。第一行的数据是
\xd110000000000000
转换为0x 101 d(检查字节序)--〉4305
最大的id是18209,最小的id是2857。我把工作分成八部分
(18209 - 2857)/ 8 = 1919
这是我运行的第一部分。所以
2857 + 1919 = 4776
这意味着我的子作业从2857 id开始,当前为4305。如果它达到4776,这个线程就完成了!
这是
(4305- 2857)/ 1919 = 75。完成5%
限制
这将不适用于哈希值更新。在我的例子中,id碰巧按顺序排列为pkey。并且计划器触发顺序读取。如果计划器正在进行某种btree索引扫描以进行更新,这也应该起作用。
如果您对按索引顺序对物理行进行排序感兴趣,请查看CLUSTER。
同样,这种方法并不精确。假设上面强调的。如果在程序中使用,则应稀疏使用,以防止磁盘I/O的额外开销
vjrehmav3#
我不确定这是否是人们正在寻找的确切答案,但我已经做了一个简单的函数,通过测量一段时间内的页面大小来报告表插入的当前状态。这不是一个直接的窗口,但它是一个很好的近似什么/是否有什么事情正在发生。这也是一个坚实的衡量底线(一个表被“填满”的速度)。
该函数返回一个表名列表,其中包含表及其所有关联索引的当前大小(以字节和人类可读单位为单位)和增长率。
**奖励:它还包括临时文件活动
我特别使用它来查看加载表的进度以及加载表的速度,这对于估计需要多长时间很有帮助(尽管对于大负载,线性度越来越低)。
下面是一个可移植的函数:
要查看它,请使用如下所示的select语句,传递一个模式限定的表列表或类似“schema”的内容。* ”表示整个模式-以及可选的采样周期(默认值为10 s)。
6kkfgxo04#
不可以。没有办法跟踪查询的“实时”进度。理论上,系统可以将顶层进度与查询计划进行比较,并发出某种百分比读数。在实践中,我怀疑它是否非常准确,我怀疑性能影响是否值得。
kt06eoxx5#
如果你只是执行INSERT,这也是一种非常快速和肮脏的跟踪进度的方法,类似于上面的建议:
然后在另一个会话中,只需使用
注意:请确保您正在对上述查询的结果进行分页,否则您将得到一个潜在的大响应。
uklbhaso6#
对于一个
INSERT
,你知道你插入的总行数,你可以使用查询计划来了解进度:这将返回查询计划,其中包括查询返回的估计行数。如果它是一个空表,这将对应于已经插入的行。对于一个非空的表,你必须知道在插入之前已经存在的行数。
ijxebb2r7#
您可以向表中添加一个
update_time
列,保存上次更新的值。如果你知道哪些记录应该受到影响,那么你也可以将它们的update_time
设置为当前时间,当你检查进度并知道受影响的行数时,你可以选择受影响的记录数,其中update_time
比你开始更新的时间新。具有“new”的受影响行数update_time
/要更新的记录数 * 100表示进度百分比。