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 18

Displaying posts with tag: MySQL 5.4 (reset)

Found a nasty COALESCE() related bug in 5.5.6-rc
+2 Vote Up -0Vote Down

Seeing as it looks like 5.5 is shortly about to go GA I thought I’d give it a run and see how well it works. The only way really to test it is to give it a bit of load and look for things which break. That I did with the 5.5.6-rc community rpms, compared to the 5.1 advanced rpms I usually run.

My colleagues, Ben and Peter, found a horrible problem which means that I can’t use this even for any real usage on my real servers. See: bug#57095 for all the gory details. Thanks to them both for finding the problem and then digging down and figuring out the real cause. Sometimes developers work a long way from the database so their errors don’t translate into something I can really look at in the database. They delved into the problem and then found the cause and a nice easy test

  [Read more...]
Video: Giuseppe Maxia Presents MySQL 5.4
+3 Vote Up -0Vote Down

At the May 2009 Boston MySQL User Group, Giuseppe Maxia of Sun Microsystems gave a presentation about MySQL 5.4 with use cases and benchmarks to show how it outperforms all other current MySQL releases (including the Google patches and the OurDelta/Percona releases).

The slides can be downloaded from http://www.slideshare.net/datacharmer/mysql-54-theory-and-practice

The video can be watched below, or directly on YouTube at http://www.youtube.com/watch?v=giT4SYXuVqA&hl

What’s new in MySQL 5.4.1
+0 Vote Up -0Vote Down

Absolutely nothing?

5.4.0 was released with a change in the MySQL Binary distributions, delivering only 1 64bit Linux platform and two Sun Solaris platforms. This was officially announced on April 21 2009 (http://www.mysql.com/news-and-events/generate-article.php?id=1602) however the 5.4.0 Release Notes state 05 April 2009. So it’s not a big deal, but consistency would be nice.

I’ve seen in a few posts 5.4.1, so I decided to try it out. Spending the time to read what’s changed in 2 months with the 5.4.1 Release Notes before I go downloading and installing, you read.

This release does not differ from 5.4.0

  [Read more...]
Employee +0 Vote Up -0Vote Down

I reported a bug about SHOW BINLOG EVENTS not working with relay logs a couple of years ago - Bug #28777.

It’s now been fixed in MySQL 5.4, by adding a new SHOW statement - SHOW RELAYLOG EVENTS.

The replication team are really hammering through things at the moment - Kudos!

Follow-up Analysis of Split Rollback Segment Mutex
Employee +1 Vote Up -0Vote Down
I performed a new set of tests of the patch to split the
rollback segment mutex on Linux. All these tests gave
positive results with improvements in the order of 2%.

One could also derive from the results some conclusions.
The first conclusion is that this split mainly improves
things when the number of threads is high and thus
contention of mutexes is higher. At 256 threads a number
of results improved up to 15%.

The numbers on lower number of threads were more timid
although in many cases an improvement was still seen.

What was also noticeable was that the sysbench read-write
with less reads which makes the transactions much shorter
the positive impact was much greater and the positive
impact on long transactions was much smaller (+0.4%
versus +2.5%). The impact on the short

  [Read more...]
Results of shootout on split page hash in InnoDB
Employee +1 Vote Up -0Vote Down
I have now tried out the buffer split page hash patches on
both a Linux/x86 box and a SPARC/Solaris server (tests done
by Dimitri).

The three variants in short description are:
1) The Google v3 derived patch. This introduces a new array
of mutexes that only protect the buffer page hash. Thus some
extra checking is needed to ensure the page hasn't been
removed from the hash before using it. This is a very simple
and attractive patch from that point of view. The patch uses
an array of 64 mutexes.

2) A variant I developed with some inspiration from the Percona
patches. This patch uses an array of page hashes which each has
its own read-write lock. I've tried this with 1, 4 and 16 page
hashes and 4 is the optimum number. The rw-lock protects the
page hash long enough to ensure that the

  [Read more...]
MySQL 5.4 Webinar
Employee +1 Vote Up -0Vote Down
The quality of MySQL 5.4.0 is very high for a beta product.
Four weeks after we released it as beta we have not had
any real serious bugs reported yet. There are some issues
due to deprecation of features, version numbers and a
bug in the SHOW INNODB STATUS printout and some concerns
with the new defaults when running on low-end machines.
It's also important as usual to read the documentation
before upgrading, it contains some instructions needed to
make an upgrade successful. The upgrade issue comes from
changing the defaults of the InnoDB log file sizes.

For those of you who want to know more about MySQL 5.4.0
and it's characteristics and why you should use it, please
join this webinar (http://www.mysql.com/news-and-events/web-seminars/display-343.html) where Allan Packer will explain what
has been done in MySQL

  [Read more...]
Patches ready for buf page hash split shootout
Employee +1 Vote Up -1Vote Down
Today I created a patch that builds on the Google v3
patch where I added some ideas of my own and some ideas
from the Percona patches. The patch is here.

Here is a reference to the patch derived from the Google
v3 patch.

Here is a reference to my original patch (this is likely to
contain a bug somewhere so usage for other than benchmarking
isn't recommended).

So it will be interesting to see a comparison of all those
variants directly against each other on a number of benchmarks.
Analysis of split flush list from buffer pool
Employee +3 Vote Up -0Vote Down
In the Google v3 patch the buffer pool mutex have been
split into an array of buffer page hash mutexes and a
buffer flush list mutex and the buffer pool mutex also

I derived the patch splitting out the buffer flush list
mutex from the Google v3 patch against the MySQL 5.4.0
tree. The patch is here.

I derived a lot of prototype patches based on MySQL 5.4.0
and Dimitri tried them out. This particular patch seems
to be the most successful in the pack of patches we
tested. It had a consistent positive impact.

The main contribution of this patch is twofold. It
decreases the pressure on the buffer pool mutex by
splitting out a critical part where the oldest dirty
pages are flushed out to disk. In addition this patch

  [Read more...]
Shootout of split page hash from InnoDB buffer pool mutex
Employee +0 Vote Up -0Vote Down
One of the hot mutexes in InnoDB is the buffer pool mutex.
Among other things this mutex protects the page hash where
pages reside when they are in the cache.

There is already a number of variants of how to split out
this mutex. Here follows a short description of the various

1) Google v3 approach
Ben Hardy at Google took the approach of using an array of
mutexes (64 mutexes) and this mutex only protects the
actual read, insert and delete from the page hash table.
This has the consequence of a very simple patch, it means
also that when the block has been locked one has to check
that the owner of the block hasn't changed since we didn't
protect the block between the read of the hash and the
locking of the block, thus someone is capable of coming in
between and grabbing

  [Read more...]
More analysis of InnoDB Thread Concurrency
Employee +0 Vote Up -0Vote Down
When I worked with Dimitri on the analysis of the
Split Rollback Segment Mutex he came up with numbers
on InnoDB Thread Concurrency set to 16 and 32 and I was curious
to see if 24 was the optimal setting. So he made some new runs and
some new graphs that I found interesting.

The first graph analyses behaviour of MySQL 5.4.0 on a SPARC
Server using InnoDB Thread Concurrency set to 0, 16, 24 and 32.
Interestingly for both readonly and readwrite benchmarks the
optimal setting for concurrency is 16 whereas the top numbers
(at 32 threads) is achieved with concurrency set to 24 or 32.

  [Read more...]
Analysis of Split of Rollback Segment Mutex
Employee +0 Vote Up -0Vote Down
When I read the blog about Split Rollback Segment Mutex,
I was interested to verify those results in the context of MySQL 5.4.0.

The patch can be found here.

We've analysed this patch both on a large SPARC system and on my
benchmark x86/Linux machine. Our results tend to be positive for
readwrite benchmarks but sometimes negative for readonly
benchmarks. Also the gain is much smaller than found in the

Also this patch has two negative effects, the first is that it
provides an upgrade problem, this can probably be handled in the
InnoDB code, but requires quite some digging. The other is that
instead of writing UNDO results to one

  [Read more...]
More data on InnoDB Thread Concurrency
Employee +1 Vote Up -1Vote Down
Here is the performance graph comparing using
InnoDB Thread Concurrency equal to 0 and
InnoDB Thread Concurrency equal to 24 using
sysbench readwrite with the new InnoDB
Thread concurrency algorithm as introduced
in MySQL 5.4.0.

Analysis of Google patches on 4,8 and 12 cores
Employee +1 Vote Up -0Vote Down
One of the goals we had originally with the MySQL 5.4
development was to improve scaling from 4 cores to
8 cores. So in my early testing I ran comparisons of
the Google SMP + IO + tcmalloc patches on 4, 8 and 12
cores to see how it behaved compared with a stock
MySQL 5.1.28 version (Note the comparison here was
done on a very early version of 5.4, 5.4.0 have a
set of additional patches applied to it).

What we can see here is that the Google SMP patch and use
of tcmalloc makes a difference already on a 4-core server
using 4 threads. On 1 and 2 threads the difference is only

  [Read more...]
A quick test with MySQL 5.4
+1 Vote Up -0Vote Down

Louis ILouis II Louis IIILouis IVLouis VLouis VILouis VIILouis VIIILouis IXLouis X (dit le Hutin)(the Quarrelsome)Louis XILouis XIILouis XIIILouis XIVLouis XVLouis XVILouis XVIILouis XVIIIet plus personne plus rien..and nobody, nothing more…qu'est-ce que c'est que ces gens-làwhat’s with those peoplequi ne sont pas foutuswho can’t evende compter jusqu'à vingt?count up to twenty?
Jacques Prevert

I did a quick test with the last version on the same testbed environment that I used for my previous blog and here is my finding.
MySQL 5.4 worked as I expected with sysbench. Performance up to 32 threads are basically the same of my build, but whilst 64 concurrent threads show a degradation in performance of the standard build and my build, with 5.4 they increase significantly, showing a pretty linear scalability - see table and graph.

  [Read more...]
Data on MySQL Performance
Employee +1 Vote Up -0Vote Down
If you like to sift through tons of benchmark data about various
MySQL versions, Dimitri at the Sun Benchmark Labs have published
a serious amount of benchmark data in a report published here.

The report shows that the new MySQL 5.4.0 release
have a very good performance. The report also shows how the day
of a developer of performance improvements and the massive amount
of benchmark data that needs to be analysed and sifted through
to understand the impact of new performance improvements.

I personally met Dimitri the first time in 2002 when I was working
together with him for a couple of weeks on a benchmark on NDB Cluster
(the storage engine of MySQL Cluster). Our goal then was to perform
1 million reads per second on a 72-cpu

  [Read more...]
MySQL 5.4 Patches: Improvements to spin-loop
Employee +1 Vote Up -0Vote Down
In InnoDB there is an implementation of both mutexes
and RW-locks. The RW-locks implementation have been
improved by the Google SMP patches. Both of these
implementation relies on spin-loops as part of their
implementation. The defaults in InnoDB is to check
the condition, if it's not ok to enter to spin for
about 5 microseconds and then come back to check the
condition again.

If one reads the Intel manual how to do spin-loops
they propose to use a PAUSE instruction and then
check the condition again, so a much more active
checking of the condition. When we tried this out
using the sysbench benchmark we found that using
the Intel approach worsened performance. So instead
we tried an approach of putting the PAUSE instruction
into the InnoDB spinloop instead.

This approach

  [Read more...]
DTrace, opensolaris and MySQL Performance
Employee +0 Vote Up -0Vote Down
Currently I'm working hard to find and remove scalability
bottlenecks in the MySQL Server. MySQL was acquired by Sun
10 months ago by now. Many people have in blogs wondered what
the impact has been from this acquisition. My personal
experience is that I now have a chance to work with Sun
experts in DBMS performance. As usual it takes time when
working on new challenges before the flow of inspiration
starts flowing. However I've seen this flow of inspiration
starting to come now, so the fruit of our joint work is
starting to bear fruit. I now have a much better understanding
of MySQL Server performance than I used to have. I know fairly
well where the bottlenecks are and I've started looking
into how they can be resolved.

Another interesting thing with Sun is the innovations they have
done in a number

  [Read more...]
Showing entries 1 to 18

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.