tcms начал вешать сервер
Posted: Mon Aug 01, 2016 5:43 pm
LA 100
вот что админы написали:
В mysql очень много запросов висит на "Waiting for table metadata lock" - ожидают разблокировки таблиц. Разбираемся...
Пример долго выполняющегося запроса
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats15 as gs JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id = 0 AND gs.gallery_id IN (SELECT rot_gal2tag.gallery_id FROM rot_gal2tag WHERE rot_gal2tag.tag_id IN ('36') ) AND gs.gallery_id NOT IN ('23457','23726','26802','32888','31558','45177','44628','16758','20591','19800','16139','21320','21446','21962','18028','16904','15362','19199','19740','23656','30397','23549','29533','23244') ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 0, 160# queryitems
"выполняться" они могут по 200..300 секунд.
Вот что выдаёт по ним explain:
+----+--------------+-------------+--------+--------------------------------------------------------------+------------+---------+------------------------------+-------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+--------------------------------------------------------------+------------+---------+------------------------------+-------+----------------------------------------------------+
| 1 | SIMPLE | | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | gd | eq_ref | PRIMARY | PRIMARY | 4 | .gallery_id | 1 | NULL |
| 1 | SIMPLE | gi | eq_ref | PRIMARY,sponsor_id,gallery_type,gallery_status | PRIMARY | 4 | .gallery_id | 1 | Using where |
| 1 | SIMPLE | gs | ref | unique_ids,best_thumb,gallery_id,thumb_id,tested_thumb_index | unique_ids | 7 | .gallery_id,const | 1 | Using where |
| 1 | SIMPLE | gt | ALL | PRIMARY | NULL | NULL | NULL | 28425 | Using where; Using join buffer (Block Nested Loop) |
| 2 | MATERIALIZED | rot_gal2tag | ref | tag_id,gallery_id | tag_id | 4 | const | 8090 | NULL |
+----+--------------+-------------+--------+--------------------------------------------------------------+------------+---------+------------------------------+-------+----------------------------------------------------+
т.е. в итоге обрабатывается не менее 28425 * 8090 = 229 958 250 записей.
вот что админы написали:
В mysql очень много запросов висит на "Waiting for table metadata lock" - ожидают разблокировки таблиц. Разбираемся...
Пример долго выполняющегося запроса
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats15 as gs JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id = 0 AND gs.gallery_id IN (SELECT rot_gal2tag.gallery_id FROM rot_gal2tag WHERE rot_gal2tag.tag_id IN ('36') ) AND gs.gallery_id NOT IN ('23457','23726','26802','32888','31558','45177','44628','16758','20591','19800','16139','21320','21446','21962','18028','16904','15362','19199','19740','23656','30397','23549','29533','23244') ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 0, 160# queryitems
"выполняться" они могут по 200..300 секунд.
Вот что выдаёт по ним explain:
+----+--------------+-------------+--------+--------------------------------------------------------------+------------+---------+------------------------------+-------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+--------------------------------------------------------------+------------+---------+------------------------------+-------+----------------------------------------------------+
| 1 | SIMPLE | | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | gd | eq_ref | PRIMARY | PRIMARY | 4 | .gallery_id | 1 | NULL |
| 1 | SIMPLE | gi | eq_ref | PRIMARY,sponsor_id,gallery_type,gallery_status | PRIMARY | 4 | .gallery_id | 1 | Using where |
| 1 | SIMPLE | gs | ref | unique_ids,best_thumb,gallery_id,thumb_id,tested_thumb_index | unique_ids | 7 | .gallery_id,const | 1 | Using where |
| 1 | SIMPLE | gt | ALL | PRIMARY | NULL | NULL | NULL | 28425 | Using where; Using join buffer (Block Nested Loop) |
| 2 | MATERIALIZED | rot_gal2tag | ref | tag_id,gallery_id | tag_id | 4 | const | 8090 | NULL |
+----+--------------+-------------+--------+--------------------------------------------------------------+------------+---------+------------------------------+-------+----------------------------------------------------+
т.е. в итоге обрабатывается не менее 28425 * 8090 = 229 958 250 записей.