I'm finally back up to block 336,892... 
Two weeks ago I started working on my SQL converter again. I've had a new (faster, larger) SSD for months but I was busy and, frankly, lacked the desire to work more on it. During July, on another paid project, I happened to set up an Electrum server and was a little miffed at how long it takes to sync with bitcoind. On this particular VPS it was often taking several minutes per block. Very often when checking my wallet I'd find it was lagging by 2-3 blocks. This then became my new impetus for working again on the project - it is to become much more than just a converter - Introducing sqlChain, a full API layer over the blockchain with Electrum support.
There's nothing special about block 336,892. It's just that in the process of developing sqlChain as a new API platform I discovered flaws in my data schema and conversion that forced me several times to restart the process. I think this is my third time in two weeks. And my fingers are crossed, I'm confident, that this time is it. I've been madly coding while the sqlchain is plodding along in the background (on my ancient laptop, someone please send me a new one).
sqlChain is my attempt to build an API platform over the blockchain using MySQL as backend. Not really something new, except that my priority has been on storage size - the goal is having it take less space than the blockchain. I think I'm succeeding at this, though I'm not yet fully caught up (this moment we're all at 369,927). One of my design parameters was to be able to run a fully capable sqlChain on a pruning node. In this scenario bitcoind becomes a verifying front end for sqlChain, which provides a more fully usable API layer over this. And yes, that includes an Electrum server running on sqlChain, over a pruning node.
Here is some current sqlchain row counts at block 337,037 from the /status API call:
- trxs 55,543,931
- addresses 58,949,447
- outputs 153,007,757
The current total sqlChain data size is 35.8 GB but includes pruneable sigscript data of approx. 17 GB. This data is not required for an Electrum server, nor most typical applications that may run over sqlChain, and can be easily pruned. In theory, we should be able to run an Electrum server over a pruning node using < 1 GB in bitcoind and ~20 GB in sqlchain, with sync times of 5-10 seconds (see screen shot, on my 7 year old Core2Duo laptop, probably much faster on any decent server instance). I'll be very happy if this works out.
sqlChain currently consists of three components, all basically working at this time (with some small bits being incomplete). Being under active development, it needs far more testing. The components are:
-
sqlchaind - the blockchain monitor daemon. Polls bitcoind to sync the blockchain to MySQL, monitors the mempool for unconfirmed txs, and detects orphans. It builds and revises the SQL data. Dependent on bitcoind responding to rpc, it can build sql data as bitcoind syncs.
-
sqlchain-api - a multi-API layer that queries SQL data created above to service applications. At this time it offers Insight, Blockchain.info and RPC compatible API interfaces, with some nice extensions. Oh, it also provides a WebSocket interface (Blockchain.info compatible), and includes a basic web server. It's designed to be easy to add new API modules.
-
sqlchain-electrum - a fully functional Electrum server that runs on top of both modules above. At this time this only supports private use. No support for the IRC peer server discovery - which is fine as it needs far more testing before it should be used publicly. This is a suprisingly thin layer over the Insight compatible (extended) API that it actually is more like a proxy, managing subscribed addresses, and otherwise mostly relaying requests down to the underlying API.
The two API daemons are using gevent co-operative threading with the expectation this will allow them to support many concurrent connections. How well this works still needs to be fully tested but given there are three interfaces (web socket, long polling sync, Electrum) that can hold connections open it seems like a good move. Other reports from highly concurrent web servers based on gevent seem to indicate it should do well. sqlchain-api is an WSGI server built with gevent-websocket; which is a layer over pywsgi, providing Web, API and Websocket support.
So, what next? More development work. I have a DEMO WEB/API server here, used for my local testing. I expect to soon move it to an AWS instance for public beta testing. It offers real-time blockchain/sqlchain statistics as part of it's "/status" API. I will add some blog posts describing some of the API extensions, design decisions and performace measures, and a tutorial on running Electrum over a pruning node. The open source code is available on my GitHub. First, I have to fully sync the chain again.
I'd very much welcome feedback from the community. And if so inclined donations will help offset diversions on paid work. Everyone needs to eat.
