Slow queries always time out after ~2 minutes

Roquefort · 26 September 2014 19:54

Hey guys,

I’ve been fighting this problem for months now. The issue is that slow queries (like “Geocode Addresses”, “Mass Update”, etc.) will always time out after about 2 minutes. I am presented with an “Error 500” message, and rarely do even 100 records update properly.

Unfortunately, verbose logging does not give me anything useful. Neither does slow query logging. I just get your standard [debug] and [info] records.

I am running on IIS 7.5, with a MySQL database. I have tried increasing PHP’s “max_execution_time”, as well as various of the “script_limit” options in SuiteCRM’s config.php.

Any other ideas about what I can try? I’m at a total loss at this point, and it’s becoming a problem that Mass Updates almost never finish completely.

Thank you in advance for any help.

Roquefort

Will · 29 September 2014 08:03

Hi Roquefort,

What is your PHP/MySQL/server set up? Is this a hosted environment?

Thanks,

Will.

Roquefort · 29 September 2014 15:45

No sir, this is a self-hosted Windows environment:

Database
Windows Server 2012 R2
MySQL 5.6
Virtualized (Hyper-V)
8 CPU cores, 8GB memory
4 NIC’s bonded on the hypervisor

Web Server
Windows Server 2012 R2
IIS 7.5, SSL-required
PHP 5.5.7 (I have tested ‘supported’ versions and there is no perceivable difference in speed)
Virtualized (Hyper-V)
Wincache-enabled (after this problem already existed)
8 CPU cores, 8GB memory
4 NIC’s bonded on the hypervisor

All of this said, I will note that SuiteCRM does in most situations seem quick and reliable. It is only these long queries that seem to get hung up.

For example, last week I was trying to change the “Contact Type” of several thousand Contacts records. When I would try to do this with Mass Update, it was failing out with the ‘Error 500’ after just 4 or 5 records! This would be after ~2 minutes of processing. I tried disabling Workflows completely - which helped a little. But it would still fail after 40 records or so.

I feel like there has got to be a PHP setting, or an IIS setting - or something - that is ‘killing’ the connection after a set amount of time. I just don’t know what that setting is, and I think that I’ve already tried the usual suspects.

Thanks for the followup. Please let me know if there is anything else that you would have me try.

Regards,

Roquefort

Roquefort · 30 September 2014 07:04

So I set up ERROR-level logging again, and started creating test contacts. I noticed just this error firing:

09/30/14 01:52:17 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][FATAL] error loading relationship project_resource
09/30/14 01:57:30 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][ERROR] Unable to find relationship projects_quotes
09/30/14 01:57:30 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][FATAL] quotes for projects_quotes failed to load

Upon enabling the Project module’s menu item, and navigating to it, I see these errors while navigating around projects:

09/30/14 01:57:30 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][FATAL] error loading relationship quotes
09/30/14 01:57:31 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][ERROR] Unable to get proper side for link project_name_link
09/30/14 01:57:31 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][ERROR] Unable to get proper side for link project_name_link
09/30/14 01:57:31 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][ERROR] Unable to get proper side for link project_name_link
09/30/14 01:57:31 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][ERROR] Unable to get proper side for link project_name_link
09/30/14 01:58:05 [8260][5575ecc7-9b88-803d-0c3d-535dd8cf3c0c][FATAL] error loading relationship project_resource

I’ve never once used the Projects module (in fact, it still had dummy data), so I’m not sure what might have happened to it. I did try a quick repair, and a rebuild of the relationships. Does this info help at all?

Will · 30 September 2014 08:25

Hi Roquefort,

These look like rogue relationships. Navigate to the project module and edit the vardefs.php file. If you can locate the project_resource and projects_quotes relationships, try to remove them and then run a Quick Repair & Rebuild and re-try.

Thanks,

Will.

Roquefort · 30 September 2014 16:14

I think this definitely worked for me, in part. Rather than removing any of these relationships, I dug into the Contacts module’s vardefs and found projects_contacts_resources was poorly formatted, for whatever reason. Fixing this formatting seems to have eliminated those error messages.

That said, it was still taking ~20 seconds to create a brand new Contact or Account record. By comparison, creating a new Lead takes about 3 seconds after clicking “save”.

I’m doing WARN-level logging now, and I don’t see anything relevant. At DEBUG level, with slow-queries turned on, I started seeing numerous preprocess_on_save actions.

It turns out that those were related to Workflows. If I turn off Workflows for that module completely - it seems nice and fast again.

That said, I had built 8 workflows to automatically assign security groups to Contact records matching the specific states that we operate in. I’d love to be able to do this still, if possible. Any idea how I can accomplish this without using Workflows (or in a more efficient way)? For what it’s worth, I have the same Workflows built on my Leads module, and it doesn’t seem to slow down the queries at all.

Roquefort · 30 September 2014 16:29

Okay, this is definitely a Workflow problem. Here’s what I found:

I found a Workflow that probably had “repeated runs” enabled at some point. There were 23000 audit records for it.
This workflow takes a long time just to open. I can’t even delete it because the query times out.
If I disable the Workflow, and create a new copy of it, my ‘save’ time is cut in half, to about 10 seconds.
Removing Workflows and duplicating them seems to be cutting the ‘save’ time with every attempt.

So, I’m not really sure why, but it seems like Workflows with long audit histories really slow down my system. Is expected behavior?

sieberta · 6 November 2014 19:05

I found this thread as a result of a search for “quotes for projects_quotes failed to load”… and Will’s suggestion seems to have worked great for me.

I was/am not having the slow load times and queries.

ConnectGO · 14 January 2015 20:54

I’m using 7.1.4.1 on Azure, Bitnami install, currently have 8 cores, and 14GB memory to try and power through the importing, I really don’t think I need nearly this much power, and it seems something must not be right. I have memory_limit set to 2048M (also probably massive overkill) and PHP 5.4.35

In the project module vardefs.php file, I don’t have a project_resource. I did comment out projects_quotes relationship and did quick rebuild, but I can barely delete 100 contacts or tasks at a time.

I only had 1 workflow set up, which I have now set to inactive.

It seems that cranking up memory limit to 2048 allowed 100 records to be deleted at a time, but it seems I should be able to delete, or import for that matter 1000’s of records in seconds with this server, it looks like it’s updating maybe 1 per second, if that. Any ideas?