Speed up hivemind replay
Preliminary performance analysis using htop suggests that hivemind replay is likely CPU-bound in the python code nowadays, so the python code needs to be profiled. Early guesses for culprits would be routines doing string processing.
In the meantime, despite the python code being the likely bottleneck, I'm still attaching pghero data for the postgres times of an 81M block replay so we have an easily available record of it (data is from s16, our fastest system):
272 min 7% 7 ms 2,350,502hivemind
SELECT * FROM hivemind_app.enum_operations4hivemind($1, $2)
234 min 6% 6 ms 2,350,502hivemind
SELECT ho.id, ho.block_num, ho.op_type_id, ho.op_type_id >= $3 AS is_virtual, ho.body::VARCHAR
FROM hive.hivemind_app_operations_view ho
WHERE ho.block_num BETWEEN _first_block AND _last_block
AND (ho.op_type_id < $4
OR ho.op_type_id in ($5, $6, $7, $8, $9)
)
ORDER BY ho.block_num, ho.id
119 min 3% 0 ms 64,535,752hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8])::VARCHAR[])
94 min 3% 0 ms 97,530,592hivemind
INSERT INTO hivemind_app.hive_posts as hp
(parent_id, depth, community_id, category_id,
root_id, is_muted, is_valid,
author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend, active, payout_at, cashout_time, counter_deleted, block_num, block_num_created)
SELECT
s.parent_id,
s.depth,
(s.composite).community_id,
s.category_id,
s.root_id,
(s.composite).is_muted,
s.is_valid,
s.author_id,
s.permlink_id,
s.created_at,
s.updated_at,
s.sc_hot,
s.sc_trend,
s.active,
s.payout_at,
s.cashout_time,
s.counter_deleted,
s.block_num,
s.block_num_created
FROM (
SELECT
hivemind_app.process_community_post(_block_num, _community_support_start_block, _parent_permlink, ha.id, $8, php.is_muted, php.community_id) as composite,
php.id AS parent_id, php.depth + $9 AS depth,
COALESCE(php.category_id, (select hcg.id from hivemind_app.hive_category_data hcg where hcg.category = _parent_permlink)) AS category_id,
(CASE(php.root_id)
WHEN $10 THEN php.id
ELSE php.root_id
END) AS root_id,
php.is_valid AS is_valid,
ha.id AS author_id, hpd.id AS permlink_id, _date AS created_at,
_date AS updated_at,
hivemind_app.calculate_time_part_of_hot(_date) AS sc_hot,
hivemind_app.calculate_time_part_of_trending(_date) AS sc_trend,
_date AS active, (_date + INTERVAL $11) AS payout_at, (_date + INTERVAL $12) AS cashout_time,
$13 AS counter_deleted,
_block_num as block_num, _block_num as block_num_created
FROM hivemind_app.hive_accounts ha,
hivemind_app.hive_permlink_data hpd,
hivemind_app.hive_posts php
INNER JOIN hivemind_app.hive_accounts pha ON pha.id = php.author_id
INNER JOIN hivemind_app.hive_permlink_data phpd ON phpd.id = php.permlink_id
WHERE pha.name = _parent_author AND phpd.permlink = _parent_permlink AND
ha.name = _author AND hpd.permlink = _permlink AND php.counter_deleted = $14
) s
ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
--- During post update it is disallowed to change: parent-post, category, community-id
--- then also depth, is_valid and is_muted is impossible to change
--- post edit part
updated_at = _date,
active = _date,
block_num = _block_num
RETURNING (xmax = $15) as is_new_post, hp.id, hp.author_id, hp.permlink_id, (SELECT hcd.category FROM hivemind_app.hive_category_data hcd WHERE hcd.id = hp.category_id) as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
93 min 3% 0 ms 130,197,762hivemind
INSERT INTO hivemind_app.hive_permlink_data
(permlink)
values
(
_permlink
)
ON CONFLICT DO NOTHING
89 min 2% 69 ms 77,414hived_group
SELECT hive.set_irreversible($1)
78 min 2% 0 ms 32,667,170hivemind
INSERT INTO hivemind_app.hive_posts as hp
(parent_id, depth, community_id, category_id,
root_id, is_muted, is_valid,
author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend,
active, payout_at, cashout_time, counter_deleted, block_num, block_num_created,
tags_ids)
SELECT
s.parent_id,
s.depth,
(s.composite).community_id,
s.category_id,
s.root_id,
(s.composite).is_muted,
s.is_valid,
s.author_id,
s.permlink_id,
s.created_at,
s.updated_at,
s.sc_hot,
s.sc_trend,
s.active,
s.payout_at,
s.cashout_time,
s.counter_deleted,
s.block_num,
s.block_num_created,
s.tags_ids
FROM (
SELECT
hivemind_app.process_community_post(_block_num, _community_support_start_block, _parent_permlink, ha.id, $9,$10, $11) as composite,
$12 AS parent_id, $13 AS depth,
(SELECT hcg.id FROM hivemind_app.hive_category_data hcg WHERE hcg.category = _parent_permlink) AS category_id,
$14 as root_id, -- will use id as root one if no parent
$15 AS is_valid,
ha.id AS author_id, hpd.id AS permlink_id, _date AS created_at,
_date AS updated_at,
hivemind_app.calculate_time_part_of_hot(_date) AS sc_hot,
hivemind_app.calculate_time_part_of_trending(_date) AS sc_trend,
_date AS active, (_date + INTERVAL $16) AS payout_at, (_date + INTERVAL $17) AS cashout_time,
$18 AS counter_deleted,
_block_num as block_num, _block_num as block_num_created,
(
SELECT ARRAY_AGG( prepare_tags )
FROM hivemind_app.prepare_tags( ARRAY_APPEND(_metadata_tags, _parent_permlink ) )
) as tags_ids
FROM
hivemind_app.hive_accounts ha,
hivemind_app.hive_permlink_data hpd
WHERE ha.name = _author and hpd.permlink = _permlink
) s
ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
--- During post update it is disallowed to change: parent-post, category, community-id
--- then also depth, is_valid and is_muted is impossible to change
--- post edit part
updated_at = _date,
active = _date,
block_num = _block_num,
tags_ids = EXCLUDED.tags_ids
RETURNING (xmax = $19) as is_new_post, hp.id, hp.author_id, hp.permlink_id, _parent_permlink as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
73 min 2% 0 ms 28,166,528hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, ($8)::VARCHAR[])
62 min 2% 0 ms 16,763,302hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12])::VARCHAR[])
60 min 2% 183 ms 19,625hivemind
SELECT hivemind_app.update_hive_posts_root_id(1, 81522953)
60 min 2% 183 ms 19,625hivemind
UPDATE hivemind_app.hive_posts uhp
SET root_id = id
WHERE uhp.root_id = 0 AND (_first_block_num IS NULL OR (uhp.block_num >= _first_block_num AND uhp.block_num <= _last_block_num))
49 min 1% 0 ms 32,667,170hivemind
INSERT INTO
hivemind_app.hive_tag_data AS htd(tag)
SELECT UNNEST( __tags )
ON CONFLICT("tag") DO UPDATE SET tag=EXCLUDED.tag --trick to always return id
RETURNING htd.id
44 min 1% 34 ms 77,414hived_group
SELECT hive.copy_transactions_to_irreversible( __irreversible_head_block, _block_num )
44 min 1% 34 ms 77,414hived_group
INSERT INTO hive.transactions
SELECT
htr.block_num
, htr.trx_in_block
, htr.trx_hash
, htr.ref_block_num
, htr.ref_block_prefix
, htr.expiration
, htr.signature
FROM
hive.transactions_reversible htr
JOIN ( SELECT
DISTINCT ON ( hbr.num ) hbr.num
, hbr.fork_id
FROM hive.blocks_reversible hbr
WHERE
hbr.num <= _new_irreversible_block
AND hbr.num > _head_block_of_irreversible_blocks
ORDER BY hbr.num ASC, hbr.fork_id DESC
) as num_and_forks ON htr.block_num = num_and_forks.num AND htr.fork_id = num_and_forks.fork_id
30 min 0.8% 91 ms 19,625hivemind
SELECT hivemind_app.update_notification_cache($1, $2, $3)
28 min 0.8% 84 ms 19,625hivemind
INSERT INTO hivemind_app.hive_notification_cache
(block_num, type_id, created_at, src, dst, dst_post_id, post_id, score, payload, community, community_title)
SELECT nv.block_num, nv.type_id, nv.created_at, nv.src, nv.dst, nv.dst_post_id, nv.post_id, nv.score, nv.payload, nv.community, nv.community_title
FROM hivemind_app.hive_raw_notifications_view nv
WHERE nv.block_num > __limit_block AND (_first_block_num IS NULL OR nv.block_num BETWEEN _first_block_num AND _last_block_num)
ORDER BY nv.block_num, nv.type_id, nv.created_at, nv.src, nv.dst, nv.dst_post_id, nv.post_id
23 min 0.6% 69 ms 19,625hivemind
SELECT hivemind_app.update_hive_posts_api_helper(1, 81522953)
23 min 0.6% 69 ms 19,625hivemind
INSERT INTO hivemind_app.hive_posts_api_helper (id, author_s_permlink)
SELECT hp.id, ha.name || '/' || hpd_p.permlink
FROM hivemind_app.live_posts_comments_view hp
JOIN hivemind_app.hive_accounts ha ON (ha.id = hp.author_id)
JOIN hivemind_app.hive_permlink_data hpd_p ON (hpd_p.id = hp.permlink_id)
WHERE hp.block_num BETWEEN _first_block_num AND _last_block_num
ON CONFLICT (id) DO NOTHING
22 min 0.6% 5,241 ms 256hivemind
INSERT INTO hivemind_app.__post_children
(id, child_count)
SELECT
h1.parent_id AS queried_parent,
SUM(COALESCE((SELECT pc.child_count FROM hivemind_app.__post_children pc WHERE pc.id = h1.id),
$3
) + $4
) AS count
FROM hivemind_app.hive_posts h1
WHERE (h1.parent_id != $5 OR __depth = $6) AND h1.counter_deleted = $7 AND h1.id != $8 AND h1.depth = __depth
GROUP BY h1.parent_id
ON CONFLICT ON CONSTRAINT __post_children_pkey DO UPDATE
SET child_count = hivemind_app.__post_children.child_count + excluded.child_count
22 min 0.6% 0 ms 11,118,867hived_group
SELECT $3 FROM ONLY "hive"."operations_reversible" x WHERE "id" OPERATOR(pg_catalog.=) $1 AND "fork_id" OPERATOR(pg_catalog.=) $2 FOR KEY SHARE OF x
21 min 0.6% 17 ms 77,414hived_group
SELECT hive.remove_obsolete_reversible_data( _block_num )
21 min 0.6% 16 ms 77,414hived_group
SELECT hive.copy_account_operations_to_irreversible( __irreversible_head_block, _block_num )
21 min 0.6% 16 ms 77,414hived_group
INSERT INTO hive.account_operations
SELECT
haor.block_num
, haor.account_id
, haor.account_op_seq_no
, haor.operation_id
, haor.op_type_id
FROM
hive.account_operations_reversible haor
JOIN (
SELECT
DISTINCT ON ( hbr.num ) hbr.num
, hbr.fork_id
FROM hive.blocks_reversible hbr
WHERE
hbr.num <= _new_irreversible_block
AND hbr.num > _head_block_of_irreversible_blocks
ORDER BY hbr.num ASC, hbr.fork_id DESC
) as num_and_forks ON haor.fork_id = num_and_forks.fork_id AND haor.block_num = num_and_forks.num
15 min 0.4% 0 ms 4,338,941hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11])::VARCHAR[])
14 min 0.4% 11 ms 77,414hived_group
DELETE FROM hive.operations_reversible hor
WHERE hor.block_num <= _new_irreversible_block
13 min 0.4% 0 ms 3,007,711hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14,$15,$16,$17])::VARCHAR[])
13 min 0.4% 0 ms 4,598,467hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9])::VARCHAR[])
13 min 0.3% 10 ms 77,394hived_group
INSERT INTO hive.account_operations_reversible VALUES( ( unnest( _account_operations ) ).*, __fork_id )
12 min 0.3% 0 ms 21,751,239hivemind
UPDATE
hivemind_app.hive_posts hp
SET
max_accepted_payout = $1,
percent_hbd = $2,
allow_votes = $3,
allow_curation_rewards = $4,
beneficiaries = $5
WHERE
hp.author_id = (SELECT id FROM hivemind_app.hive_accounts WHERE name = $6) AND
hp.permlink_id = (SELECT id FROM hivemind_app.hive_permlink_data WHERE permlink = $7)
10 min 0.3% 30 ms 19,624hivemind
SELECT hivemind_app.update_posts_rshares(81522953, 81580434)
9 min 0.2% 0 ms 2,808,779hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10])::VARCHAR[])
7 min 0.2% 0 ms 1,447,096hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14])::VARCHAR[])
6 min 0.2% 0 ms 1,278,484hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14,$15])::VARCHAR[])
6 min 0.2% 0 ms 1,146,373hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13])::VARCHAR[])
5 min 0.1% 0 ms 1,095,534hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14,$15,$16])::VARCHAR[])
5 min 0.1% 0 ms 6,408,849hived_group
SELECT $2 FROM ONLY "hive"."accounts" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
4 min 0.1% 0 ms 12,758,752hivemind
UPDATE hivemind_app.hive_accounts SET lastread_at = $1 WHERE name = $2
4 min 0.1% 0 ms 2,350,502hivemind
SELECT * FROM hivemind_app.enum_blocks4hivemind($1, $2)
4 min < 0.1% 0 ms 4,710,018hived_group
SELECT $3 FROM ONLY "hive"."account_operations_reversible" x WHERE $1 OPERATOR(pg_catalog.=) "operation_id" AND $2 OPERATOR(pg_catalog.=) "fork_id" FOR KEY SHARE OF x
4 min < 0.1% 3 ms 77,414hived_group
DELETE FROM hive.transactions_reversible htr
WHERE htr.block_num <= _new_irreversible_block
4 min < 0.1% 3 ms 77,414hived_group
DELETE FROM hive.account_operations_reversible har
USING hive.operations_reversible hor
WHERE
har.operation_id = hor.id
AND har.fork_id = hor.fork_id
AND hor.block_num <= _new_irreversible_block
3 min < 0.1% 0 ms 32,667,170hivemind
INSERT INTO hivemind_app.hive_category_data
(category)
VALUES (_parent_permlink)
ON CONFLICT (category) DO NOTHING
3 min < 0.1% 10 ms 19,625hivemind
SELECT hivemind_app.update_follow_count($1, $2)