When using the API (rest), to create an Opportunity via the set_entry call, the act of creating the opportunity can take anything from 2 seconds up to 32 seconds.
SuiteCRM is v 7.12.12, with PHP 7.3.3 running on an EC2 Ubuntu instance in AWS (t3.medium), the server is nowhere near loaded. The database is in RDS, an Aurora-mySQL-5.7 cluster with 2 instances, neither appear overloaded (db.t3.medium)
Weirdly, it appears that it is only the creation of the Opportunity has this delay, as part of our process other records are created in the database, and they all happen very fast.
The set_entry call has been tested with both file_get_contents and a cURL call, both with the same issue.
What logs should I be looking in to try and find the reason for the delay?
The specific action in the code below is the line with “file_get_contents” - this line can take up to 32 seconds to complete, but can also happen as fast as 2 seconds
// HTTP request data
$options = [
'http' => [
'header' => "Content-type: application/x-www-form-urlencoded\r\n",
'method' => 'POST',
'content' => http_build_query($data),
]
];
// Try connexion to API endpoint and get result from it
$context = stream_context_create($options);
**$response = file_get_contents($url, false, $context);**
Thanks, I’ve no idea how to identify the bottleneck, it is not consistently slow, which is throwing me off!
After some digging into log files, I think I have identified the culprit:
SELECT aow_processed.* FROM aow_processed
WHERE aow_workflow_id = 'cb1256bc-81cf-0029-f3a1-5a54b52190ce'
AND parent_id = '252a0a6e-5d95-a83b-d1fa-66bb9a865b78'
AND deleted=0 LIMIT 0,1
Tue Aug 13 18:50:31 2024 [1630750][3b05c70a-7ea0-25ab-7ab4-6033a046cc02][INFO] Query Execution Time:26.631860017776
That line can take up to 30 seconds to run at certain times (I’ve tried it myself in an SQL console and it runs usually in .0x of a sec so not too bad).
I’ll disable that workflow (its not that critical but it is related to a New Opportunity being created) and see if it eliminates the issue and then maybe come up with another way of executing the process that is more efficient
(But I’m still not sure why that query sometimes runs in a fraction of a second)
Is it possible you have an overgrown database? Please run the first query from this post to check:
I bet you will find a huge aow_processed table. Then you will ask, can I delete rows? Search for topics discussing that here in these forums, you will find advice from me and others.
These are processed workflows for keeping log. In the scheduler you can check for Purse DB jobs that is used to clear older log data. Otherwise you can also manually clear older entries that are no longer relevant. Optimisations of the Table index might also help.
Watch out, even older entries in these processed tables can be meaningful. For example, you have a Workflow that sends an email notification to a customer. It runs and is marked as processed. Years later, you delete the entry and your customer gets a new email. Multiply this by thousands of people and you have a tragedy…
So, the decisions to clear those tables need to be made with specific knowledge of the application-level consequences of re-running each Workflow.
A better design of the Workflow processes (with this in mind) can prevent the growth of these tables, by allowing more repeated runs, but controlling things with other conditions to limit what gets executed.
After a trawl through the records, the bulk (90%) of the records related to a workflow that was replaced by code some time ago. That workflow is no longer active.
So I deleted all the records related to that workflow from aow_processed and aow_processed_aow_actions
Will monitor things to ensure that eliminates the delays
