Sqlite3: Sleeping for 200ms to retry busy DB

Plex Media Server
1

i keep getting this on my plex decker logs and plex becomes unreachable only a reboot of the container fixes the issue. is plex aware of this and working on a fix. if yes what is the eta? there post about this all over your forums and years old?

2

A 200ms wait while adding a lot of media, especially when the database hasn’t been optimized, is fairly common. There are several tasks hitting the database at one compounded by it being fragmented.

Scheduled Tasks does optimize the database but at some point, the server should also be restarted for those optimizations to become effective (SQLite merges all those optimizations into the main DB)

There is no “Fixing” a slow database. It’s either optimized properly or it’s not.
If you have a slower CPU – that doesn’t help
If you have slow disk I/O – that really doesn’t help.

What I can offer you is a tool which I wrote , that provides a huge speed improvement to the DB because it reorganizes the DBs while PMS is stopped
(The key to optimizing a database is that it can’t be open & active)

Give it a read.

Understand how to use it.

Try it.

It’s solved just about everyone’s problems to date.

1 Like
3

so these settings dont do anything ? why doesnt plex do something about this or move to a more suitable database?
image

4

I don’t know anything about the hardware you have .
How much media (approximate item count) do you have indexed ?

What’s the system hardware?

Many of us have i7 / i9 / newer Xeon CPUs with NVMe storage for the PMS metadata because of the amount of media we have.

It was necessary to size the hardware to match the amount of media indexed.

5

ryzen 5, 64gb of ram, 1tb nvme
hw is not the issue in this case.

6

How much media do you have indexed?

Did you use my optimization (which is a full export/reload while PMS is shut down) ?

Hardware (per thread speed), because DB access is single-threaded is still likely the issue.

7

i haven’t used your tool yet and i have a lot of media which am not comfortable sharing here but i also think it is beside the point, don’t you think.

8

I’m not asking for a list.

I’m asking “How many items”

( Approximate total of Movies, Episodes, Tracks, and Photos) .

That’s all I ask because each indexed item has direct impact on the DB

The easy way — Provide a “ls -lah” of the “Databases” directory

9

all in total around 50 to 100k including movies and tv shows

10

Thanks. That’s what I needed to know.

I have a good idea why you see the delays you are. (I’ve seen this many times before)

Internal to SQLite (how it stores records) are links / pointers.

After a lot of Insert/update/delete , those chains get long. Can be as much as 10x the amount of media you actually have.

When PMS makes a query, it has to walk ALL those chains.
That takes time.

The tool I have does what PMS can’t (operate on an open database)

I suggest:

  1. Stop PMS

  2. Make a backup copy of the Databases directory (be safe)

  3. As root, run my tool.

  4. You want options 1, 4, and 3

    1. Check
    1. Repair (the export / import which fixes all tables
    1. Reindex (regenerates table indexes – you’ll find ‘search’ is screaming fast again)

PS: I have a newer version of this about to be released. It’s a lot easier to use

11

i will do that and just to make sure we are on the same page your tool has nothing to do with this right ? https://support.plex.tv/articles/repair-a-corrupted-database/

12

If you look at what that page tells you to do, they have some of the steps.
They don’t really tell you WHAT to do.

I do more / the rest of what is needed.

I also have the whole thing automated so you don’t have to worry about screwing it up

13

ok i will follow your steps, run your tool and report back. do i need to run this tools once in a while ?

14

In the next version of the tool, you’ll be able to run it weekly or monthly (your choice) via cron.

To share what it looks like to use:

# sudo ./DBRepair.sh  stop auto start exit
  1. Stops PMS
  2. Auto – runs Check, Repair, Reindex in one step as needed
  3. Starts PMS
  4. Exits
1 Like
15

can u automate the tool using sytemd instead of cron?

16

Sure. ExecStart is fine.
Don’t forget to give it a shell to launch from

It’s only dependencies are:

  1. PMS is installed
  2. It runs as ‘root’
  3. It recognizes the PMS environment it’s running on (
17

so u work for plex. is there a chanche of your tool getting built/ implemented into plex or better yet a different db altogether?

18

this is a little beyond me but i will follow your github for the next version

19

Plex selected SQLite because of the other dependent packages they’d need if they picked something else.

SQLite is plenty for “Joe Typical” user.

I have a test database with 340,000 TV episodes in it and I don’t get any of the 'Sleeping" errors you’re getting.

I really do think your DB is fragmented to all hell.

20

You’re having trouble following running my script ?