Opened 3 months ago

Closed 3 months ago

#13649 closed Bug Report - General (fixed)

Excessive calls to SELECT sourceid FROM capturecard WHERE cardid = ?

Reported by: bib1963 Owned by: Klaas de Waal
Priority: minor Milestone: 31.1
Component: MythTV - General Version: Unspecified
Severity: medium Keywords:
Cc: Ticket locked: no

Description

My system has 2 USB devices and set to grab the EIT data.

If I run tcpdump on the DB port, I see around 80 calls per second to the above SQL. This is somewhat excessive.

Given that few systems would have anything more than a small number of devices,and even /if/ a system had a huge number, my feeling that the result from a single call can be cached, either while the backend is always up, or even for a period of a few seconds, thus reducing the load.

Perhaps not a true bug report, but could be improved.

The following may not be true, I could have just been unlucky with my timing... This then leads onto a second problem with the EIT data. I see that as it is being read, it is always written to the DB. With SSDs, this put unnecessary wear and tear on the device. Should there not be a read first, and a write only if the data is different?

Attachments (1)

20200714-masterkey.patch (3.9 KB) - added by Klaas de Waal 3 months ago.
Create master key only once and use that each time the lock is requested instead of creating the key every time again.

Download all attachments as: .zip

Change History (6)

comment:1 Changed 3 months ago by Klaas de Waal

Owner: set to Klaas de Waal
Status: newassigned

Ticket accepted for the problem described in the subject line, "Excessive calls to SELECT sourceid FROM capturecard WHERE cardid = ?".

Please create a separate ticket for your other issue.

comment:2 Changed 3 months ago by Stuart Auchterlonie

The second part is already handled in the eit_cache code. This ensures events we have already seen are not processed again.

comment:3 Changed 3 months ago by Klaas de Waal

Milestone: needs_triage31.1
Status: assignedaccepted

This problem can be easily reproduced. What happens is the following. The SQL query comes from CaptureCard::GetSourceID. This is called from DVBChannel::GetMasterLock? each time this function is called in order to create the key for the master lock of which the source ID is a part. This is done 40 times per second for each device so for two devices that is indeed 80 times per second. Well spotted!

The solution is to create the key only once and to save the key for later use. This is now being tested, if no problems appear then this will be committed. For the curious and for those wanting to review, the implementation is attached.

Changed 3 months ago by Klaas de Waal

Attachment: 20200714-masterkey.patch added

Create master key only once and use that each time the lock is requested instead of creating the key every time again.

comment:4 Changed 3 months ago by Klaas de Waal <kdewaal@…>

In 7277ae9af/mythtv:

Create key for DVB channel master lock only once

Create master key only once and use that each time the lock is requested instead of creating the key every time again. This solves the problem that the database is accessed 40 times per second for the source ID of a capture card for each capture card while monitoring the signal status.

Refs #13649

comment:5 Changed 3 months ago by Klaas de Waal <kdewaal@…>

Resolution: fixed
Status: acceptedclosed

In f7a1f4b1a/mythtv:

Create key for DVB channel master lock only once

Create master key only once and use that each time the lock is requested instead of creating the key every time again. This solves the problem that the database is accessed 40 times per second for the source ID of a capture card for each capture card while monitoring the signal status.

Fixes #13649

(cherry picked from commit 7277ae9af33093bdf50b532888bd49b5a2d69bb0)
Signed-off-by: Klaas de Waal <kdewaal@…>

Note: See TracTickets for help on using tickets.