Configuration mySQL par défaut et grosses bases

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.

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.

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 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 le 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 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});
  }
  }
  }