Bug #45552 Performance schema: --performance_schema_max_mutex=1 gives no mutex waits events

• Submitted: 17 Jun 2009 8:24
• Modified: 18 Jun 2009 0:34
• Reporter: Jørgen Austvik
• Status: Not a Bug
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: mysql-6.0-perfschema
• OS: Any
• Assigned to: Marc ALFF
• CPU Architecture: Any

[17 Jun 2009 8:24] Jørgen Austvik

Description:
When I start MySQL with the following command line configuration options, I do not get any mutex nor cond events in the EVENTS_WAITS_HISTORY_LONG table.

--performance_schema_max_cond=1 --performance_schema_max_file=1 
--performance_schema_max_mutex=1 --performance_schema_max_rwlock=1 
--performance_schema_max_table=1 --performance_schema_max_thread=1

mysql> SELECT LEFT(EVENT_NAME, 13), COUNT(DISTINCT EVENT_NAME) FROM performance_schema.EVENTS_WAITS_HISTORY_LONG GROUP BY LEFT(EVENT_NAME, 13);
+----------------------+----------------------------+
| LEFT(EVENT_NAME, 13) | COUNT(DISTINCT EVENT_NAME) |
+----------------------+----------------------------+
| wait/io/file/        |                          1 | 
| wait/synch/rw        |                          1 | 
+----------------------+----------------------------+
2 rows in set (0.00 sec)

How to repeat:
Start MySQL

UPDATE performance_schema.SETUP_INSTRUMENTS
SET enabled = 'YES', timed = 'YES';

CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
ENGINE=InnoDB;

INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);

Normally (without limiting the length of the data structures):

mysql> SELECT LEFT(EVENT_NAME, 13), COUNT(DISTINCT EVENT_NAME), COUNT(*) FROM performance_schema.EVENTS_WAITS_HISTORY_LONG GROUP BY LEFT(EVENT_NAME, 13);
+----------------------+----------------------------+----------+
| LEFT(EVENT_NAME, 13) | COUNT(DISTINCT EVENT_NAME) | COUNT(*) |
+----------------------+----------------------------+----------+
| wait/io/file/        |                         13 |      853 | 
| wait/synch/co        |                          1 |        1 | 
| wait/synch/mu        |                         39 |     1318 | 
| wait/synch/rw        |                          3 |       81 | 
+----------------------+----------------------------+----------+
4 rows in set (0.87 sec)

39 different mutexes were hit, 1318 times, one cond one time. 

mysql> SELECT COUNT(*) FROM EVENTS_WAITS_HISTORY_LONG;
+----------+
| COUNT(*) |
+----------+
|     2220 | 
+----------+
1 row in set (0.01 sec)

Lots of events.

With:

--performance_schema_max_cond=1 --performance_schema_max_file=1 
--performance_schema_max_mutex=1 --performance_schema_max_rwlock=1 
--performance_schema_max_table=1 --performance_schema_max_thread=1

mysql> SELECT LEFT(EVENT_NAME, 13), COUNT(DISTINCT EVENT_NAME) FROM performance_schema.EVENTS_WAITS_HISTORY_LONG GROUP BY LEFT(EVENT_NAME, 13);
+----------------------+----------------------------+
| LEFT(EVENT_NAME, 13) | COUNT(DISTINCT EVENT_NAME) |
+----------------------+----------------------------+
| wait/io/file/        |                          1 | 
| wait/synch/rw        |                          1 | 
+----------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM EVENTS_WAITS_HISTORY_LONG;
+----------+
| COUNT(*) |
+----------+
|       24 | 
+----------+
1 row in set (0.00 sec)

I believe that it is wrong that there are no wait/synch/mutex and (maybe?) no wait/synch/cond event in EVENTS_WAITS_HISTORY_LONG when --performance_schema_max_cond=1 and --performance_schema_max_mutex=1. I expect one distinct of each of them

Please note that there is no limit on --performance_schema_max_*_instruments in this test.

[17 Jun 2009 10:14] Sveta Smirnova

Thank you for the report.

Verified as described.

[18 Jun 2009 0:34] Marc ALFF

Sorry, this is not a bug.
The test expected results are incorrect.

--performance_schema_max_mutex=1 means that at most 1 mutex instance
in the server will be instrumented.

This instance should be visible in table MUTEX_INSTANCES.

For this instrumented mutex to actually generate an event in
EVENTS_WAITS_CURRENT / _HISTORY / _HISTORY_LONG,
there must be a scenario where the server executes a query that cover
the instrumented code: executing any query won't do.

On top of that, if also only one thread is instrumented, because of
--performance_schema_max_thread=1
then the server needs to execute:
- the instrumented thread, not just any thread
- against the instrumented mutex, not just any mutex
for an event to be recorded.

--performance_schema_max_mutex=1
does not mean expect one distinct wait of each mutex.

Thanks for the report, closing as not a bug.