Как бороться с нагрузкой на MYSQL?

Здравствуйте.
Начну с того, что есть сайт с посещаемостью до 20к людей.
Периодически выстраивается большая очередь запросов к БД и тут сайт стает недоступный.
Запросов в секунду до 9, онлайн людей до 90.
Крутится сайт на выделенном сервере Intel® Core™ i7-4770 Quadcore, 2 x 2 TB 6 Gb/s SATA 7200, 16 GB DDR3.

Установил я XCache, думал, что он поможет, но результата особого не дал.
Движок стоит версии 1.0.3 с шаблоном synio
Из плагинов включен только mobiletpl

На данный момент сайт грузится быстро.
MySql
query: 20
time: 0,605
Cache
query: 85
— set: 20
— get: 65
time: 0,0006
PHP
time load modules: 0,039
full time: 0,707

В настройках сервера установилено одновременных соединений к MySQL 150, но заметил, что когда нагрузка превышает 30 подключений, то дальше подключений стает все больше и больше.

Как можно облегчить нагрузку на БД?
Размер БД 4ГБ

Сейчас повалили запросы:
Query SELECT tt.topic_tag_text, count(tt.topic_tag_text) as count FROM prefix_topic_tag as tt, prefix_blog as
Сервер снова начал умирать.

В логи SQL много таких запросов:

[2015-03-12 19:10:34][15577][4684][DEBUG][
SELECT
tt.topic_tag_text,
count(tt.topic_tag_text) as count
FROM
prefix_topic_tag as tt,
prefix_blog as b
WHERE
1 = 1
AND
tt.blog_id = b.blog_id
AND
b.blog_type <> 'close'
GROUP BY
tt.topic_tag_text
ORDER BY
count desc
LIMIT 0, 70
]
[2015-03-12 19:10:34][16286][4793][DEBUG][SELECT b.blog_id
FROM prefix_blog as b
WHERE b.blog_type='close'
;]
[2015-03-12 19:10:34][16286][4793][DEBUG][ — 0 ms; returned 0 row(s)]
[2015-03-12 19:10:34][16286][4793][DEBUG][SELECT
comment_id
FROM
prefix_comment_online
WHERE
target_type = 'topic'
ORDER by comment_online_id desc limit 0, 20; ]
[2015-03-12 19:10:34][16286][4793][DEBUG][ — 0 ms; returned 0 row(s)]
[2015-03-12 19:10:34][16286][4793][DEBUG][
SELECT
tt.topic_tag_text,
count(tt.topic_tag_text) as count
FROM
prefix_topic_tag as tt,
prefix_blog as b
WHERE
1 = 1
AND
tt.blog_id = b.blog_id
AND
b.blog_type <> 'close'
GROUP BY
tt.topic_tag_text
ORDER BY
count desc
LIMIT 0, 70
]

15 комментариев

avatar
А кеш какой включен?
Отключите блок тегов, посмотрите, если он у вас так тормозит
avatar
Кеш включен xcache
$config['sys']['cache']['use'] = true;
$config['sys']['cache']['type'] = 'xcache';
$config['sys']['cache']['dir'] = '___path.root.server___/tmp/';
$config['sys']['cache']['prefix'] = 'livestreet_cache';
$config['sys']['cache']['directory_level'] = 1;
$config['sys']['cache']['solid'] = true;
avatar
Я сделал в настройках конфига так:
$config['block']['tags']['tags_count'] = 0;
$config['block']['tags']['personal_tags_count'] = 0;
Все равно создается очередь этих запросов с тегами…
avatar
блок нужно отключить совсем из сайдбара
avatar
а как это сделать?
avatar
извините, разобрался, теперь буду ждать момента когда будет нагрузка.
У меня в таблице topic_tag_text больше миллиона записей, это и есть причина?
avatar
не знаю, вы же сами привели тяжелый запрос, значит с этим разобрались?
avatar
Я имею виду или миллион записей может быть причиной нагрузки на MYSQL?
avatar
Да, столкнулся с тем же. Пришлось убрать «Похожие записи» и переписал немного запрос. По памяти, там идёт обединение с блогами, для проверки закрытый/незакрытый, я вписал 3 закрытых блога прямо кодами в запрос, что бы он их игнорировал.
avatar
Если нет индексов или запрос как-то неправильно оптимизируется, то вполне возможно. Может причина и в другом, логгируйте все запросы к бд и ищите топ самых медленных в логе.
avatar
Спасибо, отключил теги и все нагрузки прошли на ура. Не было очередей вообще.
avatar
Странно, я лично уже второй раз вижу что тормозит блок тегов.
Видимо, нужно запрос построения облака поделить на два: список ид открытых блогов и подставлять его в запрос по тегам.
avatar
Файл classes\modules\topic\mapper\Topic.mapper.class.php

Функция: public function GetOpenTopicTags($iLimit)

Так было:
$sql = "
			SELECT
				tt.topic_tag_text,
				count(tt.topic_tag_text)	as count
			FROM
				".Config::Get('db.table.topic_tag')." as tt,
				".Config::Get('db.table.blog')." as b
			WHERE
				tt.blog_id = b.blog_id
				AND
				b.blog_type IN ('open','personal')
			GROUP BY
				tt.topic_tag_text
			ORDER BY
				count desc
			LIMIT 0, ?d
				";


Так стало:
$sql = "
            SELECT 
                tt.topic_tag_text,
                count(tt.topic_tag_text)    as count         
            FROM 
                ".Config::Get('db.table.topic_tag')." as tt                
            WHERE 
                tt.blog_id NOT IN (12792, 12792, 12801, 20674, 15816, 12801, 15816, 14675, 12792, 17242)
            GROUP BY 
                tt.topic_tag_text
            ORDER BY 
                count desc        
            LIMIT 0, ?d
                ";   


Проблема нарастает очень медленно, вылавливал её долго. Когда тэгов много, возникает тупёж.
avatar
ну или подставлять кроме закрытых ид блогов, так даже лучше т.к. их среднестатистически меньше.
avatar
А как это реализовать в программном коде?
Только зарегистрированные и авторизованные пользователи могут оставлять комментарии.