MySQL is the most widely used  Relational Database Management System in the open source world. MySQL stepped into the NoSQL world by introducing the JSON Data Type in MySQL 5.7 release. In this blog post I am going to explain one of the major advantage of optimisation made in JSON Replication .

This was done from the MySQL 8.0.3 release.

What happened before 8.0.3 ?

Before MySQL 8.0.3, Whatever changes (Updates) made in JSON document, it will log as a full document in binary log & replicate the same into slave. The JSON data is stored as a blob internally. Below is an example of how it is logged as full document in binary log ?

Example –

Server version - 5.7.22-log MySQL Community Server (GPL)

My Binlog …

What does Anton Ego – a fictional restaurant critic from the Pixar movie Ratatouille – have to do with MySQL 8.0 GA?

When it comes to being a software critic, a lot.

In many ways, the work of a software critic is easy. We risk very little and thrive on negative criticism, which is fun to read and write.

But what about those who give their many hours of code development, and those who have tested such code before release? How about the many people behind the scenes who brought together packaging, documentation, multiple hours of design, marketing, online resources and more?

And all of that, I might add, is open source! Free for the world to take, copy, adapt and even incorporate in full or in part into their own open development.

It is in exactly that area that the …

MySQL 8.0 has a rich set of features. One of the feature which interests DBA’s more is invisible index

What is an index in MySQL ?

• The indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

What is invisible index ?

• The invisible index is ability to mark an index as unavailable for use by optimizer.
• In MySQL 5.7 and other previous versions the indexes are visible by a default.
• To control the index visibility for a new index ,use a  visible or invisible key words as a part of the index creation.

How to add a invisible index on existing table ?

Syntax :

It’s hard to believe Percona Live 2018 starts on Monday! We’re looking forward to seeing everyone in Santa Clara next week! Here are some quick highlights to remember:

• In addition to all the amazing sessions and keynotes we’ve announced, we’ll be hosting the MySQL Community Awards and the Lightning Talks on Monday during the Opening Reception.
• We’ve also got a great lineup of demos in the exhibit hall all day Tuesday and Wednesday – be sure to stop by and learn more about open source database products and tools.
• On Monday, we …

The Madrid MySQL Users Group has been a bit quiet recently so we thought it would be good to have a meeting again. The next meeting will take place next on Wednesday, 25th April and will cover Galera Cluster (Presentation by Emanuele Mottola).  We will also talk about MySQL 8.0 and any latest updates that … Continue reading MMUG19: Multi-instances with Galera Cluster and MySQL 8.0 on 25th April in Madrid

The post MMUG19: Multi-instances with Galera Cluster and MySQL 8.0 on 25th April in Madrid first appeared on Simon J Mudd's Blog.

MySQL 8.0.11 seems to be around the corner and the new MySQL Shell will take advantage of all the new improvements made in MySQL 8.0 like SET PERSIST, RESTART, … see this previous post.

In the video below, I show you how easy it’s to deploy a MySQL InnoDB Cluster using the Shell that connects remotely to all the instances:

…

If you are interested in MySQL Performance and you are looking for an optimal storage, you should have a look at this presentation by Frank Ober where you will be able to see how MySQL behaves on Intel Optane SSDs:

This session was recorded during the Silicon Valley MySQL Group meetup of last March 28th.

You will also find an overview of the tools you can use to test your environment.

Thank you to Intel for testing MySQL 8.0 and having collaborated with Dimitri.

Dimitri will also …

At the end we all agree that what really matters is the Query Response Time, isn’t it ?

MySQL 8.0 engineers have added more visibility in the summary tables as explained in this previous post. But to add more details, now the Performance_Schema also collects the statements latency and provide their distribution thanks to the collected histogram data.

Two tables have been added to Performance_Schema:

• events_statements_histogram_by_digest: details about latency related to schema and query digest
• events_statements_histogram_global: global latency summary across all schemas and queries

Let’s have a look at what we can see: …

Recently on MySQL Forums, somebody was looking for documentation or procedure to upgrade a MySQL InnoDB Cluster (or Group Replication cluster) to a newer version.

In this post I am illustrating the best practices to achieve this task safely.

To illustrate the procedure, I will use an InnoDB Cluster of 3 members: mysql1, mysql2 and mysql3. The cluster is setup in Single-Primary mode (mysql1) and runs MySQL 5.7.21.

Let’s have a look at the cluster status:

 MySQL / mysql1:3306 / JS / cluster.status()
{
    "clusterName": "MyCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql1:3306", 
        "ssl": "DISABLED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to …

In this post, we’ll look at the MySQL 8.0 innodb_dedicated_server variable.

MySQL 8.0 introduces a new variable called innodb_dedicated_server. When enabled, it auto tunes innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method at startup (if these variables are not explicitly defined in my.cnf).

The new MySQL 8.0 variable automatically sizes the following variables based on the RAM size of the system:

innodb_buffer_pool_size:

• • <1G: 128M(default value if innodb_dedicated_server is OFF)
  • <=4G: Detected Physical RAM * 0.5
  • >4G: Detected Physical RAM * 0.75

innodb_log_file_size: …