Introduction

Les tables MDA (Monitoring Data Access) sont apparues avec la version 12.5.0.3 de Sybase ASE. Il s'agit de 35 tables "virtuelles" qui permettent un accès en SQL à de nombreux compteurs de performances internes, comme la consommation CPU, le nombre d'IO, etc. Ces tables peuvent avantageusement remplacer le vénérable sp_sysmon pour suivre la santé et la performance d'une instance ASE.

Installation

Note: ces instructions sont basées sur la doc Sybase, et sur la très bonne information fournie par le site de Rob Vershoor.

1. S'assurer que le serveur ASE a été nommé. Si select @@servername ne ramène rien, faire: sp_addserver {nom_serveur} et relancer l'instance.

2. S'assurer que la config CIS est à jour.

 
Sélectionnez
sp_configure 'enable cis', 1

Si CIS n'était pas configuré il faut relancer l'instance.

3. Créer un alias pour le serveur local nommé "loopback"

 
Sélectionnez
 sp_addserver loopback, null, @@servername

4. Charger la définition des tables MDA:

 
Sélectionnez
cd $SYBASE/$SYBASE_ASE/install
isql -Usa -P... -S... -i installmontables -o installmontables.log

5. Configurer le monitoring:

 
Sélectionnez
sp_configure "enable monitoring", 1
go
sp_configure "sql text pipe active", 1
go
sp_configure "sql text pipe max messages", 100
go
sp_configure "plan text pipe active", 1
go
sp_configure "plan text pipe max messages", 100
go
sp_configure "statement pipe active", 1
go
sp_configure "statement pipe max messages", 100
go
sp_configure "errorlog pipe active", 1
go
sp_configure "errorlog pipe max messages", 100
go
sp_configure "deadlock pipe active", 1
go
sp_configure "deadlock pipe max messages", 100
go
sp_configure "wait event timing", 1
go
sp_configure "process wait events", 1
go
sp_configure "object lockwait timing", 1
go
sp_configure "SQL batch capture", 1
go
sp_configure "statement statistics active", 1
go
sp_configure "per object statistics active", 1
go

6. Donner le rôle "mon_role" aux logins qui doivent accéder aux tables MDA:

 
Sélectionnez
grant role mon_role to sa

7. Faire un petit test

 
Sélectionnez
select * from master..monEngine

Note : l'utilisation de sp_sysmon va normalement remettre un grand nombre de compteurs MDA à zéro. Pour éviter cela il faut passer le paramètre 'noclear' à sp_sysmon (dès 12.5.2):

 
Sélectionnez
sp_sysmon '00:02:00', 'noclear'

Note : Lorsque les tables MDA sont installées il faut se rappeler de ré-exécuter installmontables lorsque on installe une nouvelle version / EBF.

Utilisation

La plupart des tables donnent des informations instantanées. Par exemple, monProcessActivity donne la consommation en CPU, IO, etc. de chaque session ouverte, monProcessSQLText donne le code SQL des sessions qui sont actuellement actives (c.à.d. des requêtes qui sont en cours d'exécutions).

Quelques tables sont des queues (p.ex. monSysPlanText, monSysSQLText, monSysStatement, monErrorLog et monDeadLock). Ces tables contiennent les X derniers enregistrements (ou X est configurable via sp_configure). Attention - un SELECT sur une de ces table la vide, donc en général il faut faire:

 
Sélectionnez
select * into #txt from monSysSQLText

et ensuite travailler sur la table temporaire.

Les tables monTables, monTableParameters et monTableColumns décrivent les tables MDA.

Les autres tables peuvent être groupées en quelques catégories:

1. Les tables de statistiques pour l'instance.

 
Sélectionnez
monEngine 			consommation CPU par engine
monDeviceIO 		Statistiques IO par device
monIOQueue 			Statistiques IO par device et par type d'IO
monSysWaits 	temps d'attentes par type d'attente - faire une jointure avec monWaitEventInfo pour avoir la description des types d'attentes
monNetworkIO 		statistiques réseau
monSysWorkerThread	statistiques par " worker process "
monOpenObjectActivity 	statistiques par objet - tables/indexes)

2. Tables concernant les caches:

 
Sélectionnez
monDataCache 		information sur les caches
monCachePool 		information sur les pool IO des caches
monCachedObject 		donne les détails de la cache par table
monCachedProcedures 	idem pour les procs stockées
monProcedureCache 	statistiques globales pour la procédure cache

3. Statistiques par SPID

Ces tables donnent des informations détaillées pour chaque connexion (SPID) :

 
Sélectionnez
monProcess, monProcessActivity, monProcessNetIO, monProcessObject, monProcessProcedures, monProcessSQLText, monProcessWaits, monProcessWorkerThread

4. Tables "Queues"

 
Sélectionnez
monDeadLock
monErrorLog
monSysPlanText
monSysSQLText
monSysStatement

Ces cinq tables permettent d'accéder à un historique d'une longueur configurable (voir les options de configuration de type " pipe ".)

Quelques exemples

Liste des indexes pour les tables ayant plus de 10000 enregistrements et qui n'ont jamais été utilisés :

rowcnt() n'est pas valable en 15.0

 
Sélectionnez
select name=object_name(id), id, rowcnt = rowcnt(doampg) 
  into #tabs
  from sysindexes 
 where indid < 2
   and rowcnt(doampg) > 10000

select "table" = substring(object_name(ObjectID, DBID), 1, 20)
     , i.name, OptSelectCount, UsedCount, t.rowcnt
  from master..monOpenObjectActivity a
     , #tabs t
     , sysindexes i
 where a.DBID = db_id()
   and a.ObjectID = t.id
   and i.id = t.id
   and i.indid = a.IndexID
   and i.indid between 1 and 254
   and (a.OptSelectCount = 0
     or a.UsedCount = 0)
 order by 5

drop table #tabs

Les champs OptSelectCount et UsedCount de monOpenObjectActivity permettent de savoir combien de fois l'optimiseur a sélectionné l'indexe pour être utilisé, et combien de fois l'indexe a effectivement été utilisé. Attention, si le paramètre de configuration "number of open objects" et/ou "number of open indexes" est trop petit par rapport au nombre d'objets dans la base les stats peuvent être faussées, puisque les stats sont remise à 0 quand un objet doit être fermé.

L'exemple suivant donne une vue compacte de l'utilisation des caches et des pool mémoire :

 
Sélectionnez
use sybsystemprocs
go
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'P' AND name = 'sp__cache')
BEGIN
DROP PROCEDURE sp__cache
END
go
create procedure sp__cache with recompile
as
begin
set nocount on
declare @time datetime
select @time = getdate()
select CacheName, 
       "AllocatedMB" = convert(char(6),sum(AllocatedKB)/1024)
  into #cachesize
  from master..monCachePool
 group by CacheName
print ""
print "Cache status at %1!. Server up since %2!", @time, @@boottime
print ""
select "Cache Name" = substring(m.CacheName+"("+t.AllocatedMB+"MB)",1,30),
       "Searches" = m.CacheSearches,
       "Logical Reads" = m.LogicalReads,
       "Phys. Reads" = m.PhysicalReads,
       "Phys. Writes" = m.PhysicalWrites,
       "STALLS" = m.Stalls
  from master..monDataCache m,
       #cachesize t
 where m.CacheName = t.CacheName
 order by m.CacheID
print ""
select "Cache Name" = substring(CacheName, 1, 15),
       "Pool Type" = convert(tinyint, IOBufferSize/1024),
       "Pool size/MB" = AllocatedKB/1024,
       "Used/MB" = (PagesTouched * (@@maxpagesize/1024))/1024,
       "Pages read" = PagesRead,
       "Moved to MRU" = BuffersToMRU ,
       "fetch/discard" =  BuffersToLRU,
       "Stalls" = Stalls
  from master..monCachePool
 order by CacheID, IOBufferSize
end
go
grant exec on sp__cache to public
go

Procédure développée avec Mich Talebzadeh

Cette procédure nous donne le nombre d'IO logiques et physiques pour chaque caches, ainsi que le taux d'utilisation de chaque pool (2k, 16k, etc). La colonne "stalls" correspond au "Buffers grabbed dirty" de sp_sysmon. Si cette valeur n'est pas 0 elle représente un problème de perf réel puisque l'engine doit attendre que l'opération d'IO soit terminée sur le buffer "sale" avant de pouvoir le réutiliser.

Analyse des temps d'attentes :

Les table monSysWaits et monProcessWaits donnent les temps d'attentes globaux et par SPID. Ces tables sont très utiles pour améliorer les performances de l'instance - il s'agit de minimiser les attentes sur les opérations d'IO, etc. Le temps d'attente principal est le WaitEventID 250 (waiting for incoming network data) ce qui est normal, c'est l'état quand le SPID est en "recv sleep" (dans sp_who, p.ex.) donc en attente de recevoir une instruction à exécuter de la part du client.

L'information de monSysWaits est assez simple à extraire:

 
Sélectionnez
select w.*, i.Description 
  from monSysWaits w, monWaitEventInfo i 
 where i.WaitEventID = w.WaitEventID 
 order by 2

Ce qui donne quelque chose comme ceci:

 
Sélectionnez
WaitEventID WaitTime    Waits       Description
 ----------- ----------- ----------- --------------------------------------------------
          35           0         122 wait for buffer validation to complete
          37           0        6367 wait for mass to finish changing
          47           0           1 wait for read on buffer to complete
          53           0        2515 waiting for mass to finish changing
          83           0          17 wait for DES state is changing
...
          57      570032       10441 checkpoint process idle loop
          19      580688        9678 xact coord: pause during idle loop
         104      580699       19357 wait until an engine has been offlined
         222     1080995    18935748 replication agent sleeping during flush
         260     1151768       12976 waiting for date or time in waitfor command
         179     1156936    58199389 waiting while no network read or write is required
         178     1160206       24323 waiting while allocating new client socket
          61     1734787      302641 hk: pause for some time
         250    56273383    42267187 waiting for incoming network data

Le WaitTime est en milliseconde.

Dans cet exemple il y a deux catégories qui mériteraient une analyse détaillée. Le WaitEventID 222 (replication agent sleeping during flush) et le 178 (waiting while allocating new client socket). A priori ce sont tous les deux des attentes qui sont liées à des paramètres réseau, ce qui pourrait indiquer que la configuration réseau est sub-optimale. Les autres sont probablement assez "normaux".

La table monProcessWaits contient la même information, mais avec le détail par process (SPID). Cela permet de zoomer sur les temps d'attentes d'une opération particulière dans le but d'analyser son comportement et de mettre en évidence des possibilités de tuning. Par exemple:

 
Sélectionnez
select w.*, i.Description from monProcessWaits w, monWaitEventInfo i
where i.WaitEventID = w.WaitEventID and w.SPID=334 order by 5
go
 SPID   KPID        WaitEventID Waits       WaitTime    Description
 ------ ----------- ----------- ----------- ----------- --------------------------------------------------
    334   790823222          31           1           0 wait for buffer write to complete
    334   790823222          54           2           0 waiting for disk write to complete
    334   790823222         214        1978         100 waiting on run queue after yield
    334   790823222          36          15         200 wait for mass to stop changing
    334   790823222          52         170         400 waiting for disk write to complete
    334   790823222          55        1376        1100 waiting for disk write to complete
    334   790823222          51        1373        2400 waiting for disk write to complete
    334   790823222         251       62368       10500 waiting for network send to complete
    334   790823222          29        2442       15400 wait for buffer read to complete
    334   790823222         250       88228      167100 waiting for incoming network data

On voit qu'il s'agit d'un process qui fait passablement d'IO, et qui semble aussi devoir attendre sur des envois de résultats vers le client (waiting for network send to complete).

Historisation de l'information

Comme on l'a vu les tables MDA donnent une information brute de l'état des compteurs depuis le démarrage de l'instance ou depuis le début de la connexion. C'est déjà intéressant, mais certainement pas suffisant pour faire une analyse du comportement de l'instance pendant une période donnée.

Pour cela il faut mettre en place une méthode de capture périodique des données instantanées, et faire un delta pour avoir la consommation intermédiaire.

Rob Vershoor propose une solution sur son site pour la capture en continu des tables "queues" (monSysSQLText, monSysPlanText, etc). Personnellement ces informations là ne me semblent en général pas aussi utiles que ceux qui se trouvent dans monProcessActivity, par exemple. Je propose donc plutôt de conserver les données de monEngine, monDeviceIO, monNetworkIO, monProcessActivity, monOpenObjectActivity et monProcessWaits. Ces informations sont capturées avec une résolution de une minute dans une base technique que j'appelle "mondb". C'est une proc stockée qui tourne en continu qui fait le travail. Cette base existe sur tous mes serveurs de production, et je fais ensuite une extraction de données horaires qui sont archivées dans un serveur d'administration technique, et qui me permet ensuite de faire des analyses d'évolutions de performance sur le long terme.

Le code pour cette base étant relativement compliqué, vous le trouverez ici .