Row size too large

[dersam]

When profiling on very heavy pages, the profiler will fail to write its data to the table, and will fail with the following message.

Error while saving Aoe_Profiler data: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

Although this could be fixed by modifying some settings with the InnoDB engine, I would rather not have to do that just to get the profiler working on a page that I'm trying to cut down anyway.

Would it be possible to get an option to use the old way of displaying the AOE Profiler data at the bottom of the page, and bypass writing to the table?

[fbrnc]

Hi,

this happened to me as well and I agree that changing the MySQL is not a good solution to workaround this problem.
On the other hand I want to avoid rendering the results within the actual page like it was before since this was not a very stable solution (since there where conflicts with the individual theme, FPC,...)
I'd much rather prefer to have a single way to look at profiles and "recording" them seemed to be a good idea.
Here are two ideas that I want to explore:
1.) Instead of writing the data to the database you can choose to store the raw data in the filesystem
2.) Allowing to configure a "level of detail" in order to keep the information below a given threshold. When the dump grows large there's also some noise. I think we could filter out some stuff either by nesting level or by duration of that bucket.

What do you think?

[dersam]

I think an option to store the raw data in the filesystem, with a table in the DB containing the metadata, is a good solution. I'm not as much a fan of restricting level of detail, unless it's a filter that can be applied to the raw data after the fact. I'd rather have everything and filter down to the relevant information, than risk not having relevant info in a capture.

[fbrnc]

I think I'll definitely add this feature, but of course there's also a problem with storing files in the filesystem in a multi-server setup. The admin will not have access to the files generated on all nodes (unless you have a shared filesystem, which I don't recommend). But I guess that's how it is then, and in this case you'll have to switch back to the database and increase the MySQL settings.

Another thought I had was that the profile data can probably be compressed really well (e.g. using gzcompress). This might allow storing a lot more data before hitting the limit.

[dersam]

Another way to handle it would be build a generic storage interface that handles retrieving the data, then implement the MySQL storage and FS storage as plugins to that. The metadata could still be stored in a mysql table, with some reference to the storage backing the profile data. This would also allow building plugins to handle specific cases (like a multi-server setup), or other storage backends.

[cewald]

Had the same problem but needed a quick fix for debugging. So I wrote the call stack to files like said above instead of writing them to the database (I have only one server in my test environment) . Here is my solution (! not for productive environment):

I changed app/code/community/Aoe/Profiler/Model/Run.php to:
(// MOD-Parts at the bottom):

<?php

/**
 * Class Aoe_Profiler_Model_Stack
 *
 * @author Fabrizio Branca
 * @since 2014-08-15
 *
 * @method getStackData()
 * @method getRoute()
 * @method getUrl()
 * @method getTotalTime()
 * @method getTotalMemory()
 * @method getCreatedAt()
 * @method getSessionId()
 * @method setStackData()
 * @method setRoute()
 * @method setUrl()
 * @method setTotalTime()
 * @method setTotalMemory()
 * @method setCreatedAt()
 * @method setSessionId()
 */
class Aoe_Profiler_Model_Run extends Mage_Core_Model_Abstract
{

    protected $stackLog = array();
    protected $treeData = array();

    protected $metrics = array('time', 'realmem' /*, 'emalloc' */);

    protected function _construct()
    {
        $this->_init('aoe_profiler/run');
    }

    /**
     * @return Aoe_Profiler_Model_Run
     */
    public function loadStackLogFromProfiler()
    {
        Varien_Profiler::disable();
        $this->stackLog = Varien_Profiler::getStackLog();
        $this->_hasDataChanges = true;
        return $this;
    }

    public function populateMetadata()
    {
        $this->setUrl(Mage::app()->getRequest()->getRequestUri());
        $this->setRoute(Mage::app()->getFrontController()->getAction()->getFullActionName());
        $this->setSessionId(Mage::getSingleton('core/session')->getSessionId());

        $totals = Varien_Profiler::getTotals();
        $this->setTotalTime($totals['time']);
        $this->setTotalMemory((float)$totals['realmem']/(1024*1024));
    }

    public function getStackLog()
    {
        return $this->stackLog;
    }

    /**
     * @return Aoe_Profiler_Model_Run
     */
    public function processRawData()
    {
        // Create hierarchical array of keys pointing to the stack
        foreach ($this->stackLog as $uniqueId => $data) {
            $this->createHierarchyArray($this->treeData, $data['level'], $uniqueId);
        }

        $this->treeData = end($this->treeData);
        $this->updateValues($this->treeData);

        $this->calcRelativeValues();

        return $this;
    }

    /**
     * Update values. (Recursive function)
     *
     * @param $arr
     * @param string $vKey
     */
    protected function updateValues(&$arr, $vKey = '')
    {
        $subSum = array_flip($this->metrics);
        foreach ($arr as $k => $v) {

            if (strpos($k, '_children') === false) {

                if (isset($arr[$k . '_children']) && is_array($arr[$k . '_children'])) {
                    $this->updateValues($arr[$k . '_children'], $v);
                } else {
                    foreach ($subSum as $key => $value) {
                        $this->stackLog[$v][$key . '_sub'] = 0;
                        $this->stackLog[$v][$key . '_own'] = $this->stackLog[$v][$key . '_total'];
                    }
                }
                foreach ($subSum as $key => $value) {
                    $subSum[$key] += $this->stackLog[$v][$key . '_total'];
                }
            }
        }
        if (isset($this->stackLog[$vKey])) {
            foreach ($subSum as $key => $value) {
                $this->stackLog[$vKey][$key . '_sub'] = $subSum[$key];
                $this->stackLog[$vKey][$key . '_own'] = $this->stackLog[$vKey][$key . '_total'] - $subSum[$key];
            }
        }
    }

    /**
     * @return array
     */
    public function getTreeData()
    {
        return $this->treeData;
    }

    /**
     * Calculate relative values
     */
    protected function calcRelativeValues()
    {
        foreach ($this->stackLog as $key => $value) {
            foreach ($this->metrics as $metric) {
                foreach (array('own', 'sub', 'total') as $column) {
                    if (!isset($this->stackLog[$key][$metric . '_' . $column])) {
                        continue;
                    }
                    $this->stackLog[$key][$metric . '_rel_' . $column] = $this->stackLog[$key][$metric . '_' . $column] / $this->stackLog['timetracker_0'][$metric . '_total'];
                }
                $this->stackLog[$key][$metric . '_rel_offset'] = $this->stackLog[$key][$metric . '_start_relative'] / $this->stackLog['timetracker_0'][$metric . '_total'];
            }
        }
    }

    /**
     * Helper function for internal data manipulation (Recursive function)
     *
     * @param array $arr
     * @param int $pointer
     * @param string $uniqueId
     * @return void
     */
    protected function createHierarchyArray(&$arr, $pointer, $uniqueId)
    {
        if (!is_array($arr)) {
            $arr = array();
        }
        if ($pointer > 0) {
            end($arr);
            $k = key($arr);
            $this->createHierarchyArray($arr[intval($k) . '_children'], $pointer - 1, $uniqueId);
        } else {
            $arr[] = $uniqueId;
        }
    }

    /**
     * Before saving...
     *
     * @return Mage_Core_Model_Abstract
     */
    protected function _beforeSave()
    {
        $date = Mage::getModel('core/date')->gmtDate();
        if ($this->isObjectNew() && !$this->getCreatedAt()) {
            $this->setCreatedAt($date);
        }
        // MOD < Write empty data
        // Row size to large (can't write to db)
        // https://github.com/AOEpeople/Aoe_Profiler/issues/26
        $this->setStackData('');
        // MOD > Write empty data
        return parent::_beforeSave();
    }

    // MOD < Write stack data to file (it is too large for db)
    // https://github.com/AOEpeople/Aoe_Profiler/issues/26
    protected function _afterSave()
    {
        // Mage file handler
        $file = new Varien_Io_File();

        // Get and create target dir
        $dir = Mage::getBaseDir('var') . DS . 'aoe_profiler';
        $file->checkAndCreateFolder($dir);

        // Get and serialize stack data
        $stackData = serialize($this->stackLog);

        // Write stack data to var folder
        $file->write(sprintf("%s/%s.txt", $dir, $this->getId()), $stackData);
    }
    // MOD > Write stack data to file (it is too large for db)

    protected function _afterLoad()
    {
        $result = parent::_afterLoad();

        // MOD < Get call stack from text file
        $file = new Varien_Io_File();
        $this->stackLog = unserialize($file->read(Mage::getBaseDir('var') . DS . 'aoe_profiler/' . $this->getId() . '.txt'));
        // MOD > Get call stack from text file

        if ($this->stackLog === false) {
            Mage::throwException('Error while unserializing data');
        }
        return $result;
    }

    // MOD < Remove stack data file after delete
    protected function _afterDelete()
    {
        $file = new Varien_Io_File();
        $file->rm(Mage::getBaseDir('var') . DS . 'aoe_profiler/' . $this->getId() . '.txt');
    }
    // MOD > Remove stack data file after delete
}