I recently did a disk bound DBT-2 benchmarking on SSD/HDD (MySQL 5.4.0, InnoDB). Now I'm pretty confident that storing tables on SSD, redo/Binlog/SYSTEM-tablespace on HDD will be one of the best practices for the time being.

This post is a detailed benchmarking report.
(This post is very long and focusing on InnoDB only. If you are familiar with HDD/SSD/InnoDB architecture and understand what my blog title means, skipping section 1 (general theory) then reading from section 2 (benchmarking results) would be fine. )

1. General Theory of HDD, SSD and InnoDB

SSD is often called as a disruptive storage technology. Currently storage capacity is much smaller and unit price is much higher than HDD, but the situation is very rapidly changing. In the near future many people will use SSD instead of HDD.

From DBA's standpoint, you have a couple of choices for storage allocation.
- …

A while ago I posted about a comment a Sun performance engineer made about a scalable replacement for InnoDB. At the time, I did not believe it referred to Falcon. In hindsight, it seems even clearer that the Sun performance experts were already working hard on InnoDB itself.

Sun’s engineers have shown that they can produce great results when they really take the problems seriously. And I’m sure that InnoDB’s performance has untapped potential we don’t see right now. However, it does not follow that their work on InnoDB is what was meant by a scalable replacement for InnoDB. Or does it?

General-purpose MVCC transactional storage engines with row-level locking, whatever their performance and scaling characteristics in …

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 …