Database performance issue with MySQL
We are seeing slow query times in our MySQL database backend for Bonita. There is a query that selects data from the arch_flownode_instance table. This table contains about 500,000 records and the query is taking over 2 minutes to execute.
Bonita version: 6.3.7 Community
Bonita & MySQL deployed on Windows Server
The customer is seeing approximately 1,500 - 2,000 cases per day.
We temporarily resolved the problem by dumping and recreating the database. Can anyone tell me what this table is used for, and is there any harm in periodically truncating the data (e.g. nightly) to keep it from accumulating and causing future performance issues?
Here is the query that is being reported in the MySQL slow query log file:
select saactivity0_.tenantid as tenantid1_6_, saactivity0_.id as id2_6_, saactivity0_.flownodeDefinitionId as flownode4_6_, saactivity0_.kind as kind3_6_, saactivity0_.rootContainerId as rootCont5_6_, saactivity0_.parentContainerId as parentCo6_6_, saactivity0_.name as name7_6_, saactivity0_.displayName as displayN8_6_, saactivity0_.displayDescription as displayD9_6_, saactivity0_.stateId as stateId10_6_, saactivity0_.stateName as stateNa11_6_, saactivity0_.terminal as termina12_6_, saactivity0_.stable as stable13_6_, saactivity0_.reachedStateDate as reached14_6_, saactivity0_.lastUpdateDate as lastUpd15_6_, saactivity0_.expectedEndDate as expecte16_6_, saactivity0_.claimedDate as claimed17_6_, saactivity0_.archiveDate as archive18_6_, saactivity0_.sourceObjectId as sourceO19_6_, saactivity0_.aborting as abortin20_6_, saactivity0_.logicalGroup1 as logical21_6_, saactivity0_.logicalGroup2 as logical22_6_, saactivity0_.logicalGroup3 as logical23_6_, saactivity0_.logicalGroup4 as logical24_6_, saactivity0_.description as descrip25_6_, saactivity0_.executedBy as execute26_6_, saactivity0_.executedBySubstitute as execute27_6_, saactivity0_.loop_counter as loop_co28_6_, saactivity0_.loop_max as loop_ma29_6_, saactivity0_.sequential as sequent30_6_, saactivity0_.loopDataInputRef as loopDat31_6_, saactivity0_.loopDataOutputRef as loopDat32_6_, saactivity0_.dataInputItemRef as dataInp33_6_, saactivity0_.dataOutputItemRef as dataOut34_6_, saactivity0_.loopCardinality as loopCar35_6_, saactivity0_.nbActiveInst as nbActiv36_6_, saactivity0_.nbCompletedInst as nbCompl37_6_, saactivity0_.nbTerminatedInst as nbTermi38_6_, saactivity0_.actorId as actorId39_6_, saactivity0_.assigneeId as assigne40_6_, saactivity0_.priority as priorit41_6_, saactivity0_.triggeredByEvent as trigger44_6_ from arch_flownode_instance saactivity0_ where 1 = saactivity0_.tenantid and saactivity0_.kind in ('user', 'manual', 'loop', 'multi', 'auto', 'receive', 'send', 'call', 'subProc') and saactivity0_.archiveDate=(select max(saactivity1_.archiveDate) from arch_flownode_instance saactivity1_ where 1 = saactivity1_.tenantid and saactivity1_.kind in ('user', 'manual', 'loop', 'multi', 'auto', 'receive', 'send', 'call', 'subProc') and saactivity1_.sourceObjectId=saactivity0_.sourceObjectId and saactivity1_.sourceObjectId=550169) limit 1;
Thanks in advance,