SQLite3 optimization to improve media search speed?

[hadr0n 3]

Hi,

I find the media search quite slow (around 5 seconds required on most case). I think it is normal and simply due to the size of my library. Anyway, I'm looking for ways to speed things up, if possible. For context:

- Emby version: 4.9.3.0 running bare-metal on Debian 13

- Hardware: Emby is installed on a rather low-end SSD (but still an SSD), Intel core 12100 CPU (4 cores / 8threads) 128Gbytes or ram (should not be the bottleneck). My point being the SQlite files are on SSD.

- Media library: on a big pool of hard drives running ZFS in raidz3 mode (5+3)

Since what is a bit slow at my taste is the media search, I'm looking at ways to optimize SQLite (in particular reads). I don't have many concurrent users. I changed the database cache parameter to 4096Mbytes and restarted the Emby server (systemctl restart) and I did the "clean DB at startup". Search still feels slow, though browsing through the site might seem faster.

So my question now is (I'm unfortunately not a DB expert):

- Is it safe to change SQLite parameters (sqlite3 library.db) manually outside the Emby software? I guess yes.

- I noticed WAL is enabled and that 'synchronous' is set to FULL . According to what I understand from SQLite3 doc (https://sqlite.org/pragma.html#pragma_synchronous) it seems to be safe enough to set synchronous to NORMAL when WAL mode is enabled. Would you advise against it?

- PRAGMA cache_size yields -2000 (which means 2000kbytes of cache) and not 4096Mbytes like I have set in the WEB GUI, but I guess it is because Emby relies on the "Application Defined Page Cache". Or is there an issue with the size of the cache?

- mmap_size is set to 0, apparently this settings is the number of bytes that can be accessed using memory-mapped I/O and apparently it allows to use an OS cache for SQLite file. Should I allocate a few Gbytes to it?

- temp_store is set to 0 by default (file), apparently setting up to 2 could make SQLite store some indexes and temporary tables in RAM
- Other ideas for search optimization?

Thanks.

 

 

 

Edited 3 hours ago by hadr0n