Re: MySQL 5.5 Queries occasionally extremely slow

[Marc Host]

I also just noticed that the last time this happened, there was another query that was taking a long time as well. It was in "Copying to tmp table" for 8 minutes. Just to be clear the query mentioned above was running as well (it was already running for 20 minutes). I just wanted to add that another query ended up going very slow during this problem.

Here is this second query:

SELECT Projects.*, lds.listing_id, listing.*, published.tour_uri as vt_uri, Projects.MLSNumber AS id_mls, listing.listing_type as listing_type, listing.pantry AS lp, listing.air_conditioning AS lac, listing.utility_room AS lur, listing.swimming_pool AS lsp, listing.fireplace AS lf, listing.deck_patio AS ldp, listing.waterfront AS lw, listing.golf_course AS lgc, listing.public_transit AS lpt, listing.acreage AS la, listing.commercial AS lc, listing.views AS lv, store.hash_key AS hash, resize.hash_key as resize_hash_key, GROUP_CONCAT(image_name ORDER BY source.sort_order ASC) as images_list FROM devdb001.Projects LEFT JOIN image.image_store AS store ON (store.owner_key = ProjectID AND store.owner_type='project' AND store.image_type = 'photo') LEFT JOIN image.resize_request AS resize ON (resize.store_key = store.hash_key AND resize.request_width = 90) LEFT JOIN image.source_image AS source ON (source.store_key = store.hash_key) LEFT JOIN real_estate.listing_data_source AS lds ON lds.id_data_source = ProjectID AND lds.data_source_id = 1 LEFT JOIN real_estate.listing AS listing ON lds.listing_id = listing.id LEFT JOIN virtualtour.published_tours AS published ON Projects.ProjectID = published.projectid WHERE latitude != '0.000000' AND activedate >= inactivedate Group By Projects.ProjectID ORDER BY Projects.ProjectID ASC, source.sort_order ASC LIMIT 30000

This query is also very fast when I run in manually at other times. It took only a few seconds to run it just now.

Here is this query's EXPLAIN:
+----+-------------+-----------+--------+-------------------------------+----------------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+-------------------------------+----------------+---------+-----------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | Projects | ALL | NULL | NULL | NULL | NULL | 92674 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | store | eq_ref | PRIMARY,owner_key | PRIMARY | 199 | const,devdb001.Projects.ProjectID,const | 1 | |
| 1 | SIMPLE | resize | ref | PRIMARY | PRIMARY | 98 | image.store.hash_key,const | 1 | |
| 1 | SIMPLE | source | ref | PRIMARY,store_key | PRIMARY | 96 | image.store.hash_key | 12 | |
| 1 | SIMPLE | lds | ref | data_source_id,id_data_source | data_source_id | 6 | const,devdb001.Projects.ProjectID | 1 | |
| 1 | SIMPLE | listing | eq_ref | PRIMARY | PRIMARY | 4 | real_estate.lds.listing_id | 1 | |
| 1 | SIMPLE | published | ref | projectid | projectid | 3 | devdb001.Projects.ProjectID | 1 | |
+----+-------------+-----------+--------+-------------------------------+----------------+---------+-----------------------------------------+-------+----------------------------------------------+

After this query started, there started to be many more queries "Waiting for table level lock" (even for completed unrelated databases).


The output of all the system stats commands (like pidstat, iostat, etc...) were all still the same. One CPU 100% load and no IO wait.