Heavy load on esi:update:contracts schedule #699
Description
-
Problem:
esi:update:contractsschedule has a huge impact on the server load and total duration is more than 10 minutes -
Expected: Really less load and process faster
-
Logs / Screenshots / Proof:
Actually, the load looks to be from the PHP schedule taskesi:update:contractswhich looks to be in an infinite loop (see below for monitored SQL queries).
It's raising the CPU above 50% for MySQL same for the php process.
During schedule :
Once killed :
I've enabled the general_log for this purpose and the final size is around 3G within an hour. Here are some query in that file :
2020-10-23T21:35:54.208665+01:00 41779 Prepare select * from `character_infos` where `character_infos`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.208758+01:00 41779 Execute select * from `character_infos` where `character_infos`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.209037+01:00 41779 Close stmt
2020-10-23T21:35:54.209617+01:00 41779 Prepare select * from `character_affiliations` where `character_affiliations`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.209760+01:00 41779 Execute select * from `character_affiliations` where `character_affiliations`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.210045+01:00 41779 Close stmt
2020-10-23T21:35:54.210616+01:00 41779 Prepare select * from `contract_details` where `contract_details`.`contract_id` = ? and `contract_details`.`contract_id` is not null limit 1
2020-10-23T21:35:54.210718+01:00 41779 Execute select * from `contract_details` where `contract_details`.`contract_id` = %%CONTRACT_ID_1%% and `contract_details`.`contract_id` is not null limit 1
2020-10-23T21:35:54.214685+01:00 41779 Close stmt
2020-10-23T21:35:54.215239+01:00 41779 Prepare select * from `character_infos` where `character_infos`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.215358+01:00 41779 Execute select * from `character_infos` where `character_infos`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.215602+01:00 41779 Close stmt
2020-10-23T21:35:54.216135+01:00 41779 Prepare select * from `character_affiliations` where `character_affiliations`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.216194+01:00 41779 Execute select * from `character_affiliations` where `character_affiliations`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.216376+01:00 41779 Close stmt
2020-10-23T21:35:54.216988+01:00 41779 Prepare select * from `contract_details` where `contract_details`.`contract_id` = ? and `contract_details`.`contract_id` is not null limit 1
2020-10-23T21:35:54.217113+01:00 41779 Execute select * from `contract_details` where `contract_details`.`contract_id` = %%CONTRACT_ID_2%% and `contract_details`.`contract_id` is not null limit 1
2020-10-23T21:35:54.221266+01:00 41779 Close stmt
2020-10-23T21:35:54.221802+01:00 41779 Prepare select * from `character_infos` where `character_infos`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.221944+01:00 41779 Execute select * from `character_infos` where `character_infos`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.222196+01:00 41779 Close stmt
2020-10-23T21:35:54.222757+01:00 41779 Prepare select * from `character_affiliations` where `character_affiliations`.`character_id` in (%%SAME_CHARACTER_ID%%)
2020-10-23T21:35:54.222817+01:00 41779 Execute select * from `character_affiliations` where `character_affiliations`.`character_id` in (%%SAME_CHARACTER_ID%%)According to MySQL, this single schedule is doing more than 150 SELECT/s.
- Version Info:
| Vendor | Package Name | Installed Version |
| ------- | -------------- | ------------------------ |
| eveseat | api | 4.1.0 |
| eveseat | console | 4.0.4 |
| eveseat | eveapi | 4.1.3 |
| eveseat | notifications | 4.0.3 |
| eveseat | services | 4.0.0 |
| eveseat | web | 4.2.2 |
| ccp | eve_online_sde | sde-20201008-TRANQUILITY |
$ php -v
PHP 7.3.23-4+0~20201018.71+debian10~1.gbpfc8934 (cli) (built: Oct 18 2020 21:34:03) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.23, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.23-4+0~20201018.71+debian10~1.gbpfc8934, Copyright (c) 1999-2018, by Zend Technologies