[mythtv] [mythtv-commits] Ticket #10391: Slow MythTV startup if DB has many tables

Michael T. Dean mtdean at thirdcontact.com
Wed Feb 29 02:07:34 UTC 2012


On 02/28/2012 08:10 PM, Nigel Pearson wrote:
> On 29/02/2012, at 11:36 AM, Gavin Hurlbut wrote:
>
>> Maybe I'm dense here, but how did a user get hundreds or thousands of
>> tables in a database that we designed?
> Sadly, INFORMATION_SCHEMA.TABLES lists info on all
> the databases in MySQL, not just our mythconverg:
>
>
>
> mysql>  SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES;
> +--------------------+---------------------------------------+
> | table_schema       | table_name                            |
> +--------------------+---------------------------------------+
> | information_schema | CHARACTER_SETS                        |
> ...
> | information_schema | USER_PRIVILEGES                       |
> | information_schema | VIEWS                                 |
> | mysql              | columns_priv                          |
> | mysql              | db                                    |
> ...
> | mysql              | user                                  |
> | mythconverg        | callsignnetworkmap                    |
> | mythconverg        | capturecard                           |
> ...
> | mythconverg3       | websites                              |
> | test               | callsignnetworkmap                    |
> ^C
>
>
>
>
>
> 1) Our current SELECT in DBUtil::GetTables() does anchor it to the current database name, so it should always return just mythconverg's hundred tables.
>
> 2) In my slight, random testing, sometimes the query takes a long time.
>
>
>
>
>
>
>
>
>
> But yes, it does seem unlikely.
>
> I haven't had time to follow up with the reporters,
> and haven't created a huge database for full testing yet.
>
> Low priority.

FWIW, the user in the referenced thread was using a year-old version of 
MythTV prior to https://github.com/MythTV/mythtv/commit/eda0880f (in 
master) and https://github.com/MythTV/mythtv/commit/4fe108c2 (in 
0.24-fixes).

What was slow was the Qt function QSqlDatabase.tables(), that used to be 
called by MSqlDatabase::OpenDatabase()--because QSqlDatabase.tables() 
(and not our code) provides a list of all tables accessible to the MySQL 
user, even if they're in a different MySQL database on the same MySQL 
server.  As you can see in the linked commmits, above, the code has been 
changed to no longer use the Qt function because of this issue--and 
doesn't even enumerate the tables, since we're now just asking for a 
COUNT().

However, no MythTV user should be using a MySQL user with such excessive 
privileges.  The MySQL user used for MythTV should only have privileges 
on the mythconverg database.  (And, with such a configuration,  the 
slow-startup-with-too-many-tables issue would be prevented even if using 
the old, broken code that relied on Qt's QSqlDatabase.tables() function.)

And, for the record, on startup:

   a) DBUtils::IsNewDatabase() is only ever called by 
SchemaUpgradeWizard::Compare() (but only if there is no DBSchemaVersion 
in the database) or by DBUtil::BackupDB() (but only if there's a 
schema-version mismatch and we are about to upgrade the database).  
DBUtils::IsNewDatabase() calls DBUtil::GetTables, which works almost 
exactly like the fixed MSqlDatabase::OpenDatabase().
   b) DBUtils::CheckTables() was called on backend or mythtv-setup 
startup for a very short time (startup table check was added on Jul 25, 
2010 ( https://github.com/MythTV/mythtv/commit/278b7cadb ) and removed a 
little less than a month later on Aug 22, 2010 
(https://github.com/MythTV/mythtv/commit/3303e2953 ), and never existed 
in any released version of MythTV--only pre-0.24 unstable/development 
code).  Currently, it is not accessible in 0.24-fixes, and can only be 
called via the Services API code in unstable/development.

So, really, neither of those is being called on startup (except when you 
first create a MythTV database schema or when you upgrade the schema), 
and they both limit (and /always/ have limited) the list to only those 
tables in the MythTV schema.  On normal startup, the only thing checking 
tables is MSqlDatabase::OpenDatabase(), and current 0.24-fixes and 
unstable/development do so by asking for a COUNT() of the tables using 
our code since we can't use Qt's.

If you have a delay on startup, you may be using an old, broken revision 
of 0.24, in which case you need to get the Mac OS X "packager" to update 
the revision being used to one that's not a year old.  Any other delay 
is almost definitely due to something else.

Mike



More information about the mythtv-dev mailing list