Confused about database cache size

[nightknight 35]

Sorry im still a little confused about setting up the database cache size and the row limit. 

I've never really touched them because it suggests that you just leave it at default but recently iv'e been reading to set the cache size to the size or greater than the database itself. However emby suggests to leave them default.

 

So which is it? should i set the cache size in MB to the size or larger than the database or or leave it default?  Row limit is confusing too. for example what if a library consists of 200k+ media files, what would be a good number for the analysis row limit/cache size?

How much do these values actually effect the server performance? suppose someone did just leave the cache to default but had a 6GB library.db file / how exactly would the row analysis setting affect the performance of the server?

Edited June 4, 2025 by nightknight

[Q-Droid 989]

If your library DB is 6GB then definitely do not leave cache size at default but it doesn't need to be 6GB either. It depends on how much you can spare so start with 1 or 2GB and see if it improves. But unless Emby pre-pages data from the DB the first access might be slower until enough active data is loaded into cache. Then adjust after that, up or down, until you're good with the results.

The cache size value is an "up to" as needed setting. It's the (suggested) upper limit of how much memory can be used for DB page cache. Obviously if the cache is set larger than the DB  size the amount of memory used won't go that high. Cache sizing is the most direct way you as the user can affect performance of the database operations. I haven't seen (nor looked into) if Emby has a way to provide DB stats but what you want is a high cache hit ratio of memory-to-I/O operations for better performance. There's a point of diminishing returns at which adding more cache memory will not result in added performance because all data under management is not always active data. In laymen's terms you're not always accessing all of the data so it won't be cached unless Emby is written to pre-page the data.

Analyze is an indirect performance optimization. It collects stats (information about the indexes) used by the DB engine to optimize query/statement execution. I believe the limit for this is 1000 (rows) though this doesn't mean it only examines that many rows, there are other steps involved. The higher the value the longer an analysis can take but this might still be a matter of seconds so you go as high as you can tolerate. I think Emby runs analyze on startup/DB open but it might run periodically after startup. The devs would know.