| Bug #54591 | Long running I_S query interferes with initial inventory -> No OS memory graphs | ||
|---|---|---|---|
| Submitted: | 17 Jun 2010 18:22 | Modified: | 5 Jul 2010 7:21 |
| Reporter: | Leandro Morgado | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Enterprise Monitor: Agent | Severity: | S3 (Non-critical) |
| Version: | Server: 2.2.1.1721 Agent: 2.2.1.1717 | OS: | Any |
| Assigned to: | Andy Bang | CPU Architecture: | Any |
[17 Jun 2010 22:28]
Andy Bang
I added a pre-condition for it in both branches/2.2 and trunk (which is currently 2.3):
=== modified file 'items/items-mysql-monitor.xml'
--- a/items/items-mysql-monitor.xml 2010-04-07 22:45:45 +0000
+++ b/items/items-mysql-monitor.xml 2010-06-17 18:19:22 +0000
@@ -174,6 +174,7 @@ SELECT GROUP_CONCAT('\\\\\n* ', t.table_
<class>
<namespace>mysql</namespace>
<classname>all_db_table_count</classname>
+ <precondition><![CDATA[SELECT @@version NOT LIKE "5.0%" AND (SELECT COUNT(*) < 200 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA')]]></precondition>
<query><![CDATA[SELECT COUNT(*) AS all_db_table_count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA']]></query>
</class>
<class>
[17 Jun 2010 22:32]
Andy Bang
In other words, the fix is committed to source control and will be included with the next 2.2 and 2.3 build we do of the Agent. Note that in order to reproduce the problem you have to be using 5.0.x and have a *lot* of tables in your databases (i.e. so that SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA' takes a long time).
[18 Jun 2010 13:42]
Leandro Morgado
Commenting out the long running query so it's doesn't run is a confirmed work around.
[22 Jun 2010 18:58]
Enterprise Tools JIRA Robot
Andy Bang writes: In build 2.2.2.1727.
[2 Jul 2010 20:34]
Enterprise Tools JIRA Robot
Bill Weber writes: verified the precondition was added in 2.2.2.1727 and 2.3
[5 Jul 2010 7:21]
MC Brown
A note has been added to the 2.2.2 and 2.3.0 changelog:
Monitoring a database with a large number of tables would lead
to long queries on <literal>INFORMATION_SCHEMA</literal> which
could lead to operating system memory graphs not being updated
correctly.
Description: When doing initial inventory, and if monitoring a 5.0.x server, the following query can run for a long time, causing the inventory to be inaccurate: <class> <namespace>mysql</namespace> <classname>all_db_table_count</classname> <query><![CDATA[SELECT COUNT(*) AS all_db_table_count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA']]></query> </class> Apparently there are some preconditions to running potential slow queries against the I_S. For some reason, this one was missed. There are log files attached to the referenced support issue. How to repeat: I've tested with same version of server and agent + mysqld 5.0.72 but wasn't able to reproduce. Please see the customer support issue 48395, more specifically, the agent debug log. Suggested fix: Prevent the long running query from corrupting the inventory discovery.