Performances MySQL sur un site à forte consultation

SuperCed

Membre d’élite
Club MacG
20 Juin 2001
1 229
55
42
superced.rb38.eu
J'ai un site à forte consultation avec un serveur MySQL 5.0.38.

J'ai quelques problèmes sur de grosses tables.
Par exemple, mes session sont stockées en base dans une table session.
Je souhaite garder les sessions pendant un mois. Cependant ces tables ont vite fait de prendre de l'embonpoint et atteignent souvent quelques millions d'enregistrements.
Evidemment, ce n'est pas vraiment optimum quand il faut rechercher une session sur chaque page php affichée.

Pour cela, j'ai fait un script qui tourne une fois par jour et qui vide les sessions expirées, soit celle qui ont plus d'un mois.
Bloc de code:
$sql = "DELETE
        FROM sessions
        WHERE expiry < '".mktime()."'";

Le problème est le suivant, sur 300 000 enregistrements, cette requête prend déjà 3 minutes!!! Ok, les sessions contiennent beaucoup de variables, mais c'est quand même énorme.
J'utilise une table InnoDB, et le problème, c'est que cette requête bloque les accès en lecture sur la table. Evidemment, car il parait logique que le delete doit d'abord intervenir sur la table complète avant une quelconque lecture.

J'aimerais savoir s'il y a un moyen pour éviter cette concurrence et que les lectures soient possible en concurrence avec les delete.

D'autre part, j'aimerais savoir s'il n'y a pas une architecture possible afin de réduire la taille de la table.

J'ai pensé par exemple, à utiliser 2 tables, une sur laquelle il n'y aurait que 20% des enregistrements, et une autre qui serve d'archive et qui sert pour 80% des autres sessions. Ainsi, les sessions les plus utilisées seront dans la petite base, et les accès seront plus rapides.
Est-ce une bonne solution?

Sinon, autre cas, est-il plus judicieux de mettre par exemple le jour (par exemple 070726) dans une autre colonne de ma table sessions avec un index dessus, et de chercher d'abord parmi les sessions du jour, puis, s'il ne trouve rien, parmi les autres jours?

J'ai également pensé au partitionnement des tables mais je n'ai pas la bonne version de MySQL. Il semble qu'il faille avoir la version 5.1 qui n'est pas encore en version stable.

Pour finir, j'ai un autre problème du même ordre. En fait, pour toutes mes tables un peu grosses (+ 1 000 000 d'enregistrements - InnoDB), j'ai le même problème sur les requêtes de type ALTER TABLE, DELETE, UPDATE et sur l'optimisation des tables. A chaque fois, ça prend pas mal de temps (quelques minutes), et ça bloque tous les accès en lecture à ces tables.

Savez-vous dans quelle direction je dois chercher?

Merci!
 

SuperCed

Membre d’élite
Club MacG
20 Juin 2001
1 229
55
42
superced.rb38.eu
Non, ce sont des variables de sessions, pas des cookies. L'utilisateur ne doit pas pouvoir les modifier.

D'autre part, c'est stocké en base en non sur fichier car j'ai 3 frontaux Apache qui tapent tous sur la BDD.

En ce qui concerne les index, je fais beaucoup d'écriture sur cette table et j'avais peur que ça ralentisse les insertions.

Mais c'est peut être la bonne solution car il semble que InnoDB ne bloque que les lignes au lieu de la table complète si on met un index.

J'essaye donc et ça va peut-être fonctionner.
 

PA5CAL

Vétéran
Club MacG
21 Juillet 2005
9 228
597
Île-de-France
Il n'y a pas de secret. Un gestionnaire de base de donn&#233;es est bien pratique parce que c'est un composant tout-fait qui r&#232;gle &#224; moindre effort pas mal de probl&#232;mes de conception, de stockage et de traitement. Mais en contrepartie, il ne faut pas trop compter sur les performances, car la structure de tables et le traitement par des requ&#234;tes simplistes emp&#234;che de faire mieux.

Pour obtenir un fonctionnement optimis&#233;, il faut reprendre la conception et utiliser d'autres &#233;l&#233;ments fournis par le syst&#232;me d'exploitation. On arrive &#224; une toute autre architecture (CGI+fichiers sp&#233;cialis&#233;s), plus compliqu&#233;e, plus co&#251;teuse &#224; d&#233;velopper, plus sp&#233;cifique et moins mal&#233;able, mais au final beaucoup plus performante.
 

SuperCed

Membre d’élite
Club MacG
20 Juin 2001
1 229
55
42
superced.rb38.eu
innoDb est de type transactionnel... ceci explique peut-être cela.

J'ai pas compris ce que tu voulais dire. Bien sur que InnoDB gère les transactions. De plus, les requêtes de type SELECT ... FOR UPDATE, REPLACE, UPDATE et DELETE sont aussi gérées de manière transactionnelle.
Mais je ne vois pas l'explication...
 

tatouille

Vétéran
1 Juin 2004
5 174
494
Stanford CA
peux tu donner les specs de ton serveur ?
RAM CPU ecetera


tu peux optimiser avec un join sur la colonne à updater ? as tu essayé ?
mais a mon avis il ya une ******* coter serveur
car j'ai deja fait pire :)
 

SuperCed

Membre d’élite
Club MacG
20 Juin 2001
1 229
55
42
superced.rb38.eu
Xeon Quad Core 4 Go de RAM (64bits). Il n'y a que MySQL qui tourne dessus.

Il n'y a pas de jointure ici, c'est simplement un delete sur plusieurs lignes.

Mais j'ai trouvé la solution maintenant.

Le serveur a du mal car il est très solicité.
16 000 000 de pages vues par mois, soit environ 500 000 pages vues par jour, 20 000 toutes les heures, donc 6 pages vues en moyenne à la seconde.
Bien sur, la charge est inégale et les visites sont bien plus fortes à 19h qu'à 3 heures du matin.
 

SuperCed

Membre d’élite
Club MacG
20 Juin 2001
1 229
55
42
superced.rb38.eu
C'est n'est pas non plus la meilleure solution, car j'ai beaucoup d'écriture.
La méthode maitre esclave (par exemple), est très bonne quand il y a peu d'écriture, et beaucoup de lecture.
Ce n'est pas mon cas.

De plus, ça coute assez cher à mettre de place d'ajouter un serveur, et ça prend du temps.

Je préfère déjà optimiser ma config.