[Icecast-dev] Icecast and MySQL Stats

Alejandro cdgraff at gmail.com
Thu Mar 28 07:51:44 PDT 2013

Hi Guys,

Here my script than I use with very big streaming site, for parse the logs
and insert into batch process the information into the Mysql DB


We need to do this into external process for the big number of concurrent
users we have, currently more than 80k on peak an average of 50k handle
this on realtime, affect the connection time performance.

I hope this be interesting to anything else.


2013/3/28 Luca Cireddu <sardylan at gmail.com>

> Hi guys,
> thanks for your answers...
> My idea was to create a simple system that can be linked to radio schedule.
> To avoid problems modifing a working code, I decided to do less
> modifications as possible by simply adding a call to 2 functions, one to
> add a listener and one to remove a listener. Using only 2 tables on db I
> can obtain all informations.
> One table, called "online", contain a "mirror" of the online status that
> can be browsed in the admin section of icecast web interface. It is
> composed by 5 fields: id, ip, agent, start and mountpoint. Each time a new
> listener connects, icecast adds a record with a numerical ID, the same ID
> used to identify the connection on the source code. The "start" field
> contains the unix timestamp, filled by icecast. "Agent" is the user-agent.
> When a listener closes the connection, my function remove the record from
> the online table (using the ID to choose the right one) and put a new
> record in the second table,  called "stats", which contains 7 fileds: "id",
> "ip", "agent", "mount", "start", "stop" and "duration".
> Many of the fields are taken from the online table, adding the "stop"
> timestamp, ancd counting the difference in seconds to fill the "duration"
> value.
> Once you have data in the "stats" table, you can simply parse them with a
> PHP page... You can order them by duration or start time, but most
> important is that you can simply have the medium time a user stay online.
> You can also have the medium calculated per-hour. You are also able to
> exclude several IP address (local connections or relay servers).
> All informations are directly created real-time by icecast itself, so you
> don't have to query the server each 5 or 10 seconds. Using a DB, you can
> also have different servers for streaming and stats, and if you like you
> can simply integrate some information in existing web sites.
> I made also few modifications to integrate mysql configuration params in
> icecast.xml file.
> About source code there is a little problem. I've start working on the
> code on-the-fly because I thought to create only one file, so I'm not using
> any of the versioning system like git.
> How can I upload it? I think that the best solution could be to checkout
> an existing repository and re-apply all modifications, but it may take a
> lot to complete.
> I considered to produce a patch using diff, but there are a lot of
> difference becasue I needed to recreate some files using autoreconf.
> I will upload a tar archive of my version as soon as possible in my
> personal site, and I will share the link in this mailing list, so you can
> start to take a look in source code.
> I used SVN several times, but I'm newbie in git. :)
> --
> Luca Cireddu
> sardylan at gmail.com
> http://www.lucacireddu.it
> _______________________________________________
> Icecast-dev mailing list
> Icecast-dev at xiph.org
> http://lists.xiph.org/mailman/listinfo/icecast-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.xiph.org/pipermail/icecast-dev/attachments/20130328/0c5644fb/attachment-0001.htm 

More information about the Icecast-dev mailing list