README-MYSQL.txt

Abe setup for MySQL: 8 easy steps.

1. Run the Bitcoin client once.  It'll create a .bitcoin directory in
your home directory, along with some other necessary files.

2. Install Python 2.7 and pycrypto and python-mysqldb.  The Debian/Ubuntu packages 
are python2.7, python-crypto, and python-mysqldb. You can use PIP 
(python package manager) to get pycrypto; mysqldb requires (Debian/Ubuntu)
sudo apt-get install python-mysqldb

3. Install MySQL 5.x server and MySQL-Python.  On Debian/Ubuntu:
sudo apt-get install mysql-client mysql-server

4. Configure the MySQL instance with InnoDB engine support.  If you
installed with Debian/Ubuntu then InnoDB is enabled by default.  
To check for InnoDB support, issue "SHOW ENGINES" and look in the output
for "InnoDB" with "YES" next to it.  If "skip-innodb" appears in the server 
configuration (my.cnf or my.ini) then remove it and restart the server.

5. Log into MySQL as root (e.g.: mysql -u root) and issue the following,
replacing "PASSWORD" with a password you choose:

    create database abe;
    CREATE USER 'abe'@'localhost' IDENTIFIED BY 'PASSWORD';
    grant all on abe.* to abe;

6. Create file abe-my.conf with the following contents, replacing
"PASSWORD" as above:

    dbtype MySQLdb
    connect-args {"user":"abe","db":"abe","passwd":"PASSWORD"}
    upgrade
    port 2750

7. Perform the initial data load:

    python -m Abe.abe --config abe-my.conf --commit-bytes 100000 --no-serve

Look for output such as:

    block_tx 1 1
    block_tx 2 2
    ...

This step may take several days depending on chain size and hardware.

8. Then run the web server as:

    python -m Abe.abe --config abe-my.conf

You should see:

    Listening on http://localhost:2750

Verify the installation by browsing the URL shown.

APPENDIX A -- Using InnoDB Compressed Tables

If you're using InnoDB with innodb_file_format=Barracuda and
innodb_file_per_table=1, it is possible to save a great deal of space by
compressing InnoDB tables. Another benefit of compression is reduced IO which
can help when IO is the bottleneck.

Compression can be done at any time, however it is desirable to do it as
early as possible; it will take much longer to run on a fully populated
database. Also please note that you should not compress tables while Abe is
running.

The general command to compress a table is:

ALTER TABLE <table> ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=<n>;

Where <n> is one of 1, 2, 4, 8 or 16.

Without going into many details, the KEY_BLOCK_SIZE parameter affects both
compression ratio and performance, and longer rows requires larger sizes as
well. To save you the trouble, the following commands have been prepared to
give you the greatest compression ratio. (NB: For the bigger tables the
compression have been tested only on small subset of tables -- 1M rows.)

ALTER TABLE txin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE txout ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE block_txin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE tx ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ALTER TABLE block_tx ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE pubkey ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ALTER TABLE block ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE chain_candidate ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
ALTER TABLE block_next ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;

These settings gave been tested on a MySQL database with binary-type=binary
and default settings for firtbits and scripsig. Compression of a full Abe
database reduced its size from 36G (37254132 KiB) down to only 17G
(17409008 KiB), a 53% compression ratio.

To test for yourself, the following bash code prints out SQL commands to
copy each table above into a compressed table for each key size. You can
add a "LIMIT <n>" at the end of the INSERT queries to set an upper limit on
copied rows.

for t in txin txout block_txin tx block_tx pubkey block chain_candidate block_next
do
    for l in 1 2 4 8 16
    do
        echo "CREATE TABLE ${t}_kbs$l like $t;"
        echo "ALTER TABLE ${t}_kbs$l KEY_BLOCK_SIZE=$l ROW_FORMAT=COMPRESSED;"
        echo "INSERT INTO ${t}_kbs$l SELECT * FROM $t;"
    done
done

Then compare the size of your table's .ibd files for each KEY_BLOCK_SIZE.

APPENDIX B: Using TokuDB

TokuDB is an optional engine for MySQL/MariaDB optimized for fast inserts.
Compared to Innodb, TokuDB has the following advantages:

- All tables compressed by default - compression ratio is higher then InnoDB
  without tuning.
- Faster inserts (mostly visible loading empty blocks; loading transaction
  requires linking txin's, and the lookup for those slows inserts, although it
  remains order of magnitude than InnoDB).
- Heavily reduced disk IO, excellent for slow disks, reduces contention with
  other loads like the Bitcoin Client.
- Extremely fast backup restoration (tested from a SQL dump - using LOAD DATA
  INFILE could be even faster!)

The drawbacks experienced were:

- TokuDB requires periodical ANALYZE TABLE to properly compute index
  cardinality. This is most important to run *during* the initial load to
  ensure optimal query optimisations. Once the blockchain is fully loaded,
  it should be sufficient to do it about once a year and after restoring
  backups.
- TokuDB does *not* enforce foreign key check. There are small risks that bugs
  lead to inconsistent database and, more importantly, it will not guard you
  against accidentally breaking table relations when manually altering the
  database contents.

You should be able to convert an existing database using (UNTESTED):
    ALTER TABLE `table` ENGINE=TokuDB;
(You may have to disable foreign key checks first)

Another option is to load a SQL backup while passing the file contents trough
sed:

    cat dump.sql |sed -r 's/ENGINE=InnoDB/ENGINE=TokuDB/;s/ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=[0-9]+//' | mysql <options>

For simplicity this example uses `cat`, but you should probably use
compression on the sql file to reduce IO.

Finally you can load the full blockchain into a database whose default engine
is TokuDB (NB: Abe will add `ENGINE=InnoDB` *only* when the default engine does
not support transactions). As of writing this, around block 340000, you can
expect up to a week to load the blockchain, depending mostly on your CPU
speed.


TODO:

Some ideas on enhancements for TokuDB:

- Backup script using SELECT INTO OUTFILE / LOAD DATA INFILE (Will it work
  with BINARY data?)
- ANALYZE script
- Script to check FK relations?