Sybase 12.5.0.3 - Tables dynamiques MDA de monitoring

Introduction

Une évolution de confort est apparue avec la version 12.5.0.3 : les tables de monitoring. Ces dernières permettent de s'affranchir de la mise en place lourde de Sybase Historical Server pour la résolution de problèmes ponctuels et également d'obtenir des compteurs plus pertinents que les résultats renvoyés par la procédure sp_sysmon.

Parmi les informations très intéressantes que l'on peut obtenir avec les tables de monitoring :

  • les deadlocks les plus récents apparus sur le serveur
  • les statistiques sur les procédures dans le cache de procédure
  • les statistiques sur les usages des indexes
  • les statistiques sur les objets ouverts
  • les requêtes SQL les plus récemment exécutés ou en cours d'exécution
  • les plans d'exécution les plus récemment élaborés

Cette documentation s'attarde plus particulièrement sur les tables de monitoring présentant un intérêt tout particulier et affichant des compteurs difficilement accessibles avec Sybase Historical Server.

Installation des tables de monitoring

Les tables proxy de monitoring d'ASE 12.5.0.3 ne sont pas installées par défaut, la procédure d'installation de ces dernières tables comprend :

  • La création d'un serveur nommé dans sysservers
  • La création du rôle mon_role
  • L'installation des tables de monitoring

Création d'un serveur nommé dans sysservers

Un serveur nommé loopback doit être créé dans sysservers pour la gestion des tables proxy de monitoring. Pour créer ce dernier :

declare @servernetname varchar(30)
select @servernetname=srvnetname
from sysservers
where srvname=@@servername
exec sp_addserver loopback, NULL, @servernetname
go

Création du rôle mon_role

Le rôle mon_role doit être créé au sein du serveur ASE, les tables proxy n'étant accessibles qu'aux utilisateurs pour lesquels le rôle mon_role est attribué :

create role mon_role
go
grant role mon_role to sa_role
go

Installation des tables de monitoring

Le script installmontables dans la répertoire $SYBASE/ASE-12_5/scripts se charge de créer les tables proxy de monitoring. Ces tables sont créées dans la base master et les droits de sélection ne sont donnés qu'au rôle mon_role créé précédemment.

Pour lancer le script installmontables :

$SYBASE/$SYBASE_OCS/bin/isql -Usa -P<sapassword> -S<servername> -iinstallmontables

Voici un exemple de création d'une table proxy dans le script installmontables

create existing table monNetworkIO (
  PacketsSent int,
  PacketsReceived int,
  BytesSent int,
  BytesReceived int,
)
external procedure
at "loopback...$monNetworkIO"
go
grant select on monNetworkIO to mon_role
go

Les 34 tables de monitoring sont listées dans le tableau qui suit :

Table Statistiques / Liste
monTables Liste des tables de monitoring
monTableParameters Liste des paramètres optionnels pour chaque table de monitoring
monTableColumns Colonnes des tables de monitoring
monState Table de monitoring sur l'état du serveur ASE
monEngine Engines utilisés par ASE (stats)
monDataCache Caches de données (stats)
monProcedureCache Cache de procédures (stats)
monOpenDatabases Bases de données ouvertes (état et stats)
monSysWorkerThread Worker process (stats)
monNetworkIO E/S réseau (stats)
monErrorLog Messages d'erreur les plus récents du fichier de log d'ASE
monLocks Verrous posés et requis par les process
monDeadLock Informations sur les deadlocks les plus récents
monWaitClassInfo Description textuelle de toutes les classes d'attente (par exemple, l'attente d'une lecture sur disque...)
monWaitEventInfo Description textuelle pour chaque situation possible d'attente pour un process
monCachedObject Statistiques sur les objets et indexes dont les pages sont en cache
monCachePool Pools alloués pour tous les caches (stats)
monOpenObjectActivity Objets ouverts (stats)
monIOQueue Files d'E/S (queues) vers les devices logiques de données et de log pour les bases de données normales ou temporaires
monDeviceIO E/S vers les devices :lectures, APF, écritures (stats)
monSysWaits Statistiques sur les process en attente d'évènements
monProcess Process en exécution ou en attente (stats)
monProcessLookup Informations sur l'origine d'un process (application, IP...)
monProcessActivity Activité des process (stats)
monProcessNetIO E/S réseau par les process (stats)
monProcessObject Objets accédés par les process (stats)
monProcessWaits Liste des rocess en attente d'un événement
monProcessStatement Commandes en cours d'exécution pour les process
monProcessSQLText Texte SQL en cours d'exécution pour les process
monSysPlanText Plans d'exécution les plus récemment générés
monSysStatement Commandes les plus récemment exécutées (stats)
monCachedProcedures Procédures dans le cache de procédure (stats)
monSysSQLText Textes SQL les plus récemment exécutés ou en cours d'exécution
monProcessProcedures Procédures en cours d'exécution par les process

Extraction des informations sur les tables de monitoring

Les 3 tables monTables, monTableParameters donnent rapidement la description des tables de monitoring.

Pour la liste descriptive des tables de monitoring

select TableName, Description from monTables

Pour la liste descriptive des colonnes d'une table de monitoring

select TableName, ColumnName, TypeName, Description from monTableColumns
where TableName='monSysPlanText'

Extraction des données des tables de monitoring

Il est impératif de spécifier des arguments de recherche pertinents pour l'extraction des données dans les tables de monitoring et ainsi éviter des requêtes non performantes et non pertinentes.

Par exemple, pour extraire les statistiques sur tous les objets ouverts pour une base de données en particulier :

select * from monOpenObjectActivity
where DBID=1

Pour obtenir la liste descriptive des paramètres pertinents d'une table de monitoring

select TableName, ParameterName, TypeName
from monTableParameters
where TableName='monOpenObjectActivity'monOpenObjectActivity  DBID         int
monOpenObjectActivity  ObjectID     int
monOpenObjectActivity  IndexID      int

Colonnes de compteurs réinitialisables dans les tables de monitoring

Quelques colonnes dans les tables de monitoring contiennent des compteurs incrémentés au cours de la durée de vie d'Adaptive Server. Lorsqu'un de ces compteurs atteint sa plus haute valeur (2,147,483,647), ce dernier est réinitialisé à 0, ce qui est communément appelé le « wrapping » de compteurs.

Pour ce type de colonnes, il est recommandé de réaliser de l'échantillonnage plutôt que d'analyser la valeur courante.

Pour obtenir la liste des colonnes associés à des compteurs réinitialisables dans les tables de monitoring :

select TableName, ColumnName from monTableColumns
where (Indicators & 1) = 1

Paramètres du serveur ASE v 12.5.0.3 liés au monitoring

Par défaut, ASE ne collecte pas les informations requises pour les tables de monitoring. Les paramètres serveur liés au monitoring sont donnés par :

exec sp_configure 'Monitoring'

Cette commande retourne alors les paramètres suivants :

Paramètre Dynamique Intervalle
deadlock pipe active 0-1
deadlock pipe max messages 0-2147483647
enable monitoring 0-1
errorlog pipe active 0-1
errorlog pipe max messages 0-2147483647
max SQL Text monitored 0-2147483647
object lock wait timing 0-1
per object statistics active 0-1
plan text pipe active 0-1
plan text pipe max messages 0-2147483647
process wait events 0-1
sql text pipe active 0-1
sql text pipe max messages 0-2147483647
statement pipe active 0-1
statement pipe max messages 0-2147483647
statement statistics active 0-1
SQL batch capture 0-1
wait event timing 0-1

Le tableau qui suit indique les paramètres serveur nécessaires pour chaque table de monitoring. Sans l'activation de ces paramètres serveur, la collecte des données dans les tables de monitoring n'est pas réalisée.

Table Paramètre serveur requis
monTables /
monTableParameters /
monTableColumns /
monState /
monEngine enable monitoring
monDataCache enable monitoring
monProcedureCache enable monitoring
monOpenDatabases enable monitoring
monSysWorkerThread enable monitoring
monNetworkIO enable monitoring
monErrorLog enable monitoring errorlog pipe active errorlog pipe max messages
monLocks enable monitoring wait event timing
monDeadLock enable monitoring deadlock pipe active deadlock pipe max messages
monWaitClassInfo /
monWaitEventInfo /
monCachedObject enable monitoring
monCachePool enable monitoring
monOpenObjectActivity enable monitoring per object statistics active
monIOQueue enable monitoring
monDeviceIO enable monitoring
monSysWaits enable monitoring wait event timing
monProcess enable monitoring wait event timing
monProcessLookup /
monProcessActivity enable monitoring wait event timing per object statistics active
monProcessNetIO enable monitoring
monProcessObject enable monitoring per object statistics active
monProcessWaits enable monitoring process wait events
monProcessStatement enable monitoring statement statistics active per object statistics active
monProcessSQLText enable monitoring max SQL text monitored SQL batch capture
monSysPlanText enable monitoring plan text pipe active plan text pipe max messages
monSysStatement enable monitoring statement statistics active per object statistics active statement pipe active statement pipe max messages
monCachedProcedures /
monSysSQLText enable monitoring max SQL text monitored SQL batch capture sql text pipe active sql text pipe max messages
monProcessProcedures /

Etat stateful des tables de monitoring

Un certain nombre de tables de monitoring fournissent les données les plus récentes plutôt qu'un état courant. ASE maintient en effet un contexte pour chaque client qui accède aux tables de monitoring en mode 'stateful' et ne renvoie que les données qui n'ont pas été précédemment déjà lues. Ce mode a été mis en place pour des performances optimales et éviter la duplication de lignes lors de l'incorporation de ces données dans un repository pour historisation.

Les tables de monitoring sont à titre d'exemple en mode 'stateful'

  • monErrorLog
  • monDeadLock
  • monSysStatement
  • monSysSQLText
  • monSysPlanText

Pour déterminer si une table de monitoring est en mode stateful avec la requête ci-dessous :

select TableName from monTables where Indicators & 1 = 1
Il est préférable pour les tables de monitoring en mode 'stateful' de lancer des commandes select * into ou insert into dans un repository plutôt que de lancer des requêtes de sélection classiques.

Exemple pratique : Fréquence d'utilisation des indexes

La table de monitoring monOpenObjectActivity offre la capacité de déterminer les indexes en cours d'utilisation et les indexes rarement utilisés par l'optimiseur.

Pour analyser la fréquence d'utilisation d'un index, les cinq colonnes de la table monOpenObjectActivity décrites ci-dessous fournissent des informations précieuses.

Colonne Description
IndexID Identifiant unique de l'index
OptSelectCount Nombre de fois que l'objet (table ou index) a été utilisé comme méthode d'accès par l'optimiseur
LastOptSelectDate Date de la dernière incrémentation de la colonne OptSelectCount
UsedCount Nombre de fois que l'objet (table ou index) a été accédé dans une requête
LastUsedDate Date de la dernière incrémentation de la colonne UsedCount

Quelques remarques importantes

  • Si un plan est déjà compilé et en cache, la colonne OptSelectCount n'est pas incrémentée à chaque exécution du plan. En revanche, la colonne UsedCount est incrémentée lorsque le plan est exécuté.
  • Si la commande set noexec est activée, la colonne OptSelectCount est incrémentée mais pas la colonne UsedCount.
  • Les données de monitoring sont non persistentes, elles sont perdues à chaque redémarrage du serveur.
  • Les données de monitoring ne sont valables que pour les objets actifs.
  • Pour les systèmes mal configurés (valeurs des paramètres 'number of open objects' et 'number of open indexes' trop faibles), les données de monitoring sont réinitialisées à chaque nouvelle montée de l'objet dans le cache de descripteurs (metadata cache), voir pour cela la valeur reused donnée par la commande sp_monitorconfig pour effectivement s'apercevoir que les valeurs des paramètres 'number of open objects' et 'number of open indexes' sont trop faibles.
exec sp_monitorconfig 'number of open objects'
exec sp_monitorconfig 'number of open indexes'

Exemple:

Cet exemple affiche tous les indexes qui ne sont pas couramment utilisés:

select DBID, ObjectID, IndexID, object_name(ObjectID, DBID)
from monOpenObjectActivity
where DBID = db_id("financials_db") and OptSelectCount = 0
ObjectName     id IndexName        OptCtLast  OptSelectDate        UsedCount LastUsedDate
----------      - ---------------  ---------  ------------------   --------- -------------------
Customer        2 ci_nkey_ckey        3        Sep 27 2002 4:05PM        20   Sep 27 2002 4:05PM
Customer        0 customer_x          3        Sep 27 2002 4:08PM        5    Sep 27 2002 4:08PM
Customer        1 customer_x          1        Sep 27 2002 4:06PM        5    Sep 27 2002 4:07PM
Customer        3 ci_ckey_nkey        1        Sep 27 2002 4:04PM        5    Sep 27 2002 4:05PM
Customer        4 customer_nation     0        Jan 1 1900 12:00AM        0    Jan 1 1900 12:00AM

Dans cet exemple, l'index customer_nation n'a jamais été utilisé, d'où la date "Jan 1 1900 12:00AM".