Slow backend and error problems with slow query

[demux]

Good morning, recently after a migration into a new dedicated server, we have problems for a very slow backend (for example to modify a product description the time is 50 seconds!) and in the opinion of our technical support in data center:

 

 

ANALYSYS OF OUR SOFTWARE ANALYST IN DATA CENTER:

"problems are linked to SLOW queries for example here we have slow queries for tag changing of a product" :

# Time: 161107 17:28:50
# User@Host: simciti7_usr[simciti7_usr] @ localhost []
# Thread_id: 3214132 Schema: simciti7_multishop_ok QC_hit: No
# Query_time: 18.524613 Lock_time: 0.000272 Rows_sent: 0 Rows_examined: 5583792
SET timestamp=1478536130;
REPLACE INTO `ps_tag_count` (id_group, id_tag, id_lang, id_shop, counter)
SELECT cg.id_group, t.id_tag, t.id_lang, ps.id_shop, COUNT(pt.id_tag) AS times
FROM `ps_product_tag` pt
LEFT JOIN `ps_tag` t ON (t.id_tag = pt.id_tag)
LEFT JOIN `ps_product` p ON (p.id_product = pt.id_product)
INNER JOIN `ps_product_shop` product_shop
ON (product_shop.id_product = p.id_product)
JOIN (SELECT DISTINCT id_group FROM `ps_category_group`) cg
JOIN (SELECT DISTINCT id_shop FROM `ps_shop`) ps
WHERE pt.`id_lang` = 1 AND product_shop.`active` = 1
AND EXISTS(SELECT 1 FROM `ps_category_product` cp
LEFT JOIN `ps_category_group` cgo ON (cp.`id_category` = cgo.`id_category`)
WHERE cgo.`id_group` = cg.id_group AND p.`id_product` = cp.`id_product`)
AND product_shop.id_shop = ps.id_shop
GROUP BY pt.id_tag, cg.id_group;
# Time: 161107 17:29:08
# User@Host: simciti7_usr[simciti7_usr] @ localhost []
# Thread_id: 3214132 Schema: simciti7_multishop_ok QC_hit: No
# Query_time: 18.164483 Lock_time: 0.000307 Rows_sent: 0 Rows_examined: 5584088
SET timestamp=1478536148;
REPLACE INTO `ps_tag_count` (id_group, id_tag, id_lang, id_shop, counter)
SELECT cg.id_group, t.id_tag, t.id_lang, ps.id_shop, COUNT(pt.id_tag) AS times
FROM `ps_product_tag` pt
LEFT JOIN `ps_tag` t ON (t.id_tag = pt.id_tag)
LEFT JOIN `ps_product` p ON (p.id_product = pt.id_product)
INNER JOIN `ps_product_shop` product_shop
ON (product_shop.id_product = p.id_product)
JOIN (SELECT DISTINCT id_group FROM `ps_category_group`) cg
JOIN (SELECT DISTINCT id_shop FROM `ps_shop`) ps
WHERE pt.`id_lang` = 1 AND product_shop.`active` = 1
AND EXISTS(SELECT 1 FROM `ps_category_product` cp
LEFT JOIN `ps_category_group` cgo ON (cp.`id_category` = cgo.`id_category`)
WHERE cgo.`id_group` = cg.id_group AND p.`id_product` = cp.`id_product`)
AND product_shop.id_shop = ps.id_shop
GROUP BY pt.id_tag, cg.id_group;
# Time: 161107 17:29:10
# User@Host: simciti7_usr[simciti7_usr] @ localhost []
# Thread_id: 3214135 Schema: simciti7_multishop_ok QC_hit: No
# Query_time: 38.976382 Lock_time: 0.001305 Rows_sent: 10 Rows_examined: 70013482
SET timestamp=1478536150;
SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
cl.link_rewrite crewrite, pl.link_rewrite prewrite ,(
SELECT SUM(weight)
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 1
AND sw.id_shop = 2
AND si.id_product = p.id_product
AND (sw.word LIKE '%200%')
) position
FROM ps_product p
INNER JOIN `ps_product_lang` pl ON (
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = 1 AND pl.id_shop = 2
)
INNER JOIN ps_product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 2)
INNER JOIN `ps_category_lang` cl ON (
product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = 1 AND cl.id_shop = 2
)
WHERE p.`id_product` IN (463,1587,1646,1647,1648,1649,1650,1652,1658,1659,1660,1661,1663,1664,1665,1743,1744,1769,1775,1796,1801,1828,1829,1830,1831,1832,1834,1835,1836,1837,1838,1846,1855,1859,1900,1901,1902,1903,1904,1905,1931,1946,1958,1965,1985,2032,2071,2072,2076,2077,2078,2097,2098,2099,2118,2119,2120,2122,2123,2124,2125,2145,2166,2167,2168,2178,2183,2185,2186,2245,2250,2317,2318,2319,2320,2330,2331,2332,2333,2343,2344,2351,22817,22832,22895,23036,23037,23204,23354,23357,23383,23447,120,142,121,2074,122,931,123,152,153,154,155,1020,5,134,135,136,137,151,157,18,19,20,23135,23136,23137,26939,26941,26942,26943,26944,26945,26946,26948,26949,26950,26951,26952,26953,26954,26955,26956,26957,26958,26959,26960,26961,26962,26963,26964,26965,26966,26967,26968,26969,26970,26971,26972,26973,2075,2104,2187,23020,23269,26771,26772,26774,26776,26778,26780,26920,26921,26922,27030,487,643,460,461,488,22862,462,464,465,475,476,1190,1191,22857,22858,22859,22860,466,166,168,1399,1222,26869,26866,1097,43,470,471,472,474,480,22853,22854,655,656,1535,1536,22960,22961,23452,653,654,1533,1534,22958,22959,657,658,1538,22962,22963,22964,1030,481,473,1413,1414,1415,1416,1417,1418,1443,22855,22856,22968,26895,74,165,1449,1450,3,2,59,39,40,41,42,34,7,4,8,9,77,78,79,80,1792,195,1494,26870,1827,1833,1210,1211,1212,1213,6,26851,26852,26853,26854,1662,1798,1799,1800,2326,689,690,691,692,1386,1389,1390,1391,1392,1290,1291,1292,685,23221,23222,23223,258,23347,200,219,232,233,234,235,190,197,191,1099,2258,193,537,538,520,539,540,541,577,578,579,580,22869,22870,494,495,496,497,1031,1050,1051,1052,1053,22871,22872,22873,546,547,548,549,550,551,22865,22866,553,554,552,192,199,23454,1595,1954,23048,23056,23064,23068,1820,1086,26890,26891,1077,1478,1479,1480,1481,23175,26893,1180,1462,610,611,612,937,661,22957,663,942,943,1500,1516,22921,22922,1517,22923,22924,22925,22926,22927,22928,679,680,22929,22930,22931,22932,23102,23103,23448,81,82,109,35,63,640,1697,22918,1862,1863,1864,1865,1866,1867,1868,1869,1870,1871,1872,1873,1874,1875,1876,26806,26807,26808,26813,1729,23296,1137,1431,1915,1930,1932,2107,1941,26860,26861,26862,26863,1456,2259,1412,972,973,974,975,1768,2304,2328,26999,27000,27003,411,2339,2340,1197,408,437,409,1198,438,410,429,430,431,432,433,1296,1297,1298,1275,1109,1110,1111,1112,1214,1651,1680,1899,23000,23324,23325,23326,23339,26831,26938,1138,1929,23277,23278,23279,23280,23281,2229,273,274,275,1015,1016,633,634,629,630,22993,632,631,635,636,637,638,244,1424,938,939,940,941,22991,22992,1638,1639,1640,23297,1606,1668,1762,1957,1959,1960,22996,22997,2070,2144,331,298,1607,1130,1131,284,285,286,287,1115,1116,1117,288,289,290,291,1612,1613,1614,1615,304,305,306,1457,1458,1459,1460,315,316,317,318,1559,1560,1561,1562,323,324,325,326,1375,293,294,295,1616,1617,1618,1619,338,23256,23353,1019,1466,1428,23416,1468,1582,2222,26764,26765,26766,26767,1134,335,1133,1772,812,988,1438,1439,1440,1441,1379,1577,2108,2228,23253,23364,1383,1384,354,361,362,363,394,395,396,934,343,344,339,990,378,393,376,342,2260,382,1469,23169,23421,26991,822,1366,1777,1779,26763,26824,1153,725,734,738,750,759,760,761,763,764,765,1229,1230,1231,1236,802,806,1308,23027,23184,26933,23304,23305,23306,23307,23038,23039,23040,23041,23042,23043,23044,23045,23046,23274,23275,23276,23386,3573,3574,3575,3576,3577,3578,4479,5312,5313,5314,5315,5316,5322,5471,10049,10796,5821,5950,5951,5952,3980,3075,3076,3077,3078,2879,2880,2881,2883,2884,2885,5401,5402,5403,5404,5409,5323,11435,2416,2599,2600,2601,2607,4705,5061,5062,5063,5064,6698,6699,6701,6702,8600,4171,5095,5108,5109,7714,7715,7717,8712,10890,24522,24527,24532,24537,24542,24547,24552,24557,2687,4468,5301,6251,6252,6253,6255,9799,18742,4162,5126,5137,5139,5141,5813,7066,8683,8684,13741,13742,13743,19444,26208,26209,26210,26419,26420,2474,2475,2476,2477,2478,2479,2480,2481,2482,2484,2485,2486,2487,2488,2489,2490,2491,2492,2493,2494,2495,2496,3586,3587,3588,7821,7822,2572,2573,13227,13228,13230,13231,13232,7823,8039,8110,8111,8112,8114,8115,8461,7513,10650,11889,11890,11891,11892,13692,5457,11985,18693,25368,5681,5682,6697,8668,10574,11791,11792,2619,7500,7501,13394,13395,13396,5281,4373,8756,18707,24265,24266,2765,2779,2780,2781,2782,6928,13572,24947,26402,26405,26406,26407,26408,26409,26410,5867,13485,24756,24757,24758,3437,4639,5927,5930,5931,5932,7616,11093,11852,8436,11510,11512,21244,21245,21246,21247,24883,24884,24885,19416,3003,3015,4170,4298,4541,10700,14126,14374,21284,24692,26127,26128,13565,4489,25904,8377,3134,3135,3136,3144,3145,3147,3148,3149,3150,3151,4248,4597,4703,7342,7381,7920,7921,7922,7923,8250,8914,8915,8916,8917,8919,8920,23692,24784,4660,4662,5111,5112,5608,5733,6091,8016,10028,19370,19374,19375,19811,19812,20817,20818,3735,3736,3737,3742,3743,3744,3745,3746,3747,3748,3749,3750,3751,3752,3753,3754,3755,3756,3757,3758,3759,3760,3761,3762,3763,3764,3765,3766,3767,3769,3770,3772,3774,3775,3776,3777,3778,3779,3780,3781,3782,3783,3784,3785,3796,3813,3998,3999,4651,4652,4656,4657,4841,4842,4843,4844,6873,6874,6876,6877,6878,6879,6880,6882,6883,6895,6896,6897,6898,6899,6900,6901,6902,6903,8506,8507,9414,9504,9505,9512,9513,9569,9572,25296,25303,26692,26693,3714,3715,3716,3717,3718,3719,3720,3721,3722,3723,3724,3725,3726,3727,3728,3729,3730,3731,3732,3822,4430,4431,4432,4433,4434,4435,4997,4998,4999,5000,5002,5004,5014,5015,5016,5017,5254,5255,5257,5258,5259,5938,5939,5940,5941,5942,5943,5944,5945,5946,5947,5948,5949,7866,8116,8986,10176,10178,10179,10180,10182,10183,10184,26124,4009,4827,7170,7962,7963,11102,11103,11104,11105,11106,11107,4516,6026,6835,24275,26633,26634,26636,26637,10828,20436,25813,15025,15026,15027,15028,15030,15031,15032,15033,15035,15036,15037,15038,15041,15042,15055,16027,18971,19749,20755,26589,26592,9254,21166,21252,11024,4301,8043,10829,5829,5830,9650,10876,23928,25070,17694,26571,4205,4206,7379,8315,8482,9378,10111,10112,10548,10906,10984,12441,21137,23647,24814,25263,10688,11806,11831,13389,13390,13391,19419,20425,24468,6022,6796,7726,7727,19858,26267,26279,11941,11942,11943,11944,12273,12274,12275,12276,13507,13508,19502,19507,21253,21256,24031,24032,24033,24034,24035,24036,24384,10887,11551,13182,25200,10368,10481,10482,25468,25469,25548,25549,25550,25551,25552,25553,25554,25555,25556,25557,25558,25559,25560,25561,25562,25724,25732,25736,25737,25738,8567,8568,8569,13892,5678,9336,9676,10778,11934,11935,12609,20954,20975,21001,21002,21003,21004,21005,21007,21009,21013,21014,21015,21016,21017,21092,21106,21108,21109,21111,24764,24765,24766,26075,5694,9301,9302,9303,10081,10082,11017,11026,19482,20489,23942,23953,23954,8898,10941,6507,24798,13005,13006,24361,24364,24366,24367,24368,24369,24370,24371,25841,25842,7058,10991,10992,10993,10994,10995,10996,10997,10999,11000,12406,12981,12982,12983,12984,12985,12986,12987,12988,13151,13152,13153,13154,13155,13156,13157,13158,13159,13814,23664,24422,24969,24970,24971,24972,24973,24974,24975,10326,10327,10328,10329,10330,10331,10332,10334,10335,10336,10337,10339,10340,10341,10342,10343,10345,10346,10860,11240,11241,18920,18922,19742,19865,19866,20871,24663,24664,24665,26189,14072,14073,15087,16533,18031,18215,18225,18996,19816,19818,19902,26621,26630,26673,26676,26689,26690,26757,26758,26759,16493,19794,19796,19830)
ORDER BY position DESC LIMIT 10;

Queste query vanno assolutamente ottimizzate.
In alternativa vanno disattivati uno ad uno i moduli aggiuntivi di Prestashop per verificare quali tra essi possa appesantire le query.


Aggiungo inoltre che sono presenti errori a livello di codice specialmente per quel che riguarda il duplice reinoltro degli headers delle pagine:

[root@sa2 multishop]# tail -f adminpuntorigenera/error_log
[07-Nov-2016 09:35:32 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/modules/ekomiintegration/ekomiintegration.php:290) in /home/simciti7/public_html/multishop/classes/Tools.php on line 247
[07-Nov-2016 11:43:56 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/modules/psmregconfirmation/psmregconfirmation.php:233) in /home/simciti7/public_html/multishop/classes/Tools.php on line 247
[07-Nov-2016 15:27:23 Europe/Rome] PHP Warning: file_put_contents(/var/www/html/prestaLog): failed to open stream: Permission denied in /home/simciti7/public_html/multishop/modules/ekomiintegration/ekomiintegration.php on line 290
[07-Nov-2016 15:27:24 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/modules/ekomiintegration/ekomiintegration.php:290) in /home/simciti7/public_html/multishop/classes/Tools.php on line 247
[07-Nov-2016 17:09:30 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/controllers/admin/AdminCartRulesController.php:118) in /home/simciti7/public_html/multishop/classes/controller/AdminController.php on line 1761
[07-Nov-2016 17:09:30 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/controllers/admin/AdminCartRulesController.php:118) in /home/simciti7/public_html/multishop/classes/controller/AdminController.php on line 1761
[07-Nov-2016 17:09:48 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/controllers/admin/AdminCartRulesController.php:118) in /home/simciti7/public_html/multishop/classes/controller/AdminController.php on line 1761
[07-Nov-2016 17:09:48 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/controllers/admin/AdminCartRulesController.php:118) in /home/simciti7/public_html/multishop/classes/controller/AdminController.php on line 1761
[07-Nov-2016 17:10:05 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/controllers/admin/AdminCartRulesController.php:118) in /home/simciti7/public_html/multishop/classes/controller/AdminController.php on line 1761
[07-Nov-2016 17:10:05 Europe/Rome] PHP Warning: Cannot modify header information - headers already sent by (output started at /home/simciti7/public_html/multishop/controllers/admin/AdminCartRulesController.php:118) in /home/simciti7/public_html/multishop/classes/controller/AdminController.php on line 1761
 

PHP utilizzato:

# php -v
ea-php-cli Copyright 2016 cPanel, Inc.
PHP 5.6.27 (cli) (built: Oct 17 2016 16:37:40)
Copyright © 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright © 1998-2016 Zend Technologies
with the ionCube PHP Loader v4.7.5, Copyright © 2002-2014, by ionCube Ltd., and
with Zend Guard Loader v3.3, Copyright © 1998-2014, by Zend Technologies
with Zend OPcache v7.0.4-dev, Copyright © 1999-2015, by Zend Technologies

# mysql -v
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3222301
Server version: 10.0.27-MariaDB-cll-lve MariaDB Server

Copyright © 2000, 2016, Oracle, MariaDB Corporation Ab and others."

Edited November 14, 2016 by demux (see edit history)