Problem with very large Database


#1

Hi all,

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 :frowning: :


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?

Thanks,

Thomas


(Thomas Seifert) #2

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 .


#3

Hi Thomas,


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?

Thanks,

Also Thomas


(Matthieu Aubry) #4

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 :slight_smile: 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


#5

Hi all,

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 :slight_smile: 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…


(Matthieu Aubry) #6

do you run the OPTIMIZE command after DELETING logs? that should help clearing up space.


(Matthieu Aubry) #7

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.

This is available in the latest 1.5 RC release, check it out now and report if you have suggestions, directly in this post: 301 Moved Permanently