Конфиг и настройка сервера под большую ( 200+ гиг ) базу смарта

[admin]

добавление\изменение тагов галеры

но технически это оч быстрый запрос, то что он был 4 сек это значит что в этот момент сервак был чем-то очень занят

в такой момент самый простой запрос может быть длинным, это не показатель

[tgptraffic_biz]

Понял.
А количество тагов на сайте влияет на загрузку mysql ? Скажем, у меня сейчас 1500 активных и 50000 wait. Еcли я удалю их, что-то поменяется ?
И тот же вопрос по группам - количество групп влияет на загрузку mysql ?

[admin]

Логично что чем больше - тем больше нагрузка при прочих равных
Но если вы не делаете выборку по этим тагам то их может быть хоть миллион - нагрузки не будет

[tgptraffic_biz]

а выборка - это любой вывод тагов на сайте из облака тагов, то есть использование Тага tag_cloud ?
И вопрос по выборкам из группы : это ресурсоемкий запрос ?
Скажем, я вывожу список групп под каждой тумбой :
<group_list num=1-8 separator=", "><a href="/<!--GROUP_NAME-->/" target="_blank"><!--GROUP_CUSTOM_NAME-->"</a></group_list>
У меня 1500 активных групп. если я уменьшу до 300 групп, то нагрузка пропорционально упадет ?

[admin]

В данный момент вы пытаетесь методом тыка оптимизировать что-то на серваке
Вы ж машину не чините методом "а что если заменить это? а если это? "
так это не работает
вам надо разобраться что именно создает нагрузку, в какие моменты и тп

[tgptraffic_biz]

Мы 4 дня этим занимаемся : программист, я, команда админов, единого понимания нет. На данный момент проблема выглядит как : "innodb_buffer_pool_size - туда должен влезать весь датафайл (таблицы в InnoDB)"
У нас не влезает. Я жду отдельного сервера под mysql, параллельно уменьшаю базу галер.

[admin]

Как это отвечает на вопрос "что именно нагружено" ?

[tgptraffic_biz]

На данный момент выяснили вот что ( Я пока не знаю точно связано ли это между собой ):

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.


т.е. по сути у нас все еще проблема со скоростью чтения с диска получается?

[admin]

Да

так что нагружено, насколько , как вы это проверяли и тп?

[tgptraffic_biz]

В том-то и дело что по МРТГ все выглядит хорошо : LA 1000, проц загружен на 25%, винты не выглядят загруженными
http://prntscr.com/ll8zy9
Но вот вы видите, запрос тормозит из-за медленного чтения с диска.