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';

+--------+----------+----------------------+---------+----------+-----------+-----------+------------+------------+---------+--------+

+========+==========+======================+=========+==========+===========+===========+============+============+=========+========+

| 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

De: "Radovan Bičiště" <radovan.biciste@ceosdata.com>
À: users-list@monetdb.org
Envoyé: Jeudi 24 Juillet 2014 17:10:25
Objet: Re: monetdb is slow to backup

Hello,
Have you deleted significant amount of rows before backup? Delete only
marks rows as deleted and during backup it scans the whole table
including deleted rows.
I would check table sizes using sys.storage view. What is the size of
the database you are backing up on the disk? It should be roughly the
same as the size of the backup.

Radovan

On 07/24/2014 02:25 PM, Pierre-Adrien Coustillas wrote:
> 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