MySQL has had a JSON data type since version 5.7 was released way back in '15. But did you know you could produce JSON output from non-JSON columns? It is very simple and saves a lot of time over trying to format it in your application.

World DatabaseWe will be using the good old World database that MySQL has used for years in documentation, examples, and in the classroom. Starting with a simple query we will build up to something more complex.

SELECT Name, District, Population FROM City;

This will output the data from the table in a tabular format.

'Kabul', 'Kabol', '1780000'
'Qandahar', 'Qandahar', '237500'

Array or Object?We have two options for composing JSON data: JSON_ARRAY and JSON_OBJECT.

Of the two, you will find JSON_ARRAY the least fussy. It will JSON-ize your data very easily. It takes a list of values or an empty list and returns a JSON array.

Why do we sometimes want to keep duplicate indexes?

I’ve done dutiful DBA work in the past to identify and remove what are commonly called duplicate indexes. That is, those indexes that look like (a) and (a,b). The thought is that a query will utilize an index as easily on (a) as on (a,b), and removing (a) will save storage cost and write performance. I’ve had the experience, though, of removing (a) and seeing performance tank.

(As an aside, these are really redundant indexes. A duplicate index would be (a,b) and (a,b) by two different names – this can commonly be done by object relational mapping (ORM) or other automated schema creation tools. I’ll call (a) and (a,b) redundant indexes below.)

This test is on Percona Server 5.7.14 with the sys schema installed and performance schema enabled.

Given two tables with the same number of rows and …

Related resources  Sharding MySQL with MySQL Fabric and ProxySQL  MySQL Load Balancing with ProxySQL - an Overview  How to set up read-write split in Galera Cluster using ProxySQL  How ProxySQL adds Failover and Query Control to your MySQL Replication Setup  Two Database Sharding Tools for MySQL

Having too large a (write) workload on a master is dangerous. If the master collapses and a …

Related resources  Sharding MySQL with MySQL Fabric and ProxySQL  MySQL Load Balancing with ProxySQL - an Overview  How to set up read-write split in Galera Cluster using ProxySQL  How ProxySQL adds Failover and Query Control to your MySQL Replication Setup  Two Database Sharding Tools for MySQL

Having too large a (write) workload on a master is dangerous. If the master collapses and a …

We’ve been having a problem with MySQL replication at VividCortex. Replicas periodically tend to fall behind and we couldn’t really figure out how to speed things up. It wasn’t about resources. The replicas have plenty of CPU and I/O available. We’re also using multithreaded replication (a.k.a. MTR) but most of the replication threads were idle.

One thing that we decided to try out was the new LOGICAL_CLOCK parallelization policy introduced in MySQL 5.7.2. Here’s what the MySQL reference manual says about slave-parallel-type:

When using a multi-threaded slave (slave_parallel_workers is greater than 0), this option specifies the policy used to decide which transactions are allowed to execute in …

Today’s blog post is related again to MySQL Group Replication.

By default MySQL Group Replication runs in Single-Primary mode. And it’s the best option and the option you should use.

But sometimes it might happen that in very specific cases you would like to run you MGR Cluster in Multi-Primary mode: writing simultaneously on all the nodes member of the Group.

It’s of course feasible but you need to make some extra verification as not all workload are compatible with this behavior of the cluster.

Requirements

The requirements are the same as those for using MGR in Single-Primary mode:

• InnoDB Storage Engine
• Primary Keys
• IPv4 Network
• Binary Log Active
• Slave Updates Logged
• Binary …

This post discusses enabling and disabling jemalloc on Percona Server for MySQL.

The benefits of jemalloc versus glibc for use with MySQL have been widely discussed. With jemalloc (along with Transparent Huge Pages disabled) you have less memory fragmentation, and thus more efficient resource management of the available server memory.

For standard installations of Percona Server 5.6+ (releases starting with 5.6.19-67.0), the only thing needed to …

Hi all,

Fosdem’s organization relocated the MySQL & Friends Devroom from H.1308 to H.1309 (Van Rijn).

It’s the same building, just the door next to it.

MySQL will also have a stand in the H building. Don’t hesitate to visit it (I’ve heard there will be some Sakila Dolphins !)

Related resources  Schema Changes for MySQL Replication & Galera Cluster  Database Cluster Management - Manual vs Automation via ClusterControl  Migrating to MySQL 5.7 - The Database Upgrade Guide

In the previous blog post, we discussed how gh-ost works internally and how it compares to Percona’s pt-online-schema-change. Today we’d like to focus on operations - how can we test a schema change with gh-ost to verify it …

Related resources  Schema Changes for MySQL Replication & Galera Cluster  Database Cluster Management - Manual vs Automation via ClusterControl  Migrating to MySQL 5.7 - The Database Upgrade Guide

In the previous blog post, we discussed how gh-ost works internally and how it compares to Percona’s pt-online-schema-change. Today we’d like to focus on operations - how can we test a schema change with gh-ost to verify it …