Here’s a new weekend project. I made a prototype database writer that takes all EOSIO contract table changes and writes them in a database. This allows searching through table rows in any possible way.
The source code is available on GitHub. The writer is taking Chronicle output for all table deltas and stores them in a table. the ROWS table is dedicated to one chain, in order to avoid locking conflicts. The script creates the table if it doesn’t exist. In addition, it creates two tables for tracking the reversible blocks, and to roll back in case of a fork. The indexes on ROWS table allow various kinds of searching through the values. The table has one row per field in a contract table structure.
Server performance is a bit of a challenge: the total number of table rows is about 90 million, and EOS mainnet has about 200–300 smart contract RAM rows changed in every block. The database master server is an Intel i9 at Hetzner with two NVMe drives directly on the host in ZFS RAID0 with filesystem compression. The server is running 3 nodeos processes (for EOS, Telos and WAX), and collecting dapps activity statistics. This new prototype added a load to it.
The master server was handing the load pretty well. When catching up, it processed about 30 blocks per second, writing every change in the database. The initial load of all the rows from an EOS snapshot took about 4 hours.
The goal of the prototype was to provide this data for public access. I picked a VPS at Hetzner, with 16GB RAM, 4 cores, and 180 GB CEPH virtualized storage. This storage offers a great redundancy and availability, at a cost of some I/O latency.
The latency killed it: the slave VPS was configured as a replication slave for MariaDB, and it was lagging badly. I had to stop the collector when the slave was about 12 hours behind, and its storage was full with the replication queue data. Also the master was pushing several gigabytes per minute, so monthly network quota would likely be a concern as well.
The database is available for public access for the next few weeks, but it’s not updating past November 29th.
Here’s an example of searching through the fields from a Linux MySQL client:
mysql --host=eostables.eoswatch.info --user=eosio_tables_ro --password=eosio_tables_ro --database=eosio_tables --execute="SELECT * from eos_ROWS where fval like 'eosbet%'"The total MariaDB database is occupying 36GB without compression, and 18GB with ZFS filesystem compression.
