[ASE 15.0.2] Optimisation cache pool

Bonjour
Nous avons des problèmes de performance sur notre serveur de developpement.
Ce serveur héberge 3 bases de développement (4Go de data + 2Go de log chacune).
On dispose de 4Go de mémoire au total.
Le default data cache est de 500 Mo.

J'ai remarqué dans mes tables "mon" que l'indice stalls n'est pas bon.

Or d'après mda

Citation:

---

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.

---

Comme indiqué dans cette page, j'ai créé la procédure sp__cache
De même que mes tables MDA, elle indique que :
stalls vaut 7211 pour le pool 2Ko de default data cache sur notre serveur

Comment optimiser ce point ?

1. Faudrait-il simplement augmenter la taille de 'default data cache' pour améliorer les performances ?
2. Ne faudrait-il pas prévoir un data cache nommé et séparé pour chaque base ?
3. Préconisez-vous un data cache nommé pour la tempdb ?
4. Préconisez-vous un cache nommé pour le log ?
   Et si oui, en faut-il créer un par serveur ou par base ?
   Est-il automatiquement reconnu/pris en compte ?
5. Utilisez-vous des mises en cache explicites de tables/indexes (binding)?
6. Les liens de base ou d'objets avec le cache, doivent-ils être redéfinis à chaque démarrage serveur ?

D'avance merci
msomso

Hello,

Déjà augmenter le DDC serait une bonne chose, mais ça dépend de ce qui tourne d'autre sur la machine. Créer des caches nommés, c'est plutôt dans un second temps. C'est une bonne habitude de le faire pour tempdb avec un gros pool de 16K mais encore une fois ça dépend de l'utilisation de tempdb. Créer un cache nommé pour le journal est un peu plus secondaire encore. On le fait par exemple avec Replication Server pour accélérer le travail du rep agent.

Le bind de caches nommés avec des bases ou des objets est persistant. Pas besoin de le redéclarer au redémarrage.

A+

bonjour

Sur la deuxième image, je vois que le nb de lectures physiques est égal aux logiques. Est-ce un hasard, ce qui signifierait qu'il y a des traitements vraiment lourds qui viennent de tourner ou bien est-ce que le serveur vient d'être redémarré ? Ou j'ai manaué qqch...

Je pense qu'il faudrait identifier les requêtes qui provoquent tant de rotation de pages sûrement des mises à jour massives. Dans l'absolu il faudrait augmenter le cache par défaut dans un premier temps, la zone de nettoyage (wash buffer) sera automatiquement augmentée.

Utiliser des caches nommés pour tables et/ou index peut s'avérer nécessaire lorsqu'il y a bcp de spinlock contention par exemple, car qq objets sont énormément accédés, et de manière concurrente.

Hello,

Tes questions appellent des réponses assez générales : les caches nommés, les pools de large IO, et les bindings sont effet des pratiques courantes, plus habituellement répandues en production.
N'aurais-tu pas le résultat de 2-3 exécutions représentatives de sp_sysmon ? Ca nous permettrait peut-être d'identifier un problème flagrant.

a+

Bonjour

Je commence par :
- doubler la taille de DDC : 1Go au lieu de 500 Mo actuelles.
- créer un DC de 500 Mo dédié à tempdb (pool de 16 Ko)

Sans être en prod., nous constatons des problèmes de requêtes avec des tris et 'group by', notamment quand il s'agit de critères étant les colonnes concaténées ou fonctions:

R1:

Code:

---

1 2 3 4 5 6

Select colonneA + '-' + colonneB as "MonCritere1", MaFonction(colonneC) as "MonCritere2", colonneC as "MaValeurDeGroupe" from <xxx> where <yyy> group by colonneA + '-' + colonneB, MaFonction(colonneC) order by 1,2

---

Ces mêmes requêtes (mêmes jointure, where clause) s'exécutent bien quand on les remplace par
R2

Code:

---

1 2 3 4

Select colonneA, colonneB ,colonneC from <xxx> where <yyy> group by colonneA , colonneB order by 1,2

---

Malheureusement quand il s'agit de fonctions autres que simple concaténation on n'a pas de solution de contournement.

Dans le meilleur de cas, avec les requête de type "R1" on constate un grand rallongement de temps d'exécution.
Parfois, ce genre de requête génère un Msg. 701 alors qu'il ne s'agit pas de procédures stockées, parfois on arrive à l'arrêt inopiné de serveur ....
J'ai lu dans la doc que la zone procédure cache est utilisée aussi pour les tris et group by. Nous avons pourtant 70Mo de 'procedure cache'. Nous n'utilisons pas de procédures stockées mais des fonctions SQL utilisées souvent dans les listes select de requêtes.
Devons nous agrandir aussi la zone 'procedure cache'?

Parfois on a le message suivant:

Citation:

---

Caused by: com.sybase.jdbc3.jdbc.SybSQLException:
Impossible d'allouer de l'espace pour 'temp worktable' dans la base 'tempdb' car le segment 'system' est plein ou n'a pas d'extents disponibles.
S'il n'y a plus de place dans syslogs, videz le journal des transactions. Ou utilisez ALTER DATABASE pour agrandir le segment.

---

Pour répondre à kagemaru: oui, le serveur venait d'être redémarré.

A la prochaine crise ;) je vais générer la trace sysmon, même si je la trouve peu parlant.
A ce propos, les tables MDA ne suffisent-elles pas ?

merci
msomso
P.S.
Doit-on adapter les caches si l'utilisation de 2 CPU (2 engines) ?

Euh - pour R1, ce serait pas un "bête" problème de produit cartésien?

Ce qui expliquerait pourquoi le cache se fait complètement flusher.

Il me semble que

Code:

---

1 2 3 4 5 6 7

SELECT colonneA + '-' + colonneB AS "MonCritere1", MaFonction(colonneC) AS "MonCritere2", colonneC AS "MaValeurDeGroupe" FROM <xxx> WHERE <yyy> GROUP BY colonneA + '-' + colonneB, MaFonction(colonneC) ORDER BY 1,2

---

devrait être ecrit

Code:

---

1 2 3 4 5 6 7

SELECT colonneA + '-' + colonneB AS "MonCritere1", MaFonction(colonneC) AS "MonCritere2", colonneC AS "MaValeurDeGroupe" FROM <xxx> WHERE <yyy> GROUP BY colonneA + '-' + colonneB, MaFonction(colonneC), colonneC ORDER BY 1,2

---

sauf si MaFonction est vraiment un aggregat, dans quel cas MaFonction(colonneC) ne doit pas être dans le group by...

Un "bad group by" expliquerait aussi que la worktable générée consomme tout l'espace dispo dans tempdb...


Michael

Bonjour,

Produit cartésien, je ne pense pas car la même requête s'exécutait en écriture "R2" tout en restant bloqué en "R1".
En fait pour ce que j'ai nommé maladroitement "colonneC" dans l'exemple généralise, nous avons des fonctions de groupe (max, somme etc).

En attendant vos avis, la semaine dernière j'ai fait ceci

Code:

---

1 2 3

exec sp_configure 'number of open partitions', 10000 -- au lieu de 20 (par defaut): exec sp_configure 'number of histogram steps', 200

---

J'ai recalculé par la suite les stats (tables et indexes).
Et ma requête s'exécute aujourd'hui, mais je suis pratiquement seule à travailler sur la base. On verra si cela continue quand l'équipe sera de retour de vacances.

Merci
msomso

Si - il s'agit bien d'un produit cartésien.

Tu peux faire le test:

Code:

---

1 2 3 4

select suid, hostname, max(spid) from master..sysprocesses group by suid

---

La plupart des SGBD n'autorisent pas cette forme - ils veulent un GROUP BY avec toutes les colonnes qui n'ont pas d'aggregat (cad suid, hostname dans mon example). Sybase autorise cette syntaxe pour une raison assez obscure (perso je ne l'ai jamais utilisée).

Donc, la requête R1 doit être ecrite:

Code:

---

1 2 3 4 5 6 7

SELECT colonneA + '-' + colonneB AS "MonCritere1", MaFonction(colonneC) AS "MonCritere2", colonneC AS "MaValeurDeGroupe" FROM <xxx> WHERE <yyy> GROUP BY colonneA + '-' + colonneB, colonneC ORDER BY 1,2

---

où MaFonction est un aggregat (max, sum, min, etc), sinon tu va avoir un grand nombre de lignes dupliquées, et donc un effet très néfaste sur le cache, tempdb, etc.

La version sans fonction (R2) marche, parce que le comportement de Sybase quand un fait un GROUP BY sans fonction d'aggregat est différend (essentiellement comme un "distinct").

Michael

Bonjour
Je m'excuse car voulant donner un exemple générique, je me suis fait mal comprendre.
Je suis absolument sûre qu'il n'y ait pas de produit cartésien dans la requête.

Pour preuve, je joins un fichier avec 2 vrais requêtes: R1 et R2.
R1 ne se termine pas.
R2 retourne 19 lignes au bout de 12 secondes.
L'appel à F_RecIdMetierDJ fait planter la requête R1.

Le fichier joint contient aussi le code de 2 fonctions SQL .
Dans R1, on effectue 2 selects de plus pour chaque ligne de/dd suite à l'appel à F_RecIdMetierDJ
pour obtenir le formatage voulu de colonnes "expéditeur"/"destinatire".
Mais de là à faire planter la requête ...

Auriez-vous une explication ?

Merci
msomso

P.S.
Le volume de données obtenues par les jointures successives est vraiment petit :
En utilisant les noms d'alias (l, ae, ad, de, dd, a, m, c), les cardinalités sont suivantes:
- on a < 10 lignes entre table "l" et "ae"/"ad"
- on a 1 ligne entre table "ae" et "de"
- on a 1 ligne entre table "ad" et "dd")
- on a < 20 lignes entre table "l" et "a"
- on a 1 ligne entre table "l" et "m"
- on a 1 ligne entre table "a" et "c" .
L'appel à la fonction ne doit donc pas être pénalisant: 19 calculs attendus pour "de" et 19 pour "dd".
Et pourtant au bout de plusieurs minutes la R1 n'affiche rien ...

Bonjour,

Les fonctions c'est très séduisant mais on se retrouve à faire un appel de fonction pour chaque ligne, ce qui peut parfois se traduire en gros pb de performance. Il est qu'avec un petit nombre de lignes, cela ne devrait pas poser de problème.

Pour mieux comprendre votre cas, est-ce qu'il vous serait possible de poster un show plan (avec noexec si la requête ne renvoie jamais la main) ,la volumétrie des tables et les index créés ?

Par ailleurs, je ne vois pas tellement l'interêt de la fonction de conversion de date à la fin. Pourquoi ne pas simplement utiliser le convert directement dans votre requête ?

Idem pour l'autre fonction, pourquoi ne pas concaténer directement dans la requête.

Code:

---

1 2 3 4

select distinct de.UCO_S_CODEUC + de.DEC_S_SEQUENCE , dd.UCO_S_CODEUC + dd.DEC_S_SEQUENCE , etc.

---

Je vois bien que c'est pour permettre une meilleure réutilisabilité du code mais pour la fonction de conversion, je reste sceptique :-)

Je comprend mieux maintenant...

En plus des commentaires de kagemaru avec lesquels je suis 100% d'accord, il serait peut-être bon d'enlever le "distinct" dans R1, qui ne sert à rien (d'ailleur - un "distinct" combiné avec un GROUP BY est pour moi un signal qu'il y a qqch de faux dans la requête!)

Michael

Bonjour
les formatages actuels ne seront peut être pas définitifs or ils sont utilisés dans de nombreux endroits (éditions).
Par exemple on voudra peut être d'utiliser les séparateurs etc. On veut garder la souplesse et facilité de maintenance.

La concaténation directe, telle que vous suggérez, je l'ai testée par curiosité et cela bloque aussi:
select de.UCO_S_CODEUC + de.DEC_S_SEQUENCE bloque quand utilisé avec group by
Par contre de.UCO_S_CODEUC, de.DEC_S_SEQUENCE passe bien avec group by.

Le problème semble donc grave et général.
On a d'autres requêtes présentant le même blocage.
Par exemple dans le fichier ci-joint ReqDenormalisée.sql on doit utiliser les "case" en dur au lieu de le centraliser via fonction sinon la requête reste bloquée.

Ce matin, comme j'ai laissé "tourner" la requête R1 un certain temps, elle a provoqué l'arrêt de serveur cf. extrait fichier log ci-joint.

Qu'en pensez-vous ?

msomso

Bonjour
voici le plan généré dans une session isql ouverte sous DOS (pourquoi ça ne marche pas dans IneractiveSQL ?)

Qu'entendez-vous précisément par "volumétrie tables et index" ?
Merci
msomso

Bonjour,

pour abonder dans le sens de Michael, voici l'extrait de la documentation sybase sur le group by:

Citation:

---

group by and SQL standards

The SQL standards for group by are more restrictive than the Sybase standard.The SQL standard requires that:

*

The columns in a select list must be in the group by expression or they must be arguments of aggregate functions.
*

A group by expression can only contain column names in the select list, but not those used only as arguments for vector aggregates.

---

http://infocenter.sybase.com/help/in...g/sqlug111.htm

Plusieurs extensions T-SQL permettent de passer outre ces restrictions, mais peuvent donner des résultats... surprenants, je l'ai déjà expérimenté:

http://infocenter.sybase.com/help/to...g/sqlug113.htm

Concernant le stack trace et le plantage du serveur, il faut voir avec le support sybase, qui vous demandera certainement un dump de la mémoire partagée.

Enfin, merci de nous donner le plan d'exécution de la requête qui n'aboutit pas.

Bonjour
Le plan a bien été ajouté.

Suite à la dernière ligne citée par Roller:

Citation:

---

A group by expression can only contain column names in the select list

---

, j'ai modifié le Group By de ma requête.
Suite à quoi, elle s'exécute bien et retourne 19 lignes en 39 secondes !

Merci beaucoup.
P.S.
ASE semble tout de même peu résistant aux écarts de syntaxe SQL, non ?

Il me semble qu'il doit quand même y avoir un problème avec cette requête.

Elle ne devrait jamais prendre 39 secondes à s'exécuter.

Si tu enlève le "distinct" - est-ce que tu as le même nombre de ligne?
Il serait aussi intéressant de voir le résultat de "set statistics IO on" pour un exécution.

Michael

Bonjour
Après avoir étudié le point soulevé par Michael:

Le "distinct" peut être omis si le group by contient:
l.LIG_D_DATECREATION au lieu de
dbo.F_FormatDate_DDMMYYYY(l.LIG_D_DATECREATION).

La fonction retourne une même valeur (date au format DD/MM/YYYY pour les dates différant par minutes, secondes ou ms) et curieusement cela occasionne la multiplication de lignes du résultat en absence de "distinct".

Conclusion:
après une application stricte de la règle :

Citation:

---

A group by expression can only contain column names in the select list

---

la requête fonctionne sans "distinct".

Et plus précisément, elle retourne le même résultat qu'avec "distinct", lequel devient donc inutile comme on pouvait s'attendre à cause de group by. Mieux: le temps d'exécution passe de 40 à 4 sec.

Je pense fermer cette discussion si vous n'avez pas d'autres remarques.
Mille mercis.

msomso
P.S.

La requête finale dans fichier joint R1sansDistinct.sql.

Bonjour
Juste pour finir:
l'utilisation de "set fipsflagger on" permet d'avoir un avertissement suivant à l'exécution de la requête telle que dans R1avecGroupBycorrigé.sql (joint ci-dessous):
ErrorCode: 7321

Citation:

---

Warning: Line number 1 contains Non-ANSI text. The error is caused due to the use of column name(s) in a select list with aggregates but column name(s) not in group by list.

SQLState: 01FFW
ErrorCode: 7321

---

msomso

Ah - merci! Je n'ai jamais utilisé cette option, intéressant de savoir à quoi ça sert... :-)

Michael