Offlining a part of the queue_log table

You may want to put offline a part of the 'queue_log' table in order to reduce disk consumption on the server. This should not give you a large performace gain - as all accesses to the table are index-based, performance will be stable even with very large tables - but will make backups easier and quicker.

Prerequisites

  • A working QueueMetrics instance with MySQL storage

Make sure you have enough disk space to carry out these operations - one of the few things MySQL does not like is finishing up disk space on a live system. So you should have at least enough space to make a full copy of the queue_log table, with indexes and all.
The following procedure is to be run when the system is idle, as it uses a lot of I/O for large tables and may lock tables. On a very large table each query might take tens of minutes to run, so beware.

You may keep on having qloaderd uploading data to your normal table, but it would be better not to have QM running queries on it.

you need to make a complete database backup before attempting this.

Moving data to a temporary table

First we create a "backup" table called 'queue_log_old' to hold data we don’t need anymore.

CREATE TABLE queue_log_old LIKE queue_log

Decide a timestamp in the past that will be your "cutoff point" - in our case it is 1326826989, that stands for "Jan 17 2012 @ 7:03:09pm UTC".

There are a number of services that will do the conversion for you. For example, see http://www.unixtimestamp.com/index.php

You need also to know the partition you want to move.

INSERT INTO queue_log_old
  SELECT *
    FROM queue_log
   WHERE partition = 'P001'
     AND time_id < 1326826989

And then delete it from the queue_log table:

DELETE FROM queue_log
      WHERE partition = 'P001'
        AND time_id < 1326826989

And optimize the queue_log table so space is claimed back:

OPTIMIZE TABLE queue_log

at this point, you might run a backup of the new "queue_log_old" table, save its contents to disk and delete it.

Backing up the temporary table

You may use the 'mysqldump' tool to create a backup of the table:

$> mysqldump queuemetrics queue_log_old > queue_log_old.sql
$> bzip2 queue_log_old.sql

You may then drop the table to have it removed.

DROP TABLE queue_log_old

Restoring data

In case you need to put data back on the main table (e.g. because you want to be able to access it again through QM):

INSERT INTO queue_log
     SELECT *
       FROM queue_log_old