Хостер прислал уведомление о том, что мой сайт создает слишком большую нагрузку на БД MySQL на сервере... Рекомендуют либо взять более дорогой тариф (по деньгам в принципе приемлемо, но не хочется менять сервер), либо соптимизировать скрипты таким образом, чтобы вписаться в допустимые рамки для моего тарифа. Вот график нагрузки на сервер:



Ограничение моего тарифа - до 50 единиц CPU и до 1000 единиц на MySQL.
Видно, что по нагрузке на CPU еще есть куда расти, а вот по БД - уже давно вышли из зеленой зоны. Желтая зона - это более дорогой тариф, на который предлагают перейти - но и там, как видите, уже стучимся "головой об потолок".

Снижение нагрузки с 14-ого по 20-е сентября (см. снижение на графике) - это результаты некоторых моих действий по оптимизации. А имено - удалил лишние записи в БД (бэкап базы весил 180 Мб, сейчас 100 Мб - т.е. где-то в полтора раза удалось уменьшить размер БД).

Еще совсем недавно - вчера-сегодня (на графике пока, думаю, не отразилось) прошерстил код и сделал следующее:
1) где можно убрал "лишние" запросы к БД (например менюшку со списком категорий раньше из БД подтягивал, сейчас сделал статичным кодом), то же самое сделал по баннерам (список баннеров для показа подтягивался из БД, сейчас просто берется из массива на PHP).
2) заменил в паре мест запросы на более узкие:
было SELECT *
стало SELECT id, title (другие поля реально в тех местах и не использовались)

Вопрос - что бы еще такого можно сделать, чтобы нагрузку на сервер MySQL снизить?

Дополнительная информация:
В месяц сайт посещает 40-45К уников, это дает 1.2-1.7 млн просмотров.
В сутки посещаемость порядка 2-4К уников, 20-40К просмотров.

P.S. сайт не тот что в подписи, конечно

Turboworld1

Мне кажется нужен пример медленного запроса...

Если запросы ещё не кешируются, то пропишите в тех запросах, таблицы в которых изменяются редко, sql_cache. На сервере (у хостера) кешировние конечно должно быть включено (query_cache_type = 2). Если включено с query_cache_type = 1, то не поможет - тут уже нужна оптимизация структуры БД и/или запросов.
Если таблицы часто обновляются - замените myISAM на InnoDB.
upd
Ах, да - отрубите всех лишних ботов. Тупые боты могут увеличить нагрузку в разы на мало посещаемых сайтах.

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru

Да

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru

Число посетители вообще мало интересует, с тех.точки зрения. Важны число генерируемых страниц и число хитов.
До 50К/сутки страниц (включая ботов, т.е. "всего") - это нагрузка для хостинга за 200р/месяц.

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru

http://habrahabr.ru/post/138163/
http://www.mysql.ru/docs/man/ORDER_BY_optimisation.html

_____________
Не тот велик, кто не падал, а тот кто падал и поднимался.

SELECT sql_cache * from table1 ORDER by date, title

У меня был один случай, в таблицу постоянно добавляются данные и выбираются для вывода (сложным запросом из многих таблиц) и пока кол-во записей было небольшое - было норм. Но где-то после 20-30 000 записей нагрузка увеличилась очень сильно. Решилось сортировкой и добавлением limit.

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


_____________
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api

Каждый конкретный запрос нужно анализировать в отдельности (как уже отметили выше, с помощью EXPLAIN), причем существует ряд нюансов, помимо индексов, как то: версия СУБД, объем БД, настройки, железо, фаза луны и т.п. Проверять лучше всего на реальных данных.
Пример из моей практики: один и тот же запрос на дев. сервере выполнялся за 11 сек, а на продакшене - 50+ сек. Данные одинаковые. Причина была следующая: на деве версия мускула 5.6, а на боевом 5.5 и внутренний оптимизатор MySQL 5.6 менял порядок связывания таблиц в запросе, а 5.5 выполнял так, как он был написан. Такие дела.

_____________

Профессиональная разработка на заказ

Я на GitHub | второй профиль