The MySQL 5.6 certification exams (Developer and DBA) are now GA! A big thanks to all those who sat for the extended length beta exams – and subsequently waited for several months for your results. Your feedback and the …[Read more...]
I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.
Isolating the problem
As always with a slow query, finding the execution plan with
EXPLAIN is the 1st step to understand where time is
spent. Here the plan was very good with almost all joins using
the primary key or a unique …
Megabytes of text had been written already on InnoDB locking and
deadlocks. Still, even very simple cases of deadlocks while
working with a table having only one row sometimes make people
wonder what happened and why.
Today I want to check if this topic is explained well in the manual and existing blog posts and understood properly. So, it's an exercise for my dear readers and those who like to report bugs as much as I do.
Let's consider a very simple example. In session #1 with default transaction isolation level execute the following:
CREATE TABLE `tt` (
`id` int(11) NOT NULL,
MySQL 5.6.16 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:
As opposed to the latest 5.5 release, this latest 5.6 release has quite a few more bug fixes, but that’s expected since 5.5 has been GA for much longer.
There were 2 minor functionality changes:
Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.16.
As you should already know, Oracle had released MySQL 5.6.16
officially on January 31, 2014. You can find all the details in
official release notes. In this post I decided
to concentrate on important fixes for bugs reported by community
in 4 most important categories: InnoDB, replication, partitioning
Let's start with Bug #70768, "Persistent optimizer statistics often causes LOCK_open stalls". It's well known that persistent statistics for InnoDB …
Over the last few days, I integrated the MySQL 5.6 GTID version of the Percona Replication Manager (PRM) work of Frédéric Descamps, a colleague at Percona. The agent supports the GTID replication mode of MySQL 5.6 and if the master suffers a hard crash, it picks the slave having applied the highest transaction ID from the dead master. Given the nature of GTID-based replication, that causes all the other slaves to resync appropriately to their new master which is pretty cool and must yet be matched by the regular PRM agent.
For now, it is part of a separate agent, …[Read more...]
Oracle has released the Critical Patch Update (CPU) advisory for January
The affected MySQL products are:
So this means that you should consider updating MySQL. For MySQL Enterprise the updates should be available on My Oracle Support and for the Community version the new versions are on the …[Read more...]
A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different with MySQL 5.6?
For this test, we will use these 2 tables (same structure as in Peter’s post):
CREATE TABLE t1000merge ( id int not …[Read more...]
In many parts of the MySQL world, whether you have users, developers or administrators of MySQL, the season holidays are for family, relax, prayers, travel, or they are simply a moment where you can enjoy what you like or care most about.
For me, this time is dedicated to my family, but also to deeper thoughts around the strategies to adopt in short and long term. My work nowadays, as the work of many others, is ruled by quick decisions, by the "time to market” - whatever “market" means in a specific context. Decisions must be made in meetings that are time-boxed in one hour or even less. In the end, you accumulate so much work and high …