MySQL has just released MySQL 8.0 DR (and yes, DR stands for Don’t Run-it-in-production-yet) so let’s jump right in and take a look at the hottest new features coming in this new release:

1. Persistent runtime configuration changes. Love it. From now on we’ll be able to use SET PERSIST innodb_buffer_pool_size = X; instead of SET GLOBAL innodb_buffer_pool_size = X; for the runtime changes to persist during a restart. It may not make much sense if you’re using a modern database that doesn’t even have a configuration file, but for us who lived with MySQL for over 20 years, this is huge!

How does it work? In a nutshell, these changes are saved in mysqld-auto.cnf file in MySQL data directory.

2. MySQL privilege tables are now InnoDB. I think this was …

We have been looking at the WebScaleSQL project with great excitement. As with any new enhancements to the MySQL world, we need to test extensively to ensure we can give PSCE customers the best advice possible. Since this project is source only, we decided to add WebScaleSQL builds to our repo, so we could examine the changes being introduced by all the different collaborators.

So what is WebscaleSQL?

WebScaleSQL is a collaboration among engineers from several companies that face the same challenges in deploying MySQL at scale, and seek greater performance from a database technology tailored for their needs.

— WebScaleSQL, Frequently Asked Questions

What makes this project so special, is the level of collaboration between some of the most …

As you probably already know, in MySQL 5.7.3 release, InnoDB Memcached reached a record of over 1 million QPS on a read only load. The overview of the benchmark and testing results can be seen in an earlier blog by Dimitri. In this blog, I will spend sometime on the detail changes we have made to achieve this number.

First thanks to Facebook's Yoshinori with his bug#70172 that brought our attention to this single commit read only load test. We have been focussing on operation with large batch size. This bug prompted us to do a series of optimization on single commit read only queries and these optimizations eliminate almost all major bottlenecks from the InnoDB Memcached plugin itself.

If you are just …

Introduction

InnoDB is a general-purpose storage engine that balances high reliability and high performance. It is a transactional storage engine and is fully ACID compliant, as would be expected from any relational database. The durability guarantee provided by InnoDB is made possible by the redo logs.

This article will provide an overview of the redo log subsystem or log subsystem of InnoDB. We will look at the following details:

• The global log system object, which provides access to important data structures and information.
• The mini-transaction (mtr), using which all redo log records are created.
• The global in-memory log buffer (or just log buffer), into which the redo logs are written to from the mini transaction buffer. This log buffer will be periodically flushed to the log file on disk. …

My MySQL bugs is a list I recently created and intend to keep up to date with issues I have seen.

FOREIGN KEYs in MySQL Cluster is a big step forward. It is now possible to run enterprise software with NDB Cluster as the storage backend. Over the years, the lack of FOREIGN KEYs have been one of the most limiting pieces of functionality. Who wants to fiddle with TRIGGERs or recode applications to enforce data integrity?
But finally, it is here. It is implemented natively at the Data Node level, where NDB stores its data. It is well known that FOREIGN KEYs come with an overhead. E.g., when writing a record into a child table, the existence must be checked in the parent table. Since data is distributed across multiple Data Nodes, the child record and parent record may be on different nodes or shards (Node Groups). Hence there is extra work to be done in terms of internal triggers and network communication, the latter being the more costly. The performance impact must be taken into account when doing …

As far as I know there isn't a well accepted set of best practices for MySQL, but there are many best practices known and used by most MySQL DBA's. One of those best practices is that the datadir must not be equal to a mountpoint; it has to be a subdirectory of a mountpoint. I learned this the hard way a few years ago when I used a NetApp Filer via NFS as a data directory. The NetApp filer exposed the snapshots via a .snapshot directory. A database in MySQL is a directory, so MySQL thought that the .snapshot directory was a database. This resulted in some issues with our monitoring scripts, so we had to create a mysql_data directory and move all the databases to that directory.

For other setups directories like lost+found, .zfs, etc. gave similar issues.

In MySQL 5.6 a new feature was introduced to make it possible to make some databases hidden. To do this a ignore-db-dir option needs to be specified for each directory. …

The recently released MySQL 5.6 gets a lot of attention, but for those who are still on 5.5 there is also good news: There are two new features in 5.5.

The first feature is that there are more INFORMATION_SCHEMA tables for InnoDB. This means that it's possible to 'see' what's in the buffer pool. It also makes it possible to get more information about the LRU list.

From the 5.5.28 changelog:
InnoDB: Certain information_schema tables originally introduced in MySQL 5.6 are now also available in MySQL 5.5 and MySQL 5.1: INNODB_BUFFER_PAGE, INNODB_BUFFER_PAGE_LRU, and INNODB_BUFFER_POOL_STATS. (Bug #13113026)

This is in the "Bugs Fixed" section instead of the "Functionality Added or Changed" section, which is a bit weird in my opinion.

The second feature is a variable which makes it …

A high-level comparative overview of the features

With the recent GA release of MySQL 5.6, there have been a lot of questions about where MariaDB stands with regards to MySQL 5.6.  SkySQL will of course support both as Patrik explained in his recent blog post, but there are many questions about the technical differences. Rasmus from Monty Program gave a detailed view on MariaDB 10.0 here but I thought it would be beneficial to share a comparison table of the two. MariaDB recently released a benchmark including various versions of both MariaDB and MySQL (as did DmitriK from Oracle), but this post will focus solely on features (not …

This article discusses about the approach taken by InnoDB Storage Engine of MySQL to provide the repeatable read isolation level.  First, an example is presented to demonstrate the two different designs that are possible.  Then the design used in InnoDB is presented followed by a short discussion about the advantages and disadvantages of this design choice.  As part of this discussion, we also present a performance optimization done in MySQL 5.6. 
An Example Scenario

I used MySQL 5.5 for this purpose.  Let us create the following tables t1 and t2 in the test database that is available by default. Even though the default storage engine in MySQL 5.5 is InnoDB, I explicitly specify it for clarity.  

mysql> use test;
mysql> create table t1 (f1 int) engine=innodb;
mysql> create …