Tips and tricks for Qloaderd

Checking how much data is in the database on a daily basis

If you want a breakdown by day of the contents of the each partition, you can run the following query:

SELECT partition,
  FROM_UNIXTIME(time_id, '%Y%m%d' ) as date,
       count(*) as n_rows
  FROM queue_log
 GROUP BY partition, FROM_UNIXTIME( time_id, '%Y%m%d' )
 ORDER BY partition, FROM_UNIXTIME( time_id, '%Y%m%d' );

The result will look something like:

 +-----------+----------+--------+
 | partition | date     | n_rows |
 +-----------+----------+--------+
 | P01       | 20070329 |   4216 |
 | P01       | 20070411 |      5 |
 | P01       | 20070412 |      3 |
 | rt        | 20070508 |   9365 |
 | rt        | 20070509 |  13248 |
 | rt        | 20070510 |   3883 |
 +-----------+----------+--------+
 6 rows in set (0.45 sec)

And will tell you how many rows were uploaded per partition, per day.

Optimizing queue_log access time

If you would like QueueMetrics to access the database faster, you can run the following query to reorganize data on disk:

ALTER TABLE `queue_log`
ORDER BY partition, time_id, unique_row_count

This query might take a very long time to complete if the database is big and will lock the table until completion, so it should be run in a moment when the system is idle (e.g. at night via a cron job).