Как определить что узким местом стал именно диск?
Вродк по Profiler не определить что проблема в нём.
Про процессор всегда можно сказать - загружен он на 100% или меньше, и кто кто его грузит, а есть ли какие-то утилиты чтобы определить тоже самое (загруженность диска в процентах и какая программа грузит) под Windows XP? Или это позволяют какие-то внутренние утилиты MySQL?

Тормозит конкретная таблица (на движке MyISAM). Таблица занимает около 18Gb (20 полей Integer, DateTime и VarChar не более 15 символов, и одно Text которое создаёт объём). Таблица очень фрагментирована из-за того что данные переодически подкидываются, и когда дефрагментатор показывает что в ней больше 20 тысчь фрагментов, делаю дефрагментацию всего диска программой Auslogics Disk Defrag Professional с приоритом по свободному месту, так как при дефрагментации обычным дефрагментатором входящим в виндоус и при стандартной дефрагментации Auslogics Disk Defrag Professional этот файл остаётся без изменений и в финальных отчётах так и пишется что осталось несколько дефрагментированных файлов и показывает log-файл из Apache и эту таблицу в каждой из которых несколько тысячь фрагментов (что интересно, даже если я в дефрагментаторе указываю дефрагментировать только один конкретный файл, при этом разумеется на время дефрагментации выключаю сервис MySQL и Apache то эти файлы не дефрагментириуются, а если указываешь что надо весь диск дефрагментировать с приоритетом по свободному месту, тоесть когда он сгоняет все файлы к началу диска и концовку физически оставляет пустой, тогда дефрагментируются и эти файлы). Но по идее эта дефрагментация не должна влиять, так как поиск происходит только по полям которые в индексе, а индексный файл цельный (не фрагментированный), а запрос типа:

тоесть поиск и сортировка по полям которые в индексе (в этом случае вроде как MySQL даже не будет открывать файл с данными), хотя вроде как при сортировках MySQL задействует диск (именно по этому и считаю что узое место диск).
При простом запросе к такой таблице результат получаю черз 10-15 секунд, а если паралельно запустить почти такой же запрос то оба процесса замерают и комп шуршит диском неизвестно сколько (через 5-10 минут мне обычно надоедало и я через сервисы говорил MySQL-серверу рестартироваться и запускал процессы по отдельности).

P.S. Слышал, что если использовать InodDB, то вроде MySQL сам мледит за фрагментацией всей таблицы (так как она вся хранится в одном файле). Действительно ли это так? Хотя на данный момент перейти на другой движок не могу, хоть и слышал что в последнем MySQL уже и в InodDB есть FullText, но у меня бекапирование осуществляется в виде файлов, так как таблица с 10 миллионами записями копируется за пару минут, а чтобы снять с неё дамп (а тем более восстановить) уходит больше часа...

miksoftВо-первых, в индексе явно нет всех полей таблицы, т.е. лазить в в файл с данными придется однозначно.Разумеется придётся лазить в файл с данными, но я имел в виду что даже если где-то для выбоки надо сделать перебор по большому поличеству полей - то этот перебор произойдёт по индексному файлу, а уже потом выбронные по ограничению limit 50 будут выбраны из файла с данными, а эта операция произойдёт быстро (тоесть гоняться будет индексный файл).

miksoftВо-вторых, не глядя в план, вообще не факт, что для этого запроса будет использоваться индекс. Зависит, как минимум, от состава индекса и селективности условия Data>'2014-05-04'.Тут, как ни странно вы оказались очень правы, в примере видно что при первом запросе вместо индекса поля по которому ищу используется первичный ключ `ID`.

По поводу экплэйна, вот пример:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLETablicaindexTipPRIMARY41370Using whereВремя выполнения 3.8 секунды

И тоже самое без лимита:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLETablicarefTipTip12const8765"Using where; Using filesort"Время выполнения 2:31 минуты!!!

При этом если я вместо "SELECT *" напишу "SELECT `ID`" то скорость не меняется (ну бегает в пределах погрешности 2:29-2:36 для второго запроса).

P.S. Размер индексного файла всего 25Мб.

miksoftА причем тут это запрос? Вы показывайте план исходного запроса.
А разве есть какая-то разница? Это всё к одной таблице обращения... Там десятки схожих запросов (везде по индексированному полю), а результат примерно у всех одинаковый: если лимитированный одиночный запрос - то время обработки до 10 секунд, а если запустить 2 запроса одновременно (ну точнее второй запрос запускается до завершения первого), то выполнение идёт больше 10 минут... Или вы так принципиально хотите именно экплэйн с полем типа datetime?

miksoftИ, кстати, предложите запросам из примера индекс (`Tip`, `ID`).
Вы думаете это будет настолько актуально когда индексный файл занимает всего 25Мб?
Я согласен что это облегчит базе поиск по данной связке. Но тогда мне придётся создать сотню новых индексов! (ведь пользователь может искать среди десяти проиндексированных полей, и дальше сортировка может быть тоже по любому из этих полей, в зависимости от того на какую колонку в таблице пользователь нажмёт). А ведь по экплэйну видно что результатом является всего 1000 записей в лимитированном и всего 8000 записей в полном запросе. На сортировку данного количества даже секунды много!
В самой таблице меньше 250 тысячь записей.

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


miksoftИндекс я предлагал больше в исследовательских целях.Я постараюсь ночью сделать. При изменении индекса MySQL ведь будет физически пересоздавать всю таблицу, а это по времени долго (да и откровенно говоря у меня сейчас нет столько свободного места). Другое дело что после дефрагментации скорость увеличивается в разы (но за день-два опять падает из-за увеличения фрагментирования файла).


miksoftInterSky...Таблица занимает около 18Gb...
...В самой таблице меньше 250 тысячь записей...
...результатом выборки является всего 1000 записей...
На сортировку данного количества даже секунды много! Вообще-то это несколько сотен мегабайт. И за секунду их никак не отсортируешь.Не соглашусь (или вы можете более равёрнуто аргументировать такую скорость?)
Да, записи большие. Но я рассказывал структуру: десяток коротких полей Integer, VarChar, DateTime и одно Text более 60Кб.
Но:
1) Я говорил что если меняю " SELECT * from ... " на " SELECT ID from ... ", то время практически НЕ меняется.
2) Разве сортирока производится всего набора данных? Почему-то я уверен что вначале в индексном файле выбираются все записи соответсвующие `Tip` = 'AA', затем из индексного же файла берутся значения поля `ID` и происходит сортировка, а уже затем из файла с данными достаются сами данные и передаются на выход... Или вы хотите сказать что MySQL собирает неотсортированный набор 8000*60Кб=480Мб и потом 2 минуты сортирует их на жёстком диске? (хотя в случае с select ID from объём выборки и мегабайта не займёт).

Из-за отсутсвия свободных 18Гб мне так и не удалось добавить двойной индекс чтобы проверить насколько изменится производительность при нём (хотя как я говорил - это не будет решением, так как я не могу поставить двойные индексы по всем вариантам поиск+сортировка).
Но зато за ночь я сделал дефрагментацию (как я уже говорил ранее - реально дефрагментировать файл Tablica.MYD может только Auslogics Disk Defrag Professional и исключительно в режиме Free space optimization, потому что в обычном режиме большие файлы с большим количеством фрагментов полностью игронирует как стандартный дефрагментатор виндоуса, так и используемый мной Auslogics Disk Defrag Professional даже если я указываю что мне надо дефрагментировать только один мой конкретный файл).

Итак после дефрагментации запрос выплняемый за "2:31 минуты" стал выплняться за 0.6 секунды, а лимитированный который выполнялся "3.8 секунды" стал выполняться за 0.02 секунды. Но это сразу после дефрагментации, а спустя пару дней всё вернулось примерно к прежним тормозным скоростям.

Сегодня даже был случай: запустил цикл апдейтов:

(ясное дело вместо "..." подставляются разные значения)
Обновления выполнялись быстро (там куча расчётов по этому скорость 3-4 обновления в секунду считаю отличной), и паралельно запустил лимитированный запрос:

(который в самые плохие времена выполняется за несколько секунд, а после дефрагментации как писал выше - выполнился за 0.02 секунды), и после запуска этого запроса всё повисло на 12 минут!!! Я подключился к MySQL через третий клиент и смотрел SHOW FULL PROCESSLIST - время выполнения у update и select запросов было одинаковое (тоесть за время сэлэкта не проскочил ни один апдэйт). Причём всё это время (все 12 минут) компьютер нещадно молотил жёстким диском... А нагрузка на процессор была почти нулевой (mysqld иногда поднимался до 2% процессорного времени, а больше ничего и не работало).
Может есть какая-то утилита под виндоус которая показывает с каким конкретным файлом работает программа и сколько раз обращалась, чтобы понять - или это идёт выборка данных из таблицы, или это сортировка в файле, или это ещё непонятно что? Упомянутый тут AnVir показывает общую нагрузку на диск (как впрочем и встроенный в виндоус PerfMon.msc)

Для вадя :
Вот результат выполнения вашего запроса:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY<derived2>ALL11017Using filesort2DERIVEDTablicarefTipTip128087Using whereПричём этот экплэйн выполнялся 2 минуты 3 секунды.

На всякий случай напишу и структуру таблицы:

Количество записей: более 300 тысячь
Размер файла Tablica.MYD: более 20Gb
Размер файла Tablica.MYI: всего 28Мб

Есть индекс по полю Data, я просто чтобы небыло очень длинно стёр повторяющиеся строки:


и их индексы, случайно захватив и индекс по дате... Хотя по дате очень редко ищется и сортируется (восновном по ID), по этому после первого примера который я привёл в описании проблемы, я давал уже более частые запросы.