SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 20.10.2015 17:55:27

sv
Участник
Зарегистрирован: 26.03.2012
Сообщений: 22

Загрузка csv в mysql с последующей дозагрузкой

Есть интересная задача: как загнать csv файл (38GiB) в mysql?
А самое главное как загонять последующую информацию из тех же csv (10-20 GiB).

К сожалению данная операция в большинстве случаев весьма нетороплива.
Ситуация ослажняется тем, что необходимо использовать индексы на таких объемах данных. Что приводит к тому, что последующая загрузка занимает продолжительное время.

Какое у меня железо?
PowerEdge R610. HT отключен. Соответвенно имеем 8 ядер. RAM в колличестве 70Gb
База живет на Intel SSD 750.

Что за база?
mysql  Ver 15.1 Distrib 10.1.7-MariaDB, for Linux (x86_64) using readline 5.1

Я создаю такую таблицу и заливаю туда данные


CREATE TABLE `table8` (
    `Timestamp` DATE NULL DEFAULT NULL,
    `field2` TIME(6) NULL DEFAULT NULL,
    `field3` TIME(6) NULL DEFAULT NULL,
    `field4` VARCHAR(50) NULL DEFAULT NULL,
    `field5` INT(11) NULL DEFAULT NULL,
    `field6` VARCHAR(5) NULL DEFAULT NULL,
    `field7` VARCHAR(50) NULL DEFAULT NULL,
    `field8` VARCHAR(50) NULL DEFAULT NULL,
    `field9` DOUBLE NULL DEFAULT NULL,
    `field10` INT(11) NULL DEFAULT NULL,
    `field11` VARCHAR(50) NULL DEFAULT NULL,
    `field12` DOUBLE NULL DEFAULT NULL,
    `field13` INT(11) NULL DEFAULT NULL,
    `field14` VARCHAR(50) NULL DEFAULT NULL,
    `field15` VARCHAR(50) NULL DEFAULT NULL,
    `field16` VARCHAR(50) NULL DEFAULT NULL,
    `field17` DOUBLE NULL DEFAULT NULL,
    `field18` INT(11) NULL DEFAULT NULL,
    `field19` DOUBLE NULL DEFAULT NULL,
    `field20` DOUBLE NULL DEFAULT NULL,
    `field21` DOUBLE NULL DEFAULT NULL,
    `field22` INT(11) NULL DEFAULT NULL,
    `field23` INT(11) NULL DEFAULT NULL,
    `field24` INT(11) NULL DEFAULT NULL,
    `field25` VARCHAR(50) NULL DEFAULT NULL,
    `field26` CHAR(4) NULL DEFAULT NULL,
    `field27` CHAR(1) NULL DEFAULT NULL,
    `field28` CHAR(1) NULL DEFAULT NULL,
    `field29` CHAR(6) NULL DEFAULT NULL,
    `field30` VARCHAR(20) NULL DEFAULT NULL,
    `field31` VARCHAR(20) NULL DEFAULT NULL,
    `field32` VARCHAR(12) NULL DEFAULT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
 


Почему без первичного ключа?
При использовании Id Prymary Key появляются ошибки:


ERROR 1205 (HY000) at line 2: Lock wait timeout exceeded; try restarting transaction
 


Конфиг mysql

egrep -v '^#|^$' /etc/my.cnf.d/my.cnf
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
[mysqld]
bulk_insert_buffer_size=256M
innodb_file_format=Barracuda
datadir         = /mnt/nvme0n1/mysql
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 16
tmpdir          = /mnt/nvme0n1/mysql_tmp
skip-host-cache
skip-name-resolve
server-id       = 1
innodb_buffer_pool_size = 40G
innodb_log_file_size = 10G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_log_buffer_size = 36M
innodb_lock_wait_timeout=150
[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
 


Как я гружу данные?

LOAD DATA INFILE '/mnt/nvme0n1/mysql_import/dump.csv' INTO TABLE table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
 


Но при загрузке мы нагружаем одно ядро из 8.
Соответсвенно чтобы ускорить загрузку я бью файл или на 8 или на 16 частей и гружу их параллельно.

При этом я имею странную статистику по объему таблиц.
Я делаю

один поток

select count(Timestamp) from table
284362279
 


8 потоков

select count(Timestamp) from table8
284362279
 


16 потоков

select count(Timestamp) from table16
284362279
 


При этом если смотреть так


SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_ROWS,
round(data_length/1024/1024,0) "data_in_MB",
round(data_free/1024/1024,0) "data_free_in_MB",
round(index_length/1024/1024,0) "index in MB",
round(SUM(data_length+index_length)/1024/1024,0) "total_in_MB"
FROM information_schema.tables t
GROUP BY t.TABLE_NAME
ORDER BY total_in_MB DESC
 



"TABLE_SCHEMA"    "TABLE_NAME"    "TABLE_ROWS"    "data_in_MB"    "data_free_in_MB"    "index in MB"    "total_in_MB"
"reports"    "table16"    "292784111"    "96676"    "7"    "0"    "96676"
"reports"    "table8"    "289360020"    "82634"    "5"    "0"    "82634"
"reports"    "table"        "280826531"    "49751"    "5"    "0"    "49751"
"reports"    "table_aria"    "284362279"    "34566"    "0"    "12967"    "47533"
 




Соответвенно мы имеем отличие count от TABLE_ROWS в таблице information_schema.tables для всех InnoDB таблиц, даже той, что лилась в один поток.
А вот таблица на движке Aria (MyISAM) такого отличия не имеет.

Зачем эта вся возня?
Дело в том, что наилучшие результаты по дозагрузке данных я получил на InnoDB в 16 потоков.
На движке Aria дозаливка 16 Gb заняла 13 часов
А при использовании InnoDB
8 threads - 1597 sec
16 threads - 1587 sec

Отредактированно sv (20.10.2015 17:59:43)

Неактивен

 

#2 20.10.2015 22:12:06

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Загрузка csv в mysql с последующей дозагрузкой

> Ситуация ослажняется тем, что необходимо использовать индексы на таких объемах данных. Что приводит к тому, что последующая загрузка занимает продолжительное время.

Попробуйте отключать индексы на время загрузки:
ALTER TABLE `table` DISABLE KEYS;
LOAD DATA
ALTER TABLE `table` ENABLE KEYS;


MyISAM в один поток - недостаточно быстро?

Неактивен

 

#3 28.10.2015 13:57:59

sv
Участник
Зарегистрирован: 26.03.2012
Сообщений: 22

Re: Загрузка csv в mysql с последующей дозагрузкой

rgbeast написал:

> Ситуаци�  о� лажн� ет� �  тем, что необходимо и� пользовать индек� ы на таких объемах данных. Что приводит к тому, что по� ледующа�  загрузка занимает продолжительное врем� .

Попробуйте отключать индек� ы на врем�  загрузки:
ALTER TABLE `table` DISABLE KEYS;
LOAD DATA
ALTER TABLE `table` ENABLE KEYS;


MyISAM в один поток - недо� таточно бы� тро?

С отключенными индек� ами отлично заливает� � .
Первый csv дамп в 25G заливает� �  - 30 min 58.17 sec
Потом отключаем индек� ы.
Второй csv дамп в 14G заливает� �  18 min 51.23 sec.
Индек� ы по� ле включени�  � тро� т� �  1 hour 9 min 52.03 sec.

� езультаты хорошие. � о е� ть опа� ение ка� ательно объема таблиц. MyISAM помнит� �  �  большими таблицами работал крайне не � табильно. Aria конечно по заверени� м Монти более � табильна. � о как мне кажет� �  InnoDB при таблицах в не� колько � отен гиг более предпочтительный вариант. � о только он заливает� �  довольно долго в один поток. Я наверное попробую отключение индек� а и �  InnoDB. � о даже е� ли � коро� ть � уще� твенно повы� ит� �  хотело� ь бы в� е равно лить в не� колько потоков. �  то довольно гру� тно � мотреть в top и видеть как грузит� �  лишь одно � дро из 8. �  Е� ли их 16 или 32?

� у и вопро� . � а� колько корректно вообще заливать в mysql не� колько потоков? Как �  пи� ал выше, mysql по крайней мере � о � тати� тикой � ходит �  ума. Хот�  таблицы по� ле заливки вроде как кон� и� тентны.

Неактивен

 

#4 28.10.2015 15:21:40

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Загрузка csv в mysql с последующей дозагрузкой

На моей проактике не было никаких проблем с MyISAM при больших объемах. Что для него опасно для производительности - много одновременных операций записи.

По записи в несколько потоков опыта нет, так как препочитаю делать это в один поток. Все будет зависеть от того, как алгоритмы среагируют на конкретные данные.

Неактивен

 

#5 29.10.2015 16:42:35

sv
Участник
Зарегистрирован: 26.03.2012
Сообщений: 22

Re: Загрузка csv в mysql с последующей дозагрузкой

У мен�  были регул� рные проблемы �  MyISAM - тиблицы крашили� ь каждую неделю. Правда то при и� пользовании репликации.
Проблема решала� ь переводом на InnoDB. � о в данном � лучае � то не актуально, так как репликаци�  не и� пользует� � .

P.S. Кака� -то ерунда �  форумом. Отправл� ешь � ообщение и он его режит до первых не� кольких букв. � едактируешь его и вновь втавл� ешь тек� т и на некоторых � имволах ромбики. � аботаю в linux �  браузерами chrome и firefox.

Отредактированно sv (29.10.2015 16:47:25)

Неактивен

 

#6 29.10.2015 20:38:42

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3761

Re: Загрузка csv в mysql с последующей дозагрузкой

Странно, тоже в linux и firefox. Все проблемы имеют очень частный характер, поэтому в каждой ситуации рекомендуется тестировать. Не могу априори сказать в чем была проблема с MyISAM при репликации.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson