1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL averaging CPU load times.

Discussion in 'MySQL' started by gunslingor, Nov 20, 2015.

0
  1. #1
    Really need help with this, been working on it for weeks. Here is what my data looks like:

    # cpu_name, used, unix_timestamp, timestamp
    'CPU 3', '0.000', '1448046719', '2015-11-20 14:11:59'
    'CPU 2', '0.000', '1448046719', '2015-11-20 14:11:59'
    'CPU 1', '0.000', '1448046719', '2015-11-20 14:11:59'
    'CPU 0', '0.000', '1448046719', '2015-11-20 14:11:59'
    'CPU 3', '0.000', '1448046717', '2015-11-20 14:11:57'
    'CPU 2', '0.375', '1448046717', '2015-11-20 14:11:57'
    'CPU 1', '0.000', '1448046717', '2015-11-20 14:11:57'
    'CPU 0', '0.000', '1448046717', '2015-11-20 14:11:57'
    'CPU 3', '0.000', '1448046716', '2015-11-20 14:11:56'
    'CPU 2', '0.025', '1448046716', '2015-11-20 14:11:56'
    'CPU 1', '0.050', '1448046716', '2015-11-20 14:11:56'
    'CPU 0', '0.250', '1448046716', '2015-11-20 14:11:56'
    'CPU 3', '0.000', '1448046715', '2015-11-20 14:11:55'
    'CPU 2', '0.000', '1448046715', '2015-11-20 14:11:55'
    'CPU 1', '0.000', '1448046715', '2015-11-20 14:11:55'
    'CPU 0', '0.000', '1448046715', '2015-11-20 14:11:55'
    'CPU 3', '0.000', '1448046713', '2015-11-20 14:11:53'
    'CPU 2', '0.000', '1448046713', '2015-11-20 14:11:53'
    'CPU 1', '0.650', '1448046713', '2015-11-20 14:11:53'
    'CPU 0', '0.000', '1448046713', '2015-11-20 14:11:53'
    'CPU 3', '0.231', '1448046712', '2015-11-20 14:11:52'
    'CPU 2', '0.000', '1448046712', '2015-11-20 14:11:52'
    'CPU 1', '0.025', '1448046712', '2015-11-20 14:11:52'
    'CPU 0', '0.025', '1448046712', '2015-11-20 14:11:52'
    'CPU 3', '0.000', '1448046711', '2015-11-20 14:11:51'
    'CPU 2', '0.000', '1448046711', '2015-11-20 14:11:51'
    'CPU 1', '0.000', '1448046711', '2015-11-20 14:11:51'
    'CPU 0', '0.000', '1448046711', '2015-11-20 14:11:51'
    'CPU 3', '0.000', '1448046709', '2015-11-20 14:11:49'
    'CPU 2', '0.000', '1448046709', '2015-11-20 14:11:49'
    'CPU 1', '0.024', '1448046709', '2015-11-20 14:11:49'
    'CPU 0', '0.341', '1448046709', '2015-11-20 14:11:49'
    'CPU 3', '0.000', '1448046708', '2015-11-20 14:11:48'
    'CPU 2', '0.051', '1448046708', '2015-11-20 14:11:48'
    'CPU 1', '0.000', '1448046708', '2015-11-20 14:11:48'
    'CPU 0', '0.293', '1448046708', '2015-11-20 14:11:48'
    'CPU 3', '0.000', '1448046707', '2015-11-20 14:11:47'
    'CPU 2', '0.000', '1448046707', '2015-11-20 14:11:47'
    'CPU 1', '0.000', '1448046707', '2015-11-20 14:11:47'
    'CPU 0', '0.000', '1448046707', '2015-11-20 14:11:47'
    'CPU 3', '0.000', '1448046705', '2015-11-20 14:11:45'
    'CPU 2', '0.000', '1448046705', '2015-11-20 14:11:45'
    'CPU 1', '0.000', '1448046705', '2015-11-20 14:11:45'
    'CPU 0', '0.300', '1448046705', '2015-11-20 14:11:45'
    'CPU 3', '0.325', '1448046704', '2015-11-20 14:11:44'
    'CPU 2', '0.000', '1448046704', '2015-11-20 14:11:44'
    'CPU 1', '0.048', '1448046704', '2015-11-20 14:11:44'
    'CPU 0', '0.000', '1448046704', '2015-11-20 14:11:44'
    'CPU 3', '0.000', '1448046703', '2015-11-20 14:11:43'
    'CPU 2', '0.000', '1448046703', '2015-11-20 14:11:43'
    'CPU 1', '0.000', '1448046703', '2015-11-20 14:11:43'
    'CPU 0', '0.000', '1448046703', '2015-11-20 14:11:43'
    'CPU 3', '0.000', '1448046701', '2015-11-20 14:11:41'
    'CPU 2', '0.000', '1448046701', '2015-11-20 14:11:41'
    'CPU 1', '0.000', '1448046701', '2015-11-20 14:11:41'
    'CPU 0', '0.275', '1448046701', '2015-11-20 14:11:41'
    'CPU 3', '0.000', '1448046700', '2015-11-20 14:11:40'
    'CPU 2', '0.025', '1448046700', '2015-11-20 14:11:40'
    'CPU 1', '0.050', '1448046700', '2015-11-20 14:11:40'
    'CPU 0', '0.359', '1448046700', '2015-11-20 14:11:40'
    'CPU 3', '0.000', '1448046699', '2015-11-20 14:11:39'
    'CPU 2', '0.000', '1448046699', '2015-11-20 14:11:39'
    'CPU 1', '0.000', '1448046699', '2015-11-20 14:11:39'
    'CPU 0', '0.000', '1448046699', '2015-11-20 14:11:39'
    'CPU 3', '0.000', '1448046697', '2015-11-20 14:11:37'
    'CPU 2', '0.175', '1448046697', '2015-11-20 14:11:37'
    'CPU 1', '0.000', '1448046697', '2015-11-20 14:11:37'
    'CPU 0', '0.000', '1448046697', '2015-11-20 14:11:37'
    'CPU 3', '0.000', '1448046696', '2015-11-20 14:11:36'
    'CPU 2', '0.049', '1448046696', '2015-11-20 14:11:36'
    'CPU 1', '0.000', '1448046696', '2015-11-20 14:11:36'
    'CPU 0', '0.439', '1448046696', '2015-11-20 14:11:36'
    'CPU 3', '0.000', '1448046695', '2015-11-20 14:11:35'
    'CPU 2', '0.000', '1448046695', '2015-11-20 14:11:35'
    'CPU 1', '0.000', '1448046695', '2015-11-20 14:11:35'
    'CPU 0', '0.000', '1448046695', '2015-11-20 14:11:35'
    'CPU 3', '0.000', '1448046693', '2015-11-20 14:11:33'
    'CPU 2', '0.000', '1448046693', '2015-11-20 14:11:33'
    'CPU 1', '0.000', '1448046693', '2015-11-20 14:11:33'
    'CPU 0', '0.150', '1448046693', '2015-11-20 14:11:33'
    'CPU 3', '0.050', '1448046692', '2015-11-20 14:11:32'
    'CPU 2', '0.048', '1448046692', '2015-11-20 14:11:32'
    'CPU 1', '0.025', '1448046692', '2015-11-20 14:11:32'
    'CPU 0', '0.425', '1448046692', '2015-11-20 14:11:32'
    'CPU 3', '0.000', '1448046691', '2015-11-20 14:11:31'
    'CPU 2', '0.000', '1448046691', '2015-11-20 14:11:31'
    'CPU 1', '0.000', '1448046691', '2015-11-20 14:11:31'
    'CPU 0', '0.000', '1448046691', '2015-11-20 14:11:31'
    'CPU 3', '0.000', '1448046689', '2015-11-20 14:11:29'
    'CPU 2', '0.000', '1448046689', '2015-11-20 14:11:29'



    I Need a query that will average the data in a variable fashion, it needs to be able to query at least 90 days worth of the data (and the data can be throttled back, currently set to log once a second). Here's my PHP thus far, it's so close but the SQL needs an expert:

    $time_period = $_POST['time_period'];
    $sample_factor = $_POST['sample_factor'];
    $time = substr($time_period, 0, -1);
    $unit = substr($time_period, -1);
    if ($unit == 'w'){
    $sql_unit = 'WEEK';
    $sql_sample_size = 60*24*7;
    }else if ($unit == 'd'){
    $sql_unit = 'DAY';
    $sql_sample_size = 60*24;
    }else if ($unit == 'h'){
    $sql_unit = 'HOUR';
    $sql_sample_size = 10;
    }else if ($unit == 'm'){
    $sql_unit = 'MINUTE';
    $sql_sample_size = 2;
    }else if ($unit == 's'){
    $sql_unit = 'SECOND';
    $sql_sample_size = 1;
    }
    $sql= "SELECT cpu_name, AVG(used), ". $sql_sample_size. "*ROUND(unix_timestamp/". $sql_sample_size. ") As unix_timestamp, timestamp FROM tbl_cpu_use WHERE timestamp>(NOW() - INTERVAL ". $time. " ". $sql_unit. ") GROUP BY unix_timestamp, cpu_name ORDER BY id DESC;";
    $result=mysql_query($sql);
    if ($result){
    while($row = mysql_fetch_array($result, MYSQL_NUM)){
    $cpu_name = $row[0];
    $used = $row[1];
    $unix_timestamp = (int)$sql_sample_size*round($row[2]/$sql_sample_size) ;
    $timestamp = $row[3];

    $rows[] = $row;
    }
    echo json_encode($rows);
    }else{
    echo "ERROR";
    }

    Variations of the SQL tried:
    SET @time_interval := date_sub(NOW(), INTERVAL 2 HOUR);
    SET @sample_interval := 60*60;
    SELECT cpu_name, AVG(used), @sample_interval*ROUND(unix_timestamp/@sample_interval) As unix_timestamp, timestamp FROM BH_DB.tbl_cpu_use WHERE timestamp>@time_interval GROUP BY unix_timestamp, cpu_name ORDER BY id DESC;


    -- SELECT cpu_name, AVG(used), AVG(unix_timestamp) FROM BH_DB.tbl_cpu_use WHERE unix_timestamp>@time_interval GROUP BY cpu_name ORDER BY id DESC;
    -- "SELECT cpu_name, AVG(used) As used, @sample_interval*ROUND(unix_timestamp/@sample_interval) As unix_timestamp, timestamp FROM tbl_cpu_use WHERE timestamp>@time_interval GROUP BY cpu_name, unix_timestamp ORDER BY id DESC;"

    And here's a basic screenshot of what this is for, the interface:
    [​IMG]

    Basically the result needs to have these properties:
    • lists each CPU discretely
    • CPU and timestamp should be able to form a primary key (for discussion purposes only, they 'should' be able to).
    • The cpu_used column needs to be an average for the time period. e.g. if the sample time period is 1, the effective resulting log clock would be counting by hours and the cpu_used for that hour would be the average for that hour, for each CPU discretely, for the entire time interval.
    EDIT (should work but doesn't, erg.):
    $sql= "SELECT cpu_name, AVG(used) as used, unix_timestamp FROM (SELECT cpu_name, used, (". $sql_sample_size. "*ROUND(unix_timestamp/". $sql_sample_size. ")) As unix_timestamp FROM tbl_cpu_use WHERE timestamp>(NOW() - INTERVAL ". $time. " ". $sql_unit. ")) GROUP BY cpu_name,unix_timestamp ORDER BY unix_timestamp DESC;";

    EDIT (This almost works perfectly, except that old values in my graph jump around so it can't be averaging correctly, man I need help):
    $sql= "SELECT cpu_name, AVG(used) as used, unix_timestamp FROM (SELECT cpu_name, used, (". $sql_sample_size. "*ROUND(unix_timestamp/". $sql_sample_size. ")) As unix_timestamp FROM tbl_cpu_use WHERE timestamp>(NOW() - INTERVAL ". $time. " ". $sql_unit. ")) AS sub_table GROUP BY cpu_name,unix_timestamp ORDER BY unix_timestamp DESC;";
     
    Last edited: Nov 20, 2015
    gunslingor, Nov 20, 2015 IP