The latest from the MySQL community

MySQL InnoDB Cluster consists of 3 components:

• MySQL Group Replication (a group of database server which replicates to each other with fault tolerance).
• MySQL Router (query router to the healthy database nodes)
• MySQL Shell (helper, client, configuration tool)

In the first part of this walkthrough, we are going to deploy a MySQL InnoDB Cluster. There are a number of hands-on tutorial available online but this walkthrough covers all the necessary steps/commands to install and run the cluster in one place. We will be covering monitoring, management and scaling operations as well as some gotchas when dealing with MySQL InnoDB Cluster in the second part of this blog post.

The following diagram illustrates our post-deployment architecture:

We are going to deploy a total of 4 nodes; A three-node MySQL Group Replication and one MySQL router node co-located within the application …

MySQL Cluster is an open-source distributed in-memory database. It combines linear scalability with high availability, providing in-memory real-time access with transactional consistency across partitioned and distributed datasets. It was developed to support scenarios requiring high-availability (99.999% or more) and predictable query time. Testing such a system is achieved via many interconnected pieces ranging from a large set of automated tests and manual exploratory testing. This post explores an overview of the testing methodologies we use and the current challenges we face.

Gaming, banking, telcos, and online services all are powered by fully-redundant and fault-tolerant software systems. At the heart of those systems, you can find MySQL Cluster — a distributed in-memory database having a minimum of five-9s availability (around 5 minutes a year). This open-source database provides nearly-linear …

From time to time you may have experienced that MySQL was not able to find the best execution plan for a query. You felt the query should have been faster. You felt that something didn’t work, but you didn’t realize exactly what.

Maybe some of you did tests and discovered there was a better execution plan that MySQL wasn’t able to find (forcing the order of the tables with STRAIGHT_JOIN for example).

In this article, we’ll see a new interesting feature available on MySQL 8.0 as well as Percona Server for MySQL 8.0: the histogram-based statistics.

Today, we’ll see what a histogram is, how you can create and manage it, and how MySQL’s optimizer can use it.

Just for completeness, histogram statistics have been available on MariaDB since version 10.0.2, with a slightly different …

Introduction In this article, we are going to see how a deadlock can occur in a relational database system, and how Oracle, SQL Server, PostgreSQL, or MySQL recover from a deadlock situation. Database locking Relational database systems use various locks to guarantee transaction ACID properties. For instance, no matter what relational database system you are using, locks will always be acquired when modifying (e.g., UPDATE or DELETE) a certain table record. Without locking a row that was modified by a currently running transaction, Atomicity would be compromised. Using locking for controlling access... Read More

The post A beginner’s guide to database deadlock appeared first on Vlad Mihalcea.

This article is an update of a previous post explaining how to setup a second cluster on a second data center to be used as disaster recovery (or to run some off site queries, like long reports, etc..).

This new article covers also the CLONE plugin. Before you ask, CLONE plugin and Replication Channel Based Filters are only available in MySQL 8.0 ! It’s time to upgrade, MySQL 8 is Great !

Also, for DR only, a single MySQL instance acting as asynchronous replica is enough. But if for any reason you want to also have a HA cluster in the second data …

MySQL introduced a JSON data type in version 5.7, and expanded the functionality in version 8.0.

Besides being able to store native JSON in MySQL, you can also use MySQL as a document store (doc store) to store JSON documents. And, you can use NoSQL CRUD (create, read, update and …

ProxySQL 2.0.7, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.7 which introduces many new features and enhancements and also …

InnoDB Page Compression Explained 

We have several customers with multi-terabyte database infrastructure on SSDs, The SSDs are great investment for performance but they are also expensive with shorter lifespans so storage efficiency management is something we are very cautious about on SSDs, At MinervaDB Labs we spend considerable amount of time doing research on InnoDB page compressions benefits and most common mistakes. The compressed tables were first introduced in 2008  with InnoDB plugin for MySQL 5.1 . Facebook has been a major committer to this project and most of it were later implemented in upstream MySQL code as well. We can implement compression in InnoDB is two ways, Either by using Barracuda InnoDB file format or ROW_FORMAT=COMPRESSED

How InnoDB page compression works ?

When a page is …

Dear MySQL users,

The MySQL Windows Experience Team is proud to announce the release
of MySQL for Visual Studio 1.2.9.

This is a maintenance release for 1.2.x. It can be used for
production environments.

MySQL for Visual Studio is a product that includes all of the
Visual Studio integration functionality to create and manage
MySQL databases when developing .NET applications.

MySQL for Visual Studio is installed using the MySQL Installer
for Windows which comes in 2 versions:
   * Full (415.1 MB) which includes a complete set of MySQL products
     with their binaries included in the downloaded bundle.
   * Web (18.6 MB – a network install) which will just pull MySQL
     for Visual Studio over the web and install it when run.

You can download MySQL …

Its not the new term for DBAs. MySQL has an awesome parameter innodb-file-per-tables allows MySQL to create separate files for each tables. This helped a lot to manage the disk space in more efficient way. But when we perform a large batch job for delete or update the data in MySQL tables, you may face this fragmentation issue. Comparing with SQL server, MySQL’s fragmentation is not high. I had a similar situation where my Disk space was consuming 80% and when I check the huge files in OS, one table’s idb file consumed 300GB+. I know it has some wasted blocks(but not actually wasted, MySQL will use this space, it’ll not return this to OS) Then I checked the information schema to find out the data size and its index size. It was 27GB only. Then I realize, we did a batch operation to delete many billions of records in that table.

Thanks to Rolando - MySQL DBA:

When I searched the similar …