Archiving Memory exhausted error - Archiving memory leaks

[anonymous-matomo-user]

Users have memory exhausted errors when running Piwik archiving. There are two distinct use cases:

• Users with a low to medium traffic websites exhausting the fixed-sized memory of their PHP web host (eg. 64MB)
• Users with very high traffic websites (100k or +1M pages per day) exhausting even a very high PHP memory limit (eg. 1G is not enough).

The error looks like:

Fatal error: Allowed memory size of 67108864 bytes exhausted 
(tried to allocate 73 bytes) in
 /var/www/web5/html/piwik/core/DataTable.php on line 939

-> We highly suggest users experiencing issues to upgrade to PHP 5.3 which has some improvements around memory management.

Ideas to solve this issue (less time consuming first)?

• 1) Monthly archiving should use the smaller amount of weeks and days inside the month Currently, archiving a month in Piwik will sum the reports for all 30 days and sum all reports together. This is causing most of the php memory issues which start failing when summing all datatables for Pages URLs for a month for example. Instead, we should reuse the algorithm used to process date ranges, which select the minimum number of periods to use to process the enclosing period. So, a month would typically use 3 or 4 full weeks + the remaining ~5-10 days. This would result in up to 70% less datatables to select and sum!

  ie a month archiving would select 4 weeks archiving + 3 day archiving, or 3 weeks archiving + 10 days archiving, so either 7 or 13 archives, instead of 30...

• 2) Fix remaining Memory leaks
  • This error is most likely caused by archiving tasks not freeing memory after the task finished runnning. We did some improvements in the past around memory management in Piwik (PHP doesn't free memory automatically in all cases) - see ticket #374 for more info. There is more investigation work to do obviously.

• 3) Process aggregation in MySQL Currently, to process "Top Page URLs" report, we select ALL URLs, then truncate to 500 URLs (see datatable_archiving_maximum_rows_actions in global.ini.php)

  Loading for example 50,000 URLs in memory in PHP results in obvious memory crashes. The solution here would be to process the aggregate of the top 500 URLs (including the last "Others" which aggregated all other "truncated" rows).

  • Proposal: run the Aggregation in Mysql! Example query that will select the sum of visits per hour, then truncate in Mysql to the first 15 rows, aggregating all rows from 16-23 in the label "Others". Imagine doing the same thing for Page URLs and only returning the top 1000 pages instead of all pages!

#This query uses the "counter" to truncate the rows after X rows. In real life, we would truncate after X=500 or 5000
SELECT SUM(count), case when counter = 15 then "Others" else hour end AS label
FROM (

#This query adds the "counter" to each row after it was sorted by visits desc
SELECT count, hour, case when @counter = 15 then 15 else @counter:=@counter+1 end AS counter
FROM
(

#This query selects count for each hour of the day
SELECT count(*) as count, 
       HOUR(visit_last_action_time) as hour
FROM `piwik_log_visit`
WHERE 1 
GROUP BY hour
ORDER BY count DESC
) T1,
(SELECT @counter:=0) T2
) T3

GROUP BY counter
ORDER BY counter ASC

Here I only sum the number of visits, but we could automatically rewrite all aggregate queries in ArchiveProcessing/Day.php using this mechanism?

Output:

SUM(count)  label
113     5
108     19
94  2
93  20
90  21
87  22
80  4
79  9
77  8
67  6
65  1
65  3
61  11
60  10
467     Others

Obviously truncating the "Hourly" report is a bad example, but replace it with Page URLs to get the picture :) This will save a lot of bandwith and CPU processing, especially for websites that have more than 500 unique Page URLs per day or external keywords, etc.

[robocoder]

In php 5.3 with zend.enable_gc, the garbage collector only runs when the root buffer is full (default max is 10000 entries). We need to know if a collection cycle is forced when the memory limit is reached, or whether we should manully call gc_collect_cycles().

[mattab]

(In [2025]) Fixes #1227 the archive script now loops over all websites and triggers a different request for each archive, helping out with the archive memory exhausted issue (refs #766)

[anonymous-matomo-user]

has there been any resolution to this? I just wanted to play with piwik on my site, but it's waaaaaay too busy for piwik apparently. We're at 80k unique visitors a day or thereabouts...i set php.ini to 1GB, but no love. help?

[anonymous-matomo-user]

i changed the cli php.ini setting to 2Gb and it appears to be working now, but that's really not a great idea...i fear that the server is going to lock up at some point due to memory usage...

[mattab]

jawrat, maybe check that you have not thousands of unique URLs to store with unique session IDs, etc. you can exclude URL parameters in the settings.

Otherwise, this bug still exists, but very few people need to increase beyond 1Gb

[anonymous-matomo-user]

well, it's been moving along now for the last 8 weeks or so since I switched it to 2Gb and hasn't choked, so for my situation it appears that 2Gb works.

the issue is that there are several thousand unique URLs to store...at any given time, we might have 50k listings active on the site...it's really nice to know which ones are the most active.

btw, thanks for all the effort you guys have put into this...it's pretty awesome (memory issues aside)

[anonymous-matomo-user]

+1

I totally agree with jawrat, both on the issue of you guys doing a great job, but also on the issue that 2GB is too big a memorylimit.

We would really like to run this locally, but having 1,5M+ pageviews....

Hope to see this cleared soon, even before 1.0 ;) But hey! Keep up the good work!

The description of this issue is right to the point, very clear!

[mattab]

Do you have issues with 1.5M pageviews? There are piwik users running that much traffic with 1G memory limit, but maybe your data set is different (eg. more unique URLs to record) ?

[anonymous-matomo-user]

Replying to matt:

Do you have issues with 1.5M pageviews? There are piwik users running that much traffic with 1G memory limit, but maybe your data set is different (eg. more unique URLs to record) ?

Is there a query I can run on the db that will tell me how many uniques there are? i'm sure it's quite a few, but i have no idea how many. matt, contact me offline if you want to poke around...jawrat AT gmail DAHT com

[anonymous-matomo-user]

I have the problem with 2M+ now, maybe a big dataset, so yes, as jawrat suggested:

How do we see this?

...and thank you for the priority-upgrade!

[anonymous-matomo-user]

okay, so I finally had to shut piwik off as it was completely killing the server...mysql was complaining about too many connections (with the max_connections set to 500)...and the dashboard just times out trying to get the data. I wish I had time to dig into the code and help out here, but i'm up to my ass in alligators with a .net codebase that was dumped on me with little to no documentation...

shoot me a heads up matt if you want more info or would like to discuss...we did see a large 20% increase in traffic over the last week or so, so I think that may have been what knocked it over...

[robocoder]

Could we implement an alternate archiving algorithm for resource-constrained environments? (eg use temporary tables in mysql to collect results?) There's a recent forum post from a hostgator user who reports his shared hosting account has max memory limit set at 8M.

[robocoder]

Php+website optimizations: http://ilia.ws/files/zend_performance.pdf

[mattab]

see also a report in #1777

[mattab]

jawrat, can you confirm that you setup the cron archiving? http://piwik.org/docs/setup-auto-archiving/

What kind of traffic did you receive on the day it broke?

[anonymous-matomo-user]

matt, for sure it was set up to auto archive with a crontab.

as far as traffic, we'd just seen a general rise in traffic of about 20% over the course of a week or two. 85k visits, 535k pageviews...

[mattab]

jawrat, did you have the full backtrace of the error?

[anonymous-matomo-user]

unfortunately no, the server fell over when it ran during peak traffic periods. :(

[mattab]

To debug this issue, first it is necessary to upgrade to 5.3 which has better memory management features. Then maybe the FB xhprof could be useful: http://mirror.facebook.net/facebook/xhprof/doc.html

http://pecl.php.net/package/xhprof

[mattab]

(In [3670]) Fix some memory leak, Thanks to XHProf refs

search for top level archiving functions (archiveDay) and sort by inc memory use DESC - more than zero inc memory means mem was leaked sometimes in the call which is a priori not expected refs #766

[mattab]

If we can't fix it completely, at least we should document known limits for memory_limits, and publish examples of large scale setups.

[mattab]

improvement patch posted upstream for zend_db http://framework.zend.com/issues/browse/ZF-10930 - if not fixed in zend we can hack it around in piwik_db

[robocoder]

Looks like the patch was rejected upstream. Because of the inheritance tree, it'll have to be implemented in the subclassed adapters. Something like this?

    /**
     * Prepares and executes an SQL statement with bound data.
     * Caches prepared statements to avoid preparing the same query more than once
     *
     * @param mixed $sql
     * @param mixed $bind
     * @return Zend_Db_Statement_Interface
     */
    public function query($sql, $bind = array())
    {
        static $cachePreparedStatement = array();

        if(isset($cachePreparedStatement[$sql]))
        {
            $stmt = $cachePreparedStatement[$sql];
            $stmt->execute($bind);
            return $stmt;
        }

        $stmt = parent::query($sql, $bind);
        $cachePreparedStatement[$sql] = $stmt;
        return $stmt;
    }

[mattab]

yes patch rejected, since we should reuse the statement objects... which makes sense.

vipsoft, something like this but $cachePreparedStatement[$sql] = $stmt; caches the result of 'prepare', not the query with the bind parameter.

I think we have to copy paste it in all adapters which is a shame, but saves us a boring refactoring ;)

[mattab]

ok your patch is probably right actually, I forgot to look at the zend code again. Looks good to me!

[robocoder]

Do we recommend the latest php version in the FAQ? php 5.3.3 changelog has this entry: Fixed very rare memory leak in mysqlnd, when binding thousands of columns. (Andrey)

[mattab]

OK I updated doc at: http://piwik.org/docs/requirements/

[mattab]

(In [3716]) Refs #766 - performance optimization, caching prepared statements in mysqli and PDO/mysql

[robocoder]

(In [3717]) refs #766 - revert optimization from Mysqli.php (spurious unit test failures and exceptions thrown); tweak code in Pdo/Mysql.php to workaround segfault (now reported as PHP Bug 53716)

[robocoder]

Opened ZF-10943 to track down the cause of the failure in Mysqli.php.

[robocoder]

Closed ZF-10943. It's a dupe. See ZF-2388 instead. (circa ZF version 1.0)

[mattab]

jawrat, would you be able to test trunk on your high traffic website? we have made many improvements to the code, queries and data storage. I see a 30% performance increase in archiving, and more importantly a much lower memory peak usage. This will help pushing the limits and Piwik might work for you now, if you are able to test it would be great :)

How many unique URLs do you track on your website?

[anonymous-matomo-user]

I can do it, but not for the next week and a half or so. our industry trade show is taking place in las vegas next week and the following week i'll be mostly on vacation, so I don't want to disrupt my servers at present. after all the crazy is past, i'll be happy to fire it up and see what happens. let me know what I need to do (svn co or something similar I suppose?) when i'm ready.

[anonymous-matomo-user]

p.s. we're running php5.2 at the moment....I can upgrade but not until after I get back...

[mattab]

jawrat, thanks for that, I'm not sure if it will work well for 500k pages, but definitely good to try. Also, if you can keep the DB dump if it fails, it might be useful for us.

You can grab the nightly build from the link on the QA page: http://piwik.org/qa/

[mattab]

To clarify: we haven't yet improved the 'Tracker log loading' mechanism, and maybe it was the original issue causing 'mysql too many connections' error you experienced.

If Tracking performance was OK before then it's worth trying trunk as it improves Archiving performance and memory usage.

For Tracking load performance improvement, stay tuned on #134 which we hope to have implemented in the next few months.

[anonymous-matomo-user]

okay, but the problem I was having was the memory leak issue in the archive process. when it ran, it just gobbled up all of my available memory until the server fell over (paniced). I think the server in question could probably use more swap space, but i'm not sure about that.

[anonymous-matomo-user]

Is there a way to help testing? I can't run piwik on the high-traffic-sites we have, but if I can help otherwise, let me know, ok?

[mattab]

FYI the current trunk should have a MUCH better memory usage, after the changeset [4239]

So if you are able, to test with TRUNK (which is stable AFAIK) it could be working better for you too! Otherwise you can wait for 1.3 which will be released in a week or so

[anonymous-matomo-user]

I have still the same problems with 1.3 (PHP web host with 64MB memory limit).

[anonymous-matomo-user]

Still the same Problem with Piwik 1.5 and PHP 5.2.14:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in /var/www/web151/html/_statistik.trumpkin.de/core/DataTable.php on line 969

[anonymous-matomo-user]

Still here in Piwik 1.6:

<result date="2011-10-19">174</result>
<result date="2011-10-20">170</result>
<result date="2011-10-21">1</result>

</results> Archiving period = week for idsite = 2... ALERT - script tried to increase memory_limit to 134.217.728 bytes which is above the allowed value (attacker 'REMOTE_ADDR not set', file '/var/www/web151/html/_statistik.trumpkin.de/core/Piwik.php', line 958) PHP Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 24 bytes) in /var/www/web151/html/_statistik.trumpkin.de/core/DataTable.php on line 1022

Fatal error: Allowed memory size of 33.554.432 bytes exhausted (tried to allocate 24 bytes) in /var/www/web151/html/_statistik.trumpkin.de/core/DataTable.php on line 1022

[anonymous-matomo-user]

same here on an installation with 10GB data in database

[mattab]

viegelinsch, what is the exact error message do you get? do you still get it after increasing memory limit to 1G?

[mattab]

If you are using Piwik 1.6 and experiencing this problem, please try to increase your memory limit.

If after increasing your memory limit you still experience the issue, you must have a high traffic website.

Please post here the error message that you get, since it helps locate where in the code the problem arises, which is very important to fix this problem. Thanks!

[anonymous-matomo-user]

Replying to matt and matt:

Yes we run definitely several high traffic web applications and our piwik instance tracks all of these.

I increased memory size to 2G - after that it works again. But this is huge for a php application, isn't it!?

I also deleted all the old detailed logs and only kept the reports. This finally helped piwik back performing well. The sad thing about that - the detailed logs are lost for future detailed analysis.

regards

[mattab]

2G is big indeed, we want to make memory usage better.

Deleting old logs was not necessary, this does not change the memory requirement!! so please don't delete logs unless the disk data size is a problem.

Do you mind putting the limit to 1G and generate the error and paste it here as an attached file to the ticket, with the full backtrace? It would be helpful to understand and hopefully then improve memory usage.

[micw]

I have a site with more visits every week. I upgrade the memory limit every few weeks. ATM we are at 2GB and it fails (I'll upgrade to 3GB). IMO this is a serious issue which will reduce piwik usage to low-traffic websites only. Otherwise one would need a deticated machine only for agregating the stats...

    <result date="2011-11-07 to 2011-11-13">35186</result>
    <result date="2011-11-14 to 2011-11-20">35351</result>
    <result date="2011-11-21 to 2011-11-27">36732</result>
    <result date="2011-11-28 to 2011-12-04">43229</result>
    <result date="2011-12-05 to 2011-12-11">51124</result>
    <result date="2011-12-12 to 2011-12-18">60269</result>
    <result date="2011-12-19 to 2011-12-25">8886</result>

</results> Archiving period = month for idsite = 1... PHP Fatal error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 71 bytes) in /vol/.../stats/piwik/core/DataTable.php on line 1022

Fatal error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 71 bytes) in /vol/.../stats/piwik/core/DataTable.php on line 1022

[mattab]

mwyraz can you please email me the full error message + backtrace at matt att piwik.org ?

[mattab]

As per new findings (see ticket description proposal) I would like to work on this issue sooner rather than later :-)

we can make things a lot better and allow Piwik to be used on higher traffic websites. Required for Piwik 2.0 and by #703