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.
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 …
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.
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:
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, keynameFROM 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:
…
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 …
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 …
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, …
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 | …
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 …
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 …
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
— …
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
— …
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 …
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 …
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 (
-- …
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:
The resulting topology I’d like to setup is composed of 2 data …
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:
The resulting topology I'd like to setup is composed of 2 data nodes, 1 management node and …
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:
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 …
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:
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 …
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 …
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.