forked from bitcoin-abe/bitcoin-abe
-
Notifications
You must be signed in to change notification settings - Fork 1
/
README-MYSQL.txt
176 lines (125 loc) · 6.79 KB
/
README-MYSQL.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
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 has 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 were tested on a MySQL database with binary-type=binary and
default settings for firstbits 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 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.
- During initial tests, TokuDB required running ANALYZE TABLE on all tables at
least once to properly compute index cardinality. This is most important to
run *during* the initial load or after restoring from backup to ensure
optimal query optimisation.
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). To set the default engine at connect time, add the
following option to your MySQLdb connect-args:
"init_command":"SET default_storage_engine='TokuDB'"
Ex. if your user and database name is abe, with no password, use:
connect-args {"user":"abe","db":"abe","init_command":"SET default_storage_engine='TokuDB'"}
There are options to convert, however you should make sure to also set the
default engine as above to ensure any new tables created during upgrades uses
TokuDB.
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.
TODO:
Some ideas on enhancements for TokuDB:
- Backup script using SELECT INTO OUTFILE / LOAD DATA INFILE (fatser)
- ANALYZE script
- Script to check FK relations?