[Announce] Database Optimizer plugin

[Roland0]

DB Optimizer is a plugin for LMS to improve it's database performance and memory use.
More information can be found on the homepage.

[SpiderJon]

DB Optimizer is a plugin for LMS to improve its database performance and memory use.
More information can be found on the homepage.

Very interested in trying this.

However, I followed the installation routine —

In the LMS web interface in Settings > Plugins, add https://www.nexus0.net/pub/sw/lmsdboptim/repo.xml to the list of Additional Repositories, activate the plugin and re-start LMS.

— but "DB Optimizer" didn't get installed.

So I tried activating the plugin (ie, ticking the box), saving settings and restarting LMS three more times, but still no luck installing it — it's just showing as an unticked box in its own "LMS DB Optimizer plugin" section of the Plugins page.

I'm using

LMS Version: 8.1.1 - 1610364019
Operating system: Debian - EN - utf8
Platform Architecture: armv7l-linux
Perl Version: 5.28.1 - arm-linux-gnueabihf-thread-multi-64int
Audio::Scan: 1.02
IO::Socket::SSL: 2.060
Database Version: DBD::SQLite 1.58 (sqlite 3.22.0)

Thanks for any help.

[Roland0]

However, I followed the installation routine —
— but "DB Optimizer" didn't get installed.

So I tried activating the plugin (ie, ticking the box), saving settings and restarting LMS three more times, but still no luck installing it — it's just showing as an unticked box in its own "LMS DB Optimizer plugin" section of the Plugins page.

Anything in the server logs?

[SpiderJon]

Anything in the server logs?

[21-03-02 18:27:09.6876] main::init (388) Starting Logitech Media Server (v8.1.1, 1610364019, Thu Jan 14 06:24:07 CET 2021) perl 5.028001 - arm-linux-gnueabihf-thread-multi-64int
[21-03-02 18:27:11.4689] Slim::bootstrap::tryModuleLoad (286) Warning: Module [Plugins::LMSdbopt::Plugin] failed to load:
Can't locate Plugins/LMSdbopt/Plugin.pm in @INC (you may need to install the Plugins::LMSdbopt::Plugin module) (@INC contains: /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/LMSdboptim/LMSdbopt/lib /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/CustomScan/lib /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/CustomBrowse/lib /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/BBCSounds/lib /var/lib/squeezeboxserver/cache/InstalledPlugins /usr/share/squeezeboxserver/CPAN/arch/5.28/arm-linux-gnueabihf-thread-multi-64int /usr/share/squeezeboxserver/CPAN/arch/5.28/arm-linux-gnueabihf-thread-multi-64int/auto /usr/share/squeezeboxserver/CPAN/arch/5.28.1/arm-linux-gnueabihf-thread-multi-64int /usr/share/squeezeboxserver/CPAN/arch/5.28.1/arm-linux-gnueabihf-thread-multi-64int/auto /usr/share/squeezeboxserver/CPAN/arch/arm-linux-gnueabihf-thread-multi-64int /usr/share/squeezeboxserver/CPAN/arch/5.28 /usr/share/squeezeboxserver/lib /usr/share/squeezeboxserver/CPAN /usr/share/squeezeboxserver /usr/sbin /etc/perl /usr/local/lib/arm-linux-gnueabihf/perl/5.28.1 /usr/local/share/perl/5.28.1 /usr/lib/arm-linux-gnueabihf/perl5/5.28 /usr/share/perl5 /usr/lib/arm-linux-gnueabihf/perl/5.28 /usr/share/perl/5.28 /usr/local/lib/site_perl /usr/lib/arm-linux-gnueabihf/perl-base) at (eval 1056) line 1.
BEGIN failed--compilation aborted at (eval 1056) line 1.

[21-03-02 18:27:11.4699] Slim::Utils::PluginManager::load (323) Error: Couldn't load Plugins::LMSdbopt::Plugin
[21-03-02 18:27:11.7114] Plugins::CustomScan::Scanner::initDatabase (114) CustomScan: Creating database tables
[21-03-02 18:27:11.7133] Plugins::CustomScan::Scanner::initDatabase (119) CustomScan: Upgrading database adding table column valuesort, please wait...
[21-03-02 18:27:11.7139] Plugins::CustomScan::Scanner::executeSQLFile (522) Couldn't open: /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/CustomScan/SQL/SQLite/dbupgrade_valuesort.sql : No such file or directory
[21-03-02 18:27:11.7143] Plugins::CustomScan::Scanner::initDatabase (125) CustomScan: Upgrading database adding table column extravalue, please wait...
[21-03-02 18:27:11.7149] Plugins::CustomScan::Scanner::executeSQLFile (522) Couldn't open: /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/CustomScan/SQL/SQLite/dbupgrade_extravalue.sql : No such file or directory
[21-03-02 18:27:11.7154] Plugins::CustomScan::Scanner::initDatabase (131) CustomScan: Upgrading database adding table column valuetype, please wait...
[21-03-02 18:27:11.7159] Plugins::CustomScan::Scanner::executeSQLFile (522) Couldn't open: /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/CustomScan/SQL/SQLite/dbupgrade_valuetype.sql : No such file or directory

[mherger]

> /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/LMSdboptim/LMSdbopt/lib

That's one folder too many. The plugin archive should extract without a
sub-folder for Plugins.pm.

[Roland0]

[[21-03-02 18:27:09.6876] main::init (388) Starting Logitech Media Server (v8.1.1, 1610364019, Thu Jan 14 06:24:07 CET 2021) perl 5.028001 - arm-linux-gnueabihf-thread-multi-64int
[21-03-02 18:27:11.4689] Slim::bootstrap::tryModuleLoad (286) Warning: Module [Plugins::LMSdbopt::Plugin] failed to load:
Can't locate Plugins/LMSdbopt/Plugin.pm in @INC (you may need to install the Plugins::LMSdbopt::Plugin module) (@INC contains: /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/LMSdboptim/LMSdbopt/lib /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/CustomScan/lib /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/CustomBrowse/lib /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/BBCSounds/lib /var/lib/squeezeboxserver/cache/InstalledPlugins /usr/share/squeezeboxserver/CPAN/arch/5.28/arm-linux-gnueabihf-thread-multi-64int /usr/share/squeezeboxserver/CPAN/arch/5.28/arm-linux-gnueabihf-thread-multi-64int/auto /usr/share/squeezeboxserver/CPAN/arch/5.28.1/arm-linux-gnueabihf-thread-multi-64int /usr/share/squeezeboxserver/CPAN/arch/5.28.1/arm-linux-gnueabihf-thread-multi-64int/auto /usr/share/squeezeboxserver/CPAN/arch/arm-linux-gnueabihf-thread-multi-64int /usr/share/squeezeboxserver/CPAN/arch/5.28 /usr/share/squeezeboxserver/lib /usr/share/squeezeboxserver/CPAN /usr/share/squeezeboxserver /usr/sbin /etc/perl /usr/local/lib/arm-linux-gnueabihf/perl/5.28.1 /usr/local/share/perl/5.28.1 /usr/lib/arm-linux-gnueabihf/perl5/5.28 /usr/share/perl5 /usr/lib/arm-linux-gnueabihf/perl/5.28 /usr/share/perl/5.28 /usr/local/lib/site_perl /usr/lib/arm-linux-gnueabihf/perl-base) at (eval 1056) line 1.
BEGIN failed--compilation aborted at (eval 1056) line 1.

I think I've found the issue. Try removing the repository, restart, add it again, active the plugin, and restart (and report if there are any issues remaining). Thanks.

[erland]

Hopefully someone will take the time to update TrackStat so it creates the indexes by itself. The intention is that TrackStat should already today create some of the single column indexes but maybe that doesn’t work since you have them in your plugin ? As it’s done now I think the indexes will be duplicated since you use different index names, duplicate indexes might decrease write performance.

The index in tracks table should probably be added to LMS code instead of having it as a plugin. The reason TrackStat creates an index in tracks table is just because it was harder to convince/get core developer to do additions to the core 15 years ago, should be easier today when LMS development is driven by community.

Have you performed any tests to see which TrackStat operations that are improved ?

Having them in your plugin as a first step could be a good idea to verify how much improvement it results in, so I’m not criticizing your work just mentioning the above since I think it’s the next step to take.

[Roland0]

> /var/lib/squeezeboxserver/cache/InstalledPlugins/Plugins/LMSdboptim/LMSdbopt/lib

That's one folder too many. The plugin archive should extract without a
sub-folder for Plugins.pm.

It extracts to LMSdbopt/Plugin.pm, which I think is correct. I think the issue was in repo.xml (plugin name="LMSdboptim" instead of plugin name="LMSdbopt")

[mamema]

Hopefully someone will take the time to update TrackStat so it creates the indexes by itself. The intention is that TrackStat should already today create some of the single column indexes but maybe that doesn’t work .

which indexes do you mean? Except those from the temp table, we've discussed in the Dev Area, all needed indexes are there.
Trackstat creates and deletes indexes and both is working.

[Roland0]

Hopefully someone will take the time to update TrackStat so it creates the indexes by itself.

Would certainly be a better solution.

The intention is that TrackStat should already today create some of the single column indexes but maybe that doesnÂ’t work since you have them in your plugin ?

I've been using TrackStat for a long time, and quite a while ago (years) I noticed that some of the default indices were missing - no idea why. Back then, I also added some other indices. Since I was writing this plugin anyway, I decided to include them - since I use 'IF NOT EXISTS', existing ones won't be affected.

As itÂ’s done now I think the indexes will be duplicated since you use different index names, duplicate indexes might decrease write performance.

Which indices do you mean? The ones taken from TrackStat should use the same names afaik.

The index in tracks table should probably be added to LMS code instead of having it as a plugin. The reason TrackStat creates an index in tracks table is just because it was harder to convince/get core developer to do additions to the core 15 years ago, should be easier today when LMS development is driven by community.

I guess Michael would be the person to assess which index is of general benefit.

Have you performed any tests to see which TrackStat operations that are improved ?

Back then, I probably did, but that was a couple of years ago, so I'm rather hazy on the details.

Having them in your plugin as a first step could be a good idea to verify how much improvement it results

Generally, maybe, but since the plugin's main purpose is to change various DB parameters, so it'll be difficult to isolate specifics.