Hight server load

[livewire]

Hi

Im having ESPO 5.1.2

In the pas few days my CRM has been running extremely slow and i have been getting random 532 errors. In using the CRM on a VPS 4GB RAM

So when i contacted the hosting guys they sent me the below

Thank you for contacting us.

I checked the server and found that the application at:

> www/crm/

is spawning too many processes which is not normal:

0 424m 104m 83m S 29.9 2.6 5359:43 /usr/local/xvarnish/bin/xvstats
25731 crms 20 0 271m 31m 20m S 2.7 0.8 0:00.08 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25732 crms 20 0 271m 31m 20m S 2.7 0.8 0:00.08 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25733 crms 20 0 271m 31m 20m S 2.7 0.8 0:00.08 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25735 crms 20 0 271m 31m 19m S 2.7 0.8 0:00.08 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
3561 varnish 20 0 584m 194m 82m S 2.3 4.9 315:58.30 /usr/sbin/varnishd -P /var/run/varnish.pid -f /etc/varnish.conf.d/default.vcl -a :80 -a 127.
25723 crms 20 0 271m 31m 20m S 2.3 0.8 0:00.07 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25725 crms 20 0 271m 31m 20m S 2.3 0.8 0:00.07 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25727 crms 20 0 271m 31m 20m S 2.3 0.8 0:00.07 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25729 crms 20 0 271m 31m 20m S 2.3 0.8 0:00.07 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25730 crms 20 0 271m 31m 20m S 2.3 0.8 0:00.07 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25734 crms 20 0 271m 31m 20m S 2.3 0.8 0:00.07 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25728 crms 20 0 271m 31m 20m S 2.0 0.8 0:00.06 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25736 crms 20 0 271m 31m 20m S 2.0 0.8 0:00.06 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php
25737 crms 20 0 271m 31m 19m S 2.0 0.8 0:00.06 /opt/cpanel/ea-php70/root/usr/bin/php-cgi www/crm/api/v1/index.php

This is leading to a high load on the server and slow loading. The average load on the server is 6-7 at the moment. Additionally, I found multiple stalled MySQL queries:

+---------+-------------+-----------+---------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+-----------+---------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 7568009 | crmuser | localhost | crmuser222 | Query | 1 | Sending data | SELECT DISTINCT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name |
| 7568237 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | INSERT entity_phone_number
(entity_id, entity_type, phone_number_id, `pr |
| 7568832 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | INSERT entity_phone_number
(entity_id, entity_type, phone_number_id, `pr |
| 7569575 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | UPDATE entity_email_address
SET `primary` = 1
|
| 7571721 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT * FROM `entity_email_address` WHERE entity_id = '5b2b4eecad4056545' AND email_address_id = '5 |
| 7573246 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | UPDATE entity_phone_number
SET `primary` = 0
|
| 7577686 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT email_address.name, email_address.lower, email_address.invalid, email_address.opt_out AS optO |
| 7577689 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name, ''))) A |
| 7577697 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name, ''))) A |
| 7577700 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name, ''))) A |
| 7577701 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT email_address.name, email_address.lower, email_address.invalid, email_address.opt_out AS optO |
| 7577705 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name, ''))) A |
| 7577707 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name, ''))) A |
| 7577709 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
| 7577710 | crmuser | localhost | crmuser222 | Query | 1 | Waiting for table level lock | SELECT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name, ''))) A |
| 7577711 | crmuser | localhost | crmuser222 | Query | 0 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
| 7577712 | crmuser | localhost | crmuser222 | Query | 0 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
| 7577713 | crmuser | localhost | crmuser222 | Query | 0 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
| 7577714 | crmuser | localhost | crmuser222 | Query | 0 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
| 7577715 | crmuser | localhost | crmuser222 | Query | 0 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
| 7577716 | root | localhost | | Query | 0 | init | show processlist |


This is also reflecting on the performance of the server and its load. I killed these queries and the issue will be temporary resolved. Additionally there are multiple slow MySQL queries:

# Time: 180621 7:13:27
# User@Host: crmuser[crmuser] @ localhost [] Id: 7578488
# Query_time: 13.199912 Lock_time: 8.140531 Rows_sent: 1 Rows_examined: 459006
SET timestamp=1529565207;
SELECT COUNT(lead.id) AS AggregateValue FROM `lead` LEFT JOIN `user` AS `assignedUser` ON lead.assigned_user_id = assignedUser.id LEFT JOIN `user` AS `modifiedBy` ON lead.modified_by_id = modifiedBy.id LEFT JOIN `user` AS `createdBy` ON lead.created_by_id = createdBy.id LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON lead.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Lead' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = '0' LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON lead.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Lead' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0'
LEFT JOIN currency AS `opportunity_amount_currency_alias` ON opportunity_amount_currency_alias.id = lead.opportunity_amount_currency
WHERE (((lead.first_name LIKE 'customeremail@mail.com%' OR lead.last_name LIKE 'customeremail@mail.com%' OR CONCAT(lead.first_name, ' ', lead.last_name) LIKE 'customeremail@mail.com%' OR CONCAT(lead.last_name, ' ', lead.first_name) LIKE 'customeremail@mail.com%') OR lead.account_name LIKE 'customeremail@mail.com%' OR lead.id IN (
SELECT entity_id
FROM entity_email_address
JOIN email_address ON email_address.id = entity_email_address.email_address_id
WHERE
entity_email_address.deleted = 0 AND entity_email_address.entity_type = 'Lead' AND
email_address.deleted = 0 AND email_address.name LIKE 'customeremail@mail.com%'
))) AND lead.deleted = '0';
# Time: 180621 7:13:37
# User@Host: crmuser[crmuser] @ localhost [] Id: 7568009
# Query_time: 23.017921 Lock_time: 8.081402 Rows_sent: 7 Rows_examined: 966388
SET timestamp=1529565217;
SELECT DISTINCT lead.id AS `id`, TRIM(CONCAT(IFNULL(lead.first_name, ''), ' ', IFNULL(lead.last_name, ''))) AS `name`, lead.deleted AS `deleted`, lead.salutation_name AS `salutationName`, lead.first_name AS `firstName`, lead.last_name AS `lastName`, lead.title AS `title`, lead.status AS `status`, lead.source AS `source`, lead.industry AS `industry`, lead.opportunity_amount AS `opportunityAmount`, lead.website AS `website`, lead.address_street AS `addressStreet`, lead.address_city AS `addressCity`, lead.address_state AS `addressState`, lead.address_country AS `addressCountry`, lead.address_postal_code AS `addressPostalCode`, emailAddresses.name AS `emailAddress`, phoneNumbers.name AS `phoneNumber`, lead.do_not_call AS `doNotCall`, lead.description AS `description`, lead.created_at AS `createdAt`, lead.modified_at AS `modifiedAt`, lead.account_name AS `accountName`, lead.opportunity_amount_currency AS `opportunityAmountCurrency`, lead.oppor

A single query should be executed for not more than a 2-3 seconds. In your case, there are queries which required more than 13 seconds. I am highly recommending to review your application and database with a local developer because this is not related to the server configuration. He should be able to review the code of your databases and most likely to resolve the issue.

Can you let me know how this can be fixed?

Thanks

[livewire]

Any help on this please?

[codeboy]

Did you check the log file? There may be an error. There could be endless looping codes.

[dhemo]

Oh, this was my problem
درمان هموروئید

[livewire]

This problem happens when i try to import leads.. for an example when i try to import like 1000 leads the whole system breaks so none of the other users are able to use it..

[tanya]

Did you try to import in idle mode?