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
--
1G6
52 route de bischwiller
67300 Schiltigheim
Société de Services et de Formations en Logiciels Libres
http://1g6.biz
Tél : 06 64 63 70 35
----- Mail original -----
De: "Radovan Bičiště"
Hello,
I'm used to save monetdb using msqldump :
msqldump --database=base --table=table > backup.sql
This command takes 30 minutes to backup 6 GB , but 4 minutes to restore.
How can I increase backup speed ?
Thanks
Pierre
-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list