Переезжать на хостинг? Медленные запросы mysql

[kak2z]

Недавно переехал на ВПС 2гига памяти, 2ядра. Вроде и недорого и быстро все шуршит. И винты у хостера с ssd кешем, до этого был на хостинге. Там бывало что тормозил сервер их. Бывало что ддосили вроде. В общем все как обычно у хостеров. Ничего сверх плохого - но хотелось стабильности. База как я и говорил ранее больше 4х гиг.
Перевел часть таблиц по совету Диггера в innoDB.

Но частенько в этой базе бывают затыки. Вот к примеру вот такие вот:

Код Выделить Развернуть

# Time: 151206  5:24:24
# User@Host: *****mysql[*****mysql] @ localhost []
# Thread_id: 24727  Schema: ********  QC_hit: No
# Query_time: 1450.887022  Lock_time: 0.000077  Rows_sent: 100  Rows_examined: 455116
# Rows_affected: 0
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: No  Merge_pantispam by SMFRC: 1  Priority_queue: No
use ********;
SET timestamp=1449375864;
SELECT t.id_topic, t.num_replies, t.num_views, t.id_board,
                m.subject, IFNULL(mem.real_name, m.poster_name) as poster, b.name,
                m.poster_time as first_time, mes.poster_time
                FROM smf_topics as t
                        INNER JOIN smf_messages as m ON (m.id_msg = t.id_first_msg)
                        INNER JOIN smf_boards as b ON (b.id_board = t.id_board)
                        INNER JOIN smf_messages as mes ON (mes.id_msg = t.id_last_msg)
                        LEFT JOIN smf_members as mem ON (mem.id_member = t.id_member_started)
                WHERE (FIND_IN_SET(-1, b.member_groups) != 0)
                ORDER BY t.id_topic DESC
                LIMIT 74300, 100;
или такие

Код Выделить Развернуть

# Time: 151206  8:06:29
# User@Host: ***mysql[***mysql] @ localhost []
# Thread_id: 36878  Schema: alsiti  QC_hit: No
# Query_time: 119.592722  Lock_time: 0.000143  Rows_sent: 0  Rows_examined: 188485
# Rows_affected: 25
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_pantispam by SMFRC: 0  Priority_queue: No
use alsiti;
SET timestamp=1449385589;
INSERT IGNORE INTO smf_log_search_results
                                                        (id_search, relevance, id_topic, id_msg, num_matches)
                                                SELECT
                                                        242,
                                                        1000 * (30 * COUNT(*) / (MAX(t.num_replies) + 1) + 25 * CASE WHEN MAX(m.id_msg) < 136336 THEN 0 ELSE (MAX(m.id_m
sg) - 136336) / 58431 END + 20 * CASE WHEN MAX(t.num_replies) < 200 THEN MAX(t.num_replies) / 200 ELSE 1 END + 15 * CASE WHEN MAX(lst.id_topic) IS NULL THEN 0 ELSE 1 EN
D + 10 * CASE WHEN MIN(m.id_msg) = MAX(t.id_first_msg) THEN 1 ELSE 0 END + 0 * MAX(t.is_sticky)) / 100 AS relevance,
                                                        t.id_topic,
                                                        MAX(m.id_msg) AS id_msg,
                                                        COUNT(*) AS num_matches
                                                FROM smf_topics AS t
                                                        INNER JOIN smf_messages AS m ON (m.id_topic = t.id_topic)
                                                        LEFT JOIN smf_tmp_log_search_topics AS lst ON (lst.id_topic = t.id_topic)
                                                WHERE m.body LIKE '%шансон%'
                                                        AND m.id_board IN (1, 73, 75, 48, 78, 4, 5, 7, 11, 13, 80, 65, 66, 67, 68, 77, 79, 81, 85, 74, 54, 14, 15, 52, 5
1, 56, 41, 8, 9, 42, 59, 64, 43, 55, 71, 72, 39, 40, 16, 83, 17, 50, 18, 19, 20, 21, 84, 22, 23, 24, 49, 25, 88, 26, 47, 35, 61, 89, 27, 90, 69, 28, 87, 29, 30, 31, 82,
 70, 60, 32, 33, 53, 34, 36, 86, 62, 57, 91, 93, 94, 95, 98, 99, 100, 101, 102, 103, 104, 105, 107, 108, 109, 110)
                                                GROUP BY t.id_topic
                                                LIMIT 1200;

понятное изза этого весь сайт вырубается.. и к нему нет доступа долго время...
что делать посоветуете? возвращаться на хостинг? впс не тянет??
я кстати думал что может у них такие ВПСы... брал другой у другого хостера.. там еще хуже было..
буферы в my.conf увеличил... все что в плане оптимизации конфига рекомендуют вроде сделал..


ошибка когда база недоступна вот такая
502 Bad Gateway
nginx/1.2.1

[Mavn]

в это время сколько на серваке памяти занято/свободно
чего сам мускул в логах пишет?

[kak2z]

в это время сколько на серваке памяти занято/свободно
чего сам мускул в логах пишет?

памяти куча свободно... 70 процентов.. процессор занят на 10-12 процентов..
логи мускула мне ничего не сказали)) куча текста как то даже не упорядоченного.. я не понял что там над увидеть)) просто лог запросов..

[Mavn]

памяти куча свободно... 70 процентов.. процессор занят на 10-12 процентов..
логи мускула мне ничего не сказали)) куча текста как то даже не упорядоченного.. я не понял что там над увидеть)) просто лог запросов..

на мыло кинь глянем может чего интересного... а у тебя получается что эти запросы плохо отрабатываются или что просто не совсем понимаю, чего не так

[Жека]

Вообще очень странно, что первый запрос выполнялся 1450/60 = 24 минуты.
Обычный запрос: 3 внутренних соединения + 1 левое + отбор + сортировка.
Скорее всего, из-за этого:
Full_scan: Yes
Посмотрите в соединяемых таблицах: везде есть индексы по полям, указанных в секциях ON запроса?

[disa]

Вообще очень странно, что первый запрос выполнялся 1450/60 = 24 минуты.
Обычный запрос: 3 внутренних соединения + 1 левое + отбор + сортировка.
Скорее всего, из-за этого:
Full_scan: Yes
Посмотрите в соединяемых таблицах: везде есть индексы по полям, указанных в секциях ON запроса?

понятно дело от фулл скана надо избавляться. но 24 минуты - борщ... Автор, сколько записей в таблицах? Навскидку - делай индексы нужные.
И каким бы ни был один тормозной запрос, для других БД должна быть доступна. Посмотри, не лочатся ли таблицы во время таких запросов?

[alexnikolaychenko]

Может не совсем в тему скажу, но считаю, что в таком случае нужно бежать на выделенный сервер. Да, дороже, но и скорости совсем другие, и настройка позволяет извращаться.