MySQL на Windows Server 2008 R2 и жирная база

[Proxy]

Столкнулся с проблемой производительности СУБД.

Есть некий сервер под управлением Windows Server 2008 R2, с самого начала (несколько лет, другой человек ставил) на нём работает некоторый софт, который хранит в ней кое-какие данные.
Пару недель назад софт перестал строить отчёты, выводил пустой отчёт из-за превышения времени ожидания ответа. Увеличил таймут, заработало.
Сегодня нашёл время разобраться немного в ситуации.

Итак: MySQL значительно грузит ЦП, даже достаточно простые запросы к этой базе выполняются оочень долго. Среди файлов из директории этой базы файл ibdata весит 35Гб. ibdata хранит innoDB хранилище собственно.
После некоторых манипуляций вытащил пароль root`а (изменить было нельзя, т.к. root используется софтом, так уж сложилось, а он канул вместе с уходом сотрудника, который ранее этим занимался) и подключился к базе. В базе есть таблицы, содержащие журналы работы софтины с самого начала, они занимают от 2 до 17ГБ (база не обслуживалась с момента создания, очевидно никто не следил). Просто снести таблицы нельзя, они связанные, несмотря на то, что это всего лишь логи (и используются при формировании отчётов).

Сделал копию базы (если вдруг потребуется сделать отчёт за какой-нибудь давний период, разверну по необходимости), нашёл на офсайте разработчика софта процедуру очистки базы от данных, старше определённой даты создания, гарантирующую целостность данных, взял запросы, но не смог использовать.

Хотел удалить данные поэтапно, отдельными запросами для каждой таблицы, но в текущем состоянии, похоже, это сделать нельзя. Очистка самой маленькой таблицы из тех, что следует вычистить, заняла 2 минуты. Эта таблица не связана с другими и содержит дату создания каждой записи (простой запрос типа "DELETE FROM `table_1` WHERE `table_1`.`Time`< '12.12.2012'"), с остальными будет сложнее намного (а с той, что содержит 17ГБ данных вообще не выйдет ничего).

Софт держит информацию за последние сутки во встроенной СУБД (каждую ночь (опционально) удаляет все записи старше 1 дня), с MySQL синхронизировался ранее каждые 15 минут (и повтор через 10 минут в случае сбоя). Установил интервал 25 минут и устранил запись лишнего мусора в базу (а повтор так и остался 10 минут). Но просто подсунуть чистую базу (в т.ч. со структурой) нельзя, боюсь при синхронизации в этом случае возникнет сбой из-за каких-нибудь номеров транзакций или ещё чего (таблиц много, возможно там и конфиги кое-какие хранятся в т.ч; не смотрел запросы софта к базе, да и нечем).
Как избавиться от мусора в базе и не повредить данные от начала текущего календарного года?

И да, останавливать ничего нельзя, сильно нагружать чем-либо и без того нагруженные ЦП тоже не стоит. Копия базы есть (просто файлы, резервирование не настроено, но эти файлы можно подсунуть в такую же СУБД на моей машине, но т.к. она слабее (пк), то и с MySQL будет ещё больше проблем).

Было в мыслях вытащить актуальную информацию из копии, затем на время остановить MySQL, подсунуть обработанные данные и снова запустить, но если что-то пойдёт не так, то закончится это печально.
Ведь часто такие ситуации бывают, как такое решается?

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

PS. Ах да, этот софт можно не останавливая переключить на другую базу, если потребуется. Есть возможность прописать внешней базе другой IP, порт, имя базы, логин, пароль и всякие мелочи. Постоянно соединение с внешней базой он не держит. В целом ничто не мешает синхронизацию с внешней базой вообще выключить на разумное время (она ни для чего, кроме отчётов и не используется), чтобы высвободить процессорное время (но СУБД сильно быстрее работать не начнёт, с ней какие-то проблемы).

PPS. Заметил, что не очень ясно обрисовал картину: СУБД и софт, который её использует, работают на одном физическом сервере. Можно базу перетащить на другой сервер, если потребуется.

[Хакер]

Так просто разбивать свои посты на абзацы вставками из одной пустой строки. Но это в 8 раз поднимает желание прочитать пост до конца.

[Proxy]

Ок. Только там скорее поток мыслей, абзацев и нет. Возможно позже переработаю, если есть неоднозначности. Жаль нет аналога тега P.

[iGrok]

35Гб для innodb - это не так уж и много. 17Гб для одной таблицы - тоже. Если, конечно, грамотно расставлены индексы и составлены запросы.

А какой запрос тормозит? Что говорит EXPLAIN тормозящего запроса?
Чтобы удаление работало быстрее, нужно задавать условия по индексу. Т.е. если данные идут последовательно, и дата не индексирована, то задавать не "where time < 'xxxx-xx-xx'", а выбирать PK записи с нужной датой, и задавать условие "where PK < XXX".

[Proxy]

А какой запрос тормозит?

Даже Select по дате.

Чтобы удаление работало быстрее, нужно задавать условия по индексу.

Нет никаких гарантий, что везде соблюдалась хронология по индексам. поэтому удалял по дате. Мало ли что с этой базой делали до меня.

Что говорит EXPLAIN тормозящего запроса?

Не пришло в голову, посмотрю.

Запрос удаления записей по дате (меньшей заданной) положил базу где-то через 10 минут выполнения (браузер базы выдал ошибку соединения с базой, служба MySQL просто остановилась).
Естественно ограничил предел числа удаляемых записей (limit) миллионом, работает медленно, но по крайней мере работает. Как бы нет сложностей набросать приложение, которое будет последовательно скармливать эти запросы (и остановится в случае любой ошибки), но сложно представить, сколько времени займёт удаление таким методом. Впрочем это уже выход, пускай работает ночами.
Ещё это, возможно, даёт возможность вытащить данные в исправную базу (пустую базу с копией структуры таблиц уже поднял), а затем переехать туда.
Пока не было уделить внимание вопросу, он может подождать до лучших времён.

[Qwertiy]

Возможно, в базе есть тяжеловесные триггеры... Я как-то повесил MS-SQL базу, добавляя в цикле всего пару тысяч записей, на несколько часов. Там на каждое добавление срабатывал триггер, который выбирал примерно 1% записей таблицы, делал по ним join'ы с десятком других таблиц и обновлял конкатенацию строк по всем этим выборкам...

[iGrok]

Не пришло в голову, посмотрю.

А начинать-то надо было как раз с этого.

Даже Select по дате.

Ну, если на дате нет индекса - select и будет тормозить на больших таблицах.

Запрос удаления записей по дате (меньшей заданной) положил базу где-то через 10 минут выполнения (браузер базы выдал ошибку соединения с базой, служба MySQL просто остановилась).

А вот это уже странно. Ни разу мне не удавалось именно положить мускул. Т.е. ошибку соединения на большой операции - это пожалуйста, база задумалась и делами занимается, а вот чтобы она остановилась...

ИМХО, действительно разобраться в ключах и структуре, и перенести всё, что нужно, в новую базу (заранее предусмотрев индексацию по нужным полям по результатам изучения explain). После удаления записей файл-то меньше весить не станет.

[Proxy]

Сегодня база разрослась настолько, что возникло предупреждение о нехватке свободного места на диске. Сначала принялся удалять временные файлы (вообще почти всё хранилось на 1 томе, увеличивать не было смысла, т.к. этот том занимает полностью 2 маленьких харда в хардварном зеркале), файлы из профилей пользователей (а там почти пусто и так), временные файлы (тоже кропаль) и т.п. В общем не удалось ощутимо высвободить место, из тяжеловесов там только база и система.
Откладывать не было смысла, поэтому в темпе: вытащил таки актуальные данные (отдельная история, в общем-то как и планировал), убедился, что на этом сервере только 1 действительно необходимая база (впрочем теперь копии есть тоже), попытался снести базы (но MySQL видимо не собирался это делать сколько-нибудь быстро, не стал дожидаться), остановил MySQL, снёс всё, что было связано с innoDB (сам ibdata1 и 2 журнала), установил параметр innodb_file_per_table в mysql.ini, запустил MySQL и залил назад базу (не целиком, разумеется). Софт подключился и успешно синхронизировал базу с интегрированной.
InnoDB данные теперь хранятся в той же директории, где раньше хранились только метаданные. Раздельно для каждой таблицы, теперь проще будет.
ЗЫ. А ещё вчера пытался сделать optimize всех таблиц, но оно дошло до первой тяжеловесной таблицы и так и не обработало её за вечер + ночь. Optimize заблокировал доступ к таблицам, поэтому сабжевый софт со вчерашнего дня ни разу успешно не синхронизировался (но при этом и чистить ничего из интегрированной базы не стал; затем после рестарта MySQL почти переполнил том с "внешней" базой).
ЗЗЫ. Вот ещё интересно поведение InnoDB: положим я из базы теперь (когда это стало возможно) удаляю по расписанию данные, созданные больше n-месяцев назад. InnoDB будет циклично размещать новые записи в высвободившимся пространстве (если хватает) или с конца? Вообще я всегда считал, что первый вариант, но теперь не уверен. InnoDB можно как-то дефрагментировать (не optimize, а именно всё свободное место дефрагментировать, разместить в конце и обрезать)?

[iGrok]

ЗЗЫ. Вот ещё интересно поведение InnoDB: положим я из базы теперь (когда это стало возможно) удаляю по расписанию данные, созданные больше n-месяцев назад. InnoDB будет циклично размещать новые записи в высвободившимся пространстве (если хватает) или с конца? Вообще я всегда считал, что первый вариант, но теперь не уверен. InnoDB можно как-то дефрагментировать (не optimize, а именно всё свободное место дефрагментировать, разместить в конце и обрезать)?

Первый вариант. Конечно, она всё равно не сможет занять 100% освобождённого места, поэтому фрагментация в этом случае неизбежна.
Optimize table - это и есть "Дефрагментация". Для InnoDB она сливает дамп данных, пересоздаёт таблицу и заливает данные обратно.

[Proxy]

Optimize table - это и есть "Дефрагментация". Для InnoDB она сливает дамп данных, пересоздаёт таблицу и заливает данные обратно.

Ну я так понимаю, что здесь цель — производительность, а не экономия места. Т.е. таблицы дефрагментируются, но целиком размещаются как придётся. Ну и вызывается потаблично, т.е. нигде и не сказано, что следует ожидать, что сами таблицы тоже "скучкуются" и свободное место будет вытеснено в конец файла. Во всех источниках сказано, что в случае с InnoDB ничего и не предусмотрено под мой случай, даже на офсайте рекомендуют пересоздать базу (правда там есть ещё хинт, как это сделать не вырубая MySQL, однако ж дольше значительно).

Для InnoDB она сливает дамп данных, пересоздаёт таблицу и заливает данные обратно.

И в это время полностью блокирует таблицу, вместо того, чтобы работать с буфером (могли бы и предусмотреть). Могли бы в конце-концов по аналогии с ФС сделать. где прикладной софт и знать не знает, что файл в это время сжимается. дефрагментируется или ещё что. Однако ж об этом явно сказано в описании Optimize, так что без претензий.
Блокировать таблицу — это вообще подло. Клиент добавляет запись в одну, в другую, в третью таблицу (связанные данные), а на четвёртой таблице получает отлуп. И по-сути всё, прощай целостность данных.

[Qwertiy]

И по-сути всё, прощай целостность данных.

Эм.. А транзакции как же?

[iGrok]

Ну я так понимаю, что здесь цель — производительность, а не экономия места. Т.е. таблицы дефрагментируются, но целиком размещаются как придётся. Ну и вызывается потаблично, т.е. нигде и не сказано, что следует ожидать, что сами таблицы тоже "скучкуются" и свободное место будет вытеснено в конец файла. Во всех источниках сказано, что в случае с InnoDB ничего и не предусмотрено под мой случай, даже на офсайте рекомендуют пересоздать базу (правда там есть ещё хинт, как это сделать не вырубая MySQL, однако ж дольше значительно).

Ничего не понял. Какой-то бессвязный набор предложений и домыслов, не понятно как связанный с процитированным.

Дефрагментация в случае innodb делается единственным образом - пересозданием таблицы. Простой способ это сделать: alter table `tablename` engine=innodb;. Начиная с Mysql5 запрос optimize table `tablename`; именно это и делает. До 5й версии этот запрос для InnoDB вообще не поддерживался.

Если у тебя не включен innodb_file_per_table, то, насколько я помню, с размером ibdata вообще ничего нельзя сделать, кроме полного пересоздания базы (дамп - очистка - восстановление).

И в это время полностью блокирует таблицу

Разумеется. Все серьёзные операции по обслуживанию базы на всех движках делаются при отключенных от базы клиентах.

Впрочем, никакого отлупа при блокировке никто не получает. Просто запрос будет дооооолго ждать, вплоть до снятия блокировки.

[Qwertiy]

Просто запрос будет дооооолго ждать, вплоть до снятия блокировки.

Что-то мне подсказывает, что он отвалится гораздо раньше по таймауту...

[iGrok]

Просто запрос будет дооооолго ждать, вплоть до снятия блокировки.

Что-то мне подсказывает, что он отвалится гораздо раньше по таймауту...

Смотря какая таблица, смотря какой таймаут.

[Proxy]

Если у тебя не включен innodb_file_per_table, то, насколько я помню, с размером ibdata вообще ничего нельзя сделать, кроме полного пересоздания базы (дамп - очистка - восстановление).

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

Эм.. А транзакции как же?

А оно похоже не использует транзакции.

Что-то мне подсказывает, что он отвалится гораздо раньше по таймауту...

Да, софт просто объявил о том, что с базой что-то не так.

[iGrok]

А оно похоже не использует транзакции.

Так разве ж это проблема базы?
Кривая архитектура приложения - это наше всё.