[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