EMG and MySQL Print
Monday, 02 April 2012

Overview

EMG does not depend on a database for normal use, but a MySQL database can be used for certain operations.

The two most important possibilities when using a dabatase with EMG are:

  • Simple user administration and credits handling
  • Message log with status for each message

We recommend MySQL 5.0 or later for use with EMG.

Configuring MySQL for EMG use

It is STRONGLY recommended to use table type InnoDB. The default MyISAM table type does not support transactions and will use table locking for different operations, which is not suitable for a production-use environment. For example it is possible to perform a homogenous database dump on a running database using "mysqldump --single-transaction" when using InnoDB tables. This is not possible when using MyISAM files.

Also EMG 5.3 and earlier uses older versions of the MySQL client libraries. This require the use of "old_passwords=1" in my.cnf. Please note that this setting needs to be in place before creating the EMG database user in order for the password to be stored in a form that emgd can understand.

EMG 5.4 and later is not compatible with "old_passwords=1".

In the "mysqld" section of my.cnf (usually /etc/my.cnf), these are relevant settings when using InnoDB:

default-table-type = INNODB
innodb_file_per_table
innodb_data_file_path = ibdata1:10M:autoextend:max:2000M
set-variable = innodb_buffer_pool_size=200M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=100M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2

In order to detect performance problems it can be of interest to enable the MySQL "slow log". This will log queries that takes more than a specified number of seconds to complete (10 seconds in the sample en try below. Once again settings should be added to the "mysqld" section of my.cnf.

log-slow-queries=/var/log/mysql/slow.log
long_query_time=10

We recommend you to read MySQL documentation to understand parameter impact and to find exact values for the configuration parameters above for your system.

Changes in my.cnf require a restart of the MySQL server.

Setting up schema

Run createemgdb-mysql.sh which is supplied with the EMG distribution to create the EMG database user and the EMG schema.

More detailed information about this in the manual.

Maintaining the database

It is recommended to trim old records from database tables on a regular basis. This can be performed via a cron script.

For example to remove entries in "messagebody" table for messages older than 1 year the following SQL statement can be used:

DELETE FROM mb USING messagebody mb LEFT JOIN routelog rl ON rl.msgid = mb.id
WHERE rl.starttime < DATE_SUB(NOW(), INTERVAL 1 YEAR);

Also the table "messageoption" most often contains information that is not of interest, so it can be truncated (emptied) on a regular basis:

TRUNCATE messageoption;

Converting from MyISAM to InnoDB

If an EMG database using MyISAM tables already exists, the steps below outline the way to migrate it to InnoDB.

In order to determine if the tables are MyISAM tables check the table file suffix for the database files (usually in /var/lib/mysql/emg). If the suffix is ".MYD" and ".MYI" the tables are MyISAM tables. InnoDB table files has the suffix ".ibd"

Steps for MyISAM to InnoDB migration

  1. Stop emgd
  2. Update MySQL configuration in /etc/my.cnf with InnoDB options (mentioned above)
  3. Restart mysqld (usually /etc/init.d/mysqld restart)
  4. For each table run "ALTER TABLE xxxx ENGINE='InnoDB';" where "xxxx" is replaced with the table name (for example "emguser")
  5. Verify that table files now has the ".ibd" suffix (there should be no ".MYI" och ".MYD" files left)
  6. Run "emgd -verify"
  7. Start emgd