Exporting playlists from mythmusic

In my new house I don’t have any free to air TV – reception is too poor.  So my mythtv installation is less useful than it could be – it’s primarily a setup that records TV.  It also plays music, but it’s not inspiring at it.

My frontend died and needed replacing, and after fighting for a while I couldn’t get LIRC/remote controls going with the new hardware.  I looked around and Kodi (an evolution of XBMC) looks to have a really good remote app that goes on my iPhone.  Way better.

I’ve installed Kodi and it’s working well after some fiddling around.  But I had a lot of music playlists in mythtv.  I want them in Kodi.  Kodi can import standard m3u playlists, mythtv doesn’t have a standard export for them.

After googling around a bit, I decided to write a perl script that does it.  Without further ado, here it is for anyone that might need such a thing.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# MySQL database configuration
my $dsn = "DBI:mysql:mythconverg";
my $username = "root";
my $password = 'Massey01';

# path prefix - haven't dealt with storage groups, so set this to your storage group path
my $prefix = "/usr/share/mythtv/server/mp3/";

# connect to database
my $dbh  = DBI->connect($dsn,$username,$password);

# create temporary table for holding song list
my $sql = "CREATE temporary table tmp_playlist_songs (
  playlist_id int(11),
  playlist_name varchar(255),
  song_id int(11)
)";
my $create_table = $dbh->prepare($sql);

# create the table
$create_table->execute();
$create_table->finish();

# setup the insert statement ready for use
$sql = "INSERT INTO tmp_playlist_songs( playlist_id, playlist_name, song_id )
        VALUES (?,?,?)";

my $insert_stmt = $dbh->prepare($sql);

# select all playlists from the database
$sql = "SELECT playlist_id,
               playlist_name,
               playlist_songs
           FROM music_playlists";
my $get_playlists = $dbh->prepare($sql);
# execute the query
$get_playlists->execute();

my $playlist_id;
my $playlist_name;
my $playlist_songs;

# iterate over each playlist
while( my @row = $get_playlists->fetchrow_array() ) {
  $playlist_id = $row[0];
  $playlist_name = $row[1];
  $playlist_songs = $row[2];

  my @songs = split( ',', $playlist_songs );

  # insert the songs into the temporary table
  foreach my $song ( @songs ) {
    $insert_stmt->execute($playlist_id, $playlist_name, $song);
  }

  # select out the song filenames
  $sql = "SELECT path, filename FROM
            tmp_playlist_songs,
            music_songs,
            music_directories
          WHERE
            tmp_playlist_songs.song_id = music_songs.song_id AND
            music_songs.directory_id = music_directories.directory_id";
  my $get_playlist_detail = $dbh->prepare($sql);

  $get_playlist_detail->execute();

  open my $file_handle, ">$playlist_name.m3u";

  while( my @song_row = $get_playlist_detail->fetchrow_array() ) {
    print $file_handle $prefix . $song_row[0] . '/' . $song_row[1];
    print $file_handle "\n";
  }
  $get_playlist_detail->finish();

  close $file_handle;

  # clear out the temporary table
  $sql = "DELETE FROM tmp_playlist_songs";
  my $delete_playlist = $dbh->prepare($sql);
  $delete_playlist->execute();
}
$get_playlists->finish();
$insert_stmt->finish();

# disconnect from the MySQL database
$dbh->disconnect();

Hope it’s useful for someone.

Advertisements

RAID performance again

Further information in my saga.

On my main server, hosting my media centre / myth setup, I am still having issues.  I’m pretty sure that the recording dropouts that I’m getting are directly correlated to performance, I’ve gotten rid of the errors that I had with the recordings, so I’m now left only with a series of the following errors:

2013-02-07 14:41:44.538606 W [10163/15173] TFWWrite ThreadedFileWriter.cpp:500 (DiskLoop) - TFW(/usr/share/mythtv/recordings/6008_20130207030900.mpg:85): write(57716) cnt 19 total 1087956 -
- took a long time, 1568 ms

This is then sometimes followed up with the following, and seems to be correlated with a recording dropout (which makes sense given the error message):

2013-02-06 10:18:35.013307 E [10163/1005] HDHRStreamHandler ThreadedFileWriter.cpp:217 (Write) - TFW(/usr/share/mythtv/recordings/6030_20130205231500.mpg:97): Maximum buffer size exceeded.
                        file will be truncated, no further writing will be done.
                        This generally indicates your disk performance 
                        is insufficient to deal with the number of on-going 
                        recordings, or you have a disk failure.

Clearly IO performance is an underlying issue here.

I note that when recording myth can be quite hungry on disk – it’s often recording 3-4 streams or more (since a number of channels simulcast shows – usually one in HD, and a couple of random SD ones, and they have subtly different program listings so myth often doesn’t notice that they’re duplicates), and it then starts up a commercial flagging job for each in parallel, reading it back and scanning for ad breaks.  Overall it’s still only doing about 15MB per second, but it seems to exceed the current array performance.

In the near term I am reducing the number of parallel  commercial flagging jobs, reducing the likelihood of contention, but I still need to fix array performance.

Overnight I pushed my testserver into single user mode, on the theory that perhaps my variation in results was driven by other jobs running on the machine.  On a debian server you do this with “init 1”.  I also learned that you don’t do this with “init 0” as that’s very similar to shutdown -h.  I now have very consistent and repeatable results, which is great, and they look something like this:

Minimum RA
1024 2048 4096 8192
Stripe 256 63.16% 68.30% 61.17% 63.73%
512 60.43% 61.54% 65.05% 81.71%
1024 83.11% 71.06% 80.88% 70.16%
2048 68.76% 83.61% 79.30% 70.35%
4096 76.21% 56.78% 76.64% 78.82%
Average RA
1024 2048 4096 8192
Stripe 256 78.39% 86.11% 79.89% 81.64%
512 88.22% 89.05% 89.40% 92.11%
1024 92.35% 87.68% 90.50% 88.55%
2048 93.46% 94.63% 94.12% 92.63%
4096 90.89% 86.88% 90.02% 90.49%

This tells me I’m getting little variation from read ahead, but a reasonably clear minimum threshold for stripe cache.  The ideal point looks to be around 1024/1024 or 2048/2048.  Of course, looking further into it, I have question about whether my read ahead setting is having effect, and I note there is also a read ahead setting on the LVM.  So I will also try tuning that to see if I get more variation.  I also have a little concern that bonnie++ might not be a fully representative load, so I may try another load testing tool as well to check I have the right process.  Or maybe run bonnie++ in multi-threaded mode.  But, overall, progress is being made.

It still leaves me questioning why one server regularly produces results around 200MB/s, and the other seems stuck at 15MB/s.  That is really my underlying problem to resolve.

Raid performance still poor

So last night having some problems with recordings, and some in the house are rather unhappy that reality TV isn’t getting recorded successfully.  The recording is cutting out about 15 minutes in.

From the logs I can see a few things:

1. I’m getting lots of log entries like these:

2013-02-04 21:33:20.556120 W [21378/14006] RecThread ThreadedFileWriter.cpp:302 (Flush) - TFW(/usr/share/mythtv/recordings/6006_20130204082002.mpg:88):
Taking a long time to flush.. buffer size 6333344

2. I get an error like this when the recording aborts:

2013-02-04 21:33:47.499270 I [21378/21388] TVRecEvent tv_rec.cpp:830 (FinishedRecording) - TVRec(16): FinishedRecording(6006_2013-02-04T08:20:02Z) damaged recq:<RecordingQuality overall_score="0" key="6006_2013-02-04T08:20:02Z" countinuity_error_count="0" packet_count="62006">    <Gap start="2013-02-04T08:30:00Z" end="2013-02-04T10:32:48Z" duration="7368" /></RecordingQuality>

I think I have two different things going on.  The second error looks to be the tuner failing in one way or another.  I’m using an hdhomerun, which is an ethernet connected tuner.  I have a crappy ethernet cable on it, with the tabs at each end broken off.  So it sometimes isn’t connected as well as it could be.  I replaced with another cat6 cable that has proper ends.  Hopefully this is the main change that makes a difference.

Secondly, the buffer errors I think are problems with writing.  I have this niggle that whilst my other performance changes in theory help my sequential reads and writes, that they are delaying random writes.  I’m not really sure, but I’ve noticed that my RAID performance is all over the place.  I’m going to write a script that tunes the array then tests the throughput – but it needs to test the throughput from a multithreaded read and write scenario.  Anyway, in the meantime I set the read ahead to 1024 (rather than 8192 I was using), and the stripe-cache to 256 (rather than the 2048 I was using).

I then downloaded hdhomerun_config

  aptitude install hdhomerun-config          # on debian

And ran it to see what my tuner is doing – my suspicion is that I’m not getting a high quality signal from the new aerial I installed.

  hdhomerun_config 1110721D get /tuner0/status
  ch=t7qam64:226500000 lock=t7qam64 ss=100 snq=100 seq=100 bps=23048800 pps=522

The important measures are the ss (signal strength?) and snq (signal to noise ratio) I believe, both are showing 100.  I think the signal is fine.

Set it up to record a bunch of shows off 7 last night, as that’s the channel that’s been giving issues.  No errors in the log overnight, other than a couple of these at 6am this morning:

2013-02-04 06:03:01.971378 W [21378/1135] TFWWrite ThreadedFileWriter.cpp:500 (DiskLoop) - TFW(/usr/share/mythtv/recordings/6032_20130203185000.mpg:80): write(63920) cnt 28 total 1645000 -- took a long time, 1537 ms

Tentatively declaring success.  We’ll see how we go with real recordings tonight.

By the way, super scary TV in the middle of the night.  Apparently people who can’t sleep want to buy mops, cooking kit, and worry about their kids not learning maths properly.  Who knew?

 

 

Media centre

So, I’ve been running mythtv for about 7 years as my media centre, with the focus on recording and playing tv and playing music.  Over the years this has evolved somewhat, and mythtv itself has improved immensely.  There’ll no doubt be a few posts on myth, I thought I’d start off with the question as to why.

I really wanted something that would play media for me, that would record tv, and that I had a reasonable amount of control over.  When I started there were very few useful set top boxes out there, and myth did most of what I wanted.  Today, there are a lot of other options in the market, so I thought I’d run down a few of them.

The functionality I’m looking for these days is:

  • Play downloaded media (so lots of file formats)
  • Play ripped media (my preference for ripped my CDs is .flac, for ripping DVDs is .iso.  These are both lossless, but many media players won’t play them)
  • Record TV and play it.  This is getting less important, but we still record a fair bit of TV
  • Allow playlists and other general music playing functionality, and support high quality audio output
  • Ideally, work on commodity hardware, and be a free install.  I kind of like open source, so this is important to me.

Firstly, there are the media players (ignoring the apple devices).  My experience with them is average – they tend to have difficult interfaces that are very file based, and relatively limited functionality.  The upside is that they’re cheap and they work well once going.  They generally don’t record TV, so they’re mostly for downloaded or ripped media.

Secondly, there are the apple devices, particularly AppleTV.  These are basically a media player with a better UI.  They are limited in functionality again, and don’t record, but they offer a lot of potential as a front-end to something else on the backend.

That kind of crosses off most of the pre-built devices for me, so we move on to software players.

Firstly Windows Media Centre.  I’ve never liked it, and I’m not a big windows fan.  I find it quite limited in operation, in particular it used to have problems with digital and analogue tuners running at the same time.  It just isn’t as scalable as I’d like, and Microsoft seem to have gone quiet about it.

Then we get the free applications.

XBMC runs on Linux and is open source.  It’s good, but no recording.  Otherwise I’d be using it.  You can apparently use it with a myth backend for recording duties, but I’ve not gone there as yet.

Mythtv – ticks all my objectives.

Plex – I have a friend with Plex, and it seems pretty good.  It does/can record, but it’s a bit more limited on platforms, and seems to be headed down a closed source path.  I’m not going there at the moment.

So, what are the good, the bad and the ugly about mythtv?

The good

  • Powerful, lots of functionality
  • Now with Apple Airplay so I can stream from iPads and iPhones
  • Still actively maintained and extended
  • Since I got an HDHomeRun tuner, very stable recording
  • Runs on relatively low end frontend hardware
  • Plays all my formats

The bad

  • Mythmusic is now prettier than it was, but also harder to use.  Needs a brush over, I’ll do a post on that one day
  • Playback of .iso is a bit flaky, some just don’t play on some revisions
  • Takes a bit of maintenance if you don’t just set and forget (and I tend not to)

So, for now, looks like mythtv is the go for me.