Better database for Tribler/ Prevent Trustchain exit nodes wiping out

[grimadas]

Current problems for scalability

Current database cannot scale well and handling big amount of trustchain records. After some number of records we see a degradation in performance for exit nodes.
One of the solutions is to wipe out database and start over, but that results in identity, reputation lose.
Alternately, we can improve underlying trustchain database. I think a good start is to look into workload and see how other databases can handle that.

Abstract Database module

Currently we have at least three different entries to the database with hardcoded sql queries in the codebase: Upgrade, IPv8/Database.py and Metastore using pony-orm.
To simplify the migration from one database to another to improve salability and latency we need to abstract database access( we might want to look into graph, key/value databases in future).

A good starting point would be a database adapter and an sqlite implementation of it.

[ichorid]

I doubt it will be possible to abstract out Metadata Store access in the foreseeable future. PonyORM already provides a good level of abstraction. However, Trustchain can benefit greatly from switching to some NoSQL-based store (e.g. LevelDB). Building an abstraction level for storing blocks could become a nice step in that direction.

@qstokkink , @devos50, do any components of Tribler beside Trustchain use IPv8/Database.py?

[devos50]

@ichorid No, I don't think so.

[qstokkink]

The attestation community has its own Database + database schema.

[ichorid]

@qstokkink , would you consider moving the attestation community to use Pony?

[qstokkink]

@ichorid I just went through the code with @grimadas, doesn't seem like a good idea right now.

[grimadas]

I'll first do quick and dirty check if it is worth to migrate to db other than sqlite, and proceed after.

[synctext]

We require DB embedding! As just discussed: our choice and selection of database technology is significantly limited due to our requirement that everything is bundled within our installer. We can't rely on big servers with dedicated database services, our DB is running locally and competing for resources.

[grimadas]

These are benchmarking results for database behind tribler explorer.

Figure 1 is the average execution time for a query. You see that one query get_block_creation takes more than 40 sec.

Figure 2 is showing number of times when query was executed.

Figure 3 is the total time for the experiment.

[ichorid]

Block explorer is a special case. Could you try to capture a typical client Tribler workload session and build the same charts for it?

[qstokkink]

Maybe it would make sense then for the block explorer to use a different database type. As this is not used by our users anyway, we can change this without any major repercussions (we'll just have to sit through the databse conversion).

I also agree with @ichorid, this tells us nothing about the Tribler user load.

[grimadas]

I'll do local experiments now, imitating typical workload of a Tribler user: idle, downloading etc.
Also, exit node is another type of user with heavy workload

[grimadas]

Some results from the exit node. It seems that database queries are not the bottleneck.

[grimadas]

[devos50]

I made some modifications to the TrustChain crawler. First, I increased the statistics maintainance interval to one hour, which means that block creation statistics (the graph) is rebuilt every hour, instead of every five minutes. Aggregation of statistics is a major bottleneck as it executes a resource-intensive SQL query.

Second, I started to explore whether a key-value store can be utilized to improve the performance of the TrustChain crawler. Last week I was told that there was some initial work to rewrite the TrustChain persistence layer to use key-value (we also received various questions already why we were not using one already). @grimadas do you have some initial code/results for this?

[grimadas]

There are several options with good python bindings, but I guess one of the best matches for us is lmdb. It is pretty easy and supports async read/write with multiple threads.

[synctext]

Multi-core crawling is possible in near future?

[ichorid]

This is a good target for 7.5 release, not for the technical 7.4.

[qstokkink]

Looking at this issue, I think it is no longer applicable for the current state of affairs: exit nodes no longer run any database.