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

Разговоры на любые темы: вы можете обсудить здесь какой-либо сайт, найти единомышленников или просто пообщаться...
Proxy
Профессор VB наук
Профессор VB наук
Аватара пользователя
 
Сообщения: 2941
Зарегистрирован: 31.08.2007 (Пт) 4:41

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

Сообщение Proxy » 23.05.2013 (Чт) 16:50

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

Есть некий сервер под управлением 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. Заметил, что не очень ясно обрисовал картину: СУБД и софт, который её использует, работают на одном физическом сервере. Можно базу перетащить на другой сервер, если потребуется.
Последний раз редактировалось Anonymous 23.05.2013 (Чт) 17:36, всего редактировалось 1 раз.
Follow the white rabbit.

Хакер
Телепат
Телепат
Аватара пользователя
 
Сообщения: 16473
Зарегистрирован: 13.11.2005 (Вс) 2:43
Откуда: Казахстан, Петропавловск

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

Сообщение Хакер » 23.05.2013 (Чт) 17:21

Так просто разбивать свои посты на абзацы вставками из одной пустой строки. Но это в 8 раз поднимает желание прочитать пост до конца.
—We separate their smiling faces from the rest of their body, Captain.
—That's right! We decapitate them.

Proxy
Профессор VB наук
Профессор VB наук
Аватара пользователя
 
Сообщения: 2941
Зарегистрирован: 31.08.2007 (Пт) 4:41

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

Сообщение Proxy » 23.05.2013 (Чт) 17:38

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

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

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

Сообщение iGrok » 23.05.2013 (Чт) 17:49

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

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

Proxy
Профессор VB наук
Профессор VB наук
Аватара пользователя
 
Сообщения: 2941
Зарегистрирован: 31.08.2007 (Пт) 4:41

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

Сообщение Proxy » 24.05.2013 (Пт) 17:30

iGrok писал(а):А какой запрос тормозит?

Даже Select по дате.
iGrok писал(а):Чтобы удаление работало быстрее, нужно задавать условия по индексу.

Нет никаких гарантий, что везде соблюдалась хронология по индексам. поэтому удалял по дате. Мало ли что с этой базой делали до меня.
iGrok писал(а): Что говорит EXPLAIN тормозящего запроса?

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

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

Qwertiy
Доктор VB наук
Доктор VB наук
 
Сообщения: 2753
Зарегистрирован: 26.06.2011 (Вс) 21:26

Сообщение Qwertiy » 25.05.2013 (Сб) 11:00

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

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

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

Сообщение iGrok » 25.05.2013 (Сб) 13:59

Proxy писал(а):Не пришло в голову, посмотрю.

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

Proxy писал(а):Даже Select по дате.

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

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

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

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

Proxy
Профессор VB наук
Профессор VB наук
Аватара пользователя
 
Сообщения: 2941
Зарегистрирован: 31.08.2007 (Пт) 4:41

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

Сообщение Proxy » 07.06.2013 (Пт) 12:56

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

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

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

Сообщение iGrok » 07.06.2013 (Пт) 14:48

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

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

Proxy
Профессор VB наук
Профессор VB наук
Аватара пользователя
 
Сообщения: 2941
Зарегистрирован: 31.08.2007 (Пт) 4:41

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

Сообщение Proxy » 07.06.2013 (Пт) 17:16

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

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

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

Qwertiy
Доктор VB наук
Доктор VB наук
 
Сообщения: 2753
Зарегистрирован: 26.06.2011 (Вс) 21:26

Сообщение Qwertiy » 07.06.2013 (Пт) 17:52

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

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

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

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

Сообщение iGrok » 07.06.2013 (Пт) 21:01

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

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

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

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

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

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

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

Qwertiy
Доктор VB наук
Доктор VB наук
 
Сообщения: 2753
Зарегистрирован: 26.06.2011 (Вс) 21:26

Сообщение Qwertiy » 07.06.2013 (Пт) 22:04

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

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

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

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

Сообщение iGrok » 07.06.2013 (Пт) 23:08

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

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

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

Proxy
Профессор VB наук
Профессор VB наук
Аватара пользователя
 
Сообщения: 2941
Зарегистрирован: 31.08.2007 (Пт) 4:41

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

Сообщение Proxy » 08.06.2013 (Сб) 4:49

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

О том и речь. Дефрагментация в одном единственном файле никак не спасает от избыточного размера, максимум позволит базе чуток побыстрее работать.
Qwertiy писал(а):Эм.. А транзакции как же?

А оно похоже не использует транзакции.
Qwertiy писал(а):Что-то мне подсказывает, что он отвалится гораздо раньше по таймауту...

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

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

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

Сообщение iGrok » 08.06.2013 (Сб) 14:08

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

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


Вернуться в Народный треп

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 11

    TopList