For those with an interest in MySQL, this week at Oracle OpenWorld has gotten off to a great start.  Demonstrating how Oracle drives MySQL innovation Tomas' "State of the Dolphin" keynote on Monday gave a great overview of the new MySQL products that have recently been delivered:

• MySQL Installer for Windows
• New MySQL Security and Scalability features 
• New MySQL High Availability features 
• New early access features in the MySQL 5.6.3 Development Milestone Release ("DMR") 
• New early access features in the …

In his Oracle OpenWorld keynote, Tomas Ulin, Oracle’s MySQL VP of Engineering, just announced exciting MySQL news, further demonstrating how Oracle drives MySQL Innovation! Here is a summary, with more detailed information following shortly:

1. Immediate Availability of the MySQL 5.6 2nd Development Milestone Release (DMR).

MySQL 5.6 builds on MySQL 5.5 by improving:

• The Optimizer for better performance and scalability
• Performance Schema for better instrumentation
• InnoDB for better transactional throughput
• Replication for improved data integrity with higher performance, availability and ease-of-use
• “NotOnlySQL” options for more flexibility in handling both key/value and complex query operations
• And more…

The MySQL 5.6 DMR #2 is available …

First benchmarks show that we're on par or faster than in-memory databases which use a similar technology: Redis and Memcached. I will never fully believe a benchmark which I haven't made myself (this one was made by pmwkaa using nosqlbench), and we have yet to work more to make the results more stable, but what we have is better than nothing.
The benchmark was done on a Intel Core I7, M 620 @ 2.67GHz, 8 GB RAM, 7200 RPM SATA Seagate ST9500420AS hard drive.

10 concurrent threads doing reads and writes (bulked into 16K packets) with tuple size varying from 200 to 350 bytes were able to perform around 700K/300K RPS respectively.

This is far lower than the disk can make, we hit the CPU wall first -- which is not good news. Well, somewhat OK news is that in that case we perform just like …

After reading about the query cache mutex contention, scalability issues on multi-cores, and several people recommending disabling query cache on multi-core machines. I decided to give it a try and disable it to improve performance and since I was also seeing a lot of "freeing items" states for my queries (mysql works on query cache for part of the time query is in that state).

Most databases, which do not natively support SEQUENCE data type require user to create a separate table, or other database object to hold sequence information.

In Tarantool, thanks to being able to access indexes directly from Lua, supporting sequences is just a few lines of code, wrapping the standard INSERT command:

function box.auto_increment(spaceno, ...)
    max_tuple = box.space[spaceno].index[0].idx:max()
    if max_tuple ~= nil then
        max = box.unpack('i', max_tuple[0])
    else
        max = -1
    end
    return box.insert(spaceno, max + 1, ...)
end

(You can see a syntax-highlighted source with comments here.)

There is no need for locks, mutexes or alike: a stored procedure is atomic. There is also no lying and no mystery implementation
to support auto_increment for …

Ronald Bradford is a busy man. He has just finished Effective MySQL: Optimizing SQL Statements, the first in a series of MySQL books. There is a sample chapter at the link above.

The chapters on indexing will be valuable to novices. Ronald has distilled his presentation on the use of EXPLAIN into chapter that should be made mandatory reading. The book is free of fluff and full of information that you will reference well after the initial reading.

For those of you at Oracle Open World, this book and others are heavily discounted in the bookstore on the second level of Moscone West, near the MySQL Community Kiosk.

…

Some suggestions for MySQL sessions at Oracle Open World 2011.

Tomas Ulin will give the MySQL keynote today:

• MySQL Executive Keynote: The state of the dolphin, Monday 12:30pm

Luis Soares, Sanjay Manwani, Chuck Bell and myself will give the following talks:

• Introduction to MySQL Replication, Monday 2:00pm
  A walk-through of how MySQL Replication works and what you can do with it.  An introduction if you don't know it, and a summary of possible use cases for those of you that are already familiar with the technology.
• Advanced MySQL Replication Architectures, Tuesday 10:15am
  Directed for the more advanced users, in this talk we go through not only the traditional but also more complex scenarious, e.g. hierarchical replication and replicating from multiple …

The other day, I just posted an article about setting up Perl on Windows for MySQL.

However, I just ran into an interesting Perl issue on Linux, and it was one slightly out of the ordinary, so I wanted to share the solution, as one might not find this one quickly otherwise.

The problem occured when trying to install the perl-DBD-MySQL module on a linux (CentOS) server, due to a dependency issue.

Most dependency issues are a little more straight-forward, but this one is what I’d almost call a ‘reverse’ dependency issue.

Here was the command:

shell$ yum install perl-DBD-MySQL

Here was the output/error:

Loaded plugins: dellsysid, fastestmirror
Loading mirror speeds from cached hostfile
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to …

It's great to see so many presentations and activities surrounding MySQL at Oracle Open World this year.  There are a lot of Dolphins around the conference which is great to see as well.

They MySQL reception Tuesday evening is going to be a great opportunity to get caught up with everyone.

A new feature in MySQL 5.6.3 Development Milestone Release is Batched Key Access (BKA). BKA can be applied when an index lookup can be used to execute a join query. One example of such a query is:

mysql> SELECT * FROM customer JOIN orders ON c_custkey = o_custkey;

Given that the customer table is significantly smaller than the orders table, and assuming that we have an index on the o_custkey column of orders, MySQL have traditionally executed this join as follows: Scan the entire customer table, and for each row in the customer table, do an index look-up into the orders table to find matching rows. This strategy is know as Index Nested Loops Join, or as MySQL EXPLAIN puts it:

 …