Reduce/limit database size

Support & Bugs
1

Hi,

I just received an email warning from my host, GoDaddy, that my Piwik SQL database exceeded their size limitation of 1GB.

I run:

  • WordPress blog
  • on GoDaddy
  • using Piwik 2.1.0
  • else, standard default settings

I know GoDaddy is not considered a good hosting option but that is a different topic.

I used the Privacy Tab to delete older visitor logs and reports, saved and manually purged the database.
It said that my database was 3.2GB before the purge and it is now 3.1GB after the purge.
I tried unticking even the most basic default (“recommended”) boxes and reduce the time frame but it only helps marginally with a further reduction of 0.1GB…

GoDaddy requires that I reduce that SQL DB file to below 1.0GB.

Why did my DB only go down from 3.2GB to 3.1GB?
Anything I miss here?

Any other tricks to reduce (and keep it smaller) the size of the DB?

Otherwise, I’d have to say Piwik a sad goodbye…

Thanks

2

you may just need to restart mysql to release the storage - not familiar with how piwik purges it, but mysql may not be reflecting the purge on the filesystem

3

GoDaddy really sucks, because their CEO is not a nice person. So please consider moving from them to anoter more responsible host :slight_smile:

4

Btw maybe the size is not showing as “decreased” because the tables need to be OPTIMIZE’d first. You could either run the OPTIMIZE table commands or wait few days and the size should be less.

5

Thanks Siivv - what do you mean by saying “to restart mySQL”? To drop all tables?
That’s an option but I am looking for a better solution that avoids losing all of the reports from the last year or so since I have installed Piwik.

Thanks Matt - I know GoDaddy sucks big time but I have a few good reasons to stick with them for now and I was trying to avoid that topic for now…
Coincidentally, it is not their fault this time…

I will try to optimize and wait and then revert with results.

Thanks to both of you!

6

i meant just stop/start the service - not sure how it’s managed via godaddy
linux command line as root

service mysqld restart

7

Thanks Matt and Siivv!

Matt’s suggestion (optimize and wait) worked for me and my DB is much smaller and faster now.
The Optimize command did the trick immediately.

Thanks again!

8

Have you enabled the Piwik plugin DBStats (Core)?

And if so have you set up to purge old visitor stats? Because this can all be done and saves lots of spaces and if done correctly won’t loose important data.

9

Until GoDaddy smashes you again. There’s a reason why EVERYONE in the web community says not to use GoDaddy for hosting. All their horrible rules which either can’t be solved or can only be solved with more and more $$$.

Seriously, find a better host to use.

10

locally - yes I set up the deletion of old visitors and reports.
And, now installed the DBStats (which saves me the visit to my phpMyAdmin console).

Anyhow, by now my DB is <50MB so all back to normal and healthy shape.

11

Running into the same issue. Found that table piwik_log_link_visit_action is having 6.4G. Talked to my hoster, who said that InnoDB tables won’t shrink, when content inside is deleted.

My Piwik tracks about 10 sites having between almost no traffic and about 1000 hits/month. Some have been added over time, some have been deleted again. Some data has been ported over some Piwik versions since 2012. What would be the estimated round about size of such a database?

I do have setup automatic deletion for both old logs and old reports with default entries, but just for a couple of days, since the hoster sent the warnings about full quota (it’s not godaddy!)

How can I make piwik massively shrink the database size? Can I also manually delete or clear data from the table within mysql?

12

If you use mysql, make sure you run mysqlcheck on your database, it will shrink your files.