[ themattreid ]

I’ve been messing around with the Kontrollbase schema for the last couple of days, writing various queries for the daily reporting scripts that will eventually be an automated pdf report. I’ll give you examples of two of the queries, the first being overall environment stats, and the second being single-host growth over time.

Overall environment stats
select ((((MAX(os_mem_used)) / 1024 ) / 1024) / 1024) max_os_mem_used, ((((MIN(os_mem_used)) / 1024 ) / 1024) / 1024) min_os_mem_used, ((((AVG(os_mem_used)) / 1024 ) / 1024) / 1024) avg_os_mem_used, ((((STDDEV_POP(os_mem_used)) / 1024 ) / 1024) / 1024) stdev_os_mem_used, ((((MAX(length_data + length_index)) / 1024 ) / 1024) / 1024) max_size, ((((MIN(length_data + length_index)) / 1024 ) / 1024) / 1024) min_size, ((((AVG(length_data + length_index)) / 1024 ) / 1024) / 1024) avg_size, ((((STDDEV_POP(length_data + length_index)) / 1024 ) / 1024) / 1024) stdev_size, MAX(num_connections) max_connections, MIN(num_connections) min_connections, AVG(num_connections) avg_connections, STDDEV_POP(num_connections) stdev_connections, MAX(queries_per_second) max_qps, MIN(queries_per_second) min_qps, AVG(queries_per_second) avg_qps, STDDEV_POP(queries_per_second) stdev_qps from server_statistics;
*************************** 1. row ***************************
max_os_mem_used: 50.743488311768
min_os_mem_used: 0.023044586182
avg_os_mem_used: 1.5759627057922952
stdev_os_mem_used: 2.4064208226596184
max_size: 283.815660957247
min_size: 0.000492287800
avg_size: 10.8213909777686940
stdev_size: 39.9443980717105447
max_connections: 435
min_connections: 0
avg_connections: 16.9734
stdev_connections: 37.3269
stdev_os_mem_used: 2.4064208226596184
max_qps: 9243.6533203125
min_qps: 0.00011409764556447
avg_qps: 216.421064774444
stdev_qps: 1071.72792986232
1 row in set (0.00 sec)

single-host growth over time
mysql> select (select (((length_data + length_index) / 1024) / 1024) as curr_size from server_statistics where server_list_id='44' and CURDATE() <= Creation_time order by Creation_time asc limit 1) as 0_day_size_mb, (select (((length_data + length_index) / 1024) / 1024) as curr_size from server_statistics where server_list_id='44' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= Creation_time order by Creation_time asc limit 1) as 30_day_size_mb, ( (select (((length_data + length_index) / 1024) / 1024) as curr_size from server_statistics where server_list_id='44' and CURDATE() <= Creation_time order by Creation_time asc limit 1) - (select (((length_data + length_index) / 1024) / 1024) as curr_size from server_statistics where server_list_id='44' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= Creation_time order by Creation_time asc limit 1)) as difference, ( select (select ((select (((length_data + length_index) / 1024) / 1024) as curr_size from server_statistics where server_list_id='44' and CURDATE() <= Creation_time order by Creation_time asc limit 1) - (select (((length_data + length_index) / 1024) / 1024) as curr_size from server_statistics where server_list_id='44' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= Creation_time order by Creation_time asc limit 1)) as difference) / (select(select (((length_data + length_index) / 1024) / 1024) as curr_size from server_statistics where server_list_id='44' and CURDATE() <= Creation_time order by Creation_time asc limit 1) as 0_day_size_mb ) * 100) as percent_growth;
+---------------+----------------+--------------+----------------+
| 0_day_size_mb | 30_day_size_mb | difference | percent_growth |
+---------------+----------------+--------------+----------------+
| 9100.43986130 | 8199.39263916 | 901.04722214 | 9.901139240197 |
+---------------+----------------+--------------+----------------+
1 row in set (0.01 sec)