Tweet

This post was originally published on the MySQL Support Team Blog at https://blogs.oracle.com/mysqlsupport/entry/innodb_locks_analysis_why_is on 14 April 2017.

Consider the scenario that you execute a query. You expect it to be fast – typically subsecond – but now it take not return until after 50 seconds (innodb_lock_wait_timeout seconds) and then it returns with an error:

mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You continue to investigate the issue using the sys.innodb_lock_waits view or the underlying Information Schema tables (INNODB_TRX, …

Yes, another post about my talks at Percona Live Santa Clara: I obviously still have things to share.  This time, I will focus on my parallel replication talks by giving a short preview.

I have two parallel replication talks at Percona Live:

MySQL/MariaDB Parallel Replication: inventory, use cases and limitations (Wednesday talk) MySQL Parallel Replication: all the 5.7 (and some of the 8.0)

In a previous post, I listed all the Booking.com talks at Percona Live.  In this post, I will give more details about my talks.

As a reminder, the list of my talks is the following:

Monitoring Booking.com without looking at MySQL (Thursday keynote) The two little bugs that almost brought down Booking.com (Tuesday Lightning Talk) MySQL/MariaDB Parallel Replication: inventory, use cases and

In a week, me and some Booking.com colleagues will be in Santa Clara for Percona Live.

Booking.com is sponsoring the conference and we will be present at the Monday Evening Reception.  You do not need a tutorial pass to attend the dinner (even if it is on the tutorial day): any valid pass will do.  If you do not have your ticket yet, it is time to register (you can use the discount code “

One of the interesting features introduced in MySQL 5.7 is that innodb_buffer_pool_size is a dynamic variable (since 5.7.5, to be more specific). Yet, past experience tells us that just because a variable is dynamic, it does not make it is safe to change it on the fly.

To find out how safe this new feature is, I measured throughput on a synthetic workload (sysbench 1.0 running the oltp script) as I made changes to this variable. In this post, I will show the results that came through.

The Environment

For my tests, I used a Google Cloud Compute instance of type n1-standard-4 (that is 4 vCPUs and 15 GB of memory) with 25 GB of persistent …

In this blog we are going to explore about innodb tablespace encryption technique, which will be useful for securing data.

In MySQL 5.7, a new feature “Innodb Tablespace Encryption“has been added to protect the data at rest. This is a most awaited feature in security. This encryption supports all file per table tablespaces and it will not  support shared tablespace. This encryption technique works on the basis of rotating  key files. There are two types of keyring plugins available for the key management and they are given below

• keyring_file plugin – Available in all MySQL versions.

• keyring_okv plugin – Available in MySQL Enterprise Edition.

Architecture:

Innodb tablespace encryption uses two tier encryption architecture, in which it has master encryption key …

MySQL 5.7 introduced the LOGICAL_CLOCK type of multi-threaded slave (MTS).  When using this type of parallel replication (and when slave_parallel_workers is greater than zero), slaves use information from the binary logs (written by the master) to run transactions in parallel.  However, enabling parallel replication on slaves might not be enough to get a higher replication throughput (VividCortex

In my previous post, I talked about a funny replication breakage that I experienced with MariaDB.  So what about different versions of MySQL... > SELECT version(); +------------+ | version() | +------------+ | 5.6.35-log | +------------+ 1 row in set (0.00 sec) > SELECT * FROM test_jfg; +----+--------+-------------+ | id | status

This blog post discusses the ramifications of STRICT mode in MySQL 5.7.

In short

By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the

SQL_MODE

 to

NO_ENGINE_SUBSTITUTION

 (same as in MySQL 5.6):

mysql> set global SQL_MODE="NO_ENGINE_SUBSTITUTION";

MySQL 5.7, dates and default values

The default

SQL_MODE

 in MySQL 5.7 is:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

That makes MySQL operate in “strict” mode for transactional tables.

“Strict mode …

The Theory

Generated Columns is a feature released on MySQL 5.7. They can be used during CREATE TABLE or ALTER TABLE statements. It is a way of storing data without actually sending it through the INSERT or UPDATE clauses in SQL. The database resolves what the data will be.

There are two types of Generated Columns: Virtual and Stored. They work with:

• mathematical expressions (product_price * quantity)
• built-in functions (RIGHT(), CONCAT(), FROM_UNIXTIME(), JSON_EXTRACT())
• literals (“2”, “new”, 0)

Besides that, they can be indexed but they don’t allow …