时间：2021-07-01 10:21:17 帮助过：11人阅读
MySQL Server includes a component-based infrastructure for extending server capabilities. A component provides services that are available to the server and other components. (With respect to service use, the server is a component, equal to other components.) Components interact with each other only through the services they provide.
MySQL distributions include several components that implement server extensions:
Components for configuring error logging. See Section 5.4.2, “The Error Log”, and Section 5.5.3, “Error Log Components”.
A component for checking passwords. See Section 6.4.3, “The Password Validation Component”.
A component that enables applications to add their own message events to the audit log. See Section 6.4.6, “The Audit Message Component”.
System and status variables implemented by a server component are exposed when the component is installed and have names that begin with a component-specific prefix. For example, the log_filter_dragnet error log filter component implements a system variable named log_error_filter_rules, the full name of which is dragnet.log_error_filter_rules. To refer to this variable, use the full name.
The following sections describe how to install and uninstall components, and how to determine at runtime which components are installed and obtain information about them.
For information about the internal implementation of components, see the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html. For example, if you intend to write your own components, this information is important for understanding how components work.
Server components must be loaded into the server before they can be used. MySQL supports component loading at runtime.
The INSTALL COMPONENT and UNINSTALL COMPONENT SQL statements enable component loading and unloading. For example:
INSTALL COMPONENT ‘file://component_validate_password‘; UNINSTALL COMPONENT ‘file://component_validate_password‘;
A loader service handles component loading and unloading, and also lists loaded components in the component table of the mysql system database that serves as a registry.
The SQL statements for component manipulation affect server operation and the mysql.component system table as follows:
INSTALL COMPONENT loads components into the server. The components become active immediately. The loader service also registers loaded components in the mysql.component system table. For subsequent server restarts, the loader service loads any components listed in mysql.component during the startup sequence. This occurs even if the server is started with the --skip-grant-tables option.
UNINSTALL COMPONENT deactivates components and unloads them from the server. The loader service also unregisters the components from the mysql.component system table so that they are no longer loaded during the startup sequence for subsequent server restarts.
Compared to the corresponding INSTALL PLUGIN statement for server plugins, the INSTALL COMPONENT statement for components offers the significant advantage that it is not necessary to know any platform-specific file name suffix for naming the component. This means that a given INSTALL COMPONENT statement can be executed uniformly across platforms.
The component table in the mysql system database contains information about currently loaded components and shows which components have been registered with INSTALL COMPONENT. To see which components are installed, use this statement:
SELECT * FROM mysql.component;
This section describes the characteristics of individual error log components. For general information about configuring error logging, see Section 5.4.2, “The Error Log”.
A log component can be a filter or a sink:
A filter processes log events, to add, remove, or modify event fields, or to delete events entirely. The resulting events pass to the next log component named in the log_error_services system variable value.
A sink is a destination (writer) for log events. Typically, a sink processes log events into log messages that have a particular format and writes these messages to its associated output, such as a file or the system log.
The server executes filters and sinks in the log_error_services value in the order they are named. The rightmost component should therefore be a sink. If the rightmost component is a filter, any changes it has on events have no effect on output.
The following sections describe individual log components, grouped by component type:
Error Log Filter Components
Error Log Sink Components
Component descriptions include these types of information:
The component name and intended purpose.
Whether the component is built in or must be loaded. For a loadable component, the description specifies the URN to use to load and unload the component with the INSTALL COMPONENT and UNINSTALL COMPONENT statements.
Whether the component can be listed multiple times in the log_error_services value.
For a sink component, the destination to which the component writes output.
Error log filter components implement filtering of error log events:
If no filter component is enabled, no filtering occurs.
Any enabled filter component affects log events only for components listed later in the log_error_services value. In particular, for any log sink component listed in log_error_services earlier than any filter component, no log event filtering occurs.
Purpose: Implements filtering based on log event priority and error code, in combination with the log_error_verbosity and log_error_suppression_list system variables. See Section 5.4.2.4, “Priority-Based Error Log Filtering (log_filter_internal)”.
URN: This component is built in and need not be loaded with INSTALL COMPONENT before use.
Multiple uses permitted: No.
If log_filter_internal is disabled, log_error_verbosity and log_error_suppression_list have no effect.
Purpose: Implements filtering based on the rules defined by the dragnet.log_error_filter_rules system variable setting. See Section 5.4.2.5, “Rule-Based Error Log Filtering (log_filter_dragnet)”.
URN: file://component_log_filter_dragnet
Multiple uses permitted: No.
Error log sink components are writers that implement error log output. If no sink component is enabled, no log output occurs.
Some sink component descriptions refer to the default error log destination. This is the console or a file and is indicated by the fault of the log_error system variable, determined as described in Section 5.4.2.2, “Default Error Log Destination Configuration”.
Purpose: Implements traditional error log message output format.
URN: This component is built in and need not be loaded with INSTALL COMPONENT before use.
Multiple uses permitted: No.
Output destination: Writes to the default error log destination.
Purpose: Implements JSON-format error logging. See Section 5.4.2.6, “Error Logging in JSON Format”.
URN: file://component_log_sink_json
Multiple uses permitted: Yes.
Output destination: The JSON log writer determines its output destination based on the default error log destination, which is given by the log_error system variable:
If log_error names a file, the JSON writer bases output file naming on that file name, plus a numbered . suffix, with NN.jsonNN starting at 00. For example, if log_error is file_name, successive instances of log_sink_json named in the log_error_services value write to file_name.00.jsonfile_name.01.json
If log_error is stderr, the JSON writer writes to the console. If log_json_writer is named multiple times in the log_error_services value, they all write to the console, which is likely not useful.
Purpose: Implements error logging to the system log. This is the Event Log on Windows, and syslog on Unix and Unix-like systems. See Section 5.4.2.7, “Error Logging to the System Log”.
URN: file://component_log_sink_syseventlog
Multiple uses permitted: No.
Output destination: Writes to the system log. Does not use the default error log destination.
Purpose: Intended for internal use in writing test cases. Not intended for production use.
URN: file://component_log_sink_test
Multiple uses permitted: Yes.
Output destination: Writes to the default error log destination.
MySQL supports a plugin API that enables creation of server components. Plugins can be loaded at server startup, or loaded and unloaded at runtime without restarting the server. The components supported by this interface include, but are not limited to, storage engines, INFORMATION_SCHEMA tables, full-text parser plugins, and server extensions.
MySQL distributions include several plugins that implement server extensions:
Plugins for authenticating attempts by clients to connect to MySQL Server. Plugins are available for several authentication protocols. See Section 6.2.17, “Pluggable Authentication”.
A connection-control plugin that enables administrators to introduce an increasing delay after a certain number of consecutive failed client connection attempts. See Section 6.4.2, “The Connection-Control Plugins”.
A password-validation plugin implements password strength policies and assesses the strength of potential passwords. See Section 6.4.3, “The Password Validation Component”.
Semisynchronous replication plugins implement an interface to replication capabilities that permit the master to proceed as long as at least one slave has responded to each transaction. See Section 17.4.9, “Semisynchronous Replication”.
Group Replication enables you to create a highly available distributed MySQL service across a group of MySQL server instances, with data consistency, conflict detection and resolution, and group membership services all built-in. See Chapter 18, Group Replication.
MySQL Enterprise Edition includes a thread pool plugin that manages connection threads to increase server performance by efficiently managing statement execution threads for large numbers of client connections. See Section 5.6.3, “MySQL Enterprise Thread Pool”.
MySQL Enterprise Edition includes an audit plugin for monitoring and logging of connection and query activity. See Section 6.4.5, “MySQL Enterprise Audit”.
MySQL Enterprise Edition includes a firewall plugin that implements an application-level firewall to enable database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. See Section 6.4.7, “MySQL Enterprise Firewall”.
Query rewrite plugins examine statements received by MySQL Server and possibly rewrite them before the server executes them. See Section 5.6.4, “The Rewriter Query Rewrite Plugin”, and Section 5.6.5, “The ddl_rewriter Plugin”.
Version Tokens enables creation of and synchronization around server tokens that applications can use to prevent accessing incorrect or out-of-date data. Version Tokens is based on a plugin library that implements a version_tokens plugin and a set of user-defined functions. See Section 5.6.6, “Version Tokens”.
Keyring plugins provide secure storage for sensitive information. See Section 6.4.4, “The MySQL Keyring”.
X Plugin extends MySQL Server to be able to function as a document store. Running X Plugin enables MySQL Server to communicate with clients using the X Protocol, which is designed to expose the ACID compliant storage abilities of MySQL as a document store. See Section 20.5, “X Plugin”.
Clone permits cloning InnoDB data from a local or remote MySQL server instance. See Section 5.6.7, “The Clone Plugin”.
Test framework plugins test server services. For information about these plugins, see the Plugins for Testing Plugin Services section of the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html.
The following sections describe how to install and uninstall plugins, and how to determine at runtime which plugins are installed and obtain information about them. For information about writing plugins, see Section 29.2, “The MySQL Plugin API”.
Server plugins must be loaded into the server before they can be used. MySQL supports plugin loading at server startup and runtime. It is also possible to control the activation state of loaded plugins at startup, and to unload them at runtime.
While a plugin is loaded, information about it is available from the INFORMATION_SCHEMA.PLUGINS table and the SHOW PLUGINS statement. See Section 5.6.2, “Obtaining Server Plugin Information”.
Installing Plugins
Controlling Plugin Activation State
Uninstalling Plugins
Before a server plugin can be used, it must be installed using one of the following methods. In the descriptions, plugin_name stands for a plugin name such as innodb, csv, or validate_password.
Built-in plugins:
A built-in plugin is known by the server automatically. Normally, the server enables the plugin at startup. Some built-in plugins permit this to be changed with the -- option.plugin_name[=activation_state]
Plugins registered in the mysql.plugin system table:
The plugin table in the mysql system database serves as a registry of plugins (other than built-in plugins, which need not be registered). At startup, the server loads each plugin listed in the table. Normally, for a plugin loaded from the mysql.plugin table, the server also enables the plugin. This can be changed with the -- option.plugin_name[=activation_state]
If the server is started with the --skip-grant-tables option, it does not consult the mysql.plugin table and does not load the plugins listed there.
Plugins named with command-line options:
A plugin located in a plugin library file can be loaded at server startup with the --plugin-load, --plugin-load-add, or --early-plugin-load option. Normally, for a plugin loaded at startup, the server also enables the plugin. This can be changed with the -- option.plugin_name[=activation_state]
The --plugin-load and --plugin-load-add options load plugins after built-in plugins and storage engines have initialized during the server startup sequence. The --early-plugin-load option is used to load plugins that must be available prior to initialization of built-in plugins and storage engines.
The value of each plugin-loading option is a semicolon-separated list of name=plugin_library and plugin_library values. Each name is the name of a plugin to load, and plugin_library is the name of the library file that contains the plugin code. If a plugin library is named without any preceding plugin name, the server loads all plugins in the library. The server looks for plugin library files in the directory named by the plugin_dir system variable.
Plugin-loading options do not register any plugin in the mysql.plugin table. For subsequent restarts, the server loads the plugin again only if --plugin-load, --plugin-load-add, or --early-plugin-load is given again. That is, the option produces a one-time plugin-installation operation that persists for a single server invocation.
--plugin-load, --plugin-load-add, and --early-plugin-load enable plugins to be loaded even when --skip-grant-tables is given (which causes the server to ignore the mysql.plugin table). --plugin-load, --plugin-load-add, and --early-plugin-load also enable plugins to be loaded at startup that cannot be loaded at runtime.
The --plugin-load-add option complements the --plugin-load option:
Each instance of --plugin-load resets the set of plugins to load at startup, whereas --plugin-load-add adds a plugin or plugins to the set of plugins to be loaded without resetting the current set. Consequently, if multiple instances of --plugin-load are specified, only the last one takes effect. With multiple instances of --plugin-load-add, all of them take effect.
The argument format is the same as for --plugin-load, but multiple instances of --plugin-load-add can be used to avoid specifying a large set of plugins as a single long unwieldy --plugin-load argument.
--plugin-load-add can be given in the absence of --plugin-load, but any instance of --plugin-load-add that appears before --plugin-load has no effect because --plugin-load resets the set of plugins to load.
For example, these options:
--plugin-load=x --plugin-load-add=y
are equivalent to this option:
--plugin-load="x;y"
But these options:
--plugin-load-add=y --plugin-load=x
are equivalent to this option:
--plugin-load=x
Plugins installed with the INSTALL PLUGIN statement:
A plugin located in a plugin library file can be loaded at runtime with the INSTALL PLUGIN statement. The statement also registers the plugin in the mysql.plugin table to cause the server to load it on subsequent restarts. For this reason, INSTALL PLUGIN requires the INSERT privilege for the mysql.plugin table.
The plugin library file base name depends on your platform. Common suffixes are .so for Unix and Unix-like systems, .dll for Windows.
Example: The --plugin-load option installs a plugin at server startup. To install a plugin named myplugin from a plugin library file named somepluglib.so, use these lines in a my.cnf file:
[mysqld] plugin-load=myplugin=somepluglib.so
In this case, the plugin is not registered in mysql.plugin. Restarting the server without the --plugin-load option causes the plugin not to be loaded at startup.
Alternatively, the INSTALL PLUGIN statement causes the server to load the plugin code from the library file at runtime:
INSTALL PLUGIN myplugin SONAME ‘somepluglib.so‘;
INSTALL PLUGIN also causes “permanent” plugin registration: The plugin is listed in the mysql.plugin table to ensure that the server loads it on subsequent restarts.
Many plugins can be loaded either at server startup or at runtime. However, if a plugin is designed such that it must be loaded and initialized during server startup, attempts to load it at runtime using INSTALL PLUGIN produce an error:
mysql> INSTALL PLUGIN myplugin SONAME ‘somepluglib.so‘;
ERROR 1721 (HY000): Plugin ‘myplugin‘ is marked as not dynamically
installable. You have to stop the server to install it.
In this case, you must use --plugin-load, --plugin-load-add, or --early-plugin-load.
If a plugin is named both using a --plugin-load, --plugin-load-add, or --early-plugin-load option and (as a result of an earlier INSTALL PLUGIN statement) in the mysql.plugin table, the server starts but writes these messages to the error log:
[ERROR] Function ‘plugin_name‘ already exists [Warning] Couldn‘t load plugin named ‘plugin_name‘ with soname ‘plugin_object_file‘.
If the server knows about a plugin when it starts (for example, because the plugin is named using a --plugin-load option or is registered in the mysql.plugin table), the server loads and enables the plugin by default. It is possible to control activation state for such a plugin using a -- startup option, where plugin_name[=activation_state]plugin_name is the name of the plugin to affect, such as innodb, csv, or validate_password. As with other options, dashes and underscores are interchangeable in option names. Also, activation state values are not case-sensitive. For example, --my_plugin=ON and --my-plugin=on are equivalent.
--plugin_name=OFF
Tells the server to disable the plugin. This may not be possible for certain built-in plugins, such as mysql_native_password.
--plugin_name[=ON]
Tells the server to enable the plugin. (Specifying the option as -- without a value has the same effect.) If the plugin fails to initialize, the server runs with the plugin disabled.plugin_name
--plugin_name=FORCE
Tells the server to enable the plugin, but if plugin initialization fails, the server does not start. In other words, this option forces the server to run with the plugin enabled or not at all.
--plugin_name=FORCE_PLUS_PERMANENT
Like FORCE, but in addition prevents the plugin from being unloaded at runtime. If a user attempts to do so with UNINSTALL PLUGIN, an error occurs.
Plugin activation states are visible in the LOAD_OPTION column of the INFORMATION_SCHEMA.PLUGINS table.
Suppose that CSV, BLACKHOLE, and ARCHIVE are built-in pluggable storage engines and that you want the server to load them at startup, subject to these conditions: The server is permitted to run if CSV initialization fails, must require that BLACKHOLE initialization succeeds, and should disable ARCHIVE. To accomplish that, use these lines in an option file:
[mysqld] csv=ON blackhole=FORCE archive=OFF
The --enable- option format is a synonym for plugin_name--. The plugin_name=ON--disable- and plugin_name--skip- option formats are synonyms for plugin_name--.plugin_name=OFF
If a plugin is disabled, either explicitly with OFF or implicitly because it was enabled with ON but fails to initialize, aspects of server operation that require the plugin will change. For example, if the plugin implements a storage engine, existing tables for the storage engine become inaccessible, and attempts to create new tables for the storage engine result in tables that use the default storage engine unless the NO_ENGINE_SUBSTITUTION SQL mode is enabled to cause an error to occur instead.
Disabling a plugin may require adjustment to other options. For example, if you start the server using --skip-innodb to disable InnoDB, other innodb_ options likely will need to be omitted at startup. In addition, because xxxInnoDB is the default storage engine, it will not start unless you specify another available storage engine with --default_storage_engine. You must also set --default_tmp_storage_engine.
At runtime, the UNINSTALL PLUGIN statement disables and uninstalls a plugin known to the server. The statement unloads the plugin and removes it from the mysql.plugin system table, if it is registered there. For this reason, UNINSTALL PLUGIN statement requires the DELETE privilege for the mysql.plugin table. With the plugin no longer registered in the table, the server does not load the plugin automatically for subsequent restarts.
UNINSTALL PLUGIN can unload a plugin regardless of whether it was loaded at runtime with INSTALL PLUGIN or at startup with a plugin-loading option, subject to these conditions:
It cannot unload plugins that are built in to the server. These can be identified as those that have a library name of NULL in the output from INFORMATION_SCHEMA.PLUGINS or SHOW PLUGINS.
It cannot unload plugins for which the server was started with --, which prevents plugin unloading at runtime. These can be identified from the plugin_name=FORCE_PLUS_PERMANENTLOAD_OPTION column of the INFORMATION_SCHEMA.PLUGINS table.
To uninstall a plugin that currently is loaded at server startup with a plugin-loading option, use this procedure.
Remove any options related to the plugin from the my.cnf file.
Restart the server.
Plugins normally are installed using either a plugin-loading option at startup or with INSTALL PLUGIN at runtime, but not both. However, removing options for a plugin from the my.cnf file may not be sufficient to uninstall it if at some point INSTALL PLUGIN has also been used. If the plugin still appears in the output from INFORMATION_SCHEMA.PLUGINS or SHOW PLUGINS, use UNINSTALL PLUGIN to remove it from the mysql.plugin table. Then restart the server again.
There are several ways to determine which plugins are installed in the server:
The INFORMATION_SCHEMA.PLUGINS table contains a row for each loaded plugin. Any that have a PLUGIN_LIBRARY value of NULL are built in and cannot be unloaded.
mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS\G
*************************** 1. row ***************************
PLUGIN_NAME: binlog
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 50158.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
...
*************************** 10. row ***************************
PLUGIN_NAME: InnoDB
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 50158.0
PLUGIN_LIBRARY: ha_innodb_plugin.so
PLUGIN_LIBRARY_VERSION: 1.0
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: Supports transactions, row-level locking,
and foreign keys
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
...
The SHOW PLUGINS statement displays a row for each loaded plugin. Any that have a Library value of NULL are built in and cannot be unloaded.
mysql> SHOW PLUGINS\G
*************************** 1. row ***************************
Name: binlog
Status: ACTIVE
Type: STORAGE ENGINE
Library: NULL
License: GPL
...
*************************** 10. row ***************************
Name: InnoDB
Status: ACTIVE
Type: STORAGE ENGINE
Library: ha_innodb_plugin.so
License: GPL
...
The mysql.plugin table shows which plugins have been registered with INSTALL PLUGIN. The table contains only plugin names and library file names, so it does not provide as much information as the PLUGINS table or the SHOW PLUGINS statement.
MySQL Enterprise Thread Pool is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, https://www.mysql.com/products/.
MySQL Enterprise Edition includes MySQL Enterprise Thread Pool, implemented using a server plugin. The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The thread pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections.
The thread pool addresses several problems of the model that uses one thread per connection:
Too many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint.
With too many threads executing in parallel, context switching overhead is high. This also presents a challenging task to the operating system scheduler. The thread pool controls the number of active threads to keep the parallelism within the MySQL server at a level that it can handle and that is appropriate for the server host on which MySQL is executing.
Too many transactions executing in parallel increases resource contention. In InnoDB, this increases the time spent holding central mutexes. The thread pool controls when transactions start to ensure that not too many execute in parallel.
Section A.15, “MySQL 8.0 FAQ: MySQL Enterprise Thread Pool”
The thread pool feature comprises these components:
A plugin library file implements a plugin for the thread pool code as well as several associated monitoring tables that provide information about thread pool operation:
As of MySQL 8.0.14, the monitoring tables are Performance Schema tables; see Section 26.12.15, “Performance Schema Thread Pool Tables”.
Prior to MySQL 8.0.14, the monitoring tables are INFORMATION_SCHEMA tables; see Section 25.46, “INFORMATION_SCHEMA Thread Pool Tables”.
The INFORMATION_SCHEMA tables now are deprecated and will be removed in a future MySQL version. Applications should transition away from the old tables to the new tables. For example, if an application uses this query:
SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_STATE;
The application should use this query instead:
SELECT * FROM performance_schema.tp_thread_state;
If you do not load all the monitoring tables, some or all MySQL Enterprise Monitor thread pool graphs will be empty.
For a detailed description of how the thread pool works, see Section 5.6.3.3, “Thread Pool Operation”.
Several system variables are related to the thread pool. The thread_handling system variable has a value of loaded-dynamically when the server successfully loads the thread pool plugin.
The other related variables are implemented by the thread pool plugin; they are not available unless it is enabled:
thread_pool_algorithm: The concurrency algorithm to use for scheduling.
thread_pool_high_priority_connection: How to schedule statement execution for a session.
thread_pool_prio_kickup_timer: How long before the thread pool moves a statement awaiting execution from the low-priority queue to the high-priority queue.
thread_pool_max_active_query_threads How many active threads per group to permit.
thread_pool_max_unused_threads: How many sleeping threads to permit.
thread_pool_size: The number of thread groups in the thread pool. This is the most important parameter controlling thread pool performance.
thread_pool_stall_limit: The time before an executing statement is considered to be stalled.
If any variable implemented by the plugin is set to an illegal value at startup, plugin initialization fails and the plugin does not load.
For information about setting thread pool parameters, see Section 5.6.3.4, “Thread Pool Tuning”.
The Performance Schema has instruments that expose information about the thread pool and may be used to investigate operational performance. To identify them, use this query:
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE ‘%thread_pool%‘;
For more information, see Chapter 26, MySQL Performance Schema.
This section describes how to install MySQL Enterprise Thread Pool. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.
The plugin library file base name is thread_pool. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).
Thread Pool Installation as of MySQL 8.0.14
Thread Pool Installation Prior to MySQL 8.0.14
In MySQL 8.0.14 and higher, the thread pool monitoring tables are Performance Schema tables that are loaded and unloaded along with the thread pool plugin. The INFORMATION_SCHEMA versions of the tables are deprecated but still available; they are installed per the instructions in Thread Pool Installation Prior to MySQL 8.0.14.
To enable thread pool capability, load the plugin by starting the server with the --plugin-load-add option. To do this, put these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary):
[mysqld] plugin-load-add=thread_pool.so
To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUSFROM INFORMATION_SCHEMA.PLUGINSWHERE PLUGIN_NAME LIKE ‘thread%‘;+-----------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-----------------------+---------------+ | thread_pool | ACTIVE | +-----------------------+---------------+
To verify that the Performance Schema monitoring tables are available, examine the INFORMATION_SCHEMA.TABLES table or use the SHOW TABLES statement. For example:
mysql>SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = ‘performance_schema‘AND TABLE_NAME LIKE ‘tp%‘;+-----------------------+ | TABLE_NAME | +-----------------------+ | tp_thread_group_state | | tp_thread_group_stats | | tp_thread_state | +-----------------------+
If the server loads the thread pool plugin successfully, it sets the thread_handling system variable to loaded-dynamically.
If the plugin fails to initialize, check the server error log for diagnostic messages.
Prior to MySQL 8.0.14, the thread pool monitoring tables are plugins separate from the thread pool plugin and can be installed separately.
To enable thread pool capability, load the plugins to be used by starting the server with the --plugin-load-add option. For example, if you name only the plugin library file, the server loads all plugins that it contains (that is, the thread pool plugin and all the INFORMATION_SCHEMA tables). To do this, put these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary):
[mysqld] plugin-load-add=thread_pool.so
That is equivalent to loading all thread pool plugins by naming them individually:
[mysqld] plugin-load-add=thread_pool=thread_pool.so plugin-load-add=tp_thread_state=thread_pool.so plugin-load-add=tp_thread_group_state=thread_pool.so plugin-load-add=tp_thread_group_stats=thread_pool.so
If desired, you can load individual plugins from the library file. To load the thread pool plugin but not the INFORMATION_SCHEMA tables, use an option like this:
[mysqld] plugin-load-add=thread_pool=thread_pool.so
To load the thread pool plugin and only the TP_THREAD_STATE INFORMATION_SCHEMA table, use options like this:
[mysqld] plugin-load-add=thread_pool=thread_pool.so plugin-load-add=tp_thread_state=thread_pool.so
To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUSFROM INFORMATION_SCHEMA.PLUGINSWHERE PLUGIN_NAME LIKE ‘thread%‘ OR PLUGIN_NAME LIKE ‘tp%‘;+-----------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-----------------------+---------------+ | thread_pool | ACTIVE | | TP_THREAD_STATE | ACTIVE | | TP_THREAD_GROUP_STATE | ACTIVE | | TP_THREAD_GROUP_STATS | ACTIVE | +-----------------------+---------------+
If the server loads the thread pool plugin successfully, it sets the thread_handling system variable to loaded-dynamically.
If a plugin fails to initialize, check the server error log for diagnostic messages.
The thread pool consists of a number of thread groups, each of which manages a set of client connections. As connections are established, the thread pool assigns them to thread groups in round-robin fashion.
The number of thread groups is configurable using the thread_pool_size system variable. The default number of groups is 16. For guidelines on setting this variable, see Section 5.6.3.4, “Thread Pool Tuning”.
The maximum number of threads per group is 4096 (or 4095 on some systems where one thread is used internally).
The thread pool separates connections and threads, so there is no fixed relationship between connections and the threads that execute statements received from those connections. This differs from the default thread-handling model that associates one thread with one connection such that a given thread executes all statements from its connection.
By default, the thread pool tries to ensure a maximum of one thread executing in each group at any time, but sometimes permits more threads to execute temporarily for best performance. The algorithm works in the following manner:
Each thread group has a listener thread that listens for incoming statements from the connections assigned to the group. When a statement arrives, the thread group either begins executing it immediately or queues it for later execution:
Immediate execution occurs if the statement is the only one received and no statements are queued or currently executing.
Queuing occurs if the statement cannot begin executing immediately.
If immediate execution occurs, execution is performed by the listener thread. (This means that temporarily no thread in the group is listening.) If the statement finishes quickly, the executing thread returns to listening for statements. Otherwise, the thread pool considers the statement stalled and starts another thread as a listener thread (creating it if necessary). To ensure that no thread group becomes blocked by stalled statements, the thread pool has a background thread that regularly monitors thread group states.
By using the listening thread to execute a statement that can begin immediately, there is no need to create an additional thread if the statement finishes quickly. This ensures the most efficient execution possible in the case of a low number of concurrent threads.
When the thread pool plugin starts, it creates one thread per group (the listener thread), plus the background thread. Additional threads are created as necessary to execute statements.
The value of the thread_pool_stall_limit system variable determines the meaning of “finishes quickly” in the previous item. The default time before threads are considered stalled is 60ms but can be set to a maximum of 6s. This parameter is configurable to enable you to strike a balance appropriate for the server work load. Short wait values permit threads to start more quickly. Short values are also better for avoiding deadlock situations. Long wait values are useful for workloads that include long-running statements, to avoid starting too many new statements while the current ones execute.
If thread_pool_max_active_query_threads is 0, the default algorithm applies as just described for determining the maximum number of active threads per group. The default algorithm takes stalled threads into account and may temporarily permit more active threads. If thread_pool_max_active_query_threads is greater than 0, it places a limit on the number of active threads per group.
The thread pool focuses on limiting the number of concurrent short-running statements. Before an executing statement reaches the stall time, it prevents other statements from beginning to execute. If the statement executes past the stall time, it is permitted to continue but no longer prevents other statements from starting. In this way, the thread pool tries to ensure that in each thread group there is never more than one short-running statement, although there might be multiple long-running statements. It is undesirable to let long-running statements prevent other statements from executing because there is no limit on the amount of waiting that might be necessary. For example, on a replication master, a thread that is sending binary log events to a slave effectively runs forever.
A statement becomes blocked if it encounters a disk I/O operation or a user level lock (row lock or table lock). The block would cause the thread group to become unused, so there are callbacks to the thread pool to ensure that the thread pool can immediately start a new thread in this group to execute another statement. When a blocked thread returns, the thread pool permits it to restart immediately.
There are two queues, a high-priority queue and a low-priority queue. The first statement in a transaction goes to the low-priority queue. Any following statements for the transaction go to the high-priority queue if the transaction is ongoing (statements for it have begun executing), or to the low-priority queue otherwise. Queue assignment can be affected by enabling the thread_pool_high_priority_connection system variable, which causes all queued statements for a session to go into the high-priority queue.
Statements for a nontransactional storage engine, or a transactional engine if autocommit is enabled, are treated as low-priority statements because in this case each statement is a transaction. Thus, given a mix of statements for InnoDB and MyISAM tables, the thread pool prioritizes those for InnoDB over those for MyISAM unless autocommit is enabled. With autocommit enabled, all statements will be low priority.
When the thread group selects a queued statement for execution, it first looks in the high-priority queue, then in the low-priority queue. If a statement is found, it is removed from its queue and begins to execute.
If a statement stays in the low-priority queue too long, the thread pool moves to the high-priority queue. The value of the thread_pool_prio_kickup_timer system variable controls the time before movement. For each thread group, a maximum of one statement per 10ms or 100 per second will be moved from the low-priority queue to the high-priority queue.
The thread pool reuses the most active threads to obtain a much better use of CPU caches. This is a small adjustment that has a great impact on performance.
While a thread executes a statement from a user connection, Performance Schema instrumentation accounts thread activity to the user connection. Otherwise, Performance Schema accounts activity to the thread pool.
Here are examples of conditions under which a thread group might have multiple threads started to execute statements:
One thread begins executing a statement, but runs long enough to be considered stalled. The thread group permits another thread to begin executing another statement even through the first thread is still executing.
One thread begins executing a statement, then becomes blocked and reports this back to the thread pool. The thread group permits another thread to begin executing another statement.
One thread begins executing a statement, becomes blocked, but does not report back that it is blocked because the block does not occur in code that has been instrumented with thread pool callbacks. In this case, the thread appears to the thread group to be still running. If the block lasts long enough for the statement to be considered stalled, the group permits another thread to begin executing another statement.
The thread pool is designed to be scalable across an increasing number of connections. It is also designed to avoid deadlocks that can arise from limiting the number of actively executing statements. It is important that threads that do not report back to the thread pool do not prevent other statements from executing and thus cause the thread pool to become deadlocked. Examples of such statements follow:
Long-running statements. These would lead to all resources used by only a few statements and they could prevent all others from accessing the server.
Binary log dump threads that read the binary log and send it to slaves. This is a kind of long-running “statement” that runs for a very long time, and that should not prevent other statements from executing.
Statements blocked on a row lock, table lock, sleep, or any other blocking activity that has not been reported back to the thread pool by MySQL Server or a storage engine.
In each case, to prevent deadlock, the statement is moved to the stalled category when it does not complete quickly, so that the thread group can permit another statement to begin executing. With this design, when a thread executes or becomes blocked for an extended time, the thread pool moves the thread to the stalled category and for the rest of the statement‘s execution, it does not prevent other statements from executing.
The maximum number of threads that can occur is the sum of max_connections and thread_pool_size. This can happen in a situation where all connections are in execution mode and an extra thread is created per group to listen for more statements. This is not necessarily a state that happens often, but it is theoretically possible.
This section provides guidelines on setting thread pool system variables for best performance, measured using a metric such as transactions per second.
thread_pool_size is the most important parameter controlling thread pool performance. It can be set only at server startup. Our experience in testing the thread pool indicates the following:
If the primary storage engine is InnoDB, the optimal thread_pool_size setting is likely to be between 16 and 36, with the most common optimal values tending to be from 24 to 36. We have not seen any situation where the setting has been optimal beyond 36. There may be special cases where a value smaller than 16 is optimal.
For workloads such as DBT2 and Sysbench, the optimum for InnoDB seems to be usually around 36. For very write-intensive workloads, the optimal setting can sometimes be lower.
If the primary storage engine is MyISAM, the thread_pool_size setting should be fairly low. Optimal performance is often seen with values from 4 to 8. Higher values tend to have a slightly negative but not dramatic impact on performance.
Another system variable, thread_pool_stall_limit, is important for handling of blocked and long-running statements. If all calls that block the MySQL Server are reported to the thread pool, it would always know when execution threads are blocked. However, this may not always be true. For example, blocks could occur in code that has not been instrumented with thread pool callbacks. For such cases, the thread pool must be able to identify threads that appear to be blocked. This is done by means of a timeout, the length of which can be tuned using the thread_pool_stall_limit system variable. This parameter ensures that the server does not become completely blocked. The value of thread_pool_stall_limit has an upper limit of 6 seconds to prevent the risk of a deadlocked server.
thread_pool_stall_limit also enables the thread pool to handle long-running statements. If a long-running statement was permitted to block a thread group, all other connections assigned to the group would be blocked and unable to start execution until the long-running statement completed. In the worst case, this could take hours or even days.
The value of thread_pool_stall_limit<