mySQL default config and very large Koha database

A technical blog entry, that could be usefull for many people,

We have discovered that the default mySQL configuration stores all the innoDB tables of all mySQL databases in a single Linux file (/var/lib/mysql/ibdata1 on a debian)
When you have a large database (or many databases), this file becomes huge. Really.

You must also be aware that innoDB engine, when you delete something frees the space in innoDB engine, but no in the ibdata1 file. So the ibdata1 file is always growing.

If your koha database is large, or very large, or you have more than one, you may face random big lowering of performances. Not sure about who is responsible : Linux having problems to deal with large ibdata1 or mySQL.

Here is the solution: http://dev.mysql.com/doc/refman/5.0/en/innodb-data-log-reconfiguration.html, the interesting part or this page being:

  • 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.

That will solve the problem, but only until it appears again later.

So, here are our two other suggestions :

  • don’t use only one file to store everything = http://dev.mysql.com/doc/refman/5.1/en/innodb-multiple-tablespaces.html. Here is our mySQL config file :

    # 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
  • Move the koha session table to myISAM : the koha session table is used only for authentication. It can grow a lot (as a new session is created for each anonymous OPAC access !). innoDB is great to deal with constraint (ie : an item can’t exist if the biblio don’t). BUT : the session table has no constraint at all. So it’s perfectly safe to move it to myISAM. Add a truncate every week to keep it low, and you get the good combination to have an optimized Koha/mySQL. Here is a script that moves some tables to 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";
    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});
    }
    }
    }

      Those changes worked really well on our hosting platform, so we thought it was worth sharing it.

 

Share

About Paul Poulain

Paul has been involved in free software projects since 1998. Since 2001, he has worked to raise awareness of free software in the library world. A contributor to Koha's developments since the end of 2001, Paul became involved full-time in this project in 2002. After 5 years as an independent, he created BibLibre and led its development. Paul is and remains above all a librist at heart, always motivated to share.

Leave a Reply

Your email address will not be published. Required fields are marked *