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.
sp_configure 'enable cis', 1Si CIS n'était pas configuré il faut relancer l'instance.
3. Créer un alias pour le serveur local nommé "loopback"
sp_addserver loopback, null, @@servername4. Charger la définition des tables MDA:
cd $SYBASE/$SYBASE_ASE/install
isql -Usa -P... -S... -i installmontables -o installmontables.log5. Configurer le monitoring:
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
go6. Donner le rôle "mon_role" aux logins qui doivent accéder aux tables MDA:
grant role mon_role to sa7. Faire un petit test
select * from master..monEngineNote : 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):
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:
select * into #txt from monSysSQLTextet 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.
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:
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 cache3. Statistiques par SPID
Ces tables donnent des informations détaillées pour chaque connexion (SPID) :
monProcess, monProcessActivity, monProcessNetIO, monProcessObject, monProcessProcedures, monProcessSQLText, monProcessWaits, monProcessWorkerThread4. Tables "Queues"
monDeadLock
monErrorLog
monSysPlanText
monSysSQLText
monSysStatementCes 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
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 #tabsLes 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 :
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
goProcé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:
select w.*, i.Description
from monSysWaits w, monWaitEventInfo i
where i.WaitEventID = w.WaitEventID
order by 2Ce qui donne quelque chose comme ceci:
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 dataLe 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:
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 dataOn 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 .