在开发服务器中，由于进程设计总是会得到更新和开发的，所以内存中会有越来越多的XPDL缓存。从长远来看，这可能会在运行时或服务器启动时导致“ java.lang.OutOfMemoryError ”异常。要解决这个问题，我们需要按照以下步骤清理未使用的XPDL。以下示例查询是使用MySQL和MSSQL语法编写的。

Note

1.请关闭您的应用程序服务器。

2.请在进行以下步骤之前先完成数据库备份。


1.检查XPDL的现有数量

在通用的生产服务器上，通常只有少数应用程序版本，每个版本只与1个进程版本绑定。这是因为我们不会在生产服务器上进行任何开发，所以进程版本只会在导入的应用程序中增加。但是在开发服务器中，情况会有所不同。进程版本将上升到几百个进程版本，这也意味着有几百个XPDL数据被缓存在内存中。要检查缓存了多少XPDL，请在数据库服务器中运行以下查询。

MySQL, MSSQL
 select count(*) from SHKXPDLS

2.检查未使用的XPDL的数量

在所有缓存的进程版本XPDL中，有一些进程版本没有被任何进程实例使用。这些未使用的XPDL可以安全地删除。检查未使用的XPDL的数量，我们可以使用下面的查询。然后，我们可以知道在XPDL的总数中有多少是未使用的。

MySQL, MSSQL
select count(*) from SHKXPDLS x
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion 
        from SHKActivities act 
        join SHKProcesses pro on act.Process = pro.oid 
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid 
        group by def.PackageId, def.ProcessDefinitionVersion 
        union 
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);

如果您想列出流程版本，则可以使用以下查询。

MySQL, MSSQL
select * from SHKXPDLS x
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion 
        from SHKActivities act 
        join SHKProcesses pro on act.Process = pro.oid 
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid 
        group by def.PackageId, def.ProcessDefinitionVersion 
        union 
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);

3.删除已完成的流程实例数据以清除缓存中的更多XPDL

如果流程实例数据对您并不重要，或者您的流程实例数据被Process Data Collector  插件捕获了副本，则  可以删除流程实例数据以增加未使用的XPDL的数量。在这种情况下，只保存运行流程实例的流程版本和每个应用程序版本的最新流程版本。

MySQL
SET FOREIGN_KEY_CHECKS=0;
 
delete sp, spr, spd, sac, sad, sa
from SHKProcesses sp
join SHKProcessRequesters spr on spr.Id = sp.ID
left join SHKProcessData spd on spd.Process = sp.oid
left join SHKActivities sac on sac.ProcessId = sp.ID
left join SHKActivityData sad on sad.Activity = sac.oid
left join SHKAssignmentsTable sa on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
SET FOREIGN_KEY_CHECKS=1;
MSSQL
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

delete sa from SHKAssignmentsTable as sa
left join SHKProcesses as sp on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete sad from SHKActivityData as sad
left join SHKActivities as sac on sad.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete sac from SHKActivities as sac
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete spd from SHKProcessData as spd
left join SHKProcesses as sp on spd.Process = sp.oid
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete spr from SHKProcessRequesters as spr
left join SHKProcesses as sp on spr.Id = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete from SHKProcesses where (State = 1000006 or State = 1000008 or State = 1000010); 
 
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

 

如果要清除包括正在运行的流程实例在内的所有流程实例数据，则可以使用以下查询。

MySQL
SET FOREIGN_KEY_CHECKS=0;

delete from SHKAssignmentsTable;
delete from SHKDeadlines;
delete from SHKActivityData;
delete from SHKActivities;
delete from SHKProcessData;
delete from SHKProcessRequesters;
delete from SHKProcesses;
 
SET FOREIGN_KEY_CHECKS=1;
MSSQL
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

delete from SHKAssignmentsTable;
delete from SHKDeadlines;
delete from SHKActivityData;
delete from SHKActivities;
delete from SHKProcessData;
delete from SHKProcessRequesters;
delete from SHKProcesses;

EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

 

4.删除未使用的XDPL

现在，您可以删除未使用的XPDL，并且在下一次服务器启动时它将从您的内存缓存中释放。

MySQL
SET FOREIGN_KEY_CHECKS=0;
delete x, xd from SHKXPDLS x join SHKXPDLData xd on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion 
        from SHKActivities act 
        join SHKProcesses pro on act.Process = pro.oid 
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid 
        group by def.PackageId, def.ProcessDefinitionVersion 
        union 
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);
SET FOREIGN_KEY_CHECKS=1;
MSSQL
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 
delete xd from SHKXPDLData as xd
left join SHKXPDLS as x on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
 select concat(packageId,packageVersion) as id from (
 select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
 from SHKActivities act
 join SHKProcesses pro on act.Process = pro.oid
 left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
 group by def.PackageId, def.ProcessDefinitionVersion
 union
 select packageId, packageVersion from app_package
 ) as used_processes group by packageId, packageVersion
);
 
delete x from SHKXPDLS as x
where concat(x.XPDLId, x.XPDLVersion) not in (
 select concat(packageId,packageVersion) as id from (
 select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
 from SHKActivities act
 join SHKProcesses pro on act.Process = pro.oid
 left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
 group by def.PackageId, def.ProcessDefinitionVersion
 union
 select packageId, packageVersion from app_package
 ) as used_processes group by packageId, packageVersion
);
 
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

 

 

  • No labels