bpmn_process table is huge

[Russ]

Guys, why it's not cleaning up itself?

[yuri]

https://docs.espocrm.com/administration/bpm-tips/#cleaning-up-ended-processes

It's been covered couple of times on the forum.

[Russ]

https://docs.espocrm.com/administrat...nded-processes

It's been covered couple of times on the forum.

Thank you

[Russ]

yuri, please help, how can I remove all flowchart processes after 30 days?

If I setup "deleted = 1;" it will delete immediately, if I add a timer..I feel that's wrong, what's the best way? Maybe some config variable that can be setup?

https://docs.espocrm.com/administration/bpm-tips/#displaying-processes-on-record-detail-view

Sorry, thanks

[yuri]

You can utilize a scheduled workflow that deletes ended processes that ended >30 days ago.

[macistda]

Optimizations for MySQL / MariaDB:
https://forum.espocrm.com/forum/gene...abase-filesize

Usefule Scripts (DB), based on Linux-VM and MariaDB:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "crm"
ORDER BY (data_length + index_length) DESC limit 10;

where "table_schema" = Name of the DB.

Personally I use the following for tables not cleaning themself:

delete from bpmn_flow_node where created_at < now() - interval 30 DAY;
delete from bpmn_process where created_at < now() - interval 30 DAY;
delete from scheduled_job_log_record where created_at < now() - interval 30 DAY;
delete from job where  created_at < now() - interval 30 DAY;​

PHP (Espo Command) to defrag index:

php /var/www/crm/rebuild.php​

Optimize DB (SSH):

mysqlcheck -u root -p --optimize crm​

where crm is the name of the DB.

Sources:

• EspoCRM own experiance
• https://vitux.com/tune-and-optimize-mysql-mariadb/
• http://woshub.com/compress-defrag-op...iadb-mysql-db/
• https://docs.espocrm.com/development/db-indexes/

[Russ]

Optimizations for MySQL / MariaDB:
https://forum.espocrm.com/forum/gene...abase-filesize

Usefule Scripts (DB), based on Linux-VM and MariaDB:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "crm"
ORDER BY (data_length + index_length) DESC limit 10;

where "table_schema" = Name of the DB.

Personally I use the following for tables not cleaning themself:

delete from bpmn_flow_node where created_at < now() - interval 30 DAY;
delete from bpmn_process where created_at < now() - interval 30 DAY;
delete from scheduled_job_log_record where created_at < now() - interval 30 DAY;
delete from job where created_at < now() - interval 30 DAY;​

PHP (Espo Command) to defrag index:

php /var/www/crm/rebuild.php​

Optimize DB (SSH):

mysqlcheck -u root -p --optimize crm​

where crm is the name of the DB.

Sources:

• EspoCRM own experiance
• https://vitux.com/tune-and-optimize-mysql-mariadb/
• http://woshub.com/compress-defrag-op...iadb-mysql-db/
• https://docs.espocrm.com/development/db-indexes/

Thanks