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 David Nay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S3 (Non-critical)
Version:	2.0.0.7122	OS:	Windows
Assigned to:	Andy Bang	CPU Architecture:	Any

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.