Bdd suspendue sur mutualisé cause trop de requêtes

Général
1

Bonjour la liste!

Celà concerne le site qui tourne sur un 1.9.1 1&1 nous a suspendu la base. Voici un extrait de leur message d’avertissement. Pouvez-vous m’aider à le décoder et m’aiguiller sur une solution?? Merci d’avance, bien cordialement.

2

Le 11 févr. 09 à 21:02, Le Fil a écrit :

Bonjour la liste!

Celà concerne le site www.jtduoff.fr qui tourne sur un 1.9.1
1&1 nous a suspendu la base.

Voici un extrait de leur message d'avertissement. Pouvez-vous m'aider à le décoder et m'aiguiller sur une solution??

Merci d'avance, bien cordialement.

un passage en v2 supprimera les index et leur construction (la recherche se fait alors directement dans la bse) ; ça ne résoudra pas forcément tout mais il y aura tout les progrès faits dans les 1.9 et 2.0 dont le site profitera.
Claude

Cette alerte concerne votre compte client XXXXXXX

Voici un log relevé par mes collègues :

*** dbo192421258@infong280: 211321 Queries: 208625 Selects, 1832 Ins,
712 Upd, 120 Del, 158 Connects

rdb292:~# zgrep dbo192421258 /var/log/anna.log*
/var/log/anna.log:*** dbo192421258@infong280: 25652 Queries: 25307
Selects, 81 Ins, 179 Upd, 70 Del, 53 Connects
/var/log/anna.log.0:*** dbo192421258@infong280: 82205 Queries: 81723
Selects, 174 Ins, 164 Upd, 132 Del, 101 Connects
/var/log/anna.log.1.gz:*** dbo192421258@infong280: 264268 Queries:
262188 Selects, 1095 Ins, 652 Upd, 302 Del, 169 Connects
/var/log/anna.log.2.gz:*** dbo192421258@infong280: 53277 Queries: 52865
Selects, 155 Ins, 142 Upd, 107 Del, 62 Connects
/var/log/anna.log.3.gz:*** dbo192421258@infong280: 48050 Queries: 47748
Selects, 137 Ins, 56 Upd, 103 Del, 84 Connects
/var/log/anna.log.4.gz:*** dbo192421258@infong280: 332509 Queries:
331480 Selects, 333 Ins, 299 Upd, 222 Del, 133 Connects
/var/log/anna.log.5.gz:*** dbo192421258@infong280: 42776 Queries: 42317
Selects, 156 Ins, 187 Upd, 110 Del, 56 Connects
/var/log/anna.log.6.gz:*** dbo192421258@infong280: 172663 Queries:
172050 Selects, 313 Ins, 161 Upd, 127 Del, 99 Connects

rdb292:~# myslowana /db/logs/mysql.slowlog.?.gz -e 'user dbo192421258 '
-s | head -n 20
user count query_time lock_time rows_sent rows_examined

rdb292:~# mylogana -U dbo192421258 /db/logs/mysql.log.1.gz |cut -d\ -f
7- |grep -E '(UPDATE|INSERT|DELETE|update|insert|delete)'| head -n 20
DELETE FROM `db192421258`.spip_caches WHERE
fichier='b/spip%3Fpage%3Dbackend.bd9abf1c' OR
fichier='b/spip%3Fpage%3Dbackend.bd9abf1c.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('b/spip%3Fpage%3Dbackend.bd9abf1c.gz','1234263713','t','13836')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='2/-inc-inc_bandeau-fr.762a0ba5' OR
fichier='2/-inc-inc_bandeau-fr.762a0ba5.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('2/-inc-inc_bandeau-fr.762a0ba5','1234263749','t','786')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='c/-inc-inc-fr.1d78eee2' OR fichier='c/-inc-inc-fr.1d78eee2.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('c/-inc-inc-fr.1d78eee2','1234263749','t','1009')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='6/-plu-der-inc-fr.af789bc1' OR
fichier='6/-plu-der-inc-fr.af789bc1.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('6/-plu-der-inc-fr.af789bc1.gz','1234263761','t','7445')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='e/-inc-inc_recherche-fr.4a5b73c9' OR
fichier='e/-inc-inc_recherche-fr.4a5b73c9.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('e/-inc-inc_recherche-fr.4a5b73c9','1234263761','t','350')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='b/-rec-rec-lang%3D.e673a111' OR
fichier='b/-rec-rec-lang%3D.e673a111.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('b/-rec-rec-lang%3D.e673a111','1234263761','t','488')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='8/-inc-inc-167-spi-fr.d0904139' OR
fichier='8/-inc-inc-167-spi-fr.d0904139.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('8/-inc-inc-167-spi-fr.d0904139','1234260163','t','116')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='8/-inc-inc_forum-167-fr.4fdae574' OR
fichier='8/-inc-inc_forum-167-fr.4fdae574.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('8/-inc-inc_forum-167-fr.4fdae574','1234260163','t','116')
INSERT IGNORE INTO `db192421258`.spip_caches (fichier, id) VALUES
('8/-inc-inc_forum-167-fr.4fdae574', 'id_forum/a167')
DELETE FROM `db192421258`.spip_caches WHERE
fichier='7/-inc-inc_pied-fr.21aba22f' OR
fichier='7/-inc-inc_pied-fr.21aba22f.gz'
INSERT IGNORE INTO `db192421258`.spip_caches (fichier,id,type,taille)
VALUES ('7/-inc-inc_pied-fr.21aba22f','1234263762','t','1353')
INSERT IGNORE INTO `db192421258`.spip_visites (date) VALUES
('2009-02-10')

Une telle utilisation se trouve trop importante pour un serveur SQL
mutualisé.
Dans cette urgence, mes collègues furent contraints de suspendre cette
base de données.
Nous sommes convaincus que vous comprendrez notre décision afin de
garantir un servir équitable pour l'ensemble de nos clients se trouvant
sur la même installation que vous.

Une mise à jour des scripts et une indexation de la base ont très
souvent raisons de ces lenteurs.

3

un passage en v2 supprimera les index et leur construction (la recherche se fait alors directement dans la bse) ; ça ne résoudra pas forcément tout mais il y aura tout les progrès faits dans les 1.9 et 2.0 dont le site profitera.
Claude

Merci Claude.

Je précise qu’on utilise le plugin comme squelette, et qu’il n’existe pas (enore?) pour la V2… Qui a t-il de spécial dans le log ci dessous? nous n’avons que 5 à 600 visites par jour, pas de quoi ralentir un mutualisé tout de même!

4

As-tu une durée de cache, pour l'ensemble des pages de ton site ?

Le Fil a écrit :

un passage en v2 supprimera les index et leur construction (la recherche se fait alors directement dans la bse) ; ça ne résoudra pas forcément tout mais il y aura tout les progrès faits dans les 1.9 et 2.0 dont le site profitera.
Claude

Merci Claude.

Je précise qu'on utilise le plugin http://egt.bardourel.com/ comme squelette, et qu'il n'existe pas (enore?) pour la V2...

Qui a t-il de spécial dans le log ci dessous? nous n'avons que 5 à 600 visites par jour, pas de quoi ralentir un mutualisé tout de même!

5

il y a #CACHE{86400} sur les pages… c’est bien?

Mais ça dit quoi ce log??

Yannick a écrit :

6

La durée de cache est relativement importante, ce qui permet de mettre de côté une explication possible d'un nombre important de requêtes liées à la visite de ton site par un robot aspirateur, par exemple.

Pour le log, je ne peux pas t'aider, et j'attends comme toi l'intervention d'une personne qui nous permettrait de comprendre ce qui c'est passé. Ce n'est pas la première fois qu'un Spip est "coupé" par un hébergeur.

Utilises-tu un ou plusieurs plugins ? J'ai lu sur la liste le cas d'une personne qui s'était fait suspendre son site à cause d'un traffic sql trop important, et cela était dû à un méchant plugin.

Peux-tu également nous dire si les stats sont activées sur ton Spip ?

Le Fil a écrit :

il y a #CACHE{86400} sur les pages... c'est bien?

Mais ça dit quoi ce log??

7

  Bonjour à tous,

Si çà peut aider:

Sur des 1.9.2e:
- J'ai supprimé les stats
- Installé le plugin FASTCACHE
- Vraiment bien ciblé et réglé la durée des caches de chaque page

Peux-tu également nous dire si les stats sont activées sur ton Spip ?

Le Fil a écrit :

il y a #CACHE{86400} sur les pages... c'est bien?

8

Le Fil a écrit :

Mais ça dit quoi ce log??

pas grand chose finalement qui permette de voir sur quoi intervenir précisemment...

dbo192421258@infong280: 211321 Queries: 208625 Selects, 1832 Ins,
712 Upd, 120 Del, 158 Connects
=> globalement, sur la base dbo192421258, il y a eu 211 321 requêtes ;
    dont 208 625 SELECT, 1 832 INSERT, 712 UPDATE, 120 DELETE
    et 158 connexions

après, c'est le détail par fichier de log...

il y a trop peu de données pour juger vraiment,
mais ce pourrait être les accès à la table spip_caches (donc la gestion des caches de 1.9) qui bombardent trop le serveur sql, mais rien n'est moins sûr...

utiliser :
   ?var_profile=1&var_mode=recalcule
dans l'url (en étant loggué administrateur) pour avoir une vue des requêtes lancées pour l'affichage de la page.

9

denisb a écrit :

dbo192421258@infong280: 211321 Queries: 208625 Selects, 1832 Ins,
712 Upd, 120 Del, 158 Connects
=> globalement, sur la base dbo192421258, il y a eu 211 321 requêtes ;
   dont 208 625 SELECT, 1 832 INSERT, 712 UPDATE, 120 DELETE
   et 158 connexions

Sur quelle période ces requêtes ont elles été générées ? 1 jour ? 1 heure ? ...

il y a trop peu de données pour juger vraiment,
mais ce pourrait être les accès à la table spip_caches (donc la gestion des caches de 1.9) qui bombardent trop le serveur sql, mais rien n'est moins sûr...

Et dans ce cas, pourquoi ? Et Comment s'en prémunir ?

10

Yannick a écrit :

denisb a écrit :

dbo192421258@infong280: 211321 Queries: 208625 Selects, 1832 Ins,
712 Upd, 120 Del, 158 Connects

Sur quelle période ces requêtes ont elles été générées ? 1 jour ? 1 heure ? ...

tu me demandes ça à moi...
il faudrait les logs complets pour répondre.

il y a trop peu de données pour juger vraiment,
mais ce pourrait être les accès à la table spip_caches (donc la gestion des caches de 1.9) qui bombardent trop le serveur sql, mais rien n'est moins sûr...

Et dans ce cas, pourquoi ? Et Comment s'en prémunir ?

pourquoi ?
parce que dans spip 1.9 l les noms des fichiers de cache (ceux de tmp/cache/0/, tmp/cache/1/ etc jusqu'à tmp/cache/f/) sont conservés dans la table spip_caches et que pour l'affichage des pages, spip va donc y vérifier/mettre à jour les portions de html à utiliser.

ce comportement est abandonné en spip 2 (plus de table spip_caches ni d'accès à la bdd) où la gestion du cache a été complètement repensée.

faut-il passer les #CACHE à zéro ?
si cela supprimera effectivement les accès à spip_caches pour contrôler la validité des portions html cachées, cela entrainera par contre des accès systématiques à la bdd pour tous les calculs de boucle de la page a afficher...
autrement dit là où l'affichage d'une page nécessitait quelques accès à spip_caches pour vérifier la jeunesse des portions html la composant,
sans cache, à chaque affichage, la page lancera toutes les requêtes sql de chacune de ses boucles.
ce n'est pas forcément plus léger en terme d'accès au serveur sql même s'il n'y aura que des accès majoritairement en SELECT.

une solution (de longue haleine) est d'optimiser l'utilisation des boucles dans ses squelettes ; d'utiliser des inclure à bon escient, avec chacun des valeurs de cache adaptés.
se méfier aussi des plugins qui peuvent peser sur le nombre de requêtes sql (et sur leur optimisation).

11

Super, merci à tous.

Je vais essayer de faire réactiver la bdd pour procéder à tous ces réglages, et pour commencer, passer à 1.9.2

puis désactiver les stats, et virer les inclures trops gourmands en requêtes (comme l’affichage aléatoire des articles de sites syndiqués…).

denisb a écrit :