Как избежать ремонта с помощью Keycache?

У меня был некоторый опыт оптимизации файла my.cnf, но у моей базы данных около 4 миллионов записей (MyISAM). Я пытаюсь восстановить из mysqldump, но каждый раз, когда я это делаю, я получаю страшный "Repair With Keycache", который может занять несколько дней. Есть ли способ преодолеть это, и пусть он рулон как "Восстановить по сортировке"?

У меня 2 ГБ оперативной памяти, два ядра, много дополнительного пространства на жестком диске.

Вывод из my.cnf:

set-variable = max_connections=650
set-variable = key_buffer=256M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer_size=2M
set-variable = read_buffer_size=2M
set-variable = query_cache_size=32M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
set-variable = thread_concurrency=8

Ответ 1

"Восстановление путем сортировки" использует процедуру filesort, которая, в свою очередь, создает несколько временных файлов (обычно) в вашем tmpdir.

Если вашему tmpdir не хватает места для них, он вернется к разделу "Восстановление по ключевым словам". Это очень плохо, поскольку он намного медленнее И создает менее оптимальные индексы.

Есть и другие условия, но я их не идентифицировал.

Разработка размера tmpdir, необходимого для filesort(), нетривиальна; данные формата хранятся в буфере filesort, не совпадают с MYD файлами, он обычно использует намного больше места.

Итак, если ваш tmpdir указывает на небольшой /tmp (или tmpfs), вы можете изменить его на более крупный /var/tmp - если это существует.

Ответ 2

MySQL будет использовать ремонт keycache для таблиц MyISAM всякий раз, когда максимально возможный размер индексов таблиц больше значения для переменной myisam_max_sort_file_size.

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

Увеличьте myisam_max_sort_file_size, и ваш индекс будет перестроен с помощью сортировки на диске, а не с помощью метода медленного keycache.

Ответ 3

Я случайно запустил таблицу ремонта в новой базе данных, которую я не настроил для быстрой регистрации. myisam_max_sort_file_size, который был слишком мал по сравнению с файлом .MID(это 88279393280 байтов большой, около 88 ГБ). Файл данных - 85 ГБ. Таблица составляет 1,2 миллиарда записей, состоящих из идентификатора, двух дат, крошечного текста, нескольких биноклей и двойника. Мой сервер (виртуальный Linux-сервер 2GB, работающий в окне под Windows7) имеет только одно ядро 4 на сервере Windows, но он работает с 3+ GHZ. Я боялся, что этот "ремонт по ключевому кэшу" произойдет навсегда - учитывая ужасные истории с гораздо меньшими таблицами.

К счастью, "только" занял 1 день, 10 часов и 20,72 секунды, чтобы быстро выполнить операцию ремонта.

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

Теперь я изменил файл my.ini и дважды проверил с df, что у меня достаточно места на диске для этих больших временных файлов.

В любом случае.. мой главный момент, который может быть очень полезным знанием для следующего парня, который попадает в эту ловушку.. на самом деле... не паникуйте! это может быть медленным, но на довольно субординированном оборудовании можно получить 1 + миллиард записей, отсортированных в течение дня или двух. Получено три индекса: один в поле даты, один в поле bigint и один первичный в поле ID.

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

Изменить: И теперь таблица ремонта в той же базе данных, но с достаточно большим значением mysiam_max_sort_file_size, заняла 10 часов, 20 минут, используя ремонт путем сортировки. Самое используемое дисковое пространство было около 250 ГБ, но я установил myisam_max_sort_file_size намного выше, что отражает объем свободного места на сервере на сервере.

Отслеживание прогресса затруднено. Дисковое пространство поднималось и опускалось, когда отдельные индексы были построены, но были часовые паузы, где не было изменений. дискового пространства (как сообщает df).

Ответ 4

Спасибо, Марк, да, это именно то, что я пытался попробовать и вижу из журналов, что причина, по которой он переключился на "Ремонт с помощью keycache", была ошибкой вне пространства.

Это то, что я сделал, чтобы получить свое решение, поскольку я не буду учитывать тот факт, что он указывал на /tmp/mysqltmp/, у которого было максимум 2 МБ.

Итак, я сделал это:

mkdir /home/mysqltmp

chown mysql:mysql /home/mysqltmp

изменил мой tmp файл в my.conf на tmpdir=/home/mysqltmp/

Теперь, если я использую df -h /home/mysqltmp, я вижу, что у dir имеется 285 ГБ, поэтому на самом деле это было неплохое зрелище, у которого было много свободного места, плюс я мог видеть, что mysql просто хочет 20GB. Итак, что заставило меня за 12 часов до этого завершить за 20 минут, то есть более 3 миллионов записей вставить в индекс.

Ответ 5

Согласно Справочному руководству по MySQL, дисковое пространство должно быть доступно "в файловой системе, содержащей каталог, в котором находится исходный индексный файл" (http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_max_sort_file_size) - это относится к (по крайней мере) версии 5.0 и выше. Это противоречит некоторым из приведенных выше ответов, в которых утверждается, что увеличение дискового пространства для каталога tmp поможет.

Я могу подтвердить поведение, описанное в справочном руководстве: используется временное пространство на диске, где хранятся данные таблицы (*.MYD) и файлы индекса (*.MYI), но не в tmpdir.

Ответ 6

Ни одно из решений здесь не работало для меня: независимо от того, насколько я увеличил переменную myisam_sort_buffer_size или где я сделал переменную point tmpdir, таблица всегда исправлялась с помощью keycache.

Что работало с использованием утилиты командной строки myisamchk:

myisamchk --sort-recover --sort_buffer_size=14G /path/to/table

где:

/path/to/table - это путь к файлу базы данных без его расширения (поэтому без .MYI в конце). Он по умолчанию находится в каталоге /var/lib/mysql/your_database.
Измените размер буфера от 14G на любое свободное пространство, которое у вас есть.

В качестве дополнительного бонуса он также отображает текущий прогресс, когда он сбрасывает данные.