[vz-dev] Performanceoptimierung MeterInterpreter

[Andreas Goetz]

Hallo Zusammen,

beim Testen von VZ auf dem eher schwachbrüstigen Raspberry Pi ist mir 
aufgefallen, dass Anfragen des Frontends nach einer bestimmten Anzahl 
von Tupeln bei niedrigen Zoomstufen dadurch beantwortet werden, dass der 
Interpreter durch alle Rows eines Resultsets läuft und diese gem. mit 
Hilfe von packageSize auf die gewünschte Anzahl Tupel aggregiert. Dabei 
müssen zum einen recht viele Daten verarbeitet werden, zum anderen diese 
auch erstmal aus MySQL abgeholt werden.

Alternativ lassen sich die Daten jedoch bereits in MySQL aggregieren. 
Die untenstehende Methode erledigt das und kann in MeterInterpreter 
eingefügt werden (siehe unterer Teil nach EmptyIterator).

Ich freue mich über Feedback/ Testergebnisse:


     /**
      * Get raw data
      *
      * Optimized version for thinning meter data
      *
      * @param string|integer $groupBy
      * @return Volkszaehler\DataIterator
      */
     protected function getData() {

         // get timestamps of preceding and following data points as a 
graciousness
         // for the frontend to be able to draw graphs to the left and 
right borders
         if (isset($this->from)) {
             $sql = 'SELECT MIN(timestamp) FROM (SELECT timestamp FROM 
data WHERE channel_id=? AND timestamp<? ORDER BY timestamp DESC LIMIT 2) t';
             $from = $this->conn->fetchColumn($sql, 
array($this->channel->getId(), $this->from), 0);
             if ($from)
                 $this->from = $from;
         }
         if (isset($this->to)) {
             $sql = 'SELECT MAX(timestamp) FROM (SELECT timestamp FROM 
data WHERE channel_id=? AND timestamp>? ORDER BY timestamp ASC LIMIT 2) t';
             $to = $this->conn->fetchColumn($sql, 
array($this->channel->getId(), $this->to), 0);
             if ($to)
                 $this->to = $to;
         }

         // common conditions for following SQL queries
         $sqlParameters = array($this->channel->getId());
         $sqlTimeFilter = self::buildDateTimeFilterSQL($this->from, 
$this->to, $sqlParameters);

         if ($this->groupBy) {
             $sqlGroupFields = self::buildGroupBySQL($this->groupBy);
             if (!$sqlGroupFields)
                 throw new \Exception('Unknown group');
             $sqlRowCount = 'SELECT COUNT(DISTINCT ' . $sqlGroupFields . 
') FROM data WHERE channel_id = ?' . $sqlTimeFilter;
             $sql = 'SELECT MAX(timestamp) AS timestamp, SUM(value) AS 
value, COUNT(timestamp) AS count'.
                 ' FROM data'.
                 ' WHERE channel_id = ?' . $sqlTimeFilter .
                 ' GROUP BY ' . $sqlGroupFields;
         }
         else {
             $sqlRowCount = 'SELECT COUNT(*) FROM data WHERE channel_id 
= ?' . $sqlTimeFilter;
             $sql = 'SELECT timestamp, value, 1 AS count FROM data WHERE 
channel_id=?' . $sqlTimeFilter . ' ORDER BY timestamp ASC';
         }

         $this->rowCount = (int) $this->conn->fetchColumn($sqlRowCount, 
$sqlParameters, 0);
         if ($this->rowCount <= 0)
             return new \EmptyIterator();

         // potential to reduce result set?
         if ($this->rowCount > $this->tupleCount && !$this->groupBy) {
             $packageSize = floor($this->rowCount / $this->tupleCount);

             // worth doing -> go
             if ($packageSize > 1) {
                 $this->rowCount = floor($this->rowCount / $packageSize);
                 $this->conn->query('SET @row:=0;');
                 $sql = 'SELECT MAX(aggregate.timestamp) AS timestamp, 
SUM(aggregate.value) AS value, '.$packageSize.' AS count '.
                        'FROM ('.
                        '    SELECT timestamp, value, @row:=@row+1 AS row '.
                        '     FROM data WHERE channel_id=?' . 
$sqlTimeFilter .
                        ') AS aggregate '.
                        'GROUP BY row DIV ' . $packageSize .' '.
                        'ORDER BY timestamp ASC;';
             }
         }

         $stmt = $this->conn->executeQuery($sql, $sqlParameters); // 
query for data

         return new DataIterator($stmt, $this->rowCount, $this->tupleCount);
     }
}


Viele Grüße,
Andreas