Raptus wrote:
Peter;177773 Wrote:
Just so you know, 18.000 records is *nothing* for MySQL.
Yes that is true. As long as you don't memory starve it. Otherwise
you'll put the HD speed in the equation...
My DB data file is about 240MB, yet SlimServer comes with MySQL
configured to the default innodb_buffer_pool_size, an that is 16MB.
Guess what happens...
From the MySQL reference manual:
innodb_buffer_pool_size
The size in bytes of the memory buffer InnoDB uses to cache data and
indexes of its tables. The larger you set this value, the less disk I/O
is needed to access data in tables. On a dedicated database server, you
may set this to up to 80% of the machine physical memory size. However,
do not set it too large because competition for physical memory might
cause paging in the operating system.
So setting it to 64MB seems reasonable for larger collections on a
machine with 1GB RAM.
It's debatable if slimserver should be using InnoDb tables at all. AFAIK
MyIsam is faster because it has less overhead. Yes, InnoDb is better for
transactions and maintaining db integrity, but who needs that on a music
server? It' s not that hard to do a full rescan.
I still have a hard time believing that this is a MySQL issue, even
InnoDb should be plenty fast enough.
I just turned on SQL debug messages and added a 'Music Folder' folder
'Classical' to an empty playlist. I see slimserver doing a SELECT
command for every file in that folder (1500 in total):
SELECT [stuff] FROM tracks me WHERE ( url = ? ):
'file:///peter/shared/music/Classical/Beethoven%20-%20Piano%20Sonatas%20-%20Claudio%20Arrau/Beethoven%20-%20Piano%20Sonatas%20-%20Disc%2003/10%20Sonata%2008%20in%20C%20Op.13%20-%20%27Pathetique%27%20-%20III%20-%203-Rondo%20allegro.mp3'
This is a very inefficient of course. You would expect it to do a single
wildcard select in this case:
SELECT [stuff] FROM tracks me WHERE ( url like? ):
'file:///peter/shared/music/Classical/%'
It's not surprising at all that this is a slow operation.
Then slimserver does 1500 single inserts, like this:
INSERT INTO playlist_track (playlist, position, track) VALUES (?, ?, ?):
'351', '354', '8399'
Surely there's a more efficient way thinkable
Check out this link for multiple inserts:
http://www.petefreitag.com/item/379.cfm
Or use an INSERT...SELECT to pump the tracks into the playlist directly
(or via a intermediate temporary table):
http://mysqld.active-venture.com/INSERT_SELECT.html
This is what happens when I add this dir (--d_playlist):
2007-02-05 11:18:40.8503 scanPathOrURL: Finding valid files in:
/peter/shared/music/Classical
2007-02-05 11:18:40.8552 About to look for files in
/peter/shared/music/Classical
2007-02-05 11:18:40.8595 For files with extensions in:
[(?i-xsm:\.(?:ape|wma|asf|wax|asx|lnk|wpl|shn|wav|wave|mp3|mp2|m3u|pls|xspf|flc|flac|m4a|mov|m4b|mp4|cue|mp+|mpc|ogg|aif|aiff)$)]
2007-02-05 11:18:41.3799 Found 1498 files in /peter/shared/music/Classical
2007-02-05 11:19:31.4622 Reshuffling, current song index: -1, preserve
song? no
2007-02-05 11:19:31.5587 Playlist: Jumping to song index: 0
2007-02-05 11:19:31.5593 new playlistindex: 0
Adding 1500 files takes 50 seconds. This is very very slow, and I'm
totally convinced this could be speeded up by at least an order of
magnitude by simple optimization in the way slimserver handles the
database. Basically, the SQL engine should be allowed to do more of the
work.
Regards,
Peter
_______________________________________________
discuss mailing list
[email protected]
http://lists.slimdevices.com/lists/listinfo/discuss
