HOWTO: Periodically clean up and reindex Emby Library.db

[Widestorm 3]

Important Update

 

Vacuuming is built into newer server versions. Please see here:

https://emby.media/community/index.php?/topic/75510-42032-vacuum-database-config-switch/

 

 

 

So I recently had the problem that Emby took forever to show any Channels when switching to Live TV and was generally slow. I'm using a HomeRun Expand with approximately 350 channels.

Keep in mind I have already tried disabling any plugins I'm not using without any success.

 

Reading through some forum posts I fould a fix that has seemed to improve the performance of the loading by quite a bit.

Thank you kjp4756 

https://emby.media/community/index.php?/topic/47386-extremely-slow-web-ui-performance/

 

His solution was intended for a Linux installation, but that's not a problem. On https://www.sqlite.org/download.html you can get the sqlite3 utility seperately. You can find it under "Precompiled Binaries for Windows" under sqlite-tools-win36-x86-xxxxxx.zip

 

So what you can do is just run that command in a cmd. The easiest way to do this is to shift+right-click in the folder you extracted sqlite3.exe to and klick "Open command window here" and running the commands as following:

 

Make sure to stop Emby before executing the query!

sqlite3 %AppData%\Emby-Server\programdata\data\library.db "VACUUM"
sqlite3 %AppData%\Emby-Server\programdata\data\library.db "ANALYZE"
sqlite3 %AppData%\Emby-Server\programdata\data\library.db "REINDEX"

After that you can restart the server and hopefully enjoy some faster browsing.

 

 

I have also created a script that automatically stops, cleans up the database and starts Emby again. Useful if you want to create a scheduled task that does the work.

Make sure to put both scripts as well as sqlite3.exe in the same folder or alternatively adjust the Parameters in the reindex-embydb.ps1 file. And keep in mind to run the script with the user Emby was installed with.

 

I'm stopping and starting the server using these functions (it's also creating a logfile in the specified directory):

# Shutdown Emby
function Emby-Shutdown {
    param([Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,Position=0)]$Hostname,
        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,Position=0)]$Port)
    $Url= "http://" + $Hostname + ":" + "$Port" + "/emby/System/Shutdown"
    $Status = Invoke-WebRequest -Uri $Url -Method POST -Body ($params|ConvertTo-Json) -ContentType "application/json"
    if ($Status.StatusCode -eq 204){
        LogWrite "Emby is shutting down..."
    }
}
Emby-Shutdown -Hostname localhost -Port 8096

# Start Emby
function Emby-Start {
    $ProcessActive = Get-Process EmbyServer.exe -ErrorAction SilentlyContinue
    if($ProcessActive -eq $null) {
        LogWrite "Emby is starting..."
        & $env:APPDATA\Emby-Server\system\EmbyServer.exe
    }
    else {
        LogWrite "Emby was already running!" -ForegroundColor Red
    }
}
Start-Emby

reindex-embydb.zip

Edited January 28, 2020 by Luke

[Luke 39701]

Thanks for the info.

[K-O-K 7]

I think this is something that should be integrated in Emby Server and by default running in the Scheduled Tasks

[nagetech 69]

One thing I noticed is, I don't have a folder named programdata, rather it just is called data. I'm guessing I should modify the script to reflect that. But curious, why do I have a different folder structure?

[Luke 39701]

The structure may have changed slightly over time.

[Happy2Play 9366]

One thing I noticed is, I don't have a folder named programdata, rather it just is called data. I'm guessing I should modify the script to reflect that. But curious, why do I have a different folder structure?

 

New installations have  \Emby-Server\programdata and \Emby-Server\system structure.  Older installation have all the programdata contents in \Emby-Server\.

[nagetech 69]

What kind of gains should one notice after running this? just curious to know what to test as a before and after.

[nagetech 69]

Actually I decided to stop being a chicken and just give it a whirl. My Database file went from 1.26GB to 965MB. So thats nice! I'm going to go click around to make sure everything is still good, and hoping to see how performance increases

[Luke 39701]

Thanks for the feedback.

[nagetech 69]

At first glance, it does feel like it is a little more responsive. I went ahead and googled and read up on what those commands do, and they make sense to me. I do look forward to this being built into emby itself for all the obvious reasons, but until then may add this to my monthly server maintenance routine. Every little bit helps!

[Jdiesel 1284]

I gave this a go out of curiosity last night and it seems to have made a pretty big difference loading images and browsing the library on my system. My database was creating during the .NET Core migration so I'd say it is over a year old now and has a fair amount of activity as far as changes in content. 

 

For those running Ubuntu here are the steps:

sudo service emby-server stop
sudo sqlite3 /var/lib/emby/data/library.db "VACUUM"
sudo sqlite3 /var/lib/emby/data/library.db "ANALYZE"
sudo sqlite3 /var/lib/emby/data/library.db "REINDEX"
sudo service emby-server start

[CBers 7064]

Aren't there duplicated tables in the new Beta version (v3.6.x and later) of the database, if an upgrade from an earlier version was done?

[Happy2Play 9366]

Aren't there duplicated tables in the new Beta version (v3.6.x and later) of the database, if an upgrade from an earlier version was done?

 

Yes, if an upgrade from an earlier version was done.

[ecs0set 3]

Excellent thread as I have occasional performance issues with Emby.  This doesn't seem to work with a service which runs under a different service account as my setup does.

 

I've hacked the script above for use with nssm (attached).

 

User beware, I haven't added much error handling and I've only tested it on my setup twice.  I'm sure someone with dev skills that aren't 15 years old would be able to overload the existing function calls using the Powershell parameter hack.

reindex-embydb-service.zip

[mscrivo 14]

I can confirm, doing this makes a big difference.

[pgriffith 73]

This makes my system MUCH more responsive. Any plans to build this functionality into server as a scheduled task?

[Luke 39701]

It's possible for the future. Thanks.

[Luke 39701]

How to vacuum your Emby server database:

 

https://emby.media/community/index.php?/topic/75510-42032-vacuum-database-config-switch/

 

Thanks.

[WilsonF 0]

On 18/07/2019 at 02:24, Luke said:

How to vacuum your Emby server database:

 

https://emby.media/community/index.php?/topic/75510-42032-vacuum-database-config-switch/

 

Thanks.

Does the vacuum command also reindex the database or is it worth while me reindexing in sqlite as well?

[Luke 39701]

Yes it will.