Wednesday, September 29, 2004

Enabling InnoDB for SpamAssassin Bayes database

Here are the steps that I used to upgrade the SpamAssassin mysql database from MyISAM to InnoDB.

Since mysql in Fedora Core 1 comes with version 3.23.58 of mysql wirth InnoDB support configured, all that was needed was to enable it in the configuration file. Using the instructions I found on this page, I added the following lines to the [mysqld] section of my my.cnf file:

# You can write your other MySQL server options here
# ...
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

  1. Stop Amavisd - "/etc/init.d/amavisd stop"
  2. Flush the bayes database - "sa-learn --sync"
  3. Backup the database - "sa-learn --backup > backup.txt"
  4. For each of the tables, run the sql commands:
    ALTER TABLE <table name> TYPE=InnoDB;
    ANALYZE TABLE <table name>;
  5. Start Amavisd - "/etc/init.d/amavisd start"