Hi there,
we are tracking two sites on our piwik server. Actually everthing works well - but there is a problem with project 2 when archiving year report. First some stats of the project
- Tracking since 1,5 years
- Database is 6 GB
- Tables
- piwik_archive_blob are between 2MB and 180MB of size
- piwik_archive_numberic is between 11KB and 500KB of size
- piwik_log_link_visit_action is 1,7GB
- piwik_log_visit ist 1,8GB
- piwik_log_action is 80MB
- Memory (RAM) of server is 3GB (for PHP and PHP-CLI)
- Apache 2.2 and MySQL 5.1 and PHP 5.3
- Latest piwik version v1.12
- Using archive.php (also tried archive.sh)
Tracking statistics:
website 1:
- 60000 visits / month
- 4 actions/visit
website 2:
- 200000 visits / month
- 3 actions/visit
Archiving log:
…
[2013-06-05 07:15:01] [5a91bd2b] - Archiving was last executed without error 59 min 57s ago
[2013-06-05 07:15:05] [5a91bd2b] Will process 2 websites with new visits since 1 hours 0 min , IDs: 1, 2
[2013-06-05 07:15:05] [5a91bd2b] ---------------------------
[2013-06-05 07:15:05] [5a91bd2b] START
[2013-06-05 07:15:05] [5a91bd2b] Starting Piwik reports archiving…
[2013-06-05 07:15:06] [5a91bd2b] Archived website id = 1, period = day, Time elapsed: 1.104s
[2013-06-05 07:15:09] [5a91bd2b] Archived website id = 1, period = week, 15874 visits, Time elapsed: 2.472s
[2013-06-05 07:15:14] [5a91bd2b] Archived website id = 1, period = month, 59355 visits, Time elapsed: 4.680s
[2013-06-05 07:15:41] [5a91bd2b] Archived website id = 1, period = year, 882080 visits, Time elapsed: 27.221s
[2013-06-05 07:15:41] [5a91bd2b] Archived website id = 1, today = 230 visits, 4 API requests, Time elapsed: 35.480s [1/2 done]
[2013-06-05 07:15:43] [5a91bd2b] Archived website id = 2, period = day, Time elapsed: 2.546s
[2013-06-05 07:15:52] [5a91bd2b] Archived website id = 2, period = week, 48453 visits, Time elapsed: 8.368s
[2013-06-05 07:16:04] [5a91bd2b] Archived website id = 2, period = month, 192842 visits, Time elapsed: 12.483s
-> I skipped year report on purpose!!!
[2013-06-05 07:16:04] [5a91bd2b] Archived website id = 2, today = 609 visits, 3 API requests, Time elapsed: 23.398s [2/2 done]
[2013-06-05 07:16:04] [5a91bd2b] Done archiving!
[2013-06-05 07:16:04] [5a91bd2b] ---------------------------
[2013-06-05 07:16:04] [5a91bd2b] SUMMARY
[2013-06-05 07:16:04] [5a91bd2b] Total daily visits archived: 839
[2013-06-05 07:16:04] [5a91bd2b] Archived today’s reports for 2 websites
[2013-06-05 07:16:04] [5a91bd2b] Archived week/month/year for 2 websites.
…
As you can see in the log above I skipped archiving year report for website 2 (I did a change on archive.php), because when trying to archive year report (of website 2) the memory (RAM, 3GB) runs full and archiving never finishes or is terminated.
I can’t find the reason why when archiving the year report the memory gets exhausted, because the day, week and month reports are okay. Also the year report is fine for website 1. Memory gets exhaused on DataTable.php.
I tried to debug and profile, but can’t find the problem/error. I think the configuration and hardware ressources (based on the amount of visitors) should be good. Also 3GB of RAM should be enough.
I would greatly appreciate every advice that could help me with the mentioned problem. What can I do to debug? I can’t find any errors on php or apache logfiles.
Thanks in advance.