Explication détaillée du code pour collecter des informations sur les tables tout en surveillant MySQL (images et texte)

1. Histoire

Peut-être qu'on vous pose souvent des questions sur la croissance mensuelle du volume de données d'une certaine table de la bibliothèque au cours de l'année écoulée. Bien sûr, ce serait plus facile si vous divisiez les tables par mois, une par une show table status S'il n'y a qu'une seule grande table, alors vous devrez probablement exécuter des statistiques SQL pendant la nuit solitaire où tout le monde se repose, car vous le pouvez. obtenez uniquement la table actuelle, les informations historiques ne peuvent pas être retracées.

De plus, en tant qu'administrateur de base de données, vous devez également estimer la croissance de l'espace de la base de données pour planifier la capacité. Les informations du tableau dont nous parlons comprennent principalement :

1. Taille des données du tableau (DATA_LENGTH)

2. Taille de l'index (INDEX_LENGTH)

3. Nombre de lignes (ROWS)

4. Valeur actuelle d'incrémentation automatique (AUTO_INCREMENT, le cas échéant)

Je n'ai Je n'ai pas encore vu lequelmysql fournit de tels indicateurs sur l'outil de surveillance. Il n'est pas nécessaire que ces informations soient collectées trop fréquemment, et le résultat n'est qu'une estimation et peut ne pas être précis. Il s'agit donc de surveiller (collecter) le tableau dans une perspective globale et à long terme.

L'outil de collecte que j'ai écrit pour présenter dans cet article est basé sur le système de suivi existant dans le groupe :

• InfluxDB : base de données de séries temporelles, suivi du stockage des données

• Grafana : Panneau d'affichage des données

• Telegraf : Agent qui collecte des informations
  Découvrez le dernier plug-in MySQL de Telegraf , j'étais très satisfait au début : il prend en charge la collecte de statistiques de schéma de table et de colonnes d'incrémentation automatique de schéma d'informations. Je l'ai essayé et il existe des données, mais comme mentionné précédemment, à l'exception de la valeur auto-augmentante, tout le reste est une estimation, la fréquence de collecte de Telegraf est trop élevée, ce qui n'a aucun sens. Peut-être que deux fois par jour suffisent. 🎜> l'option qu'il propose est corrigée. Morte dans le code, le seul moyen est de ralentir la fréquence de surveillance de l'état global. Cependant, il peut être mis en œuvre en le séparant des autres indicateurs de suivi en deux IntervalSlow fichiers de configuration et en définissant respectivement les intervalles de collecte.

Enfin, je prévois d'utiliser python pour en créer un moi-même et le signaler à influxdb :)

2. Concept

Pour le code complet, voir. l'adresse du projet GitHub : DBschema_gather

La mise en œuvre est également très simple, il suffit d'interroger les tables
et information_schema de la bibliothèque COLUMNS : TABLES

SELECT
    IFNULL(@@hostname, @@server_id) SERVER_NAME,
    %s as HOST,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.TABLE_ROWS,
    t.DATA_LENGTH,
    t.INDEX_LENGTH,
    t.AUTO_INCREMENT,
  c.COLUMN_NAME,
  c.DATA_TYPE,
  LOCATE('unsigned', c.COLUMN_TYPE) COL_UNSIGNED
  # CONCAT(c.DATA_TYPE, IF(LOCATE('unsigned', c.COLUMN_TYPE)=0, '', '_unsigned'))
FROM
    information_schema.`TABLES` t
LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND c.EXTRA = 'auto_increment'
WHERE
    t.TABLE_SCHEMA NOT IN (
        'mysql',
        'information_schema',
        'performance_schema',
        'sys'
    )
AND t.TABLE_TYPE = 'BASE TABLE'

À propos de

, en plus de prêter attention à l'endroit où se situe la croissance actuelle, nous nous soucions également de la quantité d'espace disponible par rapport à la valeur maximale de auto_increment. La colonne int / bigint a donc été calculée pour enregistrer le ratio actuellement utilisé. autoIncrUsage

Utilisez ensuite le client python d'InfluxDB pour enregistrer dans influxdb par lots. Sans InfluxDB, le résultat sera imprimé

json - il s'agit d'un format couramment pris en charge par les outils de surveillance tels que Zabbix et Open-Falcon.

La dernière étape consiste à utiliser Grafana pour dessiner des graphiques à partir de la source de données influxdb.

3. Utilisation

1. Environnement

   Écrit dans l'environnement python 2.7, 2.6 et 3.x n'ont pas été testés.
   

Le fonctionnement nécessite

et MySQLdb deux bibliothèques : influxdb

$ sudo pip install mysql-python influxdb

1. Configuration

   
   Fichier de configurationsettings_dbs.py

2. 
   

•  : la liste stocke les informations de la table d'instance MySQL qui doivent être collectées, et les tuples sont les connexions. Adresse, port, nom d'utilisateur, mot de passeDBLIST_INFOLes utilisateurs ont besoin d'une autorisation pour sélectionner la table, sinon ils ne peuvent pas voir les informations correspondantes.
  

• : Informations de connexion Influxdb, veuillez créer le nom de la base de données à l'avance InfluxDB_INFOmysql_info et définissez-le sur
  Le résultat de sortie peut être json.None

• Créez le. base de données et stratégie de stockage sur influxdb

  Stockage pendant 2 ans, 1 jeu de répliques : (Ajuster selon les besoins)
  

CREATE DATABASE "mysql_info"
CREATE RETENTION POLICY "mysql_info_schema" ON "mysql_info" DURATION 730d REPLICATION 1 DEFAULT

Regardez les grandes informations similaires à :




1. Mettez crontab à exécuter

   Il peut être placé séparément sur le serveur utilisé pour la surveillance, mais il est recommandé de l'exécuter sur l'hôte où se trouve l'instance mysql dans l'environnement de production pour des raisons de sécurité.
   

Les bibliothèques générales auront une migration de données la nuit. Vous pouvez exécuter

avant et après la migration pour les collecter. Pas recommandé trop souvent. mysql_schema_info.py

40 23,5,12,18 * * * /opt/DBschema_info/mysql_schema_info.py >> /tmp/collect_DBschema_info.log 2>&1


Taille des données du tableau et nombre de lignes


Incrément du nombre de lignes par jour, utilisation auto_increment

4. Plus

1. Dans le cas d'une sous-base de données et d'une table, l'ID unique global ne peut pas être calculé dans la table autoIncrUsage

2. C'est effectivement très difficile à mettre en œuvre Simple, le plus important est d'éveiller la conscience de la collecte de ces informations

3. Vous pouvez ajouter le format de sortie Graphite

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!