LMS DB Optimizer

LMS DB Optimizer is a plugin for LMS to improve it's database performance and memory use.

Note

Only SQLite is supported, don't install this if your LMS uses MySQL.

Features

  • sets a number of performance-related parameters for LMS' SQLite databases
  • adjusts database cache size
  • enables PRAGMA optimize to (potentially) optimize the database when LMS is shutting down
  • enables Memory-Mapped I/O (mmap) for the three main databases ( 3*384MB, non-Windows / non-OpenBSD only)
  • enables multi-threading for SQLite (2 threads)
  • adds some generally beneficial database indices
  • adds some database indices for TrackStat

Status

  • beta quality
  • only tested on Linux

Installation

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.
For best results, you should also set Settings > Advanced > Performance > Database Memory Config to Maximum (unless your server has < 1GB RAM, in which case all additional caching will be disabled).

Technical background

LMS database cache

LMS mainly uses 4 databases (library, persist, artwork, imgproxy). Maximum memory config results in 2*2 GB database cache (artwork and imgproxy get only 80MB), which seems a tad excessive (while High is only 4*80 MB, which seems rather low). This plugin sets the cache to 2*512 MB + 256 MB + 256 MB (2*256 MB + 128 MB + 128 MB if mmap is used, as this is in addition to the memory used for that) for Maximum instead.
Note: All cache sizes are upper bounds, not fixed sizes. They will start at zero and not grow larger than the (file) sizes of the databases.

Various tweaks
  • journal_size_limit for artwork and imgproxy is set to 128 MB instead of 50 MB (Rationale: everyone should have enough free space on the DB's storage anyway)
  • auto_vacuum is set to incremental instead of full for artwork and imgproxy, and incremental_vacuum is executed at shutdown (Rationale: DBI's AutoCommit leads to overhead at full)

Other methods to improve performance

  • For some reason, recent versions of DBD::SQLite disable the sqlite_stat4 table (used by the query planner "to devise better and faster query algorithms"), which used to be enabled in some earlier versions. This is compile-time option, so you'll have to re-compile DBD::SQLite after uncommenting the line '-DSQLITE_ENABLE_STAT4' in Makefile.PL to re-enable it. You can check the current status using the getCompileOptions API command (if ENABLE_STAT4 is missing from them, it's disabled).
  • The DBD::SQLite version used by LMS is v1.58 from 2018 (SQLite v3.22.0). It can be updated indepently from LMS by building / installing a newer version of of the module (./buildme.sh DBD::SQLite, see Using newer perl modules).

API

A JSON/RPC API is available at http://lmsserver:9000/plugins/LMSdbopt/js.html?cmd= where cmd can have the following values:

  • getDatabasePragmas
  • getCompileOptions
  • getStatsTables
  • getOptimizeSteps
  • getDBSizes
  • getMemInfo
  • runDBIntegrityCheck
  • dropExtraTrackStatIndices
  • startDBTrace (logs to /tmp/dbtrace.log)
  • stopDBTrace

Example: wget -q -O - "http://lmsserver:9000/plugins/LMSdbopt/js.html?cmd=getDatabasePragmas"

FAQ

How do I know if it's actually installed / doing something?
Check in LMS web interface (Settings/Plugins), and the server log for Plugins::LMSdbopt::Plugin entries. Or use the getDatabasePragmas API command.
How much will this increase performance?
No idea, as it will depend on a number of factors and the specific environment. Also, PRAGMA optimize is supposed to "achieve the best long-term query performance", so it should improve over time.
What about memory?
mmap can actually result in less memory being used (on my setup, LMS uses ~300MB instead of ~400MB (after running the benchmark 100 times)). However, the artwork / imgproxy databases now being actually cached will result in an increase.
Will this lower stability?
Unlikely
How do I check if mmap really is enabled?
run (as root, or with sudo): 
lsof |grep mem-r|grep 'persist.db$\|library.db$\|artwork.db$\|imgproxy.db$'

If you don't see something like this:

slimserve 15500  squeezeserver  mem-r  REG  .. /var/lib/logitechmediaserver/cache/persist.db
slimserve 15500  squeezeserver  mem-r  REG  .. /var/lib/logitechmediaserver/cache/library.db
slimserve 28729  squeezeserver  mem-r  REG  .. /var/lib/logitechmediaserver/cache/artwork.db
slimserve 28362  squeezeserver  mem-r  REG  .. /var/lib/logitechmediaserver/cache/imgproxy.db

it isn't.

Support

There is a thread in the LMS forums.

License

GNU GPLv3