Evaluate relational database systems
Summary
Following the sysadmin reply about MongoDB (#119 (closed)), we need to investigate the use of a relational DB to store the detector configuration. Factors influencing the choice are:
- Performance. The system needs to handle the amount of data we're going to throw at it sufficiently well.
- License. We'll need to install GEM systems out of CERN, therefore an open license is a must. This forbids Oracle.
- Availability. The chosen DB and client need to be available for CentOS 7 and 8 in a way that the sysadmins would be ready to support. We're going to need input from @cgalloni or @lpetre here.
- Ease of use from C++. This is nice to have but we can survive with a C library.
What is the expected correct behavior?
A relational DBMS is selected and we can start integrating it.
Performance
A simple test can be conducted as follows:
- Load around 1,000,000 rows in a table with 128 32-bits integers to mimic VFAT configuration data. (I used 800,000 for my MongoDB test.)
- Write a query that selects about 5000 rows from that table. This could be done by joining it with another table that contains foreign keys to the rows to fetch.
- Benchmark the query execution time with a "warm" cache. The target is to be faster than O(100ms).