:Analyse des journaux

Prérequis

Les fonctionnalités d'audit et d'analyse SQL sont activées sur votre instance PolarDB-X. Pour plus d'informations, consultez Activer l'audit et l'analyse SQL.

Précautions

• Les journaux d'audit des bases de données PolarDB-X déployées dans la même région sont stockés dans le même Logstore de Log Service. Par défaut, le champ __topic__ sert de condition dans la zone de recherche de la page d'audit et d'analyse SQL. Lorsque vous interrogez des entrées de journal et des résultats d'analyse selon cette condition, toutes les entrées retournées proviennent des bases de données PolarDB-X déployées dans cette région. Ajoutez les conditions décrites dans cette rubrique après le champ __topic__ pour effectuer des requêtes plus granulaires.

• Dans l'onglet Raw Logs, cliquez sur la valeur d'un champ pour l'utiliser comme condition de recherche.

  Par exemple, cliquez sur la valeur Delete du champ sql_type pour définir une condition permettant d'interroger toutes les instructions DELETE.

Interroger des instructions SQL spécifiques

Plusieurs types de conditions permettent d'interroger des instructions SQL.

• Utilisation d'un mot-clé comme condition

  Spécifiez par exemple la condition suivante pour rechercher les instructions SQL contenant le mot-clé 200003.

  and sql: 200003

• Utilisation d'un champ intégré comme condition

  Spécifiez des champs d'index intégrés pour rechercher les instructions SQL contenant ces champs. Utilisez par exemple la condition suivante pour trouver toutes les instructions DROP.

  and sql_type:Drop

• Combinaison de plusieurs conditions

  Combinez plusieurs conditions en définissant leurs relations avec les opérateurs AND ou OR. Appliquez par exemple les conditions suivantes pour identifier les instructions DELETE exécutées sur la ligne 200003.

  and sql: 200003 and sql_type: Delete

• Utilisation d'expressions de comparaison numérique comme conditions

  Dans cet exemple, les valeurs des champs affect_rows et response_time sont numériques et acceptent les opérateurs de comparaison. Ajoutez les conditions suivantes pour rechercher les instructions DROP dont le paramètre response_time dépasse 5 secondes.

  and response_time > 5 and sql_type: Drop

  Utilisez également les conditions ci-dessous pour retrouver les instructions SQL ayant supprimé plus de 100 lignes de données.

  and affect_rows  > 100 and sql_type: Delete

Analyse de l'exécution SQL

Exécutez les instructions suivantes pour analyser l'état d'exécution des requêtes SQL.

• Obtenir le taux d'échec des requêtes SQL

  L'instruction suivante calcule la proportion de requêtes SQL échouées.

  | SELECT sum(case when fail = 1 then 1 else 0 end) * 1.0 / count(1) as fail_ratio

  Remarque

  Cliquez sur Save as Alert dans le coin supérieur droit de la page pour créer des règles d'alerte adaptées à vos besoins métier.

• Calculer le nombre total de lignes affectées par des instructions SQL spécifiques

  Cette instruction retourne le nombre cumulé de lignes concernées par les instructions SELECT.

  and sql_type: Select | SELECT sum(affect_rows)

• Visualiser la répartition des différents types de requêtes SQL

  La requête ci-dessous affiche la distribution des requêtes SQL par type.

  | SELECT  sql_type, count(sql) as times GROUP BY sql_type

• Analyser la distribution des adresses IP utilisées par un utilisateur

  Utilisez cette instruction pour obtenir la répartition des adresses IP depuis lesquelles un utilisateur a envoyé ses requêtes.

  | SELECT  user, client_ip, count(sql) as times GROUP BY user, client_ip

Analyse des performances

Les instructions suivantes permettent d'obtenir les résultats de l'analyse des performances SQL.

• Durée moyenne d'exécution des instructions SELECT

  Cette requête calcule le temps moyen nécessaire au système pour exécuter une instruction SELECT.

  and sql_type: Select | SELECT avg(response_time)

• Répartition des instructions SQL par durée d'exécution

  Spécifiez la condition suivante pour classer les instructions SQL selon leur temps d'exécution.

  and response_time > 0 | select   case  when response_time <= 10 then '<=10ms'  when response_time > 10 and response_time <= 100 then '10~100ms'  when response_time > 100 and response_time <= 1000 then '100ms~1s'  when response_time > 1000 and response_time <= 10000  then '1s~10s'  when response_time > 10000 and response_time <= 60000  then '10s~1min'  else '>1min' end as latency_type,  count(1) as cnt group by latency_type order by latency_type DESC

  Remarque

  La condition précédente définit quatre plages de temps via le champ response_time : inférieur ou égal à 10 millisecondes, entre 10 et 100 millisecondes inclus, entre 100 millisecondes et 1 seconde incluse, et entre 1 et 10 secondes incluses. Ajustez les valeurs du champ response_time pour obtenir une granularité plus fine.

• Identification des 50 instructions SQL lentes les plus critiques

  Exécutez cette instruction pour lister les 50 requêtes SQL les plus lentes.

  | SELECT date_format(from_unixtime(__time__), '%m/%d %H:%i:%s') as time, user, client_ip, client_port, sql_type, affect_rows, response_time, sql ORDER BY  response_time desc LIMIT 50

• Recherche des 10 modèles SQL les plus consommateurs de ressources

  Dans la plupart des applications, les instructions SQL sont générées dynamiquement à partir de modèles où seules les valeurs des paramètres varient. La requête suivante identifie les 10 modèles SQL consommant le plus de ressources :

  | SELECT sql_code as "Template ID", round(total_time * 1.0 /sum(total_time) over() * 100, 2) as "Execution duration ratio (%)" ,execute_times as "Number of queries", round(avg_time) as "Average execution duration",round(avg_rows) as "Average number of operated rows", CASE WHEN length(sql) > 200 THEN concat(substr(sql, 1, 200), '......') ELSE trim(lpad(sql, 200, ' ')) end as "Sample SQL" FROM (SELECT sql_code, count(1) as execute_times, sum(response_time) as total_time, avg(response_time) as avg_time, avg(affect_rows) as avg_rows, arbitrary(sql) as sql FROM log GROUP BY sql_code) ORDER BY "Execution duration ratio (%)" desc limit 10

  Les résultats incluent des détails tels que l'ID de chaque modèle SQL, la part de la durée d'exécution des instructions générées par ce modèle par rapport au temps total, le nombre d'instructions produites, leur durée moyenne d'exécution, le nombre moyen de lignes traitées, ainsi qu'un exemple d'instruction SQL pour chaque modèle.

  Remarque

  Dans cet exemple, les modèles SQL sont triés par ratio de durée d'exécution. Selon vos besoins métier, classez-les également par durée moyenne d'exécution ou par nombre d'instructions.

• Durée moyenne d'exécution des transactions

  Pour les instructions SQL exécutées au sein d'une même transaction, les valeurs du paramètre trace_id partagent un préfixe commun. Les suffixes suivent le format '-' + Serial number. En revanche, pour les instructions hors transaction, les valeurs de trace_id ne contiennent pas le caractère '-'. L'instruction ci-dessous permet d'analyser les performances des requêtes SQL liées au traitement des transactions.

  Remarque

  L'analyse des transactions est moins efficace que les autres opérations de requête, car le système doit vérifier les préfixes des instructions SQL.

  • Temps moyen d'exécution par transaction

    Cette requête calcule la durée moyenne nécessaire au système pour exécuter une transaction complète.

     | SELECT  sum(response_time) / COUNT(DISTINCT substr(trace_id, 1, strpos(trace_id, '-') - 1)) where strpos(trace_id, '-') > 0

  • Liste des 10 transactions les plus lentes

    Utilisez cette instruction pour identifier les transactions les plus longues en termes de durée d'exécution.

    | SELECT substr(trace_id, 1, strpos(trace_id, '-') - 1) as "Transaction ID" , sum(response_time) as "Execution duration" where strpos(trace_id, '-') > 0 GROUP BY substr(trace_id, 1, strpos(trace_id, '-') - 1) ORDER BY "Execution duration" DESC LIMIT 10

    Exécutez ensuite la requête suivante pour retrouver toutes les instructions SQL d'une transaction lente donnée, en utilisant son ID. Cela facilite l'analyse de la cause racine du ralentissement.

     and trace_id: db3226a20402000*

  • Top 10 des transactions affectant le plus grand nombre de lignes

    La requête ci-après liste les 10 transactions ayant traité le volume de lignes le plus important.

    | SELECT substr(trace_id, 1, strpos(trace_id, '-') - 1) as "Transaction ID" , sum(affect_rows) as "Operated rows" where strpos(trace_id, '-') > 0 GROUP BY substr(trace_id, 1, strpos(trace_id, '-') - 1) ORDER BY "Operated rows" DESC LIMIT 10

Analyse de la sécurité SQL

Les conditions suivantes permettent d'interroger les résultats de l'analyse de sécurité.

• Répartition des échecs de requêtes SQL par type

  Appliquez cette condition pour visualiser la distribution des erreurs SQL selon leur type.

  and fail > 0 | select sql_type, count(1) as "number of failures" group by sql_type

• Détection des instructions SQL à haut risque

  Les instructions DROP et TRUNCATE sont considérées comme à haut risque dans PolarDB-X. Définissez vos propres règles d'identification des instructions critiques en fonction de vos exigences métier.

  La condition ci-dessous permet de rechercher les instructions DROP ou TRUNCATE.

  and sql_type: Drop OR sql_type: Truncate

• Instructions DELETE supprimant un grand volume de lignes

  Utilisez cette condition pour identifier les instructions SQL supprimant plus de 100 lignes de données.

  and affect_rows > 100 and sql_type: Delete | SELECT date_format(from_unixtime(__time__), '%m/%d %H:%i:%s') as time, user, client_ip, client_port, affect_rows, sql ORDER BY  affect_rows desc LIMIT 50