имейте проблему здесь того, как настроить mysql (myisam) правильно для объемной вставки (данные загрузки infile), чтобы быть выполненными быстро.
Существует текстовый файл на 6 Гбит, который будет импортирован, 15 млн строк, 16 столбцов (некоторый интервал, некоторый varchar (255), один varchar (40), один символ (1) некоторая дата и время, один mediumtext).
относительные my.conf настройки:
key_buffer = 800M
max_allowed_packet = 160M
thread_cache_size = 80
myisam_sort_buffer_size = 400M
bulk_insert_buffer_size = 400M
delay_key_write = ON
delayed_insert_limit = 10000
Существует три индекса - один основной (autincrement интервал), один уникальный интервал и один уникальный varchar (40).
Проблема состоит в том, что после выполнения данных загрузки infile команда, первые 3 концерта данных импортируются быстро (на основе увеличивающегося размера table.myd - 5-8 МБ/с), но uppon, пересечение 3 020 Мбит ограничивает уменьшения скорости импорта значительно - размер table.myd, выращивает 0,5mb/s. Я заметил, что процесс импорта замедляется на Key_blocks_unused
истощен к нулю. Это вывод mysql> show status like '%key%';
в начале импорта:
mysql> show status like '%key%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 0 |
| Key_blocks_not_flushed | 57664 |
| Key_blocks_unused | 669364 |
| Key_blocks_used | 57672 |
| Key_read_requests | 7865321 |
| Key_reads | 57672 |
| Key_write_requests | 2170158 |
| Key_writes | 4 |
+------------------------+---------+
10 rows in set (0.00 sec)
и это что, как это заботится о пределе 3020 МБ, т.е. когда key_blocks_unused
спускается для обнуления, и именно тогда объемный процесс вставки становится действительно медленным:
mysql> show status like '%key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 0 |
| Key_blocks_not_flushed | 727031 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 727036 |
| Key_read_requests | 171275179 |
| Key_reads | 1163091 |
| Key_write_requests | 41181024 |
| Key_writes | 436095 |
+------------------------+-----------+
10 rows in set (0.00 sec)
Проблема довольно ясна к моему пониманию - индексы хранятся в кэше, но после того как кэш заполняет, индексы записаны в диск один за другим, который является медленным, поэтому весь процесс замедляется. Если я отключаю уникальный индекс на основе varchar (40) столбец и, поэтому, все индексы вписываются Key_blocks_used
(я предполагаю, что это - переменная непосредственно иждивенец на key_buffer
,не так ли?), весь групповой импорт успешен. Так, мне любопытно, как заставить mysql поместить весь Key_blocks_used
данные к диску сразу, и свободный Key_blocks_used
?. Я понимаю, что это могло бы делать некоторую сортировку на лету, но тем не менее, я предполагаю, что это должно быть доступно, чтобы сделать некоторую кэшируемую синхронизацию Электронного диска для успешного управления индексами, даже когда они все не вписываются в кэш памяти. Таким образом, мой вопрос, "как настроить mysql так, чтобы объем, вставляющий, постарался бы не писать в диск на (почти) каждом индексе, даже когда все индексы не вписываются в кэш?" в последний раз не в последнюю очередь - delay_key_write установлен на 1 для данной таблицы, хотя он не добавил ускорения, по сравнению с тем, когда он был отключен.
Спасибо за любые мысли, идеи, объяснения и RTMs заранее! (:
Еще один небольшой вопрос - как я вычислил бы, сколько varchar (40) индексы впишется в кэш прежде Key_blocks_unused
добирается до 0?
P.S. отключение индексов с $myisamchk --keys-used=0 -rq /path/to/db/tbl_name
и затем перевключение их с $myisamchk -rq /path/to/db/tbl_name
, как описано в Mysql документы являются известным решением, которое работает, но только когда вставка объема в пустую таблицу. То, когда уже существуют некоторые данные в таблице, индексная проверка уникальности необходима, поэтому отключение индексирует, не является решением.
Когда вы импортируете данные с помощью "load data infile", я думаю, что mysql выполняет вставку одну за другой, и с каждой вставкой он также пытается обновить индексный файл .MYI, и это может замедлить вниз ваш импорт, поскольку он потребляет ресурсы ввода-вывода бота и ресурсы ЦП для каждой отдельной вставки.
Что вы можете сделать, так это добавить 4 файла в свой файл импорта, чтобы отключить ключи вашей таблицы и включить его в конце оператора вставки и вы должны увидеть разницу.
LOCK TABLES tableName WRITE;
ALTER TABLE tableName DISABLE KEYS;
----
your insert statement from go here..
----
ALTER TABLE tableName ENABLE KEYS
UNLOCK TABLES;
Если вы не хотите редактировать файл данных, попробуйте использовать mysqldump, чтобы получить правильный файл дампа, и вы не должны столкнуться с такой медлительностью с данными импорта.
##Dump the database
mysqldump databaseName > database.sql
##Import the database
mysql databaseName < database.sql
Надеюсь на это помогает!
Я не уверен, что key_buffer
, который вы упоминаете, совпадает с key_buffer_size
.
Я столкнулся с подобной проблемой. Моя проблема была решена путем увеличения значения key_buffer_size
примерно до 1 ГБ. Проверьте мой вопрос здесь .