Published on InterSystems Developer Community (https://community.intersystems.com)

Accueil > APM - Surveillance des performances des requêtes SQL

Article
Lorenzo Scalese · Avr 15, 2022 26m de lecture
Open Exchange

APM - Surveillance des performances des requêtes SQL

Depuis Caché 2017, le moteur SQL comprend un nouvel ensemble de statistiques. Celles-ci enregistrent le nombre de fois qu'une requête est exécutée et le temps qu'elle prend pour s'exécuter.

C'est une mine d'or pour quiconque surveille et tente d'optimiser les performances d'une application qui comprend de nombreuses instructions SQL, mais il n'est pas aussi facile d'accéder aux données que certaines personnes le souhaitent.

Cet article et l'exemple de code associé expliquent comment utiliser ces informations et comment extraire de manière routinière un résumé des statistiques quotidiennes et conserver un historique des performances SQL de votre application.

Qu'est-ce qui est enregistré ?

Chaque fois qu'une instruction SQL est exécutée, le temps pris est enregistré. Ce système est très léger et vous ne pouvez pas le désactiver. Pour minimiser les coûts, les statistiques sont conservées en mémoire et écrites sur disque périodiquement. Les données comprennent le nombre de fois qu'une requête a été exécutée dans la journée et le temps moyen et total nécessaire.

Les données ne sont pas écrites sur le disque immédiatement, et après qu'elles aient été écrites, les statistiques sont mises à jour par une tâche "Update SQL query statistics" qui est généralement programmée pour s'exécuter une fois par heure. Cette tâche peut être lancée manuellement, mais si vous souhaitez voir les statistiques en temps réel tout en testant une requête, l'ensemble du processus requiert un peu de patience.

Avertissement : Dans InterSystems IRIS 2019 et les versions antérieures, ces statistiques ne sont pas collectées pour le embedded SQL dans les classes ou les routines qui ont été déployées à l'aide du mécanisme  %Studio.Project:Deploy . Rien ne sera cassé avec l'exemple de code, mais il pourrait vous tromper (il m'a trompé) en pensant que tout était OK parce que rien n'est apparu comme coûteux. 

Comment voyez-vous les informations ?

Vous pouvez voir la liste des requêtes dans le portail de gestion. Passez à la page SQL et cliquez sur l'onglet "SQL Statements". C'est une bonne chose pour une nouvelle requête que vous exécutez et regardez, mais si des milliers de requêtes sont exécutées, cela peut devenir ingérable.

L'alternative est d'utiliser SQL pour rechercher les requêtes. Les informations sont stockées dans des tableaux du schéma INFORMATION_SCHEMA. Ce schéma comporte un certain nombre de tableaux et j'ai inclus quelques exemples de requêtes SQL à la fin de cet article.

Quand les statistiques sont-elles retirées ?

Les données d'une requête sont supprimées chaque fois qu'elle est recompilée. Ainsi, pour les requêtes dynamiques, cela peut signifier que les requêtes en cache sont purgées. Pour le embedded SQL, cela signifie que la classe ou la routine dans laquelle le embedded SQL se trouve est recompilée.

Sur un site actif, il est raisonnable de croire que les statistiques seront conservées pendant plus d'une journée, mais les tableaux contenant les statistiques ne peuvent pas être utilisés comme source de référence à long terme pour l'exécution de rapports ou d'analyses à long terme.

Comment pouvez-vous résumer l'information ?

Je recommande d'extraire les données chaque nuit dans des tableaux permanents avec lesquels il est plus facile de travailler pour générer des rapports de performance. Il est possible que certaines informations soient perdues si les classes sont compilées pendant la journée, mais il est peu probable que cela fasse une réelle différence dans votre analyse des requêtes lentes.

Le code ci-dessous est un exemple illustrant comment vous pourriez extraire les statistiques dans un résumé quotidien pour chaque requête. Il comprend trois classes courtes :

* Une tâche qui doit être exécutée chaque nuit.
* DRL.MonitorSQL est une classe principale qui extrait et stocke les données des tableaux INFORMATION_SCHEMA.

  • La troisième classe DRL.MonitorSQLText est une optimisation qui permet de stocker le texte de la requête (potentiellement long) une seule fois et de ne stocker que le hashage de la requête dans les statistiques pour chaque jour.

Notes concernant l'exemple

La tâche extrait le jour précédent et doit donc être programmée peu après minuit.

Vous pouvez exporter plus de données historiques si elles existent. Pour extraire les 120 derniers jours

Faites ##class(DRL.MonitorSQL).Capture($h-120,$h-1)

Le code d'exemple lit directement le global ^rIndex car les premières versions des statistiques n'exposaient pas la Date à SQL.

La variante que j'ai incluse boucle sur tous les espaces de noms de l'instance, mais cela n'est pas toujours approprié.

Comment faire une requête sur les données extraites

Une fois les données extraites, vous pouvez trouver les requêtes les plus lourdes en exécutant

SELECT top 20
S.RunDate,S.RoutineName,S.TotalHits,S.SumpTIme,S.Hash,t.QueryText
from DRL.MonitorSQL S
left join DRL.MonitorSQLText T on S.Hash=T.Hash
where RunDate='08/25/2019'
order by SumpTime desc

 

De plus, si vous choisissez le hachage pour une requête coûteuse, vous pouvez voir l'historique de cette requête avec

 

SELECT S.RunDate,S.RoutineName,S.TotalHits,S.SumpTIme,S.Hash,t.QueryText
from DRL.MonitorSQL S
left join DRL.MonitorSQLText T on S.Hash=T.Hash
where S.Hash='CgOlfRw7pGL4tYbiijYznQ84kmQ='
order by RunDate

Au début de l'année, j'ai analysé les données d'un site en direct et j'ai examiné les requêtes les plus coûteuses. Une requête durait en moyenne moins de 6 secondes mais était appelée 14 000 fois par jour, ce qui représentait près de 24 heures de temps écoulé chaque jour. En fait, un noyau était entièrement occupé par cette seule requête. Pire encore, la deuxième requête qui prend une heure était une variation de la précédente requête.

 

RunDate RoutineName Nombre total de visites Total Time Hash QueryText (en abrégé)
03/16/2019   14,576 85,094 5xDSguu4PvK04se2pPiOexeh6aE= DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4)  …
03/16/2019   15,552 3,326 rCQX+CKPwFR9zOplmtMhxVnQxyw= DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) , …
03/16/2019   16,892 597 yW3catzQzC0KE9euvIJ+o4mDwKc= DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) , :%col(5) , :%col(6) , :%col(7) ,
03/16/2019   16,664 436 giShyiqNR3K6pZEt7RWAcen55rs= DECLARE C CURSOR FOR SELECT * , TKGROUP INTO :%col(1) , :%col(2) , :%col(3) , ..
03/16/2019   74,550 342 4ZClMPqMfyje4m9Wed0NJzxz9qw= DECLARE C CURSOR FOR SELECT …

 

Tableau 1: Résultats réels sur le site du client

 

Les tableaux du schéma INFORMATION_SCHEMA

En plus des statistiques, les tableaux de ce schéma gardent la trace de l'endroit où les requêtes, les colonnes, les indices, etc. sont utilisés. En général, l'instruction SQL est un tableau de départ et il est joint par quelque chose comme "Statements.Hash=OtherTable.Statement".

La requête équivalente pour accéder directement à ces tableaux afin de trouver les requêtes les plus coûteuses pour une journée serait...

 

SELECT DS.Day,Loc.Location,DS.StatCount,DS.StatTotal,S.Statement,S.Hash
FROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS DS
left join INFORMATION_SCHEMA.STATEMENTS  S
on S.Hash=DS.Statement
left join INFORMATION_SCHEMA.STATEMENT_LOCATIONS  Loc
on S.Hash=Loc.Statement
where Day='08/26/2019'
order by DS.stattotal desc

 

Que vous envisagiez ou non de mettre en place un processus plus systématique, je recommande à tous ceux qui ont une grande application utilisant SQL de lancer aujourd'hui cette requête.

Si une requête particulière apparaît comme coûteuse, vous pouvez obtenir l'historique en exécutant 

SELECT DS.Day,Loc.Location,DS.StatCount,DS.StatTotal,S.Statement,S.Hash
FROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS DS
left join INFORMATION_SCHEMA.STATEMENTS  S
on S.Hash=DS.Statement
left join INFORMATION_SCHEMA.STATEMENT_LOCATIONS  Loc
on S.Hash=Loc.Statement
where S.Hash='jDqCKaksff/4up7Ob0UXlkT2xKY='
order by DS.Day

 

 
Exemple de code pour l'extraction quotidienne de statistiques
Clause de non-responsabilité standard - cet exemple est fourni à titre d'illustration uniquement. Son fonctionnement n'est ni pris en charge ni garanti. 

Class DRL.MonitorSQLTask Extends %SYS.Task.Definition
{
Parameter TaskName = "SQL Statistics Summary";
Method OnTask() As %Status
{
              set tSC=$$$OK
              TRY {
                             do ##class(DRL.MonitorSQL).Run()
              }
              CATCH exp {
                            set tSC=$SYSTEM.Status.Error("Error in SQL Monitor Summary Task")
              }
              quit tSC
 }
}

Class DRL.MonitorSQLText Extends %Persistent
{
/// Hash of query text
Property Hash As %String;
 

/// query text for hash
Property QueryText As %String(MAXLEN = 9999);
Index IndHash On Hash [ IdKey, Unique ];
}

/// Summary of very low cost SQL query statistics collected in Cache 2017.1 and later.

/// Refer to documentation on "SQL Statement Details" for information on the source data.

/// Data is stored by date and time to support queries over time.

/// Typically run to summarise the SQL query data from the previous day.
Class DRL.MonitorSQL Extends %Persistent
{
/// RunDate and RunTime uniquely identify a run
Property RunDate As %Date;
/// Time the capture was started
/// RunDate and RunTime uniquely identify a run
Property RunTime As %Time;
/// Count of total hits for the time period for
Property TotalHits As %Integer;
/// Sum of pTime
Property SumPTime As %Numeric(SCALE = 4);
/// Routine where SQL is found
Property RoutineName As %String(MAXLEN = 1024);
/// Hash of query text
Property Hash As %String;
Property Variance As %Numeric(SCALE = 4);
/// Namespace where queries are run
Property Namespace As %String;
/// Default run will process the previous days data for a single day.
/// Other date range combinations can be achieved using the Capture method.
ClassMethod Run()
{
              //Each run is identified by the start date / time to keep related items together
              set h=$h-1
              do ..Capture(+h,+h)
}
/// Captures historic statistics for a range of dates
ClassMethod Capture(dfrom, dto)
{
              set oldstatsvalue=$system.SQL.SetSQLStatsJob(-1)
                set currNS=$znspace
                set tSC=##class(%SYS.Namespace).ListAll(.nsArray)
                set ns=""
      set time=$piece($h,",",2)
      kill ^||TMP.MonitorSQL
                do {
                               set ns=$o(nsArray(ns))
                               quit:ns=""
                               use 0 write !,"processing namespace ",ns
                               zn ns
                                           for dateh=dfrom:1:dto {
                                                          set hash=""
                                                          set purgedun=0
                                                          do {
                                                                        set hash=$order(^rINDEXSQL("sqlidx",1,hash))
                                                                        continue:hash=""
                                                                        set stats=$get(^rINDEXSQL("sqlidx",1,hash,"stat",dateh))
                                                                        continue:stats=""
                                                                        set ^||TMP.MonitorSQL(dateh,ns,hash)=stats
                                                          &SQL(SELECT  Location into :tLocation FROM INFORMATION_SCHEMA.STATEMENT_LOCATIONS WHERE Statement=:hash)
                                                                        if SQLCODE'=0 set Location=""
                                                                        set ^||TMP.MonitorSQL(dateh,ns,hash,"Location")=tLocation
                                                          &SQL(SELECT  Statement INTO :Statement FROM INFORMATION_SCHEMA.STATEMENTS WHERE Hash=:hash)
                                                                        if SQLCODE'=0 set Statement=""
                                                                        set ^||TMP.MonitorSQL(dateh,ns,hash,"QueryText")=Statement
                                                          } while hash'=""
                                           }
                } while ns'=""
                zn currNS
                set dateh=""
                do {
                               set dateh=$o(^||TMP.MonitorSQL(dateh))
                               quit:dateh=""
                               set ns=""
                               do {
                                             set ns=$o(^||TMP.MonitorSQL(dateh,ns))
                                             quit:ns=""
                                             set hash=""
                                             do {
                                                         set hash=$o(^||TMP.MonitorSQL(dateh,ns,hash))
                                                          quit:hash=""
                                                         set stats=$g(^||TMP.MonitorSQL(dateh,ns,hash))
                                                          continue:stats=""
                                                          // The first time through the loop delete all statistics for the day so it is re-runnable
                                                          // But if we run for a day after the raw data has been purged, it will wreck eveything
                                                          // so do it here, where we already know there are results to insert in their place.
                                                          if purgedun=0 {
                                                                        &SQL(DELETE FROM websys.MonitorSQL WHERE RunDate=:dateh )
                                                                        set purgedun=1
                                                          }
                                                          set tObj=##class(DRL.MonitorSQL).%New()
                                                          set tObj.Namespace=ns
                                                          set tObj.RunDate=dateh
                                                          set tObj.RunTime=time
                                                          set tObj.Hash=hash
                                                          set tObj.TotalHits=$listget(stats,1)
                                                          set tObj.SumPTime=$listget(stats,2)
                                                          set tObj.Variance=$listget(stats,3)
                                                          set tObj.Variance=$listget(stats,3)
                                                         set queryText=^||TMP.MonitorSQL(dateh,ns,hash,"QueryText")
                                                         set tObj.RoutineName=^||TMP.MonitorSQL(dateh,ns,hash,"Location")
                                                    &SQL(Select ID into :TextID from DRL.MonitorSQLText where Hash=:hash)
                                                          if SQLCODE'=0 {
                                                                        set textref=##class(DRL.MonitorSQLText).%New()
                                                                        set textref.Hash=tObj.Hash
                                                                        set textref.QueryText=queryText
                                                                        set sc=textref.%Save()
                                                          }                                                       
                                                          set tSc=tObj.%Save()
                                                          //avoid dupicating the query text in each record because it can be very long. Use a lookup
                                                          //table keyed on the hash. If it doesn't exist add it.
                                                          if $$$ISERR(tSc) do $system.OBJ.DisplayError(tSc)

                                                          if $$$ISERR(tSc) do $system.OBJ.DisplayError(tSc)
                                             } while hash'=""
                               } while ns'=""
                } while dateh'=""
              do $system.SQL.SetSQLStatsJob(0)
}
Query Export(RunDateH1 As %Date, RunDateH2 As %Date) As %SQLQuery
{
SELECT S.Hash,RoutineName,RunDate,RunTime,SumPTime,TotalHits,Variance,RoutineName,T.QueryText
              FROM DRL.MonitorSQL S LEFT JOIN DRL.MonitorSQLText T on S.Hash=T.Hash
              WHERE RunDate>=:RunDateH1 AND RunDate<=:RunDateH2
}
}

 
#Performances #SQL #Surveillance #Caché #InterSystems IRIS
Voir l'application sur InterSystems Open Exchange

URL de la source:https://fr.community.intersystems.com/post/apm-surveillance-des-performances-des-requ%C3%AAtes-sql