Understanding the structure of Monero's LMDB and how explore its contents using mdb_stat

Question

I am interested in understanding the structure of Monero's database LMDB and using mdb_stat to extract relevant information from it. I am assuming querying it directly (i.e. via mdb_stat) for information should be a lot faster and easier than doing so through the wallet, daemon or RPC. Is that the case?

For instance, I was hoping to be able to extract information about blocks, transactions, outputs, rings, Pedersen commitments, range proofs, number of rings that use a given output etc, and I think it would be invaluable to have some sort of specific manual on how to conduct such queries on Monero's LMDB, and what sort of information is actually available in this data structure.

While I was able to follow the examples here and perform the first two queries successfully (for number of outputs and key images, respectively), when I tried to adapt them to queries about the number of transactions it didn't seem to work:

$ mdb_stat -s output_txs ~/.bitmonero/lmdb
Status of output_txs
  Tree depth: 1
  Branch pages: 0
  Leaf pages: 1
  Overflow pages: 0
  Entries: 30668498

$ mdb_stat -s spent_keys ~/.bitmonero/lmdb
Status of spent_keys
  Tree depth: 1
  Branch pages: 0
  Leaf pages: 1
  Overflow pages: 0
  Entries: 26564596

$ mdb_stat -s txs ~/.bitmonero/lmdb
Status of txs
  Tree depth: 0
  Branch pages: 0
  Leaf pages: 0
  Overflow pages: 0
  Entries: 0

Does that mean that I am doing a badly formed search, or is it that txs is not yet populated with transaction data?

I understand that there exists a manual for mdb_stat itself, so maybe it is just me, but I figured that everyone could benefit from having a more explicit and comprehensive guide with examples specific to Monero's database.

Thanks in advance!

Answer 1

Understanding the structure of Monero's LMDB and how explore its contents using mdb_stat

Firstly, mdb_stat is a tool to get status information about the database environment, not to explore its contents. Quoting the description directly from the mdb_stat manual:

The mdb_stat utility displays the status of an LMDB environment.

I am assuming querying it directly (i.e. via mdb_stat) for information should be a lot faster and easier than doing so through the wallet, daemon or RPC. Is that the case?

You cannot perform queries against the data using mdb_stat, as explained above. To actually query the data, you have to use either the LMDB library (as documented here), or use one of it's higher level wrapper libraries for your preferred programing language, or use the wallet/daemon RPC interfaces.

As for the structure, it is documented in the source code here (quoting):

/* DB schema:
 *
 * Table            Key          Data
 * -----            ---          ----
 * blocks           block ID     block blob
 * block_heights    block hash   block height
 * block_info       block ID     {block metadata}
 *
 * txs_pruned       txn ID       pruned txn blob
 * txs_prunable     txn ID       prunable txn blob
 * txs_prunable_hash txn ID      prunable txn hash
 * txs_prunable_tip txn ID       height
 * tx_indices       txn hash     {txn ID, metadata}
 * tx_outputs       txn ID       [txn amount output indices]
 *
 * output_txs       output ID    {txn hash, local index}
 * output_amounts   amount       [{amount output index, metadata}...]
 *
 * spent_keys       input hash   -
 *
 * txpool_meta      txn hash     txn metadata
 * txpool_blob      txn hash     txn blob
 *
 * Note: where the data items are of uniform size, DUPFIXED tables have
 * been used to save space. In most of these cases, a dummy "zerokval"
 * key is used when accessing the table; the Key listed above will be
 * attached as a prefix on the Data to serve as the DUPSORT key.
 * (DUPFIXED saves 8 bytes per record.)
 *
 * The output_amounts table doesn't use a dummy key, but uses DUPSORT.
 */

For instance, I was hoping to be able to extract information about blocks, transactions, outputs, rings, Pedersen commitments, range proofs, number of rings that use a given output etc,

The data can only be accessed as described above. It's also worth noting the data is binary. The C/C++ objects are written to the database. For this reason, even if you used a higher level language (e.g. Python or Javascript for example) LMDB wrapper, you are still going to need to convert that data to objects in your other language. For this reason, if you are going to directly query the database, you will almost certainly want to use C/C++, the native LMDB library and make use of the definitions in cryptonote_basic.h.

Does that mean that I am doing a badly formed search, or is it that txs is not yet populated with transaction data?

If I recall correctly, txs is from an older version of the database and it's data has been migrated into the other tx tables as detailed above. You can verify what tables have data in them by running:

mdb_stat -a ~/.bitmonero/lmdb

...when I tried to adapt them to queries about the number of transactions it didn't seem to work

The number of transactions can be seen by looking at the entries count of the txs_pruned, tx_outputs or tx_indices tables.