Centova Cast's statistics pages load slowly or time out
Question:
When accessing Centova Cast's statistics pages, they load extremely slowly or display a Gateway timeout error message.
-or-
My visitorstats_sessions database table is several gigabytes in size and won't stop growing.
How do I fix this?
Answer:
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 that 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 that 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.
Can't Centova Cast ignore requests made by scripts of this nature?
A properly-designed script should not connect to the SHOUTcast/IceCast server's audio feed to test for connectivity or poll for status information; rather, it should simply query the SHOUTcast/IceCast status pages for such information.
Centova Cast does not treat status page hits as visitor sessions, so such hits will not cause this problem. However, if a third-party script requests the SHOUTcast/IceCast server's audio feed, there is no way for Centova Cast to distinguish it from a legitimate audio player attempting to tune in to the stream. Accordingly, such requests are interpreted as visitor sessions and are thus added to the statistics tables.