Speicherplatz steigt dramatisch an

[ThePo8]

First of all, thanks to all of you who supported me on this issue.
@crmspace @DJuser @larsschaps

I found a temporary solution to reduce the size of data w/o destroying my DB. I guess I have to repeat this once a month as long the the long term solutioin (see below) is not setup.

The following sql was copied from the web and modified a little bit, so it suites for me.
For everybody who faces the same issue, pls be aware that dropping tables can destroy your DB if you not exactly know what you are doing!

Situation before:

then doing sql (line by line). you can adjust the interval for your needs.

CREATE TABLE default_grgq1.aow_processed_new AS SELECT FROM default_grgq1.aow_processed WHERE date_entered >=curdate()-interval 1 day;

ALTER TABLE default_grgq1.aow_processed RENAME default_grgq1.aow_processed_old;

ALTER TABLE default_grgq1.aow_processed_new RENAME default_grgq1.aow_processed;

DROP TABLE default_grgq1.aow_processed_old;

CREATE TABLE default_grgq1.aow_processed_aow_actions_new AS SELECT FROM default_grgq1.aow_processed_aow_actions WHERE date_modified >=curdate()-interval 1 day;

ALTER TABLE default_grgq1.aow_processed_aow_actions RENAME default_grgq1.aow_processed_aow_actions_old;

ALTER TABLE default_grgq1.aow_processed_aow_actions_new RENAME default_grgq1.aow_processed_aow_actions;

DROP TABLE default_grgq1.aow_processed_aow_actions_old;

Situation after:

Finaly the long term solution for me would be to get rid off the WFs (or change the frequency) and use logic hooks if possible.

[pgr]

Have a look at this

Size of Tables and Indexes in Database

Do your Worfklows require the “Repeated runs” unchecked? That’s what is making them store all those rows. And if they do require it, when you move the rows elsewhere you’re affecting that requirement.

Often you can make them allow “Repeated runs” and then create some limit to execution through conditions (check some field inside the record, for example). That is the real long-term solution.

[DJuser]

@ThePo8

Finaly the long term solution for me would be to get rid off the WFs

pgr is right - your Workflow is doing something WRONG.

Solve that, and your logs will be fine.

At least, you wrote that you Opp’s are changing not very much:

an opp will maybe changed once a week.
the field “Ort” will be copied every time on save.

because, you team are not saving that many Opps per-day? Dozens? Hundreds?

You wrote:

ich habe eine Vermutung, welcher WF hier der Übeltäter ist.
Im Modul Opp habe ich ein Feld “Ort” angelegt und lasse mir per WF beim speichern der Opp immer den Wert aus “account.billing.city” übertragen.

why do you think that specific WF is the problem?

Let’s get some more evidence
BEFORE your clean-up script above is run - run these SQL queries:

A) The total quantity of records are being added , per day, to the table

• select count(*) from aow_processed where date_modified > ‘2022-05-22’; use yesterdays date - not 22 May when you run this

B) How many from that ONE workflow:
select count(*) from aow_processed where date_modified > ‘2022-05-22’ AND aow_workflow_id=‘put-the-workflow-id-here’;

If the % is very high from just that 1 WF:
Next thing to check: Look at the time-of-day

select date_modified, aow_workflow_id, parent_id from aow_processed where date_modified > ‘2022-05-22’ AND aow_workflow_id=‘put-the-workflow-id-here’ order by date_modified;

If you see

• MANY entries when your office is empty

OR

• hundreds or entries per minute:

then something else is processing and saving many opps -and each save triggers the Workflow above.

Maybe it is a DIFFERENT workflow, that you run on the Opps, every night?
… - or a script or a logic-hook or code: anything that is editing and saving MANY opportunities very quickly, within SuiteCRM code. (anything that is doing a raw SQL query against the database will NOT cause this, because that will NOT cause the workflow to be triggered)

Without more evidence, it’s hard to advice.

But the root cause here is NOT that Workflows in general cause big databases.
It is something else - 99% sure.
Find that, and the SQL table creating/dropping can be errrrr dropped!