Jump to content

slow queries

(\)icholas
 Share

Recommended Posts

(\)icholas Newbie

(\)icholas

Posted
Posted

Recently my website has been having performance issues that seem to be related to the number of products my website has. I'm in version 1.4.6.2 and I have about 9k products. For MySQL this should be a very manageable set of data. But my mysql_slow_querys log is bloating. The host is throttling my account which can only further decrease performance.

 

Upon checking my the log I find queries such as these:

 

Query_time: 2.127586  Lock_time: 0.001325 Rows_sent: 1  Rows_examined: 98544

SELECT SUM(pv.`counter`) AS total_viewed
 FROM `ps_page_viewed` pv
 LEFT JOIN `ps_date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
 LEFT JOIN `ps_page` p ON pv.`id_page` = p.`id_page`
 LEFT JOIN `ps_page_type` pt ON pt.`id_page_type` = p.`id_page_type`
 WHERE pt.`name` = 'product.php'
 AND dr.`time_start` BETWEEN '2012-05-01 00:00:00' AND '2012-05-31 23:59:59'
 AND dr.`time_end` BETWEEN '2012-05-01 00:00:00' AND '2012-05-31 23:59:59' LIMIT 1

# Query_time: 3.057481  Lock_time: 0.000199 Rows_sent: 2  Rows_examined: 83687
SELECT c.id_guest, c.ip_address, c.date_add, c.http_referer, pt.name as page
 FROM `ps_connections` c
 LEFT JOIN `ps_connections_page` cp ON c.id_connections = cp.id_connections
 LEFT JOIN `ps_page` p ON p.id_page = cp.id_page
 LEFT JOIN `ps_page_type` pt ON p.id_page_type = pt.id_page_type
 INNER JOIN `ps_guest` g ON c.id_guest = g.id_guest
 WHERE cp.`time_end` IS NULL
 AND (g.id_customer IS NULL OR g.id_customer = 0)
 AND TIME_TO_SEC(TIMEDIFF(NOW(), cp.`time_start`)) < 900
 GROUP BY c.id_connections
 ORDER BY c.date_add DESC

# Query_time: 1.451033  Lock_time: 0.000231 Rows_sent: 5  Rows_examined: 398524
SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , m.name, psi.price_min, psi.price_max
 FROM `ps_category_product` cp
 INNER JOIN  `ps_category` c ON (c.id_category = cp.id_category)
 INNER JOIN ps_product p ON (p.id_product = cp.id_product AND p.active = 1)
 INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
 INNER JOIN `ps_layered_price_index` psi
 ON (psi.id_product = p.id_product AND psi.id_currency = 2)
WHERE
 c.nleft >= 9
 AND c.nright <= 10
 AND c.active = 1  AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 4143 OR fp.`id_feature_value` = 4142)
GROUP BY p.id_manufacturer

# Query_time: 1.069892  Lock_time: 0.000837 Rows_sent: 10  Rows_examined: 12198
SELECT p.id_product, p.reference, p.on_sale, p.out_of_stock, p.available_for_order, p.quantity, p.minimal_quantity, p.id_category_default, p.customizable, p.show_price, p.`weight`,
 p.ean13, pl.available_later, pl.description_short, pl.link_rewrite, pl.name, i.id_image, il.legend,  m.name manufacturer_name, p.condition, p.id_manufacturer,
 DATEDIFF(p.`date_add`,
 DATE_SUB(NOW(), INTERVAL 90 DAY)) > 0 AS new
 FROM `ps_category_product` cp
 LEFT JOIN ps_category c ON (c.id_category = cp.id_category)
 LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
 LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
 LEFT JOIN ps_image i ON (i.id_product = p.id_product AND i.cover = 1)
 LEFT JOIN ps_image_lang il ON (i.id_image = il.id_image AND il.id_lang = 1)
 LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
 WHERE p.`active` = 1 AND
 c.nleft >= 2
 AND c.nright <= 37
 AND c.active = 1
 AND pl.id_lang = 1
 AND p.id_product IN (108,112,116,120,123,131,134,135,137,148,159,546,549,597,600,604,606,608,621,624,637,645,647,655,663,664,665,666,674,675,676,678,689,691,692,693,701,703,704,705,706,707,708,709,710,711,712,729,730,734,735,774,778,789,792,794,796,802,803,810,811,813,815,817,818,819,820,825,829,832,836,875,1028,1038,1076,1088,1093,1094,1095,1096,1104,1106,1111,1128,1132,1134,1135,1137,1138,1141,1167,1168,1171,1182,1189,1193,1194,1206,1209,1210,1232,1237,1243,1244,1246,1278,1292,1404,1418,1422,1430,1444,1446,1448,1450,1453,1454,1457,1458,1461,1464,1467,1468,1469,1472,1474,1475,1477,1481,1482,1484,1487,1488,1538,1540,1899,1900,1901,1902,1913,1914,1915,1916,1993,1997,1998,2897,2899,2900,2902,2903,2904,2905,2906,2907,2908,2913,2914,2918,2919,2921,2922,2925,2926,2928,2930,2935,2936,2937,2939,2940,2941,2942,2943,2945,2946,2948,2949,2950,2954,2955,2956,2957,2958,2959,2960,2961,2962,2963,2964,2965,2966,2967,2968,2969,2970,2971,2972,2973,2974,2975,2976,2977,2978,2979,2980,2981,2982,2983,2984,2985,2986,2987,2988,2989,2990,2991,2992,2993,2995,2996,2997,2998,3000,3001,3002,3003,3004,3005,3006,3007,3008,3012,3013,3015,3016,3017,3019,3020,3021,3022,3023,3024,3025,3026,3027,3028,3029,3030,3031,3032,3033,3034,3035,3037,3038,3039,3040,3041,3042,3043,3044,3045,3046,3047,3048,3049,3051,3052,3053,3054,3056,3057,3058,3059,3060,3061,3062,3064,3065,3066,3068,3069,3071,3072,3073,3074,3075,3076,3079,3080,3083,3086,3087,3092,3098,3099,3100,3101,3319,3403,3404,3405,3406,3407,3410,3411,3412,3413,3414,3415,3416,3417,3418,3419,3420,3422,3425,3426,3427,3429,3431,3432,3433,3434,3435,3436,3449,3450,3451,3453,3454,3455,3458,3460,3461,3462,3463,3465,3466,3467,3468,3469,3471,3472,3473,3474,3475,3476,3477,3478,3479,3480,3481,3482,3484,3485,3486,3487,3489,3490,3491,3493,3494,3495,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3508,3509,3510,3511,3512,3513,3514,3515,3516,3518,3519,3520,3521,3522,3523,3524,3525,3526,3527,3528,3529,3530,3531,3532,3533,3534,3535,3536,3537,3538,3539,3541,3543,3544,3549,3550,3551,3552,3560,3593,3599,3600,3601,3602,3603,3604,3605,3608,3609,3613,3614,3615,3616,3617,3618,3619,3621,3622,3623,3627,3628,3629,3630,3631,3632,3633,3635,3636,3637,3638,3639,3641,3642,3643,3644,3645,3646,3647,3648,3649,3650,3651,3652,3654,3655,3656,3657,3658,3659,3660,3661,3662,3663,3664,3665,3666,3667,3668,3669,3670,3672,3673,3674,3675,3676,3677,3880,3881,3882,3883,3884,3885,3886,3891,3892,3895,3896,3897,3899,3900,3901,3902,3903,3904,3905,3906,3907,3908,3909,3910,3911,3912,3913,4228,4229,4235,4238,4240,4243,4246,4247,4248,4249,4250,4251,4252,4279,4339,4340,4343,4344,4346,4347,4348,4349,4351,4352,4353,4507,4550,4551,4552,4553,4554,4556,4558,4559,4580,4582,4611,4796,4797,4808,4810,4812,4814,4815,4826,4827,4829,4830,4834,4835,4836,4838,4839,4844,4846,4848,4850,4851,4852,4853,4856,4857,4858,4859,4860,4861,4862,4863,4864,4865,4866,4869,4871,4872,4874,4876,4877,4878,4879,4880,4881,4882,4883,4884,4885,4887,4888,4889,4890,4891,4892,4893,4894,4895,4896,4897,4898,4900,4901,4902,4903,4904,4908,4909,4910,4911,4916,4919,4921,4931,4933,4934,4937,4939,4941,4942,4943,4945,4947,4949,4950,4951,4952,4959,4960,4964,4965,4968,4969,4975,4976,4977,4978,4979,4984,4988,4991,4994,4995,4996,4997,4998,4999,5004,5005,5006,5008,5009,5013,5014,5015,5016,5017,5018,5019,5020,5021,5022,5023,5024,5025,5026,5027,5028,5031,5032,5033,5034,5035,5036,5037,5038,5040,5041,5043,5044,5045,5046,5047,5048,5050,5052,5054,5055,5056,5057,5058,5059,5060,5061,5062,5063,5064,5065,5066,5067,5069,5070,5071,5072,5073,5074,5075,5076,5077,5377,5383,5385,5400,5480,5483,5575,5649,5650,5651,5652,5653,5654,5655,5656,5659,5661,5662,5664,5665,5666,5668,5669,5670,5671,5672,5675,5677,5678,5679,5680,5681,5682,5683,5684,5702,5703,5704,5705,5706,5707,5708,5711,5712,5713,5916,5917,5919,5920,5921,5930,5931,5932,5935,5940,6040,6043,6044,6046,6047,6048,6049,6050,6051,6052,6053,6054,6056,6058,6059,6060,6071,6072,6073,6076,6079,6080,6188,6191,6192,6201,6348,6352,6355,6356,6358,6359,6360,6364,6365,6366,6367,6368,6370,6371,6374,6375,6376,6377,6378,6379,6430,6431,6432,6433,6434,6435,6436,6437,6438,6439,6442,6443,6446,6448,6449,6450,6522,6524,6530,6557,6558,6559,6560,6561,6562,6564,6631,6648,6649,6654,6655,6656,6657,6658,6659,6660,6661,6662,6663,6664,6665,6666,6670,6671,6672,6673,6675,6676,6677,6678,6682,6685,6688,6689,6690,6691,6694,6695,6696,6697,6699,6700,6701,6702,6703,6704,6706,6707,6708,6709,6710,6718,6719,6920,6944,7096,7098,7100,7101,7102,7103,7104,7106,7107,7109,7110,7111,7112,7149,7150,7152,7153,7154,7155,7157,7159,7160,7161,7165,7166,7168,7169,7170,7171,7172,7174,7176,7179,7180,7181,7182,7185,7186,7188,7189,7190,7267,7268,7269,7270,7272,7303,7306,7309,7310,7311,7312,7315,7317,7320,7321,7322,7323,7324,7336,7337,7344,7485,7486,7487,7488,7489,7490,7491,7492,7493,7494,7495,7496,7497,7498,7499,7500,7501,7502,7503,7504,7505,7506,7507,7508,7522,7523,7524,7526,7530,7531,7532,7534,7535,7537,7538,7540,7542,7543,7544,7545,7546,7548,7549,7551,7552,7553,7555,7556,7558,7559,7560,7561,7562,7563,7564,7565,7566,7567,7568,7569,7570,7571,7572,7574,7575,7576,7578,7579,7580,7581,7583,7589,7590,7591,7592,7593,7594,7595,7596,7601,7602,7603,7604,7605,7606,7607,7608,7645,7646,7691,7692,7693,7694,7695,7696,7697,7698,7708,7710,7711,7712,7717,7718,7720,7728,7731,7781,7782,7787,7789,7791,7792,7793,7795,7796,7797,7800,7801,7803,7804,7806,7807,7808,7820,7821,7840,7841,7842,7843,7847,7849,7850,7851,7852,7854,7855,7856,7857,7858,7859,7860,7861,7862,7863,7864,7865,7868,7871,7885,7886,7887,7896,7897,7905,7906,7939,7944,7946,7947,7995,7996,7997,7998,8024,8027,8029,8030,8031,8032,8044,8292,8321,8333,8334,8336,8338,8339,8340,8361,8429,8432,8433,8434,8449,8452,8453,8478,8481,8484,8514,8518,8519,8522,8558,8559,8560,8569,8570,8571,8572,8575,8587,8621,8666,8667,8668,8669,8670,8671,8672,8673,8674,8675,8676,8677,8678,8679,8680,8681,8682,8683,8684,8685,8686,8687,8688,8689,8690,8691,8693,8697,8698,8699,8709,8769,8770,8771,8772,8773,8774,8775,8777,8779,8794,8850,8883,8884,8885,8886,8887,8888,8891,8902,8926,8927,8967,8985,9028,9029,9030,9051,9052,9054,9065,9082) GROUP BY p.id_product ORDER BY p.price desc LIMIT 0,10

# Query_time: 1.142562  Lock_time: 0.000309 Rows_sent: 10  Rows_examined: 7542
SELECT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 90 DAY)) > 0 AS new,
 (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
       FROM `ps_category_product` cp
       LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
       LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
       LEFT JOIN `ps_category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1)
       LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1)
       LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
       LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1)
       LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = 21
 AND tr.`id_state` = 0)
 LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
       LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 1)
       LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
       WHERE cp.`id_category` = 73 AND p.`active` = 1
 ORDER BY `orderprice` desc
 LIMIT 0,10

 

I'm wondering if I should blame queries such as these or if there's something else that I should be looking into? The problem began around the time I attempted to move my images to the quicker file system via the option in the back office. I've since set it back to use the older style but that hasn't seemed to help anything. The vast majority of the slow scripts were coming from the block_layered_module. I uninstalled that and it seemed to help a little but not for very long. I know other people were having issues with their store when getting to around 5k items. Is there anyone out there thats using 1.4 with more items then this?

 

Now even seemingly harmless queries are occasionally being logged in mysql_slow_queries. such as:

# Query_time: 2.388693  Lock_time: 0.000125 Rows_sent: 2  Rows_examined: 2
SHOW DATABASES

 

Is this happening because other slower queries are locking out normal ones? I'm also receiving the error that I've exceeded the 'max_user_connections' for mysql. Every once and a while the application gets stuck on a page load because mysql wouldn't let me connect. Could this also be a symptom of slow queries blocking mysql connections? According to my host I have 15 available to my account.

 

It would be great to know if anyone is successfully using this software with a fairly sizable webstore, so i might be given hope that there is something i could fix without having to do serious modifications to prestashop. Thanks.

Link to comment
Share on other sites
 Share
Go to topic listing
×
×
  • Create New...