# SQL account history plugin for modular hivemind Lately our blockchain work has focused on the SQL account history plugin that injects operations, transactions, and block data into a PostgresSQL database (for use by modular hivemind apps such as wallets, games, etc). We’ve been testing and fixing bugs related to this hived plugin and the associated hivemind sync. As mentioned last time, a replay of hived into hivemind takes 11 hours (as opposed to the 15 hours required by the rocksdb-based plugin, so we already have a decent win on replay time). We ran a hivemind sync using this data and it completed in 46 hours (just under 2 days), but unfortunately we had a fail near the end of the sync (probably due to use of a different hived to supply some dynamic API data not yet provided by the plugin), so we still don’t have definitive data on the speed of a full hivemind sync using this method, but I wouldn’t be surprised if we cut the time in half for a full hivemind sync (i.e. from 4 days down to 2 days or even less) by the time we’re done. I say this because we’ve found that the hivemind sync using this method is currently CPU bound (with about 2/3 of CPU being used by the python-based hivemind indexer and 1/3 being used by postgres), so I think we will be able to reduce the 46 hours after we’ve profiled the python code (and maybe the SQL code as well). My best guess right now is that we're CPU bound due to some data transformation prior to writing the data to the database. # Performance results for SQL account history plugin We’ve run some performance benchmarks using the new SQL account history plugin (versus the rocksdb account history plugin) with excellent performance gains resulting: API call: `{"jsonrpc":"2.0", "method":"account_history_api.get_ops_in_block", "params":{"block_num":2889020,"only_virtual":false}, "id":1}` ah-rocksdb: ranges from 26s to 64s ah-sql: 0.9s API call: `{"jsonrpc":"2.0", "method":"account_history_api.get_account_history", "params":{"account":"abit", "start":125000, "limit":1000}, "id":1}` ah-rocksdb: ranges from 0.3s to 1.2s ah-sql: 0.03s API call: `{"jsonrpc":"2.0", "method":"account_history_api.enum_virtual_ops", "params":{"block_range_begin": 4000000, "block_range_end": 4020000 }, "id":1} ` ah-rocksdb: ranges from 36s to 45s ah-sql: 0.8s These gains are good enough that we should be able to eliminate the artificial 2000 operation lookback limit currently set on the get_account_history API call when filtering for specific operation types (assuming the node is configured to serve the data from the ah-sql plugin instead of the ah-rocksdb plugin, of course). # Progress on modular hivemind Now while the above sync and API response times represent substantial performance gains, that’s not the primary goal of this work. The primary goal is to support the creation of 2nd layer app development using “modular hivemind” deployments, allowing for the creation of unique hive-based apps that can scale to large numbers of users with real-time responsiveness. The first step in the development of the modular hivemind technology was to validate swapping to a model where we directly inject blockchain data into hivemind’s PostgresSQL database, and as we can see from the benchmark data above, this work is going well. We’ve proved it has better performance and we were also able to switch from “pulling” the data from hived to having hived push the data to us using the existing hivemind indexer code with minor modifications. The next step is a bit more challenging, design-wise: we plan to add support automatic handling of forks in the hivemind sync process. This will allow for modular hivemind apps to serve up data from the current headblock without any additional delay and cleanly revert whenever a micro-fork occurs (currently 2nd layer apps either have to accept a delay penalty or risk the chance for error if they don’t properly revert the state of their internal data in the case of a micro-fork). Modular hivemind application will gain a built-in micro-fork handler that automatically executes to undo the effects of blocks from the previous fork and replay blocks from the new fork in the case of a fork, eliminating the need for their developers to manually write code for fork handling. We’re currently looking into various algorithms for this fork handling logic and next we’ll be experimenting with some prototype implementations. # Hivemind-based account history API We also recently completed a python-based account history API that reuses the SQL queries that we developed for the hived SQL account history plugin (this could be used, for example, to reduce API loading on a hived instance). The performance of this API was roughly comparable to that of the ah-sql plugin, but preliminary performance tests show it a little slower (maybe 30% in some cases), probably due to more overhead during data conversions. Still this could be an acceptable tradeoff in some situations and we haven't done any tests in high load situations, where hivemind may perform better than hived. # Hivemind API (2nd layer microservice for social media apps) We fixed bugs and created new tests related to the follow code and decentralized lists and merged them to the develop branch (https://gitlab.syncad.com/hive/hivemind/-/merge_requests/396). We also merged in a few more remaining optimizations to hivemind queries: https://gitlab.syncad.com/hive/hivemind/-/merge_requests/470 And created some more tests for community API calls: https://gitlab.syncad.com/hive/hivemind/-/merge_requests/466 # Condenser and wallet (https://hive.blog) We made one change to fix a bug related to decentralized lists: https://gitlab.syncad.com/hive/condenser/-/merge_requests/209 We completed a preliminary review of vision (ecency code base) versus condesner (hive.blog code base). In general the vision code looks more up-to-date in terms of web technologies and libraries used, and the code is cleaner overall, but the vision code currently relies on too much closed-source APIs to operate it easily as a white-labeled application yet. So before we begin contributing to this work, we’re awaiting changes from @good-karma team. # Near-term work plans and work in progress We’ll continue working on modular hivemind code, with an emphasis in the next cycle on design of micro-fork handling logic. We’ll also continue tests and optimization of SQL account history plugin and hivemind sync process. We began testing the current head of develop branch of hivemind (this contains follows and decentralized list fixes) on api.hive.blog today. If it performs well, we’ll merge it to master on Monday so that other API node operators can deploy it. I’ve also given some thought in the last week to ways to improve the upward side of the HBD peg (when HBD goes above $1 USD). I have one idea that looks relatively simple to implement that could probably be fit into HF25. I’ll write more about it later in a separate post in the Hive Improvements community so that there can be some discussion about the economic considerations involved.

See: 5th update on BlockTrades' Hive development progress by @blocktrades