The main reason that people stop using Piwik on small websites is because the DB grows out of control. Often users have 25Mb or 50m for the whole Mysql database. After installing the CMS, it is common for Piwik to have only 20M of space. We should try to make it easy to run Piwik with basic history and keep DB space below 20M.
Proposal "Lightweight Piwik" feature
Note: it would be very nice to also get rid of #2805 - just one SQL query away after all...
PS: and this one too #3003
V2 of this feature (to be put as "feature request" ticket)
Attachment: Proposed modifications
proposed_mods.png
Attachment: Patch for this issue. (1st)
53.diff.tar.gz
Attachment: Patch for this issue. (2nd)
53.diff.tar.2.gz
Updating description for proposal first version. I think this would be extremely useful to a LOT of Piwik users! This would fix a major complaint with Piwik: DB size.
I'd like to implement this by modifying the 'Delete old visitor logs from database' section. I've attached an image laying out what the changes would be. Some notes:
What do you think?
Along with a few other key feature we're currently working on, I am confident this one in particular will have a HUGE impact. Because, it is in the top 3 critical feedback, that Piwik uses too much DB size. Many shared host will be relieved with this. Great that you're working on this amazing feature!!
Review:
"Estimated DB size...' could be 'Database size'
When the setting is enabled, we don't know how many rows are "older" than N days. I'm not sure how we can estimate how much data will be saved. I guess we could run a SQL query to get an estimate, but not sure if it's useful just for this help message.
Should we include piwiklog* approx DB space in the estimate?
UX Request:
Just attached a patch for this issue.
Some notes:
Let me know what you think.
Great work cappedfuzz! Nice to read...
Here is the Code & UI Review:
Otherwise code looks very good!!
'Purge DB now' link
I would say the link should always be visible when the feature is enabled. Maybe open in a new window target=_blank as to make sure user can still use Piwik?
Some questions:
Replying to matt:
Great work cappedfuzz! Nice to read...
Here is the Code & UI Review:
- en.php: when changing the text, please also update the translation key: this will force translators to translate again (otherwise they will not see that the string was updated)
- 'CoreHome_ThereIsNoDataForThisReport' should not changed
- 'CoreHome_ThereIsNoDataForThisReport' "data may have been purged" message, should only be displayed when the feature is enabled. By default, reports should still display only the simple current message.
- Also it should only display when the selected date is affected by the Report deletion. For example, only when looking at reports older than 6 months will the message say "No data may have been purged"
There's an issue with this since the purge settings can be changed. So it could be set to reports older than 6 months, a purge could run, and it could be reset to reports older than, say, 8 months. In which case, some reports won't have the message when they should. I think, though, that this can be solved w/ the following:
If the table for the report does not exist, then it has been purged.
If the table does exist, but has a special entry (where idsite && idarchive == -1 or some other special number), then rows have been deleted and data has been purged.
This should be more accurate and keep purging effective (as opposed to not deleting rows).
- the Click here with html link is replaced by Click %s here %s which are then replaced by HTML in the templates
- for code style, in getReportPeriodsToKeep, it could be an array then a loop testing all indexes and assigning
- In the JS modifications, Please add one comment above the main new pieces of code, it helps separate the concerns a bit
- Defaut delete_reports_older_than should be at least 12 months
- would it be possible to have Delete logs and Delete Reports as 2 separate scheduled tasks? this would help lower the time spent in each task
Otherwise code looks very good!!
'Purge DB now' link
I would say the link should always be visible when the feature is enabled. Maybe open in a new window target=_blank as to make sure user can still use Piwik?
Actually, I think over-thought. An AJAX request would work.
There's an issue with this since the purge settings can be changed. So it could be set to reports older than 6 months, a purge could run, and it could be reset to reports older than, say, 8 months. In which case, some reports won't have the message when they should.
I think it's an acceptable solution. I propose that the message is displayed, only for dates older than the current deletion threshold. It is just an estimate anyway. The good thing is not to show the message to normal users, or for recent dates :)
Actually, I think over-thought. An AJAX request would work.
Yes it would, if you can please use the standard "Loading data..." helper during the request (or maybe even a better message "Deleting old reports, please wait..."
Attached another patch for this issue. Let me know what you think of it.
Good work Benaka!! :)
Code review:
+ // perform checks on prediction
+ $janPeriodCount = 5 + 4 + 1 + 1;
+ $febPeriodCount = 6 + 4 + 1;
----
+ const JAN_PERIOD_COUNT = 37; // 31 + 4 + 1 + 1;
+ const FEB_PERIOD_COUNT = 34; // 29 + 4 + 1;
---
+ $expectedPrediction = array(
+ Piwik_Common::prefixTable('log_conversion') => 6,
+ Piwik_Common::prefixTable('log_link_visit_action') => 6,
+ Piwik_Common::prefixTable('log_visit') => 3,
+ Piwik_Common::prefixTable('log_conversion_item') => 3,
+ Piwik_Common::prefixTable('archive_numeric_2012_01') => -1,
+ Piwik_Common::prefixTable('archive_blob_2012_01') => -1
+ );
Idem in test_purgeData_deleteReportsKeepBasicMetrics and other tests
If you can apply these last feedback then commit it would be great! Cool feature to have.. We will have to do a bit of marketing around it.
(In [6174]) Fixes #5473. Augmented the log data deletion feature. Added the ability to purge old reports and metrics.
Other changes:
(In [6175]) Refs #5473, UI & security tweaks along w/ one bug fix ('delete_logs_max_rows_per_query' was not set in getPurgeSettingsFromRequest).
(In [6176]) Refs #5473, show 'data was purged' message for every user type, not just super user.
Commit [6177] deals with this ticket. (forgot to add the Refs)
(In [6179]) Refs #5473 labels html
I just noticed the metrcs picker is not working on the visitors overview anymore. I guess this problem has been introduced in [6174].
The problem is that Piwik_Date::factory doesn't recognize a date.
This is the backtrace (from the XHR response):
<a href='/0'>#0</a> /Users/timo/Sites/piwik/svn-trunk-git/core/ViewDataTable.php(1205): Piwik_Date::factory('2009-02-01,2011...')
<a href='/1'>#1</a> /Users/timo/Sites/piwik/svn-trunk-git/core/ViewDataTable/GenerateGraphHTML.php(133): Piwik_ViewDataTable->hasReportBeenPurged()
<a href='/2'>#2</a> /Users/timo/Sites/piwik/svn-trunk-git/core/ViewDataTable/GenerateGraphHTML.php(97): Piwik_ViewDataTable_GenerateGraphHTML->buildView()
<a href='/3'>#3</a> /Users/timo/Sites/piwik/svn-trunk-git/core/Controller.php(241): Piwik_ViewDataTable_GenerateGraphHTML->main()
<a href='/4'>#4</a> /Users/timo/Sites/piwik/svn-trunk-git/plugins/VisitsSummary/Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins('VisitsSummary', 'getEvolutionGra...', Array, Array, 'Dies ist eine ?...')
<a href='/5'>#5</a> [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
<a href='/6'>#6</a> /Users/timo/Sites/piwik/svn-trunk-git/core/FrontController.php(138): call_user_func_array(Array, Array)
<a href='/7'>#7</a> /Users/timo/Sites/piwik/svn-trunk-git/index.php(53): Piwik_FrontController->dispatch()
(In [6195]) Refs #5473, fix issue of failure in purged report message logic when a set of dates is requested.
@EZDesign Just committed a fix for the problem, the metrics picker works for me now. Let me know if you still encounter a problem.
(In [6197]) Refs #5473 reusing a function
There is a similar bug with date=last7 reported in #3107
Thanks for the good work, capedfuzz!
I have a request for this feature. I hope you can do it...
Piwik creates archives for every custom date range you pick. It would be cool if there would be a setting to only delete those archives - keeping days,weeks,months,years and also segments intact. There could be a similar setting do delete segments and keep days,weeks,months,years but I would only need the option to delete period=range archives.
Is that possible?
@EZDesign Yes, it's possible, and likely easy to do. I should be able to get to it soon.
(In [6218]) Refs #5473, Added ability to purge range reports and ability to keep segment data for saved reports.
Other changes:
(In [6219]) Refs #5473, fixes build?
(In [6220]) Refs #5473, removed Piwik_FetchCol function as mysqli seems to have a problem w/ fetchCol.
(In [6557]) Refs #5473, show DB purged message after successful purge.
(In [6559]) Refs #5473, internationalize db purged message.