Discussions

Expand all | Collapse all

Not able to find information about Added support for binary dump/restore of database tables in Document

  • 1.  Not able to find information about Added support for binary dump/restore of database tables in Document

    Posted 22 days ago
    would like to congratulate for Version 5.0

    as 5.0 added support for binary dump/restore of database tables. However, in the document not able to find the details.

    Regards,
    sumit 

    #General


  • 2.  RE: Not able to find information about Added support for binary dump/restore of database tables in Document

    Posted 22 days ago
    Edited by Candido Dessanti 22 days ago
    Hi @Sumit Srivastava,

    the docs aren't up to date yet.

    the syntax is quite straightforward so to create a dump of a table just type (the compression is optional, the default is gzipped)

    dump table table_name to '/path/file_name' with (compression 'gzip|none')

    to restore just type

    restore​ table table_name from '​/path/file_name'

    the dump/restore command is a mix between a physical and logical backup, so you can create a dump in a database and restore in another or, you can duplicate a table in the same database

    e.g.

    omnisql> dump table adsb_airplanes to '/home/mapd/adsb_airplanes.dmp.gz' with (compression='gzip');
    omnisql> restore table adsb_airplanes_res from '/home/mapd/adsb_airplanes.dmp.gz';
    omnisql> select count(*) from adsb_airplanes_res;
    EXPR$0
    1010400

    for best performance, assuming you have a fast storage is compression none

    n.b. the dump/restore actually works on single system only



  • 3.  RE: Not able to find information about Added support for binary dump/restore of database tables in Document

    Posted 22 days ago
    Hi @Candido Dessanti

    Thank you for update and we will try today .​​


  • 4.  RE: Not able to find information about Added support for binary dump/restore of database tables in Document

    Posted 12 days ago
    Hi @Candido Dessanti

    Verified the dump and restore functionality and working good. ​



  • 5.  RE: Not able to find information about Added support for binary dump/restore of database tables in Document

    Posted 12 days ago
    Hi​ @Sumit Srivastava,

    It's been a solid first step.



  • 6.  RE: Not able to find information about Added support for binary dump/restore of database tables in Document

    Posted 12 days ago
    Hi @Sumit Srivastava,

    I did a brief benchmarking using the three compression options available with the command. The most balanced of 3 it's the lz4, but you have to install the lz4 library.

    So first of all, you have to try if the lz4 lib is installed on the source and target system.

    as an example, if you run the dump with lz4 compression on a system that hasn't the library, you will get an error message straight from OMNISQL tool
    omnisql> dump table perf to '/opt/opendata/perf.dump' with (compression='lz4');
    Exception: Compression program lz4 is not found.

    Assuming you have the administrative right on your os you can install the needed library quite easily.
    On ubuntu just run
    sudo apt install liblz4-tool​

    to use various compressions option add the compression keyword in with block of dump/restore command.

    omnisql> dump table perf to '/opt/opendata/perf.dump' with (compression='lz4');
    omnisql> restore table perf2 from '/opt/opendata/perf.dump' with (compression='lz4');

    remember to specify the right compression in the restore command, because as the time of writing, the dump/restore commands defaults to gzip, so if you used none or lz4 as compression in the dump file, if you don't specify the compression,m the restore command will fail.

    Now the benchmark results on a Threadripper 1920X, with nvmes disks used for the database and a sata SSD for backup data.

    Compression Dump (ms) Restore (ms) Size (MB)
    none 1322 2691 2112.0
    gzip 18738 8118 58.8
    lz4 1745 3746 107.5

    As you can see the none and lz4 are extremely faster than gzip on dump operation (more than 10x) and quite faster on restore operation (between 2x and 3x), while the gzip is the most efficient on compression ratio (2x than lz4).

    It's up to you choose which compression method, basing on your needs, but you can also consider to mix them because not all tables or data are equals, so if you have data that's unlikely to restored you can use the gzip compression because it saves more space, while using lz4 for tables where the performance of dump/restore is important.

    I have tried yet, using the none compression against a compressed filesystem, like BTRFS, but I'll do in the future


  • 7.  RE: Not able to find information about Added support for binary dump/restore of database tables in Document

    Posted 12 days ago
    Hi @Candido Dessanti

    Thanks for extremely useful information. Tried the same and received the same outcome as you explained above.
    lz4 are extremely faster than gzip on dump operation (more than 10x-12x) and faster on restore operation (between 4x and 5x)