Configuration mySQL par défaut et grosses base Koha

Une fois n’est pas coutume, cette entrée est technique, elle pourrait être utile pour beaucoup de gens,

Nous avons découvert que la configuration par défaut de mySQL stocke toutes les tables innoDB de toutes les bases mySQL dans un seul fichier linux (/var/lib/mysql/ibdata1 sur debian)
Lorsque vous avez une grosse base de données (ou plusieurs), ce fichier devient énorme. Vraiment énorme !

Ce que vous devez également savoir, c’est que le moteur innoDB, lorsque vous supprimez une ligne (ou une colonne ou une table), libère en interne l’espace, mais ne libère rien sur le fichier ibdata1. Donc il ne fait que grossir.

Si votre base de données est importante, ou que vous en avez plusieurs, vous pouvez rencontrer d’énormes chutes de performances de manière aléatoire. Est-ce Linux ou mySQL le responsable, nous n’en savons rien. Mais nous avons trouvé comment résoudre ce problème !

La solution est dans la documentation de mySQL http://dev.mysql.com/doc/refman/5.0/en/innodb-data-log-reconfiguration.html, la partie la plus intéressante de cete page étant :

  • Use mysqldump to dump all your InnoDB tables.
  • Stop the server.
  • Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
  • Remove any .frm files for InnoDB tables.
  • Configure a new tablespace.
  • Restart the server.
  • Import the dump files.

Cela résoud le problème, mais il va réapparaître plus tard.

Voici donc nos deux suggestions complémentaires :

  • Ne pas utiliser un seul fichier pour tout stocker. La page http://dev.mysql.com/doc/refman/5.1/en/innodb-multiple-tablespaces.html explique comment faire. Voici notre fichier de configuration mySQL complet :

    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    [mysqld]
    #
    # * Basic Settings
    #
    #
    # * Fine Tuning
    #
    key_buffer = 2000M
    max_allowed_packet = 16M
    thread_stack = 128K
    thread_cache_size = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover = BACKUP
    max_connections = 500
    max_user_connections = 500
    #table_cache = 64
    thread_concurrency = 300
    table_cache = 30000
    open_files_limit = 65000
    #
    # * Query Cache Configuration
    #
    query_cache_limit = 50M
    query_cache_size = 300M
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    # You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
    #skip-innodb
    innodb_buffer_pool_size=16777216
    innodb_file_per_table
  • Modifier certaines tables de Koha pour qu’elles soient en myISAM : la table des sessions par exemple, est utilisée uniquement pour l’authentification. Elle grossig beaucoup (une nouvelle session est créée pour chaque utilisateur anonyme de l’OPAC !). innoDB présente l’avantage de gérer les contraintes d’intégrité (vous ne pouvez pas avoir un exemplaire sans sa notice biblio par exemple). MAIS : la table session n’a pas de contrainte d’intégrité ! Il est donc tout à fait possible de la mettre en myISAM ! Ajoutez un « truncate session » toutes les semaines pour la re-vider, et vous aurez une bonne combinaison optimisant Koha/mySQL. Voici notre script qui transforme quelques tables en myISAM :

    #!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;
    my $debug=$ENV{DEBUG};
    my $db_user=$ARGV[0] || "koha";
    my $db_passwd=$ARGV[1] || "your password here";
    my $db=DBI->connect(« DBI:mysql:mysql », $db_user, $db_passwd);
    my $dbquery=$db->prepare(qq{show databases});
    $dbquery->execute;
    while (my $dbname=$dbquery->fetchrow){
    next unless $dbname=~/^koha/;
    $debug && warn $dbname;
    $db->do(« use $dbname »);
    my $tablequery=$db->prepare(qq{show tables});
    $tablequery->execute;
    while (my $tablename=$tablequery->fetchrow){
    $debug && warn  » $tablename »;
    if ($tablename=~/sessions|zebraqueue|temp_upg_biblioitems|pending_offline_operations/)
    {
    $db->do(qq{ALTER TABLE $tablename engine=myisam});
    }
    else {
    $db->do(qq{ALTER TABLE $tablename engine=innodb});
    }
    }
    }
    Ces changements ont bien fonctionné sur notre plateforme d’hébergement, nous avons pensé qu’il était intéressant de les partager.

 

Share

A propos Paul Poulain

Ingénieur en Informatique, Paul a travaillé pour l’assurance maladie et, à la fin des années 1990, dans une start-up du secteur de la vente en ligne. Il s’implique dans des projets de logiciels libres depuis 1998. Depuis 2001, Paul œuvre à faire connaître les logiciels libres dans le monde des bibliothèques. Contributeur aux développements de Koha depuis fin 2001, Paul s’est impliqué à temps plein dans ce projet dès 2002. Il en est l’un des plus importants développeurs, ayant en particulier mis en œuvre la gestion des formats MARC. Il a été « Release Manager » pour les version 2.0, 2.2 (de 2003 à 2005), 3.8 et 3.10 (entre novembre 2011 et novembre 2012) Tel : +33-(0)491813508

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *