ive got a problem with piwik 0.99 on a webserver. This piwik installation is used for a large community, a few days ago the databased crashed and i spent many hours to repair it (on the server wasnt enough space to repair the db so i had to copy it with scp and so on).
At the moment the size of the repaired database is 116GB, which can be seen here: http://pastebin.com/Z7U06kx1
Ive ran the following commands two times, but the table doesnt want to get smaller :
piwik/misc/cron/archive.sh
DELETE piwik_log_visit, piwik_log_link_visit_action FROM piwik_log_visit INNER JOIN piwik_log_link_visit_action WHERE piwik_log_visit.idvisit = piwik_log_link_visit_action.idvisit AND visit_server_date <= CURRENT_DATE() - 5
optimize table piwik_log_visit
optimize table piwik_log_link_visit_action
Now my idea is to flush the table piwik_log_visit. but is there any data lost if i will exec the command to do this?
Do you have any other ideas?
How many entries does your piwik_log_visit table have?
With clearing that table you wonât be able to âreanalyzeâ existing data. Also all data from the last archive runs would be lost.
I would suggest to follow the FAQ and delete just entries older than a given date as shown in Troubleshooting - Analytics Platform - Matomo .
mysql> SELECT COUNT(*) FROM piwik_log_visit;
+-----------+
| COUNT(*) |
+-----------+
| 372905519 |
+-----------+
1 row in set (0.17 sec)
As shown above the purge-command is executed every day as a cronjob (weve also set the "CURRENT_DATE() - 30" to - 5 days, but the database wont get smaller).
If this helps you, the database grows since five or six weeks, before the cronjob worked perfectly.
And what do you mean with reanalyzing the existing data?
tompal, is there an error when running archive.sh script ?
You say that the DB doesnât get smaller. Does it mean the SQL query to delete rows doesnât work?
In all cases, a very interesting and impressive community size If you can please email me at matt at piwik to discuss your use case. We will be working on making Piwik work better for large users and would be very interested in learning about your uses
as i couldnt find any error-message or something ive deleted the whole database and set up a fresh piwik installation. It was very easy to import the archive-databases, so the graphs are also in the new system.
No, there wasn`t any error (after the database was repaired).
Yeah, the whole database growed at the live-system at about 80GB, the size of 130GB was after i ran a database-repair on a other system with more capacity.
[quote=matthieu]
In all cases, a very interesting and impressive community size If you can please email me at matt at piwik to discuss your use case. We will be working on making Piwik work better for large users and would be very interested in learning about your uses[/quote]
Yeah, i`m administrating a, in germany, well known online-gameâŚ
It looks like im running into the same problem as before, i will watch the behaviour of the cronjob todayâŚ
The feature of automatically deleting old older than 7/30/N days is now available in Piwik, under Settings > Privacy > Delete old logs from the database.
:
If you can please email me at matt at piwik to discuss your use case. We will be working on making Piwik work better for large users and would be very interested in learning about your uses
If you can please email me at matt at piwik to discuss your use case. We will be working on making Piwik work better for large users and would be very interested in learning about your uses[/quote]