MySQL Bugs: #47947: INFORMATION_SCHEMA queries disrupt agent statistic and known items gathering

Bug #47947	INFORMATION_SCHEMA queries disrupt agent statistic and known items gathering
Submitted:	9 Oct 2009 14:26	Modified:	29 Mar 2010 10:40
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S2 (Serious)
Version:	2.1.0.1094, 2.1.1.1144	OS:	Any
Assigned to:	Kay Roepke	CPU Architecture:	Any
Tags:	Agent, information_schema, windmill

Description:
We introduced 3 new data collections within MEM that use the INFORMATION_SCHEMA to gather some extra information around schemas for the rules. 

These are causing issues with people that have a very large number of schemas and tables both on initial startup, in the list known items stage, as well as during normal monitoring when a re-inventory is requested. 

How to repeat:
o Set up a database with 400 databases each with 400 tables
o Start an agent with debug logging
o Check the log - you will see the I_S query fired off, and then nothing but heartbeats for a long period of time

Suggested fix:
o Split the "custom" SQL statements off to their own thread, so they do not step on the normal dc collection
o Ensure the discovery mechanism can deal with delays in some known items

Diego Medina writes: 
Simple shell script to create the 500 databases with 500 tables

(I have seen this bug having just 3 databases and 1023 tables on each database)

{noformat}
$ cat /Users/wizard/lots_of_quan.sh 
#!/bin/bash

for t in {1..500};
  do
    ./sandboxes/msb_5_1_32/use -P44040 -e "create database test_$t;";
    for x in {1..500};
      do 
        ./sandboxes/msb_5_1_32/use -P44040 -e "create table test_$t.t$x (a int);";
        echo "creating table: test_$t.t$x";
    done;
done;
echo "==========================="
for x in {1..500};
  do
    for t in {1..500};
      do
        ./sandboxes/msb_5_1_32/use -P44040 -e "drop table test_$x.t$t;";  
        echo "dropping table: test_$x.t$t";
    done;
    ./sandboxes/msb_5_1_32/use -P44040 -e "drop database test_$x;";
    echo "dropping database: test_$x";
done;
{noformat}

Bug#47727 was marked as a duplicate of this one.

Kay Roepke writes: 
approved with slight modifications on irc.

Kay Roepke writes: 
pushed to 2.1 as:

revno: 1544
committer: Kay Roepke <kay@sun.com>
branch nick: agent
timestamp: Fri 2010-01-22 18:11:18 +0100
message:
  Bug#47947/EM-3640: execute precondition query for queries defined in items xml files, if present and evaluate result. 
  prevent actual query from running if the precondition evaluates to false.
  the precondition query must return a numeric value in a single column in the first row, and that value must either be 0 or 1, otherwise the items query will be executed.
  [merge from trunk rev 1694]

Mark Leith writes: 
Pushed actual preconditions, and an extra dc item to 2.1 branch, working on trunk next:

revno: 1545
committer: Mark Leith <mark.leith@sun.com>
branch nick: 2.1
timestamp: Mon 2010-01-25 21:42:03 +0000
message:
  Bug#47947/EM-3640: Finalize the precondition queries and add a dc item which gets the count of all tables, for use in a rule that checks the t
otal against the default, and describes how to configure further.

Keith Russell writes: 
Patch installed in versions => 2.1.1.1142.

Mark Leith writes: 
And now pushed to trunk as well:

revno: 1698
committer: Mark Leith <mark.leith@sun.com>
branch nick: trunk
timestamp: Tue 2010-01-26 13:23:42 +0000
message:
  Bug#47947/EM-3640: Finalize the precondition queries and add a dc item which gets the count of all tables, for use in a rule that checks the total against the default, and desc
ribes how to configure further.

Diego Medina writes: 
Verified fixed on 2.1.1.1142

I see queries like

SELECT COUNT(*) < 200 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA'

being run and no long I_S queries are run after that.

Mark Leith writes: 
Pushed to trunk:

Pushed up to revision 1722.

And 2.1:

Pushed up to revision 1556.  

This now changes the precondition query to:

SELECT @@version NOT LIKE "5.0%" AND (SELECT COUNT(*) < 200 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA')

Meaning - all I_S queries by default are disabled on 5.0 (which has no I_S optimization), and are only run if there are less than 200 real tables (those that have frm files etc.), by default. 

To enable these queries on 5.0 remove the first start of the precondition statement, to enable on 5.1 with more than 200 tables, increase the limit, or remove the precondition element all together from the XML. 

3.x and 4.x will have these statements fail anyway, so the items will be unknown. 

Early alpha versions of 5.1 also did not have I_S optimization - and these may still be affected even with the new precondition query - the advice is to upgrade in those cases, this was an alpha version and should no longer be used.

Diego Medina writes: 
Verified fixed on 2.1.2.1160

A note has been added to the 2.1.2 changelog