I am with a client, and they are rolling out Operaton. But I am seeing massive issues with the indexes on the database (PostgreSQL). Happy to help optimize, but who is interested to work with me?
This is a fairly large deployment, where the database is running virtualized and not on K8s.
Queries of Operaton are consuming 60+ percent of the entire database load.
The database processes that are running these queries are continously the top queries on the database server, and I am wondering if there options to send the queries to the database and a more subtle formatting.
I guess you have pg_stat_statements running and already identified at least some of the issues.
It would be great to solve these issues together and integrate our findings in the setup scripts.
Let me know where I/we can assist you, I’ll be glad to help!
Yes, I have pg_stat_statements snapshots, and I am happy to share those. I have found/created one index, but it can probably be further improved, and I am happy to also work more on that.
Additionally the formatting can do with some TLC, I guess. I shall share examples as updates on this post?
Hi, perhaps it would make sense if you join us in our slack channel in the channel development → Join Operaton on Slack
Tim recently digged into database performance research. Perhaps he has some ideas to this topic. To integrate improvements into the product, we must ensure they are compatible with all supported database engines and do not unintentionally change any execution semantics.
-- Ultimately, the index that elevated execution from 51.2 ms to 6.5 ms
CREATE INDEX CONCURRENTLY dba_act_idx_hi_var_pi_name_type_text ON act_hi_varinst (proc_inst_id_, name_, var_type_, text_);
-- The original explain plan that lead to the index QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4215.95..4216.01 rows=1 width=1622) (actual time=51.156..51.159 rows=0 loops=1)
Buffers: shared hit=43119
-> Unique (cost=4215.89..4215.95 rows=1 width=1622) (actual time=51.155..51.158 rows=0 loops=1)
Buffers: shared hit=43119
-> Sort (cost=4215.89..4215.89 rows=1 width=1622) (actual time=51.154..51.157 rows=0 loops=1)
Sort Key: self.id_, self.proc_inst_id_, self.business_key_, self.proc_def_key_, self.proc_def_id_, self.start_time_, self.end_time_, self.removal_time_, self.duration_, self.start_user_id_, self.start_act_id_, self.end_act_id_, self.super_process_instance_id_, self.root_proc_inst_id_, self.super_case_instance_id_, self.case_inst_id_, self.delete_reason_, self.tenant_id_, self.state_, self.restarted_proc_inst_id_, def.name_, def.version_, def.deployment_id_
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=43119
-> Nested Loop Left Join (cost=2.59..4215.88 rows=1 width=1622) (actual time=51.111..51.114 rows=0 loops=1)
Filter: (((self.proc_def_key_ IS NOT NULL) AND (a.resource_id_ IS NOT NULL)) OR (self.proc_def_key_ IS NULL))
Rows Removed by Filter: 1
Buffers: shared hit=43119
-> Nested Loop (cost=0.70..4212.04 rows=1 width=1622) (actual time=35.171..51.074 rows=1 loops=1)
Buffers: shared hit=43114
-> Nested Loop (cost=0.29..487.45 rows=3105 width=1622) (actual time=0.038..5.716 rows=10464 loops=1)
Buffers: shared hit=1248
-> Seq Scan on act_re_procdef def (cost=0.00..2.48 rows=11 width=119) (actual time=0.007..0.018 rows=11 loops=1)
Filter: ((key_)::text = 'procesHerziening'::text)
Rows Removed by Filter: 27
Buffers: shared hit=2
-> Index Scan using act_idx_hi_pi_pdefid_end_time on act_hi_procinst self (cost=0.29..40.94 rows=315 width=1559) (actual time=0.007..0.386 rows=951 loops=11)
Index Cond: ((proc_def_id_)::text = (def.id_)::text)
Filter: (tenant_id_ IS NULL)
Buffers: shared hit=1246
-> Index Scan using act_idx_hi_var_pi_name_type on act_hi_varinst vi0 (cost=0.42..1.19 rows=1 width=37) (actual time=0.004..0.004 rows=0 loops=10464)
Index Cond: (((proc_inst_id_)::text = (self.proc_inst_id_)::text) AND ((name_)::text = 'achternaam'::text) AND (var_type_ IS NOT NULL) AND ((var_type_)::text = 'string'::text))
Filter: ((id_ IS NOT NULL) AND (text_ IS NOT NULL) AND ((text_)::text = 'Kaptan'::text))
Rows Removed by Filter: 1
Buffers: shared hit=41866
-> Bitmap Heap Scan on act_ru_authorization a (cost=1.89..3.83 rows=1 width=36) (actual time=0.026..0.027 rows=0 loops=1)
Recheck Cond: (((resource_id_)::text = (self.proc_def_key_)::text) OR ((resource_id_)::text = '*'::text))
Filter: ((type_ < 2) AND (resource_type_ = 1) AND (((user_id_)::text = ANY ('{hubadmin,*}'::text[])) OR ((group_id_)::text = 'operator-admin'::text)) AND ((perms_ & 2147483647) = 2147483647))
Rows Removed by Filter: 27
Heap Blocks: exact=1
Buffers: shared hit=5
-> BitmapOr (cost=1.89..1.89 rows=28 width=0) (actual time=0.011..0.012 rows=0 loops=1)
Buffers: shared hit=4
-> Bitmap Index Scan on act_idx_auth_resource_id (cost=0.00..0.30 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((resource_id_)::text = (self.proc_def_key_)::text)
Buffers: shared hit=2
-> Bitmap Index Scan on act_idx_auth_resource_id (cost=0.00..1.59 rows=27 width=0) (actual time=0.004..0.004 rows=27 loops=1)
Index Cond: ((resource_id_)::text = '*'::text)
Buffers: shared hit=2
Planning:
Buffers: shared hit=25
Planning Time: 1.099 ms
Execution Time: 51.250 ms
-- The query formatted
select distinct RES.*
from (
SELECT SELF.*
, DEF.NAME_
, DEF.VERSION_
, DEF.DEPLOYMENT_ID_
FROM ACT_HI_PROCINST SELF
LEFT JOIN ACT_RE_PROCDEF DEF
ON SELF.PROC_DEF_ID_ = DEF.ID_
left join (
SELECT A.*
FROM ACT_RU_AUTHORIZATION A
WHERE A.TYPE_ < 2
AND (
A.USER_ID_ in ( $1, '*')
OR A.GROUP_ID_ IN ( $2 )
)
AND (
(
A.RESOURCE_TYPE_ = $3
AND A.PERMS_ & $4 = $5
)
)
)
AUTH ON (
AUTH.RESOURCE_ID_ IN (
SELF.PROC_DEF_KEY_
, '*'
)
)
LEFT JOIN ACT_HI_VARINST VI0
ON SELF.PROC_INST_ID_ = VI0.PROC_INST_ID_
WHERE (
1 = 1
and DEF.KEY_ = $6
and (
VI0.ID_ IS NOT NULL
AND VI0.NAME_= $7
and (
(
VI0.VAR_TYPE_ is not null
and VI0.VAR_TYPE_ = $8
and VI0.TEXT_ is not null
and VI0.TEXT_ = $9
)
)
)
)
and (
(
SELF.PROC_DEF_KEY_ is not null
AND AUTH.RESOURCE_ID_ IS NOT NULL
)
or SELF.PROC_DEF_KEY_ is null
)
and (
SELF.TENANT_ID_ is null
)
) RES
order by RES.ID_ asc
LIMIT $10
OFFSET $11
;
