Following on from my earlier proposal to change Replication + InnoDB settings, in the MySQL team, we are proposing to make the following additional changes to defaults in MySQL 5.7:
| Setting | Old Default | New Default |
| binlog_error_action | IGNORE_ERROR | ABORT_SERVER |
| innodb_checksum_algorithm | … |
It struck me today; there is an easy way to describe MySQL's Binary Log group commit improvements from MySQL 5.0-5.7 by using the example of a single ferry trying to ship passengers from point A to point B:
MySQL 5.0 Behaviour
In MySQL 5.0, the ferry will pick up the next passenger in line from point A, and transfer them to point B. The trip between A and B takes about 10 minutes return trip, so it's possible that several new passengers will arrive while the ferry is in transit. That doesn't matter; when the ferry arrives back at point A, it will only pick up the very next passenger in line.
MySQL 5.6 Behaviour
In MySQL 5.6, the ferry will pick up all passengers from the line at point A, and then transfer them to point B. Each time it returns to point A to pick up new passengers, it will collect everyone who is …
[Read more]
Today I was doing some tests with XA transactions in MySQL
5.6.
The output of the XA RECOVER command to list transactions was
hard to read because of the representation of the data
column:
The good news is that 5.7 has transaction information in
performance_schema:
mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode[Read more]
-> from performance_schema.events_transactions_current;
+-----------------+-----------------+--------+--------+----------+-------------+
| trx_id | isolation_level | state | xid | xa_state | access_mode |
+-----------------+-----------------+--------+--------+----------+-------------+
| NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE |
| 421476507015704 | REPEATABLE READ | …
Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.
There is a solution for the second problem, the usability. It’s name is “sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.
Installation
If you are a MySQL Workbench user the installation is pretty easy because sys schema is already included. You just need to install it. Click on “Performance – Performance Reports” and there you will find the “Install Helper” button that will install sys schema. …
[Read more]Yesterday (Oct. 22) I gave a presentation titled “MySQL 5.6 Full Text Search Throwdown.” If you missed it, you can still register to view the recording and my slides.
Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:
Q: Does Solr automatically maintain its index against MySQL? Do you have to hit the Solr server with a specific query to keep the index ‘warm’?
There are several strategies for updating a Solr …
[Read more]Labs releases are intended to provide early access to new features. As Gopal notes in his recent blog post About the Data Dictionary Labs Release, there is a notable restriction where upgrading from any previous MySQL database version is not supported.
Today, I thought I would demonstrate how to get the data dictionary lab up and running on a fresh Ubuntu 14.04 installation:
# Download from labs.mysql.com wget http://downloads.mysql.com/snapshots/pb/mysql-5.7.5-labs-dd/mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz # extract it to /usr/local/mysql # more or less following instructions in INSTALL-BINARY groupadd mysql useradd -r -g mysql mysql tar -xzf mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz mv mysql-5.7.5-labs-dd-linux-el6-x86_64 /usr/local/ ln -s /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64 …[Read more]
A few months back, Michael wrote about reconnecting orphaned *.ibd files using MySQL 5.6. I will show you the same procedure, this time for partitioned tables. An InnoDB partition is also a self-contained tablespace in itself so you can use the same method described in the previous post.
To begin with, I have an example table with a few orphaned partitions and we will reconnect each partition one by one to the original table.
mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t1 G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
[...]
KEY `h_date` (`h_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(h_date))
(PARTITION p0 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION …[Read more]
MySQL 5.7.5 was recently released (it is the latest MySQL 5.7, and is the “m15″ or “Milestone 15″ release), and is available for download here and here.
As for the fixes/changes, there are quite a few (the official release was split into 3 separate emails), which is expected in such an early milestone release.
The main highlights for me were (though the enhancements, and potentially impactful changes, are definitely not limited to this list):
Introduction
Evaluating the performance of database systems is a very demanding task. There are a lot of hard choices to be made, e.g.:
While performance evaluation is mostly machine time, there is still a lot of hard work for the human monitoring the tests. In this blog post we have made following choices:
In a previous post, I covered porting MySQL 5.6 to POWER and subsequently, some new record performance numbers with MySQL 5.6.17 on POWER8.
Well, those following at home will be aware that not only is the next sentence sponsored by IBM Legal, but that MySQL 5.7 alleviates a bunch of the mutex contention that we saw with MySQL 5.6. The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.
In looking at MySQL performance on POWER, it’s inevitable that I should look at MySQL 5.7 and what’s coming up in the next stable release of MySQL.
Surprisingly, a bunch of the core code in InnoDB and MySQL dealing with mutexes has changed in MySQL 5.7 when …
[Read more]