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

Пользуюсь услугами 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

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

Сделайте запрос OPTIMIZE TABLE `table`. Или его аналог в Вашей СУБД
На БД с 1 500 000 записей с частым update/insert результаты заметны:


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

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

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

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

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



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



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


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



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



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



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

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

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

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



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

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

А я уже на дедик начал копить)

Александр Лазутов, большое спасибо! Если Вы из Екатеринбурга, готов отблагодарить пивом за дельный совет)

Да не за что.
Делайте оптимиз по крону раз примерно в 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

Не совсем бесполезное
Делать watch -n 1 du -h rand.gzip и смотреть на скорость
Если будут скачки, то таки да - диск

Вчера на ночь запускал репликацию. Сегодня встал с утра - дай думаю порадуюсь скорости - фиг там - опять по 6 секунд запросы. Снова сделал - OPTIMIZE - снова все нормуль (порядок милисекунд).
Потом запустил репликацию на час. DATA FREE для самой большой таблицы стало 904500. Опять запросы по 6 сек. Т.е. даже малейшие изменения таблицы приводят к увеличению времени выполлнения на 3 порядка!
Снова сделал - OPTIMIZE - снова все нормуль (порядок милисекунд).
Вообщем пока для себя вижу такой вариант: на ночь на несколько секунд запускать репликацию, затем оптимиз всем нужным таблицам. Это можно было бы делать и днем - но OPTIMIZE для самой большой таблицы делается более 10 минут, и во время OPTIMIZE таблица блокируется - соответственно сайт просто не работает.

Еще думаю попробовать перевести на InnoDB (сейчас MyISAM) - там все в одном файле хранится. И блокировки на уровне строки а не на уровне таблицы - т.е. репликация не будет блокировать таблицы целиком. С другой стороны этот файл, в котором хранится база может быть раскидан по всему диску, и насколько я читал - OPTIMIZE для InnoDB не работает.

Вы представляете себе сколько на Inno будет кушаться CPU и памяти?
Откуда и куда(и главное с какой целью)все реплицируется?
Может Optimize и ALTER сделать для всех копий?

Кто бы мне подсказал.. Как выделяется жесткий диск на Open VZ. Физически или виртуально?

> для самой большой таблицы
Насколько большой? Важен процент фрагментации

Вот пациент: Поисковый сервис по поиску вакансий (дизайн пока в разработке)
Смысл в том что на домашнем компьютере происходит индексирование сайтов, всяческие обработки. А затем уже готовые данные заливаются на сайт. Заливаются с помощью репликации. В идеале хотелось бы постоянную репликацию. Но в виду того что при инсертах в MyISAM таблицы блокируются целиком - постоянная репликация - решение которое будет тормозить скорость генерации страницы. Вот собственно два выхода - либо ночью все заливать, либо на InnoDB перейти.



Так это решаемо. Тариф побольше. Вообщем то и сейчас оперативки немало - 1024 Мб.


560 Мб. Процент фрагментации - это (Data Free/Размер таблицы)*100 ?
Вообще мне кажется важен не сколько процент, сколько как далеко разбросаны записи на жестком диске, которые выводятся в результате запроса. Поэтому без OPTIMIZE иногда было быстро (когда все записи были рядом) - а иногда по 20 сек (когда все записи в разных концах диска).


Ничего не могу сказать, но думается мне что виртуально. Представляете - на одном сервере 20 VPS'ов - что каждому по диску давать? Да к тому же это только у меня - 80 ГБ - что сопостовимо с размерами дисков, а в других компаниях - 5-10 Гб на VPS выделяют.

Что касается самих дисков - то вот цитата с сайта компании:
"Ультра быстрые SAS диски
Дисковая подсистема виртуальных серверов основана на самых быстрых в мире нжмд технологии SAS с двойным дублированием данных Raid-10 на контроллерах Adaptec."

У меня 100мб за 20 секунд оптитмизируется.

Значит. Raidовость: эти данные могут быть на разных физических ЖД. А контроллер может не справляться по скорости. имеем задержки.
Насколько мне помнится, r10 это распараллеливание и зеркалирование параллелей. Сказанное выше мне кажется вполне понятным и логичным.
Тут ничего не поделаешь. Варианта 2: поговорить с провайдером на тему задержек по диску, сменить провайдера.

PS мне кажется, что там не raid10, а raid1(частный случай 10, только дисков всего 2, распараллеливание в 1 поток). Это же не xen..

Логично предположить что 560 Мб будет оптимизировать в районе минуты. У меня минут 10 на это уходит)



Там все глухо как в танке. Говоришь про дикие паузы. Тебя спрашиваю "Каков размер всей базы" - я говорю "1,2 Гб" (на самом деле я ошибся - она метров 700 вся занимает).
Отвечают: "Это очень большой объем базы - на такой базе запросы будут выполнятся медленно"

Хотя казалось бы причем здесь объем всей базы когда запросы идут к отдельным таблицам.
Некоторые из них занимают не более 3 метров - а паузы на запросах к ним по 4 сек.



Нашел на просторах России вот такой вариант:
Виртуальный выделенный сервер Xen Xeon 1,8 ГГц, ОЗУ 1 Гб, 40 Гб SATA RAID
Стоит такая радость всего 1500 р. в месяц. Причем разрешили протестировать.
В принципе XEN не допускает оверселлинга, но с т.зр. доступа к ЖД - мне кажется - одна фигня.
Ссылку если надо могу в личку скинуть. Есть отзывы на хостобзоре - правда не все хорошие

Переупорядочивание по праймари делали?
Есть подозрение, что это все происки неупорядоченных таблиц.
До и 700мб для mysql это достаточно серьезно.
Скорее всего, как уже сказано, Disc io. Я очень сомневаюсь, что там вообще есть Raid10 || SAS диски.
Померяйте по совету eSupport.org.ua скорость дисков.

Вообще очень познавательная тема.
/me пошел издеваться над 3GB IDE диском по поводу вынести mysql на него и довести до состояния "пожарить яичницу"

Походу дела это был еще один дельный совет. К слову сказать 560 метров "упорядочивалось" 8 минут. Но вроде бы пошустрее стало. Теперь порядок времени выполнения запрса не превышает милисекнд.



А какая скорость - нормальная? Вообщем мерял по мотивам

http://www.wl500g.info/showthread.php?t=11687

Вот скорость записи:


А вот скорость чтения (запускал 2 раза один и тот же тест):
Первый тест:


Я так понял скорость чтения (что меня больше всего интересует) - 2 Мб/s в лучшем случае. Это нормально? Почитал статьи на тему тестирования ЖД - так там речь идет о 100 Мб/s. Хотя тут конечно VPS.

В 10 рейде такой маленькой скорости быть не может. С этой лапшой понятно.
SATA нагруженная нефиговым количесвом vds (минимальный или нулевой оверсел)

Далее: ноутбук, debian5 под virtualbox
1.9 MB/s

Так что все относительно. Но то, что диски немного тормозят, очевидно.

PS за 50 баксов и дедики есть.

Вот как раз хотел встрять в тему именно с этим предложением.
Цена минимального дедика сопостовима с "толстым" VDSом.
А диски в разы менее нагружены. Даже старенькия целерон с медленным диском скорее всего будет под такой базой шустрее работать, чем заоверселлерный VDS...

1.7 MB/s ? 2 MB/s ?
Шутите что-ли?

У меня бюджетный кластер с гигабитным коннектом к стораджу на ~20 vz pvs дает ~30-50MB/s