In benchmarks passion (see my two previous posts) I managed to setup all three devices (RAID was on board; Intel X25-E SSD connected to HighPoint controller, FusionIO card) on our working horse Dell PowerEdge R900 (btw, to do that I had to switch from CentOS 5.2 to Ubuntu 8.10, as CentOS was not able to start with attached SSD card to HighPoint controller) and along with other tests I ran tpcc-like IO-bound workload on all devices.

For tests I used MySQL 5.4/InnoDB, and all other parameters are the same from previous posts (100W, buffer_pool 3GB). Filesystem - XFS mounted with nobarrier option.

Graphical results are here

and average results:

RAID10 - 7439.850 TPM
SSD - 10681.050 TPM
FusionIO - 17372.250 TPM

However what should be noted - both SSD and FusionIO are run in "non-durable" mode, that is you may lose some transactions in …

MySQL 5.4 comes with Innodb engine which seems to have much better performance than MySQL 5.0 - this is due to locking and IO patches from Google integrated in this release (which are similar to appropriate Percona patches) as well as some unique fixes such as different innodb_thread_concurrency handling and other optimization.

Should we take Innodb from MySQL 5.4 and merry it with unique Percona patches (adaptive checkpoints, additional undo slots, profiling, etc) and integrate it with MySQL 5.0 ? How useful would you find it ?

Currently we see a lot of customers not quite ready to update to MySQL 5.1 in particular as there is little in this version which benefits their workload, which consists of the queries running just fine on 5.0.
5.4 especially introduces optimizer changes which besides positive impact for some queries may …

Assume you have a table with about 300 000 rows, and an indexed column ‘col1′ with only 9 distinct values. Now you got a query like ’select * from t1 where col1 = const’. The questions are

- when the index is faster to full table scan and vice versa?
- does MySQL use the optimal plan by default?

These questions became very relevant now that QOT got server access and is able to gather various table metrics including selectivity. Besides index selectivity the threshold value obviously depends on the storage engine used, so for me it is also interesting to see how our PBXT engine compares to others in this aspect. Namely to InnoDB - an engine with similar transactional properties and MyISAM - a very fast engine for read-only scenarios.

For the test I took the …

Somewhat overshadowed in last week's headlines was news about the forthcoming MySQL 5.4 release, internally known as "Summit." While MySQL Engineering team was somewhat heads-down last year finalizing MySQL 5.1, this new version demonstrates a dramatically shorter release cycle by focusing on just two key issues: performance and scale.

read more

“We’re both in the transportation business. We have a 747, and they have a Toyota.”

The comparison of Oracle’s database and MySQL, made by Oracle president Charles Phillips at the 2004 Vortex Conference was undoubtedly meant as a criticism, but it so graphically demonstrated the differing business strategies and selling-points of the two products that MySQL executives began citing it themselves.

It is also a comparison that explains how the two products could potentially co-exist within a single company, as they seem likely to do following the announcement that Sun has agreed to be acquired by Oracle.

Much of the MySQL-related coverage of the impending acquisition has focused on the likelihood of Oracle killing-off the …

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 turned out to be a success. Even on
machines with only one …

It was a standing room only crowd at the MySQL Conference and Expo this morning in Santa Clara. With more than 2,000 attendees, this is the largest crowd the conference has ever drawn, which is saying something given that most conferences are projecting much lower numbers with the economic downturn. Perhaps open source is counter-cyclical after all and will continue to do well in tough times.

read more

There is much excitement today at the launch of MySQL 5.4, so I will relate my story about a project I contributed to this new version.

When we started looking at performance improvements for MySQL, we were interested in "low hanging fruit", or fixes and changes that could reap measurable benefits for users in the short term.

An obvious candidate at that time was the now well-known Google SMP patch. I had seen Mark Callaghan present on this at the MySQL User Conference in 2008, and was interested to investigate.

I was pretty new to InnoDB at that time, and was soon to discover that InnoDB was possibly experiencing poor scalability around its mutexes and read-write locks because InnoDB had a private implementation of adaptive mutexes and …

Today Sun announces MySQL 5.4. This is a great day for customers as they can use systems with many cores much more efficiently. Its a great day for the MySQL community and the MySQL performance team because we made it happen. MySQL 5.4 includes a lot of community contributed fixes as well as many fixes from our team. Mikael and Allan are blogging about all the cool new features and the great scalability of MySQL 5.4. I thought I will take this opportunity to blog about some of the things we tried, and rejected. Sometimes there are a lot of things to be learnt from things that do not work

Early on during our performance investigation, we were trying to see if we can reduce some of the contention in Innodb locks. If you are not familiar with Innodb locks, I suggest you read …

The release of the MySQL 5.4 contains patches which
increases the scalability of the MySQL Server. I am planning to blog
about those changes in some detail over the next few days. This blog
will give an introduction and show what the overall results we have
achieved are.

The changes we have done in MySQL 5.4 to improve scalability and
the ability to monitor the MySQL Server are:

1) Google SMP patch
2) Google IO patches
3) Update of many antiquated defaults in the MySQL Server
4) New InnoDB Thread Concurrency algorithm
5) Improved Spinloop in InnoDB mutexes and RW-locks
6) A couple of performance fixes backported from 6.0
7) Operating system specific optimisations
8) Ported the Google SMP patch to Solaris x86 and SPARC and work
underway for Windows and Intel compiler as well
9) Introducing DTrace probes in the MySQL Server …