Personal blog of Yzmir Ramirez

Just as a reminder – tomorrow, May-9, 2012, at 11am PDT I will be giving a free webinar “MySQL and SSD”. This is the same talk I gave on Percona Live MySQL Conference, so if you were there – you probably will find nothing new. Otherwise, you are welcome to join!

You might have heard that Oracle made the decision not to support MySQL for IBM i any longer. This is certainly understandable. However, there are still users who want to continue running IBM i and MySQL.

That’s why we’re happy to announce that we have assisted Zend to introduce Zend DBi as a drop-in replacement for MySQL on the IBM i platform. Zend approached us to say that they want to ensure there’s a way forward for IBM i users, and asked if we’d help them. We’re delighted to do so.

The result is Zend DBi. It is basically a renamed build of MySQL for IBM i. It’s a 100% compatible drop-in replacement. Everything works on Zend DBi just as it works on MySQL, with no need to rewrite applications, management scripts, or anything else. There is no difference in query syntax, client-server protocol, or data storage on disk. Anything that runs on MySQL will run identically on Zend DBi with no modification, and vice versa.

In addition, Zend DBi will remain compatible with MySQL in the future, so it is a no-lock-in solution. If you want to leave the IBM i platform and switch to Oracle’s MySQL on another platform, it will work seamlessly.

Why is Percona involved? Because providing quality server builds is a substantial engineering effort that requires a lot of expertise to do right, and Percona has that expertise, as we’ve proven by providing our own Percona Server variant of the MySQL server. We have a history of improving the MySQL server and finding and solving bugs in it — we’ve even found and solved bugs on the IBM i platform.

We think that Zend DBi will be a great service to IBM i MySQL users who want to remain on their chosen platform. We’re happy that Zend took this initiative, and even happier that we can play a role.

Zend DBi is available under GPL license from Zend website.

In my previous post with results for Fusion-io ioDrive we saw some instability in results, I was pointed that it may be fixed in new drivers VSL 3.1.1. I am not sure if this driver is available for everyone – if you are interested, please contact your Fusion-io support representative. I installed new drivers and firmware, and in fact, the result improved.

Information about driver and firmware: Firmware v6.0.0, rev 107006. Fusion-io driver version: 3.1.1 build 172.

Actually an upgrade was not flawless, after a firmware upgrade I had to perform low-level formatting, which erase all data. So if you want to do the same – make sure you copy your data.

So there are results for driver 3.1 (with comparison to previous driver 2.3)

Random writes:

For random writes there is not much improvements, the throughput is about the same.

Random reads:

But there is a significant improvement for random reads. The results is stable on 640 MiB/sec level and it is higher than previously.

Sync random reads per threads, throughput:

Response time:

Again, there is improvement in throughput, in both in quality and absolute value. For response time – in some cases, there is 2x improvement.

So it seems for Fusion-io ioDrive it is worth to consider upgrade to 3.1 Driver (remember to copy your data before).

Follow @VadimTk

Symfony Live Paris 2012 is in less than a month. In this post, I’ll give you a
quick update on the latest news.

The Schedule

As you might have noticed, we published the full
schedule for Symfony Live Paris
2012 some time ago. This year, we have three tracks running in parallel; and
that will allow for more breaks and plenty of time for everyone to chat and
share.

New this year is a special slot for lightning talks. The idea is really
simple: every 10 minutes, a speaker has 7 minutes to talk about something
related to Symfony: from serious talks about a specific feature or bundle to
fun topics about Symfony, developers, life, and more! Be creative, and be
prepared!

To entertain us the first day, Jeremy Mikola will host a Jeopardy game. If
you know Jeremy and if you follow him on Twitter, you already know that it
will be a lot of fun! (no pressure Jeremy)

The Training Day

Like every year, the day before the conference, Sensio Labs will organize a
training day. This year, you can choose between two different topics on
Symfony2:

• Introduction to Symfony2: Learn the basics of the Symfony framework and get ready to develop your first applications.

• Going further with Symfony2: Learn more about the Symfony features set with advanced tools like caching, security and dependency injection management.

Each training will be delivered in French and English.

Registration

I know that most of you are waiting for the last minute to register but the
sooner the better, really. One of the reason is that the hotels are already
quite full nearby the conference venue… but the main reason is that it would
make me sleep better in the coming weeks.

Also, if you have purchased your ticket as a voucher, don’t forget to redeem
it on the website to actually be registered.

Translation

Hosting a conference in France is not easy as French people expect sessions to
be in French. This year, we have a big new thing for French attendees: we are
going to have real-time translation for all sessions in track A and B.

And we are also going to have English translations for French sessions, which
is great news as we shoud have a great session on the usage of Symfony2 at
Overblog, which is one of the biggest blogging platform in Europe!

Videos

Last, but not the least, I can now announce that all sessions (in track A and
B) will be recorded. And best of all, these videos will be free for all
attendees.

Sponsors

We are still looking for
sponsors to help us cover the
cost of the conference; read the guide and consider becoming a sponsor. Thanks
to ServerGrove and KnpLabs for helping us this year.

This week, Symfony 2.0.13 was released, including 120 commits with bug fixes and some nice tweaks (view changelog).

Development mailing list

• Form circular reference
• Symfony2 slow load problem?

Symfony2 development highlights

Master branch:

• dbd9568:
  [HttpKernel] memcache and Redis profiler storage update
• 991474b:
  [HttpKernel] fixed RedisProfilerStorage falling unit tests when Redis extension is not available
• c195957:
  [Components] fixed tests/autoloading and switched to Composer
• 95b8e29:
  [BrowserKit] removed dependency of CookieJar to Response
• 503a51f, f273edc:
  [HttpFoundation] updated RFC references in Response

2.0.x branch:

• 5b92b9e:
  [Console] selectively output to STDOUT or OUTPUT stream (Addresses issues with writing console output for IBM i5 Series OS400)
• 6aa4817, b66b6fc, a7868a7, 06274db:
  Released Symfony 2.0.13
• 7fe236a:
  [Security] configured ports in RetryAuthenticationEntryPoint according to router settings
• 970d0b4:
  [BrowserKit] check class existence only when required

Repository summary: 4,585 watchers (#1 in PHP, #30 overall) and 1190 forks (#1 in PHP, #12 overall).

New plugins

• sfWidgetFormAvatar: Input field will be seen as a default picture. Default picture will be automatically changed by file selected. The file will be uploaded once selected. A preview will be automatically generated once file selected / uploaded.

Updated plugins

• dcSaml:
  • fixes and tweaks
• apostrophe:
  • added simple styles for an “allow comments” checkbox
  • use of the ‘limit’ or ‘firstOnly’ option to slideshow slots no longer prevents you from successfully editing an existing slideshow
  • if a video has no thumbnail and the video slot was rendered with the thumbnailOnly flag, display a reasonable placeholder rather than a 500 error
  • new renderTemplate method in aEngineActions allows you to return from an action in a way that allows app.yml to specify an alternative partial or component in lieu of the template that would normally be used
  • BaseaMediaActions uses return renderTemplate() consistently
• apostropheBlog:
  • app_aBlog_allow_comments_individually allows comments to be toggled on and off individually for particular posts
  • app_aBlog_allow_comments_initially determines whether comments are initially allowed on a new post (true) or forbidden (false)
  • when searching the blog, respect the order of the page ids returned by the search service

They talked about us

• Common problems designers have when working with Symfony
• LexikMailerBundle, gérez vos templates de mails en base de données
• Guía de Symfony2 – Capítulo 11 – Integrando AJAX
• Symfony2 Config Component: Using FileLocator, Loaders and LoaderResolver
• Les composants Symfony 2 seront au coeur de Drupal 8
• Form CollectionType ~ Prototype
• My First Pull Request in Github
• Symfony2-träff
• Symfony2 ??IdeatoSimplePagerBundle
• Comment faire un formulaire avec Symfony2?
• Symfony2 – Ek Kutuphaneleri Yonetmek
• Mapped properties in Symfony2 and Doctrine2 Entity inheritance
• Yii1.1.10 e Symfony2 a confronto (Prima parte)
• Symfony: disable the web debug toolbar in PROD environment
• Creating and using Templates with Symfony2
• Symfony2 Forms – Cannot add/remove entities from a collection
• How to use manytomany field in symfony2?
• symfony backend?????????????? ???????????????????????
• Caché de HTTP con Symfony2
• Custom Parameters in Symfony DI
• Symfony2: nützliche Bibliotheken und Bundles
• Authorisation on Roles in Symfony2
• Update multitables using one form in symfony+Doctrine
• Using PHP extension for symfony pages

One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to degrade.

The first thing you need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many cases. Whatever powerful and well tuned system you have if you put too heavy of concurrent load on it the response times will increase and user experience will suffer.

So what you can do to prevent this problem from happening ? The answer is easy. Throttle the side load so it does not consume too much system resources. Here are some specific techniques to use.

Do push concurrency too high Many developers will test script with multiple level of concurrency and find out doing work from 32 processes is faster than just having one process. This is true if you have system completely at your disposal. If you however need system to serve other users too you typically need to reduce concurrency to where it does not overload the system. Unless it is really time critical process I would not use more than 4 parallel processes heavily writing to database.

Introduce Throttling Sometimes even single process overloads system too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea. It also often helps with monopolizing replication thread. For example if I need to delete old data instead of DELETE FROM TBL WHERE ts<"2010-01-01" I’ll do “DELETE FROM TBL WHERE TS<"2010-01-01" LIMIT 1000 in the loop until no more rows need to be deleted. When I may inject “sleep” between iterations which to be as long as query execution – so the longer queries run (and the more system is loaded) the more “rest” it will get. Alternatively you can look at “threads_running” variable which is very good simple identifier of the current load and sleep based on its value – for example you may want chose to pause the script at all if the load is too high and wait for threads_running to go below certain value.

Tuning Cron It also often helps to look into your cron or other scheduling system you’re using. Frequently way too many scripts can be started at once, or very close to each other so they start to overlap and so producing the overload. Solutions could be spacing them out, introducing some “job control” to ensure scripts do not run in parallel if they should not (and especially you do not get many copies of same script running at once). One simple solution is instead of having bunch of scripts scheduled at midnight, 1AM, 2AM to start I can put them into nightly.sh one after another and schedule that to run at midnight – this way I get scripts ran one after another at their own pace.

Dedicated Slave I remember listening to Cary Millsap’s talk once and he recommended moving the load in time and space as optimization technique. We spoke about moving load in time before, but we also can move in space – putting it on the different system, which in MySQL space is most commonly dedicated slave. In a lot of environments especially with low level of operational/development discipline to enforce previous solutions it can be a life saver. Of course it only works for read jobs which is important limitation. Getting slave(s) for batch jobs also can help in other ways too – such as competition for buffer pool between different kinds of workloads is reduced.

innodb_old_blocks_time Surprisingly simple but effective, setting innodb_old_blocks_time=1000 can often be very helpful in avoiding batch jobs washing away buffer pool contents and so making normal user queries a lot more disk bound and slower. I wrote about it in more details few months ago.

Finally lets touch upon discovery question. To deal with load management you need to understand whenever the problem is happening in your environment (we want to catch it before users complain right?) and if it does what jobs exactly cause the overload. In complex environments it might be harder question than it looks. pt-stalk is a great tool for this purpose. Getting it running can help you to collect the state of your system when it was overloaded with side load (as well as performing poorly for other reasons). Analyzing wealth of data it generate will most likely contain answers you’re looking for.

I still continue to run benchmarks of different SSD cards. This time I show numbers for Virident FlashMAX 1400. This is a MLC PCIe SSD device. There are couple notes on these results.
First, this time I use a different server. For this benchmark it is Cisco UCS C250, while for previous results I used HP ProLiant DL380 G6.

Second note is, that I use a mode “turbo=1″ for Virident card. What does that mean? Apparently PCIe specification has a limitation on available power. If I am not mistaken it is 25W, however Virident to provide full write performance requires 28W. And while many servers can handle 28W on PCIe, this is a non-standard mode, and Virident by default uses 25W (turbo=0). To force full power, I load a driver with turbo=1. I also use “maxperformance” formatting for Virident, which gives less capacity (1.2TB visible for user), but reserves internally more space to provide better write performance.

So as usually I start with random writes, async.

Soon after initial period, the result stabilizes at 550 MiB/sec level.

Random read, async:

Random read throughput is very close to perfect line, and it is 1450 MiB/sec.
This is best read throughput I’ve seen so far in my benchmarks.

To see distribution of response time, the results for random read synchronous IO.

There we can see that 1450 MiB/sec is not quite achievable in sync mode, and only 64 threads are getting close.

Response time:

In the conclusion, from all tested cards, Virident FlashMAX shows the most stable results and the best absolute performance so far.

For reference, other results in series:

• Fusion-io ioDrive
• Intel 520
• STEC MACH16
• Samsung 830

Follow @VadimTk

Following my series of posts on testing different SSD, in my last post I mentioned that SATA SSD performance is getting closer to PCIe cards. It really makes sense to test it under MySQL workload, but before getting to that, let me review the same workload on Fusion-io ioDrive PCIe card. This is yet previous generation of Fusion-io cards, but this is the one that has biggest installation base.

Driver information: Fusion-io driver version: 2.3.10 build 110; Firmware v5.0.7, rev 107053

Following the format of previous benchmarks, first is random write async 16KB case.

We can see some wave-like pattern with throughput 350-400 MiB/sec.

Random reads, async:

Interesting to see that there is quite unstable throughput in range 450-500 MiB/sec.
This is not usual for read-only workload to have such variety in throughput.

It gets even more interesting when we go to read sync IO, with 1-64 threads.
Throughput:

Response time:

For same cases (i.e. 4 threads) we see some interesting patterns.
As for response time, actually it does not seem much better than for Intel 520.
For 8 threads it is 0.6 ms ( for Intel 520 – 0.69 ms).

To better understand patterns in the read synchronous case, let me unfold results and show them in timeline (from 0 to 1800 sec):

I am not sure how to explain it, that with 4 and 8 threads the pattern is less stable than with 32 threads.

It is curios that I published results already for bunch of cards:

• Intel 520
• STEC MACH16
• Samsung 830

and each card shows individuals patterns and different handling of write and read IO cases.

Follow @VadimTk

Following my previous benchmarks of SATA SSD cards I got Intel SSD 520 240GB into my hands. In this post I show the results of raw IO performance of this card.

The benchmark methodology I described in previous posts, so let me jump directly to results.

First case is random write asynchronous 8 threads IO, the test is done just after a secure erase operation on the card.

The card is doing stable 380 MiB/sec level, but after around 4000 sec, as garbage collector kicks in, we see a performance drop to around 300 MiB/sec with some instability, which I will research in later charts.

Now, random reads, still asynchronous

It gives almost stable 370 MiB/sec throughput, with some strange small periodic drops.

To better understand response time ranges, we need to switch to synchronous IO and vary amount of threads.

Throughput:

And response times:

We still see small hiccups in throughput and response times even for small amount of threads.
For 8 threads the 95% response time is 0.69ms.

Now let me get back to random write case. I will try synchronous IO varying amount of threads and with measurements every 1 sec to see how bad are drops.

So there is more or less stable performance only for 1 thread. For 2 or more, the throughput varies a lot from second to second. I draw boxplots, which show 25-50-75 percentiles. So there is no grow in throughput after 2 threads, and the result averages at 300 MiB/sec.

I am still interesting in asynchronous IO, as MySQL 5.5 uses async IO for writes. Maybe 8 threads in the first graph is too much and we should go with 1 thread?

So even with 1 async write thread the throughput jumps a lot in range 200 – 400 MiB/sec.

As conclusion, I should say that 300 MiB/sec level for random reads and writes is very decent result for SATA card. I think with this performance SATA is getting closer to level of PCIe cards. Of course PCIe still provides better numbers, but the question is how much MySQL can use. In his keynote Mark Callaghan mentioned that Fusion-io cards they use are highly underutilized.

With the performance variance we see it is a good question how does it affect MySQL performance, and I am going to run some MySQL workloads on these cards to understand it better.

If you are interested more in SSD and MySQL questions – I will be giving a webinary “MySQL and SSD” on May-9. It will be the same as my talk on Percona Live MySQL Conference 2012, if you did not attend my talk – you are welcome to join the webinar.

Follow @VadimTk

Symfony 2.0.13 has just been released.

The CHANGELOG
has all the details about the changes and you can even have a look at the full
diff.

If you are starting a new project, you can get the Symfony Standard Edition
distribution on the download page.

If you already have a project based on the Symfony Standard Edition 2.0.x, you
can easily upgrade to 2.0.12 by getting the new
deps and
deps.lock
files.

Then, run the vendors script (it also clears your cache):

$ ./bin/vendors install

Remember that the Symfony2 Components are also available as standalone
libraries. You can get them via their dedicated read-only repositories on
Github (https://github.com/symfony/Finder for instance), install them via
PEAR (pear install symfony2/Finder), or even
install them via Composer.