Category: Computing

All I.T. related rants/posts.

Counting the Icecast stream eyeballs

I’m at home. And battling to cover costs of moving home, finishing renovations (garden), and playing breadwinner for wife and child. Despite working, finances weren’t there in time for me to attend the always fabulous Linux.conf.au in Brisbane this year, much to my disappointment, so I stayed home and, around work, watching the video streams.

Let’s start with a big thank you to the guys and girls who worked the A/V kit; ensured audio was there, video was focused and framed, slides were pulled in, and streams happened.

Icecast admin interface example
The Icecast administration interface

Which got me to thinking; how many people are watching? All very nice to know after the show, but what about real time, so that the director, team and even presenter knows where there audience is. Each icecast server does show this information when the server’s admin logs in. Neat.

However, to distibute content, you need a lot of severs to distribute the streams, and quite probably, these servers are being run by Other People, who probably don’t want to give you their admin password to log into their icecast server.

We need a way of having something take the values from the admin interface, and expose that so we can collect these statistics.

Thus I scribbled down two scripts: one a CGI script to fetch the data from the local icecast server and serve it as a CGI in JSON format, and a second to collect multiple instacnes of this CGI’s output in parallel.

The first script would be run by the same admin who runs the remote icecast server, as the script needs to have the icecast admin password. It uses XPath expressions to fetch relevent pieces of information, and return it as JSON, a pretty compact format.

The second currently just fetches this data and displays, it, but with a little more hacking it could pump these results into an RRD, thereby giving us the option of graphing the results.

It’s very much a poll, and probably something to repeat every 15, 30 or 60 seconds. It would need to dynamically handle the adding (and removing) of individual icecast servers as they are brought online. But it’s a start.

Code can be found in my pseronal SVN repo.

Brought to you by the Perl modules CGI, JSON, HTML::Treebuilder::XPath, WWW::Curl::Multi, and others.

While I’m here, I think next year we should arrange to have an Icecast server in each city of Australia. If I had to pick one issue, I was having to connect to the US or European relays when the one public one at UQ was full or “having a moment”. I’d urge each LUG around the country to, if they have a server on decent bandwidth, offer to relay the streams; if not publicly, then at least to their members.

MySQL Partitioning Automation

This is one cool post.

It’s a shame that MySQL doesnt have the ability to automatically add partitions based upon date. For example, if result of a function should generate a new partition:

PARTITION BY YEARWEEK(MyDateTimeCol)

And hey presto, a bunch of new partitions would appear every time you insert new data thats in a new range. YEARWEEK() may be too course for your massive amount of data – perhaps TO_DAYS(). Perhaps it’s not that massive a data set, so just YEAR() would suffice. But having the new partitions created upon INSERT of data that matches would be great. As would deletion of partitions when a partion is empty. Nice bit of housekeeping.

Extracting IPv4 addresses from IPv6 in MySQL

Here’s a stored procedure that will let you find an IPv4 address embedded inside an IPv6 address, as a stored procedure in MySQL:


DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `IPv4_from_IPv6`(IPv6 varchar(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
# Split into blocks
DECLARE Block1, Block2, Block3, Block4, Block5, Block6, Block7, Block8 varchar(4);
DECLARE IPv4 varchar(255);
SELECT SUBSTRING_INDEX(IPv6, ":", 1)                           INTO Block1;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 2), ":", -1) INTO Block2;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 3), ":", -1) INTO Block3;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 4), ":", -1) INTO Block4;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 5), ":", -1) INTO Block5;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 6), ":", -1) INTO Block6;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 7), ":", -1) INTO Block7;
SELECT SUBSTRING_INDEX(IPv6, ":", -1) INTO Block8;
SELECT INET_NTOA(CONV(concat(Block7, Block8), 16, 10)) INTO IPv4;
IF (Block1 <> 0)
THEN
RETURN NULL;
ELSEIF (Block2 <> 0)
THEN
RETURN NULL;
ELSEIF (Block3 <> 0)
THEN
RETURN NULL;
ELSEIF (Block4 <> 0)
THEN
RETURN NULL;
ELSEIF (Block5 <> 0)
THEN
RETURN NULL;
ELSEIF (Block6 <> "FFFF")
THEN
RETURN NULL;
END IF;
RETURN IPv4;
END $$

Very handy.