In many MySQL Benchmarks we can see performance compared with rather high level of concurrency. In some cases reaching 4,000 or more concurrent threads which hammer databases as quickly as possible resulting in hundreds or even thousands concurrently active queries.

The question is how common is it in production ? The typical metrics to use for concurrency defined as number of queries being actually processed is “Threads_Running” which you can easily see for your production system:

root@smt2:/mnt/data/ mysqladmin extended -i1 | grep Threads_running
| Threads_running                               | 60          |
| Threads_running                               | 61          |
| Threads_running                               | 63          |
| Threads_running …

There are many reasons for wanting a small MySQL database server:

• You’re a uni student who wants to learn the SQL language better and needs a mini-testbox
• You’re a Windows user who wants to play around with Percona Server on Linux
• You’re a corporate application developer who wants a small SQL development & test box
• You’re a Internet startup that just needs a tiny startup database server without all the added costs

So, how about if you could setup a small Arch Linux ARMv6-based hardware device which runs Percona Server for MySQL, in a space not much bigger than your mouse, with the power consumption of only a smartphone charger, fully networked, all for under $80?

Introducing the Raspberry Pi with Percona Server:

Raspberry Pi Fully …

MySQL 5.6 was made generally available as a production-ready solution earlier this month. This release comes about 2 years after MySQL 5.5 was released, but MySQL 5.6 contains improvements started long before that – for example, work on the Innodb Full Text Search project was started over 6 years ago, in addition with many optimizer and replication features. We’re happy to congratulate MySQL development team at Oracle with making this release happen.

In this blog post, I will not go into a features overview of MySQL 5.6. You can check out Release Notes for a good overview as well as many blog posts and articles written on this matter. Instead, I will tell you what is the current state of things with MySQL 5.6 at …

As the part of analyzing surprising MySQL 5.5 vs 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 5.6.10 to the different tables and ran the query:

mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56  from var55 left join var56 on var55.variable_name=var56.variable_name where  var55.variable_value!=var56.variable_value;
+---------------------------------------------------+------------------------------------------+------------------------------------------+
| variable_name                                     | value55                                  | var56                                    | …

Based on a lot of surprising comments about my MySQL 5.5 vs 5.6 performance post I decided to perform deeper investigation to see where my results could go possibly wrong. I had set up everything to be as simple as possible to get maximally repeatable results. I did Read Only ran which is typically a lot more repeatable (though also less relevant for production like workload). I had done number of iterations for benchmark run and I used dedicated physical hardware box so external environment impact often causing problems in Virtualized environments can be eliminated. Still I found there could be large variance between the runs.

I set up the benchmarks run to go over night in the loop, doing the benchmark run for 5 runs when restarting MySQL server and repeating the run. I did it on 2 identical boxes to eliminate faulty hardware as …

Analyzing SQL Queries with Percona Toolkit, Feb. 25-28, 9-11 a.m. PST

Next week I’m teaching an online Percona Training class, called Analyzing SQL Queries with Percona Toolkit.  This is a guided tour of best practices for pt-query-digest, the best tool for evaluating where your database response time is being spent.

This month we saw the GA release of MySQL 5.6, and I wanted to check if any improvement has been made to the slow query log in table format.  Users of some MySQL appliances like Amazon RDS must use table logs, since they can’t access the file-based logs on the server.

I read the logging …

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 …