Bug #45090 performance schema is enabled at startup
Submitted: 26 May 2009 7:58 Modified: 14 Jan 2010 18:06
Reporter: Jørgen Austvik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:6.0.12-alpha-log-perfschema OS:Any
Assigned to: Marc Alff CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[26 May 2009 7:58] Jørgen Austvik
Description:
WL#2360 says: "At start-up, all instruments are globally disabled. They should always be enabled. But it's a new feature so we'll be cautious about making it the default."

But this does not seem to be the case:

-----------8<-------------------8<-------------------8<-------------------8<--------
(Freshly started MySQL)

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    | 
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM performance_schema.SETUP_INSTRUMENTS WHERE ENABLED = 'YES';
+----------+
| COUNT(*) |
+----------+
|      245 | 
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM performance_schema.SETUP_CONSUMERS WHERE ENABLED = 'YES';
+----------+
| COUNT(*) |
+----------+
|        8 | 
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_CURRENT;
+----------+
| COUNT(*) |
+----------+
|        3 | 
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_HISTORY;
+----------+
| COUNT(*) |
+----------+
|       29 | 
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_HISTORY_LONG;
+----------+
| COUNT(*) |
+----------+
|     2299 | 
+----------+
1 row in set (0.00 sec)
-----------8<-------------------8<-------------------8<-------------------8<--------

I expect all counts to be 0, maybe with the exception of SETUP_CONSUMERS.

This might be related to Bug #44016?

How to repeat:
Start server normally, and run:

SHOW VARIABLES LIKE 'performance_schema';
SELECT * FROM performance_schema.SETUP_INSTRUMENTS WHERE ENABLED = 'YES';
SELECT * FROM performance_schema.SETUP_CONSUMERS WHERE ENABLED = 'YES';

SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_CURRENT;
SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_HISTORY;
SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_HISTORY_LONG;

Suggested fix:
Make sure these queries (or at least the first one):
SELECT * FROM performance_schema.SETUP_INSTRUMENTS WHERE ENABLED = 'YES';
SELECT * FROM performance_schema.SETUP_CONSUMERS WHERE ENABLED = 'YES';
return 0 rows.
[26 May 2009 8:50] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 May 2009 22:00] Marc Alff
Fixed by WL#4876 Parse options before initializing mysys
[20 Nov 2009 18:42] Marc Alff
Clarifications:

At startup, when no command line parameters and no my.cnf options are present
for the performance schema, the performance schema is disabled by default.

Disabled means that the performance_schema variable is OFF.

This has been fixed by WL#4876 as mentioned earlier, which is why this
report is currently in patch queued status (in mysql-trunk-perfschema).

This does not mean however that the SETUP_INSTRUMENTS, etc table will always
display everything with enabled='NO', this is implementation dependent and may change, so tests should not check for this.
[14 Jan 2010 10:03] Marc Alff
Merged in:
- mysql-next-mr (Celosia / 5.5.99-m3)
- mysql-6.0-codebase (6.0.14)
[14 Jan 2010 18:06] Paul Dubois
Not in any released version. No changelog entry needed.