CampaignOpenUserAgentsWidget 30sec timeout

lauwens

New Member
We just send our first campaign (300k subs) and when I want to view the campaign the overview page gives a timeout.

After some debuging I found the origin of the problem in CampaignOpenUserAgentsWidget that takes way too much time.

My quick fix:

In \apps\customer\components\web\widgets\campaign-tracking\CampaignOpenUserAgentsWidget.php

change $limit to 10000

add new function

Code:
    /**
     * @param $limit
     * @param $offset
     *
     * @return array
     */
    protected function getDBGroupData($limit, $offset)
    {

        return Yii::app()->db->createCommand()
            ->select('user_agent, count(user_agent) as count')
            ->from(CampaignTrackOpen::model()->tableSchema->name)
            ->where('campaign_id=:campaign_id', array(':campaign_id'=>(int)$this->campaign->campaign_id))
            ->group('user_agent')
            ->limit($limit)
            ->offset($offset)
            ->queryAll();

    }
and then change getData()
Code:
while (($models = $this->getModels($limit, $offset))) {
to
Code:
while (($models = $this->getDBGroupData($limit, $offset))) {
also change 2x

Code:
$model->user_agent
to
Code:
$model['user_agent']
and all 3 occurrence of
Code:
['count']++
to
Code:
['count'] += $model['count']


Never used Yii before so don't know if there was a better method?
But at least now my campaign overview loads in 5seconds
 

twisted1919

Administrator
Staff member
@lauwens - while the fix works for you, it has to work for anyone :D
Any change you open a support ticket with backend url and access to your app, together with FTP so that we see what is going on and maybe improve ?
 

lauwens

New Member
@lauwens - while the fix works for you, it has to work for anyone :D
Any change you open a support ticket with backend url and access to your app, together with FTP so that we see what is going on and maybe improve ?
Unfortunately I can not provide FTP access, remote access is only provided through managed VPN connection that I can not share.

I have a unmodified clean install with just one campaign, if needed I could provide some PHP.ini info or maybe the stats table data

I understand that my provided fix needs to be viable for everyone, but I just figured having the duplicate data already together and counted with the query gives better performance then going through every entry and sorting in PHP

But the most performance loss was having 90.000+ (in my case) stat models being initialized with only one string (user agent) in them. So I didn't use the model but a simple array (that was already smaller thanks to the groupby)
 

twisted1919

Administrator
Staff member
@lauwens - Actually, since you already have the data, here's the full file content:
PHP:
<?php defined('MW_PATH') || exit('No direct script access allowed');

/**
* CampaignOpenUserAgentsWidget
* 
* @package MailWizz EMA
* @author Serban George Cristian <cristian.serban@mailwizz.com> 
* @link https://www.mailwizz.com/
* @copyright MailWizz EMA (https://www.mailwizz.com)
* @license https://www.mailwizz.com/license/
* @since 1.6.4
*/

class CampaignOpenUserAgentsWidget extends CWidget 
{
    /**
     * @var $campaign Campaign|null
     */
    public $campaign;

   /**
    * @return string
    * @throws CException
    */
    public function run() 
    {
       if (empty($this->campaign) || !version_compare(PHP_VERSION, '5.4', '>=')) {
          return '';
       }

       // 1.7.9
       if ($this->campaign->option->open_tracking != CampaignOption::TEXT_YES) {
          return '';
       }

       // 1.7.9 - static counters
       if ($this->campaign->option->opens_count >= 0) {
          return '';
       }
       
        $cacheKey = __METHOD__;
        if ($this->campaign) {
            $cacheKey .= '::' . $this->campaign->campaign_uid;
        }
        $cacheKey = sha1($cacheKey);
        
        if (($data = Yii::app()->cache->get($cacheKey)) === false) {
            $data = $this->getData();
            Yii::app()->cache->set($cacheKey, $data, 300);
        }
        
        if (empty($data)) {
            return '';
        }
        
        $chartData = array(
           'os'     => array(),
           'device' => array(),
           'browser'=> array(),
        );
        
        $allEmpty = true;
        foreach ($chartData as $key => $_) {

           if (empty($data[$key])) {
               continue;
            }
            $allEmpty = false;

           foreach ($data[$key] as $row) {
              $chartData[$key][] = array(
                 'label'           => $row['name'],
                 'data'            => $row['count'],
                 'count'           => $row['count'],
                 'count_formatted' => Yii::app()->numberFormatter->formatDecimal($row['count']),
              );
           }
        }
        
        if ($allEmpty) {
           return '';
        }
        
        Yii::app()->clientScript->registerScriptFile(Yii::app()->apps->getBaseUrl('assets/js/flot/jquery.flot.min.js'));
        Yii::app()->clientScript->registerScriptFile(Yii::app()->apps->getBaseUrl('assets/js/flot/jquery.flot.pie.min.js'));
        Yii::app()->clientScript->registerScriptFile(Yii::app()->apps->getBaseUrl('assets/js/campaign-open-user-agents.js'));
        
        $this->render('campaign-open-user-agents', compact('chartData', 'data'));
    }

    /**
     * @return array
     */
    protected function getData()
    {
       $limit  = 5000;
       $offset = 0;
       
       $detector = '\WhichBrowser\Parser';
       $data     = array(
          'os'        => array(),
          'device'    => array(),
          'browsers'  => array(),
       );
       
       while (($models = $this->getModels($limit, $offset))) {
          $offset = $offset + $limit;
          
          foreach ($models as $model) {
             
             if (strlen($model['user_agent']) < 10) {
                continue;
             }
             $result = new $detector($model['user_agent'], array('detectBots' => false));

             if (empty($result->os->name) || empty($result->device->type) || empty($result->browser->name)) {
                continue;
             }
             
             // OS
             if (!isset($data['os'][$result->os->name])) {
                $data['os'][$result->os->name] = array(
                   'name'  => ucwords($result->os->name),
                   'count' => 0,
                );
             }
             $data['os'][$result->os->name]['count'] += $model['counter'];
             
             // Device
             if (!isset($data['device'][$result->device->type])) {
                $data['device'][$result->device->type] = array(
                   'name'  => ucwords($result->device->type),
                   'count' => 0,
                );
             }
             $data['device'][$result->device->type]['count'] += $model['counter'];
             
             // Browser
             $name = $result->browser->name;
             if (!empty($result->browser->version)) {
                $version = explode('.', $result->browser->version->value);
                $version = array_slice($version, 0, 2);
                $version = implode('.', $version);
                $name .= sprintf('(v.%s)', $version);
             }
             if (!isset($data['browser'][$name])) {
                $data['browser'][$name] = array(
                   'name'  => ucwords($name),
                   'count' => 0,
                );
             }
             $data['browser'][$name]['count'] += $model['counter'];
          }
       }
       
       foreach ($data as $key => $contents) {
          $counts = array();
          foreach ($contents as $content) {
             $counts[] = $content['count'];
          }
          $items = $data[$key];
          array_multisort($counts, SORT_NUMERIC | SORT_DESC, $items);
          $data[$key] = array_slice($items, 0, 50);
       }
       
        return $data;
    }

   /**
    * @param $limit
    * @param $offset
    *
    * @return array
    */
   protected function getModels($limit, $offset)
   {
      try {

         $rows = Yii::app()->db->createCommand()
            ->select('user_agent, count(user_agent) as counter')
            ->from(CampaignTrackOpen::model()->tableName())
            ->where('campaign_id = :campaign_id', array(':campaign_id' => (int)$this->campaign->campaign_id))
            ->group('user_agent')
            ->limit($limit)
            ->offset($offset)
            ->queryAll();
         
      } catch (Exception $e) {

         $rows = array();
      }
      
      return $rows;
   }
}
Maybe you can run it and see if it's all okay.

Cheers.
 
Top