Ignore:
Timestamp:
Feb 21, 2018, 12:55:47 PM (19 months ago)
Author:
mabarracus
Message:

Completed platformdata visualization

File:
1 edited

Legend:

Unmodified
Added
Removed
  • lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php

    r6876 r6884  
    311311             }
    312312            }
    313 
    314          
     313        // architecture
     314        $sql="select year(date) as year, month(date) as month, Releases_name, count(arch) as count, arch from Client_Versions group by Releases_name,year,month,arch order by year desc,month desc,Releases_name asc,arch asc;";
     315        $cache_key='extended_arch';
     316        $data = $cache->get($cache_key);
     317        $stime=microtime(true);
     318        if ($data != false){
     319            $clients_arch=$data;
     320        }else{
     321            $result=$this->dbconn->query($sql);
     322            if ($result){
     323                $this->times+=microtime(true)-$stime;
     324                $clients_arch=[];
     325                $tmp=[];
     326                while($row=$result->fetch_array(MYSQLI_ASSOC)){
     327                    $date=$row['year'].'_'.$row['month'];
     328                    $tmp[$date][$row['Releases_name']][$row['arch']]=intval($row['count']);
     329                }
     330                foreach ($tmp as $date){
     331                    $clients_arch[]=$date;
     332                }
     333                $cache->store($clients_arch,$cache_key);
     334            }else{
     335                $clients_arch=$this->dbconn->error;
     336            }
     337        }
     338        // memory
     339        //$sql_2G="select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where mem < 2048000 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;";
     340        $sql_2G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem < 2048000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";         
     341        $cache_key='extended_memory_2G';
     342        $data = $cache->get($cache_key);
     343        $stime=microtime(true);
     344        if ($data != false){
     345            $clients_2G=$data;
     346        }else{
     347            $result=$this->dbconn->query($sql_2G);
     348            if ($result){
     349                $this->times+=microtime(true)-$stime;
     350                $clients_2G=[];
     351                $tmp=[];
     352                while($row=$result->fetch_array(MYSQLI_ASSOC)){
     353                    $date=$row['year'].'_'.$row['month'];
     354                    $tmp[$date][$row['Releases_name']]=intval($row['count']);
     355                }
     356                foreach ($tmp as $date){
     357                    $clients_2G[]=$date;
     358                }
     359                $cache->store($clients_2G,$cache_key);
     360            }else{
     361                $clients_2G=$this->dbconn->error;
     362            }
     363        }
     364        //$sql_4G="select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where mem > 2048000 and mem < 4096000 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;";
     365        $sql_4G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 2048000 and mem < 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     366        $cache_key='extended_memory_4G';
     367        $data = $cache->get($cache_key);
     368        $stime=microtime(true);
     369        if ($data != false){
     370            $clients_4G=$data;
     371        }else{
     372            $result=$this->dbconn->query($sql_4G);
     373            if ($result){
     374                $this->times+=microtime(true)-$stime;
     375                $clients_4G=[];
     376                $tmp=[];
     377                while($row=$result->fetch_array(MYSQLI_ASSOC)){
     378                    $date=$row['year'].'_'.$row['month'];
     379                    $tmp[$date][$row['Releases_name']]=intval($row['count']);
     380                }
     381                foreach ($tmp as $date){
     382                    $clients_4G[]=$date;
     383                }
     384                $cache->store($clients_4G,$cache_key);
     385            }else{
     386                $clients_4G=$this->dbconn->error;
     387            }
     388        }
     389        //$sql_8G="select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where mem > 4096000 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;";
     390        $sql_8G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     391        $cache_key='extended_memory_8G';
     392        $data = $cache->get($cache_key);
     393        $stime=microtime(true);
     394        if ($data != false){
     395            $clients_8G=$data;
     396        }else{
     397            $result=$this->dbconn->query($sql_8G);
     398            if ($result){
     399                $this->times+=microtime(true)-$stime;
     400                $clients_8G=[];
     401                $tmp=[];
     402                while($row=$result->fetch_array(MYSQLI_ASSOC)){
     403                    $date=$row['year'].'_'.$row['month'];
     404                    $tmp[$date][$row['rel']]=intval($row['count']);
     405                }
     406                foreach ($tmp as $date){
     407                    $clients_8G[]=$date;
     408                }
     409                $cache->store($clients_8G,$cache_key);
     410            }else{
     411                $clients_8G=$this->dbconn->error;
     412            }
     413        }
     414        // cpu
     415        //$sql_mono = "select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where ncpu < 2 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;";
     416        $sql_mono = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu = 1 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     417        $cache_key='extended_memory_cpu_mono';
     418        $data = $cache->get($cache_key);
     419        $stime=microtime(true);
     420        if ($data != false){
     421            $clients_mono=$data;
     422        }else{
     423            $result=$this->dbconn->query($sql_mono);
     424            if ($result){
     425                $this->times+=microtime(true)-$stime;
     426                $clients_mono=[];
     427                $tmp=[];
     428                while($row=$result->fetch_array(MYSQLI_ASSOC)){
     429                    $date=$row['year'].'_'.$row['month'];
     430                    $tmp[$date][$row['rel']]=intval($row['count']);
     431                }
     432                foreach ($tmp as $date){
     433                    $clients_mono[]=$date;
     434                }
     435                $cache->store($clients_mono,$cache_key);
     436            }else{
     437                $clients_mono=$this->dbconn->error;
     438            }
     439        }
     440        //$sql_dualquad = "select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where ncpu > 1 and ncpu < 5 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;";
     441        $sql_dualquad = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 1 and ncpu < 5 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     442        $cache_key='extended_memory_cpu_dualquad';
     443        $data = $cache->get($cache_key);
     444        $stime=microtime(true);
     445        if ($data != false){
     446            $clients_dualquad=$data;
     447        }else{
     448            $result=$this->dbconn->query($sql_dualquad);
     449            if ($result){
     450                $this->times+=microtime(true)-$stime;
     451                $clients_dualquad=[];
     452                $tmp=[];
     453                while($row=$result->fetch_array(MYSQLI_ASSOC)){
     454                    $date=$row['year'].'_'.$row['month'];
     455                    $tmp[$date][$row['rel']]=intval($row['count']);
     456                }
     457                foreach ($tmp as $date){
     458                    $clients_dualquad[]=$date;
     459                }
     460                $cache->store($clients_dualquad,$cache_key);
     461            }else{
     462                $clients_dualquad=$this->dbconn->error;
     463            }
     464        }
     465        $sql_more = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 4 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     466        $cache_key='extended_memory_cpu_other';
     467        $data = $cache->get($cache_key);
     468        $stime=microtime(true);
     469        if ($data != false){
     470            $clients_more=$data;
     471        }else{
     472            $result=$this->dbconn->query($sql_more);
     473            if ($result){
     474                $this->times+=microtime(true)-$stime;
     475                $clients_more=[];
     476                $tmp=[];
     477                while($row=$result->fetch_array(MYSQLI_ASSOC)){
     478                    $date=$row['year'].'_'.$row['month'];
     479                    $tmp[$date][$row['rel']]=intval($row['count']);
     480                }
     481                foreach ($tmp as $date){
     482                    $clients_more[]=$date;
     483                }
     484                $cache->store($clients_more,$cache_key);
     485            }else{
     486                $clients_more=$this->dbconn->error;
     487            }
     488        }
    315489         // CLIENT UPDATES
    316490         $sql="select year,month,count(*) as nclients,sum(cnt)-count(*) as nclients_updated from (select Client_uid,count(Client_uid) as cnt,year(date) as year,month(date) as month from Client_Versions where date >= '$min_date' GROUP by Client_uid,year,month having count(Client_uid) >= 1 ) t group by year,month order by year desc,month desc";
     
    442616             $stats['clients']['change_flavours']=$change_flavour;
    443617         }
     618         if (isset($clients_arch)){
     619             $stats['clients']['architectures']=$clients_arch;
     620         }
     621         if (isset($clients_2G)){
     622             $stats['clients']['mem']['2G']=$clients_2G;
     623         }
     624         if (isset($clients_4G)){
     625             $stats['clients']['mem']['4G']=$clients_4G;
     626         }
     627         if (isset($clients_8G)){
     628             $stats['clients']['mem']['8G']=$clients_8G;
     629         }
     630         if (isset($clients_mono)){
     631             $stats['clients']['cpu']['mono']=$clients_mono;
     632         }
     633         if (isset($clients_dualquad)){
     634             $stats['clients']['cpu']['dualquad']=$clients_dualquad;
     635         }
     636         if (isset($clients_more)){
     637             $stats['clients']['cpu']['more']=$clients_more;
     638         }
    444639         $stats['debug_query_time']=strval(number_format($this->times,5));
    445640         if (file_exists($this->ka_file)){
Note: See TracChangeset for help on using the changeset viewer.