WARN - Slow Query - 0 results

General Discussions
1

My logs are FILLED with the following:

Jan 28, 2019 17:59:48.032 [13520] WARN - SLOW QUERY: It took 828.125000 ms to retrieve 0 items.
Jan 28, 2019 17:59:48.927 [12872] WARN - SLOW QUERY: It took 781.250000 ms to retrieve 0 items.
Jan 28, 2019 17:59:49.684 [11548] WARN - SLOW QUERY: It took 734.375000 ms to retrieve 0 items.
Jan 28, 2019 17:59:50.403 [13520] WARN - SLOW QUERY: It took 859.375000 ms to retrieve 0 items.
Jan 28, 2019 17:59:51.153 [12872] WARN - SLOW QUERY: It took 1046.875000 ms to retrieve 0 items.

Notice that 0 results are being returned. I have two questions.

  1. What can I do to prevent this message from occurring? This never used to happen and I’ve been running on this same machine for years. I tried to speed things up by moving my Plex folder to an SSD but it literally made no difference in the warnings (though things seem faster when I am loading views)

  2. What is making the query and getting 0 results? Is this some sort of Plex indexing service or something? Any way for me to determine what is making these queries and what the queries might be?

2

The 0 items reported in the log simply means that, for some reason, the query timed out before getting any items. The timings you’re reporting are in the (roughly) 0.7 to 1 second mark, which, these days, is a long time for a computer to obtain a response.
Are these logs being generated on the server system or on a remote access system?
If remote access, what is your remote connection rate?
If server, I’m already stumped.

3

It’s a local server with the DB on an SSD. I agree, that’s very slow.

4

Other posts I’ve seen WRT Slow Query mention a fragmented database.

The fix seems to be:

  1. Stop all Scanning
  2. Optimize database
  3. Rescan ONE library section at a time until all have caught back up
  4. Optimize database between each library section scan

Here’s a post from the Synology section. The OP was having trouble transcoding TrueHD audio, and also had many Slow Query entries in the log. Some of the discussion (iNotify) seems Linux specific, but the rest would apply to any platform.

Point #4 is the relevant part.

5

TrueHD post non-related to this issue.

SLOW QUERY can also occur if the length of the indicies internal to the tables themselves are too long.

This usually happens when an excessive amount of music is indexed.

How many of each type (music, tv, and movies) is indexed?

6

Tautulli says I have the following:

Movies - 1204
TV - 7355
Music - 9540
Photos / Videos - 85139 / 2324

Perhaps my photos are the issue? Is there any way to split the photos DB into its own set of tables?

7

That is most likely the issue. Each item indexed, regardless what it is, requires an Index number in the Plex DB. When PMS’s database was created, 5000 items was a lot. It’s improved a great deal.

Which processor is in use here?

8

I’m running an AMD FX-6300 Six Core Processor @ 3.50 Ghz. 16GB RAM. 6386 CPU Marks.

It’s older but I don’t usually have more then 2 or two streams and lately I’ve been limiting transcoding (thanks to hardware support on iOS).

Further to this, I’m running Windows Server 2016 Essentials.

9

IF you were running Linux, you would be far better off.
IMO, Windows has gotten too heavy for a processor of that vintage.

10

Understood, and I know that an upgrade is needed soon.

I guess what you’re saying is that you don’t think there are any issues with the DB?

11

No I do not think there are any issues.

An AMD FX-6300 6 core (fine CPU) has 6000 Passmarks. That’s more than most domestic NAS Boxes. Those NAS boxes run Linux. I know of no Windows-based NAS boxes.

12

OK, makes sense. I thought the SSD would help but not enough I guess. :slight_smile:

Is there a limit on the number of items in the DB?

13

There is no physical limit but there is a measurable limit of how many items the SQLITE interface library can return in 250ms on even the fastest of processors.

14

Can you give us a rough idea of what that is so we can keep that in mind while building servers?

15

I will create a best case scenario I can. It will take a bit to load the entire music library. Stand by a bit please.

16

No problem!

17

Minor delay (13 hours worth to be precise).

I borked the NAS (locked the UI and had to power cycle). :imp:

This is what I get for holding my finger too close to the left mouse button while zipping from side to side.

It is conducting a mandatory sync of the volume.
When that completes, I will run my rsync cross-comparison to make certain no files are damaged (QNAP v Synology).

18

Thank you so much for this.

19

I have completed my testing.

58082 music tracks indexed. Time to retrieve all records on i7-7700 @ 3.6 Ghz = 23.1 ms

Extrapolating to the 250ms marker yields 628,593 music tracks per Library section.

Please note the distinction here. Per Library section.

The entire server now has 116,164 tracks indexed (from two other libraries). There is no observable degradation nor any abnormalities reported in the log files.

Host details:

  1. i7-7700 @3.6 Ghz (64-bit)
  2. 32 GB RAM
  3. Plex metadata and database - Samsung 860 EVO SSD
  4. OS: QTS 4.3.6.0806 (Linux 4.2.8 kernel)

A consideration. If on a 32 bit OS, a significant portion of the hardware’s capabilities are being wasted to clock cycles. Nearly 2x as much data can be retrieved in the same amount of time. (the speedup is in excess of 2/3 improvment for most operations in PMS)

20

Supplemental:

I migrated the Plex data from the SATA-3 (2.5" Samsung SSD) to the main QTS M.2 RAID pair.

Being a mirror, write performance is unchanged since both are written as a mirror.
Read performance, as expected, is 2x performance over a single SSD because a mirror RAID performed striped reading . This yields 1+ GB/sec read speed