Optimisation des performances avec PRAGMA

SQLite est conçu pour être léger et rapide, mais dans certains scénarios, vous pouvez obtenir des gains de performance considérables en ajustant certains paramètres. Cette section explore les PRAGMA qui peuvent vous aider à optimiser votre base de données SQLite pour de meilleures performances.

Gestion de la mémoire cache

PRAGMA cache_size

La taille du cache détermine combien de pages de base de données SQLite garde en mémoire. Augmenter cette valeur peut réduire les accès disque et améliorer les performances, au prix d’une utilisation accrue de la mémoire.

-- Consulter la taille du cache actuelle (en pages)
PRAGMA cache_size;

-- Définir la taille du cache en nombre de pages
PRAGMA cache_size = 10000;

-- Définir la taille du cache en kilo-octets (valeur négative)
PRAGMA cache_size = -10000;  -- Environ 10 Mo

Note : Lorsque vous spécifiez une valeur négative, SQLite interprète cela comme une taille en kilo-octets plutôt qu’en nombre de pages.

PRAGMA temp_store

Ce PRAGMA contrôle où sont stockées les tables temporaires créées lors de l’exécution de requêtes complexes.

-- Consulter le mode de stockage temporaire actuel
PRAGMA temp_store;

-- Définir le mode de stockage temporaire
PRAGMA temp_store = DEFAULT;  -- Utilise temp_store_directory ou le répertoire par défaut
PRAGMA temp_store = FILE;     -- Utilise des fichiers sur le disque
PRAGMA temp_store = MEMORY;   -- Utilise la mémoire (plus rapide mais consomme plus de RAM)

Utiliser MEMORY peut accélérer considérablement les requêtes complexes qui créent des tables temporaires, mais nécessite plus de mémoire disponible.

Mécanismes de verrouillage

PRAGMA locking_mode

Ce PRAGMA contrôle comment SQLite gère les verrous de base de données.

-- Consulter le mode de verrouillage actuel
PRAGMA locking_mode;

-- Définir le mode de verrouillage
PRAGMA locking_mode = NORMAL;  -- Comportement par défaut
PRAGMA locking_mode = EXCLUSIVE;  -- Garde un verrou sur le fichier DB

Le mode EXCLUSIVE peut améliorer les performances en gardant un verrou sur le fichier de base de données, mais limite l’accès concurrent par d’autres connexions.

Utilisation de la mémoire mappée

PRAGMA mmap_size

La mémoire mappée (mmap) permet à SQLite d’accéder au fichier de base de données comme s’il était en mémoire, ce qui peut améliorer les performances.

-- Consulter la taille mmap actuelle
PRAGMA mmap_size;

-- Définir la taille maximale pour la mémoire mappée (en octets)
PRAGMA mmap_size = 1073741824;  -- 1 Go

-- Désactiver mmap
PRAGMA mmap_size = 0;

En général, activer mmap avec une taille raisonnable peut améliorer les performances de lecture, surtout pour les grandes bases de données.

Optimisation des requêtes

PRAGMA optimize

Ce PRAGMA analyse votre base de données et effectue des optimisations automatiquement.

-- Optimiser toute la base de données
PRAGMA optimize;

-- Optimiser une table spécifique
PRAGMA optimize(ma_table);

PRAGMA analysis_limit

Ce PRAGMA contrôle combien de lignes sont examinées lors de l’analyse des index par la commande ANALYZE.

-- Consulter la limite d'analyse actuelle
PRAGMA analysis_limit;

-- Définir la limite d'analyse à 1000 lignes
PRAGMA analysis_limit = 1000;

Réduire cette valeur peut accélérer la commande ANALYZE sur de grandes bases de données, bien que les statistiques collectées puissent être moins précises.

Autres optimisations importantes

PRAGMA busy_timeout

Définit le temps d’attente en millisecondes avant d’abandonner si la base de données est verrouillée.

-- Définir un délai d'attente de 5 secondes
PRAGMA busy_timeout = 5000;

PRAGMA threads

Contrôle le nombre maximum de threads auxiliaires qu’une instruction préparée peut lancer.

-- Consulter le nombre de threads actuel
PRAGMA threads;

-- Permettre jusqu'à 4 threads auxiliaires
PRAGMA threads = 4;

Augmenter le nombre de threads peut améliorer les performances des requêtes complexes sur les systèmes multiprocesseurs.

Surveillance des performances

PRAGMA stats

Ce PRAGMA fournit des statistiques sur l’utilisation interne de SQLite (disponible uniquement en version de débogage).

PRAGMA stats;

Cas pratique : Configuration optimisée pour une application avec lecture intensive

Voici un exemple de configuration optimisée pour une application qui fait beaucoup plus de lectures que d’écritures :

-- Utiliser le mode WAL pour permettre les lectures pendant les écritures
PRAGMA journal_mode = WAL;

-- Réduire le niveau de synchronisation pour les performances
PRAGMA synchronous = NORMAL;

-- Cache important pour réduire les accès disque
PRAGMA cache_size = -50000;  -- Environ 50 Mo

-- Utiliser la mémoire mappée pour les performances de lecture
PRAGMA mmap_size = 536870912;  -- 512 Mo

-- Stocker les tables temporaires en mémoire
PRAGMA temp_store = MEMORY;

-- Permettre jusqu'à 4 threads auxiliaires
PRAGMA threads = 4;

-- Définir un délai d'attente raisonnable
PRAGMA busy_timeout = 3000;

Cas pratique : Configuration optimisée pour une application avec écriture intensive

Pour une application qui fait beaucoup d’écritures, vous pourriez utiliser cette configuration :

-- Mode journal classique peut être plus rapide pour les écritures fréquentes
PRAGMA journal_mode = MEMORY;  -- Dangereux mais rapide, ou WAL pour plus de sécurité

-- Réduire la synchronisation pour les performances d'écriture
-- ATTENTION: risque de perte de données en cas de panne
PRAGMA synchronous = OFF;

-- Cache important
PRAGMA cache_size = -100000;  -- Environ 100 Mo

-- Verouillage exclusif peut être plus rapide pour une seule connexion
PRAGMA locking_mode = EXCLUSIVE;

-- Mode batch pour les transactions
-- Rassemblez plusieurs opérations dans une seule transaction BEGIN...COMMIT

Stratégies d’optimisation et bonnes pratiques

Testez avant d’optimiser : Mesurez les performances avant et après les changements.
Équilibrez sécurité et performances : Certaines optimisations comme synchronous=OFF sacrifient la sécurité des données.
Adaptez à votre matériel : Les paramètres comme cache_size et mmap_size doivent être adaptés à la RAM disponible.
Considérez l’accès concurrent : Des paramètres comme locking_mode=EXCLUSIVE peuvent limiter l’accès concurrent.
Utilisez EXPLAIN QUERY PLAN : Pour identifier les requêtes inefficaces indépendamment des PRAGMA.
Segmentez les transactions : Des transactions trop longues peuvent bloquer d’autres connexions.
Créez les index appropriés : Souvent plus important que les PRAGMA pour les performances des requêtes.