Dans ce blog, je vais discuter de l’utilisation du filtre MaxScale Top pour l’analyse des requêtes et, en particulier, comment l’utiliser pour trouver facilement les requêtes les plus lentes pour un utilisateur particulier sans utiliser le journal lent. Le filtre supérieur est un excellent outil pour l’analyse des requêtes. Il peut être activé sans temps d’arrêt et les journaux ne nécessitent pas d’espace de stockage sur le serveur de base de données lui-même.
MaxScale est un proxy de base de données intelligent avancé de MariaDB. Il fournit des fonctionnalités d’équilibrage de charge et de haute disponibilité et inclut des capacités de filtrage faciles à utiliser. Les fonctionnalités MaxScale sont implémentées sous forme de modules. Il est donc facile d’intégrer les modules nécessaires à votre cas d’utilisation particulier.
Qu’est-ce que le filtre supérieur ?
Les modules de filtrage de MaxScale traitent les demandes après que MaxScale a reçu la demande du client, mais avant que le service n’achemine la demande vers un serveur de base de données principal. Les filtres peuvent enregistrer, modifier ou rejeter la demande, selon le filtre spécifique utilisé. Le filtre Top peut être placé dans ce pipeline pour surveiller et mesurer le temps d’exécution de chaque instruction au sein d’une connexion et consigner les détails des instructions les plus longues dans un fichier.
Architecture
Comment ça marche?
MaxScale gère les connexions d’application entrantes et, en fonction des paramètres de correspondance et d’exclusion, le filtre Top effectue le suivi des instructions exécutées dans la base de données et surveille le temps de réponse de chaque requête. Si le temps de réponse est plus long que pour les autres instructions qui ont été enregistrées, cette requête et les informations sur le temps de réponse sont ajoutées à la liste ordonnée dans le filtre jusqu’au nombre maximum « N » à stocker (défini avec le count paramètre).
Si vous avez défini le count dans le fichier de configuration MaxScale, le filtre Top n’enregistrera que ce nombre d’instructions. Par exemple, si nous fixons count=10 le rapport Top filter comprendra les 10 déclarations les plus anciennes, ainsi que des données récapitulatives pour cette session. Une fois la session terminée, un rapport sera écrit dans un fichier journal spécifique à la session qui est construit en fonction de l’ID de session et du paramètre filebase.
Filtre supérieur La journalisation des requêtes contiendra :
- Les N premières requêtes sont exécutées par la connexion et le temps d’exécution pour chacune.
- L’heure à laquelle la connexion a été ouverte.
- Détails de la connexion hôte.
- Le nom d’utilisateur utilisé dans la connexion.
- Durée de la connexion.
- Nombre total d’instructions exécutées.
- Temps d’exécution moyen d’une instruction.
Options de filtre
base de fichiers – Il s’agit d’un paramètre obligatoire à ajouter à maxscale.cnf. Il crée un nom de base du fichier de sortie pour chaque session.
compter – configure le nombre total « N » d’instructions SQL à stocker et à rapporter. Si plus de « N » instructions sont exécutées, seul le « N » supérieur avec les temps d’exécution les plus longs sera conservé. La valeur par défaut est 10, donc le fichier de sortie contiendra par défaut les 10 premières requêtes lentes.
correspondance, exclusion et options — Ces paramètres peuvent être utilisés pour inclure ou exclure des requêtes de la journalisation à l’aide d’expressions régulières. Le paramètre « options » est utilisé pour configurer le mode de traitement de l’expression régulière, comme la sensibilité à la casse et l’utilisation ou non d’une expression régulière étendue.
utilisateur – active la journalisation du filtre supérieur pour les sessions utilisées par le nom d’utilisateur donné. Si le paramètre n’est pas défini, les sessions de tous les utilisateurs seront enregistrées, à l’exception des sessions exclues par le paramètre « source ».
la source – Seules les sessions qui proviennent de cette adresse seront enregistrées.
Exemple de configuration
À titre d’exemple, j’ai configuré une configuration principale/réplique avec MaxScale comme équilibreur de charge. Pour plus de détails sur l’installation et la configuration, consultez la documentation MariaDB.
Server 1 - 192.168.56.113
Server 2 - 192.168.56.114
MaxScale - 192.168.56.115
maxctrl > list servers
┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼─────────────────┤
│ server1 │ 192.168.56.113 │ 3306 │ 0 │ Master, Running │ 0-2-42,1-1-4344 │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼─────────────────┤
│ server2 │ 192.168.56.114 │ 3306 │ 0 │ Slave, Running │ 0-2-42,1-1-4344 │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴─────────────────┘Configuration du filtre supérieur
Utilisez MaxCtrl pour créer un nouveau filtre ou modifiez le fichier de configuration MaxScale. Pour éditer le fichier de configuration, dans cette configuration Top filter, après le [monitor] module, nous devons ajouter module=topfilter sous le [LogFilter] dans le fichier de configuration MaxScale.
[LogFilter]
type=filter
module=topfilter
filebase=/tmp/slowquery
count=20Ensuite, nous devons définir le service sous le [Service] étiqueter.
[Service]
type=service
router=readconnroute
servers=server1,server2
user=max_mon
password=XXXXX
filters=LogFilterUne fois les paramètres ci-dessus ajoutés au fichier de configuration MaxScale, redémarrez le service MaxScale.
Cas d’utilisation de la production
Prenons le cas d’utilisation suivant comme exemple.
Supposons que chaque jour, nous exécutons des requêtes de rapport à 2 heures du matin. Pendant ce temps, notre utilisation des ressources de production est très élevée. Nous avons récemment ajouté de nouvelles requêtes à notre module de rapport. Au cours des deux derniers jours, les rapports n’ont pas été générés correctement, nous souhaitons donc collecter les requêtes les plus lentes pour le report_user.
MaxScale rend cela facile – il suffit de configurer le filtre supérieur et d’ajouter quel utilisateur doit être surveillé dans la configuration MaxScale.
Bien sûr, il existe d’autres moyens (plus exigeants en main-d’œuvre) d’obtenir les informations. Vous pouvez utiliser:
- Journal lent — Vous pouvez trouver les requêtes problématiques à l’aide du journal lent, mais cela prendra du temps et des efforts. Si vous avez activé
master_accept_reads=truedans votre configuration MaxScale, MaxScale achemine certaines lectures vers le serveur principal. Vous devrez donc analyser le journal lent pour les serveurs principaux et de réplique.
Mais pourquoi utiliser l’une ou l’autre de ces méthodes alors que le filtre supérieur peut faire le travail à votre place ?
Dans notre cas, nous choisissons d’utiliser le filtre Top dans MaxScale. Nous avons configuré le filtre supérieur et ajouté le report_user dans le fichier de configuration MaxScale.
Exemple
[LogFilter]
type=filter
module=topfilter
filebase=/tmp/slowquery
count=20
user=report_userEnsuite, nous avons démarré le sysbench pour charger les enregistrements 3M. Une fois les données chargées, nous avons lancé le test d’exécution.
Exemple
[root@centos13 sysbench]# sysbench select_random_points.lua --tables=1 --table_size=3000000 --mysql-host=192.168.56.115 --db-driver=mysql --mysql-user=report_user --mysql-password=xxxx --mysql-port=4006 --mysql-db=sbtest --time=600 --report-interval=10 --threads=2 run
Initializing worker threads...
Threads started!
[ 10s ] thds: 2 tps: 6690.11 qps: 6690.11 (r/w/o: 6690.11/0.00/0.00) lat (ms,95%): 0.39 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 2 tps: 6528.01 qps: 6528.01 (r/w/o: 6528.01/0.00/0.00) lat (ms,95%): 0.40 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 2 tps: 6495.30 qps: 6495.30 (r/w/o: 6495.30/0.00/0.00) lat (ms,95%): 0.41 err/s: 0.00 reconn/s: 0.00
.
.
.
[ 590s ] thds: 2 tps: 6601.24 qps: 6601.24 (r/w/o: 6601.24/0.00/0.00) lat (ms,95%): 0.40 err/s: 0.00 reconn/s: 0.00
[ 600s ] thds: 2 tps: 6453.91 qps: 6453.91 (r/w/o: 6453.91/0.00/0.00) lat (ms,95%): 0.41 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3903414
write: 0
other: 0
total: 3903414
transactions: 3903414 (6505.65 per sec.)
queries: 3903414 (6505.65 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0019s
total number of events: 3903414
Latency (ms):
min: 0.20
avg: 0.31
max: 30.67
95th percentile: 0.42
sum: 1196337.76
Threads fairness:
events (avg/stddev): 1951707.0000/17520.00
execution time (avg/stddev): 598.1689/0.01Après avoir terminé le test d’exécution, nous pouvons aller à /tmp directory et vérifiez le slowquery.0 déposer. Il a une liste de requêtes lentes qui sont exécutées sur report_user.
Exemple de sortie
[root@centos13 sysbench]# cat /tmp/slowquery.0
Top 20 longest running queries in session.
==========================================
Time (sec) | Query
-----------+-----------------------------------------------------------------
300.064 | SELECT id, k, c, pad
FROM sbtest1
WHERE k IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1.034 | SELECT id, k, c, pad
FROM sbtest1
WHERE id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
-----------+-----------------------------------------------------------------
Session started Sat Jun 19 09:31:57 2021
Connection from ::ffff:192.168.33.13
Username report_user
Total of 2 statements executed.
Total statement execution time 390.5 seconds
Average statement execution time 195.002 seconds
Total connection time 390.9 secondsD’après les résultats ci-dessus, le nombre total d’instructions exécutées est de deux (2) et le temps d’exécution total de ces instructions est de 390,5 secondes. Le temps d’exécution moyen de l’instruction est de 195,002 s. La durée totale de ce fil particulier est de 390,9 secondes. Plus important encore, nous pouvons voir par le rapport de requête « Top 20 » qu’une requête varie en temps d’exécution d’un facteur 300 en fonction des valeurs utilisées dans la requête. Bien que ce ne soit pas assez de détails pour terminer le dépannage ici, il fournit suffisamment de détails pour affiner le dépannage à une requête spécifique.
Conclusion
Le filtre Top de MariaDB MaxScale peut être un outil utile pour l’analyse des requêtes. C’est facile a utiliser. Les paramètres disponibles offrent une flexibilité pour limiter les requêtes à filtrer. Il peut être activé sans temps d’arrêt et les journaux n’utilisent pas d’espace de stockage sur le serveur de base de données lui-même.