@vladimirchelyabinskiy

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

Всем привет, уже очень давно ищу ответ на свой вопрос по 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();


В коде программы все отлично в ней причины быть не может.
  • Вопрос задан
  • 4002 просмотра
Решения вопроса 2
opium
@opium
Просто люблю качественно работать
поставьте mysql 5.6 там все таки бодрее все и куда лучше работает с большим количеством данных
откажитесь от индекса , он довольно тяжело пересчитывается для мд5
Ответ написан
@lexore
Так ли нужно использовать myisam?
Есть и другие движки.
en.wikipedia.org/wiki/Comparison_of_MySQL_database...
Я бы на вашем месте посмотрел в сторону движка csv.
Ну или хотя бы не использовать индексы во время вставки.
Индекс всегда можно добавить после вливания данных.
Ответ написан
Пригласить эксперта
Ответы на вопрос 3
@vdem
Я только мельком глянул на задачу, попробуйте так: 1) LOAD_DATA_INFILE в таблицу без всяких там PRIMARY KEY (но поле объявите и заполните MD5 хэшем), 2) ALTER TABLE, устанавливаете PRIMARY KEY. Поскольку это РАЗОВАЯ операция (насколько я понимаю), нет смысла парить My(!!)SQL с переиндексацией после каждой вставки.
Ответ написан
@vladimirchelyabinskiy Автор вопроса
Ну вот на изображении видно что уже есть различия во времени, то есть по немногу скорость всетаки падает.... mysql 5.6.19 x64 тот же конфиг.

dbc5f09e415f4ea38741b0f328f3fe35.jpg
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы