Recently some of my fellow Perconians and I have noticed a bit of an uptick in customer cases featuring the following error message:

SQLSTATE[HY000] [1135] Can't create a new thread (errno 11); if you are not 
out of available memory, you can consult the manual for a possible OS-dependent bug.

The canonical solution to this issue, if you do a bit of Googling, is to increase the number of processes / threads available to the MySQL user, typically by adding a line like this to /etc/security/limits.conf:

mysql  soft  nproc  4096

followed up by a restart of MySQL in a fresh user session. If you’re running RHEL/CentOS 5, Ubuntu, or versions of Fedora prior to Fedora 9, this solution will most likely solve your problem. But with RHEL/CentOS 6 (and derivatives) and Fedora 9 and later, you’ll likely find that this tweak no longer works.

In Fedora 9 and RHEL 6, a “bug fix” was introduced which was …

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

To read what Metadata Locking exactly is please read this section here in the MySQL manual.

Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3

Metadata Locking behavior prior to MySQL 5.5.3

Prior to MySQL 5.5.3 a statement that opened a …

At Percona we offer a number of services. One of them, Custom MySQL Server Development, is commonly the most misunderstood and undervalued. There are a lot of ways Percona custom MySQL server development can help your business be more successful with MySQL. Here are some ways:

Bugs – There are Bugs in MySQL, Percona Server, and other products. By far the best and most cost efficient way to deal with Bugs is to have a Percona MySQL Support subscription which includes all you can eat bug fixes. If you need just one bug fixed this may be a way to go. Many bugs we’re tasked to fix are actually bugs in MySQL which the Oracle MySQL development team has not fixed. This does not only apply to “community reported bugs” as we had a number of customers who had an Oracle MySQL Support …

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 complete so If I miss something significant please let me know through the comment and I’ll update the page

Scalability
- Scalable Read Only Transactions
- Concurrent Innodb data file extension
- Non-Recursive Deadlock Detection
- Faster Locking Primitives
- Improved Innodb Thread Concurrency
- Multiple background Purge Threads
- Improved Purge lag control (now works)
- Split of “Kernel Mutex”
- Data Dictionary Cache
- Improved Adaptive Flushing
- Page …

Last time I wrote about a few tips that can make you more efficient when using the command line on Unix. Today I want to focus more on pager.

The most common usage of pager is to set it to a Unix pager such as less. It can be very useful to view the result of a command spanning over many lines (for instance SHOW ENGINE INNODB STATUS):

mysql> pager less
PAGER set to 'less'
mysql> show engine innodb status\G
[...]

Now you are inside less and you can easily navigate through the result set (use q to quit, space to scroll down, etc).

Reminder: if you want to leave your custom pager, this is easy, just run pager:

mysql> pager
Default pager wasn't set, using stdout.

Or \n:

mysql> \n
PAGER set to stdout

There are times where you need to know exactly how much memory the mysqld server (or any other program) is using, where (i.e. for what function) it was allocated, how it got there (a backtrace, please!), and at what point in time the allocation happened.

For example; you may have noticed a sharp memory increase after executing a particular query. Or, maybe mysqld is seemingly using too much memory overall. Or again, maybe you noticed mysqld’s memory profile slowly growing overtime, indicating a possible memory bug.

Whatever the reason, there is a simple but powerful way to profile MySQL memory usage; the Massif tool from Valgrind. An excerpt from the Massif manual page (Heap memory being simply the allotted pool of memory for use by programs);

Massif tells you not only how much heap memory your program is using, it also …

While we do have many blog posts on replication on our blog, such as on replication being single-threaded, on semi-synchronous replication or on estimating replication capacity, I don’t think we have one that covers the very basics of how MySQL replication really works on the high level. Or it’s been so long ago I can’t even find it. So, I decided to write one now.

Of course, there are many aspects of MySQL replication, but my main focus will be the logistics – how replication events are written on the master, how they are transferred to the replication slave and then how …

I prefer to run MySQL with innodb_flush_method=O_DIRECT in most cases – it makes sure there is no overhead of double buffering and I can save the limited amount of file system cache I would normally have on database server for those things which need to be cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should allow it to load IO subsystem very effectively being able to issue many outstanding requests which when can be merged on OS level or RAID controller so we should expect at least as good performance from O_DIRECT as from buffered mode ? It turns out it is not always the case.

I came to this take by accident so there is not a lot of science in coming up with it but I think it is still pretty well representative. I have an old test server having 4*7200RPM SATA hard drives in RAID10. It has 8GB of RAM and I’m running Percona Server …

pt-stalk recipes: Gather forensic data about MySQL when a server problem occurs

It happens to us all from time to time: a server issue arises that leaves you scratching your head. That’s when Percona Toolkit’s pt-stalk comes into play, helping you diagnose the problem by capturing diagnostic data that helps you pinpoint what’s causing the havoc hitting your database.

From the documentation (http://www.percona.com/doc/percona-toolkit/pt-stalk.html):

pt-stalk watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it to execute a custom command, or to gather the data on demand without waiting for the trigger to happen. …

With Performance Schema improvements in MySQL 5.6 I think we’re in the good shape with insight on what is causing performance bottlenecks as well as where CPU resources are spent. (Performance Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re still walking blind with MySQL is resource usage – specifically Memory Usage.

I can’t count how many time I had to scratch my head with system configured to consume only few GBs in global buffers growing to consume much more for some unknown needs leaving me puzzled whenever it is user variables, complex stored procedures temporary tables or something else. Not only such connection related allocations are invisible but many global allocations are poorly visible too. Sure, we know how much memory was allocated for Innodb Buffer Pool or Query Cache but amount of memory used for …