Analyse huge amount of blockchain data

Question

I am trying to go over all transactions data from every block on the bitcoin blockchain from the previous 4 years. With almost 2k transaction per block, it will take a lot of queries per block. I have a full node running locally and I tried two ways:

Python with RPC: This is very slow and keeps losing connection after some time (httpx.ReadTimeout)

Python with os.popen commands: Doesn't have the connection problem, but still very slow.

Would there be any other way? Any recommendation on how to analyze bulk data from the blockchain? The methods listed above are unfeasible given the time it would take.

EDIT: The problem isn't memory, but the time the bitcoin node takes to answer the queries.

Answer 1

With almost 2k transaction per block, it will take a lot of queries per block.

The getblock RPC supports a verbosity=2 argument which returns all the transactions in the block as JSON objects, so you can make do with a single query per block.

With RPC batching (i.e. sending multiple commands in a single request), you can do even better! You can query the transactions of n blocks with just 2 RPC requests: one to get all the block hashes, and one to get the blocks (with transactions). The below code snippet shows how you can implement both approaches, and includes a simple performance benchmark. On my machine, the batch approach is ~13x faster when querying the first 2000 blocks.

An example Python implementation, which should work out of the box if:

• requests is installed (pip3 install requests)
• bitcoind -signet -rpcuser=user -rpcpassword=pass

import json
import time
from typing import List

import requests


def get_n_blockhashes(n: int, start_height: int = 0):
    data = [{
        "method": "getblockhash",
        "params": [height]
    } for height in range(start_height, start_height + n)]
    hashes = [item["result"] for item in make_request(data)]
    return hashes


def get_block_transactions_single(block_hashes: List[str]):
    transactions = []
    for block_hash in block_hashes:
        data = {
            "method": "getblock",
            "params": [block_hash, 2]
        }
        block_data = make_request(data)["result"]
        transactions.append(block_data["tx"])

    return transactions


def get_block_transactions_batch(block_hashes: List[str]):
    data = [
        {
            "method": "getblock",
            "params": [block_hash, 2]
        } for block_hash in block_hashes
    ]
    transactions = [item["result"]["tx"] for item in make_request(data)]

    return transactions


def make_request(data):
    url = "http://user:[email protected]:38332/"
    r = requests.post(url, data=json.dumps(data))
    assert r.status_code == 200
    return r.json()


def time_function(fn, *args: str, **kwargs) -> float:
    """Return average fn time execution and check that the last obtained blockheader hash matches last_hash_check """
    iters = 5
    start = time.perf_counter()
    for i in range(iters):
        fn(*args, **kwargs)
    avg_duration = (time.perf_counter() - start) / iters
    return avg_duration


if __name__ == '__main__':
    block_hashes = get_n_blockhashes(2000)
    print(f"single: {time_function(get_block_transactions_single, block_hashes):.4f}s")
    print(f"batch: {time_function(get_block_transactions_batch, block_hashes):.4f}s")

Answer 2

I think the fastest way to go about this, is to process all blkNNNNN.dat files sequentially, and keeping an in-memory table of transaction-hash -> block-timestamp.

You can shrink that table by using only the first 64bits / 8 bytes of the hash as an index. I use parallel-hashmap for quick in-memory indexes.

Then for each transaction you can lookup the block-timestamp of the input, and subtract it from the timestamp of the current block. ... and calculate your statistics from that as you wish.

I think processing the entire blockchain would take a couple of hours on a modern laptop, with the blockchain stored on SSD.

and you'd need about 64G of RAM I think. That worked for me at least. ... I looked it up: there are currently almost 800M txns, So you would need about 13G of data for a table with 16-byte entries. Maybe that would even fit in 32G of RAM.

btw, python is nice for the high-level code, but not so much for quickly processing 500G of data. I would do this in C++.

btw2: I noticed that ordering blocks by timestamp yields a different order than ordering blocks by block height. And also, blocks stored in the blkNNNN.dat files in yet another order. So you may want to take that into account.