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.