0

Is my proposed schema below prone to make overly complex and slow running queries? Is there a better way to layout this database?

The situation:

I have no formal DBA training.

tl;dr section below this one.

  1. We have a piece of in-house developed scientific software that communicates with multiple different sensors/devices and performs lots of math in real-time using data polled from these devices. Real-time processing is a hard requirement.
  2. Each time the "run" button is pressed, and until the "stop" button is pressed, we call that a "session". A session lasts for up to five hours and usually occurs twice a day.
  3. Each time the data is polled and calculations are run, we call that an "acquisition" or "acq".
  4. Each session has metadata associated with it that stores the start datetime, stop datetime, notes, field personnel, etc.
  5. The equations that are computed each acq are fully customizable and can vary drastically based on the site/project that the system is deployed to and they can, in uncommon circumstances, change slightly from within the software after the system is deployed. Each acq must be associated with its relevant session.
  6. The number of equations being computed each acq can be anywhere from ~100 to ~400.
  7. The frequency of acqs can be up to three times per second.

The situation tl;dr

Batch of hundreds of configurable equations being run up to 3 times per second for sessions of several hours at a time. Each batch calculation we call an "acquisition". Each session has associated metadata.

Current schema/config:

Database engine:

Currently thinking local SQLite with automated differential sync up to a MariaDB on LAN which can then sync up to "home" over WAN if the site has internet access at all.

Tables

I drew this up quickly so I didn't fully sanity check it but I think it's indicative well enough to what I'm thinking.

Sessions
+-------------------+---------------------+-----+----------------+-----------------+
| Field             | Type                | Key | Extra          | Foreign Key     |
+-------------------+---------------------+-----+----------------+-----------------+
| id                | bigint(20) unsigned | PRI | auto_increment |                 |
| session_name      | varchar(30)         |     |                |                 |
| start_time        | datetime            |     |                |                 |
| stop_time         | datetime            |     |                |                 |
| field_team_leader | varchar(50)         |     |                |                 |
| comments          | varchar(4096)       |     |                |                 |
+-------------------+---------------------+-----+----------------+-----------------+
Equations
+-------------------+---------------------+-----+----------------+-----------------+
| Field             | Type                | Key | Extra          | Foreign Key     |
+-------------------+---------------------+-----+----------------+-----------------+
| id                | bigint(20) unsigned | PRI | auto_increment |                 |
| title             | varchar(60)         |     |                |                 |
| formula           | varchar(300)        |     |                |                 |
+-------------------+---------------------+-----+----------------+-----------------+
Acquisitions
+-------------------+---------------------+-----+----------------+-----------------+
| Field             | Type                | Key | Extra          | Foreign Key     |
+-------------------+---------------------+-----+----------------+-----------------+
| id                | bigint(20) unsigned | PRI | auto_increment |                 |
| sequence_number   | int(11)             |     |                |                 |
| timestamp         | datetime            |     |                |                 |
| session_id        | bigint(20) unsigned | MUL |                | Sessions.id     |
+-------------------+---------------------+-----+----------------+-----------------+
AcquisitionEquationValues
+-------------------+---------------------+-----+----------------+-----------------+
| Field             | Type                | Key | Extra          | Foreign Key     |
+-------------------+---------------------+-----+----------------+-----------------+
| id                | bigint(20) unsigned | PRI | auto_increment |                 |
| acquisition_id    | bigint(20) unsigned | MUL |                | Acquisitions.id |
| equation_id       | bigint(20) unsigned | MUL |                | Equations.id    |
| value             | double              |     |                |                 |
+-------------------+---------------------+-----+----------------+-----------------+

This schema will result in hundreds or thousands of INSERTs per second into the Acquisitions and AcquisitionEquationValues tables. Is this the best way to layout the database for such an operation?

5
  • Does the schema allow you to do what you need to do? If yes, it's correct; if not, it is not. Simple as that.
    – mustaccio
    Commented Feb 21 at 2:04
  • I'm worried about it potentially being inefficient and prone to being slow. Commented Feb 21 at 3:20
  • A schema cannot be "fast" or "slow"; it's a logical construct. Queries can be slow, but you don't show any.
    – mustaccio
    Commented Feb 21 at 13:14
  • Sure, a schema cannot be fast or slow but, a schema impacts the complexity of the queries which in turn can cause the operation to be slow, as far as I'm aware. I'll try to add a little to my question. Commented Feb 21 at 21:21
  • It's the SELECTs that are "fast or slow", not the schema. Let's see them.
    – Rick James
    Commented Feb 22 at 3:33

1 Answer 1

0

As mustaccio and Rick James stated in the comments, a schema is not inherently fast or slow. However, if you are concerned about performance when doing thousands of inserts in a SQLite database, I can offer some advices.

Do not blindly use the MYSQL schema you describe in SQLite, but take instead advance of some SQLite specific implementations. More specifically:

  1. Define your IDs as INTEGER PRIMARY KEY. This will make them substitutes of ROWIDs, reducing the size of the rows and allowing the fastest possible search by IDs, without the need for a separate index. source
  2. INTEGER PRIMARY KEYs are autoincremented by default. Do not specify the AUTOINCREMENT option, because that will add some overhead to the autoincrement algorithm that you don't need in your use case. source
  3. While SQLite supports foreign keys, enforcing them will slow down your inserts. If your application can afford the loss of some of these relations without crashing, you could drop them. (Please note that SQLite will not lose any data by itself. I'm referring to any loss caused by errors in your application). source
  4. To achieve a fast rate of INSERTs, they must be batched in transactions. Based on your description I suggest that you should encapsulate in a transaction (BEGIN TRANSACTION; ... COMMIT;) all the INSERTs that belongs to an acquisition, so you would have 100-400 inserts for every transaction and up to 3 transactions per second. This also means that in case of errors or failures, an acquisition will either be completely written to the db or completely lost, but never half-written. source

It should not be necessary, but another advanced optimization you could adopt is the following. Instead of letting SQLite pick an auto incrementing value for IDs, you could choose those yourself. So if your equations are less than 9999 your table AcquisitionEquationValues could have an ID composed as (acquisition_id * 10000 + equation_id). This way you could drop both acquisition_id and equation_id (and the corresponding indexes) from that table.

Querying for values belonging to acquisition_id = N would be simply a matter of selecting values where id between (N * 10000) and (N * 10000 + 9999) which would be an highly optimized query since values are stored in ID order.

1
  • Thank you for these tips! This is exactly the kind of help I was looking for. I know just enough about DBA to get myself into trouble so I appreciate your take on this very much. Commented Feb 25 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.