I'll respond to everyone at once here... brace yourselves.
My centova database is 2396.82542610 MB in size.
It's only been running since the beginning of 2014, but the visitorstats_sessions table is 2202.52 MB, and the playbackstats_tracks table is 193.59 MB.
That's absolutely outrageous for the visitorstats_sessions table and indicates that your users are hitting your server with poorly-designed scripts or somesuch. The link AlexiuB provided is definitely relevant to this.
If after running those queries (including the OPTIMIZE TABLE query at the end) you don't see the visitorstats_sessions table shrink to a fraction of that 2.2GB, it likely contains some other form of "pollution" that was pulled in from the SHOUTcast/IceCast logs. If you contact the helpdesk about this, we can help identify the problem. (And yes, I know this is irrelevant to you now as you said you ran a DELETE query in a subsequent post; this is just general information for anyone reading.)
This is a problem for me also, and has been for a while, some of my servers have databases so big, backups & migrations usually fail and I must import using another method like big dump.
The solution depends on which tables are large. Really can't help without knowing that. If it's the visitorstats_sessions table, and it's disproportionately huge compared to the other tables (like isaacl's situation) then you, too are being hit by bad scripts and need to have the table cleaned up.
I think the optimization process runs on a cron, or at least it should ...
Bear in mind that optimizing the tables does just that -- it optimizes them, via MySQL's OPTIMIZE TABLE query. Essentially, it performs "garbage collection" for lack of a better term -- freeing up disk space from data that has been marked as deleted via DELETE queries. It does NOT (and cannot) remove bogus data, because Centova Cast has no way of knowing what's bogus and what's not.
I agree with the others here, we started to see the database get too large and it was slowing down the generation of stats pages.
The only time I've *ever* seen that happen is (again, not to sound like a broken record) with server abuse showing up in the visitorstats_sessions table.
To resolve this, we've enabled the option so that listener sessions less than x seconds (5 seconds in our case) are not stored, as these usually indicate stat checking script
Absolutely the best option (short of educating the users, although obviously that's difficult and may be bad for business) to curb this kind of abuse.
Doing that, and removing all sessions that were less than 5 seconds helped a lot
It should do. I've seen many cases where doing this, and then optimizing your database, can cut the database down to literally 1/10th or less of its original size.
but I still think there is a lot of DBA that centova could be doing for us as part of its existing db crons e.g. pruning data older than x months (make this a configurable option for admins).
If you have suggestions, I'm all ears. Removing old statistics is most certainly an option we could add; admittedly, it was my belief that clients would never use it as end-users would demand access to their historical stats. I've been wrong at least once or twice before, though.
BTW, before anyone shoots back "but it's better to have less history than having the queries time out", bear in mind that they *shouldn't* ever time out, nor even markedly slow down, if your visitorstats_sessions table is healthy. We've invested considerable hours in optimizing our SQL queries and database schema over the years to eliminate bottlenecks, and in all but the most massive installations with many tens of thousands of concurrent listeners, every part of the UI should load in a very reasonable amount of time.
I'm not sure on this one, but making sure database entries for deleted accounts are all removed, e.g. track data and playlists, maybe it is, but I swear I've seen stuff from old accounts that aren't on the server!
Older versions (in the 2.x era) had some bugs of this type, but when we implemented the database backup/restore functionality I noticed that a few tables weren't being cleaned up properly. That was fixed before 3.0 was released, and due to the way the account importer works, it shouldn't have been pulled into v3 from the v2.x database at all. At this point, AFAIK we aren't leaving any data behind when accounts are terminated (short of situations where the database itself is damaged and a deletion query fails).
Consolidating older stats into monthly figures or something along those lines could help too.
That's certainly an option too, and one that I've considered. The problem I had with it was that it'd require basically generating static reports (one per month, and maybe one per year) which eliminates the user's ability to zoom/pan/etc. in the graphs over a period of time. It'd just become a set of static charts. Want to see performance over a 6-week period in 2014? Sorry, no can do. It'd also require a separate statistics viewer for the consolidated stats, as so much of the functionality would be reduced.
That's not to say I wouldn't consider it, just that I fear backlash with changes like this.
Huge database