|
Задавайте вопросы, мы ответим
Вы не зашли.
Добрый день.
Помогите, пжл, ускорить выполнение операции OPTIMIZE TABLE.
Исх.данные:
Linux, 2Гб RAM.
БД MySql 5.1 MyISAM, одна из таблиц с миним. кол-вом записей ~2млн, размер таблицы ~2Гб, размер индексов к таблице ~1Гб.
Ежедневно в таблицу добавляется не менее 40тыс. записей.
Ежедневно раз в сутки запускается процесс очистки таблицы от старых (уже ненужных по некоторым критериям) данных, тоже порядка 40тыщ записей.
Процесс очистки выполняется с помощью Event Scheduler, который вызывает процедуру, удаляющую записи в таблице.
Сам процесс удаления выполняется за 5 секунд.
После удаления записей в процедуре выполяется оператор OPTIMIZE TABLE, который выполняется 7 минут!
Возможно ли и как можно ускорить выполнение этой операции?
Настройки сервера:
[mysqld]
event_scheduler = on
concurrent_insert = 2
key_buffer = 256M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 5000
query_cache_limit = 1M
query_cache_size = 32M
[isamchk]
key_buffer = 16M
Результат выполнения утилиты mysqltuner:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 1270)
[--] Data in InnoDB tables: 14M (Tables: 850)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 980
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 17h 2m 37s (23M q [58.802 qps], 3K conn, TX: 3B, RX: 1B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 314.0M global + 2.7M per thread (5000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 13.7G (793% of installed RAM)
[OK] Slow queries: 0% (2/23M)
[OK] Highest usage of available connections: 1% (76/5000)
[OK] Key buffer size / total MyISAM indexes: 256.0M/1.1G
[OK] Key buffer hit rate: 99.3% (848M cached / 5M reads)
[!!] Query cache efficiency: 0.0% (12K cached / 110M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[OK] Temporary tables created on disk: 18% (399 on disk / 2K total)
[OK] Thread cache hit rate: 95% (154 created / 3K connections)
[!!] Table cache hit rate: 0% (64 open / 12K opened)
[OK] Open file limit used: 0% (104/25K)
[OK] Table locks acquired immediately: 99% (34M immediate / 34M locks)
[!!] InnoDB data size / buffer pool: 14.9M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 1M, or use smaller result sets)
table_cache (> 64)
innodb_buffer_pool_size (>= 14M)
---------------------------------------------------------------------------------
Заранее спасибо.
Отредактированно sha (05.03.2012 20:18:40)
Неактивен
Способ ускорения OPTIMIZE TABLE мне неизвестен.
Неактивен
Ну разве что добавить key_buffer / sort_buffer в оптимизирующий процесс.
Как вариант — оптимизировать копию таблицы, а потом заменять (RENAME).
Неактивен
paulus написал:
Ну разве что добавить key_buffer / sort_buffer в оптимизирующий процесс.
Как вариант — оптимизировать копию таблицы, а потом заменять (RENAME).
Спасибо за оперативный ответ.
Пробовала ранее сделать вот так в теле процедуры:
delete from....
SET SESSION myisam_sort_buffer_size = 512 * 1024 * 1024;
OPTIMIZE TABLE table_name;
Только это нисколько не изменило время исполнения.
Это оно?
Предлагаете еще добавить и к key_buffer? Попробую.
С копированием мысль интересная, но во-1 таблица все время обновляется, во-2 2Гб копировать...
Есть какой-то способ сделать это быстрее чем за 7 минут?
Отредактированно sha (06.03.2012 00:50:43)
Неактивен
Хм, у меня сложилось впечатление, что таблица меняется один раз в день
заменой этих 40к строк, а оптимизируете Вы для увеличения скорости
доступа. Если постоянно меняется — конечно, не подойдет. Можно оптимизи-
ровать реже. Вряд ли Вы выигрываете значительный прирост производи-
тельности, перетряхивая базу, изменившуюся на 2%, каждый день.
Неактивен
paulus, а существуют вообще какие-то критерии/рекомендации на тему того, насколько регулярно и как часто стоит делать optimize/analyze table? Существует ли какая-то (возможно эвристически подобранная) цифра n, такая, что если из таблицы удалили n% записей или, допустим, добавили столько же - что после этого стоит прооптимизировать таблицу?
Неактивен
Насколько я знаю, нет. Стандартная рекомендация на курсах — «порядка 50%». Единственное,
что может испортиться в табличке, — это оценка cardinality индекса. Соответственно, надо исхо-
дить из того, какая погрешность нас устраивает. Очевидно, обычно устраивает такая, которая
не мешает оптимизатору выбрать правильный план выполнения запроса. Для простых запросов,
следовательно, можно вообще никогда не анализировать таблицу. Для более сложных (например,
WHERE a=xxx AND b=yyy при наличии отдельных индексов на (a) и (b)) — нужно будет делать
выбор, и тут уже статистика будет влиять. В реальных случаях обычно при этом одно из полей
принимает всегда очень небольшой набор значений, и выбор очевиден (и тогда тоже можно ана-
лизировать редко).
Фу, наспамил. Надеюсь, никого не напугал 
Неактивен
sha, скорее всего вам мешает не сам OPTIMIZE TABLE, а последствие блокировки таблицы на 7 минут.
В этом случае может помочь инструмент oak-online-alter-table выполняющий действия на копии, соответственно нет блокировки на таблицу.
С этим инструментом работал, работает стабильно, но именно с OPTIMIZE TABLE не проверял.
Отредактированно evgeny (06.03.2012 13:14:45)
Неактивен
Спам был настолько скрытым, что никто не догадался. Но мы никому не скажем 
.
Ну да, я cardinality и имел в виду в вопросе. Спасибо !
Неактивен
evgeny, а как оно работает? Я всегда думал, что такие штуки делают insert into
othertable select * from thistable, что эффективно вешает блокировку на первую
таблицу на время копирования.
Неактивен
paulus написал:
Хм, у меня сложилось впечатление, что таблица меняется один раз в день
заменой этих 40к строк, а оптимизируете Вы для увеличения скорости
доступа. Если постоянно меняется — конечно, не подойдет. Можно оптимизи-
ровать реже. Вряд ли Вы выигрываете значительный прирост производи-
тельности, перетряхивая базу, изменившуюся на 2%, каждый день.
Да, на базе данных держится риалтайм система, постоянные инсеты/апдейты.
Вставок в таблицу не менее 40тыщ в сутки, в особо загруженные дни до 300тыщ новых строк.
Совет реже перетряхивать приняла, учитывая максимум в 300тыщ, получается, что нормально будет, если оптимайзить не чаще чем раз в 3 дня.
Отредактированно sha (06.03.2012 14:32:37)
Неактивен
evgeny написал:
sha, скорее всего вам мешает не сам OPTIMIZE TABLE, а последствие блокировки таблицы на 7 минут.
В этом случае может помочь инструмент oak-online-alter-table выполняющий действия на копии, соответственно нет блокировки на таблицу.
С этим инструментом работал, работает стабильно, но именно с OPTIMIZE TABLE не проверял.
Совершенно верно, конечно, мешает блокировка таблицы на 7 минут, а не сам OPTIMIZE TABLE )
Вот спасибо за ссылку, отправляюсь читать.
Я предполагаю, что это внешняя утилита, из тела процедуры ее не вызовешь, да? Было бы удобнее повесить это на саму БД в EVENT-e, но при отсутствии других вариантов, конечно, воспользуюсь продуктом.
Неактивен
paulus написал:
evgeny, а как оно работает? Я всегда думал, что такие штуки делают insert into
othertable select * from thistable, что эффективно вешает блокировку на первую
таблицу на время копирования.
В общем работает по принципу :
1. Добавление триггера к таблице. Содержание триггера : писать все изменения в специальный лог.
2. Копирование таблицы во временную таблицу.
3. Выполнение операций на временной таблице.
4. Закрытие главной таблицы, синхронизация записей накопившихся в логе, переименование таблиц, удаление лог и триггера.
Что касается самого копирования, то точно не знаю ...
Копируется как то так без блокировки таблицы ... Способы могут быть разные, например генерацией большого балк инсерта.
Неактивен
sha написал:
evgeny написал:
sha, скорее всего вам мешает не сам OPTIMIZE TABLE, а последствие блокировки таблицы на 7 минут.
В этом случае может помочь инструмент oak-online-alter-table выполняющий действия на копии, соответственно нет блокировки на таблицу.
С этим инструментом работал, работает стабильно, но именно с OPTIMIZE TABLE не проверял.Совершенно верно, конечно, мешает блокировка таблицы на 7 минут, а не сам OPTIMIZE TABLE )
Вот спасибо за ссылку, отправляюсь читать.
Я предполагаю, что это внешняя утилита, из тела процедуры ее не вызовешь, да? Было бы удобнее повесить это на саму БД в EVENT-e, но при отсутствии других вариантов, конечно, воспользуюсь продуктом.
Обычный perl скрипт, запускается по крону, командой из линукса.
Так как у вас там не маленькая таблица, возможно вся процедура возьмёт не мало времени, но главное что таблица не будет заблокирована.
Неактивен
paulus написал:
evgeny, а как оно работает? Я всегда думал, что такие штуки делают insert into
othertable select * from thistable, что эффективно вешает блокировку на первую
таблицу на время копирования.
Наверное, что-то типа "горячего бэкапа" без блокировки таблицы...
А вообще, если б можно было внутри процедурки скопировать без блокировки, по типу горячего бэкапа, потом оптимайзить вновь созданную таблицу за время Х, а по завершению процесса наложить лок на начальную таблицу и (за время Y) скопировать из старой в новую всё, что легло за время Х, дальше ринэйм и анлок. Тогда б время лока таблицы было только на время Y.
Но это фантастика... хотя бы только поэтому:
" Stored routines cannot contain arbitrary SQL statements. The following statements are not permitted: The locking statements LOCK TABLES and UNLOCK TABLES."
Так, что отправляюсь читать oak-online-alter-table.
Неактивен
evgeny написал:
sha написал:
evgeny написал:
sha, скорее всего вам мешает не сам OPTIMIZE TABLE, а последствие блокировки таблицы на 7 минут.
В этом случае может помочь инструмент oak-online-alter-table выполняющий действия на копии, соответственно нет блокировки на таблицу.
С этим инструментом работал, работает стабильно, но именно с OPTIMIZE TABLE не проверял.Совершенно верно, конечно, мешает блокировка таблицы на 7 минут, а не сам OPTIMIZE TABLE )
Вот спасибо за ссылку, отправляюсь читать.
Я предполагаю, что это внешняя утилита, из тела процедуры ее не вызовешь, да? Было бы удобнее повесить это на саму БД в EVENT-e, но при отсутствии других вариантов, конечно, воспользуюсь продуктом.Обычный perl скрипт, запускается по крону, командой из линукса.
Так как у вас там не маленькая таблица, возможно вся процедура возьмёт не мало времени, но главное что таблица не будет заблокирована.
Да, это для меня главное.
Спасибо за развернутое объяснение механизма.
Обязательно попробую.
Неактивен
Я не верю в магию. Она будет заблокирована минимум на время копирования 2 гигабайт
данных в сторону. Это будет меньшее время, но я не могу представить себе, как оно может
отработать на MyISAM без блокировок вообще.
Неактивен
paulus написал:
Я не верю в магию. Она будет заблокирована минимум на время копирования 2 гигабайт
данных в сторону. Это будет меньшее время, но я не могу представить себе, как оно может
отработать на MyISAM без блокировок вообще.
Ну почему магия ?
Я же привёл простой пример "например генерацией большого балк инсерта"
Это же делает perl скрипт, то есть копирование не обязано быть одним SQL вырожением.
Если сильно интересно я могу этот нюанс проверить, и написать ...
Неактивен
evgeny написал:
paulus написал:
Я не верю в магию. Она будет заблокирована минимум на время копирования 2 гигабайт
данных в сторону. Это будет меньшее время, но я не могу представить себе, как оно может
отработать на MyISAM без блокировок вообще.Ну почему магия ?
Я же привёл простой пример "например генерацией большого балк инсерта"
Это же делает perl скрипт, то есть копирование не обязано быть одним SQL вырожением.
Если сильно интересно я могу этот нюанс проверить, и написать ...
Конечно очень интересно) просим)
Неактивен
paulus написал:
Я не верю в магию. Она будет заблокирована минимум на время копирования 2 гигабайт
данных в сторону. Это будет меньшее время, но я не могу представить себе, как оно может
отработать на MyISAM без блокировок вообще.
Проверил.
Копирует с помощью INSERT ... SELECT ... , а магия заключается в делении на кусочки, то есть сами блокировки есть но они маленькие и не заметные.
В логах вижу много INSERT ... SELECT .. запросов выбирающих по определённым диапазонам.
Ну а для sha, плохая новость OPTIMIZE TABLE не поддерживается.
Слышал про существование аналогичного скрипта на PHP, разработанного by FaceBook Team. Возможно там предусмотрен OPTIMIZE
Неактивен
ALTER TABLE ENGINE=MyISAM в данном случае будет иметь тот же эффект,
поэтому попробовать таки можно.
Неактивен
evgeny написал:
Ну а для sha, плохая новость OPTIMIZE TABLE не поддерживается.
Слышал про существование аналогичного скрипта на PHP, разработанного by FaceBook Team. Возможно там предусмотрен OPTIMIZE
Спасибо.
Это ничего, если не поддерживается.
В любом случае всегда можно написать своё аналогичное с поддержкой нужных функций.
Неактивен
paulus написал:
ALTER TABLE ENGINE=MyISAM в данном случае будет иметь тот же эффект,
поэтому попробовать таки можно.
Спасибо за подсказку эквивалентной операции.
Неактивен
