Про время выполнения SQL запросов на VDS, Откуда такие задержки Опции

[frog]

Добрый день всем!

Пользуюсь услугами VDS одного украинского хостера (не будем показывать пальцами)

Анализирую лог времени выполнения SQL запросов. Задача: выбрать несколько записей по первичному ключу на одной таблице в которой 1000 записей (примитивный запрос который даже на самом старом оборудовании должен выполнятся за доли секунды).

Лог иногда приобретает вот такой вид:

0.03 сек
0.005 сек
0.007 сек
4 сек
0.01 сек
0.006 сек

Пытался понять откуда берется задержка в 4 сек? Перешел на более высокий тариф, загнал в кеш некоторые таблицы. Проблема осталась. Затем думал что происходят блокировки таблиц при репликации (у меня настроена репликация) - отключил репликацию - т.е. идут только SELECTы, стало работать немножко пошустрее - но такие логи все равно остались. Примитивные запросы иногда выполняются по 6 сек! При отключенной репликации и при том что я один на сервере.
Соответственно в итоге время генерации страницы становится более 6 сек!
У Вас есть какие то предположения? Хостер работает на OpenVZ, а эта система допускает оверселлинг. Возможно ли что моя проблема возникает из за загруженности всего сервера, и в частности из за большого количества обращений к жесткому диску? Ведь MySQL берет данные с него.


Текущие параметры:
Operon 1300 MHz
1024 RAM

[lazutov]

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

Сделайте запрос OPTIMIZE TABLE `table`. Или его аналог в Вашей СУБД
На БД с 1 500 000 записей с частым update/insert результаты заметны:
(IMG:http://smages.com/t/6e/fa/6efa110e58cd644e68f3693ad1b418e6.jpg)

Знаками v отмечены моменты оптимизации.
UPD: отошлите, если не сложно, мне в ЛС ссылочку на хостера...

Сообщение отредактировал lazutov - 23.05.2009, 15:53

[Ivan]

Предположу, что в disk io сервер упирается временами.

[Maxim Volgin]

Примитивные запросы это какие (IMG:style_emoticons/default/smile.gif) ? Возможно для мукула они таковыми не является. Запросу могут ждать выполнения других запросов даже если для этого явных видимых причин нет. Например заходят 2 юзера на одну и туже страницу одновременно и мускул героически сам с собой борется.

[eSupport.org.ua]

Диск
Погоняйте gzip из /dev/random на диск и посмотрите сколько времени занимает

[frog]

Спасибо всем ответившим.

Индексы не используются?

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

Код

  SET GLOBAL gl_vacaddr_index_cache.key_buffer_size = 10 * 1024 * 1024;
  CACHE INDEX gl_vacaddr IN gl_vacaddr_index_cache;
  LOAD INDEX INTO CACHE gl_vacaddr;

Но дело в том что когда MySQL по первичному индексу (который сидит в оперативке) находит запись, но за содержанием самой записи все равно нужно на винт лезть.

Сделайте запрос OPTIMIZE TABLE `table`.

Спасибо за совет, попробую.

отошлите, если не сложно, мне в ЛС ссылочку на хостера...

Мне запрещено пользоваться ЛС (не знаю почему), скажу так что по запросу VDS это первое место в яндексе в блоке контекстной рекламы на ВИП месте (что над результатом поиска) (имя из 5 символов в зоне .com) (IMG:style_emoticons/default/biggrin.gif)
Дешевле по параметрам я не нашел (IMG:style_emoticons/default/smile.gif) Вообще я как посмотрю - на Украине цены заметно ниже.

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

По первичному ключу выбрать одну запись из таблицы в 1000 записей. Куда уж проще. Это даже на самом первом в мире компьютере (по моему Абаке) всяко разно быстрее чем за 4 сек. выполнится(IMG:style_emoticons/default/smile.gif))
И у хостера это выполняется в основном всегда за несколько милисекунд. Но вот иногда случаются паузы по 4 сек. Причем на совершенно разных таблицах. Сначала на одной, потом на другой. База всего 700 метров. Основную часть занимает одна таблица 560 метров. Там еще я понимаю что 10 записей по первичному ключу выбрать - полсекунды надо. Но когда задержка 4 сек. (то же самое - выбрать одну запись по первичному ключу) на таблице в 10 метров - это мне непонятно. Причем тут же идет еще один такой же запрос - 20 милисекунд.

Погоняйте gzip из /dev/random на диск и посмотрите сколько времени занимает

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

[lazutov]

/dev/urandom псевдоустройство, которое извегает из себя случайные данные.
/dev/null также пседоустройство, которое поглощает все, что туда отпраляется. Накопитель неограниченной емкости (IMG:style_emoticons/default/smile.gif)
/dev/zero генерирует нули (В никсах, они, видимо, в особом дефиците)
Погонять между ними
cat /dev/urandom > /dev/null

Но это бесполезное и никому нафиг не нужное занятие.
C gzip навскидку
cat /dev/urandom | gzip -c > rand.gzip

[frog]

Сделайте запрос OPTIMIZE TABLE `table`.

Блин, я просто в шоке! В хорошем шоке(IMG:style_emoticons/default/smile.gif))
Сначала сделал OPTIMIZE этой большой таблице на 560 метров. Думаю, вряд ли поможет но на всякий случай. Раньше на ней запрос хорошо если в пол-секунды укладывался. Сделал OPTIMIZE - стало за считанные милисекунды! Т.е. скорость возрасла на 2 порядка! Смотрю - не верю. Сделал OPTIMIZE всем остальным - и страница стала генерироваться мгновенно, ну или почти мгновенно(IMG:style_emoticons/default/smile.gif))
Вот средняя статистика:

Код

Время выполнения SQL запросов :0.06983
Максимальное время выполнения SQL :0.01511s

Теперь узким местом стал Сфинкс который делает основную работу - тот тоже бывает по 6 секунд ищет. (Бывает но редко) Но это уже не сюда вопрос.

Что за чудо команда OPTIMIZE!:))

А я уже на дедик начал копить(IMG:style_emoticons/default/smile.gif))

Александр Лазутов, большое спасибо! Если Вы из Екатеринбурга, готов отблагодарить пивом за дельный совет(IMG:style_emoticons/default/smile.gif))

[lazutov]

Да не за что.
Делайте оптимиз по крону раз примерно в 2 недели для редкообновляемых таблиц и раз в 2-3 дня для сложных многотабличных систем с INSERT/DELETE

Структура таблиц такова такова, что при многих INSERT/DELETE она фрагментируется и разбрасывается по далеким друг от друга адресам.
Эту статистику вы можете посмотреть через PMA. Называется Overhead <что-то> или в переводе Фрагментировано <что-то> во вкладке операции.

Отлов в mysql вручную по
SHOW TABLE STATUS `тaблица`
поле data_free - индикатор оверхеда, читать фрагмантации
Data_length - общий размер таблицы

UPD перегруппировка индекса. ALTER TABLE `tbl` ORDER BY `primarykey` [asc/desc] Если запросы селекта по праймари вызывают подвисание, их нужно перегруппировать. Optimize это вроде не делает, а может и делает.

Перед запросом делаем бекап в обязательном порядке.
mysqldump -uUser -pPass DB | gzip -c > /home/db.`date`.gz

Сообщение отредактировал lazutov - 23.05.2009, 20:58