1

I've just moved a Magento 1.9 site using a tarball and a mysqldump of the old site to a new server having the same hardware specs as the old one, followed all instructions I could find online, saw that everything seemed to be working well and found no problems logging into and using the admin backend.

However the front-end became painfully slow (it takes 1 minute to load a complete page) so I did a ‘SHOW FULL PROCESSLIST’ on MySQL and found out that it was stuck on this query:

SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '42') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '42') AND (`at_is_active`.`store_id` = 1)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu_default`.`attribute_id` = '67') AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu`.`attribute_id` = '67') AND (`at_include_in_menu`.`store_id` = 1)
 LEFT JOIN `core_url_rewrite` ON (core_url_rewrite.category_id=e.entity_id) AND (core_url_rewrite.is_system=1 AND core_url_rewrite.store_id='1' AND core_url_rewrite.id_path LIKE 'category/%') WHERE (`e`.`entity_type_id` = '3') AND (`e`.`entity_id` IN('210', '211', '212', '213', '214', '206', '207', '258', '259', '294', '299', '300', '203', '204', '205', '202', '3', '77', '5', '9', '10', '11', '12', '13', '6', '14', '15', '16', '17', '18', '19', '7', '20', '21', '22', '23', '24', '25', '26', '78', '181', '8', '27', '28', '29', '30', '79', '80', '81', '82', '170', '171', '172', '173', '174', '175', '176', '177', '178', '179', '180', '182', '4', '31', '209', '42', '43', '192', '193', '32', '44', '45', '46', '47', '48', '49', '50', '51', '194', '33', '34', '35', '37', '38', '39', '52', '57', '58', '59', '60', '62', '63', '64', '65', '66', '67', '53', '54', '195', '196', '55', '68', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '198', '69', '126', '127', '128', '129', '130', '131', '132', '133', '166', '167', '168', '199', '70', '134', '136', '135', '137', '138', '139', '140', '141', '142', '71', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153', '154', '155', '156', '157', '169', '184', '185', '186', '187', '158', '159', '183', '160', '188', '189', '190', '255', '256', '257', '72', '161', '162', '163', '164', '165', '200', '74', '75', '76', '201', '215', '216', '221', '223', '245', '218', '224', '225', '226', '227', '228', '229', '230', '219', '231', '232', '233', '234', '235', '236', '238', '239', '220', '241', '242', '243', '244', '249', '252', '253', '260', '261', '305', '306', '307', '308', '309', '310', '311', '312', '313', '314', '315', '316', '317', '318', '319', '320', '321', '322', '323', '324', '262', '263', '264', '265', '266', '267', '268', '269', '270', '271', '272', '274', '275', '276', '277', '278', '301', '302', '303', '304', '279', '280', '282', '283', '284', '285', '286', '287', '288', '281', '289', '290', '291', '292', '293', '295', '296', '297', '298')) AND (`e`.`entity_id` NOT IN('42', '43', '51', '68', '69', '70', '72', '74', '75', '76', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '144', '145', '146', '147', '150', '151', '152', '153', '154', '155', '156', '157', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '184', '185', '186', '187', '188', '189', '192', '198', '199', '200', '201', '202', '207', '209')) AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1')

which to my understanding is the query to build the category hierarchy(?) The catalog has 7,500 products, catalog_category_entity has 304 rows, core_url_rewrite WHERE is_system = 1 has 21,090 rows (from a total of 749,932 rows).

Yes I have cleared the cache and reindexed using php -f shell/indexer.php reindexall and from the admin backend as well but the query keeps showing up for every single click that renders the front-end. What I've noticed is that on the old site this query never shows up so everything on the front-end was snappy.

I have also tried to set ‘Use Flat Catalog Category’ to ‘Yes’ but still got a slow query only in a different form which takes almost the same amount of time to execute:

SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path`, `main_table`.`umm_dd_type`, `main_table`.`umm_dd_width`, `main_table`.`umm_dd_proportions`, `main_table`.`umm_dd_columns`, `main_table`.`umm_dd_blocks`, `main_table`.`umm_cat_target`, `main_table`.`umm_cat_label` FROM `catalog_category_flat_store_1` AS `main_table`
 LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.include_in_menu = '1') AND (main_table.is_active = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC

Has anybody ever experienced such a thing before and did you find a solution to it? Any help would be greatly appreciated.

Improve this question
2
  • Additional DB information request, please. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; H) SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Oct 25, 2022 at 15:51
  • Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Oct 25, 2022 at 15:52

2 Answers 2

Reset to default
0

I would like to compare MySql version and my.cnf config file between 2 servers, then make sure the config in the new server same as the old server.

In addition, search for "how to optimize my.cnf for MySQL performance" on google and apply the changes, that may help speed up your performance.

Improve this answer
3
  • Thanks for the suggestion. I finally downgraded MySQL to version 5.5.62 (from 5.7.39) and now the same query takes < 1 sec to process. I haven't had a closer look at the MySQL parameters set from my.cnf on v5.7.39 yet but will explore later.
    – Reza Anwar
    Oct 26, 2022 at 3:03
  • Do you mean your slow query issue is resolved?
    – Tu Van
    Oct 26, 2022 at 3:16
  • Yes. By downgrading MySQL to 5.5.62.
    – Reza Anwar
    Oct 26, 2022 at 3:18
0

set optimizer_switch='rowid_filter=off' in my.cnf. I think you can do it in the running server as well, but it looks like you've already downgraded.

Improve this answer
7
  • Tried it and got this error: Query 1 ERROR: Variable 'optimizer_switch' can't be set to the value of 'rowid_filter=off' Perhaps not supported in MySQL 5.7? optimizer_switch
    – Reza Anwar
    Oct 27, 2022 at 1:27
  • Oh, sorry, I thought that was in mysql as well as MariaDB, because it's crippled some queries for me before. Last time I moved server and reinstalled from a mysqldump and had a broken query, I ended up comparing the explain output on the old and new servers, noticed it was different, and then realised that the import had failed to populate the indexes for one of the url_rewrite table, and it was having to do a full scan on every product.
    – Richard
    Oct 27, 2022 at 6:39
  • @RezaAnwar Another way to ensure INDEXES are current, with root login, SHOW INDEX FROM table_name;
    – Wilson Hauck
    Oct 27, 2022 at 17:48
  • @Richard I dumped the database from a MySQL 5.5 server and restored to a MySQL 5.7 server so perhaps the restore failed to populate the indexes of core_url_rewrite table (more than 700K rows) as well. Tried on MySQL 8 with the same results.
    – Reza Anwar
    Nov 3, 2022 at 7:28
  • @wilson-hauck I did a SHOW INDEX FROM core_url_rewrite table and these were the results. The query starts to get really slow after a LEFT JOIN with the core_url_rewrite table.
    – Reza Anwar
    Nov 3, 2022 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.