Почему сайт с простейшими sql запросами «валит» VPS уже при 50 активных посетителей?

Question

[Avemes]

Есть сайт с достаточно большой посещаемостью. Раньше он находился на обычном хостинге и все хорошо работало. Максимальное время отклика не превышало секунду. Но из-за роста посетителей было решено перенести его на VPS.

После этого начались ужасные вещи. Уже при 3 запросах в секунду и 50 активных юзеров время отклика сайта перевалило за 30 секунд! Писал в поддержку хостера и там мне ответили, что проблема запросах к базе (mysql), которые съедают 97% ресурсов сервера и проблема в оптимизации самого VPS. Мол их сервера для хостинга максимально оптимизированы и поэтому там такая проблема не проявлялась.

И вот я теперь думаю как же так нужно оптимизировать VPS, чтобы он хотя бы 20 пользователей держал. Смех да и только.

Вот пример тех запросов, которые выполняет сайт:

$res = mysql_query("SELECT * FROM content WHERE category = 'new' AND title LIKE '%".mysql_real_escape_string($search)."%' GROUP BY thumb ORDER BY id DESC LIMIT $p, $limit") ;

Проще некуда и эти запросы уж при 50 пользователей точно не частые.

Далее покажу графики с тестов loadimpact (от 0 до 100 посетителей). Первые 2 картинки - это графики сайта с VPS (все плохо):



А теперь покажу для сравнения графики того же сайта, но с обычного хостинга:



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

Если нужна какая-то дополнительная информация, то я ее предоставлю.

Заранее спасибо!

Comments

[Avemes]

Забыл уточнить конфигурацию VPS.

Linux CentOS 5 64bit
2400 МГц
1024мб ОЗУ.

[Opaspap]

покажите explain ваших основных запросов, включите slow query log

Answer 1

[Masterme]

Действительно. Какой-то глупый VPS. Разве может LIKE '%...' тормозить?

Answer 2

[Masterme]

А я вот раньше ездил на автобусе и за 20 минут доезжал до работы. А неделю назад купил машину, так в ней в пробки попадаю, и едет она медленно. В сервисе сказали, что машина оптимизирована для езды, должно быть всё в порядке. Вот графики моих поездок на машине
[картинка жпг]
а вот на автобусе раньше
[картинка жпг]
Пожалуйста подскажите, в чём может быть проблема.

Comments

[Masterme]

Ах да, забыл добавить, машина чёрная, 3 двери, 4 колеса.

[wibuhu]

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

Answer 3

[Eugene Mosyukov]

Проблема в LIKE '%...'. Данный запрос не использует индексы и ведет построчный поиск. Для поиска лучше использовать Sphinx или что-нибудь вроде него.

Answer 4

[Владимир Шишминцев]

Делаете вывод этого запроса

echo "SELECT SQL_NO_CACHE * FROM content WHERE category = 'new' AND title LIKE '%".mysql_real_escape_string($search)."%' GROUP BY thumb ORDER BY id DESC LIMIT $p, $limit";

Затем идете в phpMyAdmin (или что у вас там стоит) и выполняем запрос.
Добавляем перед запросом EXPLAIN и смотрим что происходит.

Comments

[Avemes]

Спасибо, попробую.

Answer 5

[Алексей Калинин]

1. Oversell
2. Медленное хранилище
3. Все таки шаред хостинг может работать быстрее чем дешевые VPS.

Answer 6

[Arman]

1. Обычный хостинг сильнее многих VPS - на момент запроса вам доступна практически вся мощь физического сервера, очень редко хостеры делают ограничения здесь
2. Запрос может быть и простым, но вы не написали кол-во данных в этой таблице 100? 10 000 000?
3. У вас одна таблица? Почему грешите именно на этот запрос? Опять же сколько в БД данных и какая структура (индексы)
4. Бывало тормозила VPSка пока не перекинули на другую железку, бывают плохие соседи и они по полной насилуют жесткий диск.

Comments

[Avemes]

Да у меня других запросов-то толком нету. Остальные примерно такие же.
Записей в базе около 40 000.
InnoDB и MyISAM (Самая большая таблица MyISAM).

Space usage
Data 7.4 MiB
Index 2 MiB
Overhead 197.5 KiB
Effective 9.2 MiB
Total 9.4 MiB

Row statistics
Format dynamic
Collation cp1251_general_ci
Rows 36,210
Row length 209 B
Row size 272 B
Next autoindex 63,855

Насчет соседей, как можно это проверить?

Answer 7

[Артур Смирнов]

Для выявления причины тормозов воспользуйтесь Slow Query Log (можно почитать тут). Когда найдете тормозящие запросы, попытайтесь их оптимизировать. Возможно имеет смысл кешировать результаты медленных запросов в памяти (memcache, redis, etc.).

И еще небольшой совет для повышения производительности работы с БД - перейдите с mysql (которая уже deprecated) на mysqli или PDO. На Mysqli перевести существующую систему не составит труда, она может работать в функциональном режиме и похожа синтаксически на mysql.

Comments

[Alex Grey]

функциональном режиме = процедурном стиле?

[Артур Смирнов]

Да, я именно это имел в виду.

Answer 8

[Алекс Белов]

1. Ну про LIKE все сказали. Это очевидно. Теперь уже очевидно.
2. Что у вас с оперативкой на VPS? Что за ОС?
3. Убейте все расширения Apache и PHP которыми не пользуйтесь. Много это не даст, скажу честно.
4. Сколько у вас запросов приходится на одного пользователя при его обращении к одной странице!? А то вдруг у вас история как у vBulletin нашпигованного модами!? По 50 запросов с одного обращения пользователя.
5. Индексы в БД везде есть!? Может дополнительные сделать!? Пусть размер БД увеличится, зато скорость может подрасти.

Больше информации сообщите публике. А то все к LIKE и будут придираться.

Answer 9

[Пума Тайланд]

это вы лайк то назвали простейшим запросом?

Answer 10

[Влад Животнев]

У вас виртуалка на openvz, скорее всего. Бегите оттуда )
Тем более, скорее всего, действительно плохо настроенная.
Приходите в жаббер, помогу поселиться на digitalocean (там kvm), расскажу как настроить. Первый месяц у do бесплатен.

Comments

[Opaspap]

обоснование с циферками кроме панического страха oversell вы можете выдать vz vs kvm ?

[Александр]

@Opaspap там SSD хранилище (для БД словно манна небесная), вкусная цена, гибкость в расширяемости/управляемости и приличная отказоустойчивость

[Влад Животнев]

@Opaspap панически боюсь системных администраторов, которые не разобрались в куче крутилок для памяти. Например, kmemsize зачем то закрутили в минимум.

Answer 11

[Eugene Mosyukov]

Попробуйте LIKE '%...%' переписать с использованием MyISAM-овского FULLTEXT. Для этого достаточно добавить FULLTEXT индекс и немного переписать запрос. По ключевой фразе "MyISAM FULLTEXT search tutorial" в нагуглится много статей.

Comments

[Александр]

Позволю себе ещё добавить, что в данном случае запрос идёт через условие с AND, что требует помимо задания FULLTEXT индекса сгенерировать ещё и составной индекс. Я правда не знаю, можно ли так сделать в MyISAM

Answer 12

[shagguboy]

этот запрос вполне может использовать индекс по category для поиска category = 'new'
другой вопрос в том, что потом идет группировка при полном отсутствии аггрегатных функций.. зачем непонятно.

Answer 13

[Александр Чекалин]

Сам факт, что "обычный хостинг" у вас держит нагрузку лучше VPS, указывает, что на VPS что-то не так. Во-первых, что за MySQL у вас стоит? Попробуйте MariaDB, потюнингуйте ее. Попробуйте выделить ей как можно меньше ОЗУ (гуглите "mysql low memory", смотрите пример my-small.cnf). Какой движок хранения вы используете, InnoDB/MyISAM? Если не Inno, попробуйте с ним, а если он не актуален, отключите.
У вас может быть включен general_log в MySQL - это лог всех запросов к СУБД. Отключайте, он очень напрягает машинку, особенно если IO тормозит!
Ну и для начала, задумайтесь о создании кеша средствами софта, чтобы постоянно в СУБД не ходить.