Bug #79894 Page cleaner worker threads are not instrumented for performance schema
Submitted: 8 Jan 2016 13:56 Modified: 6 Jun 2017 7:17
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, page cleaner, performance, performance schema

[8 Jan 2016 13:56] Laurynas Biveinis
Description:
Credit shared with Alexey Stroganov.

Page cleaner worker threads do not call pfs_register_thread.

This means:
1) (if I recall correctly how PFS thread instrumentation works) no events by these threads are actually accounted.
2) they are invisible in PERFORMANCE_SCHEMA.THREADS table.

If 1) is correct, then any results so far on 5.7 to gain insights into MT page cleaner performance by PFS are basically invalid. To verify 2), start server in default configuration, and query P_S.THREADS. You will see one cleaner thread there only. The three worker threads there are purge. The cleaner worker threads are not there.

How to repeat:
See above, check buf0flu.cc for buf_flush_page_cleaner_worker NOT calling pfs_register_thread.

Suggested fix:
Add a new thread key, register cleaner worker threads with PFS.
[12 Jan 2016 12:51] Laurynas Biveinis
Bug 79894 fix for 5.7.10 that reuses the same PFS key

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug79894.patch (application/octet-stream, text), 486 bytes.

[12 Jan 2016 12:52] Laurynas Biveinis
Submitted a three-line patch that does not introduce a new PFS thread key. Alexey and I discussed that not differentiating between coordinator and workers should be OK for PFS.
[20 May 2016 5:21] MySQL Verification Team
Indeed, only the coordinator thread is key'd at the moment.
[6 Jun 2017 7:17] Laurynas Biveinis
This has been fixed in 8.0.0 at [1] by using buf_flush_page_cleaner_coordinator_key and separate buf_flush_page_cleaner_worker_key, then changed in [2] as described in the commit message.

I'd ask to keep this bug open until 5.7 is fix, as it's both safe and useful for diagnostics.

[1]:

commit a31f512d7b4d6682e5dca3a10125661cee1e42b4
Author: Sunny Bains <Sunny.Bains@oracle.com>
Date:   Wed May 18 23:57:03 2016 +0530

    WL#9273 - Switch to C++11 std::thread
    
    (cherry picked from commit 19cb44d6796a931da5b7226c03e00b9904a00503)
    
    This push has all the other changes from WL#9273 too, to make this work.

[2]:

commit cbdc8784551fe8bf1ce2b7f4d765f8dbc3ed4d3c
Author: Sunny Bains <Sunny.Bains@oracle.com>
Date:   Wed Jun 15 11:30:40 2016 +0530

    WL#9359 - Instrument all background threads. Record test.
    
    page_cleaner_thread_key split into:
     page_flush_coordinator_thread_key
     page_flush_thread_key
    
    Move flush thread creation to flush init code.