Plugin to test database tweaks

[JJZolx]

Have you seen this? I'm not sure if this is version-specific, but it does seem to indicate that your plugin can't just turn auto_vacuum on and off on the fly.

Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referrer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.

More at: http://sqlite.org/pragma.html#pragma_auto_vacuum

One thing I was thinking might be handy, since I'm doing it already in a roundabout way: Having the ability to schedule a nightly database maintenance operation from within LMS, much the same as we can do a nightly library rescan. In fact, ideally you'd be able to run it right after the scan has run, both to avoid running them simultaneously, and also to optimize the library database for that day's use.

[mherger]

"However, changing from "none" to "full" or "incremental" can only occur when the database is new (no tables have yet been created) or by running the VACUUM command."

That's why somebody noted you'd have to run a VACUUM after enabling the flag to make it stick.

[JJZolx]

Ah... I missed that. Are you using the full or incremental setting?

[DJanGo]

Have you seen this? I'm not sure if this is version-specific, but it does seem to indicate that your plugin can't just turn auto_vacuum on and off on the fly.



More at: http://sqlite.org/pragma.html#pragma_auto_vacuum

Hi,

please check 1 means enable incremental vacuum see http://www.tutorialspoint.com/sqlite/sqlite_vacuum.htm

root@muzzigbox:# sqlite3 /var/lib/squeezeboxserver/cache/library.db "PRAGMA auto_vacuum;"
1

[DJanGo]

> If i am searching "putumayo" its very (kinda faster than it was) fast
> and shows me all Albums that starts with "Putumayo presents".
> But if i search after e.g. "the girl" it needs the same amount of time
> than without the plugin and shows me all Tracks from "Everything but the
> Girl"

You don't mention what parameter you changed. Installing the plugin won't do anything.

--

Michael

*lol* dont call me schnitzel please use "someone" or crazy professor ;-)

I Just stopped the server drop the db files set the Buffersize to 100 MB and edit whats going on.
[edit]
id changed the buffer to 100 MB and a full scan needs 7808 seconds for 60.733 tracks (idv2.3 only all with embedded artwork) - thats 20 mins more than without the settings.
Searching was bit faster so i drop the unneeded indizes - fragmentation for lib.db goes up to 5% db size was 92.9 MB
and searching was like normal.

But after i press fragment lib.db the buffersize was [as shown in the webgui] set to "" - just set to 100.
The lib.db after that was 87.4 MB.

[/edit]
what kind of information you need?

[mherger]

Plugin to test database tweaks

> Ah... I missed that. Are you using the full or incremental setting?

Full.

--

Michael

[mherger]

Plugin to test database tweaks

> I also knew that vacuum is a good thing and can tell is realy worth to
> do (even if a dont rescan always drop db and do a freshscan).
> The "new" feature to look @ the db filesizes via webgui is something
> "some" not so experienced Endusers will kiss your feets ;-)

The auto_increment is not about performance, but about disk space
recovery only. There's even a chance that it would harm performance, due
to increased fragmentation. Only the full vacuum does re-organize the
file internally to clean up fragmentation. The plugin's UI is probably a
bit misleading, as what is reported as "fragmentation" actually is the
percentage of unused disk space occupied by the db file.

I guess that the performance improvement you've seen wouldn't become
reality with enabling auto_vacuum only. But I still consider it an
option for the db files which change a lot (caches).

--

Michael

[Mnyb]

I did the vacuum thing shrank some files considerably .

See the pictures . I'll report other findings if i notice anything (mid size collection 42k files not very fast server anyway.. )

[PasTim]

I notice that my persist.db is much larger than many others reported, at around 411MB, even with a smallish collection of < 25,000 tracks. This is because I have several custom tags, this being the only way I have found to properly index classical music. I believe the fragmentation got severe for every rescan.

[DJanGo]

I guess that the performance improvement you've seen wouldn't become
reality with enabling auto_vacuum only. But I still consider it an option for the db files which change a lot (caches).
Michael

i know it has nothing to do with that, cause i vacuum (like i wrote earlier in this thread) the db after each clear and scan so i had "no other" feeling of the time consumption on a not vacuumed db - cause i never ever had one.....

The Big performance improvement was after i screw the buffer size to 100 mb (cause of my library.db was 93.7 MB).
[Hab ich doch extra zwischen eckige klammer auf B eckige klammer zu gemalt]? *lol*

[erland]

No, I didn't take a note of it, but what Custom Scan seems to do every time is a large number of deletes followed by the inserts. Once I discovered vacuuming I've been running it every time after a scan, and it's been a great improvement. Your tool makes it that much easier.

Just to confirm, Custom Scan will delete all data and re-create it every time you rescan.
If there are more than 40000 rows for the Custom Tag/Mixed Tag module in the customscan_track_attributes table, it will instead drop the table and recreate it, since DELETE is too slow when the table gets large.

I wonder whether the scanning for Custom Scan reads a lot of data from library.db. I suspect it does. If so, a larger buffer might help.

Custom Tag module should for each track do a:

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

Mixed Tag module will do INSERT based on SELECT, like for example:

INSERT IGNORE INTO customscan_track_attributes 
    (
       track,
       url,
       musicbrainz_id,
       module,
       attr,
       value,
       valuesort,
       extravalue,
       valuetype
    ) 
    SELECT 
        tracks.id,
        tracks.url,
        case when tracks.musicbrainz_id like '%-%' then tracks.musicbrainz_id else null end,
        'mixedtag',
        ?,
        contributors.name,
        contributors.namesort,
        contributors.id,
        'artist' 
    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

There will be a SQL like this for each tag you have configured in the Mixed Tag scanning module.

I suspect the buffer might help with Mixed Tag scanning module but it feels like it will have less impact on the Custom Tag scanning module. However, I haven't had the time to try this myself and I suspect my library is probably too small to notice any difference. If auto vacuum works, I suspect it will probably be the biggest improvement related to Custom Scan related performance.

[Wirrunna]

Back from the sticks for a day.

First off, I have moved my LMS servers from Windows to VortexBox 2.3, but my workstations are still on Win 7 with SSD and RAMDisk, so I changed CACHE from RAMDisk to a fragmented WD 640Gb work disk for this testing.

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

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 .

Browse New Music - alsmost instant (used to stop server and music playback for a minute or two - the reason I went to RAMDisk !)
Search Albums for "the girl" - first time took 54 seconds, player stopped with re-buffering. Second search almost instant.
New Albums 2 seconds.

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.

Memory :Hard Faults/sec - Commit (KB) - Working Set (KB) - Shareable (KB) - Private (KB)
SQUEEZ~3.EXE 0 - 314,692 - 332,528 - 23,044 - 309,484

Tweak Buffer Size - 500
Searches are returned in 1~2 seconds, New Music almost instant, New Albums 2 seconds.
Memory Usage
SQUEEZ~3.EXE 0 - 435,664 - 450,940 - 23,044 - 427,896

Restarted LMS with Tweak Buffer set to 500, took almost 3 minutes, however search albums for "the girl" almost instant. Other searches, New Music, New Albums and memory useage are as above.

Back to the farm for a few days then down the coast for more testing.

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.

[PasTim]

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.

But please also keep the vacuum options for those of us for whom it also makes a really big difference

[Wirrunna]

But please also keep the vacuum options for those of us for whom it also makes a really big difference

I stand corrected.

OK, I have removed the RAMDisk and the system has returned the 2GB to the memory pool. Also moved cache back to C:\.....cache on the SSD.
Have run lots of searches, browses and lookups, nothing hangs like it used to a year or so ago. Checked memory with the Win 7 Resource Monitor -

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

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.

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

[mherger]

Plugin to test database tweaks

> Just to confirm, Custom Scan will delete all data and re-create it every
> time you rescan.
> If there are more than 40000 rows for the Custom Tag/Mixed Tag module in
> the customscan_track_attributes table, it will instead drop the table
> and recreate it, since DELETE is too slow when the table gets large.

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.

> Custom Tag module should for each track do a:

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?

> SELECT
> tracks.id,
> tracks.url,
> case when tracks.musicbrainz_id like '%-%' then tracks.musicbrainz_id else null end,
> 'mixedtag',

(See - I didn't even know such a thing as a case within a select was
available!..)

> ...
> 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

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?

But that's rather OT.

> If auto vacuum
> works, I suspect it will probably be the biggest improvement related to
> Custom Scan related performance.

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? A full vacuum might help re-arrange things to keep them together.
But audo_vacumm does not.

--

Michael