Import 2,5TB.sql souboru do DB respektive jeho rozdělení

Dobrý den,

Bohužel jsem dostal do rukou archive(záloha MySQL databáze), který má cca 800GB a po jeho rozbalení má hrubý soubor 2,5TB. V souboru je jedna velká databáze rozdělená na několik (cca 30) schémat.

V prvé řadě jsem zkusil naivně spustit celý import dat do DB*, ale i přesto, že server disponuje velkým výpočetním výkonem import jsem vypočítal na cca 3 týdny. Takto dlouhou dobu nemohu čekat. Potřeboval bych ze zdrojového souboru "vyzobat" jednotlivé schémata a pouštět (importovat) tyto data podle priority.

Máte někdo představu jak by se tato věc dala řešit?

Informace:
OS Serveru: Ubuntu 16.04LTS
SW DB: MySQL 5.7 ComunityServer

*mysql -u username -p database_name < backup_name.sql

Děkuji za všechny reakce!


Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #1 kdy: 24. 10. 2017, 12:07:09 »
...

Nemám velké zkušenosti s MySQL, ale 2,5 TB by mělo být zvladatelné řádově rychleji. Podívejte se na nastavení fsync.

Rozdělit to nemůžete jinak, než že si jednotlivá schemata vypreparujete do oddělených souborů v textovém editoru.

Štefan

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #2 kdy: 24. 10. 2017, 12:30:23 »
Jako nejlepší řešení mi nakonec vždy přišlo rozsekání velkého souboru na jednotlivé create a insert části. Jednak je lze v případě nutnosti/chyby opakovaně spustit, jednak lze některé části paralelizovat (rozhodně create a inserty číselníků) a nechat si největší sousta nakonec .. I tam lze pak příliš velký insert jen jednoduše rozdělit na části, které se zpracují v relativně rychlejším sledu. Nemíval jsem žádné problémy, když jsem si inserty rozsekal po cca max 200 MB souborech.. větší už obvykle "drhly"..

ttt

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #3 kdy: 24. 10. 2017, 12:49:04 »
Rozdělit to nemůžete jinak, než že si jednotlivá schemata vypreparujete do oddělených souborů v textovém editoru.

Jaký textový editor dokáže otevřít 1,5 TB a vyzobnout z toho 30 GB? Na https://github.com/jhallen/joes-sandbox/tree/master/editor-perf jsou experimenty se 3 GB souborem (ale bez nastavení editoru) a už tam to většinou zamrzne. Dokáže to nvi, mcedit nebo je potřeba dělat to s less, more, head apod.?

Z poměrně dávného experimentu si vybavuju, že
LOAD DATA INFILE
je řádově rychlejší než
INSERT INTO t VALUES (), ()
a to je o něco rychlejší než
INSERT INTO t VALUES (); INSERT INTO t VALUES ();

Každopádně to chce kouknout do toho SQL souboru a rozsekat ho na menší části (schéma / data).


Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #4 kdy: 24. 10. 2017, 13:30:07 »
Jaký textový editor dokáže otevřít 1,5 TB a vyzobnout z toho 30 GB?

Máte pravdu, neupřesnil jsem.
Zejména mě zaráží "problém" naimportovat pár tera. To bych řešil jako první.
Na extrakci jednocho schématu či jedné databáze z dumpu jsou příklady na googlu, lze to poměrně snadno nascriptovat.
Hledejte například: extract single database from mysql dump


Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #5 kdy: 24. 10. 2017, 13:33:24 »
Jako nejlepší řešení mi nakonec vždy přišlo rozsekání velkého souboru na jednotlivé create a insert části. Jednak je lze v případě nutnosti/chyby opakovaně spustit, jednak lze některé části paralelizovat (rozhodně create a inserty číselníků) a nechat si největší sousta nakonec .. I tam lze pak příliš velký insert jen jednoduše rozdělit na části, které se zpracují v relativně rychlejším sledu. Nemíval jsem žádné problémy, když jsem si inserty rozsekal po cca max 200 MB souborech.. větší už obvykle "drhly"..

Jsem v linuxu zacatecnik, takze si dost dobre nedokazu predstavit jak bych takovy soubor pomoci prikazu mohl rozsekat. Asi teoreticky chapu, ze bych si mel najit radky kde zacina a konci konkretni schema s inserty a pak je postupne zkouset, ale jak na to?

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #6 kdy: 24. 10. 2017, 13:35:39 »
Jaký textový editor dokáže otevřít 1,5 TB a vyzobnout z toho 30 GB?

Máte pravdu, neupřesnil jsem.
Zejména mě zaráží "problém" naimportovat pár tera. To bych řešil jako první.
Na extrakci jednocho schématu či jedné databáze z dumpu jsou příklady na googlu, lze to poměrně snadno nascriptovat.
Hledejte například: extract single database from mysql dump

Ano nasel jsem dobry skript, ktery toto umi. Problem je v tom, ze ze souboru napred vytahne schema a pak je musim nasledne importovat coz je v ramci tak velkeho objemu dat casove narocne.

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #7 kdy: 24. 10. 2017, 13:57:57 »
Ano nasel jsem dobry skript, ktery toto umi. Problem je v tom, ze ze souboru napred vytahne schema a pak je musim nasledne importovat coz je v ramci tak velkeho objemu dat casove narocne.

A jak byste to chtěl jinak dělat? V mysql SHEMA = DATABASE.
To už byste pak musel jít po jednotlivých tabulkách?

dustin

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #8 kdy: 24. 10. 2017, 14:04:13 »
Na netu je řada sql splitterů, které tohle zvládnou. Sami jsme před lety používali, jelo to po řádcích, takže streamové zpracování. Ručně bych to nedělal, i když s pomocí pluginu https://github.com/vim-scripts/LargeFile jsem editoval v pohodě i 100gb dump (nedělá to nic jiného, než vypne featury vimu, které se na velkém souboru zaseknout).

Příště je lepší dumpovat jednotlivé tabulky a db, snáze se s tím pak pracuje, navíc nalití může běžet paralelně (i jednotlivé tabulky jedné DB, když se pro lití vypne kontrola referenční integrity a jedinečnosti). Ukázka dumpu (jsou v tom nějaké funkce, ale ty nejsou podstatné):

Kód: [Vybrat]
#vyrobime tab zalohu
DBS=$(mysql $LOGIN_PATH -e 'show databases;' | tail -n +2) || clean_quit "Failed reading databases" 1

# vyrobime seznamy tabulek
for DB in $DBS; do
TABLES=$(mysql $LOGIN_PATH -e "use $DB; show tables;" | tail -n +2 ) || clean_quit "Failed reading tables" 1
# dumpneme jednotlive tabulky
DB_DIR=$TMP_DATA_DIR/$DB
[ -a $DB_DIR ] && rm -rf "$DB_DIR"
mkdir -p $DB_DIR
chmod 777 $DB_DIR
echo "$TABLES" | xargs -n 1 -P 4 ionice -c 2 -n 7 mysqldump $LOGIN_PATH --max_allowed_packet=64M --default-character-set=utf8 --skip-opt --add-drop-table --add-locks --create-options --disable-keys --extended-insert --quick --set-charset --tab $DB_DIR  $DB || clean_quit "Failed dumping data" 1
done

Obnovuje se něčím takovým (tohle konkrétně nejede paralelně):

Kód: [Vybrat]
#! /bin/bash -x
DB_PATH=$1

function info() {
        MSG="$1"
        echo "$MSG" >&2
}

function disableChecks() {
        info "Disabling checks"
        echo "SET @@session.unique_checks = 0;"
        echo "SET @@session.foreign_key_checks = 0;"
}

function enableChecks() {
        info "Enabling checks"
        echo "SET @@session.unique_checks = 1;"
        echo "SET @@session.foreign_key_checks = 1;"
}

function getCharset() {
        TXT_FILE=$1
        SQL_FILE=${TXT_FILE%.txt}.sql
        grep "DEFAULT CHARSET=" $SQL_FILE | sed  's/^.*DEFAULT CHARSET=\([^ ;]*\).*/\1/g'
}

function loadInfile() {
        TABLE=$1
        FILE=$2
#       CHARSET=$(getCharset $FILE)
        CHARSET="binary"
        info "Loading file $FILE into table $TABLE with charset $CHARSET"
        echo "LOAD DATA INFILE '$FILE' INTO TABLE $TABLE CHARACTER SET $CHARSET;"
};

function createTables() {
        DB_PATH=$1
        for SQL_PATH in $(find $DB_PATH -type f -name "*.sql"); do
                TABLE_FILE=$(basename $SQL_PATH)
                info "Creating table ${TABLE_FILE%.sql}"
                cat $SQL_PATH
        done
}

function loadData() {
        DB_PATH=$1
        for FILE_PATH in $(find $DB_PATH -type f -name "*.txt"); do
                FILE=$(basename $FILE_PATH)
                TABLE="${FILE%.txt}"
                loadInfile $TABLE $FILE_PATH
        done
}

function recreateDB() {
        DB_PATH=$1
        # odstranime pripadne zaverecne lomitko, abychom na nazev adresare mohli pouzit basename
        DB=$(basename ${DB_PATH%/})
        info "Recreating database $DB"
        echo "DROP DATABASE IF EXISTS $DB;"
        echo "CREATE DATABASE $DB;"
        echo "USE $DB;"
}


(
        disableChecks
        recreateDB $DB_PATH
        createTables $DB_PATH

        # vytvorime strukturu
        loadData $DB_PATH
        enableChecks
)

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #9 kdy: 24. 10. 2017, 14:13:16 »
Děkuju všem za rady. Jakmile se proberu řešením. Dám vědět. Je mi jasné, že je lepší zálohovat jednotlivé tabulky či schémata.

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #10 kdy: 24. 10. 2017, 14:15:19 »
Děkuju všem za rady. Jakmile se proberu řešením. Dám vědět. Je mi jasné, že je lepší zálohovat jednotlivé tabulky či schémata.

Mně hlavně zaráží ten výkon, jak píšete, na to bych doporučoval se podívat. Tam čuju nějaký problém.

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #11 kdy: 24. 10. 2017, 14:28:03 »
Děkuju všem za rady. Jakmile se proberu řešením. Dám vědět. Je mi jasné, že je lepší zálohovat jednotlivé tabulky či schémata.

Mně hlavně zaráží ten výkon, jak píšete, na to bych doporučoval se podívat. Tam čuju nějaký problém.

S výkonem ze strany HW by problém být neměl. Spíš kde může být chyba, že SW nedokáže využít HW..
« Poslední změna: 24. 10. 2017, 14:29:39 od Clee-Shock »

dustin

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #12 kdy: 24. 10. 2017, 14:29:32 »
Jednak to importuješ celé v jednom vlákně (takže celkový výkon stroje téměř nevyužiješ), jednak před importem určitě nevypínáš kontroly na cizí klíče a unique checks, zkusil bych i vypnutí čekání na flush

set global innodb_flush_log_at_trx_commit=2

Takže stávající dump rozsekat na tabulky (vždy je to v pořadí create database, create table, inserty do table) a ty nalévat paralelně. + samozřejmě rychlé pole či SSD, ale to u tak velkého dumpu předpokládám.

A jak jsem psal, opravit zálohovací skript, aby nedělal takovéto obludy. Navíc se to snázeji pak zálohuje/deduplikuje, protože se řada tabulek (tj. souborů) mezi intervalem záloh určitě nemění.

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #13 kdy: 24. 10. 2017, 14:32:37 »
Jednak to importuješ celé v jednom vlákně (takže celkový výkon stroje téměř nevyužiješ), jednak před importem určitě nevypínáš kontroly na cizí klíče a unique checks, zkusil bych i vypnutí čekání na flush

set global innodb_flush_log_at_trx_commit=2

Takže stávající dump rozsekat na tabulky (vždy je to v pořadí create database, create table, inserty do table) a ty nalévat paralelně. + samozřejmě rychlé pole či SSD, ale to u tak velkého dumpu předpokládám.

A jak jsem psal, opravit zálohovací skript, aby nedělal takovéto obludy. Navíc se to snázeji pak zálohuje/deduplikuje, protože se řada tabulek (tj. souborů) mezi intervalem záloh určitě nemění.

Děkuju

dustin

Re:Import 2,5TB.sql souboru do DB respektive jeho rozdělení
« Odpověď #14 kdy: 24. 10. 2017, 14:35:22 »
Koukám na ten htop.

Proč máš 10GB swap na stroji s 300GB RAM? Vypnul bych jej.

Proč má mysql nastaveno tak málo paměti? Máme 512GB RAM a mysql má v bufferech nastaveno 100GB (pravda nijak jsme si s tím nehráli a netestovali, ale používá je)

Kód: [Vybrat]
4065 mysql  20   0  108g 105g  17m S     3 20,9  12975:49 mysqld                                                                                                                                                                                                                                                         
20 jader = nalévání alespoň 18 jádry současně. Snadno si nastavíš parametry xargs - viz ten dump skript.