MySQL: flood, performances & max_questions

---

De temps en temps, je vois passer des gens qui tombent sur cette erreur et se demandent de quoi il s'agit:

ERROR 1226 (42000): User 'xxx' has exceeded the 'max_questions' resource (current value: yyy)

Qu'est-ce que c'est ? ; Causes ; Solutions: c'est parti.

De quoi s'agit-il ?

Il s'agit de notre système anti-flood. Quand je dis "anti-flood", je parle d'une limite vraiment haute qui sert à protéger la plateforme d'un comportement vraiment anormal. Si vous faites partie des 0.01% d'utilisateurs à qui c'est déjà arrivé (ce n'est pas une approximation mais une vraie statistique, prise au moment où je vous écrit), alors il faut faire quelque-chose. Je parle des solutions un peu plus bas.

Qui est concerné ?

Vous ne retrouverez cette erreur que sur les bases de données livrées avec vos hébergement web, appelées également "SharedSQL" ou "Bases de données MySQL mutualisées". Mais ça ne vaut pas dire pour autant que passer sur une Web Cloud Database, une Public Cloud Database, un serveur dédié ou un autre hébergeur corrigera le problème. Eux aussi vont se faire noyer par le comportement anormal, et la perf ne sera pas au rendez-vous.

max_questions?

MySQL permet de limiter la fréquence du nombre de requêtes faites par un utilisateur: https://dev.mysql.com/doc/refman/8.0/en/user-resources.html Le message dont on parle est ce que réponds MySQL quand cette limite a été dépassée.

C'est sur cette fonctionnalité que s'appuye notre système anti-flood, mais on l'a améliorée. En effet, la fonctionnalité telle quelle permet de définir un nombre maximum de requête… par heure. Prenons l'exemple où on limiterait un utilisateur à 60000 requêtes par heure.

• 14:00 : Une heure pile, MySQL reset le compteur. L'utilisateur a fait 0 requête cette heure-ci.
• 14:20 : 20 minutes se sont écoulées, et le compteur en est à 1234. Pour une raison ou pour une autre, l'utilisateur fait 100000 requêtes d'un coup (ce qui n'est pas normal, on va voir un peu plus loin comment c'est possible). Les 58766 premières passent, les 41234 suivantes sont bloquées.
• De 14:20 à 14:59 : La limite a été atteinte pour cette heure-ci. Toutes les requêtes sont bloquées en attendant 15:00, heure à laquelle le compteur va être remis à 0.

Ce comportement n'est pas satisfaisant: le site branché à la base de données est cassé pendant 40 minutes, alors que le comportement anormal est passé. Les requêtes légitimes sont bloquées.

Ce qu'on a fait pour améliorer ça et transformer la limite de requêtes par heure en limite de requêtes par minute, c'est qu'on définit une limite 60 fois plus petite, mais on reset le compteur toutes les minutes. Pour reprendre notre exemple, au lieu de définir une limite à 60000 et attendre que MySQL reset le compteur toutes les heures, on va définir une limite à 1000 et remettre le compteur à 0 toutes les minutes. Reprenons notre exemple:

• 14:20:10 : Le compteur est à 9 (le nombre de requêtes faites de 14:20:00 et 14:20:10). C'est l'heure du comportement anormal, les 100000 requêtes illégitimes arrivent. La majorité se fait bloquer. Plus rien ne passe jusque 14:21.
• 14:21:00 : Le compteur est à 0. Le comportement anormal est passé, le site va rester UP.

Causes et solutions

Les cas que je décris ci-dessous sont des cas qu'on a constaté de multiples fois. La liste n'est pas exhaustive, mais elle couvre une grande majorité des cas.

Les indexes

Une grosse table non correctement indexée est très souvent à l'origine de gros problèmes de performance. Il faut peu de ce genre de requêtes pour noyer le serveur. C'est généralement très simple à corriger.

Solution: Vérifiez / corrigez vos indexes en allant voir ce thread et en recherchant "80% des indexes sont extrêmement faciles à créer."

Le filtrage en front

Je vois également passer ce genre de requête:

select * from table1;

Avec une table à 20 colonnes et à 1 million de lignes, MySQL passe son temps à envoyer le contenu de toute la table à un PHP qui va ensuite filtrer tout ça. Le filtrage doit se faire au niveau de la base. Pas du PHP.

Solution:

Ne sélectionnez que les lignes qui vous intéressent en utilisant la clause where:

select * from table1 where userId='Jean-Jacques';

Ne sélectionnez que les colonnes qui vous intéressent en évitant le * et en explicitant ce que vous voulez:

select firstName, lastName from table1 where userId='Jean-Jacques';

Les boucles en front

Un autre cas étonnant que j'ai vu passer plusieurs fois concernait un traitement fait sur une table. Pour chaque ligne de la table, l'utilisateur souhaitait agréger des informations et les stocker autre part. La boucle était faite côté PHP, et la table faisait 1 million de lignes. Conséquence: 1 million de traitements lancé par PHP, et un million de requêtes faites à la base de données. Ce genre de chose peut très généralement se faire en 1 seule requête.

Solution:

Une requête dans une boucle PHP est souvent une mauvaise idée. Essayez de faire la même chose en 1 requête.

Les problèmes de redirection et les bots bêtes

L'IA n'apporte pas que de bonne choses. Ça nécessite souvent l'absorption de bases de connaissances par tout un tas de robots qui passent leur temps à crawler le web (à le lire, à l'aspirer…). Les robots qui font ça sont plus ou moins bien codés. Dans la plupart des cas, il vient nous noyer de requêtes quelques minutes, on absorbe tout ça, et il repart.

On a vu passer des robots qui, cumulés à des problèmes de redirection, bouclaient sur de l'aspiration de contenu. Prenons par exemple une page, https://example.org/a/b.php, où figure un lien généré à partir de la page elle même: https://example.org/a/a/b.php. Par un jeu de réécriture, https://example.org/a/a/b.php est en fait la même page que https://a/b.php, et va générer un lien vers https://example.org/a/a/a/b.php, qui va générer un lien vers https://example.org/a/a/a/a/b.php, etc…

Tant que le robot voit une URL qu'il n'a pas déjà traité, il continue sont aspiration, et boucle ainsi à l'infini, à coup de millions de requêtes par heure…

Solution:

Vérifiez vos règles de réécriture et les liens générés dans vos pages.

Hébergement compromis

Il est également possible que votre site se soit fait attaquer, généralement, en passant par une faille dans votre code.

Solution:

Quand on rencontre ce genre de cas, on ferme le site par mesure de sécurité, et on vous préviens. Il est également possible qu'on ne l'ait pas encore détecté. Dans ce cas, à vous de vérifier que tout le code du site est bien à vous, de bien vérifier que votre CMS et ses plugins sont à jour, etc…

Les crons et plugins

Les appels massifs vers la base de données ne viennent pas toujours d'appels externes comme les bots décrits au dessus. Ils viennent parfois de l'intérieur de votre hébergement:
- Des crons
- Des wp_crons

Solution:

Depuis votre Manager, allez voir les statistiques de votre hébergement web. Si vous voyez un nombre raisonnable de hits, c'est que le problème vient sûrement de l'un des 3 cas listés ci-dessus. Vérifiez vos crons et vos wp_crons.

En espérant que ça aide =)

Mikaël