MySQL with EMG - Nordic Messaging

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 (or better). 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.

The following SQL statement can be used to remove entries in “messagebody” table for messages older than 1 year. The configuration option DISABLE_MESSAGEBODY can be used to prevent this table from being written to.

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. By adding DISABLE_MESSAGEOPTION to the top part of the server.cfg file, these entries are never created.

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

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