Améliorer les performances de MySQL en partionnant les tables

Base de données mysql optimiséeDans son document : “Scaling MySQL writes through Partitioning“, Philip Tellis nous explique comment il a réussi à augmenter très significativement le débit en écriture sur une base MySQL.

Le constat est assez basique, et le problème très répandu. Il s’agit d’optimiser les écritures sur une table qui augmente très vite. La premiere solution consiste à multiplier les machines et répartir les données sur plusieurs serveurs. Mais avant d’utiliser cette stratégie vous pouvez aussi optimiser votre serveur.

L’astuce est assez simple, elle est même désormais intégrée dans la dernière version de MySQL. Il s’agit de partitionner les données dans plusieurs petites tables. Ainsi au lieu d’avoir un énorme fichier, contenant des index difficiles à maintenir, vous optenez des petits fichiers, avec des index simples et des accès très rapides.

L’avantage c’est que cette technique vous permet de garder un débit en écriture constant, quelque soit le nombre d’éléments enregistrés. Les slides de Philip le montrent bien, et celui-ci illustre par exemple la chute de performance au delà d’un certain seuil (ici 10 M d’enregistrements, insérés à un rythme de 1200 inserts par seconde) :

performance Mysql

Dans son implémentation Philip Tellis a partionné les données par tranche horaire. Ce découpage demande un travail particulier pour consolider ou compter les données mais le problème etait ici d’enregistrer les requêtes qui arrivent massivement et en temps réel.

J’ai déjà utilisé ce type de technique, c’est simple et très efficace. Ce n’est pas très compliqué à mettre en place et le résultat et rapidement visible. Vous pouvez même mettre en place un partionnement sur une architecture existante avec un script ad’hoc (ie : insert/truncate, contacter moi si besoin).

Voilà, j’espère que ce petit mémo pourra vous servir. Ce sont parfois les choses les plus simples qui sont les plus efficaces, d’autant qu’optimisé de cette manière, votre cluster de 10 mysql n’en sera que plus efficace. Alors avant de multiplier les serveurs, vérifier bien que votre architecture est optimale.

Sources et informations :

Sur le même thème :

5 Responses to “Améliorer les performances de MySQL en partionnant les tables”

  1. spf says:

    Très intéressant, mais reste ensuite l’utilisation de telles données.

    Ex : utilisation d’un système de ce genre pour suivre toutes les consultations des fiches produits de votre site de e-commerce (n° du produit, ip du client, date par ex)
    Si on partitionne par heure on finit avec un paquet colossal de tables.
    Au moment de faire des stats dessus (pour sortir un top des produits les plus consultés, croiser quels produits sont consultés avec quels autres… ça va être coton non ?

  2. Marc says:

    Oui effectivement, mais dans tous les cas nous obtenons des quantités de données importantes. L’avantage c’est qu’avec cette méthode le volume n’impacte pas les performances de votre système lors des enregistrements.

    Concernant les stats, c’est un peu plus complexe, mais en général la consolidation est également plus rapide avec ce type de méthode. Dans votre exemple il suffit de comptabiliser les tops par heure, puis par jour, et enfin par mois. Le split permet même de paralléliser les calculs.

    Je vous conseille de mettre en place des scripts automatiques qui vous permettrons de consolider ces stats toutes les heures. Ce calcul sera très rapide, et beaucoup moins couteux que si la consolidation devait se faire sur des tables de plusieurs dizaines de millions de lignes.

    Ainsi l’enregistrement est rapide et stable, la consolidation à jour toutes les heures et les tables peuvent être archivées régulièrement si ces données n’ont pas besoin d’être en ligne en permanence.

  3. Olivier B. says:

    A noter que si on utilise les fonctions de partionnement internes à MySQL, ceci devient transparent sur les insert/update/delete/select, aucune modification n’est donc nécessaire et on ne se retrouve pas avec des centaines de tables supplémentaires à gérer.

    Un simple “alter table” de la sorte a par exemple fait des merveilles sur des tables “volumineuses” (20Go) telles que celles générées par Zabbix, sans avoir à modifier l’outil.

  4. Marc says:

    Oui merci Olivier, mais certaines architectures fonctionnent avec des versions de MySQL qui ne possèdent pas cette option. D’autre part, sur des volumes très importants le partitionnement manuel peut être plus adapté que celui de MySQL.

    Cependant, pour une architecture simple possédant un MySql récent, cette option est effectivement idéale !

    Merci,
    Marc.

    Info :http://dev.mysql.com/doc/refman/5.1/en/partitioning-management.html

  5. Bruno C. says:

    Il est a noté aussi que le système de partitionnement n’est pas forcement nécessaire.
    Si MySQL est bien configurer pour de forte charge en écriture : moteur MyISAM, ROW FORMAT FIXED et optimisé ainsi que l’option magique : concurrent_insert = 2

    dans ce cas précis et a condition que les données soit sur le disque (ou volume raid) le partionning n’apporte rien ;)

Leave a Reply