WL#8881: PERFORMANCE_SCHEMA, RESOURCE CONTROL

Provide support for Resource Groups, specifically:

• an API to query the system and security attributes of a given thread,
• a notification service to signal thread and session events,
• an API to set the resource group name for a given thread, and
• a resource group column in the THREADS table.

Use Documentation

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html
• https://dev.mysql.com/doc/refman/8.0/en/threads-table.html

CURRENT-VERSION = 8.0.2
PREVIOUS-VERSION = 5.7

F-1 A fresh MySQL installation of CURRENT-VERSION must create the THREADS table as specified below.

F-2 An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must recreate the THREADS table with the RESOURCE_GROUP column.

F-3 The THREADS.RESOURCE_GROUP column shall be indexed. The index cannot be altered.

F-4 The Notification service must support multiple callback functions registered for each event type.

F-5 Both the Notification and Resource Group service should be stress tested with many concurrent sessions.

Existing tables are changed as follows:

performance_schema.threads

CREATE TABLE `threads` (
  `THREAD_ID` bigint(20) unsigned NOT NULL,
  `NAME` varchar(128) NOT NULL,
  `TYPE` varchar(10) NOT NULL,
  `PROCESSLIST_ID` bigint(20) unsigned DEFAULT NULL,
  `PROCESSLIST_USER` varchar(32) DEFAULT NULL,
  `PROCESSLIST_HOST` varchar(60) DEFAULT NULL,
  `PROCESSLIST_DB` varchar(64) DEFAULT NULL,
  `PROCESSLIST_COMMAND` varchar(16) DEFAULT NULL,
  `PROCESSLIST_TIME` bigint(20) DEFAULT NULL,
  `PROCESSLIST_STATE` varchar(64) DEFAULT NULL,
  `PROCESSLIST_INFO` longtext,
  `PARENT_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `ROLE` varchar(64) DEFAULT NULL,
  `INSTRUMENTED` enum('YES','NO') NOT NULL,
  `HISTORY` enum('YES','NO') NOT NULL,
  `CONNECTION_TYPE` varchar(16) DEFAULT NULL,
  `THREAD_OS_ID` bigint(20) unsigned DEFAULT NULL,
  
New column:
  `RESOURCE_GROUP` varchar(64) DEFAULT NULL,
  
   PRIMARY KEY (`THREAD_ID`),
   KEY `PROCESSLIST_ID` (`PROCESSLIST_ID`),
   KEY `THREAD_OS_ID` (`THREAD_OS_ID`),
   KEY `NAME` (`NAME`),
   KEY `PROCESSLIST_ACCOUNT` (`PROCESSLIST_USER`,`PROCESSLIST_HOST`),
   KEY `PROCESSLIST_HOST` (`PROCESSLIST_HOST`)

New index:   
   KEY `RESOURCE_GROUP` (`RESOURCE_GROUP`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

The Performance Schema will provide two new APIs implemented as services with the MySQL server:

• pfs_resource_group Assign a resource group to or query system attributes for a given thread.
• pfs_notification Notify registered callback functions of thread and session events.

These APIs are used by the resource manager during:

• thread create to bind to a default resource group
• runtime when a thread association with a with a resource group is changed
• thread termination
• drop resource group

Although these APIs are implemented as MySQL component services, they may also be invoked via the existing Performance Schema interface, for example:

  PSI_THREAD_CALL(get_thread_system_attrs)(&thread_attrs);

Service: pfs_resource_group

set_thread_resource_group()

Assign a resource group to the current thread and update the corresponding RESOURCE_GROUP column in the THREADS table.

  int set_thread_resource_group(const char* group_name,
                                int group_name_len,
                                void *user_data)
   
  @param group_name      Resource group name string
  @param group_name_len  Length of resource group name string
  @param user_data       User-defined context, optional.
  
  Returns 0 if successful, 1 otherwise.

Notes

• The group name is limited to 64 characters, UTF8. Names longer than 64 characters will be truncated.
• user_data is an optional user-defined context associated with thread_id that will be returned to the callback function in the thread attributes structure.

set_thread_resource_group_by_id()

Assign a resource group to an instrumented thread and update the corresponding RESOURCE_GROUP column in the THREADS table. The thread is identified either by a pointer to the thread instrumentation (PSI_thread *) or by the internal Performance Schema thread id.

  int set_thread_resource_group_by_id(PSI_thread *psi_thread,
                                      ulonglong thread_id,
                                      const char* group_name,
                                      int group_name_len,
                                      void *user_data)
   
  @param psi_thread      Target thread instrumentation. Ignored if NULL.
  @param thread_id       Thread id of the target thread (THREADS.THREAD_ID). Only used if thread is NULL.
  @param group_name      Resource group name string
  @param group_name_len  Length of resource group name string
  @param user_data       User-defined context, optional.
  
  Returns 0 if successful, 1 otherwise.

Notes

• See above.

get_thread_system_attrs()

Get system and security attributes for the current thread.

  int get_thread_system_attrs(pfs_thread_attrs_t *pfs_thread_attrs)
                              
  @param pfs_thread_attrs   Pointer to thread attribute structure (see below)
  
  Returns 0 if successful, 1 otherwise.

Thread attributes are returned in this structure:

  struct pfs_thread_attrs_t
  {
    ulonglong m_thread_internal_id;   // PFS internal thread id, unique 
    ulong m_processlist_id;           // SHOW PROCESSLIST thread id, not unique
    ulonglong m_thread_os_id;         // Operating system thread id, if any
    void *m_user_data;                // User-defined context, set by  set_thread_resource_group()
    char m_username[USERNAME_LENGTH]; // User name
    uint m_username_length;           // User name length
    char m_hostname[HOSTNAME_LENGTH]; // Host name
    uint m_hostname_length;           // Host name length
    char m_groupname[NAME_LEN];       // Resource group name
    uint m_groupname_length;          // Resource group name length
    struct sockaddr_storage m_sock_addr; // Raw socket address
    socklen_t m_sock_addr_length;        // Length of address
    my_bool m_system_thread;        // True for system/background threads
}

get_thread_system_attrs_by_id()

Get system and security attributes for a thread identified either by the thread instrumentation or its thread id.

  int get_thread_system_attrs_by_id(PSI_thread *psi_thread,
                                    ulonglong thread_id,
                                    pfs_thread_attrs_t *pfs_thread_attrs)
                              
  @param psi_thread         Target thread instrumentation. Ignored if NULL.
  @param thread_id          Thread id of the target thread (THREADS.THREAD_ID). Only used if psi_thread is NULL.
  @param pfs_thread_attrs   Pointer to thread attribute structure (see above)
  
  Returns 0 if successful, 1 otherwise.

Service: pfs_notification

Notify registered callback functions of thread and session events.

This is a synchronous, dedicated API designed to impose minimal performance overhead.

Callback functions must be of type PSI_notification_cb:

  typedef void (*PSI_notification_cb)(PSI_thread_attrs *thread_attrs);

The thread attributes structure will contain the current system attributes of the thread.

The corresponding IP address (m_sock_addr), if applicable, is stored in binary format, but can be converted to a readable format with intet_ntop or similar functions.

register_notification()

Register the callback functions for the Notification service.

  struct PSI_notification
  {
    PSI_notification_cb thread_create;
    PSI_notification_cb thread_destroy;
    PSI_notification_cb session_connect;
    PSI_notification_cb session_disconnect;
    PSI_notification_cb session_change_user;
  };

  int register_notification(PSI_notification *callbacks,
                            bool with_ref_count);

  @param callbacks      Structure of callback functions
  @param with_ref_count Set TRUE for callback functions in dynamically loaded
                        modules. Set FALSE for callback functions in static or
                        unloadable modules.
  @return handle    Non-zero handle if successful, 0 otherwise.
                    Handle is required to unregister the callback set.

To register for one or more events, set the corresponding function pointer in the PSI_notification structure. This structure defines a callback set. Leave the function pointer NULL for events that do not have a callback. A non-zero registration handle is returned if the registration is successful. This handle must be used to unregister the callback set.

For callback functions defined in dynamically loaded modules, set with_ref_count to TRUE so that the module can be safely unloaded after the callbacks are unregistered.

For callback functions defined in static or unloadable modules, set with_ref_count to FALSE to optimize callback performance in high-concurrency environments.

Callbacks that reside in a dynamically loaded module, e.g. a server plugin, must be unregistered successfully before the module is unloaded.

For callbacks in static or unloadable modules, unregister() will disable the callback functions, but the callback references will remain resident.

unregister_notification()

Unregister callback functions from the Notification service.

  int unregister_notification();

  @param handle  Unique handle returned from register_notification().
  @return 0 if successful, 1 otherwise

Use the handle returned by registration_notification() to identify the callback set to be unregistered. Callbacks that reside in a dynamically loaded module, e.g. a server plugin or component, must be successfully unregistered before the module is unloaded. If unregister_notification() returns an error, then the module should not be unloaded.

Usage Notes

• No error is returned for calling register_notification() more than once for a given callback set. Callback functions are invoked once for each time they are registered.
• unregister_callback() returns an error if the callback set was previously unregistered.
• unregister_callback() returns an error if any of the functions in the callback set are in use and fail to return after 2 seconds.
• Failing to unregister all callback functions from a dynamically loaded plugin or component may result in undocumented behavior during server shutdown.