Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 30

Displaying posts with tag: Tools/ dbSTRESS (reset)

MySQL Performance: Overhead of Optimizer Tracing in MySQL 5.6
Employee +4 Vote Up -0Vote Down

Last week I was faced to a strange problem while benchmarking MySQL 5.6: some of my user sessions become "frozen" periodically and finally my whole test was not valid..

More in-depth analyze shown that:

  • sessions are "frozen" when executing a SELECT statement..
  • sessions are not really frozen, but waiting on read!..
  • the read seems to be a kind of long read, involving many I/O operations..
  • all makes think that query is execution a full table scan (200M rows, 40GB table) and ignoring indexes, so instead of 1ms I have >5min response time..

But the problem is completely "random" - it's not happening all of the time and not in all



  [Read more...]
MySQL Performance: 5.6.4 @dbSTRESS Benchmark
Employee +3 Vote Up -0Vote Down
MySQL 5.6.4 milestone is here and I'd like to share some benchmark results I've obtained with it on dbSTRESS workload.

First of all, I was curious to observe a single user session performance (I did not look on it for a while, and there were several persons reporting we got a performance degradation in 5.6 comparing to 5.5 and 5.1).. - Seems it's very workload depending, because on dbSTRESS I've obtained the following:

Read-Only :
  • 5.1 : 1800 TPS
  • 5.5 : 1750 TPS (but sometimes jumps to 2700 TPS)
  • 5.6.2: 2600 TPS
  • 5.6.4: 2600 TPS
Read-Write :
  • 5.1: 2700 TPS
  • 5.5: 2600 TPS
  • 5.6.2 : 3300 TPS




  [Read more...]
MySQL Performance: 5.5 and InnoDB btr_search_latch
Employee +4 Vote Up -0Vote Down

This post continues the story started during the previous testing with MySQL 5.5.6 where I've observed a hot contention on the btr_search_latch and was curious to get a more detailed view on this performance issue.. Well, the story is starting here, but you never know what will be at the end.. ;-))

So, I've started with the same initial test workload I've before:

  • dbSTRESS injector
  • READ transaction: SELECT1 + SELECT2 queries
  • WRITE transaction: UPDATE only queries
  • think time between transactions: 0 sec.
  • Concurrent user sessions: 1, 2, 4, 8, .. 256 users (growing
  [Read more...]
MySQL Performance: 5.5 Notes..
Employee +4 Vote Up -0Vote Down

Since 5.5 is announced as Release Candidate now, I'll not compare it with 5.1 anymore - I think there was written enough about the performance gain even since introduction of 5.4 :-)

From the other side, we want to be sure that the final 5.5 will be at least as good as 5.5.4 release, and here the feedback from real users with real workloads will be very precious! So, please, don't keep quiet! :-))

After that, few notes while testing MySQL 5.5 on dbSTRESS and points for discussions..

Buffer Pool and Purge

Last month I've got some time to focus on the Buffer Pool pages management I've observed many months before with older MySQL/InnoDB versions, and now was curious if I'll reproduce the same issue

  [Read more...]
MySQL Performance: InnoDB IO Capacity & Flushing
Employee +5 Vote Up -0Vote Down

This article was initially inspired by Vadim's post on mysqlperformanceblog.com comparing MySQL 5.5.4 and XtraDB under TPCC-like workload. Vadim's post has opened an interesting discussion about InnoDB I/O capacity feature in general, and particularly - tunning / settings regarding 5.5.4 and XtraDB. However, going more in depth I've discovered more interesting things than I've expected initially :-) and here is a kind of summary of my observations..

The following stuff is mainly covering heavy Read+Write workloads as I/O capacity was added to InnoDB to improve page writes rather reading ;-)

First of all - why I/O capacity in InnoDB?..

InnoDB

  [Read more...]

MySQL Performance: Using Performance Schema
Employee +4 Vote Up -0Vote Down

Two weeks ago I was very lucky to meet in Paris Marc Allf (one of developers of MySQL Performance Schema) and get all details about Performance Schema directly from the source :-)

I would say I'm very excited about this feature giving so enormous potential to observe any activity inside of MySQL, as well by a great flexibility of the proposed solution! :-)

However, discussing with Marc and going over a day more and more in depth, I've got some remarks that I needed to group, summarize and then express (and vacation time helping here a lot ;-)) My points will not be about adding some new extensions, but rather fixing some core issues which I consider important (and, please, correct me if you're not agree, express your ideas, etc - it's one of the cases where a

  [Read more...]
MySQL Performance: Improving Stability
Employee +1 Vote Up -0Vote Down

Huge performance improvement was came with MySQL 5.5.4, and looking on the following picture it's very easy to see why:

It's a one hour Read+Write dbSTRESS workload with 32 sessions running non-stop on 16 cores server. The left part of the picture corresponds to MySQL 5.1 activity, and the right one to MySQL 5.5.4. The first graph represents TPS levels, and the second one - the mutex waits observed during each test.

Even without going in details you may see how dramatically were reduced mutex waits! And I would say one of the greatest InnoDB features was a compact presentation of the "show innodb mutex" output which gave the way to monitor InnoDB more in

  [Read more...]
MySQL Performance: 5.5.4 @dbSTRESS
Employee +1 Vote Up -0Vote Down

Today we have something to celebrate - the MySQL 5.5.4 was announced! And a huge number of performance improvement made in this version make me very happy :-))

I've got an immense pleasure to participate in this story and seek for the most optimal solutions together with all these talented engineers from MySQL/Sun/InnoDB/Oracle teams (and in few months all Oracle :-)) - and I think it was a huge step ahead to see how well we may all work together :-))

And now I'm happy to share with you my results obtained with MySQL 5.5.4 on dbSTRESS benchmark. All tests were executed on the 32cores Intel server running Fedora 10 and having 128GB RAM and absolutely fast internal SSD drives (and if from the memory I've used only 16GB for the buffer pools, the SSD drives helped

  [Read more...]
MySQL Performance: Breaking limits with XtraDB !
Employee +4 Vote Up -0Vote Down

I've just finished my benchmark report about InnoDB current hottest internal contentions on the dbSTRESS workload. Since InnoDB-plugin-1.0.4 there is an excellent feature was added by Innobase team to monitor internal mutex lock waits: a compact output of "SHOW MUTEX STATUS" become much more useful then before! and helps greatly to understand InnoDB limits!

I've integrated innodbMUTEX stats within dim_STAT monitoring and was able to see performance limits from completely different axes!

Initial results

Initially I've retested again:

  • MySQL Perf-build5
  • MySQL 5.4
  • InnoDB-plugin-1.0.4
  • XtraDB-8

  [Read more...]
MySQL Performance: Conference Session @Paris
Employee +1 Vote Up -0Vote Down

I'm presenting during MySQL Performance Session in Paris which will take place on 24th November in our office building - Sun Solution Center, 32 rue Monceau, 75008 Paris. I did not blog about to avoid to increase frustration for all people who cannot attend it.. Because even we reserved for this even the biggest conference hall in the building there is still not enough place to accept everybody.. As well we did not expect so high interest - the first invitations were sent in priority to MySQL customers, and in few days there was already no more place..

So, why I'm writing now? ;-) Just because I'm pretty sure there will be a second session! :-) I cannot say you date & place yet because it'll directly depend on the number of persons willing to attend. And if you have a

  [Read more...]
MySQL Performance: Current performance levels of 5.4, XtraDB-8 and Innodb-1.0.4 @dbSTRESS
Employee +2 Vote Up -0Vote Down

This is a short post just to make a point on the current performance level of available InnoDB engine implementations. I've made the current testing not in the goal to isolate performance issues, but to see how well the code works now when it meets the business constrain to flush on every transaction!..

The problem is that I've tested recently Oracle 10g with dbSTRESS during several customer's demands and was curious how well MySQL competes now with Oracle :-)) Due Oracle license restrictions I cannot present any Oracle results here (sorry folks, I still need my job :-)) But I've put MySQL into the similar conditions and was curious to see what is the current performance level we may expect on the same platform..

Until now I've always used

  [Read more...]
Performance Trainings & Conferences in Kiev
Employee +2 Vote Up -0Vote Down

Last week I've participated in several Performance Conferences and Trainings in Kiev. Events mainly were dedicated to the latest improvements within MySQL performance, as well Solaris and Oracle tuning / performance analyzing and best practices. The week was very hot :-) but I've enjoyed every day discussing with creative and smart people! People hungry on knowledge and ready to stay 2 hours more overnight outpassing all allowed timing - it was a real pleasure to share all I know! :-))

So, I'd like to thank all participants! As well Sun@Kiev and i-Klass teams who organized and prepared all these events! I may only wish to see such events more and more often, and don't forget to invite me again! :-))

  [Read more...]
MySQL Performance: I/O Optimization & InnoDB
Employee +3 Vote Up -0Vote Down

After my previous post about InnoDB Doublewrite Buffer impact I've received several feedbacks where people are claiming to see an important performance degradation when they enabling doublewrite... Discussing with them about their wokloads I've tried to reproduce a similar activity with dbSTRESS tool. And finally I was happy (or unhappy - all depends on the side :-))

  • Happy because I was able to reproduce the problem :-)
  • And unhappy because I've got 30% performance degradation by activating doublewrite buffer!

And now I'll tell you a story how to fix it :-))

Sorry, it'll be little bit long (but it was hard to make it shorter

  [Read more...]
MySQL Performance: InnoDB Doublewrite Buffer Impact
Employee +2 Vote Up -0Vote Down

Recently Mark Callaghan(who I trust a lot) published on his facebook blog a post about a Doublewrite Buffer impact . And Sarah Sproehnle (who I trust a lot too :-)) commented it by saying:

..the doublewrite buffer is actually a benefit. Beyond guaranteeing that pages are recoverable, it also reduces the required fsyncs. Without it, each page that is written to the tablespace would need to be fsync'ed. With doublewrite enabled, a chunk of pages is written to the doublewrite buffer then 1 fsync is called, then pages are written to the
  [Read more...]
MySQL Performance: Final fix for Ahead Flushing & Purge Lag
Employee +11 Vote Up -0Vote Down

The goal of this post is to tell you a final story about performance study looking to fix the Ahead FLushing and Purge Lag issues.. (I've tried to make it short, but if you feel you'll need more details or just have some more time for reading - go directly to the full report: http://dimitrik.free.fr/db_STRESS_MySQL_540_Purge_Lag_and_Ahead_Flushing_Fixed_Aug2009.html  :-))

Before to tell you the story about the final solution, let me show you again the TPS level graph obtained during my last tests:

 

The read+write workload is executed during one hour non-stop on 4 engines

  [Read more...]
MySQL Performance: InnoDB plugin-1.0.4 & others @dbSTRESS
Employee +1 Vote Up -0Vote Down

This post is an update of my previous one about XtraDB-6 performance - as InnoDB plugin-1.0.4 announce came the same day I did not have yet any benchmark results on that time :-)

To be short, the new InnoDB plugin looks very positive and have several very valuable improvement (and of course we expected to see them much more earlier, no? ;-) on the same time analyzing all latest updates - probably it's the first sign that things will go much more faster in the near future? ;-)

Anyway, what I liked with this release:

  • group commit is back! (and we should thank a lot Percona team for their efforts to get it fixed! ;-)
  • configurable number of I/O threads and I/O capacity (aligned now with XtraDB, Google patched and MySQL 5.4)
  [Read more...]
MySQL Performance: XtraDB-6 & others @dbSTRESS
Employee +0 Vote Up -0Vote Down

I'm happy to present you my first benchmark results with XtraDB-6 on dbSTRESS. Percona team made a huge work preparing this release and there are really a lot of improvements regarding performance as well general usage (for more details about XtraDB-6 see the full announce  from Percona site).

But my main interest is around performance (sorry :-)), and I was curious how well now XtraDB-6 resists to the stress workload. New release also integrating the "timed based" concurrency model introduced within MySQL 5.4 - missing this feature was negatively impacted XtraDB in previous tests. But now we may expect it runs at least as fast as MySQL 5.4! Let's see...

  [Read more...]
MySQL Performance: Why Ahead Flushing for InnoDB?...
Employee +2 Vote Up -0Vote Down

Recently I've done several performance studies to find the most optimal way to fix the "furious flushing" observed during all my benchmarks with InnoDB. The initial report about this work you may find here including initially proposed patches. But analyzing test workloads with the latest MySQL performance build I think there is a need to go more far...

First of all let's me present you the result I've got on the Read+Write workload @dbSTRESS - first part of graph represents the TPS level obtained with MySQL 5.4, and the second part - with the latest MySQL performance build (#46):

  [Read more...]
MySQL Performance: Once again about InnoDB thread concurrency...
Employee +2 Vote Up -0Vote Down

Even this topic was covered several times already, the question is still open - use or not to use InnoDB thread concurrency?.. And the only true answer here - "It depends" :-) and I'll try to make a short summary about the current state here.

The main current InnoDB bottlenecks are all mostly due to a high contention on internal locks. As all multi-threaded (as well multi-process) applications giving a concurrent access to its resources, InnoDB have to guarantee there is only one thread may modify a given resource on the same time. This guarantee is achieved by locking a resource in exclusive mode and implemented with mutexes. Depending on implementation, some locks may become very hot with a growing access concurrency. And it's exactly what's happening with InnoDB...

  [Read more...]
MySQL Performance: 5.4 and new Performance builds
Employee +1 Vote Up -0Vote Down

Recently new performance builds were made by our MySQL Team, and it seems to me reaching now a new milestone with the build #45 - within this single build Mikael joined several previously tested performance builds showed a positive performance improvement. So, how well it's comparing now to the default 5.4 ?..

Here are my results obtained on M5000 (quad-cores)/ Solaris10 update7 and db_STRESS.

Solaris 10 update7

Special note about update7: I was pleasantly surprised the kernel locks issue was gone! CPU Spinlocks within a similar workload are devided by 4(!) - very positive improvement, and

  [Read more...]
MySQL Performance: InnoDB Purge Lag and Ahead Flushing
Employee +1 Vote Up -0Vote Down

After publishing in May a benchmark report about InnoDB Dirty pages & Log size impact I received several very interesting comments, and one of them pointed to the purge lag problem and InnoDB innodb_max_purge_lag parameter created specially for such purpose! So, I was very curious to know how it may help me in my workload...

The following study is about how innodb_max_purge_lag is working now and what may be done to make things better :-))

I'm not pretending here on any absolute truth :-) My goal is to understand what's going on with purge on my workload and see if there is something else possible to improve..

  [Read more...]
MySQL Performance: MySQL 5.4 and other InnoDB engines on 32cores AMD server & dbSTRESS
Employee +1 Vote Up -0Vote Down

Currently several probe InnoDB code improvements were done by our MySQL Team. I was happy to test them with db_STRESS workloads but on Solaris/SPARC server (M5000). Then discussing with Mikael  I was surprised he saw much less improvement from the latest probe builds on his Linux/AMD64 box... And it was unclear why the performance improvement gap is more important on SPARC: due SPARC itself? due Solaris? due a test case?.. To bring more lights and understand better what's going differently on an AMD box I've preferred to avoid to change too many things on the same time :-) So, once one of the latest 32cores AMD server (X4600-M2) was available, I was curious to test it under Solaris10 and connected to the same storage box as M5000 before. And here are my results...

My intention is

  [Read more...]
MySQL Performance: Some results comparing InnoDB log size impact @dbSTRESS benchmark
Employee +1 Vote Up -0Vote Down

Following my previous post (where I've also told about a significant performance gain by using a bigger InnoDB log size ), I'd like to present you some results obtained on the Read+Write workload and log size equal to 1024MB. I've labeled it on graphs as "FF" (Fast & Furious :-)) as within a such configuration MySQL server may go very fast until it'll meet a "furious flushing" to free some space within a log file... :-)

However, the speed-up is quite important, so if you don't worry too much if time to time your production activity may have a short drop on performance - you have to consider this option as one of the first to test to improve your

  [Read more...]
MySQL Performance: InnoDB Dirty Pages & Log Size Impact
Employee +3 Vote Up -0Vote Down

The beginning of this story is coming from the first weeks of the last year - seeking for the most optimal MySQL config parameters I've discovered a strange thing:

  • my dirty pages percentage setting 15 was completely ignored by InnoDB during my tests!...
  • once the test workload was finished it still took 30 minutes yet to flush dirty pages!...

I've told about a lot of people on that time, but everybody were just surprised and did not really believe such case is possible... Agree, it's hard to believe ;-)

So, to refresh the story, let me first to show you the following picture:

  [Read more...]
So, Why finally PostgreSQL is slower on db_STRESS Benchmark comparing to MySQL ?...
Employee +2 Vote Up -0Vote Down

Last month when I've published my results obtained with PostgreSQL 8.3.7 on db_STRESS benchmark I was surprised they were much lower comparing to MySQL, as well some signs alarmed me there is something goes wrong with PostgreSQL... So, once it was possible, I took my time and prepared another testing on the same M5000 server I published MySQL results last week.

I would say I discovered a lot of new things benchmarking PostgreSQL ! I'm not kidding :-) And without going too much in detail, the main gain seems to me of MySQL over PostgreSQL was a lower cost on executing a single query. The query in question was the second SELECT in db_STRESS.

SELECT-2 execution time:

  • MySQL 5.4: 0.44ms
  • PostgreSQL 8.4:
  [Read more...]
MySQL Performance: MySQL 5.4, XtraDB-5 and others @dbSTRESS Benchmark
Employee +2 Vote Up -0Vote Down

Here are my new db_STRESS results obtained on the latest Sun M5000 server (SPARC64-VII 8CPU quad-core / bi-thread 2400Mhz). I was very curious to retest the "official" MySQL 5.4 on this machine as well other versions and InnoDB engines. But on the same time the new XtraDB version 5 was announced. As well Google shipped their v3 patch. How to resist? :-) (I'll try to be short :-))

MySQL 5.0 & 5.1

There is no more performance gap between 5.0 and 5.1 as I observed before , both versions are having near the same performance and both are way slower comparing to others :-) So, honestly, if you have any performance problem - why do not migrate to 5.4, XtraDB, or at least the

  [Read more...]
dbSTRESS Benchmark kit updated!
Employee +0 Vote Up -0Vote Down

In case you also want to replay the same dbSTRESS test scenarios I've posted results yesterday, I've added into the test kit a shell script which may be used as a Reference Scenario.

More details on how to use it is here .
As well dbSTRESS kit and User Guide are available for download from http://dimitrik.free.fr

MySQL Performance: 5.4 outperforms PostgreSQL 8.3.7 @dbSTRESS !
Employee +0 Vote Up -0Vote Down

Forget to say, I've also tested PostgreSQL 8.3.7 during the last benchmark serie with dbSTRESS!

A big surprise - if two years ago on the same workload PostgreSQL was two times faster (see: http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html ), now it's MySQL 5.4 outperforming PostgreSQL!

  • Read-Only workload: MySQL is near two times faster now! (13.500 TPS vs ~7.000 TPS for PostgreSQL)

  • Read+Write workload: MySQL performs as well or better (7.000-8.000 TPS vs 6.000-7.000 TPS for PostgreSQL)

For more details: http://dimitrik.free.fr/db_STRESS_MySQL_540_and_others_Apr2009.html#note_5443  

MySQL Performance: MySQL 5.4 and other InnoDB engines @dbSTRESS Benchmark
Employee +0 Vote Up -0Vote Down

As you may already know, today Sun announces MySQL 5.4. It's a big step forward and I hope you'll have enough time today to follow all blog posts and other interesting related publications! :-)

I leave my colleagues to tell you what is new in 5.4 and what will come with next releases! (start with Mikael's and Allan's blogs). As well you will not have a whole story if you miss Tim's, Neel's, Richard's, Vince's, David's and many other valuable blogs posts!

From my part I want

  [Read more...]
MySQL Performance: MySQL 5.4 and other InnoDB engines @dbSTRESS Benchmark
Employee +0 Vote Up -0Vote Down

As you may already know, today Sun announces MySQL 5.4. It's a big step forward and I hope you'll have enough time today to follow all blog posts and other interesting related publications! :-)

I leave my colleagues to tell you what is new in 5.4 and what will come with next releases! (start with Mikael's and Allan's blogs). As well you will not have a whole story if you miss Tim's, Neel's, Richard's, Vince's, David's and many other valuable blogs posts!

From my part I want

  [Read more...]
Showing entries 1 to 30 of 30

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.