Personal blog of Yzmir Ramirez

Over on his blog, Eric Hogue has written an interesting article on the tools available to you for profiling your application. Click on in, the link is around here somewhere.

You may have seen in the last couple of weekly news posts that Baron mentioned we are working on a new adaptive flushing algorithm in InnoDB. In fact, we already have three such algorithms in Percona Server (reflex, estimate, keep_average). Why do we need one more? Okay, first let me start by showing the current problems, and then we will go to solutions.

The basic problem is that, unfortunately, none of the existing flushing implementations (including both MySQL native adaptive flushing and that in Percona Server) can handle it properly. Our last invention, “keep_average”, is doing a very good job on systems based on SSD/Flash storage, but it is not so good for regular slow hard drives.

Let me state the following: If you have a lot of memory (and this is not rare nowadays, for example Cisco UCS C250), your database fits into memory, and your write rate is significant, then the slow disk is, by definition, not able to keep up with all the changes you do in memory.

Here is a graph for the tpcc-mysql benchmark (100W ~ 10GB of data, 12G innodb_buffer_pool_size, 1G innodb_log_file_size),
MySQL 5.5.10 with innodb_adaptive_flushing=ON (default)). The problem here is that the system has quite slow hard drives (2 hard disks in software RAID0).

As you can see, after the initial warmup, throughput drops constantly, all the way to zero, and may stay in that state for a long time (minutes). This comes from the fact that MySQL performs changes in memory at a faster rate than it can write to disks. Eventually it gets into an “async” state, where InnoDB tries to flush as many pages as possible in order to keep the checkpoint age fitting into the InnoDB transaction logs, and this makes the situation even worse (these are the periods of zero throughput on the graph).

MySQL’s general recommendation for this case is to increase innodb_io_capacity and decrease innodb_max_dirty_pages_pct so as to have fewer dirty pages. But I call that tuning by shooting yourself in the left foot. (You can’t run fast with a broken foot, right ?) And actually it does not work, as MySQL is not able to keep the number of dirty pages within the given innodb_max_dirty_pages_pct limit.

Another possible solution would be to increase innodb_log_file_size, but that: 1) only delays the problem until later; 2) increases recovery time (and that is an important factor with slow disks); and 3) MySQL does not support innodb_log_file_size > 4GB (it is supported in Percona Server).

To make things more interesting, let’s touch on the topic of flushing of neighbor pages. Performing some research, we found it works in a way we really did not expect. My understanding was that flushing neighbor pages was implemented to perform sequential writes where possible and avoid random writes, which is quite critical for hard drives. What we found is that InnoDB does it in the following way. Say we want to flush page P. InnoDB is looking in an area of 128 pages around page P, and flushes all the pages in that area that are dirty. To illustrate, say we have an area of memory like this: ...D...D...D....P....D....D...D....D where each dot is a page that does not need flushing, each “D” is a dirty page that InnoDB will flush, and P is our page.

So, as a result of how it works, instead of performing 1 random write, InnoDB will perform 8 random writes. I do not have a good explanation for why it is implemented this way.

To make the situation even worse, the count of flushed neighbor pages is counted toward the number of pages we asked to be flushed. That is, for example, we see that to make an improvement in the checkpoint_age we need to flush 8 pages. InnoDB flushes page P and its 7 neighbors (which are not really neighbors), and then stops after this (alas, 8 pages flushed). That is, instead of flushing 8 pages what we would expect to be flushed, InnoDB flushes 1 needed page and 7 random pages, which may not even be relevant for improving the checkpoint age.

This makes calculating how many pages we need to flush extremely hard (read “impossible”), and this is one of the reasons why innodb_adaptive_flushing is not able to keep up.

What if we disable flushing of neighbor pages (we have the option innodb_flush_neighbor_pages in Percona Server)? Well, it may help to some extent, but remember that hard disks love sequential operations, and your throughput in this case may turn out significantly worse.

So what is the solution? This was exactly the topic of our research. We set these goals:

• Provide stable throughput by avoiding big jumps in flushing pages.
• Make the algorithm independent of innodb_io_capacity and innodb_max_dirty_pages_pct. (This is important for systems where I/O capacity may vary a lot, like EC2 systems, or is affected by periodic tasks like backup or heavy background queries.)
• If we see that flushing to disk is not able to keep up with changes in memory, we need to have a throttling mechanism that will limit the rate of changes in memory.
• Optimize the flushing of sequential neighbor pages in a way that makes sense.

We have made good progress, and I will keep you posted on our ideas and results.

Stuart Herbert, PHP community member and all around nice guy, has been posting a series of posts on his blog based on his PHPUK talk “Beyond Frameworks”. Given the number of posts involved, one must wonder when Stuart sleeps. Click on in, I’ve got links…

We’ve released these fast-follow versions to address a regression that was introduced when we switched the version of Doctrine bundled in symfony to the latest security release. The Doctrine 1.2.4 tag does not include an unreleased fix to multiple database management that we had included in symfony 1.3.9 and 1.4.9. These new releases bundle the Doctrine library from the current master branch on GitHub via a new Subversion mirror (which you can checkout here).

How to Upgrade

If you’ve checked out a copy of the tag from Subversion you can switch to
the latest version:

// symfony 1.3
$ svn switch http://svn.symfony-project.com/tags/RELEASE_1_3_11

// symfony 1.4
$ svn switch http://svn.symfony-project.com/tags/RELEASE_1_4_11

If you are using the PEAR package you can update using the pear command:

// symfony 1.3
$ pear upgrade symfony/symfony-1.3.11

// symfony 1.4
$ pear upgrade symfony/symfony-1.4.11

This is the ZendCon 2010 session recording of “Improving QA on PHP Projects” by Michelangelo van Dam.

If you don’t participate to the weekly IRC
meetings and if
you have not attended the Symfony Live
Conference last week in Paris, you might have missed an important
announcement. The Symfony core team has decided to delay the release of the
Symfony2 final version. It has been a tough decision to make of course but we
think that there is no point in rushing out Symfony2. We also think that this
is indeed a good news for the project. Why? Because during the last few
months, more and more great developers have joined the Symfony2 team. They
give us great feedback on a day to day basis and as a result, the activity on
the Git repository has been quite intense during the last few weeks. To back
my words, let’s just say that we have merged more than 300 pull requests in
the last month alone and that more than 150 different developers have
contributed in one way or another to Symfony2. This is fantastic, but it also
means that we need a bit more time to stabilize all the changes we have made
recently.

The core architecture is now stable, except for a few changes in the naming
conventions that will be done in the coming days. It is really important that
the core team is confident that the core architecture of Symfony2 is flexible
enough as we will keep backward compatibility on it for the next few years.

We know that many of you are impatient and want to play with the framework.
The good news is that it has never been easier to do so. The ninth preview
release is now available for download. As we
slow down the number of changes in the core framework, it is easier for us to
release more frequently. It’s also better as the documentation evolves quickly
and it only documents the master version of the framework.

Upgrading from one preview release to the next one is now easier, thanks to
the new UPDATE
document
(Japanese
version).

So, why not at least release the first beta? Because we are waiting for the
last big modification that will possibly be merged: the form framework
refactoring. The first beta
version will be released after we take the decision (probably at the end of
this week) to merge this pull request or not. Entering beta means that all the
main features of the framework will be available. That does not mean that we
won’t break backward compatibility here and there, but all these changes will
be documented to ease migration.

Last but not the least, we have started to tag the public and stable API with
@api. As I wrote in my last
post
on my blog: “All classes, methods, and properties tagged with @api are
public in the sense that we guarantee their stability over time: their name,
signature, and behavior won’t change for any minor version of the library.”

This is a continuation of my series of benchmark posts comparing Amazon RDS to a server running on Amazon EC2. Upcoming posts (probably 6 or 8 in total) will extend the scope of the benchmark to include data on our Dell r900 with traditional hard drives in RAID10, and a server in the Joyent cloud. As a reminder, my goal was to run a long-term benchmark and see how the instance performed over time. Can it sustain performance over a several-day period of intense workload? The first step was to determine the number of threads that should be used for the benchmark.

To gauge this, I ran a series of 60-second benchmarks on the RDS server, and extracted the transactions per second from them, then used the peak throughput as my target configuration. The benchmark was sysbench oltp complex, with 400 million rows (88GB of data and indexes, which is larger than memory in all of the servers I benchmarked). Here are the results:

#Threads    #TPS
       8  319.47
      16  496.63
      32  885.46
      64 1254.46
     128  348.32
     256  194.67

With that as a rough guide, I ran another series of 60-second benchmarks, with the following thread counts:

#Threads    #TPS
       1   44.26
       2   78.66
       4  183.47
       8  316.79
      16  527.50
      24  576.44
      32  671.08
      40  796.30
      48 1128.46
      56 1278.88
      64 1333.63
      72 1312.06
      80 1203.37
      88  900.18
      96  733.57
     104  655.50
     112  567.87

The following is a chart of both benchmark runs.

Now, you may ask the question, is a duration of 60 seconds a good way to decide on a number of threads for a benchmark that runs for a long time? I answered that in my previous post on choosing a good benchmark length. The answer is no, 60 seconds is not enough to decide. However, while testing a variety of setups, I actually ran some other thread counts for durations of a few hours at a time. For example, I benchmarked some machines at up to 256 threads for a few hours.

I did not prepare publishable reports on all of the benchmarks I ran. While running the benchmarks, I gathered many gigabytes of performance and diagnostic metrics, and ensuring that it is ready to publish on this blog takes a lot of care. Instead, during the benchmarks I performed live bottleneck analysis, and observed data such as snapshots of the processlist and status counters, and created one-off graphs of various metrics, to determine how the machines and configurations were behaving. Based on my observations (not my formal analysis), I thought that 64 threads was a good balance between over-working some machines and under-working others. We have to find something in the middle so that we can benchmark all systems at the same number of threads, or the benchmarks will not be apples-to-apples.

It is important to note that these are the results I achieved on this RDS server, from this EC2 “driver” machine, at this specific point in time. There will be variations between machines and from time to time, so your benchmark mileage may vary.

Based on these results, I decided to benchmark the system at the RDS server’s peak short-term throughput of 64 threads and see how things went in longer benchmarks. The next blog post in this series will discuss the results.

I’ve presented at two different venues about HandlerSocket recently and the number one question that always arises is:

Why hasn’t HandlerSocket become more popular than it is?

Considering how fast and awesome HandlerSocket is, it’s not seeing as rapid adoption as some might expect. I theorize that there are five reasons for this:

Bugs, Bugs, Bugs

Up until the beginning of the year, HandlerSocket had a couple of bugs that a lot of people considered deal-breakers, and it’s not widely known that these issues have been fixed.

• Write operations do not invalidate the query cache – Exactly what it sounds like, if you performed a write operation via HandlerSocket, you could potentially read stale data from the MySQL interface.
• No support for auto increment – Exactly as it sounds, you couldn’t have auto-incrementing columns get an auto increment value on insert.

Distribution

Lots of organizations simply use what is provided to them. This was the case with MyISAM in standard distributions of MySQL for the longest time; many people were on MyISAM simply because it was the default. Even today, many organizations using recent versions of MySQL 5.1 are not using the InnoDB plugin because it is not enabled by default! If people cannot be bothered to add a couple of lines to my.cnf to enable the InnoDB plugin, they certainly cannot be expected to build the HandlerSocket plugin from source.

This is being changed with Percona-Server. The HandlerSocket plugin is now included by default and it simply takes a couple of steps to set it up. Hopefully we’ll see HandlerSocket be included in other places in the MySQL community.

No releases

There are no real releases or release schedules that are easy to digest. There is active development, bugs are being fixed, and the documentation is improving, but that does not translate into people knowing that their bugs are fixed and there is new code available.

No popular use cases

This is sort of a chicken-and-egg problem where people need to see use cases or endorsements of well-known companies in order to adopt a technology. Generally speaking, engineers are more inclined to remember a use case for a particular technology when it is well documented.

Not enough benchmarks

Finally, the benchmarks are not thorough enough. Nobody has yet taken the time to show diverse workloads on a variety of data sets. The existing work has focused almost entirely on primary-key-select workloads because that is where HandlerSocket truly shines; but people still want to know how the rest of the functionality performs. Also, there is an inherent distrust of benchmarks when done only by parties who have an interest in the product; there needs to be a wide variety of benchmarks from an even wider variety of industries and companies.

Summary

Most of these “issues” will work themselves out naturally as HandlerSocket matures. More people will publish benchmarks and use cases. As companies upgrade their Percona-Server installations, HandlerSocket will become available and easily accessible to them. Finally, as the code matures and the community grows, we’ll see lots more third-party involvement with bug reports, feature requests, and patches. I see lots of interest in HandlerSocket and am tremendously optimistic about it’s future.

I am happy to announce that Percona Server and Percona XtraBackup are now available for the Solaris platform!

• You can download the latest Percona Server 5.1.55-12.6 stable release from our download area.
• As for Percona XtraBackup, we made Solaris binaries for the upcoming XtraBackup-1.6 release, and they are available from “pre-release” directory.

There are couple features not available in Solaris build of Percona Server: Response Time Distribution (it depends on GCC platform specific functions) , HandlerSocket ( compilation issues on Solaris), Maatkit UDFs.

I want to say a special thanks to Joyent (a cloud computing provider) for Solaris resources and help with building binaries. Joyent provides templates for Percona Server and Percona XtraBackup by request.

We have web badges for promoting Percona Server and XtraBackup! If you use them and want to put little buttons in your website’s footer or sidebar, now it’s easy. There is copy-paste ready HTML for three styles of badge, for each of them: Percona Server badges and Percona XtraBackup badges.