Opened 5 years ago

Last modified 4 years ago

#12167 new Patch - Feature

Improve caching to reduce SQL queries

Reported by: Rune Petersen <rune@…> Owned by:
Priority: minor Milestone: unknown
Component: MythTV - General Version: Unspecified
Severity: medium Keywords:
Cc: Ticket locked: no

Description

mythfrontend make allot of SQL queries when when it is launched, and this affects the time it takes to launch the frontend quite significantly when you have a relatively slow master backend.

Allot of the time is spent querying the settings table for a single value at a time, and for querying the keymappings a single key at a time.

I have made 2 PoC patches showing it is possible to to eliminate almost all of these SQL queries which reduces launch time from 18 secs. to 4-5 secs with a Cortes A8 1GHz master backend.

mythtv_poc_improve_caching_of_host_settings.patch:

  • Enable caching if settings a little earlier.
  • When enabling settings cache query all host specific and global settings from master backend.
  • If cache is enabled and a key is not found in the cache don't fall back to querying master backend since we should be synchronized.
  • Use HostSettingStorage? instead of HostDBStorage for Settings menus. HostSettingStorage? gets/sets settings via MythDB which is cached. (can be extended to also work with global settings as well)
  • Reduces load time by ca. 9 secs. for me.

mythtv_poc_cache_all_keybindings.patch:

  • Cache all keymappings and make use of it when binding keys.
  • Reduces load time by ca. 4 secs. for me.

I know these patches are little rough, but I would like to know if you are interested in changes along these lines.

Attachments (3)

mythtv_poc_improve_caching_of_host_settings.patch (8.8 KB) - added by Rune Petersen <rune@…> 5 years ago.
mythtv_poc_cache_all_keybindings.patch (8.7 KB) - added by Rune Petersen <rune@…> 5 years ago.
mythtv_poc_improve_caching_of_host_settings_v2.patch (8.2 KB) - added by Rune Petersen <rune@…> 5 years ago.

Download all attachments as: .zip

Change History (11)

Changed 5 years ago by Rune Petersen <rune@…>

Changed 5 years ago by Rune Petersen <rune@…>

comment:1 Changed 5 years ago by JYA

thank you for your patch.

please make the new verbosity "debug", not info.

Please follow mythtv coding style: http://www.mythtv.org/wiki/Coding_Standards

in particular braces and spaces

comment:2 Changed 5 years ago by JYA

Owner: set to JYA
Status: newaccepted

comment:3 Changed 5 years ago by JYA

i wonder how initial settings are read when you have options set like starting mythfrontend with -p (to choose the backend etc)

i have strong suspicions it will introduce a regression

comment:4 Changed 5 years ago by Rune Petersen <rune@…>

good point, I suspect you may be right.

I'll try the -p option, and see what happens.

comment:5 Changed 5 years ago by Rune Petersen <rune@…>

I cannot test the -p option because it doesn't even work without my changes see #12217

comment:6 Changed 5 years ago by Rune Petersen <rune@…>

So I managed to test the -p option, and to my surprise It actually does work without any changes.

I did find some other bugs.

mythtv_poc_improve_caching_of_host_settings_v2.patch:

  • Implement HostSettingStorage::Save() properly - changes are now saved =)
  • Properly detect when a setting doesn't exist HostSettingStorage?(when to use default value).

Changed 5 years ago by Rune Petersen <rune@…>

comment:7 Changed 4 years ago by Lawrence Rust

Owner: changed from JYA to Lawrence Rust
Status: acceptedassigned

Jean-Yves

This bug is probably fixed by some of the database caching patches that I've proposed (devel/lvr/startup branch). If you have no objection I would like to take this on...

comment:8 Changed 4 years ago by Karl Egly

Owner: Lawrence Rust deleted
Status: assignednew
Note: See TracTickets for help on using tickets.