Magento Frontend suddenly very slow (Backend fast) issue MySQL related

Question

I have a very strange Magento problem. We run Magento since 7 years and moved it a couple month ago to a new server. The performance was really good since then. But now suddenly since 4 days, the FRONTEND performance is very sluggish though BACKEND is normal -> fast. In the frontend, it takes up to 5 seconds until the page starts loading but then really quick. MySQL is using a lot of CPU power. Normally our server idles at around 2% CPU power but now it is due to the constant MySQL draw up to 25% of its total performance (8 Core) and never comes down since 4 days. Restarting MySQL didn't change anything. Monitoring status of MySQL in phpmyadmin doesn't show unusual high activity at any given time. There are about 10-15 users browsing the store causing normal load and a normal amount of MySQL queries.

What I do wonder is if the SQL queries that take a lot of time (3-5 sec) and come from Magento frontend are a bit strange. I captured the following with phpmyadmin: (please look further down)

Maybe somebody can shed some light since I don't know anymore where to look for the problem. Sidenote: all security patches are installed in the store and we did have some unwanted hits on our backend entry page (even though URL is disguised) from Russia. I logged those out and changed the backend URL but problems with the frontend persist. So here the SQL that is at least strange looking to me. Any help very much appreciated!

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 = '32') 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 = '32') 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 = '531') 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 = '531') 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('3', '5', '6', '214', '208', '169', '7', '153', '4', '15', '16', '210', '17', '154', '51', '52', '226', '205', '53', '203', '54', '61', '159', '22', '24', '188', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '160', '36', '39', '38', '48', '224', '225', '40', '42', '43', '44', '45', '161', '9', '49', '172', '173', '174', '175', '176', '50', '55', '56', '206', '57', '209', '62', '63', '64', '65', '158', '177', '178', '179', '180', '181', '182', '183', '184', '185', '207', '67', '219', '220', '221', '66', '200', '73', '199', '198', '75', '186', '78', '85', '84', '83', '81', '187', '82', '79', '76', '80', '86', '162', '202', '68', '69', '70', '71', '72', '215', '227', '157', '74', '191', '197', '171', '189', '190', '192', '193', '194', '195', '10', '88', '87', '201', '89', '90', '91', '93', '92', '217', '94', '95', '96', '97', '98', '163', '99', '100', '101', '11', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '164', '12', '114', '115', '116', '117', '211', '212', '118', '119', '120', '124', '121', '122', '123', '165', '14', '125', '126', '128', '166', '218', '13', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '167', '140', '170', '168', '216', '204', '46')) AND (e.entity_id NOT IN('7', '45', '48', '72', '73', '76', '79', '80', '82', '83', '84', '85', '87', '89', '89', '91', '92', '100', '110', '123', '153', '154', '157', '158', '159', '160', '161', '162', '164', '165', '166', '167', '168', '170', '171', '177', '178', '179', '180', '181', '182', '183', '184', '185', '187', '190', '192', '193', '194', '195', '201', '201', '214', '215', '218', '225', '227')) 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')

• Yiorgos Moschovitis: Did you reindex all and cleared cache? Do you use flat tables?
• Gunnar: Yes i did that but it did not change anything. I could solve the problem just now by duplicating the troubled database and run from the new copy. I don't know if mysql has some build in checks and repairs when duplicating the databases but this now did the trick for me.

Answer 1

What solved the problem for me was just duplicating the somehow corrupted database and running from that copy. All speed issues are gone. I can't say what the culprit was in the end but it works again. If that would not have solved the problem i would have used the magento database repair tool.