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 8 hours ago by hadr0n

[Luke 42304]

HI, Emby sets synchronous to normal.

Quote

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

We are not testing doing this so we can't really make promises.

But cache size, temp store and synchronous all get set on server startup, so anything you do outside of the server will just get changed back.

[hadr0n 3]

Hi, mmh except if I misunderstand something (which is possible), Emby does not apparently set synchronous to "normal" on my system (on the library.db file at least). emby-server is up while I print the synchronous value, so I guess it should display the value configured by the Emby database session. I don't have an idea about how much this would impact search performance though.

[hadr0n 3]

Well "When you run PRAGMA synchronous; in your terminal or SQL editor, you are only seeing the setting for your own connection, not Emby’s." No way to play much with the SQLite settings outside Emby then as I understand.