I have moved!

 Check my new blog, includes old contents and newer ones!

www.mortensi.com

How To Monitor MySQL With dim_STAT

Monitoring a database is a critical mission: collecting information over time allows tracking how's behaving certain instance in response to a determined workload. There are several solution on the market, and MySQL offers its own solution in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I'd like to remember that there's also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and "as is"). I'm talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline metrics captures.

Extensive instructions to setup, configure and use dim_STAT are presented here. In this post I will share a practical summary of what I did to set it up. First of all it's worth clarifying that dim_STAT is composed of a server, dim_STAT-Server, and allows two modalities: online and offline.

1. Online: can monitor live instances (STAT-service) from multiple hosts
2. Offline: can collect metrics for later analysis (EasySTAT), useful for instances that are not accessible e.g. behind a firewall

Here's a brief recap of the architecture: STAT-service is the remote Agent that monitors the instance and delivers metrics back to a backend, which in turn stores them into an own MySQL Server for later processing. The web interface allows configuration and it's the main entry point to retrieve data, make graphs and in general consult captured data. Following picture summarises the architecture.

In this post I'll explore the offline analysis: this is using EasySTAT. 

Download

dim_STAT-v90-linux-x64.tgz here's the main package

WebX_apps-v90-u19-12.tgz the upgrade package to apply on top of the main install
STATsrv-v5-Linux-x64-mysql.tgz includes EasySTAT tool, to allow data collection "offline"

There are also packages for macOS (which I will use in this post).

Install

Once uncompressed the main package dim_STAT-v90-linux-x64.tgz, run INSTALL.sh as root. This will prompt different choices, like paths for install, user and group to run processes, MySQL (if running other MySQL instance on the same host, specify a port) and STAT-service port.

If everything is correct, installation will finalise with:

** =========================================================================
**
** You can start dim_STAT-Server now from /opt/packages/ADMIN:
**
** # /opt/packages/ADMIN/dim_STAT-Server start
**
** NOTE: for the very first start you'll need to type "start-force"
** (instead of simple "start" command)
**
** and access homepage via Web browser - http://mortensi:80
**
** To collect stats from any Solaris, Linux or MacOSX machines just
** install & start on them a corresponding [STAT-service] package...
**
** Enjoy! ;-)
**
** -Dimitri
** =========================================================================


Upgrade

After installation, it is possible to apply the latest patch. Uncompress WebX_apps-v90-u19-12.tgz and then copy its content over /opt/WebX/apps. Patching is hot, so can be done later as well, especially when dim_STAT-Server is already running.

Start

In order to start dim_STAT-Server, this one command will do the job:

sh-3.2# /opt/packages/ADMIN/dim_STAT-Server start-force
================[ dim_STAT-Server: start-force ]================
*
* MySQL Database Server
*
=> Log output : /opt/packages/mysql/data/mysqld.log
=> Local socket: /opt/packages/mysql/data/mysql.sock
=> Admin Access: root# /opt/packages/mysql/bin/mysql -S /opt/packages/mysql/data/mysql.sock

Starting...
200305 01:21:40 mysqld_safe Logging to '/opt/packages/mysql/data/mysqld.log'.
200305 01:21:40 mysqld_safe Starting mysqld daemon with databases from /opt/packages/mysql/data
Done.
** CHECK MySQL Server connection...
Starting HTTP server from: /opt/packages/httpd
Done.
================[ dim_STAT-Server: start-force -- done. ]================
sh-3.2#


This will start the minimal Apache web server and the internal MySQL instance used internally to store all the metrics that will be imported after the collection. Test the internal MySQL instance, can be accessed with:

sh-3.2# /opt/packages/mysql/bin/mysql -S /opt/packages/mysql/data/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.53 Source distribution

Check the installation is successful by loading in your browser:
http://127.0.0.1/cgi-bin/WebX.mySQL/dim_STAT/x.welcome

Collect metrics with EasySTAT

As mentioned, here I am interested in doing an offline audit of an instance, then I have used EasySTAT to capture metrics from the MySQL instance under observation. Instructions to deploy, configure, test and start the collection can be followed here:
http://dimitrik.free.fr/blog/posts/collect-stats-howto.html

I have tested a 8 hours collection of a MySQL 8 instance:

nohup ./EasySTAT.sh /opt/stats/data/ 10 8 "20200228-mortensi"

And once collection is started, make sure something is running in background:

[root@host1 bin]# ps aux | grep STAT
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 1448 0.0 0.0 6356 624 pts/1 S 18:51 0:00 /opt/stats/STATsrv/bin/vmstat 10
root 1450 0.0 0.0 5096 884 pts/1 S 18:51 0:00 /opt/stats/STATsrv/bin/WebX /opt/stats/STATsrv/bin/x.Timer -Timeout 10
root 1462 0.0 0.0 4328 764 pts/1 S 18:51 0:00 /opt/stats/STATsrv/bin/mpstat -P ALL 10
root 1463 0.0 0.0 5096 876 pts/1 S 18:51 0:00 /opt/stats/STATsrv/bin/WebX /opt/stats/STATsrv/bin
...

At the end of the collection, a gunzipped series of files will be collected under /opt/stats/data.

Import metrics with BatchLOAD

Now it's time to import data with BatchLOAD under the database (that back-end MySQL instance started by dim_STAT-Server).

First, configure file ./LoadDATA.sh (this is generated by EasySTAT under data folder collection: /opt/stats/data/2020_02_28.host1.12503/LoadDATA.sh), this import procedure uses socket authentication, so make sure there is no conflicting "~/.my.cnf".
As an example, my header for LoadDATA.sh will look like:

BatchLOAD=$1
BatchLOAD=${BatchLOAD:=/opt/packages/ADMIN/BatchLOAD}
BASE=$2
BASE=${BASE:=mortensi}
TITLE="20200228-mortensi"
HOST=$3
HOST=${HOST:=127.0.0.1}
Read more details about BatchLoad. Three important things to keep in mind to complete import of metrics:

Prepare the database
A single collection done with EasySTAT will be imported into a dedicated MySQL schema. This must be created in advance and configured in LoadDATA.sh. I decided to call my schema "mortensi" (BASE=${BASE:=mortensi}). This schema must come prepared with all the tables beneath, and must be created from dim_STAT preferences panel, choosing a name under "Create a New Database".

Enable Add-Ons
Upgrade patch delivers several add-ons that must be installed. To the purpose, from main menu, browse to "Add-On STAT(s)" and install all new add-ons at the bottom of the page. Only after this operation it is convenient to import metrics into the system, so that all collected data will be stored and used, otherwise data collected by a more recent agent (EasySTAT or STAT-service) won't be imported and simply discarded. Important note: add-ons must be enabled for every new database created in the previous step!

Troubleshoot errors
If you are trying this import procedure, for one reason or another you will fail, then it's handy to refer to the sql.error error log to understand what's gone wrong. It's generated under the install directory chosen.

It is now possible to start the import with LoadDATA.sh, which will log all the metrics imported, one by one:

sh LoadDATA.sh
***==================================================================
*** START dim_STAT BatchLOAD
*** Collect ID: 1 Host: goldgate Command: ADD
***==================================================================
* DTSET: 2020-02-28 20:52:11
* STAT: mysqlFILE_IO
* END [#359]
***==================================================================
*** END BatchLOAD
*** Collect ID: 1
***==================================================================

Generate reports

After all this effort, time to extract information from the data captured.

1. Go to preferences and choose the default database, where this capture metrics were stored (mortensi, in the example followed)
2. Go back to main menu and choose "Analyze". Documentation available here.

Choosing graphs to show, and also how many different metrics combinations, has a wide set of options to generate ad-hoc reports. For that, official online documentation and experimenting are the best ways to get the most out of this very interesting tool.

Manage InnoDB Cluster using MySQL Shell Extensions

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and functions under the same umbrella: the plugin.

As an example of the things that are possible, I have modified Rene's great example so to stop Group Replication in a shot from MySQL Shell, and it's particularly easy, check the following script.

1. Create directory  ~/.mysqlsh/plugins/ext/idc/
2. Create there init.js script as follows, it will be loaded at MySQL Shell startup.

// Get cluster object, only if session is created
function get_cluster(session, context) {
  if (session) {
    try {
      return dba.getCluster();
    } catch (err) {
      throw "A session to a cluster instance is required: " + err.message
    }
  } else {
    throw "A session must be established to execute this " + context
  }
}


function stop_cluster() {
  var cluster = get_cluster(shell.getSession(), "function");
  var data = cluster.status();
  var topology = data.defaultReplicaSet.topology;
  var sess = shell.getSession()
  var uri = sess.getUri()
  var user = (uri.split('//')[1]).split('@')[0]

  // iterate through members in the cluster
  for (index in topology) {
if (topology[index].status=="ONLINE")
print("\n-----> " + topology[index].address + " is ONLINE and will be evicted from GR\n")

var sess = shell.connect(user + "@" + topology[index].address)
var result = sess.runSql("STOP GROUP_REPLICATION;")
//print(JSON.stringify(result, null, 4))
  }

  // Reconnect original session
  shell.connect(uri)
  return;
}

// Creates the extension object
var idc = shell.createExtensionObject()

// Adds the desired functionality into it
shell.addExtensionObjectMember(idc, "stopCluster", stop_cluster, {
  brief: "Stops GR on all nodes, secondaries first, primary instance the last.",
  details: [
    "This function will stop GR on all nodes.",
    "The information is retrieved from the cluster topology."]});

// Register the extension object as a global object
shell.registerGlobal("idc", idc, {
  brief:"Utility functions for InnoDB Cluster."})


The script defines stop_cluster function that is invoked with idc.stopCluster() and:

1. Get the cluster object from the session (session against any member must be created beforehand)
2. Fetch topology from cluster object
3. Iterate through members belonging to the topology and get the address
4. For every member, establish a session using same session user (e.g. root or whatever, it is a prerequisite to administer a cluster with the same user)
5. Send command to stop Group Replication
6. After iterating through all members, reset the original session

The script also creates an extension object, registers it as global object and adds the function so it can be invoked as follows:

It is also possible to restart the cluster with the built-in dba global object, with function dba.rebootClusterFromCompleteOutage(); 

So in short, it is possible to start and stop the cluster with one command and from the same MySQL Shell session. This is only a quick skeleton (can be improved e.g. like stopping GR starting by secondary instances, and the primary at last) to connect to instances and do operations, there is no limit to the number of things that are possible. Read more on LeFred's blog here. 

MySQL Server Performance Tuning: The Perfect Scalability

When data and concurrency grow, together with queries complexity, a standard configuration does not fit anymore. This content walks through the ways MySQL has to adapt to an ever-increasing amount of data in context of tuning, under the heading “multiplication”:
—Scaling up through partitioning and how partitioning can help manage/archive data
—How to relocate binlog, undo, redo, tablespaces, or disk temporary tables and more on different mount points to take advantage of multiple storages and tackle I/O bottlenecks
—All improvements to parallel slave replication
—A quick OS approach, to verify swapping and affinity tuning take the most out of the machine.

All of this with different approaches to monitor the instance to spot what parts of the system and what queries need to be considered, mainly using:

—Sys Schema
—Global Status
—SHOW ENGINE INNODB STATUS

MySQL Performance Tuning: The Perfect Scalability (OOW2019) from Mirko Ortensi

I presented this content at last Oracle Open World 2019 as a Hands-On Lab.

How To Bulk Import Data Into InnoDB Cluster?

If you need to do bulk importing into InnoDB Cluster, it is certainly possible to do so by using any of:

• mysqldump logical dump
• tab separated dump, exported with SELECT..INTO OUTFILE statement or by using mysqldump with --tab option. And import with LOAD DATA..INFILE

Unfortunately both imports will add load to instances and to channels interconnecting instances: data imported on the primary instance needs to be replicated to the rest of instances. And the bigger the data to import, the higher the load (and this could end up affecting performance). The import operation could be batched to reduce load, and Group Replication allows at least to throttle workload with flow control or to split messages in several smaller messages with group_replication_communication_max_message_size option.

How to import data into InnoDB Cluster?

But in case data to import is a whole table (MySQL 8 adds also flexibility to swap partitions and tables, may become handy), or data can be first loaded into an InnoDB table, there's simple way to have an arbitrary amount of data pushed to InnoDB Cluster, and it takes advantage of tablespaces copying feature. I made a quick test to import a table.

I created the table t5 on an arbitrary instance and added a few rows. Then exported as in the instructions (does nothing but flush it and create an auxiliary .cnf file for definition validation at import time, not mandatory to use it but recommended):

FLUSH TABLES t5 FOR EXPORT;

On the InnoDB Cluster setup, I created the table t5 with same definition from the primary, then again on the primary:

ALTER TABLE t5 DISCARD TABLESPACE;

This will remove the t5.ibd tablespace on all the 3 instances. And with a simple SELECT, I made sure that this is as expected:

mysql> select * from test.t5;
ERROR 1814 (HY000): Tablespace has been discarded for table 't5'

After that, I copied t5.ibd from the former instance under the related schema folder in *each* GR node.
Let's check initial GTID set:

mysql> select @@GLOBAL.GTID_EXECUTED;
+------------------------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                                     |
+------------------------------------------------------------+
| 550fa9ee-a1f8-4b6d-9bfe-c03c12cd1c72:1-270:1000011-1000014 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

Then on the primary, did:

mysql> ALTER TABLE t5 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

I am lazy and did not perform validation using .cfg (more from the instructions):

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t5.cfg', will attempt to import without schema verification |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And all the tablespaces are loaded from local file system into the GR member. And new GTID set is:

mysql> select @@GLOBAL.GTID_EXECUTED;
+------------------------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                                     |
+------------------------------------------------------------+
| 550fa9ee-a1f8-4b6d-9bfe-c03c12cd1c72:1-271:1000011-1000014 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

Let's test it's all ok:

mysql> select * from test.t5;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|  777 |
+------+
8 rows in set (0.00 sec)

So data will be available on the rest of the nodes at no bandwidth and protocol cost, only this is indeed replicated, from binlog.

# at 2714
#190919  1:34:34 server id 1  end_log_pos 2821  Query   thread_id=34    exec_time=0     error_code=0
SET TIMESTAMP=1568849674/*!*/;
ALTER TABLE t5 IMPORT TABLESPACE
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Most important, I broke nothing!

mysql> SELECT MEMBER_ID, MEMBER_STATE FROM performance_schema.replication_group_members;
+--------------------------------------+--------------+
| MEMBER_ID | MEMBER_STATE |
+--------------------------------------+--------------+
| 43a67ea3-e1a0-11e7-8a9a-0021f66e910e | ONLINE |
| 45ab082d-e1a0-11e7-8b73-0021f66e910e | ONLINE |
| 48be28c0-e1a0-11e7-8c19-0021f66e910e | ONLINE |
+--------------------------------------+--------------+
3 rows in set (0.00 sec)


To wrap up, instead of loading GB or TB into InnoDB Cluster and have the cluster replicate massive amount of rows, this trick can push your data at no cost.
Comments are welcome!