Plugin to test database tweaks

[erland]

Edit: Starting LMS still takes about 2mins 30secs.

Are you using any of my plugins ?

I'm just asking since I know that some of them run heavy database queries at server startup.

[mherger]

Plugin to test database tweaks

> Logitech Media Server Version: 7.8.0 - 1395409907 @ Thu Mar 27 20:19:17
> CUT 2014
> Hostname: Gladstone, Intel Q9450 2.7GHz Quad Core 8GB RAM
> Total Tracks: 144,947, Total Albums: 11,302, Total Artists: 3,146, Total
> Genres: 104

That's the test case I wanted to see: Windows, lots of tracks :-). Thanks!

> Tweak Buffer Size - 20
> Starting LMS took more than 10 minutes after moving the CACHE from R:\
> to E:\ , Windows Task Manager showed LMS was reading Persist.db and
> Library.db .

But no scan was going on then, right? I wonder what's going on there.

> This is frustrating, most searches are returned in 1~2 seconds where a
> year ago they would lock up the server and stop the players.

Why frustrating? If I got you right, things are much better now?

> These results show the great work already done in optimising LMS 7.8 ,
> and I think that a cut down version of this plugin with just the memory
> slider should be included in LMS as a Settings / Performance function.
> It also shows me that RAMDisk is no longer needed for Windows.

Wow... that would be great! But please continue to use your setup. Let's
wait and see how it behaves in the longer run. Thanks!

--

Michael

[mherger]

Plugin to test database tweaks

> SQUEEZ~3.EXE 0 630,704 643,820 23,052 620,768

That's a lot of memory :-). But still less than running a 2GB ramdisk.

> So compared to memory used when Tweak Buffer is 20, 500 will allow the
> process to expand its memory use, but with 8GB there is still 5GB
> available.

Yes, the 20MB were not enough in my testing with 100k tracks. 500MB
probably is much more than really would be required to make a
difference. But then it wouldn't use it all unless needed.

--

Michael

[erland]

Are you storing your tables in library.db, too?

I was wondering about the dropping of tables (or truncate), too. But
Andy left that note:

-- Use DELETE instead of TRUNCATE, as TRUNCATE seems to need unlocked
tables.

I'd expect a drop table to be as restrictive at least. Did you never
encounter any problems? Because yes, delete is slow.

Does SQLite have a TRUNCATE command ? Isn't this done with SQLite using a DELETE without WHERE ?

Anyway, my problem is that I really need to use DELETE with a WHERE directive so TRUNCATE wouldn't work.

What I do is:

If more than 40000 rows matching the WHERE criteria
-- CREATE TEMPORARY TABLE (temporary table).... AS SELECT ... FROM (original table) WHERE (inversed criteria)
-- DROP TABLE (original table)
-- CREATE TABLE (original table)
-- INSERT IGNORE INTO (original table) SELECT ... FROM (temporary table)
-- DROP TABLE (temporary table)

If less than 40000 rows matching the WHERE criteria
-- DELETE FROM (original table) WHERE ... (criteria)

Just a few comments (and keep in mind I'm no SQL expert at all, only
just read quite a few performance FAQs :-)):

> SELECT id,url from tracks where audio=1 limit 1 offset ?

Wouldn't it be faster to create a statement handler with this query
(without the limit & offset) and iterate it?

It might be faster but it will leave the transaction open during the whole scanning process which results in gigantic WAL files if I remember correctly. I did it the way you suggested first, but I had to change it because it caused problem when the WAL feature was activated in SQLite.

> ...
> from tracks,contributor_track,contributors
> where
> tracks.audio=1 and
> tracks.id=contributor_track.track and
> contributor_track.role=1 and
> contributor_track.contributor=contributors.id[/color]

Could you change the order of the where statements? If I understood the
FAQs correctly (http://www.sqlite.org/optoverview.html), only the first
statement's index would be used to narrow down the selection of records.
In your case that's the "audio=1", which would probably still return
>>90% of all tracks. If you used the tracks.id first, that might
probable speed up the processing?

Thanks, will give a try as soon as I get some time.

TBH: I still don't understand why vacuum would have such a large impact,
as I'd expect the DB to be smart enough to only read those parts of the
data which it requires. Thus the file _size_ shouldn't matter, should
it?

No idea, but apparently it helps a lot for people with big libraries.

[PasTim]

With regard to persist.db, my impression is that the problem is one of disorganisation, possibly of the index records, rather than on of sheer size.

On a complete re-installation with no persist.db, and scanning for the 1st time, the custom browse/scan menus run fine. After the 1st rescan they are slow the 1st time they are run after a
restart. I've several different menus, and each one based on different custom or mixed tags (eg Works by Composer) is slow until run the 1st time.

My impression is that reading the indices into memory was itself taking time, and that once done, and presumably some of the related data, all was well.

I experimented with deleting and recreating one index on persist.db (using a script kicked off via server power control). This worked well except LMS took several extra minutes to start. When I discovered the vacuum command, I ran this once after each rescan and had no menu delay problems.

Apologies for not having precise measurements. All I can say is that with auto-vacuum on all databases, 100MB, and deleting the unused library.db indices, LMS 7.9 is running very well indeed, with almost instant responses to all actions.

[Wirrunna]

Are you using any of my plugins ?

I'm just asking since I know that some of them run heavy database queries at server startup.

License Manager, Custom Browse and Trackstat.
Just checked - Trackstat had startup refresh set to Yes.
Will try a restart with it set to No.

[Wirrunna]

> SQUEEZ~3.EXE 0 630,704 643,820 23,052 620,768

That's a lot of memory :-). But still less than running a 2GB ramdisk.

> So compared to memory used when Tweak Buffer is 20, 500 will allow the
> process to expand its memory use, but with 8GB there is still 5GB
> available.

Yes, the 20MB were not enough in my testing with 100k tracks. 500MB
probably is much more than really would be required to make a
difference. But then it wouldn't use it all unless needed.

--

Michael

Memory use appears to expand and then remain static. I thought it might shrink after some time but after very limited testing and observation it appears to remain allocated. However, modern operating systems are very good at paging out memory that is not accessed after a period of time, depending on memory availability. But with 8GB ram, the system is not going to run short. With Tweak Buffer set to 500 it has only grown by 300, and after re-booting and running a whole bunch of look-ups, New Music browses etc it is currently sitting at
SQUEEZ~3.EXE 0 508,512 526,964 22,324 504,640
with the look-ups etc being very quick.

[Wirrunna]

>

But no scan was going on then, right? I wonder what's going on there.

> This is frustrating, most searches are returned in 1~2 seconds where a
> year ago they would lock up the server and stop the players.

Why frustrating? If I got you right, things are much better now?

> These results show the great work already done in optimising LMS 7.8 ,
> and I think that a cut down version of this plugin with just the memory
> slider should be included in LMS as a Settings / Performance function.
> It also shows me that RAMDisk is no longer needed for Windows.

Wow... that would be great! But please continue to use your setup. Let's
wait and see how it behaves in the longer run. Thanks!

--

Michael

No scan, just starting up - but first time after copying the cache directory from one drive to another.

Frustrating because I expected New Music to take as long as a wet week like it used to, the same for some other look-ups, then, I expected to see some dramatic improvement with Tweak Buffer set to 500. However, the difference was barely noticeable and certainly hard to measure - nothing like the speed up I got when I loaded cache into a RAMDisk (http://forums.slimdevices.com/showth...ith-a-RAM-disk). Also frustrating because the RAMDisk had masked the speedups that had been achieved.

Can you get the Scanner to observe the Tweak Buffer size ?

[Wirrunna]

Please ignore LMS start up timings, they were caused by Trackstat Startup Refresh.

[PasTim]

I should perhaps mention that when I referred to slow custom scan menus after a restart, I'm talking about delays of a minute to two, and if playing music it often stopped for several seconds. Running vacuum completely resolves these problems.

[DJanGo]

These results show the great work already done in optimising LMS 7.8 , and I think that a cut down version of this plugin with just the memory slider should be included in LMS as a Settings / Performance function. It also shows me that RAMDisk is no longer needed for Windows.

+1
But the important line "how big is /var/lib/squeezeboxserver/cache/library.db" should be included as well, cause that info is the base for "how much Buffer should i give"

[mherger]

Plugin to test database tweaks

I've pushed out an updated version 0.6. Changes:

- don't drop the contributor name index - it's being used extensively in
the scanner

- enable the buffer setting in the scanner, too

- change the name of the "waste" column. It's not fragmentation.

If you dropped the indices previously, please re-create them (and drop
them again if you wish to do so).

And please give the scanner with your buffer setting another try. Let me
know what effect changing the buffer in the scanner has. I did run my
tests - I'm looking forward to your results!

Thanks!

--

Michael

[JJZolx]

I've pushed out an updated version 0.6. Changes:

Is the cache.db reset with each restart of the server? I see mine is at 98% waste, which doesn't make a lot of sense if I vacuumed all of the databases a few hours ago and haven't used the server yet today.

Do you automatically tick the vacuum checkbox when waste in a db is greater than some %? I hadn't noticed that before.

[mherger]

Plugin to test database tweaks

> Is the cache.db reset with each restart of the server? I see mine is at

Reset in what way?

> 98% waste, which doesn't make a lot of sense if I vacuumed all of the
> databases a few hours ago and haven't used the server yet today.

Much of the data stored in cache.db is rather short lived (eg. 1h). If
you don't use LMS for a while, the 98% are quite possible.

> Do you automatically tick the vacuum checkbox when waste in a db is
> greater than some %? I hadn't noticed that before.

Yeah, it should be ticked if waste is >30% and the wasted disk space is
larger than 20MB iirc.

--

Michael

[PasTim]

I've a record of a full scan with a few less tracks from March on 7.8.1, then using 7.9 on on your 1st release with 100MB buffer, and one again today with the same setting (and rebuilt indices). Assuming 7.9 is no quicker in itself, my full scan came down from ~ 24 minutes to ~ 19 minutes for the latter 2 scans. For me there seems little or no difference between the very newest version and the previous one with respect to scanning.

To be honest I'm not too bothered about the main scanner since I still have a couple of hours to wait for my custom scan to finish. I forgot to have extra logging on, so I'm not 100% sure how long that takes (it doesn't record completion unless the info log is on). I'll keep an eye on it and give some idea when it finishes.

BTW - I keep a few videos and photos on the same and occasionally view using UPnP.

31/3/14

Logitech Media Server Version: 7.8.1 - 1396040560 @ Sat Mar 29 03:05:16 UTC 2014
Hostname: vortexbox
Server IP Address: 192.168.1.10
Server HTTP Port Number: 9000
Operating system: Debian - EN - utf8
Platform Architecture: x86_64-linux
Perl Version: 5.14.2 - x86_64-linux-gnu-thread-multi
Database Version: DBD::SQLite 1.34_01 (sqlite 3.7.7.1)
Total Players Recognized: 2

Total Images: 1,135
Total Videos: 28
Total Tracks: 24,387
Total Albums: 1,386
Total Artists: 1,764
Total Genres: 97
Total Playing Time: 1964:29:35

Discovering files/directories: /home/crusty/storage/music/flac   (32213 of 32213)   Complete  00:00:39
Scanning new music files: /home/crusty/storage/music/flac   (24387 of 24387)   Complete  00:16:59
Scanning new media files: /home/crusty/storage/movies   (28 of 28)   Complete  00:00:05
Scanning new media files: /home/crusty/storage/pictures   (1135 of 1135)   Complete  00:04:28
Discovering playlists: /home/crusty/storage/music/playlist   (3 of 3)   Complete  00:00:00
Scanning new playlists: /home/crusty/storage/music/playlist   (2 of 2)   Complete  00:00:03
Pre-caching Artwork   (1338 of 1338)   Complete  00:02:23
The server has finished scanning your media library.
Total Time: 00:24:37 (Thursday 27 March 2014 / 22.57.43)

3/5/14

Logitech Media Server Version: 7.9.0 - 1398949384 @ Sat May 3 04:07:25 UTC 2014
Hostname: vortexbox
Server IP Address: 192.168.1.10
Server HTTP Port Number: 9000
Operating system: Debian - EN - utf8
Platform Architecture: x86_64-linux
Perl Version: 5.18.2 - x86_64-linux-gnu-thread-multi
Audio::Scan: 0.94
Database Version: DBD::SQLite 1.34_01 (sqlite 3.7.7.1)
Total Players Recognized: 1
 
Library Statistics
Total Images: 1,135
Total Videos: 28
Total Tracks: 24,686
Total Albums: 1,396
Total Artists: 1,774
Total Genres: 97
Total Playing Time: 1989:18:13
 
Media Scan Details
Discovering files/directories: /storage/music/flac   (32461 of 32461)   Complete  00:00:54
Scanning new music files: /storage/music/flac   (24574 of 24574)   Complete  00:12:24 
Scanning new media files: /storage/movies   (28 of 28)   Complete  00:00:04 
Scanning new media files: /storage/pictures   (1135 of 1135)   Complete  00:04:02 
Discovering playlists: /storage/music/playlist   (3 of 3)   Complete  00:00:00 
Scanning new playlists: /storage/music/playlist   (2 of 2)   Complete  00:00:07 
Pre-caching Artwork   (1349 of 1349)   Complete  00:02:03 
The server has finished scanning your media library.
Total Time: 00:19:34 (Saturday, May 3, 2014 / 17:09:13)

6/5/14

Logitech Media Server Version: 7.9.0 - 1398949384 @ Sat May 3 04:07:25 UTC 2014
Hostname: vortexbox
Server IP Address: 192.168.1.10
Server HTTP Port Number: 9000
Operating system: Debian - EN - utf8
Platform Architecture: x86_64-linux
Perl Version: 5.18.2 - x86_64-linux-gnu-thread-multi
Audio::Scan: 0.94
Database Version: DBD::SQLite 1.34_01 (sqlite 3.7.7.1)
Total Players Recognized: 1
 
Library Statistics
Total Images: 1,135
Total Videos: 28
Total Tracks: 24,685
Total Albums: 1,396
Total Artists: 1,774
Total Genres: 96
Total Playing Time: 1989:14:44
 
Media Scan Details
Discovering files/directories: /storage/music/flac   (32461 of 32461)   Complete  00:00:47
Scanning new music files: /storage/music/flac   (24574 of 24574)   Complete  00:12:17
Scanning new media files: /storage/movies   (28 of 28)   Complete  00:00:04
Scanning new media files: /storage/pictures   (1135 of 1135)   Complete  00:03:55
Discovering playlists: /storage/music/playlist   (3 of 3)   Complete  00:00:00
Scanning new playlists: /storage/music/playlist   (2 of 2)   Complete  00:00:06
Pre-caching Artwork   (1349 of 1349)   Complete  00:01:55
The server has finished scanning your media library.
Total Time: 00:19:04 (Tuesday, May 6, 2014 / 16:55:40)