[mythtv] sql code
mdz at debian.org
Thu Feb 20 12:13:44 EST 2003
On Thu, Feb 20, 2003 at 01:22:42AM -0500, Andy Davidoff wrote:
> Some of the SQL communication is sub-optimal, but "acceptable", like the
> insertions during mythfilldatabase -- it'd be faster to group them
> together and make a few insertions of several hundred rows, but it could
> be argued that this could impose CPU load spikes which could impact a
I run mythfilldatabase on a diskless (NFS root) box, talking to a remote
MySQL server, which is probably fairly close to a worst-case scenario, and
it takes a negligible amount of time to do these inserts. Additionally, 99%
of the time is spent waiting for the grabber anyway. Is there really a
problem to be fixed here?
> Then there's the queries for settings in the database. Every such query
> is actually three queries -- one to check for a key/value for the current
> host, one to check for a default value (if the first has failed), and then
> a query that does nothing at all ("SELECT NULL").
The per-host settings stuff went in only in the past few days; I didn't
realize that it added an extra query. Feel free to provide a patch for
The SELECT NULL query is a workaround for the fact that the mysql client
library does not recover gracefully if a TCP connection is lost (for
example, a timeout, or a mysqld restart). This query is a canary which will
fail, but allow the client library to clean itself up so that the next one
This sucks, but it works for now.
> Alpha code, I know, but in some cases these groups of queries are executed
> for the same settings values several times per second; in the case of
> GuiHeight/GuiWidth, 15-20 times during MythMusic initialization. This
> results in sluggish load times -- several seconds on my Athlon 2k. It
> should be trivial to cache settings and/or load them all at once in a
> single query during initialization. By my estimation, the
> mythfilldatabase code queries for the value of NULL (!) and to check for a
> possible TimeOffset several thousand times per session. These should be
> static values in mid-session.
Caching these settings would be easy; feel free to provide a patch.
However, I doubt this is truly the source of your slowdown, on my system
(which I described above), mythfrontend starts in 2-3 seconds.
More information about the mythtv-dev