Оптимизация mysql

После переноса базы на vds появились возможности манипуляции с настройками mysql.
В настоящее время взят сервер с памятью 256 мб, что явно недостаточно, но все же хотел поговорить по поводу оптимизации запросов.
Ранее никогда этим не занимался, поэтому надеюсь на помощь с вашей стороны и пользователей КБ.
Статистика показывает довольно большой (по моему мнению) трафик. На предыдущем месте за 20 дней он перевалил за 15 Тб.
Здесь измерить его за длительный период не получается, поскольку ежедневно, в 3-10 ночи сервис mysql перезапускается в силу того, что не может осуществить бекап базы (данный вопрос сейчас решается совместно с техподдержкой). Поэтому имею статистику за небольшой интервал времени: за 6 часов - более 37 Мб. Тоже не мало и это за ночной период, когда пользователей не было.  сервер_1.png   11,87К   15 Количество загрузок:  сервер_2.png   11,7К   15 Количество загрузок:
Как возможно снизить нагрузку? Была мысль использовать индексы для полей, которые часто используются в запросах, либо вообще проиндексировать все поля, которые являются полями связи. Эксперимент показал, что выборка по индексированному полю (select) снижает время выполнения более, чем 30 раз, но обновление данных в этой же таблице (update) возрастает раз в 10-15. Выборка по статистике происходит несколько чаще, но это в количественном показателе. Сколько времени тратится на запросы каждого типа статистика не показывает. Если временной показатель на выборку значительно выше, чем на обновление, тогда имеет смысл, видимо, проставить индексы. Делали что-нибудь подобное? Есть ли такая статистика для стандартной базы?

С недавнего времени КБ стала безумно тормозить. Страница может загружаться минуту и не загрузится вовсе, приходится прерывать и делать повторную загрузку - проходит. Программа стоит на VDS. Обратился в поддержку с проблемой и получил ответ - превышается процессорный ресурс. Причем рос он постепенно и теперь превышен. Поскольку ни количество пользователей, ни активность работы за это время не возросла, предположили, что вырос объем обрабатываемых данных. Может и вырос, но не настолько. Возникает стойкое ощущение, что выросла прожорливость самого приложения за это время. Есть статистика по трафику mysql. В базе, которая установлена, но в ней не работают совсем за 12 дней трафик составил 3,7 Тб - 308 Гб в сутки. В базе с активной работой за 64 дня - 19,2 Тб, т.е. 300 Гб. Количество запросов которое выполняется в секунду вызывает много вопросов. Большинство из них простые без join, что сказывается на нагрузке. Планируется какая-нибудь оптимизация? Предыдущий вопрос остался без ответа.

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

Roman (24 Апрель 2014 - 16:48) писал:

Сколько составляет трафик на базовой конфигурации за месяц, например на той же демонстрационной базе? Это не является коммерческой тайной, надеюсь...

andibrag (27 Декабрь 2013 - 08:57) писал:

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

А повлиять на это у пользователей, кроме как в собственных отчетах, нет ни какой возможности.
Попробуйте прописать в конфиге $config['sql_log']=1 и увидите какое количество запросов генерируется для вывода одной страницы и абсолютное большинство которых относится к выводу меню, проверке прав, видимости кнопок и т.д. Пользователю как то оптимизировать их нет никакой возможности. Они зашиты в код и закрыты Zend'ом

Тем не менее, даже при таких скромных возможностях, используя EXPLAIN, можно выстроить индексы так, что производительность повысится в несколько раз. Делалось не на одном аккаунте

А вообще, если не было опыта оптимизации, можно почитать
http://phpclub.ru/de.../mysql_optimize
http://habrahabr.ru/post/70640/

Сообщение отредактировал maksn: 25 Апрель 2014 - 02:01

maksn (25 Апрель 2014 - 00:53) писал:

Я тоже считаю, что движок должен оптимизировать производитель ПО. Индексов, кроме id, в базе нет. Затрачивать время на исследования и правку базы - занятие, мягко говоря, очень утомительное. Оптимизировать собственные запросы возможно, если не использовать стандартные функции работы с КБ, но это капля в море и как показывает статистика загрузка процессора и mysql сервера практически не отличаются от времени суток, т.е. не важно работает кто-то в базе или нет. Процессор грузит движок.

maksn (25 Апрель 2014 - 00:53) писал:

Вписал. Получил

Call to undefined function form_local_number() in /var/www/.../cb/include/mysql_connect.php on line 65

andibrag (25 Апрель 2014 - 10:54) писал:

Вот этого не понял. Куда вы вписали?
А имел в виду файл /include/config.php - http://clientbase.ru..._16/config_par/
Если установлен этот параметр, все запросы пишутся в лог файл, в папке /temp/

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

Сообщение отредактировал maksn: 25 Апрель 2014 - 21:30

maksn (25 Апрель 2014 - 18:12) писал:

Туда и вписал. После этого КБ не работает, при загрузке любой страницы выдает ошибку, которая выше.

andibrag (25 Апрель 2014 - 20:49) писал:

Странно. Делал это не однократно. Более того, это описано в документации на http://clientbase.ru..._16/config_par/

Быть может стоит озадачить ТП?

Сообщение отредактировал maksn: 25 Апрель 2014 - 21:28

maksn (25 Апрель 2014 - 21:26) писал:

Думаю прочтут...

andibrag (24 Апрель 2014 - 14:48) писал:

Если есть постоянный внешний IP, то может быть стоит подумать о том, чтобы поставить КБ на свой сервер в офисе? Проблем с оперативкой, по крайней мере какое то время, не будет. И за VDS платить не нужно

maksn (26 Апрель 2014 - 08:55) писал:

Была и такая мысль, но пока остановились на VDS. Оперативки 1 Гб, но похоже маловато будет.