MySQL не выдерживает большое количество данных, как решить проблему?

Question

[vladimirchelyabinskiy]

Всем привет, уже очень давно ищу ответ на свой вопрос по MySQL может тут его найду....

Начнем

У меня на жестком диске имеются файлы данных большого размера, мне нужно вставить эти данные в базу данных MySQL с типом таблиц MyISAM

Всего таблиц в базе данных 16 по 2 столбца в каждой таблице к каждой записи генерируется MD5 хеш в таблицу записывается оригинал строки и его MD5.

Я не знаю, как это сделать поэтому написал программу на C#

Спросите почему и зачем INSERT если можно сделать с помощью LOAD DATA INFILE и MD5() ?

Потому, что я не знаю, как средствами SQL сделать запрос для того что-бы сначала строку преобразовать в md5 а потом по ее первому символу (0123456789ABCDEF) вставить в нужную таблицу: (MD50, MD51, MD5A, MD5F).

Примерное содержание файла: 1.txt

6789876154321987654245667563457
3468679765675423222786658115645
0364345444442355765896679674563
6588067546743345568900044365346

Таких строк в файле может быть от 100 до 500 000 000 а то и больше.
моя программа вставляет в базу данных очень быстро методом INSERT

НО! с каждой новой вставкой скорость падает понемногу и к 50 000 000 падает почти в 0.

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

я даже реализовал эти функции в своей программе но это все не помогло.

Использую базу данных MySQL версии 5.1.73-winx64

Скромные данные моего сервера:

Процессор: Core i5.
Память: 8 GB
Жесткий диск: SATA3 6 TB Hitachi He6 7200rpm (5 штук - 30 терабайт).

Структура 16 таблиц:

CREATE TABLE `MD50` (
`ORIGINAL` CHAR(32),
`MD5Hash` CHAR(32),
PRIMARY KEY (MD5Hash)
) TYPE=MyISAM DELAY_KEY_WRITE=1;

Мои настройки из файла my.cnf

[mysqld]
port = 3306
socket = MySQL
skip-locking
skip-innodb
key_buffer_size = 4000M
table_open_cache = 1024
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
myisam_sort_buffer_size = 512M
myisam_recover = force,backup
query_cache_size = 128M
query_cache_type=1
thread_concurrency = 8
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size = 256
server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Код запроса из моей программы на C#

MySqlCommand insert_command = new MySqlCommand("insert ignore into md5" + md5_file_line[0] + " (original,
md5hash) values ('" + MySqlHelper.EscapeString(file_line) + "', '" + md5_file_line + "')", Settings.mysql_connect);

sql_query_execute = insert_command.ExecuteNonQuery();

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

Comments

[Андрей Панов]

Добрый день.
Ну для начала сделайте пакетную загрузку данных, а то судя по вашему коду вы вставляете по одной записи(сильно поднимет скорость). Хорошо бы отключить индексирование, что бы не пересчитывать индексы каждый раз после вставки новых строк.
П.С.: в ms-sql есть такая вещь как BULK INSERT, которая работает на больших объемах намного быстрее обычного INSERT, думаю что и у MySql что то подобное должно быть

Answer 1

[Пума Тайланд]

поставьте mysql 5.6 там все таки бодрее все и куда лучше работает с большим количеством данных
откажитесь от индекса , он довольно тяжело пересчитывается для мд5

Comments

[vladimirchelyabinskiy]

Согласен, мне всегда казалось что это индекса но как мне от него отказаться? :) и еще в 5.6 по умолчанию движок InnoDB я пробовал и с ним но с ним ужасно медленная скорость по сравнению с myisam там есть другой похожий тип таблиц или myisam?

[Пума Тайланд]

там есть myisam
выбери для индекса что нибудь попроще тот же id сделайте инкрементный будет в сто раз легче ему пересчитывать
а потом как вставите создадите новый индекс на мд5

[vladimirchelyabinskiy]

но у меня нету id у меня 2 поля `ORIGINAL` CHAR(32),
`MD5Hash` CHAR(32),

[vladimirchelyabinskiy]

можете привести пример?

[Пума Тайланд]

ну что вам религия что ли запрещает его сделать ?

[Пума Тайланд]

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

[vladimirchelyabinskiy]

сейчас проверю но думаю что нечего не сработает, спасибо :)

[VBKesha]

@vladimirchelyabinskiy habrahabr.ru/post/66684
тут написано что сделать чтобы InnoDB был не такой уж и тормозной.

[Пума Тайланд]

быстрее myisam он не станет тем более на таком железе и объеме данных.

Answer 2

[lexore]

Так ли нужно использовать myisam?
Есть и другие движки.
en.wikipedia.org/wiki/Comparison_of_MySQL_database...
Я бы на вашем месте посмотрел в сторону движка csv.
Ну или хотя бы не использовать индексы во время вставки.
Индекс всегда можно добавить после вливания данных.

Comments

[vladimirchelyabinskiy]

"не использовать индексы", "Индекс всегда можно добавить после вливания данных." любезнейший не подскажите как?)

[lexore]

CREATE INDEX для обычных индексов ( dev.mysql.com/doc/refman/5.0/en/create-index.html ).
ALTER TABLE для PRIMARY KEY ( dev.mysql.com/doc/refman/5.0/en/alter-table.html ).

[lexore]

У таблицы можно удалить индекс (DROP INDEX), или создать без индекса (без строчки PRIMARY KEY)

Answer 3

[mrstrictly]

Почитайте про LOAD DATA: dev.mysql.com/doc/refman/5.1/en/load-data.html

Comments

[vladimirchelyabinskiy]

Спасибо, да я знаю я изначально его и использовал, а не INSERT но INSERT мне кажется проще и с ним больше возможностей хоть и уступает в скорости, увы это не решает мой вопрос так как если делать туже самую операцию только с LOAD DATA база все равно больше 50 мл записей не проглотит.

[vdem]

Проглотит. И больше глотала.

Answer 4

[vdem]

Я только мельком глянул на задачу, попробуйте так: 1) LOAD_DATA_INFILE в таблицу без всяких там PRIMARY KEY (но поле объявите и заполните MD5 хэшем), 2) ALTER TABLE, устанавливаете PRIMARY KEY. Поскольку это РАЗОВАЯ операция (насколько я понимаю), нет смысла парить My(!!)SQL с переиндексацией после каждой вставки.

Comments

[vladimirchelyabinskiy]

Дело в том что у меня может повторятся строка которая будет вставятся в поле original поэтому я не могу отказаться от индекса поля original и соответственно не могу использовать auto_increment вот такая беда сейчас поставил 5.6.19 посмотрим может что и изменится но это маловероятно так как проблема все же в индексах наверно...

[vdem]

1) чтотоянихренанепонял; 2) Вы пропустили мягкий знак; 3) никто Вам не запрещает вставлять снова те же данные, если только не нарушается требование уникальности.

[vdem]

@vladimirchelyabinskiy Итак, у Вас имеется куча файлов, данные из которых в зависимости от значения вычисленного md5 хэша разносятся по разным таблицам. Я прав или я прав?

[vdem]

@vladimirchelyabinskiy Сделайте ОДИН РАЗ загрузку всех данных в таблицу, без каких-либо преобразований. Просто всех данных вообще. Через LOAD DATA INFILE. В таблице не должно быть никаких ключей. Таблица MyISAM.

[vladimirchelyabinskiy]

Вы прав :)

[vladimirchelyabinskiy]

ну допустим я сделаю потом не нужно будет ко всем строкам сделать md5 и без уникальности не получится так как после некоторого времени будут еще данные уже другие и там могут быть те же строки и они будут повторятся а так у меня primary key (original) и insert IGNORE что бы ошибки Duplicate не выдавал...

[vdem]

1) md5 по неуникальной строке не даст Вам уникального значения.

[vdem]

md5('abcd') === md5('abcd')

[vdem]

Зато md5('348763049867394876394867039487636') вполне может быть === md5('321')

[vladimirchelyabinskiy]

хорошо берем одну строку из файла пусть будет строка 123 заносим ее в базу потом берем другую 1234 тоже заносим потом бац опять строка 123 и она тоже заносится, а она там не нужна она уже ведь есть вот для чего мне нужен primary key(stroka) :)

[vladimirchelyabinskiy]

А как возможно это сделать через LOAD DATA INFILE ? если у меня в файле одна строка и 2 столбца к ней нужен md5 во второй столбец.

[vdem]

Хорошо, пожалуйста скажите, вот у Вас в тех файлах находятся всякие последовательности символов в каждой строке. Вы утверждаете, что они могут повторяться (т.е. или в одном и том же файле, или в разных, попадется одна и та же последовательность символов). Вам необходимо как-то различать эти одинаковые, но разные (!) последовательности символов. Верно? md5() Вам здесь не поможет, потому что для одинаковых последовательностей символов он возвращает одинаковый результат.

[vladimirchelyabinskiy]

нет не так сейчас подробно опишу

[vdem]

@vladimirchelyabinskiy В ПРИВЕДЕННОМ ПРИМЕРЕ ФАЙЛА Я ВИДЕЛ ТОЛЬКО ОДИН СТОЛБЕЦ. Я СЛЕПЕЦ?

[vdem]

@vladimirchelyabinskiy "нет не так сейчас подробно опишу" - вот блеать с этого и надо начинать!!!

[vladimirchelyabinskiy]

MD5 - 16 символов 0123456789ABCDEF, 16 баз - MD50,1,2... и тд.
структура файла:
1.txt
123
1234
12345

берем строку 123 ее MD5 - 202cb962ac59075b964b07152d234b70 она заносится в базу MD52 и так далее по первому символу чтоб потом искать легче было. вы правы может случится так что будет еще одна запись 123 для чего мне и нужно что бы строка была уникальной и не повторялась...

[vdem]

@vladimirchelyabinskiy Ну постарайтесь объяснить мне, как строка "123" может быть уникальной, если может появиться "еще одна запись 123", (внимание!) - "для чего мне и нужно что бы строка была уникальной и не повторялась...". Еще раз, результат вычисления хэш-фукнции md5 для строки "абракадабра" будет равна результату вычисления хэш-функции md5 для строки "абракадабра". Замените в этом комментарии все слова "абракадабра" на "123".

[vdem]

@vladimirchelyabinskiy Т.е. Если Вы будете добавлять не просто несколько одинаковых записей "123", но еще и прилепите к ним md5-хэш по "123", то получите те же одинаковые хэши, что и сами записи.

[vladimirchelyabinskiy]

блиииин ну в том то и дело как только будет INSERT еще одного 123 он просто отсекает его и все в таблице уже не будет одинаковых записей это то что мне и нужно!

[vdem]

@vladimirchelyabinskiy Ну блиииин, а это нельзя было сразу сказать??? *ROFL*

[vladimirchelyabinskiy]

я пытался :D

[vdem]

@vladimirchelyabinskiy 1) LOAD DATA INFILE в таблицу t1 (с одним! столбцом (a_row_from_a_file), который и содержит отдельную строку из файла) без всяких primary key, 2) создаем другую таблицу t2 с primary key по столбцу a_row_from_file, 3) делаем INSERT INTO t2 (a_row_from_a_file) SELECT distinct(a_row_from_a_file) FROM t1;;;;;;;;

[vdem]

@vladimirchelyabinskiy а теперь делаем t3_1, t3_2, ..., t3_f (со столбцами original и hashed_original). Т.е. сначала ALTER TABLE t2 ADD `a_row_from_a_file_hash` varchar(32), потом UPDATE t2 SET a_row_from_a_file_hash = md5(a_row_from_a_file), ну а потом уже 16 запросов вида INSERT INTO t3_x SELECT a_row_from_a_file, a_row_from_a_file_hash FROM t2 WHERE t2.a_row_from_a_file_hash LIKE 'x%';, правда может таки индекс на a_row_from_a_file_hash поставить? всё, я сплю уже

[vladimirchelyabinskiy]

программа вставила 60 мл записей из 110 и встала вылезла ошибка в логах 05:45:26: [-] MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.TimeoutException: Timeout in IO operation

[vdem]

@vladimirchelyabinskiy увеличьте таймаут же

[vdem]

@vladimirchelyabinskiy stackoverflow.com/questions/3475867/mysql-data-mys...

[vladimirchelyabinskiy]

это уже бесполезно он одну строку в час вставлять будет

[vdem]

@vladimirchelyabinskiy Только я не совсем понимаю, зачем всю эту беду делать через C# (читать: через жопу).

[vladimirchelyabinskiy]

да все норма в c# просто mysql не вывозит, индексов набирается в каждой таблице по 40 мб и все не может insert сделать или load data

[vdem]

я же говорил не добавляйте индексы в таблицу!!!

Answer 5

[vladimirchelyabinskiy]

Ну вот на изображении видно что уже есть различия во времени, то есть по немногу скорость всетаки падает.... mysql 5.6.19 x64 тот же конфиг.

Comments

[vladimirchelyabinskiy]

Программа вставила 60 мл записей и вывела в логах ошибку 05:45:26: [-] MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.TimeoutException: Timeout in IO operation