Fitting this in to a 20 minute slot was an interesting thing to do, so I couldn’t go in depth on the entire API, but hopefully I did have enough time to show just how simple it is to move to instrumented code.

The output is a little different to before, but now it’s possible to set up multiple clusters, of different shapes if you like, on different docker networks. You simply provide a unique value for the new –base-network and –name parameters when using the build and start commands.

This creates the initial cluster config.ini on build, then creates the network “myc1“, using that also as the prefix for the container names, and sets that all up on the “172.18” network IP range on start.

To create the second cluster side by side we now just use a different –name and –base-network:

Nothing that helps to orchestrate starting the containers, I don’t want anything fancy, just to start a Cluster of a certain shape (n of each node type), all on a local machine, for purely testing and playing around with.

So, with my searching failing to satisfy, I instead just created my own.

Note: This is not meant to grow in to some multi-machine, production oriented, MySQL Cluster deployment orchestrator, it’s purely meant for a fully local test set up. If you want to deploy MySQL Cluster in docker, in production, this is not the script for you, sorry. If you want to play around locally with a test setup though – I think this is ideal personally (it’s helping me!).

Oracle Bug #23621189 – The `ps_trace_statement_digest` procedure ran EXPLAIN incorrectly in certain cases (such as on a SHOW statement, no query being specified, or not having a full qualified table), the procedure now catches these issues and ignores them

It has significant contributions from both external contributors (thank you Daniël and Shlomi) and internal (thank you Jesper and Morgan). Jesper particularly made many significant improvements in this release.

Here’s a full summary of the changes:

Improvements

The `format_bytes` function now shows no decimal places when outputting a simple bytes value

Issue #60 – A new `statement_latency` column was added to all versions, which reports the current statement latency with picosecond precision from the `performance_schema.events_statements_current` table, when enabled

Some transaction information was exposed, with the `trx_latency` (for the current or last transaction depending on `trx_state`), `trx_state` (ACTIVE, COMMITTED, ROLLED BACK), and `trx_autocommit` (YES/NO) columns

A new `metrics` view has been added. On 5.7 this provides a union view of the performance_schema.global_status and information_schema.innodb_metrics tables, along with P_S memory and the current time, as a single metrics output. On 5.6 it provides a union view of the information_schema.global_status and information_schema.innodb_metrics tables, along with the current time. (Contributed by Jesper Wisborg Krogh)

New `session`/`x$session` views have been added, which give the same output as the `processlist` view counterparts, but filtered to only show foreground connections (Contributed by Morgan Tocker)

A new `session_ssl_status` view was added, which shows the SSL version, ciper and session resuse statistics for each connection (Contributed by Daniël van Eeden)

A new `schema_auto_increment_columns` view was added, that shows statistics on each auto_incrment within the instance, including the `auto_increment_ratio`, so you can easily monitor how full specific auto_increment columns are (Contributed by Shlomi Noach)

A new `schema_redundant_indexes` view was added, that shows indexes made redundant (or duplicated) by other more dominant indexes. Also includes the the helper view `x$schema_flattened_keys`. (Contributed by Shlomi Noach)

New `schema_table_lock_waits`/`x$schema_table_lock_waits` views have been added, which show any sessions that are waiting for table level metadata locks, and the sessions that are blocking them. Resolves Git Issue #57, inspired by the suggestion from Daniël van Eeden

The `innodb_lock_waits` view had the following columns added to it, following a manually merged contribution from Shlomi Noach for a similar view

`wait_age_secs` – the current row lock wait time in seconds

`sql_kill_blocking_query` – the “KILL QUERY <connection id>” command to run to kill the blocking session current statement

`sql_kill_blocking_connection` – the “KILL <connection id>” command to run to kill the blocking session

A new `table_exists` procedure was added, which checks for the existence of table, and if it exists, returns the type (BASE TABLE, VIEW, TEMPORARY) (Contributed by Jesper Wisborg Krogh)

A new `execute_prepared_stmt()` procedure was added, which takes a SQL statement as an input variable and executes it as a prepared statement (Contributed by Jesper Wisborg Krogh)

A new `statement_performance_analyzer()` procedure was added, that allows reporting on the statements that are have been running over snapshot periods (Contributed by Jesper Wisborg Krogh)

A new `diagnostics()` procedure was added, which creates a large diagnostics report based upon most of the new instrumentation now available, computed over a configurable number of snapshot intervals (Contributed by Jesper Wisborg Krogh)

A 5.7 specific `ps_trace_thread()` procedure was added, which now shows the hierarchy of transactions and stored routines, as well as statements, stages and waits, if enabled

Added a new `ps_thread_account()` stored function, that returns the “user@host” account for a given Performance Schema thread id

Added a new `ps_thread_trx_info()` stored function which outputs, for a given thread id, the transactions, and statements that those transactions have executed, as a JSON object

Added new `list_add()` and `list_drop()` stored functions, that take a string csv list, and either add or remove items from that list respectively. Can be used to easily update variables that take such lists, like `sql_mode`.

The `ps_thread_id` stored function now returns the thread id for the current connection if NULL is passed for the in_connection_id parameter

Added a new `version_major()` stored function, which returns the major version of MySQL Server (Contributed by Jesper Wisborg Krogh)

Added a new `version_minor()` stored function, which returns the minor (release series) version of MySQL Server (Contributed by Jesper Wisborg Krogh)

Added a new `version_patch()` stored function, which returns the patch release version of MySQL Server (Contributed by Jesper Wisborg Krogh)

The `ps_is_account_enabled` function was updated to take a VARCHAR(32) user input on 5.7, as a part of WL#2284

The generate_sql_file.sh script had a number of improvements:

Generated files are now output in to a “gen” directory, that is ignored by git

Added using a new default “mysql.sys@localhost” user (that has the account locked) for the MySQL 5.7+ integration as the DEFINER for all objects

Added a warning to the top of the generated integration file to also submit changes to the sys project

Improved the the option of skipping binary logs, so that all routines can load as well – those that used SET sql_log_bin will now select a warning when being used instead of setting the option

Bug Fixes

Git Issue #51 – Fixed the `generate_sql_file.sh` script to also replace the definer in the before_setup.sql output

Git Issue #76 – Fixes for the new show_compatibility_56 variable. 5.7 versions of the `format_path()` function and `ps_check_lost_instrumentation` view were added, that use performance_schema.global_status/global_variables instead of information_schema.global_status/global_variables

Oracle Bug #21484593 / Oracle Bug #21281955 – The `format_path()` function incorrectly took and returned a VARCHAR(260) instead of VARCHAR(512) (as the underlying is exposed as in Performance Schema) causing sporadic test failures

Oracle Bug #21550271 – Fixed the `ps_setup_reset_to_default` for 5.7 with the addition of the new `history` column on the `performance_schema.setup_actors` table

Oracle Bug #21550054 – It is possible that the views can show data that overflows when aggregating very large values, reset all statistics before each test to ensure no overflows

Oracle Bug #21647101 – Fixed the `ps_is_instrument_default_enabed` and `ps_is_instrument_default_timed` to take in to account the new instruments added within 5.7

MySQL Bug #77848 – Added the missing ps_setup_instruments_cleanup.inc

Fixed the `ps_setup_reset_to_default()` procedure to also set the new `ENABLED` column within `performance_schema.setup_actors` within 5.7

The `user_summary_by_file_io`/`x$user_summary_by_file_io` and `host_summary_by_file_io`/`x$host_summary_by_file_io` tables were incorrectly aggregating all wait events, not just `wait/io/file/%`

Implementation Details

Tests were improved via 5.7 integration

Template files were added for stored procedures and functions

Improved the sys_config_cleanup.inc procedure in tests to be able to reset the sys_config table completely (including the set_by column to NULL). The triggers can now be set to not update the column by setting the @sys.ignore_sys_config_triggers user variable to true

Like this:

Many of us have been there in the past, you get an alert telling you that replication has stopped because of an error, you dig in to it to find that you’re getting an error for an update event that is trying to update a non-existent row, or a duplicate key error because the row ID for some INSERT already exists.

Even with the server set to read only (and not using the new super_read_only variable from MySQL 5.7.8), these problems can still happen – how many of you have seen over-zealous ops trying to “quickly fix” some problem only to royally screw up your data integrity?

The question then becomes – “who or what is making changes on my replica that shouldn’t be?!?”.

The only way to find this out in the past, and still “the conventional wisdom” (I just saw it recommended in a discussion in the MySQL support group) was to turn on the binary log on the replica, make sure log_slave_updates is not enabled, and then see what events turn up in the binary log.

The data within them is like the merged output of “SHOW GLOBAL STATUS LIKE ‘Com_%'” (only by user here for example, or by host/account in the other base summary views), with the regular performance schema statement data (latency, lock time, result info, sorting info, temporary table info etc.).

The replication SQL thread statement activity is also recorded within these tables. Within the base performance schema tables, these will have a user / host of NULL, and in the sys schema these are translated to a generic “background” user (an example from my local sandbox topology where one table was created with one row via replication, shown in the “background” user, and the msandbox user has created newly the sys schema):

Added the `blocking_trx_started`, `blocking_trx_age`, `blocking_trx_rows_locked` and `blocking_trx_rows_modified` for blocking transaction

Order the result set so the oldest lock waits are first

The `waiting_table` and `waiting_index` were always the same as the `blocking_table` and `blocking_index`. So the blocking_% columns have been removed and the waiting_% columns have been renamed to locked_%

The `waiting_lock_type` and `blocking_lock_type` were also always the same. So these were removed and replaced with a single `locked_type` column

Renamed the `waiting_thread` and `blocking_thread` to `waiting_pid` and `blocking_pid` respectively to avoid confusion with the threads from the Performance Schema.

Added the `sys_get_config` function, used to get configuration parameters from the `sys_config` table – primarily from other sys objects, but can be used individually (Contributed by Jesper Wisborg Krogh)

Added the `ps_is_thread_instrumented` function, to check whether a specified thread is instrumented within Performance Schema

Added the `ps_is_consumer_enabled` function, to check whether a specified consumer is enabled within Performance Schema (Contributed by Jesper Wisborg Krogh)

Added some further replacements to the `format_path` function (`slave_load_tmpdir`, `innodb_data_home_dir`, `innodb_log_group_home_dir` and `innodb_undo_directory`)

Bug Fixes

The 5.6 `host_summary` and `x$host_summary` views incorrectly had the column with `COUNT(DISTINCT accounts.user)` named `unique_hosts` instead of `unique_users` (Contributed by Jesper Wisborg Krogh)

Both the `format_time` and `format_bytes` took a BIGINT as input, and output VARCHAR, but BIGINT could be too small for aggregated values for the inputs. Now both functions both use TEXT as their input (Issue #34, Issue #38)

The `format_time` function displayed values in minutes incorrectly, it now rounds to minutes, and uses an ‘m’ suffix, like the rest of the units

The `sys_config` related triggers had no DEFINER clause set

The `ps_setup_disable_thread` procedure always disabled the current thread and was ignoring the connection id given as an argument (Contributed by Jesper Wisborg Krogh)

The `ps_trace_thread` procedure had an incorrect calculation of how long the procedure has been running (Contributed by Jesper Wisborg Krogh)

Implementation Details

Various changes were made to allow better generation of integration sql files:

The formatting for all comments has been standardized on — line comments. C-style /* comments */ have been removed

Issue #35 had one instance of this resolved in this release (contributed by Joe Grasse), but the entire code base has now been done

Each object has been created within it’s own file. No longer do x$ views live with their non-x$ counterparts