This is typically the result of one of your clients using a poorly-designed third-party script to poll the SHOUTcast/IceCast server's audio feed at excessively frequent intervals. As such requests for the audio feed are indistinguishable from legitimate visitor tune-ins, they are added to Centova Cast's statistics database table as visitor sessions.
As these scripts often hammer the server as frequently as multiple times per second, the statistics database tables end up being filled with bogus, 1-second sessions, thereby dramatically increasing the size of the MySQL data files and reducing the performance of MySQL when reading these tables.
Identifying the responsible account requires familiarity with MySQL and running queries from the MySQL console (or a tool such as phpMyAdmin).
To identify the problem account(s), run the following query:
SELECT COUNT(*) AS sessions,accounts.username,accounts.id AS accountid FROM visitorstats_sessions LEFT JOIN accounts ON visitorstats_sessions.accountid=accounts.id GROUP BY accounts.id ORDER BY sessions DESC LIMIT 10;
This will return a list of the top 10 accounts according to the number of visitor sessions on file for each account. Typically, just one account (or possibly two) will stand out at the top of the list with several million sessions, and this will be your problem account(s). The remainder of the accounts will typically show dramatically fewer sessions -- often only tens or hundreds of thousands of sessions (or less) -- which represent normal usage.
Once you know which account is causing problems, you can proceed to delete the statistics for that account. Make note of the accountid for the problem account from the above query.
To delete the excessive data for the problem account, run the following query:
DELETE FROM visitorstats_sessions WHERE starttime<DATE_SUB(NOW(),INTERVAL 1 MONTH) AND accountid=[ACCOUNTID];
Replace [ACCOUNTID] with the accountid for the problem account, which you made note of earlier.
Note:
This will only remove the existing data; it will not stop the client from continuing to hammer the SHOUTcast server with requests, so you should also contact the client and ask him to fix his script to avoid having to repeat this process every few weeks.
Finally, note that MySQL will not actually shrink its data files after running the above query, so they will still remain several gigabytes in size. If you want to reclaim the unused space, you can run:
OPTIMIZE TABLE visitorstats_sessions;
Note:
Table optimization will take a substantial amount of time to run, and may cause errors on the Centova Cast statistics pages while it's running. After the optimization completes, however, the errors will disappear and everything will work normally.
