Library.db size more than doubled in latest version

Plex Media Server
,
207

I ran some tests comparing methods of deleting the statistics data from a bloated database.

This is definitely not scientific, but hopefully provides an idea of the speed of each option.

Faster storage will help any method. Faster CPU cores will help when using the “delete from statistics_bandwidth…” command as it is single threaded.

Setup:

  • db: ~215 MB db, bloated to ~33 GB (a backup copy from my server).
    statistics_bandwidth size = 571483322
  • PC: Win 10 (4790K) with m.2 PCIe 3.0 x4 SSD (PMS server in use, so used my desktop instead).
  • PMS: 1.41.7.9799 (although I doubt it matters).

Results

  1. Create/Drop Table as outlined by @hlve (here)
    Four threads running at ~20% ea., SSD at 90 - 100% utilization.
    Time: 957 sec (15:57)

  2. Change journal_mode as outlined by @twig123 (here)
    One thread at ~85%, SSD at 70-80% utilization.
    Time: 2254 sec (37:35)

  3. Delete from statistics_bandwidth without changing journal mode
    One thread at ~85%, SSD at ~40% utilization.
    3627 sec (1:01:13)

statistics_bandwidth = 24513 for all three methods.


Additional Details

Below are the details for each test.

.timer ON shows the time for each SQLite command.
real = elapsed time. See this SQLite Forum post for more info.

#1 Create/Drop Table 
c:\>dir "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\*.db"
05/17/2025  15:10    33,120,415,744 com.plexapp.plugins.library.db

c:\>"C:\Program Files\Plex\Plex Media Server\Plex SQLite.exe" "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db"
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> CREATE TABLE statistics_bandwidth_new AS SELECT * FROM statistics_bandwidth WHERE account_id IS NOT NULL;
Run Time: real 250.699 user 29.546875 sys 60.203125
sqlite> DROP TABLE statistics_bandwidth;
Run Time: real 706.498 user 34.828125 sys 156.484375
sqlite> ALTER TABLE statistics_bandwidth_new RENAME TO statistics_bandwidth;
Run Time: real 0.011 user 0.015625 sys 0.000000
sqlite> CREATE INDEX index_statistics_bandwidth_on_at ON statistics_bandwidth ('at');
Run Time: real 0.008 user 0.000000 sys 0.000000
sqlite> CREATE INDEX 'index_statistics_bandwidth_on_account_id_and_timespan_and_at' ON 'statistics_bandwidth' ('account_id', 'timespan', 'at');
Run Time: real 0.013 user 0.015625 sys 0.000000
sqlite> vacuum;
Run Time: real 3.475 user 0.562500 sys 2.312500
sqlite> .quit

c:\>dir "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\*.db"
05/26/2025  18:57       216,965,120 com.plexapp.plugins.library.db
c:\>
#2 Change Journal Mode 
c:\>dir "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\*.db"
05/17/2025  15:10    33,120,415,744 com.plexapp.plugins.library.db

c:\>"C:\Program Files\Plex\Plex Media Server\Plex SQLite.exe" "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db"
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> pragma journal_mode=delete;
delete
Run Time: real 0.027 user 0.000000 sys 0.000000
sqlite> DELETE FROM statistics_bandwidth WHERE account_id is NULL;
Run Time: real 2254.959 user 740.109375 sys 406.968750
sqlite> VACUUM;
Run Time: real 3.548 user 0.703125 sys 2.250000
sqlite> pragma journal_mode=wal;
wal
sqlite> .quit

c:\>dir "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\*.db"
05/26/2025  22:22       216,948,736 com.plexapp.plugins.library.db
#3 DELETE FROM statistics_bandwidth 
c:\>dir "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\*.db"
05/17/2025  15:10    33,120,415,744 com.plexapp.plugins.library.db

c:\>"C:\Program Files\Plex\Plex Media Server\Plex SQLite.exe" "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db"
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> delete from statistics_bandwidth where account_id is NULL;
Run Time: real 3627.571 user 2037.015625 sys 576.750000
sqlite> vacuum;
Run Time: real 3.727 user 0.703125 sys 1.875000
sqlite> .quit

c:\>dir "%LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\*.db"
05/26/2025  20:21       216,948,736 com.plexapp.plugins.library.db
c:\>
10 Likes