Увеличение медленной вставки данных в mySQL

Question

Фон:

У нас большие плоские файлы размером около 60 ГБ, которые мы вставляем в базу данных. Мы наблюдаем постепенное снижение производительности во время установки.

• У нас есть 174 (миллион) записей, и мы ожидаем, что еще 50 (миллион) будут вставлены
• Мы разбили основную таблицу на 1000+ таблиц на основе первых двух-двух символов имени объекта, например, entity_aa, entity_ab ... entity_zz
• Во время каждой вставки выполняется три запроса: (а) поиск по диапазону в другой таблице,(б) проверка, вставлена ​​ли запись или нет (в) вставка в таблицу подробностей (entity_briefs)
• Мы добавили entity_briefs для обработки частых поисковых запросов, но поняли, что после вставки в базу данных она постепенно замедляется, независимо от того, будет ли мы ALTER TABLE entity (или entity_briefs) DISABLE (или ENABLE) KEY.
• Машина имеет 4 процессора, гигабайт дискового пространства, 2 ГБ оперативной памяти. Операционная система Linux CentOS (5.4) 32bit
• Мы обнаружили, что не все 4 процессора используются
• Мы запустили 4 сценария импорта одновременно, но общая производительность неудовлетворительная

Проблемная таблица

CREATE TABLE `entity_briefs` (
`entity_brief_id` bigint(11) NOT NULL auto_increment,
`entity_id` bigint(11) default NULL,
`entity_table_prefix` char(2) default NULL,
`string_1` varchar(255) default NULL,
`string_2` varchar(255) default NULL,
`zip` varchar(25) default NULL,
`phone` bigint(11) default NULL,
PRIMARY KEY  (`entity_brief_id`),
KEY `idx_entity_id` (`entity_id`),
KEY `idx_entity_table_prefix` (`entity_table_prefix`),
KEY `idx_zip` (`zip`),
KEY `idx_string_1` (`string_1`),
KEY `idx_string_2` (`string_2`),
KEY `idx_phone` (`phone`)
);

Вывод mysqltuner.pl:

 >>  MySQLTuner 1.1.1 - Major Hayden <[e-mail]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: xxxxx
Please enter your MySQL administrative password:xxxxx

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.85-community
[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: 101M (Tables: 1344)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 1

-------- Security Recommendations  -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'xxxx'@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 15h 53m 55s (2M q [4.395 qps], 9K conn, TX: 1B, RX: 425M)
[--] Reads / Writes: 51% / 49%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.3G (67% of installed RAM)
[OK] Slow queries: 0% (9/2M)
[OK] Highest usage of available connections: 1% (5/500)
[!!] Key buffer size / total MyISAM indexes: 8.0M/105.3M
[!!] Key buffer hit rate: 94.1% (72M cached / 4M reads)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 7% (101 on disk / 1K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 277K opened)
[OK] Open file limit used: 0% (127/18K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] Connections aborted: 38%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    key_buffer_size (> 105.3M)
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    table_cache (> 64)

Требование: чтобы ускорить вставку, какую стратегию оптимизации можно использовать?

Answer 1

Несколько общих предложений, поскольку у меня нет для вас серебряной пули:

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

Если что-то замедляется, а процессор не привязан, то вы, вероятно, связаны с доступом к базе данных. Если вы обнаружите, что это так, вы можете попробовать более быстрые накопители, Raid 0, более быстрые контроллеры накопителей и т. Д. Возможно, вы даже захотите создать базу данных на твердотельном накопителе и затем скопировать ее после создания на традиционный жесткий диск. привод. Они должны быть намного быстрее для поведения произвольного доступа, которое вы можете ожидать от mysql в файловой системе, хотя я понимаю, что вы со временем "изнашиваете" их. Тем не менее, вы можете получить Терабайт Твердотельного хранилища менее чем за 10 тысяч долларов.

Также внимательно посмотрите на оптимизацию процедуры вставки. Отключение индексов во время вставок, как вы упомянули, хотя и не остановит постепенное замедление, должно значительно ускорить общую процедуру. Я беру из вашего описания, что у вас есть какая-то логика сценария вставки, которая выбирает и вставляет, а не простая ЗАГРУЗКА плоского файла. Вы делаете три разных запроса на одну вставку, возможно, многократно отключая данные между вашим клиентом и базой данных. Особенно обратите внимание на выборочный диапазон и убедитесь, что один только этот запрос не имеет плохих характеристик производительности по размеру таблицы.

Другая возможность может заключаться в том, чтобы израсходовать намного больше оперативной памяти и использовать ее в качестве дискового кэша. Если та "другая таблица", в которой вы используете эти диапазоны, не изменяется во время вашего insertfest, возможно, вы можете получить это в памяти, чтобы сократить поиск диска, если вы определите, что время поиска действительно ограничивает производительность.