Моят личен и професионален живот

2018-07-11

Защо зареждането на данни с MySQL Workbench е толкова бавно?

Наскоро започнах малък личен проект за статистики за състезанието за издръжливост 24-те часа на Льо Ман на което съм фен. Започнах със събирането на данни и за зареждането им започнах да използвам функционалността в MySQL Workbench. Функционалността е достъпна от контекстното меню на таблица - щракате с десния бутон върху таблица и след това на елемента "Table Data Import Wizard". Това е доста изпипана възможност, защото ви позволява:
  • първо да изберете локален файл;
  • после, да изберете местоназначение - или съществуваща таблица (и да я изтриете цялата ако е нужно) или нова таблица;
  • после, да зададете настройки (напр. възможности за CSV като разделители на колони и редове и символ за ограждане на стойности), кодиране на файла и карта на колоните;
  • накрая да започнете зареждането и да следите за грешки (печата се съобщения за всеки ред от входния файл).
Обаче, с нарастващия брой линии в CSV файла, забелязах че зареждането на данни става по-бавно и по-бавно (не обърнах много внимание първоначално), така че за около 4500 линии вече отнема около 5 минути (!) в локална мрежа и на сървър, който не е толкова натоварен с работа. Не мога да си представя ако трябва да заредя стотици хиляди или дори милиони редове по този начин.
Пробвах същото с LOAD DATA INFILE и същите данни бяха заредени за около 1 секунда, дори при положение, че имам някои тригери на таблицата проверяващи новите въвеждани данни. Разликата е доста очевидна. Така, че защо зареждането на данни с MySQL Workbench е толкова бавно в сравнение с LOAD DATA INFILE заявката?
С проверка на общия журнал на сървъра разликата става незабавно явна - MySQL Workbench създава INSERT заявка за всеки ред от входния файл и изпълнява тези заявки последователно една по една. Това е доста скъпо и време отнемащо особено по мрежата (виж Optimizing INSERT Statements).
Но защо това е така? Две възможни обяснения:
  1. LOAD DATA INFILE заявката изисква файла да бъде достъпен за сървъра (т.е. на локална файлова система достъпна за сървъра). Обаче, с ключовата дума LOCAL клиента може да прехвърли файла на сървъра, така че сървъра да може да го чете. Натоварването в този случай ще е прехвърлянето на файла по мрежата. Това обаче изисква и сървъра и клиента да имат разрешена способност за зареждане на локални данни (за сървъра виж системната променлива local_infile, а за клиента е с конфигурационна променлива ENABLED_LOCAL_INFILE на CMake). Затова, въпреки че имам тази способност разрешена на сървъра явно тя е забранена в MySQL клиента използван от Workbench, защото опитвайки се да изпълня LOAD DATA INFILE с ключовата дума LOCAL дава Error Code: 1148. The used command is not allowed with this MySQL version (виж повече в Security Issues with LOAD DATA LOCAL).
  2. Използване на INSERT заявка с множество VALUES списъци за вмъкване на много редове наведнъж няма да даде възможност на Workbench да предоставя информация за всеки обработен ред от входния файл.
Зареждане на данни с MySQL WorkbenchАко съм прав за горното, тогава бих очаквал журнала за зараждането на данни в MySQL Workbench да бъде по-напредничав. На първо място, не се печата информация за номера на реда, така че съобщенията "Data import" (успешен?) и различните възможни грешки са безполезни при определяне на проблемния ред. Трябва да копирам журнала и да индексирам съобщенията във външен текстов редактор, за да определя проблема във файла с данни. След това, бих искал да имам информация само за грешките (напр. някакъв род филтър). Има лента за напредъка, но без каквито и да било цифри - смятам, че съобщение като "Обработване на ред X от NB_LNS_IN_FILE" ще бъде доста полезно, но това би изисквало преброяване на редовете предварително. Също така няма възможност за съхраняване на журнала от зареждането, а копирането му изисква превъртане (т.е. няма възможности като Ctrol+A или Избери всичко).
Във всеки случай, намирам възможността за зареждане на данни с MySQL Workbench доста полезна, но имайте едно наум ако трябва да зареждате големи обеми от данни (т.е. няколко хиляди или повече реда). В случай, че трябва да зареждате такива обеми от данни е силно препоръчително да прехвърлите файла с данни локално и използвате LOAD DATA INFILE, което според наръчника "чете редове от текстов файл в таблица с много висока скорост".