Paramétrage de MariaDB pour iTop 3.2

iTop 3.2 supporte officiellement :

• MariaDB 10.4, 10.6 (LTS), 10.11 (LTS) — la version 10.11 LTS est à privilégier pour les nouvelles installations.
• MySQL 8.0 (support limité, MariaDB reste le choix recommandé par Combodo).

Note : Avant toute modification de la configuration, effectuez une sauvegarde complète de vos bases et documentez les paramètres actuels avec SHOW VARIABLES.

iTop utilise exclusivement InnoDB pour ses tables. Assurez-vous qu’il est bien le moteur par défaut et que les tables ne sont pas en MyISAM (ce qui peut arriver après une migration).

-- Vérifier le moteur par défaut
SHOW VARIABLES LIKE 'default_storage_engine';

-- Lister les tables non-InnoDB
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema = 'itop'
  AND engine != 'InnoDB';

Avant de recopier la moindre valeur, retenez ceci : la quasi-totalité des paramètres mémoire de MariaDB se déduisent de la RAM disponible sur le serveur. Une configuration copiée telle quelle depuis un autre environnement est soit trop timide (RAM gaspillée), soit dangereuse (swap, voire OOM killer).

Le calcul en quatre postes

RAM utilisée ≈ buffer pool + surcoût interne + buffers de session + caches fixes
Marge restante = RAM totale − RAM utilisée (à garder pour l'OS)

Le piège des buffers de session. Ils sont alloués par connexion et uniquement quand une requête en a besoin. Le pire cas théorique (300 connexions qui trient toutes en même temps) donne 12 Mo × 300 = 3,6 Go, ce qui est énorme sur un serveur de 16 Go. En pratique, la concurrence réelle est de quelques dizaines de sessions actives, d’où le calcul sur 50 ci-dessus. Mais c’est la raison pour laquelle on n’augmente jamais ces buffers « au cas où » : la valeur se multiplie par le nombre de connexions.

Tous les paramètres ne dépendent pas de la RAM. Pour le reste, la valeur se déduit d’autre chose :

• innodb_log_file_size dépend du volume d’écriture, pas de la RAM. On dimensionne le redo log pour absorber environ une heure d’écritures de pointe.
• max_connections dépend du nombre d’utilisateurs et de tâches de fond iTop. Chaque connexion potentielle augmente le pire cas mémoire.
• max_allowed_packet dépend de la taille des imports et des pièces jointes iTop.
• query_cache_*, innodb_flush_*, charset, timeouts sont indépendants de la RAM : ce sont des choix de comportement.

Valeurs recommandées

Lecture du tableau : quand la RAM double, le buffer pool, les connexions et les buffers de session doublent aussi. Les paramètres de comportement (O_DIRECT, durabilité, query cache, charset) ne changent pas.

Deux points de vigilance :

• open_files_limit est plafonné par la limite système (LimitNOFILE dans l’unité systemd de MariaDB) : modifiez les deux, sinon la valeur du my.cnf est ignorée.
• innodb_buffer_pool_instances n’apparaît plus : il est inutile sur les versions recommandées (voir § 2).

-- Tables sans index primaire (ne devrait pas arriver avec iTop, mais vérifiez après migration)
SELECT table_schema, table_name
FROM information_schema.tables t
WHERE table_schema = 'itop'
AND NOT EXISTS (
SELECT 1 FROM information_schema.statistics s
WHERE s.table_schema = t.table_schema
AND s.table_name = t.table_name
AND s.index_name = 'PRIMARY'
);

-- Activer la collecte de statistiques si ce n'est pas déjà fait
SET GLOBAL userstat = ON;

-- Requête les index non utilisés
SELECT index_statistics.table_name, index_name, rows_read
FROM information_schema.index_statistics
WHERE table_schema = 'itop'
  AND rows_read = 0;

Sur une base iTop active depuis longtemps, les index InnoDB peuvent se fragmenter.

-- Analyser une table
ANALYZE TABLE itop.ticket;

-- Reconstruire les index (attention : verrou temporaire sur MariaDB < 10.6)
OPTIMIZE TABLE itop.ticket;

Tip : Préférez ALTER TABLE ... ENGINE=InnoDB sur les très grosses tables, car il est mieux géré par InnoDB et peut être exécuté en ligne (ALGORITHM=INPLACE) selon les versions.

Dans cette partie nous allons détailler un à un les différents paramètres configurable pour  optimiser votre base de données. 

Vérifier que vous avez les bons accès en bases de données, que celle-ci est en cours d'utilisation depuis suffisament longtemps pour avoir des données exploitables et que informations_schema est sur ON.

max_connections

iTop ouvre une connexion par utilisateur connecté, plus les tâches de fond (crons, synchronisations). Trop bas : les utilisateurs voient une erreur "Too many connections". Trop haut : chaque connexion consomme de la RAM même inactive.

-- 1. Valeur actuellement active
SHOW VARIABLES LIKE 'max_connections';

-- 2. Pic historique atteint depuis le dernier redémarrage
--    Si Max_used_connections est proche de max_connections, la limite est trop basse
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Max_used_connections_time';

-- 3. Connexions actuellement ouvertes
SHOW STATUS LIKE 'Threads_connected';

-- 4. Erreurs de connexion rejetées pour cause de saturation
--    Si Connection_errors_max_connections > 0, des utilisateurs ont été bloqués
SHOW STATUS LIKE 'Connection_errors_max_connections';

-- 5. Répartition des connexions par utilisateur (photo à l'instant T)
SELECT user, host, COUNT(*) AS connexions
FROM information_schema.processlist
GROUP BY user, host
ORDER BY connexions DESC;

Lecture du résultat : Max_used_connections / max_connections donne le taux de saturation historique. Si > 80 %, augmenter. Si < 30 % depuis longtemps, la valeur actuelle est surdimensionnée (surconsommation RAM inutile).

wait_timeout et interactive_timeout

Durée avant qu'une connexion inactive soit fermée. Trop bas : les scripts de synchronisation iTop longue durée voient des erreurs "MySQL server has gone away". Les deux doivent être alignés.

-- 1. Valeurs actives (doivent être identiques pour la cohérence)
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- 2. Connexions dormantes en ce moment et depuis combien de temps
SELECT id, user, host, command, time, state
FROM information_schema.processlist
WHERE command = 'Sleep'
ORDER BY time DESC
LIMIT 20;
-- Si des connexions Sleep dépassent la valeur de wait_timeout : anomalie côté applicatif (connexions non fermées)

net_read_timeout et net_write_timeout

Temps d'attente max pour lire (client → serveur) ou écrire (serveur → client) des données réseau. Critique pour les imports de données volumineuses dans iTop et les exports CSV/rapports.

-- 1. Valeurs actives
SHOW VARIABLES LIKE 'net_read_timeout';
SHOW VARIABLES LIKE 'net_write_timeout';

-- 2. Connexions en attente réseau en ce moment
SELECT id, user, time, state
FROM information_schema.processlist
WHERE state LIKE '%wait%'
   OR state LIKE '%send%'
ORDER BY time DESC;

max_allowed_packet

Taille maximale d'un paquet réseau ou d'une requête SQL. iTop stocke les pièces jointes et images dans la base. Une valeur insuffisante provoque des erreurs silencieuses lors des uploads.

-- 1. Valeur active (doit être ≥ 64M, idéalement 256M)
SHOW VARIABLES LIKE 'max_allowed_packet';

-- 2. Vérifier les erreurs de paquets trop grands
SHOW STATUS LIKE 'Aborted_clients';
SHOW STATUS LIKE 'Aborted_connects';

-- 3. Taille des plus grosses pièces jointes en base iTop
--    (nécessite d'être connecté sur la base iTop)
SELECT
    ROUND(MAX(LENGTH(contents)) / 1024 / 1024, 2) AS max_attachment_MB,
    ROUND(AVG(LENGTH(contents)) / 1024 / 1024, 4) AS avg_attachment_MB
FROM itop_document_content
WHERE contents IS NOT NULL;
-- Si max_attachment_MB > max_allowed_packet en MB : des uploads échoueront silencieusement

innodb_buffer_pool_size

Le paramètre le plus important. Cache en RAM des données et index InnoDB. Plus il est grand, moins le serveur lit le disque. Un buffer pool sous-dimensionné est la première cause de lenteur sur une instance iTop mature.

-- 1. Valeur active
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 2. Hit rate du buffer pool — l'indicateur le plus important
--    Objectif : > 99 %. En dessous de 95 % : augmenter le buffer pool.
SELECT
  ROUND(
    (1 - (
      (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
      /
      (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )) * 100, 4
  ) AS hit_rate_pct;

-- 3. Lectures depuis le disque vs depuis la mémoire (delta sur 60 secondes)
--    Méthode : noter les valeurs, attendre 60s, noter à nouveau, calculer la différence
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';          -- lectures disque
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- lectures totales (RAM + disque)

-- 4. Dirty pages en attente d'écriture sur disque
--    Si pages_dirty / pages_total > 75 % : le flush n'est pas assez rapide (voir innodb_io_capacity)
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';

-- 5. Taux d'occupation réel du buffer pool
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_free';
-- pages_free proches de 0 : buffer pool saturé, augmenter si RAM disponible

-- 6. Attente de pages libres (ne doit jamais augmenter)
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';
-- Si > 0 et en augmentation : saturation critique du buffer pool
-- Évolution : relever la valeur, attendre un cycle d'activité complet (idéalement 24 h),
-- relever à nouveau. Delta ÷ heures écoulées = attentes par heure sur la période.
-- idéalement cibler l'activité spécifique sur des périodes données, ce n'est peut-être pas la configuration qui est en cause.

-- 7. Comparaison avec la taille réelle des données InnoDB
SELECT
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_innodb_gb
FROM information_schema.tables
WHERE engine = 'InnoDB'
  AND table_schema NOT IN ('information_schema','performance_schema','mysql','sys');
-- Si total_innodb_gb < buffer_pool_size_gb : buffer pool surdimensionné (pas nocif mais inutile) -- Si total_innodb_gb >> buffer_pool_size_gb : hit rate faible attendu, augmenter

Lecture du résultat : Ici il faut bien comprendre les différentes formules et éléments de comparaison :

hit rate = (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) *100 : Proportion de fois ou mariaDB a trouvé le résultat de sa requête dans la RAM plutôt que dans le disque.
buffer pool libre = pages_free / pages_total * 100 : Partie libre de la RAM disponible pour de nouvelles requêtes.

Interprétation les plus communes :

innodb_buffer_pool_instances

Divise le buffer pool en N instances indépendantes pour réduire la contention sur les mutex. Sur MariaDB > 10.5 : cette variable est supprimée, la gestion des instances est calculé automatiquement, plus besoin d'indiquer les variables spécifiques pour chaque instance.

-- 1. Valeur active et version (détermine si la variable est pertinente)
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SELECT VERSION();

-- 2. Contention sur les mutex du buffer pool
--    (nécessite performance_schema activé)
SELECT object_name, count_star, sum_timer_wait / 1000000000 AS wait_ms_total
FROM performance_schema.rwlock_summary_by_instance
WHERE object_name LIKE '%buf_pool%'
ORDER BY sum_timer_wait DESC
LIMIT 10;
-- Si plusieurs instances ont une contention élevée : le nombre d'instances est peut-être insuffisant (MySQL/MariaDB ≤10.4)

innodb_log_file_size / innodb_redo_log_capacity

Taille des fichiers de log de transactions. Trop petit : checkpoints fréquents, pics d'I/O lors des écritures. La variable à utiliser dépend de la version 

-- 1. Valeur active (selon la version)
SHOW VARIABLES LIKE 'innodb_log_file_size';      -- MariaDB et MySQL < 8.0.30 
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';  -- MySQL 8.0.30+ 
-- 2. Mesurer le churn des redo logs (quantité de log générée par seconde) 
--    Méthode : relever le LSN, attendre 60s, relever à nouveau SHOW ENGINE INNODB STATUS\G 
-- Chercher : "Log sequence number" dans la section LOG -- Churn/s = (LSN_t2 - LSN_t1) / 60
 -- Capacité recommandée = Churn/s * 3600 (1h de marge) 
-- 3. Fréquence des checkpoints (indicateur indirect) 
SHOW STATUS LIKE 'Innodb_checkpoint%'; 
SHOW STATUS LIKE 'Innodb_os_log_written'; 
-- Innodb_os_log_written en augmentation rapide = redo log trop petit pour absorber la charge 
-- 4. Waits de log (ne doit pas augmenter) SHOW STATUS LIKE 'Innodb_log_waits'; 
-- Si > 0 : le log buffer est trop petit ou le disque est trop lent

innodb_log_buffer_size

Buffer en mémoire avant écriture dans les redo logs. Utile pour les transactions volumineuses (imports batch, synchronisations LDAP).

-- 1. Valeur active
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

-- 2. Attentes sur le log buffer (indique si le buffer est insuffisant)
SHOW STATUS LIKE 'Innodb_log_waits';
-- Si > 0 et en augmentation depuis le dernier redémarrage : augmenter innodb_log_buffer_size

-- 3. Quantité totale écrite dans les logs depuis le démarrage
SHOW STATUS LIKE 'Innodb_os_log_written';
-- Diviser par Uptime (secondes) = Mo/s moyens écrits en log
-- Si >> innodb_log_buffer_size / 10 : buffer potentiellement trop petit

innodb_flush_log_at_trx_commit

Trade-off durabilité/performance : à quel moment les logs de transaction sont écrits sur disque.

-- 1. Valeur active
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 1 = flush à chaque commit (ACID complet, recommandé en production bancaire)
-- 2 = flush toutes les secondes (recommandé pour iTop : perte max 1s en cas de crash OS)
-- 0 = flush toutes les secondes côté MariaDB uniquement (risque plus élevé)

-- 2. Vérifier que la durabilité correspond aux attentes
--    (pas d'indicateur dynamique direct : vérification de la valeur suffit)

-- 3. Impact mesurable sur les écritures (latence des commits)
--    Comparer les temps d'exécution d'un INSERT avec flush=1 vs flush=2
--    (test à faire en staging, pas en production)

Note : Le script recommande 2 (performance). Le guide complet recommande 1 (durabilité totale). Choisir selon la tolérance au risque du client — pour un ITSM non financier, 2 est défendable.

innodb_flush_method

Méthode d'écriture sur disque. O_DIRECT évite la double mise en cache OS + InnoDB, crucial pour ne pas gaspiller de RAM.

-- 1. Valeur active
SHOW VARIABLES LIKE 'innodb_flush_method';
-- O_DIRECT : recommandé sur Linux avec stockage local (SSD, HDD en direct)
-- Ne pas utiliser avec NFS ou certains SAN/NAS

-- 2. Vérifier le type de stockage (en shell, pas en SQL)
-- df -T /var/lib/mysql
-- lsblk -d -o name,rota,size /dev/sda
-- rota=0 : SSD/NVMe (O_DIRECT pleinement efficace)
-- rota=1 : HDD (O_DIRECT reste recommandé sur Linux)

innodb_io_capacity et innodb_io_capacity_max

Indique à InnoDB combien d'IOPS sont disponibles pour vider les dirty pages. À calibrer selon le stockage réel — c'est la valeur que le plus de gens oublient d'adapter.

-- 1. Valeurs actives
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'innodb_io_capacity_max';

-- 2. Dirty pages accumulées (si trop élevé : io_capacity trop bas pour votre stockage) (performance_schema = ON)
SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') AS dirty_pages,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') AS total_pages,
  ROUND(
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty')
    * 100.0 /
    NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'), 0)
  , 2) AS dirty_pct;
-- dirty_pct > 50 % en continu : innodb_io_capacity trop bas pour votre charge d'écriture

-- ou performance_schema = OFF
SELECT  
dirty.val AS dirty_pages,  
total.val AS total_pages,  
ROUND(dirty.val * 100.0 / NULLIF(total.val, 0), 2) AS dirty_pct 
FROM  
(SELECT CAST(VARIABLE_VALUE AS UNSIGNED) AS val  
  FROM information_schema.GLOBAL_STATUS  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') dirty,
(SELECT CAST(VARIABLE_VALUE AS UNSIGNED) AS val  
  FROM information_schema.GLOBAL_STATUS  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') total;
-- 3. Activité I/O réelle (en shell)
-- iostat -x 1 10 /dev/sda
-- Comparer %util et await avec innodb_io_capacity configuré

Référence IOPS par type de stockage :

innodb_read_io_threads et innodb_write_io_threads

Threads dédiés aux I/O asynchrones. iTop effectue beaucoup de lectures (listes, recherches) et des écritures par lots (imports, synchros).

-- 1. Valeurs actives
SHOW VARIABLES LIKE 'innodb_read_io_threads';
SHOW VARIABLES LIKE 'innodb_write_io_threads';

-- 2. Activité réelle des threads I/O
SHOW ENGINE INNODB STATUS\G
-- Chercher la section "FILE I/O" :
-- Pending flushes (fsync) ou "reads/writes pending"  : si > 0 en permanence, les threads sont saturés
-- Augmenter les threads si le nombre de cœurs CPU le permet

Lecture du résultat : En dehors de la vérification sur Pending flushes (fsync) proche ou égale à 0, il est intéressant de noter les différence entre les lectures/s et écritures/s pour les comparer avec l'utilisation qui est faite de la base de données et les différentes valeurs précédemment analysé à savoir le buffer_pool size.

innodb_adaptive_hash_index

InnoDB construit des index de hachage en mémoire pour les recherches répétitives. Recommandé ON pour iTop dont les listes accèdent souvent aux mêmes index.

-- 1. Valeur active
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

-- 2. Utilisation effective de l'AHI
SHOW ENGINE INNODB STATUS\G
-- Chercher la section "INSERT BUFFER AND ADAPTIVE HASH INDEX"
-- hash searches/s élevé : l'AHI est actif et utile
-- Si contention sur les mutex AHI mentionnée : envisager de le désactiver

-- 3. Statistiques AHI via STATUS
SHOW STATUS LIKE 'Innodb_adaptive_hash%';
-- Innodb_adaptive_hash_hash_searches 
-- Innodb_adaptive_hash_non_hash_searches

Lecture du résultat : Comparer la part d'utilisation de AHI (Adaptive Hash Index) par rapport aux recherches qui ne l'utilisent pas.

innodb_buffer_pool_dump_pct

Pourcentage du buffer pool sauvegardé à l'arrêt et rechargé au démarrage. Évite la période de « chauffe » après un redémarrage.

-- 1. Valeur active
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_pct';

-- 2. Vérifier que le dump/restore est activé
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_at_shutdown';
SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup';
-- Les deux doivent être ON pour que innodb_buffer_pool_dump_pct soit utile

-- 3. Statut du dernier restore (après un redémarrage)
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
-- "Loaded xxxxxx pages" : le restore a fonctionné
-- "not started" : le restore n'a pas eu lieu (vérifier les variables ci-dessus)

-- 4. Déclencher un dump manuel (sans redémarrer)
-- SET GLOBAL innodb_buffer_pool_dump_now = ON;
-- SET GLOBAL innodb_buffer_pool_load_now  = ON;  -- pour tester le restore

-- 5. Vérifier que le fichier de dump existe (en shell)
-- ls -lh /var/lib/mysql/ib_buffer_pool

query_cache_type et query_cache_size

Le query cache est contre-productif sur iTop (charge mixte lecture/écriture). Chaque écriture invalide toutes les entrées cache d'une table, créant une contention globale sur le mutex du cache. Supprimé en MySQL 8.0 — ne jamais mettre ces variables dans my.cnf sur MySQL 8.

-- 1. Valeurs actives (les deux doivent être à 0 / OFF)
SHOW VARIABLES LIKE 'query_cache_type';
SHOW VARIABLES LIKE 'query_cache_size';
-- Sur MySQL ≥ 8.0 : ces variables n'existent pas (résultat vide = normal)

-- 2. Vérifier qu'aucune mémoire n'est allouée au cache
SHOW STATUS LIKE 'Qcache%';
-- Qcache_free_memory doit être 0 (ou la variable absente sur MySQL 8)
-- Qcache_lowmem_prunes élevé = cache trop petit ET actif = double problème

-- 3. Si le cache est encore actif, mesurer sa nuisibilité
SHOW STATUS LIKE 'Qcache_inserts';     -- entrées ajoutées
SHOW STATUS LIKE 'Qcache_hits';        -- hits
SHOW STATUS LIKE 'Qcache_not_cached';  -- requêtes exclues

-- Ratio utile = Qcache_hits / (Qcache_hits + Qcache_inserts)
-- En dessous de 20 % : le cache coûte plus qu'il ne rapporte

Rappel important : tous ces buffers sont alloués par connexion, pas globalement. Avec max_connections = 300 et sort_buffer_size = 4M, le pire cas théorique est 300 × 4M = 1,2 Go rien que pour le sort buffer. Ajuster en conséquence.

sort_buffer_size

Buffer pour les ORDER BY et GROUP BY sans index. iTop trie ses listes de tickets, CIs, contacts par de nombreux critères.

-- 1. Valeur active
SHOW VARIABLES LIKE 'sort_buffer_size';

-- 2. Tris débordant sur disque (Sort_merge_passes > 0 = tri sur disque)
SHOW STATUS LIKE 'Sort_merge_passes';
SHOW STATUS LIKE 'Sort_rows';
SHOW STATUS LIKE 'Sort_scan';

-- Ratio : Sort_merge_passes / Sort_rows
-- Si > 0 régulièrement : sort_buffer_size insuffisant pour les tris courants
-- Avant d'augmenter le buffer : vérifier si des index manquants sont la vraie cause

join_buffer_size

Buffer pour les jointures sans index. iTop génère des requêtes avec 10-15 jointures entre ses tables objets.

-- 1. Valeur active
SHOW VARIABLES LIKE 'join_buffer_size';

-- 2. Jointures sans index (le vrai problème à résoudre avant d'augmenter le buffer)
SHOW STATUS LIKE 'Select_full_join';
-- Select_full_join > 0 = jointures sans index existantes
-- Idéalement 0. Si non nul : chercher les requêtes via le slow log plutôt qu'augmenter le buffer

SHOW STATUS LIKE 'Select_range_check';
-- Jointures partiellement optimisées

-- 3. Via sys schema (si performance_schema activé)
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC
LIMIT 10;

read_buffer_size et read_rnd_buffer_size

Buffers pour les lectures séquentielles et aléatoires. Utiles pour les rapports iTop qui scannent de grandes tables.

-- 1. Valeurs actives
SHOW VARIABLES LIKE 'read_buffer_size';
SHOW VARIABLES LIKE 'read_rnd_buffer_size';

-- 2. Full scans en cours ou récents
SHOW STATUS LIKE 'Handler_read_rnd%';
-- Handler_read_rnd_next élevé = beaucoup de lectures séquentielles
-- Si > Handler_read_key : beaucoup de full scans → chercher des index manquants

SHOW STATUS LIKE 'Handler_read_key';
-- Lectures via index : doit être >> Handler_read_rnd_next dans un système bien indexé

tmp_table_size et max_heap_table_size

Taille maximale d'une table temporaire en mémoire. Dépassé → la table bascule sur disque, impact fort sur les performances. Ces deux valeurs doivent toujours être identiques.

-- 1. Valeurs actives (doivent être ÉGALES)
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

-- 2. Tables temporaires créées sur disque vs en mémoire
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';

SELECT
  ROUND(
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_disk_tables')
    * 100.0 /
    NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_tables'), 0)
  , 2) AS disk_tmp_pct;

-- 3. Identifier les requêtes générant des temp tables sur disque (sys schema)
SELECT query, db, tmp_disk_tables_total, exec_count
FROM sys.statement_analysis
WHERE tmp_disk_tables_total > 0
ORDER BY tmp_disk_tables_total DESC
LIMIT 10;

Attention : sur iTop, un ratio élevé ne signifie pas forcément un buffer trop petit. Une table temporaire bascule sur disque pour deux raisons distinctes : elle a dépassé tmp_table_size (le cas classique), ou elle contient une colonne incompatible avec le moteur MEMORY, ce qui la place sur disque dès sa création, même avec une seule ligne. Le calcul du friendlyname d'iTop déclenche ce second cas de façon systématique.

Avant d'augmenter les valeurs, ventilez le ratio par requête avec la requête ci-dessous. Si les requêtes en tête ont un temps moyen très faible et un ratio disque proche de 100 %, augmenter les buffers ne changera rien. 

Ce sujet fera l'objet d'un prochain article, avec l'analyse détaillée et les pistes de correction.

SELECT query, tmp_tables, tmp_disk_tables, exec_count,
       ROUND(tmp_disk_tables * 100.0 / NULLIF(tmp_tables, 0), 1) AS pct_disk,
       ROUND(avg_latency / 1000000000, 2) AS avg_ms
FROM sys.statement_analysis
WHERE tmp_disk_tables > 0
ORDER BY tmp_disk_tables DESC
LIMIT 10;

thread_cache_size

Nombre de threads conservés en réserve après la fermeture d'une connexion, prêts à être réutilisés par une nouvelle connexion plutôt que d'en créer un nouveau (opération coûteuse en mémoire et en initialisation).

-- 1. Valeur active
SHOW VARIABLES LIKE 'thread_cache_size';

-- 2. Threads réellement créés depuis le démarrage (= ratés de cache)
SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Connections';

-- 3. Taux de miss
SELECT ROUND(Threads_created.val * 100.0 / NULLIF(Connections.val, 0), 2) AS pct_thread_miss
FROM
  (SELECT VARIABLE_VALUE AS val FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_created') Threads_created,
  (SELECT VARIABLE_VALUE AS val FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Connections') Connections;

Lecture : un taux < 1-2 % est excellent, le cache absorbe presque toutes les connexions. Au-delà de 10-20 %, thread_cache_size est trop petit pour le volume de connexions courtes typique d'iTop (une par utilisateur actif, plus les crons). Attention à l'effet de bord après un redémarrage : Threads_cached repart à 0, et les premières connexions créent mécaniquement des threads le temps que le cache se reconstitue — un taux ponctuellement élevé juste après un restart n'est pas forcément un signal de sous-dimensionnement.

thread_handling et thread_pool_size

Sur MariaDB, le thread pool (pool-of-threads) est natif et disponible gratuitement dans toutes les éditions à partir de la version 10.4 — contrairement à MySQL, où sa disponibilité dépend de l'édition. Il regroupe les connexions par petits groupes de threads plutôt que d'en attribuer un par connexion, ce qui devient plus efficace que le mode par défaut one-thread-per-connection dès que le nombre de connexions courtes et concurrentes augmente — exactement le profil d'iTop en usage normal.

-- 1. Mode actif
SHOW VARIABLES LIKE 'thread_handling';

-- 2. Disponibilité du plugin avant d'envisager l'activation
SHOW PLUGINS LIKE 'thread_pool%';

-- 3. Si activé : nombre de groupes de threads (recommandé = nombre de cœurs CPU)
SHOW VARIABLES LIKE 'thread_pool_size';

Vérifiez toujours la disponibilité du plugin avant d'activer thread_handling = pool-of-threads — une activation sans le plugin chargé échoue au démarrage. thread_pool_size n'a d'effet que si ce mode est actif ; sur un serveur 8 cœurs, une valeur de 8 est un point de départ raisonnable.

group_concat_max_len

iTop utilise intensivement GROUP_CONCAT() pour construire ses listes d'objets liés — par exemple la liste des CI rattachés à un ticket, ou les membres d'une équipe. La valeur par défaut (1024 octets) est largement insuffisante et provoque des troncatures silencieuses dans les listes affichées, sans erreur visible côté application.

SHOW VARIABLES LIKE 'group_concat_max_len';
-- Doit être au minimum 1M (1048576) sur une instance iTop

Vigilance particulière : une troncature silencieuse ne remonte aucune erreur SQL ni log applicatif — elle se traduit juste par une liste incomplète à l'écran. C'est un des rares paramètres où le symptôme n'est pas une lenteur mais une donnée fausse.

optimizer_search_depth

Contrôle la profondeur d'exploration de l'optimiseur pour choisir un plan d'exécution. iTop génère fréquemment des requêtes à 10-15 jointures : laisser l'optimiseur en mode automatique (0) évite les timeouts d'optimisation sur ces requêtes complexes et produit en général de meilleurs plans qu'une profondeur fixe.

SHOW VARIABLES LIKE 'optimizer_search_depth';
-- 0 = automatique, recommandé pour les requêtes multi-jointures d'iTop

key_buffer_size

iTop utilise exclusivement InnoDB pour ses tables applicatives, mais les tables système (mysql.*) restent en MyISAM. Ce buffer cache leurs index — une valeur modeste (environ 6 Mo) suffit largement, sauf si des tables MyISAM personnalisées ont été ajoutées hors du périmètre iTop.

SHOW VARIABLES LIKE 'key_buffer_size';

-- Vérifier s'il existe des tables MyISAM en dehors des tables système
SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql');

query_prealloc_size

Mémoire pré-allouée pour le parsing et l'exécution de chaque requête. Une valeur légèrement supérieure au défaut réduit les allocations dynamiques répétées sur les requêtes courtes et fréquentes — impact marginal à l'unité, mais mesurable cumulé sur une instance qui traite plusieurs milliers de requêtes par seconde.

SHOW VARIABLES LIKE 'query_prealloc_size';

L'optimisation de MariaDB pour iTop repose sur un nombre restreint de leviers, mais leur ordre compte : un buffer pool correctement dimensionné et un query cache désactivé posent les fondations ; des index alignés sur les usages réels et une maintenance régulière (statistiques à jour, sauvegardes systématiques) les maintiennent dans le temps. Aucun de ces réglages ne demande de modifier iTop lui-même.

Ce guide reste néanmoins un point de départ. Chaque paramètre donné ici comme « recommandé » doit être confronté aux compteurs réels de votre instance avant/après changement, sur au moins 24 à 48 heures de charge — et l'expérience montre que les ralentissements les plus tenaces ne se résolvent pas toujours dans my.cnf