Excessive calls to SELECT sourceid FROM capturecard WHERE cardid = ?

[bib1963]

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?

[Klaas de Waal]

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.

[Stuart Auchterlonie]

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

[Klaas de Waal]

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.

[Klaas de Waal]

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

[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

[Klaas de Waal <kdewaal@…>]

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@…>