Overview of the MySQL Server Architecture

Sometimes it can be useful to take a step back and look at the world from a bit larger distance than usual. So in this blog, I will take a look at the high level architecture of MySQL Server.

Information

This is meant as a simplified overview and does not include all details.

Overview of the MySQL Server Architecture

For the discussion I will be referring to the the following figure that shows some of the features and plugins of MySQL. Orange boxes are available both for the community version and the commercial (enterprise) version, whereas red means the plugin is exclusive for the commercial version. Ellipsoid elements are plugins, whereas square (yes with round corners) elements indicate other features. The figure is based on MySQL 5.7.20 where the audit log can be used to block access to tables.
At the top there are the various connectors and APIs that can be used in the application to connect to MySQL. As the mysql command-line client uses the C API, this is included in this part of the figure as well. There are more connectors and APIs than I could fit into the figure; for the complete list see the documentation index.

The large darkish box represents the server. At the top there is the connection handling where there are two choices: use one thread per connection or use the commercial thread pool plugin. One thread per connection is the original way of handling new connections and as the name implies, it is simply creating a new thread for each new connection. The MySQL Enterprise Thread Pool will limit the overall number of threads by re-using the threads. This leads to better scalability particularly in the case of many connections executing short queries. After the initial thread handling, it is possible to send the connection through the optional Connection-Control plugin which will throttle the connections for an account, when there are multiple attempts to login with the wrong password.

Once the connection has been cleared to move ahead, the query is checked against the Query Cache if it is a SELECT statement, and the Query Cache is enabled. For most workloads the Query Cache will be an overhead that does not justify the potential gain. So, in practice it is recommended to completely disable it.

Note

The Query Cache has been deprecated and is removed in MySQL 8.0.3 and later.

Next is the optional Query Rewrite plugin which allows the DBA to define which queries should be rewritten based on normalized queries like those in the Performance Schema digest summary table. For example, to change the query SELECT 130 to SELECT * FROM world.City WHERE ID = 130 – and the same for all other IDs, the following rule can be used:

INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES ('SELECT ?', 'SELECT * FROM world.City WHERE ID = ?');

CALL query_rewrite.flush_rewrite_rules();

This assumes the Query Rewrite plugin has already been installed.

It is not until now that the query actual reaches the parser (this will not happen if the query has a match in the Query Cache). After the parser has processed the query, it is known which schemas, tables, columns, etc. that will be required to answer the query, which means it is also possible to verify whether the user is allowed to use these objects.

After the parser, the MySQL Enterprise Firewall will be invoked if it is installed and enabled. The firewall compares the query's digest to a whitelist of digests and if the query is in the whitelist. If the query is not in the whitelist it can be recorded or blocked (depending on configuration).

The last plugin is the MySQL Enterprise Audit plugin. In MySQL 5.7.20 and later, the audit log can be used to block access to a schema or a table in addition to log the access. This is the time where the plugin checks whether the access is allowed.

Finally the optimizer will determine the query plan and the query will be executed. The query execution will in almost all cases involve requesting data from or sending data to one of the storage engines. MySQL supports several storage engines through the pluggable storage engine architecture. Nowadays, the main storage engine is InnoDB, but several others exists including the NDBCluster storage engine that is used in MySQL NDB Cluster to execute queries through the SQL nodes (MySQL Server instances).

If You Want to Learn More

One nice way to investigate which order plugins and features are executed is to use the Performance Schema. For example to investigate the above features, enable the following instruments and consumers in the MySQL configuration file (or online executing UPDATE statements):

performance_schema_instrument                              = %=OFF
performance_schema_instrument                              = %alog%=ON
performance_schema_instrument                              = %firewall%=ON
performance_schema_instrument                              = %/Query_cache%=ON
performance_schema_instrument                              = %query cache%=ON
performance_schema_instrument                              = %rewriter%=ON
performance_schema_instrument                              = statement/%=ON
performance_schema_instrument                              = stage/%=ON
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_stages_current          = ON
performance_schema_consumer_events_stages_history_long     = ON
performance_schema_consumer_events_waits_current           = ON
performance_schema_consumer_events_waits_history_long      = ON

Then use two connections – one for monitoring and one for executing the query. Disable instrumentation for the monitoring connection:

mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID());
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Determine the Performance Schema thread ID for the connection that will be executing queries:

mysql> SELECT THREAD_ID
         FROM performance_schema.threads
        WHERE PROCESSLIST_USER = 'testuser';
+-----------+
| THREAD_ID |
+-----------+
|        30 |
+-----------+
1 row in set (0.07 sec)

This assumes the two connections use different usernames. If that is not the case, then you will need to adjust the above query.

Optionally, to make it easier to find the events just for the test query, truncate all tables in the Performance Schema (this will lose all historical data, so is most appropriate for a test system):

mysql> CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary             |
+---------------------+
| Truncated 44 tables |
+---------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Now execute the query – for example one that gets rewritten by the Query Rewriter plugin using the rule above:

mysql> SELECT 130;
+-----+--------+-------------+-----------------+------------+
| ID  | Name   | CountryCode | District        | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS         | New South Wales |    3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set, 1 warning (0.08 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                             |
+-------+------+-----------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'SELECT 130' rewritten to 'SELECT * FROM world.City WHERE ID = 130' by a query rewrite plugin |
+-------+------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Finally, the Performance Schema events can be retrieved:

mysql> (SELECT EVENT_ID, 'Statement' AS Type, EVENT_NAME FROM performance_schema.events_statements_history_long WHERE THREAD_ID = 30)
       UNION
       (SELECT EVENT_ID, 'Stage' AS Type, EVENT_NAME FROM performance_schema.events_stages_history_long WHERE THREAD_ID = 30)
       UNION
       (SELECT EVENT_ID, 'Wait' AS Type, EVENT_NAME FROM performance_schema.events_waits_history_long WHERE THREAD_ID = 30)
       ORDER BY EVENT_ID;
+----------+-----------+--------------------------------------------------------+
| EVENT_ID | Type      | EVENT_NAME                                             |
+----------+-----------+--------------------------------------------------------+
|        1 | Statement | statement/sql/select                                   |
|        2 | Stage     | stage/sql/starting                                     |
|        3 | Wait      | wait/synch/rwlock/rewriter/LOCK_plugin_rewriter_table_ |
|        4 | Wait      | wait/synch/rwlock/firewall/users_lock                  |
|        5 | Stage     | stage/sql/checking permissions                         |
|        6 | Stage     | stage/sql/Opening tables                               |
|        7 | Wait      | wait/synch/mutex/alog/LOCK_reopen                      |
|        8 | Wait      | wait/synch/mutex/alog/LOCK_block                       |
|        9 | Wait      | wait/synch/mutex/alog/LOCK_block                       |
|       10 | Stage     | stage/sql/init                                         |
|       11 | Stage     | stage/sql/System lock                                  |
|       12 | Stage     | stage/sql/optimizing                                   |
|       13 | Stage     | stage/sql/statistics                                   |
|       14 | Stage     | stage/sql/preparing                                    |
|       15 | Stage     | stage/sql/executing                                    |
|       16 | Stage     | stage/sql/Sending data                                 |
|       17 | Stage     | stage/sql/end                                          |
|       18 | Stage     | stage/sql/query end                                    |
|       19 | Stage     | stage/sql/closing tables                               |
|       20 | Stage     | stage/sql/freeing items                                |
|       21 | Stage     | stage/sql/cleaning up                                  |
|       22 | Statement | statement/sql/show_warnings                            |
|       23 | Stage     | stage/sql/starting                                     |
|       24 | Wait      | wait/synch/rwlock/rewriter/LOCK_plugin_rewriter_table_ |
|       25 | Wait      | wait/synch/rwlock/firewall/users_lock                  |
|       26 | Stage     | stage/sql/query end                                    |
|       27 | Stage     | stage/sql/closing tables                               |
|       28 | Stage     | stage/sql/freeing items                                |
|       29 | Stage     | stage/sql/cleaning up                                  |
+----------+-----------+--------------------------------------------------------+
29 rows in set (0.00 sec)

Have fun.