Hi all, a few months have passed since our first preview release of MySQL Group Replication. Now is the time for the second preview release of MySQL Group Replication, the plugin that brings multi-master update everywhere to MySQL, as described in Hello World post.

Introduced changes User interface changes

After receiving plenty of feedback and continuing to pursue a more integrated look and feel with MySQL, we have given the user interface a facelift. Some of the changes are:

• The plugin has been renamed to group_replication, and as a consequence the plugin’s option names were also renamed to group_replication_*;
• Start/stop command: now the commands are START/STOP GROUP_REPLICATION;
• Performance_schema tables were improved to have better names, fields and relationships. …

In MySQL 5.7.6, we added a new feature called Generated Columns. In the initial work all Generated Columns were materialized, even virtual ones. This not only resulted in unnecessary disk space being used and disk I/O being done, but it also meant that any table alteration required that the full table be rebuilt. In the new MySQL 5.7.7 JSON Lab release, we have resolved all of these issues by implementing new features that not only allow users to create non-materialized virtual …

When storing XML documents in a BLOB or TEXT column there was no way to create indexes on individual XML elements or attributes. With the new auto generated columns in MySQL 5.7 (1st Release Candidate available now!) this has changed! Let me give you an example. Let's work on the following table:

 mysql> SELECT * FROM country\G  
 *************************** 1. row ***************************  
 docid: 1  
  doc: <country>  
     <name>Germany</name>  
     <population>82164700</population>  
     <surface>357022.00</surface>  
     <city name="Berlin"><population></population></city>  
     <city …

The MySQL Development team is very happy to announce that MySQL 5.7.7, the first 5.7 Release Candidate (RC1), is now available for download at dev.mysql.com (use the “Development Releases” tab). You can find the full list of changes and bug fixes in the 5.7.7 release notes. Here are some highlights. Enjoy!

SYS Schema

Include SYS Schema in MySQL 5.7 (WL#8159) — This work by Mark Leith integrates the SYS Schema (formerly ps_helper) within the MySQL Server by default. The MySQL SYS schema project has …

Last week, we introduced VividCortex support for Redis. One of the reasons we expanded our product capabilities is because of our own use of Redis. According to Baron, “We rely on Redis to help analyze the massive amounts of time-series data we receive from agents running on customer systems.” Let’s go a little deeper…

VividCortex keeps tables with all the known metrics and queries for many hosts measured at 1-second granularity. We use Redis to avoid repeating the “insert on duplicate update” into the metric/query tables. We are able to do this by keeping hourly buckets of seen queries and metrics. When a batch of metrics/queries arrive, before insert/updating them in the mysql tables, we check that we haven’t …

Last time we set up a High Availability server farm with MySQL Fabric. Now it is time to set up sharding. I will be using the good old World database and sharding the City table on the ID field. There are 4,079 cities in this table and they will be split in two. So one shard, that we will call CityLow will have the records 2,000 and below and the other records at 2,001 and above will be called CityHigh. We also need a global group for setting up sharding that will be called CityGlobal.

Sadly, the first step is to remove the previous setup with mysqlfabric manage teardown. This will remove the fabric database from the Fabric controller. Fabric itself has to be stopped with mysqlfabric manage stop. The command mysqlfabric manage setup will set up a fresh, clean fabric database. And …

Two years ago Ovais Tariq had explained in detail what kinds of problems existed before MySQL introduced metadata locks in 5.5.3 and how these locks help to prevent them. Still, some implications of metadata locking in MySQL remain unclear for users – DBAs and even software developers that target recent MySQL versions. I’ve decided to include a slide or two into the presentation about InnoDB locks and deadlocks I plan to make (with my colleague Nilnandan Joshi) on April 16 at Percona Live 2015.

I decided to do this as …

MySQL Enterprise Firewall is a commercial extension, and is included with MySQL Enterprise Edition. This new SQL based firewall was just released in the MySQL 5.6.24 Enterprise Server! Let me tell you a bit more about how it reduces security vulnerabilities, how it works, and how to use it.

All too often, as statistics and daily headlines show, badly written applications continue to expose an organizations sensitive data to malicious attackers. These …

Having big tables is one of the expected database problems, especially, for the fast growing database systems. In fact, big tables itself is not a problem but with big tables, the following problems are strongly expected:

1. Retrieving data from big tables is so slow.
2. It is a very hard job to maintain those tables like adding/removing an index, adding/dropping/modifying a column, … etc.
3. System resources, especially, the IO system might not be able to handle such huge traffic of writes and reads.
4. When it comes to the reporting queries, it might be a horrible nightmare!
5. Always cause disk space problem.

All the above problems will show up the need for scaling! So, let’s check out what are the possible solutions for that problem:

• MySQL Partitioning: Is a good solution but we will still face disk space and server resources problems. …

On a recent engagement I worked with a customer who makes extensive use of UUID() values for their Primary Key and stores it as char(36), and their row count on this example table has grown to over 1 billion rows.

The table is INSERT-only (no UPDATEs or DELETEs), and the bulk of their retrieval are PK lookups. Lookups by PK were performing acceptably, but they were concerned with the space usage by the table as we were approaching 1TB (running with innodb_file_per_table=1 and Percona Server 5.5).

This schema model presents an increasing burden for backups since they use Percona XtraBackup, and so the question was asked: does their choice of an effectively random Primary Key based on UUID() impact their on-disk storage, and to what extent? And as a neat trick I show towards the end of this post how you can calculate the …