L3 - Default Deleting a deployment triggers deletion of job log exception stack traces via the statement
delete
from
ACT_GE_BYTEARRAY
WHERE ID_ in (
select
JOB_EXCEPTION_STACK_ID_
from
ACT_HI_JOB_LOG
WHERE JOB_EXCEPTION_STACK_ID_ is not null
and DEPLOYMENT_ID_ = <deployment_id>
)
On MySQL (and probably also MariaDB), this results in a query plan like
+----+--------------------+------------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+------+---------------+------+---------+------+---------+-------------+ | 1 | PRIMARY | ACT_GE_BYTEARRAY | ALL | NULL | NULL | NULL | NULL | 125093 | Using where | | 2 | DEPENDENT SUBQUERY | ACT_HI_JOB_LOG | ALL | NULL | NULL | NULL | NULL | 3767036 | Using where | +----+--------------------+------------------+------+---------------+------+---------+------+---------+-------------+
Note that the subselect is a DEPENDENT SUBQUERY. This means the sub query is correlated to the outer query and evaluted once per distinct value of the outer query (see https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_select_type). In this case, it is executed once for each row in ACT_GE_BYTEARRAY. Runtime depends linearly on the cardinality of ACT_GE_BYTEARRAY.
A better query avoiding the sub query would be
delete b from ACT_GE_BYTEARRAY b inner join ACT_HI_JOB_LOG j on b.ID_ = j.JOB_EXCEPTION_STACK_ID_ and j.DEPLOYMENT_ID_ = <id_of_deployment_to_delete>;
Also check if other such queries are executed during deployment deletion.