Just appending some off-line correspondence to this bug report... ------
Julien has commented that database optimisation is on the to-do list ------ The running instance of forked-daapd is using less that 11MB with 80MB RAM available. The issue looks more to be to do with database optimisation with the database performing full table scans for sort operations and joins. The first culprit is: [2010-09-15 02:00:01] db: Starting query 'SELECT * FROM files WHERE disabled = 0 AND 1 = 1 AND (1 = 1) ORDER BY album COLLATE DAAP ASC, disc ASC, track ASC LIMIT -1 OFFSET 0;' [2010-09-15 02:00:40] This is taking 39s with the ORDER BY clause causing the full table scan - the custom collate macro is not independently responsible for any of the slow down as the query takes 39 seconds with the COLLATE clause removed. The attached debug log shows events from switching an Apple Remote connection from a different library over to the forked-daapd library. [2010-09-15 01:59:51] daap: DAAP request: /server-info [2010-09-15 01:59:51] httpd: Found query string [2010-09-15 01:59:51] daap: DAAP request: /login?pairing-guid=0x4CA9FED87154E463 [2010-09-15 01:59:51] db: Running query 'SELECT * FROM pairings WHERE guid = '4CA9FED87154E463';' [2010-09-15 01:59:51] daap: Remote 'pcipod64' logging in with GUID 4CA9FED87154E463 [2010-09-15 01:59:52] httpd: Found query string [2010-09-15 01:59:52] daap: DAAP request: /databases?session-id=100 [2010-09-15 01:59:52] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0;' [2010-09-15 01:59:53] db: Running query 'SELECT COUNT(*) FROM playlists WHERE disabled = 0;' [2010-09-15 01:59:53] httpd: Found query string [2010-09-15 01:59:53] dacp: DACP request: /ctrl-int/1/playstatusupdate?revision-number=1&session-id=100 [2010-09-15 01:59:53] player: Player status: stopped [2010-09-15 01:59:53] httpd: Found query string [2010-09-15 01:59:53] dacp: DACP request: /ctrl-int/1/getproperty?properties=dmcp.volume&session-id=100 [2010-09-15 01:59:53] dacp: Asking for 1 properties [2010-09-15 01:59:53] dacp: Found 1 properties [2010-09-15 01:59:53] player: Player status: stopped [2010-09-15 01:59:53] httpd: Found query string [2010-09-15 01:59:53] daap: DAAP request: /update?session-id=100&revision-number=1 [2010-09-15 01:59:53] httpd: Found query string [2010-09-15 01:59:53] daap: DAAP request: /databases/1/containers?session-id=100&meta=dmap.itemname,dmap.itemcount,dmap.itemid,dmap.persistentid,daap.baseplaylist,com.apple.itunes.special-playlist,com.apple.itunes.smart-playlist,com.apple.itunes.saved-genius,dmap.parentcontainerid,dmap.editcommandssupported,com.apple.itunes.jukebox-current,daap.songcontentdescription [2010-09-15 01:59:53] daap: Asking for 12 meta tags [2010-09-15 01:59:53] daap: Found 12 meta tags [2010-09-15 01:59:53] db: Running query 'SELECT COUNT(*) FROM playlists WHERE disabled = 0;' [2010-09-15 01:59:53] db: Starting query 'SELECT * FROM playlists WHERE disabled = 0 LIMIT -1 OFFSET 0;' [2010-09-15 01:59:53] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND 1 = 1;' [2010-09-15 01:59:53] daap: Done with meta tag dmap.itemname (Library) [2010-09-15 01:59:53] daap: Done with meta tag dmap.itemid (1) [2010-09-15 01:59:53] daap: Done with meta tag dmap.persistentid (1) [2010-09-15 01:59:53] daap: Could not find requested meta field (8) [2010-09-15 01:59:53] daap: Could not find requested meta field (10) [2010-09-15 01:59:53] daap: Could not find requested meta field (11) [2010-09-15 01:59:53] daap: Done with playlist [2010-09-15 01:59:53] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 1;' [2010-09-15 01:59:54] daap: Done with meta tag dmap.itemname (Music) [2010-09-15 01:59:54] daap: Done with meta tag dmap.itemid (2) [2010-09-15 01:59:54] daap: Done with meta tag dmap.persistentid (2) [2010-09-15 01:59:54] daap: Could not find requested meta field (8) [2010-09-15 01:59:54] daap: Could not find requested meta field (10) [2010-09-15 01:59:54] daap: Could not find requested meta field (11) [2010-09-15 01:59:54] daap: Done with playlist [2010-09-15 01:59:54] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 2;' [2010-09-15 01:59:55] daap: Done with meta tag dmap.itemname (Movies) [2010-09-15 01:59:55] daap: Done with meta tag dmap.itemid (3) [2010-09-15 01:59:55] daap: Done with meta tag dmap.persistentid (3) [2010-09-15 01:59:55] daap: Could not find requested meta field (8) [2010-09-15 01:59:55] daap: Could not find requested meta field (10) [2010-09-15 01:59:55] daap: Could not find requested meta field (11) [2010-09-15 01:59:55] daap: Done with playlist [2010-09-15 01:59:55] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 64;' [2010-09-15 01:59:55] daap: Done with meta tag dmap.itemname (TV Shows) [2010-09-15 01:59:55] daap: Done with meta tag dmap.itemid (4) [2010-09-15 01:59:55] daap: Done with meta tag dmap.persistentid (4) [2010-09-15 01:59:55] daap: Could not find requested meta field (8) [2010-09-15 01:59:55] daap: Could not find requested meta field (10) [2010-09-15 01:59:55] daap: Could not find requested meta field (11) [2010-09-15 01:59:55] daap: Done with playlist [2010-09-15 01:59:55] db: End of query results [2010-09-15 01:59:55] daap: Done with playlist list, 4 playlists [2010-09-15 01:59:55] httpd: Found query string [2010-09-15 01:59:55] dacp: DACP request: /ctrl-int/1/playstatusupdate?revision-number=2&session-id=100 [2010-09-15 01:59:55] httpd: Found query string [2010-09-15 01:59:55] dacp: DACP request: /ctrl-int/1/getspeakers?session-id=100 [2010-09-15 01:59:55] httpd: Found query string [2010-09-15 01:59:55] dacp: DACP request: /ctrl-int/1/nowplayingartwork?mw=320&mh=320&session-id=100 [2010-09-15 01:59:55] httpd: Found query string [2010-09-15 01:59:55] daap: DAAP request: /update?session-id=100&revision-number=2 [2010-09-15 01:59:55] httpd: Found query string [2010-09-15 01:59:55] daap: DAAP request: /databases/1/containers?session-id=100&meta=dmap.itemname,dmap.itemcount,dmap.itemid,dmap.persistentid,daap.baseplaylist,com.apple.itunes.special-playlist,com.apple.itunes.smart-playlist,com.apple.itunes.saved-genius,dmap.parentcontainerid,dmap.editcommandssupported,com.apple.itunes.jukebox-current,daap.songcontentdescription [2010-09-15 01:59:55] daap: Asking for 12 meta tags [2010-09-15 01:59:55] daap: Found 12 meta tags [2010-09-15 01:59:55] db: Running query 'SELECT COUNT(*) FROM playlists WHERE disabled = 0;' [2010-09-15 01:59:55] db: Starting query 'SELECT * FROM playlists WHERE disabled = 0 LIMIT -1 OFFSET 0;' [2010-09-15 01:59:55] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND 1 = 1;' [2010-09-15 01:59:56] daap: Done with meta tag dmap.itemname (Library) [2010-09-15 01:59:56] daap: Done with meta tag dmap.itemid (1) [2010-09-15 01:59:56] daap: Done with meta tag dmap.persistentid (1) [2010-09-15 01:59:56] daap: Could not find requested meta field (8) [2010-09-15 01:59:56] daap: Could not find requested meta field (10) [2010-09-15 01:59:56] daap: Could not find requested meta field (11) [2010-09-15 01:59:56] daap: Done with playlist [2010-09-15 01:59:56] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 1;' [2010-09-15 01:59:56] daap: Done with meta tag dmap.itemname (Music) [2010-09-15 01:59:56] daap: Done with meta tag dmap.itemid (2) [2010-09-15 01:59:56] daap: Done with meta tag dmap.persistentid (2) [2010-09-15 01:59:56] daap: Could not find requested meta field (8) [2010-09-15 01:59:56] daap: Could not find requested meta field (10) [2010-09-15 01:59:56] daap: Could not find requested meta field (11) [2010-09-15 01:59:56] daap: Done with playlist [2010-09-15 01:59:56] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 2;' [2010-09-15 01:59:57] daap: Done with meta tag dmap.itemname (Movies) [2010-09-15 01:59:57] daap: Done with meta tag dmap.itemid (3) [2010-09-15 01:59:57] daap: Done with meta tag dmap.persistentid (3) [2010-09-15 01:59:57] daap: Could not find requested meta field (8) [2010-09-15 01:59:57] daap: Could not find requested meta field (10) [2010-09-15 01:59:57] daap: Could not find requested meta field (11) [2010-09-15 01:59:57] daap: Done with playlist [2010-09-15 01:59:57] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 64;' [2010-09-15 01:59:57] daap: Done with meta tag dmap.itemname (TV Shows) [2010-09-15 01:59:57] daap: Done with meta tag dmap.itemid (4) [2010-09-15 01:59:57] daap: Done with meta tag dmap.persistentid (4) [2010-09-15 01:59:57] daap: Could not find requested meta field (8) [2010-09-15 01:59:57] daap: Could not find requested meta field (10) [2010-09-15 01:59:57] daap: Could not find requested meta field (11) [2010-09-15 01:59:57] daap: Done with playlist [2010-09-15 01:59:57] db: End of query results [2010-09-15 01:59:57] daap: Done with playlist list, 4 playlists [2010-09-15 01:59:57] dacp: DACP request: /ctrl-int [2010-09-15 01:59:57] httpd: Found query string [2010-09-15 01:59:57] daap: DAAP request: /databases/1/containers?session-id=100&meta=dmap.itemname,dmap.itemcount,dmap.itemid,dmap.persistentid,daap.baseplaylist,com.apple.itunes.special-playlist,com.apple.itunes.smart-playlist,com.apple.itunes.saved-genius,dmap.parentcontainerid,dmap.editcommandssupported,com.apple.itunes.jukebox-current,daap.songcontentdescription [2010-09-15 01:59:57] daap: Asking for 12 meta tags [2010-09-15 01:59:57] daap: Found 12 meta tags [2010-09-15 01:59:57] db: Running query 'SELECT COUNT(*) FROM playlists WHERE disabled = 0;' [2010-09-15 01:59:57] db: Starting query 'SELECT * FROM playlists WHERE disabled = 0 LIMIT -1 OFFSET 0;' [2010-09-15 01:59:57] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND 1 = 1;' [2010-09-15 01:59:58] daap: Done with meta tag dmap.itemname (Library) [2010-09-15 01:59:58] daap: Done with meta tag dmap.itemid (1) [2010-09-15 01:59:58] daap: Done with meta tag dmap.persistentid (1) [2010-09-15 01:59:58] daap: Could not find requested meta field (8) [2010-09-15 01:59:58] daap: Could not find requested meta field (10) [2010-09-15 01:59:58] daap: Could not find requested meta field (11) [2010-09-15 01:59:58] daap: Done with playlist [2010-09-15 01:59:58] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 1;' [2010-09-15 01:59:58] daap: Done with meta tag dmap.itemname (Music) [2010-09-15 01:59:58] daap: Done with meta tag dmap.itemid (2) [2010-09-15 01:59:58] daap: Done with meta tag dmap.persistentid (2) [2010-09-15 01:59:58] daap: Could not find requested meta field (8) [2010-09-15 01:59:58] daap: Could not find requested meta field (10) [2010-09-15 01:59:58] daap: Could not find requested meta field (11) [2010-09-15 01:59:58] daap: Done with playlist [2010-09-15 01:59:58] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 2;' [2010-09-15 01:59:59] daap: Done with meta tag dmap.itemname (Movies) [2010-09-15 01:59:59] daap: Done with meta tag dmap.itemid (3) [2010-09-15 01:59:59] daap: Done with meta tag dmap.persistentid (3) [2010-09-15 01:59:59] daap: Could not find requested meta field (8) [2010-09-15 01:59:59] daap: Could not find requested meta field (10) [2010-09-15 01:59:59] daap: Could not find requested meta field (11) [2010-09-15 01:59:59] daap: Done with playlist [2010-09-15 01:59:59] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND media_kind = 64;' [2010-09-15 01:59:59] daap: Done with meta tag dmap.itemname (TV Shows) [2010-09-15 01:59:59] daap: Done with meta tag dmap.itemid (4) [2010-09-15 01:59:59] daap: Done with meta tag dmap.persistentid (4) [2010-09-15 01:59:59] daap: Could not find requested meta field (8) [2010-09-15 01:59:59] daap: Could not find requested meta field (10) [2010-09-15 01:59:59] daap: Could not find requested meta field (11) [2010-09-15 01:59:59] daap: Done with playlist [2010-09-15 01:59:59] db: End of query results [2010-09-15 01:59:59] daap: Done with playlist list, 4 playlists [2010-09-15 01:59:59] httpd: Found query string [2010-09-15 01:59:59] dacp: DACP request: /ctrl-int/1/getproperty?properties=dmcp.volume&session-id=100 [2010-09-15 01:59:59] dacp: Asking for 1 properties [2010-09-15 01:59:59] dacp: Found 1 properties [2010-09-15 01:59:59] player: Player status: stopped [2010-09-15 02:00:00] httpd: Found query string [2010-09-15 02:00:00] daap: DAAP request: /databases/1/containers/1/items?session-id=100&meta=dmap.itemname,dmap.itemid,daap.songartist,daap.songalbum,dmap.containeritemid,com.apple.itunes.has-video,daap.songuserrating,daap.songtime&type=music&sort=album&query='daap.songalbumid:0' [2010-09-15 02:00:00] daap: Fetching song list for playlist 1 [2010-09-15 02:00:00] daap: Asking for 8 meta tags [2010-09-15 02:00:00] daap: Found 8 meta tags [2010-09-15 02:00:00] daap: Sorting songlist by album [2010-09-15 02:00:00] daap: DAAP browse query filter: 'daap.songalbumid:0' [2010-09-15 02:00:00] daap: Trying DAAP query -'daap.songalbumid:0'- [2010-09-15 02:00:00] daap: DAAP SQL query: -(1 = 1)- [2010-09-15 02:00:00] db: Running query 'SELECT * FROM playlists WHERE id = 1;' [2010-09-15 02:00:00] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND 1 = 1;' [2010-09-15 02:00:00] db: Running query 'SELECT COUNT(*) FROM files WHERE disabled = 0 AND (1 = 1) AND 1 = 1;' [2010-09-15 02:00:01] db: Starting query 'SELECT * FROM files WHERE disabled = 0 AND 1 = 1 AND (1 = 1) ORDER BY album COLLATE DAAP ASC, disc ASC, track ASC LIMIT -1 OFFSET 0;' [2010-09-15 02:00:40] xcode: Determining transcoding status for codectype mpeg [2010-09-15 02:00:40] xcode: Codectype is in no_transcode [2010-09-15 02:00:40] daap: Investigating dmap.itemname [2010-09-15 02:00:40] daap: Done with meta tag dmap.itemname (XXXXXXXXXXXXX) [2010-09-15 02:00:40] daap: Investigating dmap.itemid [2010-09-15 02:00:40] daap: Done with meta tag dmap.itemid (32236) [2010-09-15 02:00:40] daap: Investigating daap.songartist [2010-09-15 02:00:40] daap: Done with meta tag daap.songartist (XXXXXXXXXXXXXX) [2010-09-15 02:00:40] daap: Investigating daap.songalbum [2010-09-15 02:00:40] daap: Done with meta tag daap.songalbum (XXXXXXXXXXXX) [2010-09-15 02:00:40] daap: Investigating dmap.containeritemid [2010-09-15 02:00:40] daap: Done with meta tag dmap.containeritemid (32236) [2010-09-15 02:00:40] daap: Investigating com.apple.itunes.has-video [2010-09-15 02:00:40] daap: Done with meta tag com.apple.itunes.has-video (0) [2010-09-15 02:00:40] daap: Investigating daap.songuserrating [2010-09-15 02:00:40] daap: Done with meta tag daap.songuserrating (0) [2010-09-15 02:00:40] daap: Investigating daap.songtime [2010-09-15 02:00:40] daap: Done with meta tag daap.songtime (191973) [2010-09-15 02:00:40] daap: Done with song -- To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org
