Поставили клиенту витруальную машину Битрикс несколько дней назад.
После этого обнаружили, что на ней крутятся незавершающиеся процессы, часами и сутками висят в состоянии statistics.
Что делать и как бороться?
Запросы однотипные, вот такие:
COUNT(DISTINCT BE.ID) as C
FROM b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
LEFT JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='WHELL_TYRE_MODEL'
INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='whell_tyre_model'
INNER JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID = B.ID AND FP2.CODE='wheel_width'
INNER JOIN b_iblock_property FP3 ON FP3.IBLOCK_ID = B.ID AND FP3.CODE='wheel_diam'
INNER JOIN b_iblock_property FP4 ON FP4.IBLOCK_ID = B.ID AND FP4.CODE='wheel_vilet'
INNER JOIN b_iblock_property FP5 ON FP5.IBLOCK_ID = B.ID AND FP5.CODE='wheel_count'
INNER JOIN b_iblock_property FP6 ON FP6.IBLOCK_ID = B.ID AND FP6.CODE='wheel_diam_otv'
INNER JOIN b_iblock_property FP7 ON FP7.IBLOCK_ID = B.ID AND FP7.CODE='wheel_diam_center'
INNER JOIN b_iblock_property FP8 ON FP8.IBLOCK_ID = B.ID AND FP8.CODE='ACTIVE_S'
LEFT JOIN b_iblock_element_property FPV0 ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV1 ON FPV1.IBLOCK_PROPERTY_ID = FP1.ID AND FPV1.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV2 ON FPV2.IBLOCK_PROPERTY_ID = FP2.ID AND FPV2.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV3 ON FPV3.IBLOCK_PROPERTY_ID = FP3.ID AND FPV3.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV4 ON FPV4.IBLOCK_PROPERTY_ID = FP4.ID AND FPV4.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV5 ON FPV5.IBLOCK_PROPERTY_ID = FP5.ID AND FPV5.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV6 ON FPV6.IBLOCK_PROPERTY_ID = FP6.ID AND FPV6.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV7 ON FPV7.IBLOCK_PROPERTY_ID = FP7.ID AND FPV7.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV8 ON FPV8.IBLOCK_PROPERTY_ID = FP8.ID AND FPV8.IBLOCK_ELEMENT_ID = BE.ID
LEFT JOIN b_iblock_element BE0 ON BE0.ID = FPV0.VALUE_NUM AND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL))
LEFT JOIN b_catalog_price CAT_P1 ON (CAT_P1.PRODUCT_ID = BE.ID AND CAT_P1.CATALOG_GROUP_ID = 1)
LEFT JOIN b_catalog_price CAT_P4 ON (CAT_P4.PRODUCT_ID = BE.ID AND CAT_P4.CATALOG_GROUP_ID = 4)
LEFT JOIN b_catalog_price CAT_P10 ON (CAT_P10.PRODUCT_ID = BE.ID AND CAT_P10.CATALOG_GROUP_ID = 10)
LEFT JOIN b_catalog_price CAT_P11 ON (CAT_P11.PRODUCT_ID = BE.ID AND CAT_P11.CATALOG_GROUP_ID = 11)
LEFT JOIN b_catalog_product CAT_PR ON (CAT_PR.ID = BE.ID)
LEFT JOIN b_catalog_iblock CAT_IB ON ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
LEFT JOIN b_catalog_vat CAT_VAT ON (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
WHERE 1=1
AND (
(((FPV1.VALUE_NUM IN ('23540', '23542', '23571', '23572', '23574', '23575', '23576', '23577', '23578', '23650', '23653',
'23654', '23655', '23656', '23657', '23658', '23659', '23660', '23661', '23662', '23666', '23667', '23668', '23669', '23670', '23671', '23672', '23674',
'23675', '23676', '23677', '23742', '23743', '23744', '23745', '23754', '23755', '23808', '23809', '23810', '23811', '23812', '23928', '23929', '23930',
'23931', '23932', '23933', '24027', '24028', '24029', '24030', '24031', '24032', '24033', '24034', '24035', '24036', '24037', '24038', '24064', '24065',
'24067', '24068', '24069', '24070', '24071', '24072', '24080', '24081', '24082', '24083', '24084', '24127', '24132', '24133', '24134', '24135', '24136',
'24137', '24138', '24139', '24141', '24144', '24151', '24152', '24153', '24154', '24155', '24156', '24157', '24158', '24159', '24160', '24161', '24162',
'24163', '24165', '24166', '24167', '24168', '24169', '24170', '24171', '24172', '24173', '24174', '24175', '24176', '24177', '24178', '24179', '24180',
'24187', '24188', '24189', '24190', '24191', '24192', '24193', '24601', '24602', '24603', '24604', '24605', '24606', '24607', '24608', '24611', '24612',
'24616', '24617', '24618', '24619', '24620', '24621', '24622', '24653', '24667', '24668', '24669', '24670', '24671', '24672', '24673', '24675', '24676',
'24677', '24678', '24679', '24680', '24681', '24682', '24710', '24711', '24712', '24713', '24714', '24715', '24716', '24717', '24718', '24719', '24735',
'24736', '24739', '24815', '24816', '24817', '24818', '24819', '24909', '24910', '24986', '24987', '24988', '24989', '24990', '24991', '24992', '24993',
'24994', '24995', '24996', '24997', '24998', '24999', '25000', '25001', '25002', '25003', '25004', '25005', '25006', '25007', '25008', '25009', '25010',
'25011', '25012', '25013', '25014', '25015', '25016', '25017', '25018', '25019', '25020', '25021', '25022', '25023', '25024', '25025', '25026', '25027',
'25028', '25029', '25030', '25031', '25032', '25033', '25034', '25035', '25036', '25037', '25038', '25039', '25040', '25042', '25043', '25044', '25045',
'25046', '25047', '25681', '25682', '25683', '25684', '25685', '25686', '25687', '25688', '25689', '25690', '25691', '25692', '26077', '26078', '26079',
'26080', '26081', '26082', '26083', '26084', '26085', '26086', '26087', '26088', '26089', '26090', '26091', '26092', '26093', '26094', '26095', '26096',
'26097', '26098', '26099', '26100', '26101', '26105', '26106', '26107', '26108', '26116', '26117', '26118', '26119', '26120', '26121', '26122', '26123',
'26124', '26125', '26126', '26127', '26128', '26129', '26130', '26131', '26132', '26133', '26134', '26135', '26144', '26145', '26146', '26147', '26148',
'26149', '26150', '26151', '26152', '26153', '26154', '26155', '26314', '26315', '26316', '26317', '26318', '26319', '26320', '26321', '26322', '26323',
'26522', '26523', '26524', '26525', '26526', '26527', '26528', '26529', '26530', '26531', '26532', '26533', '26547', '26548', '26549', '26822', '26823',
'26824', '26825', '26826', '26827', '26828', '26829', '26830', '27428', '27429', '27431', '27433', '27436', '27437', '27438', '27439', '27440', '27441',
'27442', '27443', '27444', '27445', '27446', '27447', '27448'))))
AND ((((FPV2.VALUE_NUM = '18618'))))
AND ((((FPV3.VALUE_NUM = '18602'))))
AND ((((FPV4.VALUE_NUM = '18545'))))
AND ((((FPV5.VALUE_NUM = '18591'))))
AND ((((FPV6.VALUE_NUM = '18567'))))
AND ((((FPV7.VALUE_NUM = '18584'))))
AND (
((((FPV8.VALUE LIKE 'Y'))))
)
AND ((((BE.IBLOCK_ID = '88'))))
AND ((((B.ACTIVE='Y'))))
AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL)))
AND ((((BE.ACTIVE='Y'))))
AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
После этого обнаружили, что на ней крутятся незавершающиеся процессы, часами и сутками висят в состоянии statistics.
Что делать и как бороться?
Запросы однотипные, вот такие:
COUNT(DISTINCT BE.ID) as C
FROM b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
LEFT JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='WHELL_TYRE_MODEL'
INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='whell_tyre_model'
INNER JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID = B.ID AND FP2.CODE='wheel_width'
INNER JOIN b_iblock_property FP3 ON FP3.IBLOCK_ID = B.ID AND FP3.CODE='wheel_diam'
INNER JOIN b_iblock_property FP4 ON FP4.IBLOCK_ID = B.ID AND FP4.CODE='wheel_vilet'
INNER JOIN b_iblock_property FP5 ON FP5.IBLOCK_ID = B.ID AND FP5.CODE='wheel_count'
INNER JOIN b_iblock_property FP6 ON FP6.IBLOCK_ID = B.ID AND FP6.CODE='wheel_diam_otv'
INNER JOIN b_iblock_property FP7 ON FP7.IBLOCK_ID = B.ID AND FP7.CODE='wheel_diam_center'
INNER JOIN b_iblock_property FP8 ON FP8.IBLOCK_ID = B.ID AND FP8.CODE='ACTIVE_S'
LEFT JOIN b_iblock_element_property FPV0 ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV1 ON FPV1.IBLOCK_PROPERTY_ID = FP1.ID AND FPV1.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV2 ON FPV2.IBLOCK_PROPERTY_ID = FP2.ID AND FPV2.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV3 ON FPV3.IBLOCK_PROPERTY_ID = FP3.ID AND FPV3.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV4 ON FPV4.IBLOCK_PROPERTY_ID = FP4.ID AND FPV4.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV5 ON FPV5.IBLOCK_PROPERTY_ID = FP5.ID AND FPV5.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV6 ON FPV6.IBLOCK_PROPERTY_ID = FP6.ID AND FPV6.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV7 ON FPV7.IBLOCK_PROPERTY_ID = FP7.ID AND FPV7.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV8 ON FPV8.IBLOCK_PROPERTY_ID = FP8.ID AND FPV8.IBLOCK_ELEMENT_ID = BE.ID
LEFT JOIN b_iblock_element BE0 ON BE0.ID = FPV0.VALUE_NUM AND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL))
LEFT JOIN b_catalog_price CAT_P1 ON (CAT_P1.PRODUCT_ID = BE.ID AND CAT_P1.CATALOG_GROUP_ID = 1)
LEFT JOIN b_catalog_price CAT_P4 ON (CAT_P4.PRODUCT_ID = BE.ID AND CAT_P4.CATALOG_GROUP_ID = 4)
LEFT JOIN b_catalog_price CAT_P10 ON (CAT_P10.PRODUCT_ID = BE.ID AND CAT_P10.CATALOG_GROUP_ID = 10)
LEFT JOIN b_catalog_price CAT_P11 ON (CAT_P11.PRODUCT_ID = BE.ID AND CAT_P11.CATALOG_GROUP_ID = 11)
LEFT JOIN b_catalog_product CAT_PR ON (CAT_PR.ID = BE.ID)
LEFT JOIN b_catalog_iblock CAT_IB ON ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
LEFT JOIN b_catalog_vat CAT_VAT ON (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
WHERE 1=1
AND (
(((FPV1.VALUE_NUM IN ('23540', '23542', '23571', '23572', '23574', '23575', '23576', '23577', '23578', '23650', '23653',
'23654', '23655', '23656', '23657', '23658', '23659', '23660', '23661', '23662', '23666', '23667', '23668', '23669', '23670', '23671', '23672', '23674',
'23675', '23676', '23677', '23742', '23743', '23744', '23745', '23754', '23755', '23808', '23809', '23810', '23811', '23812', '23928', '23929', '23930',
'23931', '23932', '23933', '24027', '24028', '24029', '24030', '24031', '24032', '24033', '24034', '24035', '24036', '24037', '24038', '24064', '24065',
'24067', '24068', '24069', '24070', '24071', '24072', '24080', '24081', '24082', '24083', '24084', '24127', '24132', '24133', '24134', '24135', '24136',
'24137', '24138', '24139', '24141', '24144', '24151', '24152', '24153', '24154', '24155', '24156', '24157', '24158', '24159', '24160', '24161', '24162',
'24163', '24165', '24166', '24167', '24168', '24169', '24170', '24171', '24172', '24173', '24174', '24175', '24176', '24177', '24178', '24179', '24180',
'24187', '24188', '24189', '24190', '24191', '24192', '24193', '24601', '24602', '24603', '24604', '24605', '24606', '24607', '24608', '24611', '24612',
'24616', '24617', '24618', '24619', '24620', '24621', '24622', '24653', '24667', '24668', '24669', '24670', '24671', '24672', '24673', '24675', '24676',
'24677', '24678', '24679', '24680', '24681', '24682', '24710', '24711', '24712', '24713', '24714', '24715', '24716', '24717', '24718', '24719', '24735',
'24736', '24739', '24815', '24816', '24817', '24818', '24819', '24909', '24910', '24986', '24987', '24988', '24989', '24990', '24991', '24992', '24993',
'24994', '24995', '24996', '24997', '24998', '24999', '25000', '25001', '25002', '25003', '25004', '25005', '25006', '25007', '25008', '25009', '25010',
'25011', '25012', '25013', '25014', '25015', '25016', '25017', '25018', '25019', '25020', '25021', '25022', '25023', '25024', '25025', '25026', '25027',
'25028', '25029', '25030', '25031', '25032', '25033', '25034', '25035', '25036', '25037', '25038', '25039', '25040', '25042', '25043', '25044', '25045',
'25046', '25047', '25681', '25682', '25683', '25684', '25685', '25686', '25687', '25688', '25689', '25690', '25691', '25692', '26077', '26078', '26079',
'26080', '26081', '26082', '26083', '26084', '26085', '26086', '26087', '26088', '26089', '26090', '26091', '26092', '26093', '26094', '26095', '26096',
'26097', '26098', '26099', '26100', '26101', '26105', '26106', '26107', '26108', '26116', '26117', '26118', '26119', '26120', '26121', '26122', '26123',
'26124', '26125', '26126', '26127', '26128', '26129', '26130', '26131', '26132', '26133', '26134', '26135', '26144', '26145', '26146', '26147', '26148',
'26149', '26150', '26151', '26152', '26153', '26154', '26155', '26314', '26315', '26316', '26317', '26318', '26319', '26320', '26321', '26322', '26323',
'26522', '26523', '26524', '26525', '26526', '26527', '26528', '26529', '26530', '26531', '26532', '26533', '26547', '26548', '26549', '26822', '26823',
'26824', '26825', '26826', '26827', '26828', '26829', '26830', '27428', '27429', '27431', '27433', '27436', '27437', '27438', '27439', '27440', '27441',
'27442', '27443', '27444', '27445', '27446', '27447', '27448'))))
AND ((((FPV2.VALUE_NUM = '18618'))))
AND ((((FPV3.VALUE_NUM = '18602'))))
AND ((((FPV4.VALUE_NUM = '18545'))))
AND ((((FPV5.VALUE_NUM = '18591'))))
AND ((((FPV6.VALUE_NUM = '18567'))))
AND ((((FPV7.VALUE_NUM = '18584'))))
AND (
((((FPV8.VALUE LIKE 'Y'))))
)
AND ((((BE.IBLOCK_ID = '88'))))
AND ((((B.ACTIVE='Y'))))
AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL)))
AND ((((BE.ACTIVE='Y'))))
AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))