Hi,
Here are the queries I use to load and backup the base with :
sql>COPY INTO data_100 FROM '/root/data_maria100_utf.txt' DELIMITERS '\t' ; 103704336 affected rows (3m 10s)
The imported file :
ls -lh /root/data_maria100_utf.txt -rw-r--r-- 1 root root 6,4G juin 10 18:22 /root/data_maria100_utf.txt
The backup command :
date ; msqldump --database=test --table=data_100 > data_100.sql ; date jeudi 24 juillet 2014, 17:40:51 (UTC+0200) jeudi 24 juillet 2014, 18:09:29 (UTC+0200)
ls -lh data_100.sql -rw-r--r-- 1 root users 7,0G juil. 24 18:09 data_100.sql
If I use COPY query INTO file to backup instead of msqldump :
sql>COPY SELECT * FROM data_100 INTO '/home/pcoustillas/dump/data_100_coyp.csv' USING DELIMITERS '\t','\n','"'; 103704336 affected rows (17m 53s)
It divide the necessary time by 2 , but i don't know if it is a good practice .
For reference here is the the output of sys.storage for this table :
sql>SELECT * FROM sys.storage WHERE "table"='data_100'; +--------+----------+----------------------+---------+----------+-----------+-----------+------------+------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+==========+======================+=========+==========+===========+===========+============+============+=========+========+ | sys | data_100 | id_visite | int | 62/6262 | 103704336 | 4 | 414817344 | 0 | 0 | false | | sys | data_100 | id_visiteur_unique | varchar | 60/6071 | 103704336 | 19 | 414817344 | 1286078464 | 0 | false | | sys | data_100 | visiteur_identifie | int | 63/6316 | 103704336 | 4 | 414817344 | 0 | 0 | false | | sys | data_100 | site_niveau1 | int | 62/6270 | 103704336 | 4 | 414817344 | 0 | 0 | false | | sys | data_100 | site_niveau2 | varchar | 07/764 | 103704336 | 12 | 207408672 | 10240 | 0 | false | | sys | data_100 | chapitre_niv1 | int | 60/6016 | 103704336 | 4 | 414817344 | 0 | 0 | false | | sys | data_100 | chapitre_niv2 | int | 56/5605 | 103704336 | 4 | 414817344 | 0 | 0 | false | | sys | data_100 | indicateur_global | varchar | 53/5335 | 103704336 | 1 | 207408672 | 20480 | 0 | false | | sys | data_100 | id_cmp_autopromotion | int | 17/1700 | 103704336 | 4 | 414817344 | 0 | 0 | false | | sys | data_100 | nb_pages_vues | int | 56/5652 | 103704336 | 4 | 414817344 | 0 | 0 | false | | sys | data_100 | temps_passe | int | 62/6264 | 103704336 | 4 | 414817344 | 0 | 0 | false | +--------+----------+----------------------+---------+----------+-----------+-----------+------------+------------+---------+--------+
Pierre