Moving the queue_log table to InnoDB

'Thanks to Jens von Bulow'

Most QueueMetrics tables use a storage engine called myISAM - this was the default storage engine, and works well on tables that are written infrequently and read often. If you run a clustered call-center with multiple partitions and many rows being inserted per second, you may see database response times degrade. In this case, moving the storage engine to InnoDB, a storage engine that is way better for contended tables, may make a difference.

Prerequisites

  • A working QueueMetrics instance

  • MySQL server version 5

Before you consider doing this, make sure that you have read the current MySQL documentation on "Converting tables to MySQL" - http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html - your server settings may require some tweaking to get good performance out of the new table.

Changes to MySQL

In order to convert the table, we will:

  • Make sure you have enough space on disk (at least 2x the size of your current queue_log table). Make sure you have a complete backup of the old DB (in case something goes wrong) and you have a suitable maintenance window so you can work comfortably.

  • Create a new table called "queue_log_i" where we will load old data. We will create it with the following definition - make sure that the set of fields exactly matches your current fields:

CREATE TABLE `queue_log_i` (
  `partition` varchar(20) NOT NULL DEFAULT '',
  `time_id` int(11) unsigned NOT NULL DEFAULT '0',
  `call_id` varchar(200) NOT NULL,
  `queue` varchar(50) NOT NULL,
  `agent` varchar(30) NOT NULL DEFAULT '',
  `verb` varchar(30) NOT NULL DEFAULT '',
  `data1` varchar(200) NOT NULL DEFAULT '',
  `data2` varchar(200) NOT NULL DEFAULT '',
  `data3` varchar(200) NOT NULL DEFAULT '',
  `data4` varchar(200) NOT NULL DEFAULT '',
  `data5` varchar(200) NOT NULL DEFAULT '',
  `serverid` varchar(10) NOT NULL DEFAULT '',
  `unique_row_count` int(10) unsigned NOT NULL ,
  KEY `idx_sel` (`partition`,`time_id`,`queue`(2)),
  KEY `partizione_b` (`partition`,`time_id`,`unique_row_count`),
  KEY `by_hotdesk` (`verb`,`time_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

  • Copy data from the old table to the new one. We will insert it in the correct order to make reading quicker.

INSERT INTO queue_log_i
 SELECT *
   FROM queue_log
  ORDER BY `partition` ASC, `time_id` ASC, `unique_row_count` ASC

  • Stop qloaderd

  • Rename the new table to "queue_log" and the previous one to "queue_log_old". This can be done atomically as in:

RENAME TABLE queue_log TO queue_log_old,
             queue_log_i TO queue_log

  • Restart QueueueMetrics

  • Restart qloaderd

  • Optionally - delete the old queue_log table.

Bonus: checking your InnoDB execution plan

If you are unsure whether your new table is working correctly, you can:

  • Log slow queries from QueueMetrics

  • Run the following commands:

SET optimizer_trace="enabled=on";

SELECT ..... (your slow query);

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

At this point, you can find the detailed execution plan in a field called TRACE. This is especially useful to diagnose why an index may not be used.

Changes to QueueMetrics

None required.