MySQL Support Blogs

This is an aggregated feed of various blogs of Oracle support staff who work and support MySQL customers and users. There is an RSS feed to which you can subscribe.

Manage MySQL Database Service (MDS) DB Systems with OCI CLI

OCI CLI is a powerful tool that does it all for OCI administration: from creating a compute instance to configuring a Virtual Cloud Network (VCN), from setting up Identity Management Service (IAM) to managing all the different storage flavours. Consult the OCI CLI command reference.

MySQL Database Service (MDS) is not an exception, everything related to the new MySQL Server service running over OCI can be easily managed with a set of commands using OCI CLI, check it …


[Read More]

Geo-Redundancy for MySQL InnoDB And NDB Clusters

MySQL highly available solutions, InnoDB Cluster (it uses InnoDB storage engine and is based on Group Replication plugin) and NDB Cluster (NDB storage engine), offer high scalability and redundant topologies.


[Read More]

Configure a MySQL Database Service (MDS) DB System

It’s now more than two months since MDS, the managed MySQL Server solution by Oracle MySQL team, hosted on Oracle Cloud Infrastructure (OCI), became available in several regions on the 1st of September. You can find online several resources online, such as:


[Read More]

JSON in MySQL: Keys which do NOT match a value (the more elegant way)

Given a JSON object:
SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';

How do you find the keys which do NOT contain a value of 0?

A second approach to finding the non-0 values from a JSON object is to turn the keys and values into separate columns of a table. This is cleaner, but a little wordier.

We’ll get the keys in one table:

SELECT ord, keyname 
FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (
    ord FOR ORDINALITY,
    keyname VARCHAR(100) PATH '$')
) AS keyTable;
 +------+----------+
 | ord  | keyname  |
 +------+----------+
 |    1 | RETRO    |
 |    2 | BOOSTER  |
 |    3 | SURGEON  |
 |    4 | GUIDANCE |
 |    5 | RECOVERY |
 +------+----------+

And the values in a second table:


[Read More]

JSON in MySQL: Keys which do NOT match a value

Given a JSON object:
SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';

How do you find the keys which do NOT contain a value of 0?

One approach is to find the keys which do match 0, and then remove them.

Sounds like JSON_SEARCH and JSON_REMOVE? But there’s a gotcha: JSON_SEARCH works only with strings. Ok, so we REPLACE(@j, ‘0’, ‘”0″‘) – but that doesn’t help, because JSON_REMOVE can’t accept an array of paths like JSON_SEARCH would return; it requires each path as a separate parameter.

Instead, JSON_MERGE_PATCH will suffice. It has the feature that values from the second parameter are preserved only if they are not NULL. So, the approach becomes turning the 0s into NULLs, and then merging that document into an empty one.

SELECT REPLACE(@j, 0, 'null') AS j; -- case matters! null, not NULL …


[Read More]

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 solutions on the market, and MySQL offers its own 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 …


[Read More]

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, …


[Read More]

Aggregate JSON arrays

Given: a table with JSON arrays

CREATE TABLE t (id int auto_increment primary key, d json);
INSERT INTO t VALUES (1, '["apple", "apple", "orange"]');
INSERT INTO t VALUES (2, '["apple", "banana", "orange", "orange", "orange"]');

The desired output is each row with a count of the unique objects:

+------+----------------------------------------+
| id   | fruitCount                             |
+------+----------------------------------------+
|    1 | {"apple": 2, "orange": 1}              |
|    2 | {"apple": 1, "banana": 1, "orange": 3} |
+------+----------------------------------------+

JSON_TABLE() can transform the array into rows.

SELECT id, fruit
FROM t,
JSON_TABLE(d,
    "$[*]" COLUMNS (
        fruit VARCHAR(100) PATH "$"
    )
) AS dt;

+----+--------+
| id | fruit  |
+----+--------+
|  1 | apple  |
|  1 | apple  |
|  1 | orange |
|  2 | apple  |
|  2 | banana |
|  2 | orange |
|  2 | orange |
|  2 | orange | …


[Read More]

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 …


[Read More]

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 …


[Read More]

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
— …


[Read More]

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
— …


[Read More]

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:

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 …


[Read More]

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:


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 …


[Read More]

Window Functions with Unusual Boundaries

Somebody on Freenode wanted this:

   Source            Result
+----+------+    +----+------+
| id | x    |    | id | c    |
+----+------+    +----+------+
|  1 |    1 |    |  1 |    2 |
|  2 |    1 |    |  2 |    2 |
|  3 | NULL |    |  3 | NULL |
|  4 | NULL | -> |  4 | NULL |
|  5 |    1 |    |  5 |    1 |
|  6 | NULL |    |  6 | NULL |
|  7 |    1 |    |  7 |    3 |
|  9 |    1 |    |  9 |    3 |
| 10 |    1 |    | 10 |    3 |
+----+------+    +----+------+

The result uses the NULL values in x as boundaries of windows, and counts the number of rows within each window. I don’t know why anyone wants such a thing; it is not ours to reason why…

Anyway, the point is that you can use arbitrary expressions, even subqueries, to define your window partitions.

SELECT 
    id, 
    -- Count of rows in windows bound by NULL values in x
    IF(
      x IS NULL, 
      NULL, 
      COUNT(*) OVER (PARTITION BY (
        -- …


[Read More]

High Availability with MySQL Cluster, Setup From Command Line (II)

In the first delivery of this series of posts, delivered for whom who are interested to understand the basics of MySQL Cluster “by examples”, I wrote about installing MySQL Cluster with a python utility called ndb_setup-py, which offers a nice web graphical interface to define and start our brand new cluster.

In this post I will share an example to do everything from scratch and manually, instead. Doing things manually is always the best recommendation to learn everything about processes life cycle through their:

  • Initialization
  • Administration (start/stop/reconfigure)
  • Monitoring (logs/counters/status)
  • Troubleshooting 

The resulting topology I’d like to setup is composed of 2 data …


[Read More]

High Availability with MySQL Cluster, Setup From Command Line (II)

In the first delivery of this series of posts, delivered for whom who are interested to understand the basics of MySQL Cluster "by examples", I wrote about installing MySQL Cluster with a python utility called ndb_setup-py, which offers a nice web graphical interface to define and start our brand new cluster.

In this post I will share an example to do everything from scratch and manually, instead. Doing things manually is always the best recommendation to learn everything about processes life cycle through their:

  • Initialization
  • Administration (start/stop/reconfigure)
  • Monitoring (logs/counters/status)
  • Troubleshooting 

The resulting topology I'd like to setup is composed of 2 data nodes, 1 management node and …


[Read More]

Top 5 Things to Consider to Get Started with MySQL Performance Tuning

Today I’ll share a few slides I prepared last year for a presentation delivered at Oracle Open World. This is a quick and easy hands-on lab for fresh MySQL Server DBAs. I chose 5 among the most relevant topics when tuning and scaling a MySQL Server using InnoDB tables.

In particular, in this hands-on, I will talk about:

  • Scaling connections
  • The threads model
  • InnoDB REDO log
  • InnoDB Buffer Pool
  • The Execution plan

MySQL Performance Tuning 101 from Mirko Ortensi

Rate at which MySQL is delivering new features and improvements is impressive, in fact MySQL Server 8 boosts performance in many fields, especially …


[Read More]

Top 5 Things to Consider to Get Started with MySQL Performance Tuning

Today I'll share a few slides I prepared last year for a presentation delivered at Oracle Open World. This is a quick and easy hands-on lab for fresh MySQL Server DBAs. I chose 5 among the most relevant topics when tuning and scaling a MySQL Server using InnoDB tables.

In particular, in this hands-on, I will talk about:

  • Scaling connections
  • The threads model
  • InnoDB REDO log
  • InnoDB Buffer Pool
  • The Execution plan



MySQL Performance Tuning 101 from Mirko Ortensi

Rate at which MySQL is delivering new features and improvements is impressive, in fact MySQL Server 8 boosts performance in many fields, especially regarding InnoDB REDO logging. Hence while the rest of …


[Read More]

High Availability with MySQL Cluster, a Quick How-To Guide for Dummies (I)

I have been playing with MySQL Cluster for some years now, and today I'd like to start writing a bit about it, how to set it up, configure, backup and also how to use it, as there's plenty of ways to drive operations towards the Cluster for brutal speed  and concurrency. MySQL Cluster, the open source in memory database from Oracle MySQL, is available for free from MySQL Cluster download page (Community version has GPL license).
But before starting with an overview of installation and setup, if you're new to MySQL Cluster, I would strongly recommend to have a look at this video.



What I find more interesting about MySQL Cluster, is that it is possible to have a setup running on commodity hardware (the bare laptop), as it can be configured to have a minimum footprint in terms of memory and storage requirements. About high availability, MySQL Cluster offers unique …


[Read More]


Content reproduced on this page is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.