Critical Performance Issue. Urgent Help Required

[VeonConsulting]

Hi

Our sugar instance is hosted in Siteground. Sugar Version 4.5.1e

Our instance has 100000 leads and 50000 emails. Now the sugar has a serious performance problem (Error 503 - Service Temporarily Unavailable This error means that the web server is currently unable to handle your request due to a temporary overload. Please try to reload the webpage in a few seconds.) We are not able to do any transaction. It is very business critical.

Following are the queries that are taking too much time to execute.

The table which our system administrators referred to is the emails one (emails.MYD), which is actually the one taking the whole space of the database.

>> Details of queries/ frequency and name of query if any specific query is problematic.

Executed 15 min ago for 2 sec on Database --> resumen1_visa.
SELECT count(*) c FROM emails LEFT JOIN emails_contacts ec ON emails.id = ec.email_id LEFT JOIN contacts ON ec.contact_id = contacts.id LEFT JOIN users ON emails.assigned_user_id=users.id WHERE emails.assigned_user_id IN ('abc','17180d64-f9e3-4eac-7aef-478a75729f4a' ) AND emails.type = 'inbound' AND emails.deleted=0

Executed 9 min ago for 4 sec on Database --> resumen1_visa.
SELECT count(*) c FROM leads LEFT JOIN leads_cstm ON leads.id = leads_cstm.id_c LEFT JOIN users jt0 ONjt0.id= leads.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 where leads.deleted=0

Executed 6 min ago for 2 sec on Database --> resumen1_visa.
SELECT count(*) c FROM emails LEFT JOIN emails_contacts ec ON emails.id = ec.email_id LEFT JOIN contacts ON ec.contact_id = contacts.id LEFT JOIN users ON emails.assigned_user_id=users.id WHERE emails.assigned_user_id = '68484c18-2494-eb1c-a0ea-478a746effe1' AND emails.type='out' AND emails.deleted=0

Executed 6 min ago for 101 sec on Database --> resumen1_visa.
SELECT count(*) c FROM emails LEFT JOIN emails_contacts ec ON emails.id = ec.email_id LEFT JOIN contacts ON ec.contact_id = contacts.id LEFT JOIN users ON emails.assigned_user_id=users.id WHERE emails.assigned_user_id IN ('abc','17180d64-f9e3-4eac-7aef-478a75729f4a' ) AND emails.type = 'inbound' AND emails.deleted=0




WE HAVE MADE THE FOLLOWING CHANGES IN CONFIG.PHP, BUT STILL THE PROBLEM PERSISTS

'disable_count_query' => true,
'disable_vcr' => true,
'hide_subpanels_on_login' => true,
'save_query' => 'populate_only',
'dump_slow_queries' => true,


Please help us in this regard. It is a very critical issue.

[kuske]

Perhaps you should innoDB Tables instead of myISAM, do you use myISAM?
And perhaps an index on table emails on column assigned_user_id could help.

[VeonConsulting]

Hi

Thanks for your reply. Every thing is ok as u mentioned. Any idea how to block that count query on the emails table.
If u have an idea please let us know as this is very urgent.

[kbrill]

I wish I had a 4.5.1e instance with that many records to test, but I don't. I just checked my 4.5.1i instance and I can't seem to reproduce this, but you don't really go into detail of how it happens. If your diable_count_queries is on and it still happens I would assume that there is a bug in 4.5.1e. I looked through the bug reports and did not find it in there. You might need to get someone with PHP skills to put some diagnostic code in the create_list_count_query() function in data/sugarbean.php and see when it is running and what is running it.

But what I can tell you is that 50,000 emails should NOT take 101 seconds to count. Can you run that exact query on your system and get the same result outside of Sugar?

What are the specs on the server? Is it shared? Is the database on the same machine as the web server?

I have see many installations with alot more data in them (including the test 451i instance I have on my laptop) and have not seen this error message ever.

[netron]

have you tweaked your mysql default configuration - such as upping the key buffer sizes and other tweaks?

[netron]

that last WHERE statement will return ALL emails with an assigned_user_id containing the string "abc":

WHERE emails.assigned_user_id IN ('abc','17180d64-f9e3-4eac-7aef-478a75729f4a' ) AND emails.type = 'inbound' AND emails.deleted=0

that looks a bit odd, as why would you be searching for "abc" strings in assigned user ids?


also, what is your output for

SHOW INDEXES IN emails;

[netron]

SELECT count(*) c FROM leads LEFT JOIN leads_cstm ON leads.id = leads_cstm.id_c LEFT JOIN users jt0 ONjt0.id= leads.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 where leads.deleted=0;

above took 0.52 seconds on my box. lead table with 40,000 records.
optimized my.cnf

quad operton box with 3 gig of ram.

[VeonConsulting]

Hi netron and kbrill

First of all I am very sorry I was unable to give to you answers in right time. Actually I was going through a hectic schedules searching out how to optimize that query and also mysql query cache. Anyway many thanks to your suggestions.

At last I came to know the problem is not from sugar side. Its due to my sugar is hosted in shared hosting server on site ground. I immediately converted it to VPS. Now everything is running ok except the following thing.

I am having updated data in the mysql database for emails, leads, schedulers tables. But from sugar it is only showing those data present in the database before the upgradation from shared server to VPS. I even asked this to my hosting provider siteground.com

Even if I put some echo in the listviewgroup.php under email module to see whether it is rendering the current php scripts or not, it is not at all affecting to the html page.

So I am again stuck because I am not able to process my mails.

[netron]

talk to your ISP about this.
you probably dont have the correct user permissions in your vps.

i would also ask the isp for a dedicated box. it'll cost more, but at least you'll have full (root) control over it.

[VeonConsulting]

Hi netron

Can you please specify what kind of user permissions you wanted to mean. I doubt may be this problem can be caused due to some uncleared cache files, not sure. But I havnot found anything. I have posted the same topic on the developers forum also.