MySQL Bugs: #41555: trigger_schema query from items-mysql-monitor.xml taking a long time to execute

Bug #41555	trigger_schema query from items-mysql-monitor.xml taking a long time to execute
Submitted:	17 Dec 2008 15:14	Modified:	13 Jan 2009 11:46
Reporter:	Roger Nay	Email Updates:
Status:	Closed
Category:	Monitoring: Agent	Severity:	S3 (Non-critical)
Version:	2.0.0.7122	OS:	Microsoft Windows
Assigned to:	Andy Bang	Target Version:	2.0 ASAP
Triage:	Triaged: D2 (Serious) / R1 (None/Negligible) / E1 (None/Negligible)

Description:
Agent not starting up. Looking at the list of processes it was noted.

The user "enterprise_monitor" has send the following query since 5000 seconds:

SELECT GROUP_CONCAT( '\\\\\n* ', trigger_schema, '.', trigger_name ORDER BY
trigger_schema, trigger_name ) AS trigger_defn FROM information_schema.triggers WHERE
UPPER( action_statement ) LIKE '%SELECT *%'

The trigger_schema query is taking too long on agent start up. The monitored server hosts
round about 500 databases with 212 tables each and is under heavy load.

Workaround to remove the query from the .xml file (including the surrounding <class> ...
</class>) but that will leave the monitor unable to enable advisors or rules that will
need this query.

How to repeat:
Large number of databases (lots of triggers?) on a heavily loaded server.

This data collection item is only used in the "Triggers Found With SELECT * Syntax" rule,
which we removed from the Advisor bundle a while ago (maybe a year ago).  Since we seem
to run the statement at agent startup in our 2.0 release (as part of the inventory
discovery process), we have now commented out the item in the items-mysql-monitor.xml
file that is installed with the agent (as of build 2.0.2.7126).  In earlier builds it
must be commented out manually.

The underlying problem is that SELECTing FROM information_schema.triggers means that the
MySQL server must go out to the file system and look inside each .TRG file associated
with each table for the information you are requesting.  If you have lots of databases
and tables this means a lot of file I/O, which can take a lot of time.

This is considered a partial solution.  We should be able to include things like this but
not have them come into play until the corresponding rule is actually scheduled.  That way
the user can decide if the rule is worth the cost or not.  A separate bug (#41608) has
been opened to track this.

verified this is removed from items-mysql-monitor.xml in build 2.0.2.7126

An entry was added to the 2.0 changelog:

The Agent did not start up when the monitored server had many databases and tables, and
was under heavy load. This was because the trigger_schema query was taking too long on
agent start up.