J'utilise MariaDB 10.2.31 sur Ubuntu 18.4.4 LTS. Je rencontre régulièrement l'énigme suivante - en particulier lorsque je démarre le matin, c'est-à-dire lorsque mon environnement DEV a été inactif pendant la nuit - mais aussi pendant la journée de temps en temps.
J'ai une table (cela s'applique également à d'autres tables) avec env. 15.000 lignes et (entre autres) un index sur une colonne VARCHAR contenant en moyenne 5 à 10 caractères.
Notamment, la plupart des colonnes, y compris celle-ci, sont GENERATED ALWAYS AS (JSON_EXTRACT (....)) STORED puisque 99% de mes données proviennent d'une API REST sous forme de chaînes codées JSON (et commodément, je stocke simplement ceux dans une colonne et extraire tout le reste).
Lors de l'exécution d'une requête sur cette colonne WHERE colname LIKE 'text%' , je trouve des durées de résultat de requête de 0,006 secondes. Joli. Lorsque j'ai ma requête EXPLAIN , je peux voir que l'index est utilisé.
Cependant, comme je l'ai mentionné, quand je commence le matin, cela prend beaucoup plus de temps (14 secondes ce matin). Je connais le cache de requête et j'ai essayé ceci avec le cache de requête désactivé (à la fois via SET GLOBAL query_cache_type = OFF et RESET QUERY CACHE ). Dans ce cas, j'obtiens des temps cohérents d'env. 0,3 seconde - comme prévu.
Alors, que recommanderiez-vous que je devrais examiner? Ma DB dort-elle? Existe-t-il une telle chose?
3 Réponses :
Activez votre journal de requête lent avec log_slow_verbosity = query_plan, expliquez et le long_query_time suffisant pour attraper les résultats. Voyez s'il utilise parfois un index différent (ou aucun).
Avant de commencer votre journée suivante, regardez SHOW GLOBAL STATUS LIKE "innodb_buffer_pool%" et après votre requête, regardez à nouveau les valeurs. Voyez combien de lectures de pool de mémoire tampon par rapport aux demandes de lecture sont dans cette sortie d'état pour voir si toutes sortent du disque.
Comme @Solarflare l'a mentionné, les sauvegardes et l'activité nocturne peuvent purger le pool de mémoire tampon innodb des données mises en cache et revenir sur le disque pour le ralentir à nouveau. Dans le cadre de vos activités nocturnes, vous pouvez définir innodb_buffer_pool_dump_now = 1 sur enregistrer les pages chaudes avant l'activité scriptée et innodb_buffer_pool_load_now = 1 pour le restaurer.
Merci, je vais voir ce que je peux recueillir des sorties STATUS. Il semble que je devrai aller plus loin pour trouver la cause. ;)
Et apprenez des choses précieuses en attendant. Bon apprentissage. Au plaisir de voir les questions de suivi sur dba.stackexchange.com/.
très certainement! : D merci. Je mettrai à jour ma progression.
Il y a deux choses qui pourraient se produire:
1) Les caches froids (une sauvegarde de nuit, un redémarrage de mysqld ou un travail de traitement important entraîne l'expulsion de cet index et des données de table de la mémoire).
2) Les statistiques sur la table deviennent obsolètes et le planificateur de requêtes devient confus jusqu'à ce que vous exécutiez des requêtes sur la table et que les statistiques soient actualisées. Vous pouvez forcer une mise à jour en utilisant ANALYZE TABLE nom_table.
3) Le planificateur de requêtes heisenbug. Très courant dans MySQL 5.7 et versions ultérieures, jamais vu auparavant sur MariaDB, donc c'est plutôt improbable.
Vous pouvez aller au fond de cela en activant ce qui suit dans la configuration:
log_output='FILE' log_slow_queries=1 log_slow_verbosity='query_plan,explain' long_query_time=1
Ensuite, examinez ce qui se trouve dans le journal lent juste après avoir vu une occurrence lente. Si le plan d’explication enregistré est le même pour les cas lents et rapides, vous avez un problème de caches froids. S'ils sont différents, vous avez un problème de statistiques de table et vous devez créer ANALYZE TABLE à la fin de la tâche de nuit qui lit / écrit beaucoup dans cette table. Si cela ne vous aide pas, en dernier recours, codez en dur un indice d'index dans votre requête avec FORCE INDEX (index_name) .
heisenbug arrive encore, je l'ai vu au début de 10,0 jours. A écrit innodb_stats_traditional = OFF pour améliorer la collecte de statistiques. Avant de revenir à l'index forcer, regardez les statistiques persistantes à mon humble avis.
Après avoir désactivé mon démon, je semble obtenir de meilleurs résultats, donc je pense que ses nombreuses opérations de lecture / écriture sont la cause. C'est juste après un court laps de temps cependant. De plus, mon démon semble augmenter l'utilisation de la RAM (ce qui est l'une des choses sur ma liste TODO). Cela pourrait-il pousser les pages du pool de tampons ou est-ce de taille fixe?
Le pool de mémoire tampon est réservé, la pression de la mémoire ne le poussera pas (mais les choses peuvent planter en raison de l'état du MOO). Je vous suggère d'allouer votre mémoire dans d'énormes pages (grandes pages = 1 dans my.cnf et vous devrez configurer l'allocation de pages énorme séparément en utilisant sysctl, cela peut nécessiter un redémarrage pour libérer suffisamment de mémoire contiguë). Le résultat des pages énormes est que la mémoire qui leur est allouée est invisible pour les autres processus, et elles ne sont pas échangeables, de sorte que le pool de mémoire tampon ne peut pas être poussé pour échanger - une autre chose qui peut vous arriver s'il y a une forte pression de mémoire.
Bravo et merci à tous ceux qui ont donné des informations précieuses! D'après tous les conseils que vous avez donnés, je pense que je commence à mieux comprendre le problème et à le réduire:
La première chose que j'ai trouvée était mon innodb_buffer_pool_size par défaut de 134 Mo. Avec le type et la quantité de données que je traite, c'est ridiculement bas - j'ai donc pu l'augmenter.
Message très utile: https://dba.stackexchange.com/a/27341
Et à partir de la documentation: https: // dev .mysql.com / doc / refman / 8.0 / fr / innodb-buffer-pool-resize.html
Maintenant que je l'ai augmenté à près de 2 Go et que je suis capable de surveiller son utilisation et l'utilisation de la RAM en général (cli: cat / proc / meminfo), je me rends compte que ma RAM de 4 Go est en fait plutôt faible. Je suis loin de voir une surcharge inutilisée (utilisation de la mémoire tampon toujours à 99% et RAM libre autour de 100 Mo).
Je vais ensuite commencer à optimiser l'utilisation de la RAM de mon démon et voir où cela mène - mais cela ne libérera pas assez de RAM au total.
@danblack a mentionné innodb_buffer_pool_dump_now et innodb_buffer_pool_load_now . C'est une approche intéressante à utiliser peut-être chaque fois que le démon accède à la base de données car j'aimerais bien séparer l'utilisation du tampon de mon démon de celle du frontal (apparemment, ce n'est pas possible!). J'examinerai cela plus en détail, mais comme mon démon fonctionne tout le temps (pas seulement la nuit), cela pourrait ne pas être possible.
@Gordan Bobic a mentionné "rafraîchir" les tables DB en utilisant ANALYZE TABLE tableName . J'ai trouvé que c'était assez rapide et je l'ai incorporé dans le démon après chaque fois qu'il effectue une lecture / écriture approfondie. Cela augmente les temps d'exécution du démon de quelques secondes, mais ce n'est aucun problème. Et je pense que je ne peux pas me tromper avec ça :)
Donc, en fin de compte, je pense que mon problème est une combinaison de choses: trop petite taille de tampon, trop petite RAM, trop d'opérations de lecture / écriture pour cet environnement (suppression des index tamponnés, etc.). Je devrai également en savoir plus sur l'allocation de mémoire, etc. et optimiser cela mieux (grandes pages = 1, etc.).
C'est probablement une question pour dba.stackexchange.com , mais pour commencer quelque part: votre serveur de base de données fait-il quelque chose pendant la nuit / quelqu'un y accède ( par exemple les sauvegardes, le reste api, ...)? Cela inclut toutes les bases de données sur ce serveur.
J'ai un démon en cours d'exécution qui récupère et insère en permanence les données JSON mentionnées (et effectue d'autres travaux de nettoyage). Cela signifie que la base de données doit être utilisée à tout moment, même lorsque personne n'utilise le frontend.