На данный момент выяснили вот что ( Я пока не знаю точно связано ли это между собой ):
1. в списке висящих запросов mysql висит куча поиска:
| 2483078 | okidokipornc_scj | localhost | okidokipornc_scj | Query | 85 | Waiting for table level lock | INSERT INTO rot_search_queries
SET search_query = 'black j',
search_query_limit = 'zh',
hits = '1', date = NOW()
ON DUPLICATE KEY UPDATE hits = hits + 1
| 2484057 | okidokipornc_scj | localhost | okidokipornc_scj | Query | 72 | Waiting for table level lock | INSERT INTO rot_search_queries
SET search_query = 'real mother',
search_query_limit = 'en',
hits = '1', date = NOW()
ON DUPLICATE KEY UPDATE hits = hits + 1
14 таких запросов одновременно и это при том что они выполняются довольно быстро.
причем я вижу в логах кучу сообщений типа:
2018-11-21 06:05:26 : Mysql error 1064: offset out of bounds (offset=198120000, max_matches=20000) (SELECT id FROM `okidokiporn_index`, `delta` WHERE match ('@(alt,description,tags,group_names) "beautiful and temperament classmate mom"/1') AND sponsor_id NOT IN (4,2) AND id NOT IN (192626,2839,162302,17482,573336,240331,161477,2825,810318,2830,1052,186495,1114,7790,343750,2577,161554,171947,192387,839408,5971,76136,503,200714,3751,167819,2839,164543,169228,176039,23011,180334,6747,469869,161356,161423) LIMIT 198120000, 120 OPTION max_matches=20000, ranker = PROXIMITY)
это, как я понимаю, означает что искали полуторамиллионную страницу по этому поисковому запросу
сфинкс выдает максимум 20к результатов поиска (167 страниц по сути как я понимаю), с какого перепуга кто-то постоянно дергает страницы результата с номерами под миллион мне непонятно
2. я попытался профилировать один из частых запросов смарта (взял прямо с processlist):
7.72648475 | 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_stats17 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id JOIN rot_gallery_data1 as gd on gd.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.tested_thumb = 'no' and gi.sponsor_id NOT IN (4,2) and gs.group_id = 0 AND gs.gallery_id NOT IN ('166398','387134','10836','7025','234699','1711','321859','318185','1472','169146','169146','387134','5127','309545','280035','169149','174598','102264','1875','91040','1711','6983','616','270344','669016','202800','695429','233829','3400','102264','1710','328681','622151','97729','19206','239038','513959','298786','519369','723066','492275','674988','911963','854610','821558','791555','59672','791831','595240','851039','856434','935050','875228','1087544','655692','857037','127369','854783','833427') ORDER BY gs.total_ctr DESC LIMIT 0, 30;
почти 8 секунд
show profile for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000027 |
| Waiting for query cache lock | 0.000003 |
| Waiting on query cache mutex | 0.000002 |
| init | 0.000002 |
| checking query cache for query | 0.000265 |
| checking permissions | 0.000004 |
| checking permissions | 0.000004 |
| checking permissions | 0.000002 |
| checking permissions | 0.000006 |
| Opening tables | 0.000063 |
| Waiting for table metadata loc | 0.446306 |
| Opening tables | 0.000582 |
| init | 0.000165 |
| System lock | 0.000022 |
| Waiting for query cache lock | 0.000003 |
| Waiting on query cache mutex | 0.000411 |
| System lock | 0.000064 |
| optimizing | 0.000057 |
| statistics | 0.008669 |
| preparing | 0.000037 |
| Creating tmp table | 0.000129 |
| Sorting result | 0.000005 |
| executing | 0.000002 |
| Sending data | 6.917617 |
| Creating sort index | 0.331635 |
| end | 0.000007 |
| removing tmp table | 0.020209 |
| end | 0.000023 |
| query end | 0.000037 |
| closing tables | 0.000058 |
| freeing items | 0.000050 |
| logging slow query | 0.000002 |
| logging slow query | 0.000003 |
| cleaning up | 0.000021 |
+--------------------------------+----------+
как видно основное время ушло на Sending data | 6.917617 |
судя по документации на mysql
https://dev.mysql.com/doc/refman/5.7/en ... tates.html :
Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
т.е. по сути у нас все еще проблема со скоростью чтения с диска получается?