SQLite: base de données dans un fichier mais avec un cache mémoire automatique pour l'écriture

Bonjour, Dans mon projet professionnel nous avons une application C++ qui collecte des données et événements chaque seconde et qui enregistre le tout dans une base de données, ici c’est SQLite 3 qui est utilisé. Une base de données représente une journée de ces données, à minuit on change de fichier et on compresse l’ancien.

Cependant la mémoire de masse est un eMMC qui a des capacités en écriture assez faibles et n’est pas très très fiable. Nous souhaitons donc réduire au maximum les opérations d’écriture, idéalement une fois par tranche de 15 minutes. On considère que la perte de données de cette ordre de grandeur n’est pas un problème.

La solution retenue par mon prédécesseur est d’ouvrir la base de données SQLite avec les pragma suivants :

PRAGMA temp_store=MEMORY;
PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint=0;

Ce qui permet d’avoir la journalisation en mémoire et de désactiver la sauvegarde automatique dans le fichier. Puis chaque table est en fait triplée lors de l’ouverture, la table du fichier nommée data reçoit ses copains data_trash et data_event qui sont des tables vides temporaires à l’ouverture. Ces deux nouvelles tables sont donc en mémoire et pas dans le fichier.

L’idée est que chaque suppression est enregistrée dans data_trash et chaque ajout dans data_event avant (toutes les 15 minutes) de synchroniser manuellement ce qui s’est passé dans data_trash et data_event dans data lui même. À ce moment là ces deux tables temporaires sont vidées et on recommence.

La mécanique fonctionne plutôt bien et est assez fiable. Du moins de ce que nous avons pu mesurer et tester en pratique. Mais niveau conception je ne trouve pas cela terrible. Il y a beaucoup de code assez tordu pour gérer tout cela. Et le besoin me semble assez élémentaire pour que sqlite propose quelque chose de similaire sans devoir gérer tout cela à la main.

Cependant j’ai parcouru la doc de sqlite sans trouver malheureusement de solutions internes qui géreraient ça de manière automatique avec juste quelques PRAGMA ou options de compilation. Mais j’ai pu louper ou mal comprendre un élément de la doc, d’où ma question si cela parle à certains.

Merci d’avance.

Je trouve bancal d’avoir 2 tables, une pour les suppression et une pour les ajouts. J’aurais juste fait une seule table “pending_actions” avec `ajout/suppression/update".

Actions effectivement effectuées toutes les 15minutes.

Je pense que ça simplifie la gestion des cas particuliers (ajout / suppression d’une même valeur qui n’est pas encore sauvegardée).

Je n’ai pas dit que le design était parfait, surtout que j’hérite de ce code.

Cependant si je peux déléguer cette gestion du cache à sqlite, ce serait encore mieux.

Ping @sgble

Hello

Je ne sais pas si j’ai une solution à ce problème prévis, mais voici quelques idées qui me viennent en tête.

Je penserais à un design de write buffer. Soit implémenté dans ton applicatif, éventuellement en wrappant l’interface d’appel de SQLite pour rendre le comportement transparent, ou bien en reposant sur une brique supplémentaire qui se place entre ton applicatif et SQLite, typiquement une in-memory database comme Redis (en utilisant une structure ordonnée comme une liste ou un stream).

En général, le médiateur entre l’applicatif et la base de données peut avoir un intérêt pour augmenter les performances en écriture d’un système qui doit ingérer de façon soutenue plein de petites écritures, au détriment de la sûreté des données (tu indiques que la perte de 15 minutes est toutefois acceptable) et de la latence en disponibilité dans la DB finale. En plus, ça permet d’écrire un batch complet, parfois en une seule et unique requête INSERT (ou en plusieurs, mais dans une même transaction).

Autre piste à laquelle je pense, mais que je n’ai pas testée personnellement en production (que tu as déjà un peu exploré, on dirait). SQLite permet de faire des tables temporaires dont le nom est préfixé par temp. ou temporary.. D’après mes tests empiriques rapides, un INSERT dedans n’implique pas une écriture sur le disque (contrairement au même INESRT dans une table classique).

CREATE TABLE temp.transient (t timestamp, content text); -- table "cache"
CREATE TABLE stable (t timestamp, content text); -- table définitive

-- Toutes les secondes :
INSERT INTO temp.transient (t, content) VALUES (datetime('now'), 'Coucou');

-- Toutes les 15 minutes, en une transaction :
BEGIN TRANSACTION;
  INSERT INTO stable SELECT * FROM temp.transient;
  DELETE FROM temp.transient;
COMMIT;

Attention, les tables temporaires ne restent présentes que tant que la connexion est établie !

EDIT :

Si tu te sens d’attaque pour écrire un module SQLite (en C++ ça devrait être possible), tu peux aussi implémenter un in-memory store en tant que module et créer une table virtuelle en l’utilisant :

CREATE VIRTUAL TABLE transient USING mon_mondule_in_memory (...);

Je penserais à un design de write buffer. Soit implémenté dans ton applicatif, éventuellement en wrappant l’interface d’appel de SQLite pour rendre le comportement transparent, ou bien en reposant sur une brique supplémentaire qui se place entre ton applicatif et SQLite, typiquement une in-memory database comme Redis (en utilisant une structure ordonnée comme une liste ou un stream).

Ah le retour de Redis. Je vois l’idée, cependant je ne suis pas sûr que cela simplifie beaucoup le sujet, ça ajoute même une bibliothèque en plus. Je peux regarder mais ça ne me semble pas la première approche que je tenterais en tout cas.

En général, le médiateur entre l’applicatif et la base de données peut avoir un intérêt pour augmenter les performances en écriture d’un système qui doit ingérer de façon soutenue plein de petites écritures, au détriment de la sûreté des données (tu indiques que la perte de 15 minutes est toutefois acceptable) et de la latence en disponibilité dans la DB finale. En plus, ça permet d’écrire un batch complet, parfois en une seule et unique requête INSERT (ou en plusieurs, mais dans une même transaction).

Les perfs en écriture dans notre cas n’est pas un problème, la BDD encaisse bien. Ce n’est qu’une insertion par seconde de 15 à 30 valeurs entières, ça reste assez léger.

Autre piste à laquelle je pense, mais que je n’ai pas testée personnellement en production (que tu as déjà un peu exploré, on dirait). SQLite permet de faire des tables temporaires dont le nom est préfixé par temp. ou temporary.. D’après mes tests empiriques rapides, un INSERT dedans n’implique pas une écriture sur le disque (contrairement au même INESRT dans une table classique).

J’ai l’impression que c’est plus ou moins la solution actuelle qui est en place. Bon en l’occurrence il y a une table en plus pour les suppressions mais c’est tout.

Peut être que c’est la meilleure approche, avoir une table qui sert de cache mais unifiée avec checkpoints manuels (car là c’est en semi automatique en fonction de la taille du contenu dans le cache) pour simplifier le code.

Par contre les requêtes pour lire les anciennes valeurs en tenant compte du cache sont toujours un peu pénible à cause de cela. Mais avec qu’une table de cache ce serait déjà plus simple.

Salut, tu as pu trouver une solution plus satisfaisante depuis le temps ?

Pour l’instant rien de satisfaisant qui réponde au cahier des charges et qui soit raisonnable à mettre en place malheureusement.

Pour le moment on se dirige vers une simplification mais en terme de conception des tables cela en restera ainsi, le temps peut être qu’un solution émerge de sqlite (ou qu’on puisse y contribuer directement :P).