IBM Portable PC
Posts: 46
Joined: Sun Apr 26, 2015 10:18 am
Location: Melbourne, Australia

Re: Improved forked-daapd (iTunes server)

Mon Mar 05, 2018 9:34 pm

IBM Portable PC wrote:
Sun Jan 21, 2018 2:17 am
I've had Forked-DAAPD working for a while now, however, no matter what I try I cannot label streams as I would like to see them in Apple Remote.

#EXTM3U
#EXTINF:-1,Aloha Joe - Relaxation Island
http://s2.voscast.com:7932;

For example, the above stream plays, however only shoutcast metadata is used to name the stream i.e. Aloha Joa - Relaxation Island is ignored. The same issue occurs with my other streams also.

I have also tried exporting the playlist from iTunes in various formats: m3u,m3u8,xml and txt. Interestingly, iTunes exports files in a "Classic Mac" format with CR's but not LF's at the end of each line. Even after correcting this in Text Wrangler, there is no change.

Any thoughts?
ejurgensen wrote:
Sun Jan 21, 2018 6:02 pm
Yes, Shoutcast metadata wins over the m3u tags. I don't recall have changed this, I think it has been that way for quite some time. Did you notice from what version you have noticed a change?

In relation to which metadata gets used, the format of the m3u doesn't matter, so I don't quite understand the latter you wrote? Perhaps I have misunderstood the issue?
Would this be a simple change to implement?

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Tue Mar 06, 2018 3:47 pm

Should be a rather small change. I will try to get it done for the next RPi release.

cdlenfert
Posts: 41
Joined: Mon May 01, 2017 8:30 pm

Re: Improved forked-daapd (iTunes server)

Sun Mar 11, 2018 5:26 pm

I'm on Debian Stretch and installed forked-daapd from the repo. When I reboot the my Pi3 I have to manually start forked-daapd via this command.

Code: Select all

systemctl start forked-daapd
I did not upgrade from Jessie on this device, so I believe I'm using systemd to manage services. What would be the best approach to get forked-daapd to start automatically on reboot?

This is a little out of the scope of forked-daapd, so I understand if there's no response, but since my library is on an external smb share It's sort of relevant ;) I've added a line to my /etc/fstab file that I thought was supposed to mount my external drive at boot. If I run:

Code: Select all

sudo mount -a
then the drive (and my library) mounts immediately, but I'd like to automate that step upon reboot as well.

Thanks for any input.

edit: I just found this ticket - https://github.com/ejurgensen/forked-daapd/issues/430. Has this fix been baked into the current release? I'm on version 26 and just ran an "apt-get upgrade".

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Sun Mar 11, 2018 9:38 pm

You could try "sudo systemctl enable forked-daapd". If that doesn't work then run "systemctl status forked-daapd" after boot to see why it is not starting.

Yes, issue 430 that you mention was fixed a while ago, and should not be in version 26.

cdlenfert
Posts: 41
Joined: Mon May 01, 2017 8:30 pm

Re: Improved forked-daapd (iTunes server)

Sat Mar 17, 2018 2:11 pm

Thanks, that seems to have done the trick. ( systemctl enabled forked-daapd )

I can't figure out an issue I'm having with playlists and smart playlists. I'm running on 2 devices, and what I think are the same playlists are being handled differently on different devices. One shows all of the tracks in the list, the other device shows empty playlists. Here are some examples.

Smart Playlist that fails (all-songs-no-streams.smartpl)

Code: Select all

“All Songs” {
  data_kind is file
  and media_kind is music
}
Smart Playlist that works (all-songs.smartpl)

Code: Select all

"ALL SONGS" {
  data_kind is file
  and media_kind is music
}
Is it the quotes? and how do I make sure I'm getting the right type of quote?

Regular playlist that works (Songza MIx.m3u)

Code: Select all

I Don't Know I Know.mp3
How Can You Luv Me.mp3
._Solitude Is Bliss.mp3
._I Wanna Do It.mp3
._It All Feels Right.mp3
._Before We Begin.mp3
._Neither Of Us, Uncertainly.mp3
._Keep You.mp3
._Stillness Is The Move.mp3
Amor Fati.mp3
._Weird.mp3
Solitude Is Bliss.mp3
Baby.mp3
._Microlite.mp3
._Lofticries.mp3
Places.mp3
Secrets.mp3
Powerful Lovin'.mp3
._Paradise.mp3
Wide Eyes.mp3
._Hold On (Feat. Sampha).mp3
._Desire Lines.mp3
Your Life Your Call.mp3
._When U Love Somebody.mp3
Dirty Work.mp3
Dark Water.mp3
Long Hot Summer.mp3
In The City.mp3
Out Of Tune.mp3
._This Must Be The Place (Naive Melody).mp3
._Megalomania.mp3
F Kenya Rip.mp3
._Drift Dive.mp3
._Lovin.mp3
._Dance For You.mp3
Cannons.mp3
._How Can You Luv Me.mp3
Bright Lit Blue Skies.mp3
Before Your Very Eyes....mp3
Turquoise (Skyline).mp3
._Same Damn Time.mp3
._Dirty Work.mp3
Killin The Vibe.mp3
Europa.mp3
._After The Moment.mp3
._Remember Our Days.mp3
Desire Lines.mp3
Fright Night (Nevermore).mp3
Helicopter.mp3
._Counting.mp3
._Know Your Own Strength.mp3
Can't Find My Way Home.mp3
._Eveningness.mp3
._Long Hot Summer.mp3
I Wanna Do It.mp3
._Fright Night (Nevermore).mp3
._Secrets.mp3
Remember Our Days.mp3
._Turquoise (Skyline).mp3
._Summer Of Love.mp3
Eveningness.mp3
Crawlersout.mp3
Exchange.mp3
Stillness Is The Move.mp3
Know Your Own Strength.mp3
Turning On.mp3
Weird.mp3
._Amor Fati.mp3
._Turning On.mp3
._Never Come Around.mp3
._So High.mp3
._Chinatown.mp3
._Summer Holiday.mp3
._Pickle.mp3
Northern Islands.mp3
Dance For You.mp3
._I Don't Know I Know.mp3
Lofticries.mp3
Hold On (Feat. Sampha).mp3
._Northern Islands.mp3
Megalomania.mp3
Taro.mp3
._Helicopter.mp3
._Killin The Vibe.mp3
._F Kenya Rip.mp3
._Taro.mp3
From The Morning Heat.mp3
._Can't Find My Way Home.mp3
._Friend Crush.mp3
Warm Spell.mp3
Pickle.mp3
._In The City.mp3
Microlite.mp3
The Bitterest Pill (I Ever Had To Swallow).mp3
Friend Crush.mp3
._Bright Lit Blue Skies.mp3
Before We Begin.mp3
._Baby.mp3
Paradise.mp3
._Up With People.mp3
._Your Life Your Call.mp3
Never Come Around.mp3
._The Bitterest Pill (I Ever Had To Swallow).mp3
._Exchange.mp3
Counting.mp3
._Crawlersout.mp3
Summer Holiday.mp3
Keep You.mp3
Up With People.mp3
Same Damn Time.mp3
._Europa.mp3
._Warm Spell.mp3
._Before Your Very Eyes....mp3
._Feel It All Around.mp3
Feel It All Around.mp3
._Cannons.mp3
Neither Of Us, Uncertainly.mp3
Summer Of Love.mp3
._Wide Eyes.mp3
This Must Be The Place (Naive Melody).mp3
After The Moment.mp3
._Dark Water.mp3
It All Feels Right.mp3
Lovin.mp3
Chinatown.mp3
Everything Is Moving So Fast.mp3
._Make Me A Fool.mp3
._Places.mp3
._Powerful Lovin'.mp3
When U Love Somebody.mp3
So High.mp3
._Out Of Tune.mp3
._Everything Is Moving So Fast.mp3
Make Me A Fool.mp3
Drift Dive.mp3
._From The Morning Heat.mp3
Regular playlist that fails (Songza-mix.m3u)

Code: Select all

F Kenya Rip.mp3
F Kenya Rip.mp3
Your Life Your Call.mp3
Before We Begin.mp3
Drift Dive.mp3
When U Love Somebody.mp3
Summer Holiday.mp3
Megalomania.mp3
Remember Our Days.mp3
Up With People.mp3
Bright Lit Blue Skies.mp3
Pickle.mp3
So High.mp3
Chinatown.mp3
Make Me A Fool.mp3
Dark Water.mp3
Lofticries.mp3
Helicopter.mp3
Same Damn Time.mp3
Stillness Is The Move.mp3
This Must Be The Place (Naive Melody).mp3
Crawlersout.mp3
Powerful Lovin'.mp3
Eveningness.mp3
Killin The Vibe.mp3
Taro.mp3
In The City.mp3
How Can You Luv Me.mp3
Never Come Around.mp3
Turning On.mp3
Dirty Work.mp3
Solitude Is Bliss.mp3
Everything Is Moving So Fast.mp3
Wide Eyes.mp3
After The Moment.mp3
Fright Night (Nevermore).mp3
Warm Spell.mp3
Summer Of Love.mp3
Neither Of Us Uncertainly.mp3
Keep You.mp3
Friend Crush.mp3
Lovin.mp3
Dance For You.mp3
Counting.mp3
Turquoise (Skyline).mp3
Long Hot Summer.mp3
Cannons.mp3
Out Of Tune.mp3
I Don't Know I Know.mp3
It All Feels Right.mp3
Desire Lines.mp3
Feel It All Around.mp3
Amor Fati.mp3
Can't Find My Way Home.mp3
Exchange.mp3
From The Morning Heat.mp3
Weird.mp3
Baby.mp3
Northern Islands.mp3
I Wanna Do It.mp3
Paradise.mp3
Hold On (Feat. Sampha).mp3
Places.mp3
The Bitterest Pill (I Ever Had To Swallow).mp3
Secrets.mp3
Microlite.mp3
Before Your Very Eyes....mp3
Know Your Own Strength.mp3
Europa.mp3
The only difference I see is that the former includes the hidden files, but I don't expect that should matter. Can't you make a list of any songs within a directory you want (not requiring all files to be included)?

Thanks for any tips. The systems are the same hardware (Pi Zero W), software (Debian Stretch latest), and version of Forked-Daapd (latest from the repo).

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Tue Apr 03, 2018 8:23 pm

Here is a late and not very good reply, @cdlenfert!

I cannot think of any reason why one of the devices would show empty playlists. Both the smart playlist and the regular one look fine. You certainly can include just the songs you want, and it should make no difference at all whether you list hidden files or not (they should just be ignored).

To get wiser on what is going on, I would ask you to set the log level in the config all the way to "spam". It will produce a lot of logging, so don't keep it there too long. Then try to modify one of the playlists that is showing up empty (maybe start with a regular one). In the log you should then find detailed messages about how forked-daapd tried to locate for instance "Your Life Your Call.mp3", and also probably also how it failed. Can you share that? Just share the messages for one playlist item (so don't post massive log output).

rainerstandke
Posts: 8
Joined: Sun Apr 27, 2014 1:09 am

Playlist Question

Tue Apr 24, 2018 11:44 pm

I am considering rebuilding my music server, as it is still on Jessie. I am trying to figure out what it takes to get playlists (of songs, as well as internet radio stations) working - do I need iTunes XML formatted playlists for that? And if so, do I need to compile forked-daapd myself?

Also, in the current distributed version, is the web interface support turned on? (Currently I can't get beyond the login screen...)

Thanks!

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Wed Apr 25, 2018 6:17 pm

You can make playlists with m3u files, pls files or with iTunes XML. You don't need to compile forked-daapd yourself, you can it install it from my repo by following the instructions in the very first post of this thread.

Yes, the web interface is also enabled in that version. Have you checked that you are actually running the most recent version? It is 26.0. You can see from apt or from the top of the log file (/var/log/forked-daapd.log) what version you are running.

AmusedSandpaper
Posts: 2
Joined: Mon Feb 19, 2018 2:47 pm

Re: Improved forked-daapd (iTunes server)

Mon Jun 04, 2018 2:20 pm

Hello,

I am trying to pipe music from a Bluetooth source to forked-daapd using named pipes on raspberry PI but it seems to be very slow. The whole system works, I can send music through Bluetooth but the music comes out on the other side about 7-8 seconds later. From what I can tell with my tests, it is the pipe that takes so long or at least the time that Forked-daapd takes to realize that there have been changes in the pipe. Do you know what could cause this and how I could fix it?

Thanks in advance,
Pieter

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Mon Jun 04, 2018 7:17 pm

Is that with an Airplay speaker? I have a 5-6 second delay on my Airplay speaker.

It's been a while since I looked at this, but out of memory what happens is: 1) audio on the pipe is detected, 2) the connection to the speaker is established (creating a session), 3) pipe read, 4) writing to speaker. Maybe 2 and 3 are in reverse order, but it doesn't make a difference, because I think what takes time is establishing the connection and then speaker buffering (which is about 2 seconds). I think step 1 is pretty fast.

If you somehow ahead of time know that you will be needing to play from your input, you can start playback of the pipe before writing to it. Forked-daapd will then just stream silence to the speakers until there is data to read. That would bring the delay down to maybe 3-4 seconds.

If that is not viable, then there is no way to avoid a startup delay, but that doesn't mean you can't bring down the lag between the input and the output. You could do that by 1) detect by some other means that it is time to start playback, 2) request playback start of the pipe from forked-daapd, 3) discard data from the input until playback has actually started, 4) then start writing to the pipe. But again, I don't think you can get much below 3 seconds.

AmusedSandpaper
Posts: 2
Joined: Mon Feb 19, 2018 2:47 pm

Re: Improved forked-daapd (iTunes server)

Tue Jun 05, 2018 7:21 am

Yes exactly. Ok thanks for the information.

It is like I thought then, the chain of events is what makes it take so much time.

I will try and look into that! Thank you very much for the help.

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Fri Jun 08, 2018 11:25 pm

I've uploaded a new build of forked-daapd to the repo, this one is version 26.1.68. What's new:

- Player web interface! Browse to http://[your_rpi_hostname]:3689/ and check it out
(credit to @chme for this awesome improvement)
- Support for Airplay speaker control commands: Some speakers have controls for play/stop/next/previous and volume. This should now work with forked-daapd.
- Add non-library items (e.g. radio stations) to the queue (credit @chme)
- Fix for keeping connection alive towards some devices e.g. old ApEx's
- Extension of the JSON API (credit @chme)
- Under-the-hood improvements of Spotify integration (credit @chme)
- Fix for Android "Remote for iTunes"
- Performance improvements + misc

SoundBridgeUser
Posts: 7
Joined: Tue Jun 12, 2018 7:07 pm

Re: Improved forked-daapd (iTunes server)

Tue Jun 12, 2018 7:17 pm

Hi,

First of all, I'm a complete newbie to this forum, but I have been using forked-daapd for 18 months now on a Raspberry PI and used the original Firefly / mt-daapd on an NSLU2 for about 10 years before.

I use forked-daapd on a Raspberry PI 3 to serve to a small number of Roku Soundbridges (I haven't got any AirPlay devices). The application is awesome and is something we use frequently everyday.

My media server is large however - there are 47000+ music files, around 200 .M3U Playlists and two Smart Playlists.

Everything has worked well apart from being able to browse Artists which I had put down to having 4000+ artists and it taking a long time to serve them. However, with the new release (26.1.68), the Artist browse now works, but I am having an issue accessing Playlists. If I enable SPAM logging and select Playlists on the Roku, the request times out, and the log file has the following:

Code: Select all

[2018-06-12 18:42:05] [DEBUG]      rsp: RSP request: '/rsp/info'
[2018-06-12 18:42:05] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0;'
[2018-06-12 18:42:08] [DEBUG]      rsp: RSP request: '/rsp/db'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM playlists f WHERE f.disabled = 0;'
[2018-06-12 18:42:08] [DEBUG]       db: Starting query 'SELECT f.* FROM playlists f WHERE f.disabled = 0  ;'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND 1 = 1;'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND f.media_kind = 1;'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND f.media_kind = 2;'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND f.media_kind = 64;'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND f.media_kind = 4;'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND f.media_kind = 8;'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND ((f.compilation = 0 AND (((((((((((((((f.genre LIKE 'Pop' OR f.genre LIKE '      Rock') OR f.genre LIKE 'House') OR f.genre LIKE 'Downtempo') OR f.genre LIKE 'Jazz') OR f.genre LIKE 'Rave') OR f.genre LIKE 'Drum And Bass') OR f.genre LIKE 'Latin') OR f.genre LIKE '      Techno') OR f.genre LIKE 'Big Beat') OR f.genre LIKE 'Disco') OR f.genre LIKE 'Dub') OR f.genre LIKE 'Reggae') OR f.genre LIKE 'Ambient') OR f.genre LIKE 'Blues') OR f.genre LIKE 'Funk      ')));'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM files f WHERE f.disabled = 0 AND ((f.compilation = 0 AND NOT((((f.genre LIKE 'Christmas' OR f.genre LIKE 'Ch      ildrens') OR f.genre LIKE 'Spoken Word') OR f.genre LIKE 'Loop'))));'
[2018-06-12 18:42:08] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND pi.playlistid = 132;'
[2018-06-12 18:42:09] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND f.data_kind = 1 AND pi.playli      stid = 132;'
(taking out entries for 200 other playlists)

Code: Select all

[2018-06-12 18:43:45] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND pi.playlistid = 33;'
[2018-06-12 18:43:45] [DEBUG]       db: Running query 'SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND f.data_kind = 1 AND pi.playlistid = 33;'
[2018-06-12 18:43:45] [DEBUG]       db: End of query results
[2018-06-12 18:43:45] [DEBUG]    httpd: Gzipping response
I'm pretty sure the queries being run didn't happen on the previous version and the querying is the cause of the timeout. Do you have any suggestions as to how I can get it working? I have uninstalled and reinstalled the application and rebuilt the database so I don't imagine this is an upgrade issue.

Thank you

Richard

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Wed Jun 13, 2018 6:51 pm

I've made some database index and query changes, actually also to help with RSP response times. I don't think I did anything related to playlists, but maybe I did. Can you say what the previous version you were using was? Then I can compare db performance.

There is so much you can do on your side. When I find the reason for the poor performance I might be able to identify an index that you can manually add to the db.

SoundBridgeUser
Posts: 7
Joined: Tue Jun 12, 2018 7:07 pm

Re: Improved forked-daapd (iTunes server)

Wed Jun 13, 2018 8:28 pm

ejurgensen wrote:
Wed Jun 13, 2018 6:51 pm
I've made some database index and query changes, actually also to help with RSP response times. I don't think I did anything related to playlists, but maybe I did. Can you say what the previous version you were using was? Then I can compare db performance.

There is so much you can do on your side. When I find the reason for the poor performance I might be able to identify an index that you can manually add to the db.
Hi - Thanks for your reply. I was previously using 26.0. I did an upgrade to 26.1 from that but after I had issues with Playlists, I reinstalled the app from scratch so the database has now been recreated from a vanilla 26.1 install.

The performance from Browse queries has significantly improved - with 26.1, I can now browse by artist (and it returns over 5700 very quickly!), but Playlists are now slow - what used to take a second or so to return now times out. When I look at the log, it takes over a minute to run the queries and appears slow when viewing the log in real time.

Hope this helps

Richard

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Wed Jun 13, 2018 8:32 pm

Oh, saw I wrote there is "so much you can do on your side", but I actually meant "not much you can do on your side". I will investigate the difference between 26.0 and 26.1 on the playlists.

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Wed Jun 13, 2018 9:13 pm

My immediate investigation reveals no difference between 26.0 and 26.1 when it comes to RSP playlist requests. Queries and indices seem to be exactly the same. Maybe it is something else?

Could you 1) check what the log file says about the queries when it is fast, so with 26.0 - are the queries the same as you posted above? 2) try the below command sequence and see if you get the same output? (preferably with both 26.0 and 26.1, but otherwise just with whatever you have installed currently)

Code: Select all

$ sqlite3 [your_database_file]
sqlite> .load /usr/lib/forked-daapd/forked-daapd-sqlext.so 
sqlite> explain query plan SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND pi.playlistid = 132;
0|0|0|SEARCH TABLE playlistitems AS pi USING COVERING INDEX idx_playlistid (playlistid=?)
0|1|1|SEARCH TABLE files AS f USING INDEX idx_rescan (path=?)
sqlite> explain query plan SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND f.data_kind = 1 AND pi.playlistid = 132;
0|0|0|SEARCH TABLE playlistitems AS pi USING COVERING INDEX idx_playlistid (playlistid=?)
0|1|1|SEARCH TABLE files AS f USING INDEX idx_rescan (path=?)
I want to be sure you also have the same queries and indices.

SoundBridgeUser
Posts: 7
Joined: Tue Jun 12, 2018 7:07 pm

Re: Improved forked-daapd (iTunes server)

Wed Jun 13, 2018 9:49 pm

Hi,

Thanks for your quick response.

I only have 26.1 installed - not sure how best to rollback to 26.0.

I ran the commands - the .load command didn't work as you wrote it, as the file "forked-daapd-sqlext.so" was in a different location (I had to search for it).

Running the commands produced slightly different results - not sure if that is due to the different file location.

Code: Select all

[email protected]:~ $ sqlite3 /var/cache/forked-daapd/songs3.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .load /usr/lib/arm-linux-gnueabihf/forked-daapd/forked-daapd-sqlext.so
sqlite> explain query plan SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND pi.playlistid = 132;                                                    
0|0|1|SEARCH TABLE files AS f USING INDEX idx_pl_dir (disabled=?)
0|1|0|SEARCH TABLE playlistitems AS pi USING COVERING INDEX idx_playlistid (playlistid=? AND filepath=?)
sqlite> explain query plan SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND f.data_kind = 1 AND pi.playlistid = 132;
0|0|1|SEARCH TABLE files AS f USING INDEX idx_pl_dir (disabled=?)
0|1|0|SEARCH TABLE playlistitems AS pi USING COVERING INDEX idx_playlistid (playlistid=? AND filepath=?)
Not sure if that is because I have loaded the wrong file or something else.

Thanks,

Richard

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Thu Jun 14, 2018 12:19 pm

Hm, looks like sqlite3 is using a very suboptimal query strategy on your device. With that strategy it would be processing pretty much all rows in the files table for each playlist, which would indeed be very slow (200 x 47.000). I can't figure out why sqlite is doing that... a possibility is that the corrected indices are somehow missing or incorrect. Can you try to run ".schema idx_rescan" and ".schema idx_playlistid" and post output here? That should help confirm the two relevant indices.

SoundBridgeUser
Posts: 7
Joined: Tue Jun 12, 2018 7:07 pm

Re: Improved forked-daapd (iTunes server)

Thu Jun 14, 2018 5:50 pm

ejurgensen wrote:
Thu Jun 14, 2018 12:19 pm
Hm, looks like sqlite3 is using a very suboptimal query strategy on your device. With that strategy it would be processing pretty much all rows in the files table for each playlist, which would indeed be very slow (200 x 47.000). I can't figure out why sqlite is doing that... a possibility is that the corrected indices are somehow missing or incorrect. Can you try to run ".schema idx_rescan" and ".schema idx_playlistid" and post output here? That should help confirm the two relevant indices.
Hi - thanks for your response. Running ".schema idx_rescan" and ".schema idx_playlistid" doesn't produce any results. Running ".schema" on its own produces the following:

Code: Select all

sqlite> .schema
CREATE TABLE files (   id                 INTEGER PRIMARY KEY NOT NULL,   path               VARCHAR(4096) NOT NULL,   fname              VARCHAR(255) NOT NULL,   title              VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   artist             VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   album              VARCHAR(1024) NOT NULL COLLATE DAAP,   genre              VARCHAR(255) DEFAULT NULL COLLATE DAAP,   comment            VARCHAR(4096) DEFAULT NULL COLLATE DAAP,   type               VARCHAR(255) DEFAULT NULL COLLATE DAAP,   composer           VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   orchestra          VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   conductor          VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   grouping           VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   url                VARCHAR(1024) DEFAULT NULL,   bitrate            INTEGER DEFAULT 0,   samplerate         INTEGER DEFAULT 0,   song_length        INTEGER DEFAULT 0,   file_size          INTEGER DEFAULT 0,   year               INTEGER DEFAULT 0,   track              INTEGER DEFAULT 0,   total_tracks       INTEGER DEFAULT 0,   disc               INTEGER DEFAULT 0,   total_discs        INTEGER DEFAULT 0,   bpm                INTEGER DEFAULT 0,   compilation        INTEGER DEFAULT 0,   artwork            INTEGER DEFAULT 0,   rating             INTEGER DEFAULT 0,   play_count         INTEGER DEFAULT 0,   seek               INTEGER DEFAULT 0,   data_kind          INTEGER DEFAULT 0,   item_kind          INTEGER DEFAULT 0,   description        INTEGER DEFAULT 0,   time_added         INTEGER DEFAULT 0,   time_modified      INTEGER DEFAULT 0,   time_played        INTEGER DEFAULT 0,   db_timestamp       INTEGER DEFAULT 0,   disabled           INTEGER DEFAULT 0,   sample_count       INTEGER DEFAULT 0,   codectype          VARCHAR(5) DEFAULT NULL,   idx                INTEGER NOT NULL,   has_video          INTEGER DEFAULT 0,   contentrating      INTEGER DEFAULT 0,   bits_per_sample    INTEGER DEFAULT 0,   album_artist       VARCHAR(1024) NOT NULL COLLATE DAAP,   media_kind         INTEGER NOT NULL,   tv_series_name     VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   tv_episode_num_str VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   tv_network_name    VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   tv_episode_sort    INTEGER NOT NULL,   tv_season_num      INTEGER NOT NULL,   songartistid       INTEGER NOT NULL,   songalbumid        INTEGER NOT NULL,   title_sort         VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   artist_sort        VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   album_sort         VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   composer_sort      VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   album_artist_sort  VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   virtual_path       VARCHAR(4096) DEFAULT NULL,   directory_id       INTEGER DEFAULT 0,   date_released      INTEGER DEFAULT 0);
CREATE TABLE playlists (   id             INTEGER PRIMARY KEY NOT NULL,   title          VARCHAR(255) NOT NULL COLLATE DAAP,   type           INTEGER NOT NULL,   query          VARCHAR(1024),   db_timestamp   INTEGER NOT NULL,   disabled       INTEGER DEFAULT 0,   path           VARCHAR(4096),   idx            INTEGER NOT NULL,   special_id     INTEGER DEFAULT 0,   virtual_path   VARCHAR(4096),   parent_id      INTEGER DEFAULT 0,   directory_id   INTEGER DEFAULT 0, query_order VARCHAR(1024), query_limit INTEGER DEFAULT -1);
CREATE TABLE playlistitems (   id             INTEGER PRIMARY KEY NOT NULL,   playlistid     INTEGER NOT NULL,   filepath       VARCHAR(4096) NOT NULL);
CREATE TABLE groups (   id             INTEGER PRIMARY KEY NOT NULL,   type           INTEGER NOT NULL,   name           VARCHAR(1024) NOT NULL COLLATE DAAP,   persistentid   INTEGER NOT NULL,CONSTRAINT groups_type_unique_persistentid UNIQUE (type, persistentid));
CREATE TABLE pairings(   remote         VARCHAR(64) PRIMARY KEY NOT NULL,   name           VARCHAR(255) NOT NULL,   guid           VARCHAR(16) NOT NULL);
CREATE TABLE speakers(   id             INTEGER PRIMARY KEY NOT NULL,   selected       INTEGER NOT NULL,   volume         INTEGER NOT NULL,   name           VARCHAR(255) DEFAULT NULL, auth_key VARCHAR(2048) DEFAULT NULL);
CREATE TABLE inotify (   wd          INTEGER PRIMARY KEY NOT NULL,   cookie      INTEGER NOT NULL,   path        VARCHAR(4096) NOT NULL);
CREATE TABLE directories (   id                  INTEGER PRIMARY KEY NOT NULL,   virtual_path        VARCHAR(4096) NOT NULL,   db_timestamp        INTEGER DEFAULT 0,   disabled            INTEGER DEFAULT 0,   parent_id           INTEGER DEFAULT 0);
CREATE TABLE admin(   key   VARCHAR(32) PRIMARY KEY NOT NULL,   value VARCHAR(32) NOT NULL);
CREATE TABLE queue (   id                  INTEGER PRIMARY KEY AUTOINCREMENT,   file_id             INTEGER NOT NULL,   pos                 INTEGER NOT NULL,   shuffle_pos         INTEGER NOT NULL,   data_kind           INTEGER NOT NULL,   media_kind          INTEGER NOT NULL,   song_length         INTEGER NOT NULL,   path                VARCHAR(4096) NOT NULL,   virtual_path        VARCHAR(4096) NOT NULL,   title               VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   artist              VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   album_artist        VARCHAR(1024) NOT NULL COLLATE DAAP,   album               VARCHAR(1024) NOT NULL COLLATE DAAP,   genre               VARCHAR(255) DEFAULT NULL COLLATE DAAP,   songalbumid         INTEGER NOT NULL,   time_modified       INTEGER DEFAULT 0,   artist_sort         VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   album_sort          VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   album_artist_sort   VARCHAR(1024) DEFAULT NULL COLLATE DAAP,   year                INTEGER DEFAULT 0,   track               INTEGER DEFAULT 0,   disc                INTEGER DEFAULT 0,   artwork_url         VARCHAR(4096) DEFAULT NULL,   queue_version       INTEGER DEFAULT 0);
CREATE INDEX idx_rescan ON files(path, db_timestamp);
CREATE INDEX idx_fname ON files(disabled, fname COLLATE NOCASE);
CREATE INDEX idx_sari ON files(songartistid);
CREATE INDEX idx_sali ON files(songalbumid, disabled, media_kind, album_sort, disc, track);
CREATE INDEX idx_state_mkind_sari ON files(disabled, media_kind, songartistid);
CREATE INDEX idx_state_mkind_sali ON files(disabled, media_kind, songalbumid);
CREATE INDEX idx_albumartist ON files(disabled, album_artist_sort, album_artist, media_kind);
CREATE INDEX idx_composer ON files(disabled, composer_sort, composer, media_kind);
CREATE INDEX idx_genre ON files(disabled, genre, media_kind);
CREATE INDEX idx_title ON files(disabled, title_sort, media_kind);
CREATE INDEX idx_album ON files(disabled, album_sort, album, media_kind);
CREATE INDEX idx_filelist ON files(disabled, virtual_path, time_modified);
CREATE INDEX idx_file_dir ON files(disabled, directory_id);
CREATE INDEX idx_pl_path ON playlists(path);
CREATE INDEX idx_pl_disabled ON playlists(disabled, type, virtual_path, db_timestamp);
CREATE INDEX idx_pl_dir ON files(disabled, directory_id);
CREATE INDEX idx_filepath ON playlistitems(filepath ASC);
CREATE INDEX idx_playlistid ON playlistitems(playlistid, filepath);
CREATE INDEX idx_grp_persist ON groups(persistentid);
CREATE INDEX idx_pairingguid ON pairings(guid);
CREATE INDEX idx_dir_vpath ON directories(disabled, virtual_path);
CREATE INDEX idx_dir_parentid ON directories(parent_id);
CREATE INDEX idx_queue_pos ON queue(pos);
CREATE INDEX idx_queue_shufflepos ON queue(shuffle_pos);
CREATE TRIGGER update_groups_new_file AFTER INSERT ON files FOR EACH ROW BEGIN   INSERT OR IGNORE INTO groups (type, name, persistentid) VALUES (1, NEW.album, NEW.songalbumid);   INSERT OR IGNORE INTO groups (type, name, persistentid) VALUES (2, NEW.album_artist, NEW.songartistid); END;
CREATE TRIGGER update_groups_update_file AFTER UPDATE OF songalbumid ON files FOR EACH ROW BEGIN   INSERT OR IGNORE INTO groups (type, name, persistentid) VALUES (1, NEW.album, NEW.songalbumid);   INSERT OR IGNORE INTO groups (type, name, persistentid) VALUES (2, NEW.album_artist, NEW.songartistid); END;
sqlite>
Hope this helps. Thanks.

Richard

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Thu Jun 14, 2018 8:48 pm

It looks ok to me. I have exactly the same version of sqlite, but mine chooses the right query strategy, so not sure what the deal is. As an experiment you could try deleting the idx_pl_dir index and see if it will then use a better query plan (just run "DROP INDEX idx_pl_dir;"). Then try again with explain query plan to see what it says. If you want the idx_pl_dir back you can just recreate with "CREATE INDEX idx_pl_dir ON files(disabled, directory_id);".

SoundBridgeUser
Posts: 7
Joined: Tue Jun 12, 2018 7:07 pm

Re: Improved forked-daapd (iTunes server)

Thu Jun 14, 2018 9:13 pm

Hi,

Deleting the index doesn't appear to make a difference - the query plan results are the same as before.

Recreating the index also gives the same results.

Cheers,

Richard

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Thu Jun 14, 2018 9:29 pm

How can the query plan be the same if you deleted the index? The previous plan used the index.

In any case I am running out of ideas on what you can do. It is difficult when I can't reproduce.

ejurgensen
Posts: 546
Joined: Thu Jul 04, 2013 8:11 pm
Location: Denmark

Re: Improved forked-daapd (iTunes server)

Fri Jun 15, 2018 8:47 pm

@SoundBridgeUser, I have a few more suggestions/ideas:

Run sqlite3 on the database and again load the .so file like before. Then:
1. Try to run "SELECT * FROM sqlite_stat1;" and post the output here. It should give information about why sqlite is picking the index it is.
2. Try to run "ANALYZE;" (might take a while)
3. Again run "SELECT * FROM sqlite_stat1;" - any significant changes to idx_rescan? Or idx_pl_dir?
4. Again try "explain query plan SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND pi.playlistid = 132;" - is it still using idx_pl_dir?

If this also doesn't really help I have an idea to modify the query and then see if it gets optimized correctly. That would require you to install a special build of forked-daapd that I would make, so first let's give the above a chance.

SoundBridgeUser
Posts: 7
Joined: Tue Jun 12, 2018 7:07 pm

Re: Improved forked-daapd (iTunes server)

Sat Jun 16, 2018 8:15 am

Hi,

Thanks for your response - I really appreciate the time you are spending on this. I have some good news and bad news.

Here is the output from what you have asked to do:

Code: Select all

$ sudo sqlite3 /var/cache/forked-daapd/songs3.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .load /usr/lib/arm-linux-gnueabihf/forked-daapd/forked-daapd-sqlext.so
sqlite> SELECT * FROM sqlite_stat1;
admin|sqlite_autoindex_admin_1|4 1
speakers||1
sqlite> ANALYZE;
sqlite> SELECT * FROM sqlite_stat1;
admin|sqlite_autoindex_admin_1|5 1
files|idx_pl_dir|47007 47007 7
files|idx_file_dir|47007 47007 7
files|idx_filelist|47007 47007 1 1
files|idx_album|47007 47007 4 4 4
files|idx_title|47007 47007 2 2
files|idx_genre|47007 47007 644 644
files|idx_composer|47007 47007 4701 4701 4701
files|idx_albumartist|47007 47007 9 9 9
files|idx_state_mkind_sali|47007 47007 47007 4
files|idx_state_mkind_sari|47007 47007 47007 9
files|idx_sali|47007 4 4 4 4 4 2
files|idx_sari|47007 9
files|idx_fname|47007 47007 2
files|idx_rescan|47007 1 1
playlists|idx_pl_disabled|213 213 71 2 2
playlists|idx_pl_path|213 2
playlistitems|idx_playlistid|3808 19 1
playlistitems|idx_filepath|3808 2
groups|idx_grp_persist|21405 1
groups|sqlite_autoindex_groups_1|21405 10703 1
speakers||1
inotify||14295
directories|idx_dir_parentid|14301 3
directories|idx_dir_vpath|14301 7151 1
sqlite>

sqlite> explain query plan SELECT COUNT(*) FROM playlistitems pi JOIN files f ON pi.filepath = f.path WHERE f.disabled = 0 AND pi.playlistid = 132;
0|0|0|SEARCH TABLE playlistitems AS pi USING COVERING INDEX idx_playlistid (playlistid=?)
0|1|1|SEARCH TABLE files AS f USING INDEX idx_rescan (path=?)
The explain results have changed. After a restart of the server, the Playlists now open and list. Static playlists work, but unfortunately, Smart Playlists don't return anything (the response times out on the player with a "Failed to load playlist" error).

I'm going to do some more investigation and I'll post some logs on this issue.

Thanks,

Richard

Return to “Raspbian”

Who is online

Users browsing this forum: No registered users and 28 guests