October 25, 2014

MySQL 5.5 and MySQL 5.6 default variable values differences

As the part of analyzing surprising MySQL 5.5 vs MySQL 5.6 performance results I’ve been looking at changes to default variable values. To do that I’ve loaded the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query:

Lets go over to see what are the most important changes […]

MySQL 5.6: Improvements in the Nutshell

Preparing for my talk for Percona MySQL University in Raleigh,NC, Tuesday 29th of January I have created the outline of improvements available in MySQL 5.6 which I thought was worth sharing to give a feel for how massive work have been done for this release in variety of areas. I’m sure the list is not […]

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the […]

kernel_mutex problem cont. Or triple your throughput

This is to follow up my previous post with kernel_mutex problem. First, I may have an explanation why the performance degrades to significantly and why innodb_sync_spin_loops may fix it. Second, if that is correct ( or not, but we can try anyway), than playing with innodb_thread_concurrency also may help. So I ran some benchmarks with […]

kernel_mutex problem. Or double throughput with single variable

Problem with kernel_mutex in MySQL 5.1 and MySQL 5.5 is known: Bug report. In fact in MySQL 5.6 there are some fixes that suppose to provide a solution, but MySQL 5.6 yet has long way ahead before production, and it is also not clear if the problem is really fixed. Meantime the problem with kernel_mutex […]

Three ways that the poor man’s profiler can hurt MySQL

Over the last few years, Domas’s technique of using GDB as a profiler has become a key tool in helping us analyze MySQL when customers are having trouble. We have our own implementation of it in Percona Toolkit (pt-pmp) and we gather GDB backtraces from pt-stalk and pt-collect. Although it’s helped us figure out a […]

Beware the Innodb Table Monitor

As I stated in my last post, I decided to use the Innodb Table monitor to diagnose an Index count mismatch error a customers found in their mysqld.err log to verify if the problem still existed. The Innodb Table Monitor has existed for basically forever in Innodb (the MySQL manual discusses it back in the 4.1 […]

Avoiding auto-increment holes on InnoDB with INSERT IGNORE

Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this is undocumented behaviour. This documentation bug is already submitted. Firstly, we will start with a simple question. Why do we have […]

Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit

It is no secret that bugs related to multithreading–deadlocks, data races, starvations etc–have a big impact on application’s stability and are at the same time hard to find due to their nondeterministic nature.  Any tool that makes finding such bugs easier, preferably before anybody is aware of their existence, is very welcome.

Understand InnoDB spin waits, win a Percona Live ticket

It’s Friday again (so soon!) and time for our TGIF contest, to give away a free ticket to Percona Live London. Before we do that, though, just what in the world does this output from SHOW INNODB STATUS mean?

To understand this text, you have to understand how InnoDB handles mutexes. It tries a […]