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).