Optimiser sa base de données: dupliquer intelligeamment l'information.

Compter prend du temps, de l'argent et de l'énergie.

Nous avons vu dans le chapitre précédent que l'information prend du volume et que le volume d'information diminuait les performances de mysql. Pour autant, toute règle a une exception.

Le format de table le plus utilisé (et le plus recommandé) de mysql est le format innoDB. Sans rentrer dans les détails techniques, ce format est utilisé pour deux raisons:

• Lors d'une écriture, il verrouille uniquement (et temporairement) les enregistrements dont il a besoin. Myisam, par exemple, verouille des tables entière.
• Il est un un des seuls formats sérieux à pouvoir gérer les transactions.

Pour autant, innoDB à des désavantages. Le principal étant de mettre du temps compter ou d'obtenir des données agrégées.
Par exemple, si je veux pouvoir afficher la liste de mes billets et le nombre de votes, je serais obligé de faire 2 requêtes et d'assembler ensuite les résultats dans mon script:

SELECT * from billets where id_utilisateur='3128';

Temps d'exécution: 0.01 secondes.

Select id_billet, count(id_vote) as nbVotes from votes where id_utilisateur='3128' group by id_billet use index index_id_utilisateur;

 Temps d'exécution: 0.3 secondes.
Autrement dit, j'ai multiplié mon temps de recherche d'information par 30, sans compter le temps que le script mettra à dé-multiplexer l'information.

De là, j'ai deux solutions: soit ma base est une base d'écriture (type boursorama) et cela ne me dérange pas plus que cela, soit j'ai une base de lecture et c'est un problème.
La solution simple serait peut être tout simplement de ne pas afficher les votes, de ne pas classer par vote et de ne pas paginer par votes. Mais un système judicieux permet de passer ce problème sans trop de difficultés: il suffit de rajouter un champ "nbVotes" dans la table "billet".

Ainsi, en cherchant les billets, j'aurais tout de suite leur nombre de votes.
Ce système me fait gagner pas mal de performance, mais comporte deux désavantages:

• A chaque fois que je veut ajouter un vote, ceci me prend une requête d'ajout de vote ET une requête de mise a jour du nombre de votes de mon billet:

Update billets set nbVotes = nbVotes+1 where id_billet='215987';

•  De temps en temps (tous les mois ou toutes les semaines à 4 heures du matin) je devrais lancer un script mettant à jour ces données.

Ces actions sont très recommandées si votre projet prend de l'ampleur et du trafic. Prendre le double de temps en écriture pour gagner 30 fois plus de temps à chaque lecture me parait un bon calcul pour se permettre ces contraintes.

De même, imaginez que non seulement je veuilles afficher le nombre de votes, mais aussi la dernière date où mon billet à été modifié (ajout d'un vote, ajout d'un message ou message re-publié), cela donnerais:

Select max(date_changement) as derniere_date_changement
from (
   (select date_publication as date_changement from billets where id_billet='215987')
   union
   (select max(date) as date_changement from votes where id_billet='215987')
   union
   (select max(date) as date_changement from commentaires where id_billet='215987')
) as b;

 Imaginez la performance de mysql sur ce genre de requête !

Cela serait bien plus simple et rapide si l'information était déjà "digérée" et présente à l'intérieur de mon enregistrement "billet" sous le champ "derniere_date_changement".

Conclusion:
Prendre un peu de volume et perdre du temps sur une écriture n'est jamais un mauvais calcul si cela fait gagner du temps sur des milliers de lectures. Aussi la duplication d'informations pour faciliter la récupération des données agrégées est en soi un très bon principe.

Capacité de réduction du temps de réponse: au moins 90% sur les requêtes retournant des données agrégées.



Optimiser sa base de données: Choisir ses types de données

Dans le billet précédent, nous avons laissé à mysql uniquement les champs de recherche (ids, date) et les champs qui vont être affiché dans une liste.

Le choix des champs peut accélerer la recherche dans mysql.

Par exemple, savez vous que si vous mettez tous vos champs à "NOT NULL", mysql passera par une branche de recherche plus rapide qui, justement, ne testera pas les "NULL"s ?

Ensuite, viens le choix du type des ids. En effet, mysql ira plus vite à rechercher un TINYINT qu'un DOUBLE. Rappelez vous: les performance lors de la recherche d'un enregistrement (même sur un index) dépend du volume de données. Ainsi, si vous avez des millions d'enregistrements dans une table (et donc des millions d'ids dans l'index),  si nous ne pouvons pas jouer sur le nombre d'enregistrement, nous pouvons jouer sur la taille de l'ids.

Ainsi les types de clés numériques conseillées sont:

• UNSIGNED TINYINT: 0-255. Ce type est conseillé sur des tables fixes (sur une table contenant "Monsieur", "Madame" ou "Mademoiselle", par exemple). Nous plus bas comment ne plus avoir de jointures avec ce genre de tables.

• UNSIGNED SMALLINT: 0-65535. Peut définir par exemple le nombre de billets dans un blog, ou le nombre de sous-parties d'un forum. Ce type n'est que peu utilisé.

• UNSIGNED MEDIUMINT: 0-16.7 millions. Le type le plus utilisé. Il peut correspondre aux ids de questions sur un forum, aux ids de blogs, les utilisateurs, commentaires, etc. Quand les projets prennent de l'ampleur, ce type peut être augmenté à l'aide d'un "alter table" au besoin. C'est le type à verifier/changer quand votre projet dépasse le stade de "proof of concept".

• UNSIGNED INT: 0-4.2milliards. Ce type est utilisé sur des données faciles à entrer et presque jamais recherchées (typiquement: les votes sur blog).

• UNSIGNED BIGINT: 0-18446744073709551615. Juste pour info: ils y'a moins de grains de sable sur terre que ce chiffre là. Mettez vous en plein desert et repensez-y.

• Double: Type imprécis après une certaine valeur, inutile dans une primary key. 

Les types ENUM et SET:
Les types ENUM et SET permettent d'apporter un choix de valeur dans une colonne. SET peut stocker plusieurs valeurs (je suis a la fois "Monsieur", "Madame" et "Mademoiselle"), ENUM ne peut en stocker une seule.
Dans votre enregistrement, c'est en interne un smallint que vous stockerez, mysql fera la traduction. Les ENUM et les SET vous eviterons de creer des tables inutiles, et surtout, des jointures inutiles !

Les charactères:
Dans votre base mysql, vous ne devez pas stocker de champs textes variables: ils seront en duplication dans votre base NOSQL. Si vous desirez mettre un abstract dans mysql (par exemple pour l'affichage d'une liste de profil sur meetic, avec le début de leur description), utilisez des champs de taille fixe comme char(180) ou char(255). Un char ne pourra pas dépasser la taille de 255 caractères.


Les dates:

• Year: un octet
• Date: 3 octets
• TIMESTAMP: 4 octets.
• DateTime: 8 octets

Utilisez donc les dates suivant vos besoins. Si vous faites uniquement des comparaisons de date, utilisez timestamp, mais si vous les affichez, utilisez date ou datetime.

Blob et text:
blob et text sont des données à taille variable. Non seulement il vont vous pourrir vos performances, mais surtout: il n'ont rien a foutre là (mysql n'est pas un lieu de stockage de données mais un moteur de recherche !). Text ira donc dans votre noSQL, et le blob ira dans votre serveur de média.

Conclusion:

En choisissant le type de ses champs et en les mettant tous en "NOT NULL", mysql augmente ses temps de recherche (environ 30%). Cela ne se verra pas forcement dans une requête unitaire, mais votre serveur tiendra mieux la charge en période de stress.

Optimiser sa base de données: diviser ses données (pour mieux regner).

Comme nous l'avons vu dans l'article précédent, plus vos enregistrements contiennent de données, moins votre serveur de base de données sera efficace dans sa recherche (le temps d'ouverture d'un enregistrement dépend du volume de données à lire).

Aussi quel est le but de mysql ? Pour quel usage l'utilisez-vous ? En quoi est-il le plus efficace ?
La grande erreur des sites d'aujourd'hui est d'utiliser une base mysql comme l'endroit de stockage de toutes les données du projet: une poubelle à données.
Or, autant mysql est efficace dans la recherche de petits enregistrements, autant il est totalement inefficace en tant que poubelle à données. La bonne pratique est d'utiliser mysql uniquement comme moteur de recherche, et d'y laisser uniquement les données de recherche, ainsi que les données a afficher dans des listes (les previews).

5 type de données existent dans un site:

• Des données de recherche (les ids, date, etc...)
• Les données d'affichages (previews dans des listes et données complètes). Par exemple, la description d'un membre ou d'un produit: Le preview sera un abstract de taille fixe (char180 ou char255), et la donnée sera de taille variable (text).
• Les données "froides": les données qui ne seront jamais affichées ou utilisées (à part peut-être dans l'interface d'admin) mais qu'on garde pour des raisons légales ou des raisons système. Exemple: l'ip ayant servi à la création d'un compte, la date de cloture d'un compte, etc...
• Les fichiers utilisateurs (La photo a afficher sur son profil, son cv, etc...)
• Les fichiers "statiques" (liés au site et non aux données): les fichiers javascript, css, les images du thème du site, etc...

Seules les données de recherche et d'affichage-preview concernent mysql.
Nous utiliserons en parallèle de mysql une base de données non-relationelle (par exemple noSQL) et un serveur de média.

Le but est de laisser dans mysql uniquement les données "recherche" et "liste", en mettant la base de données non-relationelle en "poubelle à données". La base NoSQL sera garante de toutes les données: mysql pourra etre vidé a chaque instant et re-rempli à partir des données de noSQL.

Le serveur de média, quand a lui, hebergera tous les fichiers binaires. Par exemple, la photo d'un produits sera sur le serveur de media, qui vous retournera un identifiant d'une dixaine-vingtaine de lettres. Cette identifiant sera bien plus facile a stocker dans mysql et dans NoSQL. Il stockera aussi les fichiers statiques. Ces fichiers seront directement accessibles via une url qui mènera au serveur de média.

La puissance de NoSQL réside dans le fait qu'il n'aura a s'occuper que d'un enregistrement à la fois, en lecture comme en écriture. NoSQL est très rapide pour trouver un enregistrement à partir de son id, toute autre recherche étant proscrite. Il pourra en revanche retourner ou modifier les données de cet enregistrement. Bien que NoSQL mette lui aussi beaucoup de temps à ouvrir ou modifier un gros enregistrement, cela sera négligeable car il n'aura à traiter qu'un enregistrement par requête, tandis que mysql sera appelé pour retourner les données d'une recherche ou d'une liste paginée.

Sur mysql, si l'on suit la bonne pratique, chaque table de liaison aura de 3 à 5 champs, et chaque table "objet" (table d'utilisateur, table de produits, etc...) devra avoir une dixaine-quinzaine de champs maximum.

Le désavantage du système de division de données est que pour chaque modification de données, il vous faudra requêter 2 bases (mysql + nosql), voir 3 avec le serveur de medias.
Néanmoins, NoSQL sera requêté lors de l'affichage d'un seul enregistrement (profil, produit) et le serveur de média sera requêté en dehors du chargement de la page (la page construit l'url de l'image, les image sont chargées par la suite).
Diviser les données sur plusieurs serveurs est donc particulièrement efficace dans un site de lecture (les chateaux de la loire, les blogs, rue du commerce, amazon, forums) avec au final bien plus de lecture que d'écriture, et proscrit dans les sites à fort taux d'écriture (boursorama, etc...).


Conclusion:
Diviser les données permet à mysql de passer de plusieurs centaines de gigas de données à quelques centaines de méga au plus. Sa rapidité en est ainsi augmenté d'environ 20% lors d'une recherche.
Mysql étant réduit à un outil de recherche, la modification des données devra se faire sur mysql et noSQL, augmentant le temps d'écriture.
Ceci dit, la base de données non-relationnelle, ainsi que le serveur de média, pourront, eux, être mutualisés avec d'autres projets sans trop de perte de perf. Si noSQL commence à ramer, changez le disque dur par un disque dur électronique SSD et mettez-le tout seul sur un NAS.

Optimiser sa base de données: utiliser les index.

La première chose à faire après avoir conçu son modèle de base de données est d'utiliser les indexs.
Toute base en environnement de production doit avoir tous ses indexs. 

Qu'est-ce qu'un index ? Prenons un exemple simple: Lorsque vous cherchez le numéro de téléphone de votre ami "Zorg", la première chose que vous faites est d'aller consulter les pages "Z" de votre répertoire. De la, si vous avez classé vos noms par ordre alphabétique, vous n'aurez aucun mal à trouver le numéro de téléphone de Zorg: 0666 !

Un index est un fichier d'enregistrement en plus de votre table de données. Il se compose d'enregistrement comprenant un ou plusieurs champs, de taille fixe (ici, une liste de noms, classés par ordre alphabétique) et les enregistrements associés dans votre table.

Ce qui fait perdre le plus de temps à un serveur de base de données (ici mysql) est d'ouvrir les enregistrements un par un, de chercher la concordance et de passer à l'enregistrement suivant. De plus, plus les enregistrements sont gros, plus ils metterons unitairement de temps a s'ouvrir.
Avec l'index, un tout petit fichier par rapport à votre table, mysql va commencer par le charger ce fichier en mémoire. Ensuite, à la manière de votre répertoire téléphonique, il va chercher les noms, et enfin, quand il aura la liste des enregistrements à ouvrir, il s'adressera à votre grosse table.

J'avais ainsi par le passé une table d'utilisateurs de 30000 personnes, chaquns ayant une 60 aine de colonne.
Executons la requête:

SELECT id FROM UTILISATEURS WHERE NOM='ZORG'

(oui, il y'a vraiment des noms de famille "ZORG" en france).

Temps de recherche: 3.68 secondes => innacceptable !

Mettons maintenant un index:

CREATE INDEX index_nom ON UTILISATEURS (nom);

Et recherchons de nouveau notre ami 'ZORG':

Temps de recherche: 0.00 secondes => bien mieux !

A savoir, les index peuvent porter aussi sur plusieurs colonnes. Imaginons que je cherche un nom sur une région, je peut mettre dans le même index le nom et la région:

CREATE INDEX index_nom_region ON UTILISATEURS (nom, region);

De plus, les index peuvent être crées pour introduire une contrainte. 

Par exemple, je ne veut pas dans ma table utilisateur deux fois le même numéro de sécurité sociale:

CREATE INDEX UNIQUE index_ss ON UTILISATEURS (numerosecu);
 

Avec cet index, non seulement la recherche par numéro de sécu sera bien plus rapide, mais chaque requête essayant d'ajouter un nouvel utilisateur avec le même numéro de sécu échouera.

Tous les champs qui peuvent être recherchés doivent être indexés, c'est la première chose à faire.
Certains champs typiques seront efficaces pour mysql à indexer:

• Les clés primaires de chaque table. En les désignant comme "PRIMARY KEY", elles seront indexées.
• Les clés étrangères: toutes ! Lors d'une jointure, mysql recherchera l'index.
• Les dates et autres datetime: Elles sont souvent un outil de recherche et sont faciles à indexer.
• Les int et autres decimal, numeric, si vous faites des recherches dessus.  Exemple: Il est utile d'indexer le nombre de votes d'un billet si l'on veut afficher les billets classés par vote.
• Les "tags" (champ texte désignant un mot, pouvant servir pour la recherche. Il est pertinent que les tags soient de type char.)
• Les ENUM et SET.

Certains autre ne sont, justement, pas efficace à indexer, et la recherche sur ce type de champ est déconseillée:

• Les varchar, text et autres chars

• Les blobs

Lors d'une recherche, mysql cherchera de lui-même le meilleur index possible. 

Parfois c'est évident, et parfois il se trompera. Vous pouvez lui indiquer quel index utiliser:

SELECT * FROM UTILISATEURS WHERE NOM='ZORG' AND REGION='75' USE INDEX index_nom_region

Dans le cas d'un 'OR', mysql peut aussi avoir du mal a choisir ses indexs, et parfois faire deux requettes avec  un union peut augmenter la rapidité de la requette.

SELECT * FROM UTILISATEURS WHERE REGION='75' AND DATE_CREATION > '01-01-2000';

 Temps: 0.04 secondes.

(SELECT * FROM UTILISATEURS 

WHERE REGION='75' USE INDEX index_region)

UNION

(SELECT * FROM UTILISATEURS
WHERE DATE_CREATION > '01-01-2000' USE INDEX index_dateCreation)

Temps: 0.01 secondes.

Conclusion:

Les index sont la première chose à penser pour améliorer l'optimisation 

d'une base. Il est plus important que tous les autres paramètres réunis.

Toute base en environnement de production, que ce soit en environnement 

b2b ou fort traffic, ne peut pas se passer d'index.

Capacité de réduction du temps de réponse d'une requête grâce aux 

indexs: entre 80% et 98%.

Dossier N°1 - optimiser sa base de données.

Au jour où j'écris ce message, beaucoup de mes collègues utilisent mal leur bases de données. C'est pourtant le point névralgique d'un site web à fort trafic.

Même si une base marche assez bien quand on la teste, elle peut se révéler complètement inefficace lorsqu'elle est mise en environnement à fort trafique.

On peut toujours "clusteriser" ses serveurs mysql pour en diviser le stress, il n'empêche que si une requête sans stress met du déja du temps à s'effectuer toute seule, c'est mal parti.
Dans cette suite de billets, nous étudierons les bonnes pratiques à avoir lorsqu'on commence une application et sa gestion de données.


Temps de réponse SQL = Temps unitaire * taux de charge * hardware.

Les différents articles qui suivrons analyserons ces 3 paramètres du temps de réponse des requêtes sql.

• Optimiser le temps unitaire d'une requête:
• Utiliser les indexs.
• Diviser ses données
• Choisir ses types de données.
• Dupliquer intelligemment l'information.
• Procédures stoquées.
• Taux de charge
• Cache applicatif.
• Cache HTML.
• Clusteriser son serveur mysql.
• Hardware
• Pas de mutualisation hardware.
• Processeur.
• Disque dur.
• Reseau.

Introduction

Simple developpeur à ergonomie variable dans un futur éloigné j'ai fuis ma vie et mon époque quand j'ai émis spontanément l'idée que google, au final, était le système le plus communiste qui ait marché dans un environnement capitaliste.

Il faut bien avouer qu'a mon époque, le système est trusté des empereurs issus d'entreprises multiplanétaires.

Etant bloqué dans les années 2000 pour un long moment (le choix de la date étant choisi complètement au hasard lorsque la foudre est tombée sur mon véhicule volant), j'ai remarqué que les personnes ici avaient beaucoup d'imaginations par rapport au niveau de leur technologie. Cet état de fait est diamétralement différent de mon époque, et j'ai donc décidé de lister les bonnes idées et pratique du milieu où je travaille.

Vous ne rêvez pas, je ne suis pas fou, mais il parait que chaque blog doit commencer par une introduction originale, c'est donc chose faite !
Toute référence au futur, et l'ironie qui en découle, est donc purement fortuite, même si ma soucoupe volante n'est pas toujours d'accord avec moi sur ce point là.

N'étant pas un assidu des publications, et devant faire face aux services de recherche de ma vie d'avant, ce blog sera complété de manière très irrégulière, veuillez m'en excuser.