The X Dev API is a great new way to interact with MySQL. As we learned, the new NoSQL mechanism is built on the X DevAPI, X Plugin, and the X Protocol. You may have the impression these technologies are just there and once enabled nothing more is needed. That is largely true, but as with all good features, there is more to the story than simply enabling the feature.
In this chapter, we take a closer look at the X Plugin. As you will see, there is more to it than simply turning it on. The fact that it just works with the defaults means it is very stable and is applicable in most cases. However, you can configure it in several ways including a very interesting option for securing connections. However, there is more about this and even how to monitor the X Plugin in following sections.
Note
I use the term, plugin in this chapter to refer to plugins in general and X Plugin to refer to specific features of the X Plugin.
Overview
Recall from Chapter 2 that the X Plugin is a separately compiled component of MySQL that can be loaded and unloaded at runtime. Oracle named the X Plugin mysqlx and it is listed with that name in the server. Once loaded (installed), the plugin will start automatically each time the server is restarted. Also, recall that the plugin feature in MySQL is the primary mechanism Oracle uses to extend the functionality of the server without having to rebuild the code from scratch. Although plugin technology has been in MySQL for some time and initially used for storage engines, it has become the default mechanism Oracle uses for expanding and adding new features to the server.
In that respect, the X Plugin is an excellent example of the power that a plugin can bring to the server. For example, by default, the server communicated with clients using a fixed protocol commonly referred to as the MySQL client/server protocol or simply, the MySQL protocol or the old protocol. This protocol was built into the server and except for some minor changes through the lifetime of MySQL; it hasn’t changed much since the MySQL 4.X code base. Until the X Plugin came about, this was the only way clients could communicate with the server.1 Now, once you load the X Plugin, it enables a new communication protocol for the client and server using the X Protocol.
How Do MySQL Plugins Work?
In the most general sense, when a plugin is installed or started on startup, the server and plugin communicate using a special plugin API that allows the plugin to register itself as part of the server. For instance, the plugin provides callback methods for processing status variables as well as methods for enabling its functionality. This negotiation process is how a plugin can extend the functionality of the server without having to force the server to restart and does not require a recompile of the server.
That said, it is important to note that plugins are compiled against the common server libraries and as such must match the server for specific versions as well as platform (e.g., you cannot use a plugin compiled for Linux on Windows). Detection of compatibility is provided using a special versioning mechanism that is checked during startup of the plugin. Most plugins are published clearly listing the versions of the server supported. When you decide to use a new plugin, be sure to check that it is compatible with your server version. For more information about plugins, see the section “The MySQL Plugin API” in the online MySQL reference manual.
Features
Once again, the X Plugins primary purpose is to support the X Protocol for communication with the server to enable the X DevAPI (NoSQL) interface. Although that is its primary focus, there are some interesting features that you can use to help make the experience better. These including configuring the plugin to use different secure socket layer (SSL) settings than the server and changing the behavior of the plugin using system variables. We will see how to change the SSL settings and how to change the default port in the following sections. We will see more about the other system variables in a later section.
Note
Although the documentation and other text show variables for the X Plugin with initial capital letters, the variables are shown in the SQL results with lowercase names. For example, you may see a prefix of Mysqlx_, but the output from the server with display as mysqlx_. Fortunately, most SQL commands on most platforms will accept either version.
Secure Socket Layer (SSL) Connections
If you use SSL connections on your MySQL servers and want to use secure connections for the X Plugin (and your NoSQL applications), you can setup the X Plugin to use different values for the SSL options than the server. This means you can setup the X Plugin to use one SSL certificate and the server to use another. This can be very helpful in making your NoSQL applications secure without sharing the SSL data among client/server and X Protocols.
You can place the system variables and their values in the my.cnf file or pass the system variables on the server startup command (command-line). When used in this manner, system variables are often referred to as startup options. Listing the system variables and their current values can be accomplished using the following command. Note that I used the MySQL Shell to get the information using batch mode.
You can set these variables in your configuration file (my.cnf) by placing them in the section for the server named [msyqld] but you should omit the dashes. The following shows an excerpt demonstrating how to use a different SSL configuration for the server and X Plugin.
Note that I have included both sets of SSL options only the X Plugin options are named with the mysqlx_ prefix.
Note
In general, most system variables have corresponding startup options and are used in the configuration file with the same name only the underscores are changed to dashes. For example, the startup option for the mysqlx_ssl_ca system variable is --mysqlx-ssl-ca. However, the --mysqlx_ssl_ca version also works for those who forget.
To change the values temporarily or as part of a shell or batch file, you can specify the system variables as options on the command line as shown in the following. Note that we used the same values as shown previously.
Although you can use the options on the command line like this, it is not the best method. This is because unless you record the new command line somewhere or use it in a shell or batch command (and even then), it is very easy to forget what value you used or even which system variables were used. Thus, for the best method, always place custom system variable changes in your MySQL configuration file.
Changing the Default Port
Recall the X Plugin uses a different port than the server. The default port is 33060. If you want to change the default port, you can do so using the mysqlx_port system variable. As with the SSL options, you can place this in the my.cnf file or pass it as a startup option on the server startup command (command-line). You can also check the default port with the following command. The valid range of values is 1-65535. For example, you can setup the X Plugin to use port 3307.
Because the mysqlx_port system variable is only read at startup (for obvious reasons), changing the value requires a restart to use a different port.
As with the SSL options, you can set the port on the command line as shown in the following. In this case, we start the server on port 3307 and the X Plugin listens on port 3308.
Once again, this is not the recommended method because command-line options tend to be forgotten if not placed in a shell or batch file.
Going Deeper—Journey into the Source Code
Downloading the MySQL 8 source code
Once downloaded, you can find the X Plugin source code in the rapid/plugin/x folder. You can browse the source code and see how it works and even how it negotiates with the server on startup. For example, to see the system variables, open the xpl_plugin.cc file in the rapid/plugin/x/src folder and scroll down to about line number 240 or so. You will find a structure like the example in Listing 6-1 that lists the variables supported by the plugin.
System Variable Definition (X Plugin)
Note that there is a macro definition, MYSQL_SYSVAR, that is used to define the system variables. There is also system variables listed by their names. Once the plugin is started, you can see system variables using the command in Listing 6-2. Note that the variables are named with the prefix mysqlx_ and all 14 are present (the host system was running macOS—your results may vary).
Listing the System Variables for the X Plugin
We discover more about the system variables in the next section. If you’re adventurous, keep reading the code in that file for more clues about status variables. Hint: look at the file named xpl_global_status_variables.h.
Options and Variables
As we saw in the previous section, the X Plugin has several system variables that can be set at startup either in the configuration file or on the server command line. The configuration items that can be controlled include such items as the default port, configure parameters for connections, and establish timeout limits. You also can see several status variables that the X Plugin reports concerning performance, statistics, and more. These status variables can be used to monitor the X Plugin to help you tune its options to match your environment. I explore the commonly used startup options, system variables, and status variables in the following sections.
Note
I use the term variable to apply to qualities and features that are common to startup options, system variables, and status variables.
Variables can have two scope levels: global that apply to all connections and session that apply only to the current connection (session), that is, the connection you are currently using. There is no provision to capture data from other sessions that you are not currently using.
Variables also can support dynamic values that can be set at runtime and values that can only be set at startup. Although you can view the values of any variable regardless of scope, you can only set values at runtime for dynamic variables. You must take care when setting global variables so that you do not adversely affect other connections.
How to View Values of Variables
There are several ways to see the values of variables. We saw in the last section that you can use the SQL commands SHOW VARIABLES to see system variables and SHOW STATUS command to see the values of status variables. Remember, startup options are associated with a system variable so using the SHOW VARIABLES command is all you need to see those.
You can also see the values of system variables by using a special form of the SELECT command using a special notation or shortcut in the form of @@GLOBAL for the value at the global scope and @@SESSION for the value at the session scope. Although there are currently no session level system variables for the X Plugin, the following shows the global system variable mysqlx_connect_timeout.
You also can see the values of variables using the PERFORMANCE_SCHEMA tables (views). In this case, you can see the status variables either by session or global scope. Or you can write a SQL query to combine the data with scope and shown in Listing 6-3 (your results may vary). I formatted the following SQL statement to make it easier to read.
X Plugin Status Variables with Scope
Note that we see the same variables and their scope.
Note
A complete description and tutorial of using the Performance Schema is beyond the scope of this book. For more information about the performance schema, see the section “MySQL Performance Schema” in the online MySQL reference manual.
You may have noticed in previous examples that I used the SHOW SQL command to see the values of variables. There are two SHOW commands: one for system variables (SHOW VARIABLES) and another for status variables (SHOW STATUS). You can use the LIKE clause to find all the X Plugin variables. The LIKE clause allows you to specify part of a name and use wildcards. For example, you can find all the system and status variables for the X Plugin using the following two commands.
Note that I use the LIKE clause using mysqlx_%. This will show all the variables that start with mysqlx_. Because all X Plugin variables have this prefix, we see all the variables for the X Plugin.
Tip
The LIKE clause can be very handy in another way. You can use it to search for a variable that you may have forgotten its name simply by using a keyword. For example, if you wanted to see all the variables that have dir in the name, use LIKE '%dir%'.
By now you may be thinking that we’re using a lot of SQL commands. You may be wondering if there is a way to see the values of variables using the NoSQL interface. As of this writing, there are no objects in the X DevAPI or part of the MySQL Shell that you can use to get information about variables and their values.2 This is the reason I mentioned earlier in the book that the SQL interface is still needed for some routine maintenance tasks. Checking and setting variables is one of the maintenance and configuration tasks that require the use of SQL commands.
What About Information_Schema?
If you are familiar with the special INFORMATION_SCHEMA database, you may be wondering what happened to using the session_* and global_* tables (views) for showing values of variables. Starting with server version 5.7.6, these tables (views) were deprecated. This is because they were replaced with tables (views) in PERFORMANCE_SCHEMA. For more information about the changes and migrating to PERFORMANCE_SCHEMA, see the section “Migrating to Performance Schema System and Status Variable Tables” in the online MySQL reference manual.
How to Set Values of Variables
We have already discovered we can set system variables in the configuration file and we can use startup options to set the system variables. These methods are used for variables that can only be set at startup. However, for those variables that can be set dynamically, you can change their values for session or global scope using the SET command and the @@SESSION and @@GLOBAL notation shown previously. However, because there are no session variables currently, we can only set the values for global variables as shown in Listing 6-4.
Setting Global System Variables
Should session dynamic system variables be introduced, you can set their values with the SET @@SESSION.<variable_name> command.
Tip
System variables that can be changed at runtime are known as dynamic variables. This only applies to those system variables that can be changed while the X Plugin is running.
Now that we know more about variables and how to see and set values, let’s look at the specific variables for the X Plugin. Let’s begin with those system variables that you can place in the configuration file.
System Variables and Startup Options
System Variables and Startup Options (X Plugin)
Name | Default | SysVar | Dynamic | Description |
|---|---|---|---|---|
mysqlx_bind_address | * | Yes | No | The network address that X Plugin uses for connections. |
mysqlx_connect_timeout | 30 | Yes | Yes | Number of seconds to wait for the first packet to be received from newly connected clients |
mysqlx_idle_worker_thread_timeout | 60 | No | No | Time in seconds after which an idle worker thread is terminated |
mysqlx_max_allowed_packet | 1048576 | No | Yes | The Maximum size of a network packet that X Plugin can process. |
mysqlx_max_connections | 100 | Yes | Yes | The Maximum number of concurrent client connections the X Plugin can accept. |
mysqlx_min_worker_threads | 2 | No | Yes | The minimum number of worker threads the X Plugin uses for handling client requests. |
mysqlx_ port | 33060 | Yes | No | Specifies the port where the X Plugin listens for connections |
mysqlx_port_open_timeout | 0 | Yes | No | The amount of time in seconds that X Plugin waits for a TCP/IP port to become free. |
mysqlx_socket | Platform dependent | Yes | No | The socket where X Plugin listens for connections. |
mysqlx_ssl_ca | Yes | No | The path to a file with a list of trusted SSL CAs. | |
mysqlx_ssl_capath | Yes | No | The path to a directory that contains trusted SSL CA certificates in PEM format. | |
mysqlx_ssl_cert | Yes | No | The name of the SSL certificate file to use for establishing a secure connection. | |
mysqlx_ssl_cipher | No | No | The list of permissible ciphers to use for SSL encryption. | |
mysqlx_ssl_crl | Yes | No | The path to a file containing certificate revocation lists in PEM format. | |
mysqlx_ssl_crl_path | Yes | No | The path to a directory that contains files containing certificate revocation lists in PEM format. | |
mysqlx_ssl_key | Yes | No | The name of the SSL key file to use for establishing a secure connection. |
As you can see, there are a number things we can set for the X Plugin including setting up the SSL connection, tuning the X Plugin with maximum connections limit, minimum of worker threads, and even setting the size of the data packet (how much data can be sent over the network in a single packet). Of course, we also can change the port that the X Plugin uses.
Status Variables
Recall system variables are those variables that only report statistics and other data from the plugin. Status variables cannot be set at runtime. However, most are reset whenever the server restarts. That is, counters are reset at reboot.
There are quite a few status variables for the X Plugin that report on several areas in the X Plugin. Rather than look at the status variables individually (there are over 120 if you count session and global scope), we look at the groups or areas that the status variables report on. We will see more about specific status variables in the next section where we see how to monitor the X Plugin.
mysqlx_connections_*: the number of connections accepted, rejected, and closed.
mysqlx_sessions_*: statistics about sessions such as accepted, closed, killed, and rejected.
mysqlx_stmt_*: statistics for execution, drop, list, and create for collections.
There are a few other discrete status variables that you may want to examine including errors on startup (mysqlx_init_error) and the number of rows sent to clients (mysqlx_rows_sent). For a complete list of the available status variables for the X Plugin, see the section “Status Variables for X Plugin” in the online MySQL reference manual.
Now let’s briefly look at some ways you can monitor the X Plugin and why you would want to do so.
Monitoring the X Plugin
If you want to keep an eye on the X Plugin either to ensure all is working correctly, diagnose problems, verify configuration, or tune performance, you can monitor the X Plugin using the system variables for the X Plugin. This requires reading the values at a specific time or when an event has occurred. Recall that some status variables have both a session and global scope. Thus, you may want to use the @@ notation discussed previously to query the session or global scope values.
You can see the values of status variables in several ways including using the SHOW STATUS command as well as reading the tables (views) from the PERFORMANCE_SCHEMA database. Listing 6-5 shows the tables (views) that can be used to read the values of status variables.
Performance Schema Views for Status Variables
Note that there are a few tables (views) for status variables including those for replication and by scope. Just remember to use the LIKE clause when querying for status variables for the X Plugin. However, as I mentioned previously, a complete tutorial of using the performance schema is beyond the scope of this book. Fortunately, the SHOW STATUS and SELECT with @@ notation SQL commands work well enough for most uses.3
Communication: Information about messages and data sent and received.
Connections: Information about connections including accepted, rejected, and deleted.
CRUD operations: Statistics on created, read, updated, and deleted operations.
Errors and warnings: Information about errors or warnings at startup or sent to the client.
Sessions: Information about sessions including accepted, rejected, and deleted.
SSL: Information about secure connections.
Statements: Statistics about execution, creation, and more for the document store.
Worker threads: Information about the worker threads in the X Plugin.
The following sections describe the eight areas in more detail including suggestions for tasks that you may want to perform using the variables. Each section also includes a complete list of the associated status variables, their scope and a brief description. You can use the sections as a guide when exploring the X Plugin during your diagnostic procedures or simply for curiosity.
Communication
The communication category includes status variables that report information that is transmitted to or received from the clients. You can observe the amount of traffic over the network for both a session or globally, see the number of rows sent to the client both session and globally, and check the expectation blocks for the X Protocol.
Expectation blocks are a mechanism the X Protocol uses to manage situations when there are messages in the pipeline that may have failed. That is, other, dependent tasks that are executed prior to the end of block. Expectation blocks are a way to ensure safe, reliable failure of the entire block (think transaction). There are several facets to expectation blocks and it is unlikely that you will be required to monitor them. If you’d like to know more about expectation blocks, see https://dev.mysql.com/doc/internals/en/x-protocol-expect-expectations.html .
Communication Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_bytes_received | Both | The number of bytes received through the network. |
mysqlx_bytes_sent | Both | The number of bytes sent through the network. |
mysqlx_expect_close | Both | The number of expectation blocks closed. |
mysqlx_expect_open | Both | The number of expectation blocks opened. |
mysqlx_rows_sent | Both | The number of rows sent back to clients. |
The types of tasks where you may want to use these status variables include observing how much data is sent and received and how many rows are sent to the client (in a result set). You also can see the expectation blocks data, but that may be a more advanced than what most will need when monitoring the X Plugin.
Connections
Connection Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_connection_accept_errors | Both | The number of connections that have caused accept errors. |
mysqlx_connection_errors | Both | The number of connections that have caused errors. |
mysqlx_connections_accepted | Global | The number of connections that have been accepted. |
mysqlx_connections_closed | Global | The number of connections that have been closed. |
mysqlx_connections_rejected | Global | The number of connections that have been rejected. |
The types of tasks where you may want to use these status variables include monitoring the connection errors status variables for cases when there are a lot of failures (errors). This could be as simple as an application that is using the wrong credentials or could be as nefarious as attempts to discover a login account and password.
You also can use the accepted, closed, and rejected system variables to keep an eye on the number of connections that are used. That is, if your application is used by less than 10 users, you would expect to see rather low values for these status variables. High numbers could indicate applications that connect and disconnect too often (not always a bad thing) or cases where you have more instances of the application(s) than you thought.
CRUD Operations
CRUD Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_crud_create_view | Both | The number of create view requests received. |
mysqlx_crud_delete | Both | The number of delete requests received. |
mysqlx_crud_drop_view | Both | The number of drop view requests received. |
mysqlx_crud_find | Both | The number of find requests received. |
mysqlx_crud_insert | Both | The number of insert requests received. |
mysqlx_crud_modify_view | Both | The number of modify view requests received. |
mysqlx_crud_update | Both | The number of update requests received. |
The types of tasks where you may want to use these status variables include monitoring a document store application for activity such as how many requests for deletes are issued, number of new data items added (inserted), and so on. Because the status variables have both a session and global scope, you can see the activity for a specific session and compare that to the values for the global scope (overall statistics).
Errors and Warnings
The errors and warning category provides a means to see the number of errors that have occurred at startup and notices or errors sent to the client. All the status variables in this category have both session and global scope and thus can be used to check statistics for an individual connection (session) or aggregate values from all sessions.
Notices are a way for the X Protocol to send additional information to the client at either session or global scope. When sent at the sessions level (referred to as local in the internal manual), these can include a list of committed transaction identifiers, transaction state changes, SQL warnings, and changes to variables. When sent at the global level, these can include server shutdown, disconnections in group replication, table drops, and so forth. Keep in mind that status variables are only counters so although you cannot see the messages (notices) themselves, you can see how many have been sent and whether they are informational (warning) or a response to an error or another serious event. For more information about notices in the X Protocol, see http://dev.mysql.com/doc/internals/en/x-protocol-notices-notices.html .
Errors and Warnings Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_errors_sent | Both | The number of errors sent to clients. |
mysqlx_init_error | Both | The number of errors during initialization. |
mysqlx_notice_other_sent | Both | The number of other types of notices sent back to clients. |
mysqlx_notice_warning_sent | Both | The number of warning notices sent back to clients. |
The types of tasks where you may want to use these status variables include checking for excessive errors for a session, which may indicate something is wrong with the application (or users’ use thereof). The notices status variables may be helpful to gather data for diagnosing errors and warnings sent to the clients. That is, it may indicate there is additional data that you may want to look for in the logs. For example, a high count for these variables at the session level could indicate the application is attempting to do something it should not do or is performing the operations too often.
However, the most important status variable in this category to watch when starting out with the X Plugin or when changing its configuration is the mysqlx_init_error status variable. Check this variable to ensure there are no errors at startup (initialization) and if there are issues, track them down to make sure you have everything configured correctly. Although sometimes an error might be okay, in general you should not see any errors registered for initialization.
Sessions
Session Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_sessions | Global | The number of sessions that have been opened. |
mysqlx_sessions_accepted | Global | The number of session attempts that have been accepted. |
mysqlx_sessions_closed | Global | The number of sessions that have been closed. |
mysqlx_sessions_fatal_error | Global | The number of sessions that have closed with a fatal error. |
mysqlx_sessions_killed | Global | The number of sessions that have been killed. |
mysqlx_sessions_rejected | Global | The number of session attempts that have been rejected. |
The types of tasks where you may want to use these status variables include checking to see how many sessions failed (msyqlx_sessions_fatal_error), were killed by someone like an admin (mysqlx_sessions_killed), and how many were opened or closed successfully. As with connection attempts, you could use the status variables in this category to monitor how often and how many sessions are being created and used. Too many may indicate more sessions than you originally planned, widespread use has increased, and so forth. Check these status variables whenever you have or think there may be issues with creating sessions or when sessions begin to fail frequently.
SSL
SSL Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_ssl_accepts | Global | The number of accepted SSL connections |
mysqlx_ssl_active | Both | If SSL is active |
mysqlx_ssl_cipher | Both | The current SSL cipher (empty for non-SSL connections) |
mysqlx_ssl_cipher_list | Both | A list of possible SSL ciphers (empty for non-SSL connections) |
mysqlx_ssl_ctx_verify_depth | Both | The certificate verification depth limit currently set in ctx |
mysqlx_ssl_ctx_verify_mode | Both | The certificate verification mode currently set in ctx |
mysqlx_ssl_finished_accepts | Global | The number of successful SSL connections to the server |
mysqlx_ssl_server_not_after | Global | The last date for which the SSL certificate is valid |
mysqlx_ssl_server_not_before | Global | The first date for which the SSL certificate is valid |
mysqlx_ssl_verify_depth | Global | The certificate verification depth for SSL connection |
mysqlx_ssl_verify_mode | Global | The certificate verification mode for SSL connection |
mysqlx_ssl_version | Both | The name of the protocol used for the connection ssl |
The types of tasks where you may want to use these status variables include checking to ensure SSL is turned on for a session or for all sessions (mysqlx_ssl_active), viewing the number of SSL connections accepted (mysqlx_ssl_finished_accepts), and the dates for valid SSL certificates. This last operation can save you from a host of rabbit hole diagnosis4 chasing down strange error messages.
Note that some of the variables have both a session and global scope so you can use these to help diagnose SSL connection issues at the session level. For example, if a client cannot connect properly to the X Plugin with SSL, takes a long time to connect, or there are errors during the connection.
For more information about these status variables, you can see the section “Using Secure Connections” in the online MySQL reference manual. Because most of these status variables are the same as those used by the server, the same techniques and descriptions apply.
Statements
The statements category is a very interesting category and can be quite handy in diagnosing or observing operations related to the X DevAPI. In particular, there are status variables that count the number of collection creates and drops, collection indexing, number of execution events, listing clients, and more.
Statement Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_stmt_create_collection | Both | The number of create collection statements received. |
mysqlx_stmt_create_collection_index | Both | The number of create collection index statements received. |
mysqlx_stmt_disable_notices | Both | The number of disable notice statements received. |
mysqlx_stmt_drop_collection | Both | The number of drop collection statements received. |
mysqlx_stmt_drop_collection_index | Both | The number of drop collection index statements received. |
mysqlx_stmt_enable_notices | Both | The number of enable notice statements received. |
mysqlx_stmt_ensure_collection | Both | The number of ensure collection statements received. |
mysqlx_stmt_execute_mysqlx | Both | The number of StmtExecute messages received with namespace set to mysqlx. |
mysqlx_stmt_execute_sql | Both | The number of StmtExecute requests received for the SQL namespace. |
mysqlx_stmt_execute_xplugin | Both | The number of StmtExecute requests received for the X Plugin namespace. |
mysqlx_stmt_kill_client | Both | The number of kill client statements received. |
mysqlx_stmt_list_clients | Both | The number of list client statements received. |
mysqlx_stmt_list_notices | Both | The number of list notice statements received. |
mysqlx_stmt_list_objects | Both | The number of list object statements received. |
mysqlx_stmt_ping | Both | The number of ping statements received. |
The types of tasks where you may want to use these status variables include monitoring the document store for creation and drop of collections and related indexes. This could be helpful if you are monitoring a document store application for how it is using collections. That is, frequent collection creation may indicate that the data is not being saved often or is being generated on the fly. This may lead you to discover ways to improve how your applications use data.
Other tasks include monitoring the notices (messages), number of times client kill requests were sent (not necessarily successfully executed), and listing notices, clients, and objects. Most of these status variables are beyond the scope of normal monitoring that most will require. Indeed, some of these status variables are referenced only briefly in the documentation and rarely anywhere else other than the source code itself.
One last status variable that may be helpful is the mysqlx_stmt_ping status variable to see how many times clients checked the server to see if it is alive. High values here could indicate potential network connectivity issues.
Worker Threads
Worker Threads Status Variables (X Plugin)
Variables | Scope | Description |
|---|---|---|
mysqlx_worker_threads | Global | The number of worker threads available |
mysqlx_worker_threads_active | Global | The number of worker threads currently used. |
The types of tasks where you may want to use these status variables include when there are performance problems regarding slower execution. This can happen if more worker threads are active than what the system can handle or if there are not enough worker threads available for all the connections and requests for execution of tasks.
As the X Plugin matures, there are likely to be more tasks that you may want to perform for diagnosing problems or tuning performance, or simply configuring the plugin. If you are interested in monitoring the X Plugin, be sure to check the online MySQL reference manual as each new release of MySQL 8 is announced for updates to the status variables as well as tasks for monitoring the X Plugin.
Summary
The X Plugin is an extension to the MySQL Server that can be loaded dynamically. This is very significant because the X Plugin enables the document store feature permitting the storing and retrieving of JSON documents. In particular, the X Plugin permits communication between the server and clients using the X Protocol and interact with the X DevAPI to permit ACID compliant storage. Further, using the X DevAPI you can use a NoSQL like syntax to execute CRUD operations against the document store. It is the X Plugin the ties all the functionality together to turn the MySQL server into a document store.
In this chapter, we learned more about the X Plugin and how it works. In particular, we saw how to configure the X Plugin such as changing the port and enabling secure connections via SSL that are separate from the server. We also discovered the other system variables as well as a lengthy list of status variables that you can use to monitor the X Plugin. Finally, we discovered some interesting internal facts about the X Plugin such as how it registers system variables.
If you are still curious about the X Plugin and how it works internally, there is no better document to examine than the source code itself. Although it may not be for the uninitiated,5 studying the source code is akin to reading the Greek originals.
In the next chapter, I take a closer look at how the new X Protocol works including a look at how the server exchanges packets with clients. As you will see, it is quite different than the old protocol. This is mostly due to the building blocks used to design and implement the new protocol.