Есть вот база данных, в которую очень активно пишутся данные, и так же активно удаляются, пример 1-2кк записей за сутки по ней проходит. Проблема в том, что через некоторое время таблица становится сильно фрагментированной и нужно делать оптимизацию, чтобы вернуть неиспользуемое дисковое пространство. Проблема в том, что оптимизация этой таблице происходит достаточно долго, даже на ССД дисках, а нужно 100% доступность данных всегда. Сделал партиционирование, но даже тут пока не нашел способов решения проблемы, думал может можно как-то отдельные партишены выводить из работы и делать с ними что захочется, но нифига...
Партиционирование осуществляется по дням месяца, и обычно нужны данные за сегодня и вчера, поэтому остальные партишены можно было бы и оптимизировать, но вся база при этом все равно лочится...

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

-rw-rw---- 1 mysql mysql 201326592 Jan 16 09:24 /var/lib/mysql/db/fh_download#P#p15.ibd
-rw-rw---- 1 mysql mysql 385875968 Jan 16 09:25 /var/lib/mysql/db/fh_download#P#p16.ibd

удаляем 1000 строк из 15-ой секции:

-rw-rw---- 1 mysql mysql 201326592 Jan 16 09:31 /var/lib/mysql/db/fh_download#P#p15.ibd
-rw-rw---- 1 mysql mysql 390070272 Jan 16 09:31 /var/lib/mysql/db/fh_download#P#p16.ibd

вуаля....

Возможно если удалить все данные из партиции и потом сделать оптицизацию ее, то это будет быстро... надо попробовать

miksoftЕсть TRUNCATE PARTITION и DROP PARTITION.
Что именно вы используете?

Я честно говоря еще только разбираюсь в партишенах и даже не знал про TRUNCATE, видимо это как раз то, что нужно. На данный момент данные удалялись просто с помощью DELETE

miksoftHett,

Не надо DELETE, это медленно и печально.
Скорее всего вам нужен DROP PARTITION. Ведь пустая секция потом не нужна?

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

Еще в доках мускуля нашел, что оптимизация партишена нужна только в случае с динамическим размером строки
http://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html
но прям не знаю, у меня они чего-то файлы данных очищались сами...

Я, как понял вообще, OPTIMIZE просто пересоздает таблицу... хотя честно говоря я им никогда не пользовался, т.к. он какой-то бесмысленный, т.к. блокирует всю таблицу и на долго...

В общем поимел геморою с этим партиционированием.
Почти сутки все работало хорошо, но потом вдруг сервер показал нагрузку 800%
В SHOW PROCESSLIST было очень много запросов к этой таблице в стадии 'Sending data', перезапуски мускуля и т.п. потуги ничего не дали, в итоге пришлось закомментировать пару маловажных запросов в коде, после чего сервер БД поднялся с колен и продолжал работу с нагрузкой в пару раз больше чем обычно. Потом убрал партиционирование и все заработало нормально.... Что это было? Боюсь теперь даже его использовать. И оттестить то не оттестишь нормально, сутки вот проработало ведь.

Причем закомеентированные вопросы 100% использовали индекс (составной из 4х полей, они просто проверяли наличие данных в таблице, по сути ничего не выбирали там особо)

miksoftHett,

Хорошо бы было включить slow query log и посмотреть запросы с "затыками".
Дело в том, что я их просто в SHOW PROCESSLIST видел, они не то чтобы долго выполнялись, они вообще просто висели не выполнялись, это было видно по времени

netwindHett, ну раз посоветовали включить, почему бы не включить?
ответ на вопрос "что это было?" - это была непродуманная политика сбора информации о производительности сервера.

Не знаю тогда как продумывать, обычно нагрузка 100% (исходя из 8 ядер процессора, нужно считать что это 1/8), потом в один прекрасный момент нагрузка становится 800, данные вообще не обрабатываются, все висит, не понятно что происходит. Как нужно было продумывать я не знаю. Я конечно мог бы предоложить, что в какой-то момент не хватило кэша процессора и все встало, но тогда хоть как-то данные бы должны были обрабатываться, а оно вообще все стояло ступором...

netwindHett, ну как продумывать? заранее продумывать !
так slow log не был включен ?
Если нет информации, то и нет идей. А предположений может быть слишком много. Там еще и наверняка у вас не только mysql крутится.

Только MySQL. Лог был выключен на тот момент. Но я не знаю, чтобы он мог дать, я во время проявления проблемы смотрел какие запросы висят в обработке, но чем бы это могло помочь?