This is not exactly a question, more of a survey. Here at BlackBean we have a time series aggregation and analysis platform built on top of MySQL, where each client have your own database, and each metric has it's own table, like this:
CREATE TABLE IF NOT EXISTS `ts_metric_x` ( time INTEGER(20) UNSIGNED NOT NULL DEFAULT '0', value DECIMAL(20,4) NOT NULL DEFAULT '0.0000', PRIMARY KEY (time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Currently we have about 90TB of client data stored within our database servers (2 production servers with about 16GB of RAM each) and so far the platform is running exceptionally well with execution times below 0.08 seconds.
Reading the logs we discovered that some pages (dashboards) were executing over 900 queries to aggregate and retrieve the metrics data. This high query count was caused because for each chart we first calculate all the required timestamp intervals, and then generate and execute a query for each metric at each interval, for example:
SELECT SUM(value) FROM `ts_metric_x` WHERE time>=1234567 AND time<=1234567
So metrics X aggregation X intervals = many queries. After a brainstorm session we though about the possibility to aggregate all the queries by metric, and the transform the above query in many subqueries and executed just once:
SELECT (SELECT SUM(value) FROM `ts_metric_x` WHERE time>=1234567 AND time<=1234567) AS 1234567, (SELECT SUM(value) FROM `ts_metric_x` WHERE time>=2345678 AND time<=2345678) AS 1234567, (SELECT SUM(value) FROM `ts_metric_x` WHERE time>=3456789 AND time<=3456789) AS 3456789, (SELECT SUM(value) FROM `ts_metric_x` WHERE time>=4567890 AND time<=4567890) AS 4567890
We deployed this solution to our heavier clientes, and the results are amazing. We managed to lower our execution times below 0.02, and the dashboards that were executing around 900+ queries are now generating 50 queries! The MySQL servers have the same load footprint so far.
My question is, does anybody have experience with this kind of data set or scenario? Do we have to worry about any gotchas regarding subqueries?
Update The execution times I've talked about was the page generation/execution, not each query execution. Sorry