Edit

Measure Business Process disk space occupation in MySQL

• The AwsHitQuestion table stores the output of any bot or manual step.

• The HitSubmissionDataItem table stores the input for any bot or manual steps.

• The AwsHitAssignmentAnswer table stores the output of any bot or manual steps before a business rule processes one record for each output field of the step.

This article will help you to measure the disk space occupation of a particular Business Process (BP) or a list of them. Generally, it can help to understand the impact of a particular Business Process on the MySQL disc space.

In general, the AwsHitQuestion, HitSubmissionDataItem, AwsHitAssignmentAnswer tables are the biggest three tables in the MySQL database occupying about 70% of the disc space. That is why this topic covers only these three tables.

To calculate the disk space occupied by a particular BP, you need its UUID. You can be extract it from the link like {Your_WF_instance_web_address}/workfusion/secure/business-process/edit/{bp_uuid}. Then, you execute the following three queries:

AwsHitQuestion by UUID:

select
    count(record.id) as amount_of_records,
        round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.gold), 0) +
        ifnull(char_length(record.item_id), 0) +
        ifnull(char_length(record.run_id), 0) +
        ifnull(char_length(record.status), 0) +
        ifnull(char_length(record.hit_id), 0) +
        ifnull(char_length(record.assignmentCount), 0) +
        ifnull(char_length(record.jsonAnswers), 0))/1024/1024),2) as total_size_in_mb
from (
    Select
        ah.*
    from AwsHitQuestion ah
        inner join Run r on r.id=ah.run_id
    where r.rootRunUUID = "place-run-uuid-here") record;

HitSubmissionDataItem by UUID:

select
    count(record.id) as amount_of_records,
        round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.item_source), 0) +
        ifnull(char_length(record.jsonDataItemValues), 0) +
        ifnull(char_length(record.lineNo), 0) +
        ifnull(char_length(record.marked_gold), 0) +
        ifnull(char_length(record.originalItemId), 0) +
        ifnull(char_length(record.sandbox), 0) +
        ifnull(char_length(record.rework), 0) +
        ifnull(char_length(record.campaign_id), 0) +
        ifnull(char_length(record.run_id), 0) +
        ifnull(char_length(record.hit_id), 0) +
        ifnull(char_length(record.destinationCrowdId), 0))/1024/1024),2) as total_size_in_mb
from (
    Select
        hs.*
    from HitSubmissionDataItem hs
        inner join Run r on r.id=hs.run_id
    where r.rootRunUUID = "place-run-uuid-here") record;

AwsHitAssignmentAnswer by UUID:

select
    count(record.id) as amount_of_records,
    round((sum(
    ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.formInputName), 0) +
        ifnull(char_length(record.formInputValue), 0) +
        ifnull(char_length(record.status), 0) +
        ifnull(char_length(record.assignment_id), 0) +
        ifnull(char_length(record.correctInputValue), 0) +
        ifnull(char_length(record.correct), 0) +
        ifnull(char_length(record.gold), 0) +
        ifnull(char_length(record.conditional), 0) +
        ifnull(char_length(record.conditionalValueData), 0) +
        ifnull(char_length(record.item_id), 0) +
        ifnull(char_length(record.answerCode), 0) +
        ifnull(char_length(record.suffix), 0))/1024/1024),2) as total_size_in_mb
from (
    select
        AAA.*
    FROM AwsHitAssignmentAnswer AAA
        inner JOIN AwsHitAssignment AA ON AAA.assignment_id = AA.id
        inner JOIN AwsHit AH ON AH.id = AA.hit_id
        inner JOIN (
            Select
                R.id as run_id
            FROM Run R
            where R.rootRunUUID ="place-run-uuid-here") RR on RR.run_id= AH.run_id ) record;

Each of these queries will output the disk space (in MB) occupied by this BP in one of the three tables.

You can calculate similar data for a specific date using a BP title:

AwsHitQuestion by title:

select
    record.rootRunUUID,
    record.bp_title,
    count(record.id) as amount_of_records,
    round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.gold), 0) +
        ifnull(char_length(record.item_id), 0) +
        ifnull(char_length(record.run_id), 0) +
        ifnull(char_length(record.status), 0) +
        ifnull(char_length(record.hit_id), 0) +
        ifnull(char_length(record.assignmentCount), 0) +
        ifnull(char_length(record.jsonAnswers), 0))/1024/1024),2) as total_size_in_mb
from (
    Select
        rec.rootRunUUID,rec.bp_title,ah.*
    from AwsHitQuestion ah
        inner join (
            select r.id as run_id, r.rootRunUUID,C.title as bp_title from Run r
                inner join Campaign C on C.id=r.campaign_id
            where C.title like "%put-bp-title-here%"
                and r.rootRunUUID is not null
                and r.startDate like "YYYY-MM-DD%"
        ) rec on rec.run_id=ah.run_id) record
group by record.rootRunUUID;

HitSubmissionDataItem by title

select
    record.rootRunUUID,
    record.bp_title,
    count(record.id) as amount_of_records,
    round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.item_source), 0) +
        ifnull(char_length(record.jsonDataItemValues), 0) +
        ifnull(char_length(record.lineNo), 0) +
        ifnull(char_length(record.marked_gold), 0) +
        ifnull(char_length(record.originalItemId), 0) +
        ifnull(char_length(record.sandbox), 0) +
        ifnull(char_length(record.rework), 0) +
        ifnull(char_length(record.campaign_id), 0) +
        ifnull(char_length(record.run_id), 0) +
        ifnull(char_length(record.hit_id), 0) +
        ifnull(char_length(record.destinationCrowdId), 0))/1024/1024),2) as row_size_in_mb
from (
    Select
        rec.rootRunUUID,rec.bp_title,hs.*
    from HitSubmissionDataItem hs
        inner join(
            select r.id as run_id, r.rootRunUUID,C.title as bp_title from Run r
                inner join Campaign C on C.id=r.campaign_id
            where C.title like "%put-BP-title-here%"
                and r.rootRunUUID is not null
                and r.startDate like "YYYY-MM-DD%"
        )  rec on rec.run_id=hs.run_id) record
group by record.rootRunUUID;

AwsHitAssignmentAnswer by title

select
    record.rootRunUUID,record.bp_title,
    count(record.id) as amount_of_records,
    round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.formInputName), 0) +
        ifnull(char_length(record.formInputValue), 0) +
        ifnull(char_length(record.status), 0) +
        ifnull(char_length(record.assignment_id), 0) +
        ifnull(char_length(record.correctInputValue), 0) +
        ifnull(char_length(record.correct), 0) +
        ifnull(char_length(record.gold), 0) +
        ifnull(char_length(record.conditional), 0) +
        ifnull(char_length(record.conditionalValueData), 0) +
        ifnull(char_length(record.item_id), 0) +
        ifnull(char_length(record.answerCode), 0) +
        ifnull(char_length(record.suffix), 0))/1024/1024),2) as total_size_in_mb
from (
    select 
        AAA.* ,rec.rootRunUUID, rec.bp_title
    FROM AwsHitAssignmentAnswer AAA
        inner JOIN AwsHitAssignment AA ON AAA.assignment_id = AA.id
        inner JOIN AwsHit AH ON AH.id = AA.hit_id
        inner JOIN (
            select r.id as run_id, r.rootRunUUID, C.title as bp_title from Run r
                inner join Campaign C on C.id=r.campaign_id
            where C.title like "%put-BP-title-here%"
                and r.rootRunUUID is not null
                and r.startDate like "YYYY-MM-DD%"     
        ) rec on rec.run_id= AH.run_id ) record
group by record.rootRunUUID;

These queries will return a list of BPs for the exact date with the calculated disk space occupation in each of the three tables. The queries result also has the rootRunUUID field that you can use to build a link to the exact BP on the WF instance in the following format: {Your_WF_instance_web_address}/workfusion/secure/business-process/edit/{rootRunUUID}.

The following queries return a list of all BPs for the exact date with the calculated disk space occupation in the AwsHitAssignmentAnswer, HitSubmissionDataItem, AwsHitQuestion ordered by the maximum occupied space.

AwsHitAssignmentAnswer by title:

select record.bp_title,
    count(record.id) as amount_of_records,
    round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.formInputName), 0) +
        ifnull(char_length(record.formInputValue), 0) +
        ifnull(char_length(record.status), 0) +
        ifnull(char_length(record.assignment_id), 0) +
        ifnull(char_length(record.correctInputValue), 0) +
        ifnull(char_length(record.correct), 0) +
        ifnull(char_length(record.gold), 0) +
        ifnull(char_length(record.conditional), 0) +
        ifnull(char_length(record.conditionalValueData), 0) +
        ifnull(char_length(record.item_id), 0) +
        ifnull(char_length(record.answerCode), 0) +
        ifnull(char_length(record.suffix), 0))/1024/1024),2) as total_size_in_mb
from (
    select
        AAA.* ,rec.rootRunUUID, rec.bp_title
    FROM AwsHitAssignmentAnswer AAA
        inner JOIN AwsHitAssignment AA ON AAA.assignment_id = AA.id
        inner JOIN AwsHit AH ON AH.id = AA.hit_id
        inner JOIN (
            select r.id as run_id, r.rootRunUUID, C.title as bp_title from Run r
                inner join Campaign C on C.id=r.campaign_id
            where r.rootRunUUID is not null
            and r.startDate like "YYYY-MM-DD%"    
        ) rec on rec.run_id= AH.run_id ) record
group by record.bp_title
order by total_size_in_mb DESC;
AwsHitQuestion by title
SELECT    record.bp_title,
    count(record.id) as amount_of_records,
    round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.gold), 0) +
        ifnull(char_length(record.item_id), 0) +
        ifnull(char_length(record.run_id), 0) +
        ifnull(char_length(record.status), 0) +
        ifnull(char_length(record.hit_id), 0) +
        ifnull(char_length(record.assignmentCount), 0) +
        ifnull(char_length(record.jsonAnswers), 0))/1024/1024),2) as total_size_in_mb
from (
    Select
        rec.rootRunUUID,rec.bp_title,ah.*
    from AwsHitQuestion ah
        inner join (
            select r.id as run_id, r.rootRunUUID,C.title as bp_title from Run r
                inner join Campaign C on C.id=r.campaign_id
            where r.rootRunUUID is not null
                and r.startDate like "YYYY-MM-DD%"
        ) rec on rec.run_id=ah.run_id) record
group by record.bp_title
order by total_size_in_mb DESC;

HitSubmissionDataItem by title

select
    record.bp_title,
    count(record.id) as amount_of_records,
    round((sum(
        ifnull(char_length(record.id), 0) +
        ifnull(char_length(record.item_source), 0) +
        ifnull(char_length(record.jsonDataItemValues), 0) +
        ifnull(char_length(record.lineNo), 0) +
        ifnull(char_length(record.marked_gold), 0) +
        ifnull(char_length(record.originalItemId), 0) +
        ifnull(char_length(record.sandbox), 0) +
        ifnull(char_length(record.rework), 0) +
        ifnull(char_length(record.campaign_id), 0) +
        ifnull(char_length(record.run_id), 0) +
        ifnull(char_length(record.hit_id), 0) +
        ifnull(char_length(record.destinationCrowdId), 0))/1024/1024),2) as row_size_in_mb
from (
    Select
        rec.rootRunUUID,rec.bp_title,hs.*
    from HitSubmissionDataItem hs
        inner join(
            select r.id as run_id, r.rootRunUUID,C.title as bp_title from Run r
                inner join Campaign C on C.id=r.campaign_id
            where C.title like "%automl%"
                and r.rootRunUUID is not null
                and r.startDate like "YYYY-MM-DD%"
        )  rec on rec.run_id=hs.run_id) record
group by record.bp_title
order by total_size_in_mb DESC;