Chris CalenderMySQL 5.7.5 Overview and Highlights (8.10.2014, 00:12 UTC)

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):

  • InnoDB: The innodb_buffer_pool_size parameter is now dynamic, allowing you to resize the buffer pool without restarting the server. The resizing operation, which involves moving pages to a new location in memory, is performed chunks. Chunk size is configurable using the new innodb_buffer_pool_chunk_size configuration option. You can monitor resizing progress using the new Innodb_buffer_pool_resize_status status variable. For more information, see Resizing the InnoDB Buffer Pool Online.
  • Replication: When replicating from a master running a version earlier than MySQL 5.6.0 [Read "5.5" or "5.1"] to a slave running MySQL 5.6.0 or later, the slave requires the master_uuid value, which is the server_uuid value from the master. The master_uuid value is unsupported on the older master, and in such a replication situation could become invalid on the newer slave. A check for empty master_uuid now ensures that the slave uses an empty value for master_uuid. (Bug #18338203)
  • Incompatible Change: mysql_install_db has been rewritten from Perl into C++. This enables it to be provided as an executable binary and eliminates its dependency on having Perl installed.
  • MySQL builds on Windows using Visual Studio now require Visual Studio 2013 or later. The previous requirement was Visual Studio 2010 or later. (Bug #18404381)
  • Now, MYSQL_MAINTAINER_MODE is on by default when compiling debug builds with GCC, and MYSQL_MAINTAINER_MODE enbles -Werror regardless of whether GCC or Clang is used.
  • MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel. If DTrace is present, server builds will detect it with no special CMake options required.
  • Incompatible Change: A new log record type (MLOG_FILE_NAME) is used to identify file-per-table tablespaces that have been modified since the last checkpoint. This enhancement simplifies tablespace discovery during crash recovery and eliminates scans on the file system prior to redo log application. For more information about the benefits of this enhancement, see Tablespace Discovery During Crash Recovery. This enhancement changes the redo log format, requiring that MySQL be shut down cleanly before upgrading to or downgrading from MySQL 5.7.5.
  • Incompatible Change: The InnoDB storage engine can no longer be disabled. The –skip-innodb option is deprecated and has no effect, and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (–innodb=OFF, –disable-innodb, and so forth). A new innodb_lock_no_retry flag for the –debug option is now available.
  • Incompatible Change: The Performance Schema now provides a user_variables_by_thread table that exposes user-defined variables. For more information, see Performance Schema Connection Attribute Tables. In consequence of this change, the server now limits user-defined variable names to a maximum of 64 characters, the length of the VARIABLE_NAME column in the table. Previously, the server did not enforce a limit.
  • The optimizer computes more accurate costs for semi-join materialization. (Bug #18558561)
  • To generate execution plans, the optimizer uses a cost model that is based on estimates of the cost of various operations that occur during query execution. The optimizer has a set of compiled-in default “cost constants” available to it to make decisions regarding execution plans. The optimizer now has in addition a database of cost estimates to use during execution plan construction. These estimates are stored in the server_cost and engine_cost tables in the mysql system database and are configurable at any time: Any non-NULL cost estimate stored in the cost model tables overrides the corresponding compiled-in default estimate. Any NULL estimate indicates to the optimizer to use the compiled-in default. Implementation and testing is ongoing to make it safe for DBAs to change these values. Currently, changing them should be considered at your own risk. If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the mysql database.
  • The optimizer now uses more exact index statistics.

Truncated by Planet PHP, read more at the original (another 8422 bytes)

Peter ZaitsevMySQL ring replication: Why it is a bad option (7.10.2014, 16:15 UTC)

I’ve recently worked with customers using replication rings with 4+ servers; several servers accepting writes. The idea behind this design is always the same: by having multiple servers, you get high availability and by having multiple writer nodes, you get write scalability. Alas, this is simply not true. Here is why.


High Availability

Having several servers is a necessary condition to have high availability, but it’s far from sufficient. What happens if for instance C suddenly disappears?


  • The replication ring is broken, so updates from A and B will never go to D. D will then quickly become so out-of-date that it’s not going to be usable. But wait! A will no longer receive the updates from B so A will quickly become non usable as well. Same thing for B. So unless you are very quick to configure a smaller ring with the remaining nodes, the whole chain will soon be non working.
  • If an event from C is still being executed on one of the other servers, it will go into an infinite loop, simply because C is the only server being able to prevent an event originally coming from C to cycle through the ring.

Conclusion: each time a server goes down, the whole system goes down. In other words, availability is poorer than with a single server.

Write Scalability

You can think that if you are able to run 1000 writes/s on a single server, writing on 4 servers in parallel will allow you to run 4000 writes/s over the whole cluster. However reality is quite different.

Don’t forget that ALL writes will be executed on ALL servers. So we have 2 separate scenarios:

  • Scenario #1: 1000 writes/s is the point where you’re hitting a bottleneck (for instance disk saturation). Then you’ll never be able to handle the extra load coming from replication. What is going to happen is simply that the servers will become slow because of overload and they’ll never be able to go beyond the 1000 writes/s mark.
  • Scenario #2: a single server could handle 5000 writes/s. Then writing on all servers will indeed allow you to claim that your cluster can absorb 4000 writes/s. But you would achieve the same result by running 4000 writes/s on a single server. This has nothing to do with write scalability.

Conclusion: As all writes are run on all servers, writing on multiple nodes doesn’t magically create extra write capacity. You’re still bound by the capacity of a single server.

Other concerns

Another concern when allowing multiple writers is write conflicts. MySQL doesn’t have any mechanism to detect or solve write conflicts.

So lots of “funny” things can happen when writes are conflicting:

  • Duplicate key errors that will cause replication to halt. And no, setting auto_increment_increment and auto_increment_offset cannot resolve all possible situations when duplicate key errors can happen.
  • An even funnier situation is when conflicting writes do not generate a replication error, but instead create hidden data inconsistencies. Like you have value=100 in a field, A does value=value+2 and B does value=valuex2. You can end up with one server having value=202 and another server having value=204. Which one is the right value? Impossible to know…

If you’re interested in learning more on the risk of writing on multiple nodes while using regular MySQL replication, you can check out this webinar.


A ring is one the worst MySQL replication topologies as it dramatically increases the complexity of all operations on the ring while providing no benefit.

If you need an HA solution, it is not an easy choice as there are many of them and all have tradeoffs, but a ring is definitely not the right option. This post can help you find the right candidate(s).

If you need write scalability, the options are limited, but again, MySQL ring replication is not a good fit. The main question to answer is how many writes do you want to be able to run? For instance, if you want 10x write scalability but your current workload is 100 writes/s, that’s easy: just make sure you have a decent schema, decent indexes and decent hardware. If you want 10x write scalability but you’re already running 5000 writes/s, it’s probably time to explore sharding.

The post MySQL ring replication: Why it is a bad option appeared first on MySQL Performance Blog.

Peter ZaitsevPercona Server 5.6.21-69.0 is now available (7.10.2014, 16:04 UTC)

Percona ServerPercona is glad to announce the release of Percona Server 5.6.21-69.0 on October 7, 2014. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.21, including all the bug fixes in it, Percona Server 5.6.21-69.0 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free. Complete details of this release can be found in the 5.6.21-69.0 milestone on Launchpad.

New Features:

Bugs Fixed:

  • Backup Locks did not guarantee consistent SHOW SLAVE STATUS information with binary log disabled. Bug fixed #1358836.
  • Audit Log Plugin would rotate the audit log in middle of an audit message. Bug fixed #1363370.
  • When the binary log is enabled on a replication slave, SHOW SLAVE STATUS performed under an active BINLOG lock could lead to a deadlock. Bug fixed #1372806.
  • Fixed a memory leak in Metrics for scalability measurement. Bug fixed #1334570.
  • Fixed a memory leak if secure-file-priv option was used with no argument. Bug fixed #1334719.
  • LOCK TABLES FOR BACKUP is now incompatible with LOCK TABLES, FLUSH TABLES WITH READ LOCK, and FLUSH TABLES FOR EXPORT in the same connection. Bug fixed #1360064.

Other bugs fixed: #1361568.

NOTE: Automatic upgrade for Percona Server with TokuDB on Debian/Ubuntu distribution will cause an error. In order to upgrade you’ll need to force the upgrade with “apt-get install -f” or remove the percona-server-tokudb-5.6 before upgrading and install it after the server package upgrade is done.

Release notes for Percona Server 5.6.21-69.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker

The post Percona Server 5.6.21-69.0 is now available appeared first on MySQL Performance Blog.

Peter ZaitsevPercona Server 5.5.40-36.1 is now available (7.10.2014, 15:20 UTC)

Percona Server
Percona is glad to announce the release of
Percona Server 5.5.40-36.1 on October 7, 2014. Based on MySQL 5.5.40, including all the bug fixes in it, Percona Server 5.5.40-36.1 is now the current stable release in the 5.5 series.

Percona Server is open-source and free. Details of the release can be found in the 5.5.34-36.1 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

Bugs Fixed:

Release notes for Percona Server 5.5.40-36.1 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

(Please also note that Percona Server 5.6 series is the latest General Availability series and current GA release is 5.6.21-69.0.)

The post Percona Server 5.5.40-36.1 is now available appeared first on MySQL Performance Blog.

Open QueryDatabase talks at OSDC 2014 Gold Coast (7.10.2014, 05:56 UTC)

Open Query Engineer Daniel Black and Engineer/Trainer Peter Lock will be presenting sessions at the upcoming Open Source Developers’ Conference which is hosted at Griffith University Gold Coast Campus, 4-7 November 2014.

I also spotted

which should be very interesting as well. There many be more still, there are lots of sessions!

Full conference tickets cost less than $300 and include the lunches as well as the conference dinner, and all the tutorials/workshops in the main conference. Speaking from experience, OSDC is always great with good talks and excellent people to chat with.

If you use this special link you’ll get the awesome ticket price. Although if you are a student, do go through the main website for your extra special price.

Peter ZaitsevPercona XtraBackup 2.2.5 now available (free MySQL hot backup software) (6.10.2014, 12:50 UTC)

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.2.5 on October 2, 2014. Downloads are available from our download site here and Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

New Features:

  • Percona XtraBackup has been rebased on MySQL 5.6.21.

Bugs Fixed:

  • The fix for bug #1079700 introduced a problem for users with huge numbers of InnoDB tablespaces, and the workaround of raising the open files limits didn’t work in all cases due to a limitation in the Linux kernel. A new innobackupex --close-files option has been implemented to close the file handles once they are no longer accessed. NOTE: Using this option may result in a broken backup if DDL is performed on InnoDB tables during the backup procedure. Bug fixed #1222062.
  • Fix for bug #1206309 introduced a regression in Percona XtraBackup 2.2.0 which caused Percona XtraBackup to fail to copy redo logs in random cases. Bug fixed #1365835.
  • innobackupex --galera-info didn’t copy the last binlog file when it was taking a backup from server where backup locks are supported. Bug fixed #1368577.
  • xtrabackup binary would accept arguments that were not options, which could lead to unexpected results. Bug fixed #1367377.
  • If innobackupex is run against MySQL 5.1 with built-in InnoDB, it will now suggest using Percona XtraBackup 2.0 or upgrading to InnoDB plugin, rather than just failing with the generic unsupported server version message. Bug fixed #1335101.
  • Using the (deprecated) log parameter in mysqld section would cause backups to fail. Bug fixed #1347698.
  • Percona XtraBackup now uses MySQL code to get the stack trace in case Percona XtraBackup crashes with a segmentation fault or an assertion failure. Bug fixed #766305.
  • Attempt to use any of the following options without the --incremental option now fails with an error message rather than creates a full backup: --incremental-lsn, --incremental-basedir,

Truncated by Planet PHP, read more at the original (another 2099 bytes)

Chris CalenderMySQL 5.6.21 Overview and Highlights (3.10.2014, 21:48 UTC)

MySQL 5.6.21 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there was 1 “InnoDB Notes” and 1 “Functionality Added or Changed” bug fix (and 0 “Security Fix”), so not much there, but of course they should be noted:

  1. InnoDB Note: The –skip-innodb option is now deprecated and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (–innodb=OFF, –disable-innodb, and so forth).
  2. Functionality Added: Internally, spatial data types such as Geometry are represented as BLOB values, so when invoked with the –hex-blob option, mysqldump now displays spatial values in hex. (Bug #43544, Bug #11752369)

In addition to those, there were 43 other bug fixes (the “partitioning” bug is also an “InnoDB” bug):

  • 12 InnoDB
  • 13 Replication
  • 18 Miscellaneous
  •   1 Partitioning

The highlights for me were 3 InnoDB regression bugs fixed and an important InnoDB/Partitioning performance-related bug fix as well (there were also 2 InnoDB/memcached bugs, and 13 replication bugs, so if using memcached or replication, you should review the full changelogs), so check if they might have affected you:

  1. InnoDB: An ALTER TABLE … ADD FOREIGN KEY operation could cause a serious error. (Bug #19471516, Bug #73650, Bug #73761 – Public, & Percona Bug #1366073)
  2. InnoDB: During recovery, a segmentation fault would occur when marking a table as corrupt. (Bug #18942294)
  3. InnoDB: With a transaction isolation level less than or equal to READ COMMITTED, gap locks were not taken when scanning a unique secondary index to check for duplicates. As a result, duplicate check logic failed allowing duplicate key values in the unique secondary index. (Bug #19140907, Public Bug #73170) References: This bug is a regression of Bug #16133801 (Public Bug #68021).
  4. InnoDB; Partitioning: Large numbers of partitioned InnoDB tables could consume much more memory when used in MySQL 5.6 or 5.7 than the memory used by the same tables used in previous releases of the MySQL Server. (Bug #17780517, Bug #70641) References: This bug was introduced by Bug #11764622, Bug #57480.


So while there were no major changes, those 3 InnoDB regression bugs are definitely of concern, so I’d be sure to review these to see if you’re running an affected version, and consider upgrading if so.

The last bug really only relates to partitioned InnoDB tables, so if you’re not using them, then it’s not really a big deal. However, of course, if you are and you are running an affected version, then I would consider upgrading due to the memory over-consumption.

Similarly, if running memcached, I’d recommend checking those fixes and affected versions to see if you might benefit from an upgrade also.

The full 5.6.21 changelogs can be viewed here (which has more details about all of the bugs listed above):

Hope this helps. :)


Peter ZaitsevHAProxy: Give me some logs on CentOS 6.5! (3.10.2014, 13:59 UTC)

HAProxy is frequently used as a load-balancer in front of a Galera cluster. While diagnosing an issue with HAProxy configuration, I realized that logging doesn’t work out of the box on CentOS 6.5. Here is a simple recipe to fix the issue.

If you look at the top of /etc/haproxy/haproxy.cfg, you will see something like:

    log local2

This means that HAProxy will send its messages to rsyslog on But by default, rsyslog doesn’t listen on any address, hence the issue.

Let’s edit /etc/rsyslog.conf and uncomment these lines:

$ModLoad imudp
$UDPServerRun 514

This will make rsyslog listen on UDP port 514 for all IP addresses. Optionally you can limit to by adding:


Now create a /etc/rsyslog.d/haproxy.conf file containing:

local2.*    /var/log/haproxy.log

You can of course be more specific and create separate log files according to the level of messages:

local2.=info     /var/log/haproxy-info.log
local2.notice    /var/log/haproxy-allbutinfo.log

Then restart rsyslog and see that log files are created:

# service rsyslog restart
Shutting down system logger:                               [  OK  ]
Starting system logger:                                    [  OK  ]
# ls -l /var/log | grep haproxy
-rw-------. 1 root   root      131  3 oct.  10:43 haproxy-allbutinfo.log
-rw-------. 1 root   root      106  3 oct.  10:42 haproxy-info.log

Now you can start your debugging session!

The post HAProxy: Give me some logs on CentOS 6.5! appeared first on MySQL Performance Blog.

Cédric PEINTREProbably the best European conference on MySQL is coming this fall (1.10.2014, 21:45 UTC)

The full schedule for Percona Live London 2014 was revealed earlier this month.
Congratulation to all selected speakers and a big thanks to those who submitted a talk.

I have many reasons to believe that this conference will be a very good year.
Let me explain why:

A community event made by community members

If you didn’t know who selected the talks for the conference, have a look to the conference committee page.
This committee is a perfect mixed between community members and evangelists.
As chairman of this conference, I can say that these guys made an amazing job to rate and comment this huge amount of  submitted talks.

I particularly want to point out that despite the commitment of some members of the committee for their companies, they played the game with a perfect fair play. They made smart choices with honesty and impartiality.

Also, Percona organizes the conference and selects the committee members.
But isn’t involved in any way on the choices made by the committee members, it should be highlighted.
That mean that the committee works independently, on a fair process, really.

I established a new process this year to reinforce this idea of ​​fair selection of the talks.
The committee members didn’t have access to the other members’s votes until the end of the rating period.
I found this method very interesting to force a spontaneous and personal choice from each committee members.
It was probably not perfect but if you have a look to the selected talks, you may find that the selection is particularly renewed this year.

if I have the opportunity to work on this event again next year, we probably should involve the whole community in this process.
I don’t know how we could make this possible but we have 1 year to think about. Any ideas?

One more thing: We did all this for fun.

Active sponsors

I don’t want to promote the sponsors here but remember that this conference couldn’t be possible without them.
I just want to point out that the sponsors are actively involved in the MySQL Community and its development.

You probably heard how GitHub works with MySQL or all the good news that Tokutek provides for MySQL and MongoDB.
Codership, Continuent, Devart and Severalnines are really active, as usual.
Pythian (included Blackbird) is a leader in the world of (big/small)data, don’t miss their blog.
And HGST works to improve integration between databases and SSD.

Good sponsors make good events!

A well balanced schedule

Of course, good topics make good events.

The predefined categories allowed to have a good sample of topics this year.
HA, security, performance, replication… and a lot of case studies, you have no excuse to not attend.

The tutorials and the talks offered this year are simply amazing.

Also, Facebook is back this year with good stuff, it could be interesting to learn how they manage their small data.
Booking, eBay, HP and Spil Games certainly have very interesting things to expose too.

And of course, all the latest news about Oracle and MariaDB.

You still have doubts? Book now and save money!

Hope to see you there next month.
Good luck and have fun!

PS: Don’t miss the (free) community dinner, because smart attendees make good events!

Monty SaysWhy SkySQL becoming MariaDB Corporation will be good for the MariaDB Foundation (1.10.2014, 08:23 UTC)
Today SkySQL is changing its name to MariaDB Corporation. This is something that I had both anticipated and I think it's a great step for MariaDB.

I wanted here to to share my thoughts on how this change affects the MariaDB community.

The short version: As the MariaDB Corporation is the main driving force behind the development of the MariaDB server and the biggest support provider for it, it makes sense to give it a name that clearly communicates this fact.  The name change doesn't of course stop the company to continue it's excellent support for MySQL.

For MariaDB users and customers, the name change should not affect them in any way, except that it will make it easier for them to find more information about MariaDB as there is fewer names involved.

For the MariaDB Foundation, there is no big change either. After all, the MariaDB foundation was created to protect the MariaDB server, not the MariaDB trademark as such.

The longer version, for those who want more context, starts with some history.

After the Sun acquisition of MySQL AB, I started Monty Program to work on a branch of the MySQL code base named MariaDB after my youngest daughter. In 2010, I was one of the founders behind SkySQL as an alternative service provider for Oracle MySQL customers. Last year SkySQL merged with Monty Program to increase the support behind MariaDB. As the adoption of MariaDB has grown, SkySQL’s business has evolved to provide products and services to over 2 million MariaDB users.

Talking about a company called SkySQL, that provides subscription services for MariaDB while also supporting MySQL, was becoming too complicated and confusing. To make things simpler, and reinforce the company’s focus, I both agreed and recommended that a name change was due.

Having the company using the MariaDB name will also help ensure that the company will focus on MariaDB and put even more development resources on MariaDB.

I assume that some people will wonder if the MariaDB Foundation is needed anymore?  I think it's needed now more than ever to ensure that the MariaDB server is always guaranteed to be open for development by the community! The Foundation will continue in its role at the center of the open, independent and dynamic community that drives the adoption of MariaDB.  The Foundation will also need more paying members to be able to continue interacting with the ever growing external MariaDB developer community.

We’ve been working with the team at MariaDB Corporation (formerly SkySQL!) and have come to agreement on how the trademark will be used. Details of this will be published soon on

I continue to believe passionately that the world needs an open, actively developed relational database platform that is adopting to your needs and is better suited for modern web scale application development than other alternatives. MariaDB is that platform. We, the MariaDB developers and all other people at the MariaDB foundation and MariaDB Corporation, are all excited that so many of you are choosing MariaDB. We are all committed to making this choice a success.

MariaDB would not be what it is today without your support!  Thank you for this!
LinksRSS 0.92   RDF 1.
Atom Feed