10bet网址
MySQL 8.0 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

27.4.5 Pre-Filtering by Object

Thesetup_objectstable controls whether the Performance Schema monitors particular table and stored program objects. The initialsetup_objectscontents look like this:

mysql> SELECT * FROM performance_schema.setup_objects; +-------------+--------------------+-------------+---------+-------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED | +-------------+--------------------+-------------+---------+-------+ | EVENT | mysql | % | NO | NO | | EVENT | performance_schema | % | NO | NO | | EVENT | information_schema | % | NO | NO | | EVENT | % | % | YES | YES | | FUNCTION | mysql | % | NO | NO | | FUNCTION | performance_schema | % | NO | NO | | FUNCTION | information_schema | % | NO | NO | | FUNCTION | % | % | YES | YES | | PROCEDURE | mysql | % | NO | NO | | PROCEDURE | performance_schema | % | NO | NO | | PROCEDURE | information_schema | % | NO | NO | | PROCEDURE | % | % | YES | YES | | TABLE | mysql | % | NO | NO | | TABLE | performance_schema | % | NO | NO | | TABLE | information_schema | % | NO | NO | | TABLE | % | % | YES | YES | | TRIGGER | mysql | % | NO | NO | | TRIGGER | performance_schema | % | NO | NO | | TRIGGER | information_schema | % | NO | NO | | TRIGGER | % | % | YES | YES | +-------------+--------------------+-------------+---------+-------+

修改setup_objectstable affect object monitoring immediately.

TheOBJECT_TYPEcolumn indicates the type of object to which a row applies.TABLEfiltering affects table I/O events (wait/io/table/sql/handlerinstrument) and table lock events (wait/lock/table/sql/handlerinstrument).

TheOBJECT_SCHEMAandOBJECT_NAMEcolumns should contain a literal schema or object name, or'%'to match any name.

TheENABLEDcolumn indicates whether matching objects are monitored, andTIMEDindicates whether to collect timing information. Setting theTIMEDcolumn affects Performance Schema table contents as described inSection 27.4.1, “Performance Schema Event Timing”.

The effect of the default object configuration is to instrument all objects except those in themysql,INFORMATION_SCHEMA, andperformance_schemadatabases. (Tables in theINFORMATION_SCHEMAdatabase are not instrumented regardless of the contents ofsetup_objects; the row forinformation_schema.%simply makes this default explicit.)

When the Performance Schema checks for a match insetup_objects, it tries to find more specific matches first. For rows that match a givenOBJECT_TYPE, the Performance Schema checks rows in this order:

  • Rows withOBJECT_SCHEMA='literal'andOBJECT_NAME='literal'.

  • Rows withOBJECT_SCHEMA='literal'andOBJECT_NAME='%'.

  • Rows withOBJECT_SCHEMA='%'andOBJECT_NAME='%'.

For example, with a tabledb1.t1, the Performance Schema looks inTABLErows for a match for'db1'and't1', then for'db1'and'%', then for'%'and'%'. The order in which matching occurs matters because different matchingsetup_objectsrows can have differentENABLEDandTIMEDvalues.

For table-related events, the Performance Schema combines the contents ofsetup_objectswithsetup_instrumentsto determine whether to enable instruments and whether to time enabled instruments:

  • For tables that match a row insetup_objects, table instruments produce events only ifENABLEDisYESin bothsetup_instrumentsandsetup_objects.

  • TheTIMEDvalues in the two tables are combined, so that timing information is collected only when both values areYES.

For stored program objects, the Performance Schema takes theENABLEDandTIMEDcolumns directly from thesetup_objectsrow. There is no combining of values withsetup_instruments.

Suppose thatsetup_objectscontains the followingTABLErows that apply todb1,db2, anddb3:

+-------------+---------------+-------------+---------+-------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED | +-------------+---------------+-------------+---------+-------+ | TABLE | db1 | t1 | YES | YES | | TABLE | db1 | t2 | NO | NO | | TABLE | db2 | % | YES | YES | | TABLE | db3 | % | NO | NO | | TABLE | % | % | YES | YES | +-------------+---------------+-------------+---------+-------+

If an object-related instrument insetup_instrumentshas anENABLEDvalue ofNO, events for the object are not monitored. If theENABLEDvalue isYES, event monitoring occurs according to theENABLEDvalue in the relevantsetup_objectsrow:

  • db1.t1events are monitored

  • db1.t2events are not monitored

  • db2.t3events are monitored

  • db3.t4events are not monitored

  • db4.t5events are monitored

Similar logic applies for combining theTIMEDcolumns from thesetup_instrumentsandsetup_objectstables to determine whether to collect event timing information.

If a persistent table and a temporary table have the same name, matching againstsetup_objectsrows occurs the same way for both. It is not possible to enable monitoring for one table but not the other. However, each table is instrumented separately.