Sometimes, I make mistakes. It’s true. That can be difficult for us Systems Engineering-types to say, but I try to distance myself from my ego and embrace the mistakes because I often learn the most from them. ..Blah, blah, school of hard knocks, blah, blah…. Usually my mistakes aren’t big enough to cause any visible impact, but this one took the site out for 10 minutes during a period of peak traffic due to a confluence of events.

Doh!

Here is how it went down…

We have an issue where MySQL table statistics are occasionally getting out of whack, usually after a batch operation. This causes bad explain plans, which in turn cause impossibly slow queries. An ANALYZE TABLE (or even SHOW CREATE INDEX) resolves the issue immediately, but I prefer not get woken up at 4AM by long running query alerts when my family and I are trying to sleep. As a way to work around the issue, we decided to disable InnoDB automatic …

I will be in Austin for SITE presenting Teaching Database Concepts with Open Source Software on Wednesday, March 6th. So if you are a teacher attending SITE, please come attend my session. And bring your boots so we can hit 6th Street afterwards!

…

MySQL Embedded Online Forum

Tuesday, January 31, 2012 9:00AM PST – 12:00 PM PST

Think You Know MySQL? Get the Facts.

MySQL is even better than you think. According to the experts, it’s easier to use and it’s more capable than most ISVs / OEMs realize, and by taking a few tried-and-tested steps before shipping your MySQL-embedded products, you can lower your product’s costs and increase its performance.

Join our expert-created and delivered MySQL Embedded Online Forum to learn:

Why MySQL is a great embedded database for startups as well as the largest software, hardware, and appliance vendors in the world, and how its features ensure costs remain low throughout an …

Tuesday, February 21, 2012
1:30 PM – 7:00 PM
(registration starts 1:00 p.m.)

Raffles Hotel Singapore
Casuarina Suite B, Level 3
1 Beach Road, 189673, Singapore

Please Register Online

The MySQL Developer Day is a one-stop shop for you to learn all the essential skills about MySQL. In this free, half-day seminar, we will cover everything you need to know to successfully design, develop, and manage your MySQL databases. You’ll also learn the guidelines and best practices in performance tuning and scalability.

Attend this event and gain the knowledge to:

Develop your new applications cost-effectively using MySQL
Improve performance of your existing MySQL databases
Manage your MySQL environment more efficiently

…

MariaDB 5.3 has now three statistics counters for Multi Range Read optimization:

MariaDB [test]> show status like 'Handler_mrr%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Handler_mrr_extra_key_sorts   | 0     |
| Handler_mrr_extra_rowid_sorts | 0     |
| Handler_mrr_init              | 0     |
+-------------------------------+-------+
3 rows in set (0.08 sec)

I’ve just added the first two. The reason for having them is as follows: the point of MRR is to provide speedup over regular execution by doing reads in disk order. In order to make reads in disk order, MRR needs buffer space where it accumulates and sorts read requests. If there are too many read requests to fit into the buffer, MRR will make multiple accumulate-sort-read passes.

Doing multiple …

MySQL Cluster usage has certainly continued to spread and recently accelerate well beyond its initial telco vertical roots into Healthcare, Financial Services, SaaS and more. With those additions it certainly becomes desirable for many to provide transparent encryption on the NDB nodes where the data, logs, and checkpoints that write to disk. I’ll not go into all those reasons in this blog, but certainly there are plenty, these white papers provide  more details, especially if you are running within hosted, managed, or cloud environments platforms.

The solution for ndb in a nutshell was straight forward:
1 Set up Gazzang ezNcrypt Flex Platform
2 Stop the ndb process prior to encrypting the ndb_data directory
3 Encrypt the ndb_data directory
ezncrypt -e @ndbdata /home/mysql/my_cluster/ndb_data
4 Add a Flex ACL Rules …

The votes have been counted and now there is a schedule for MySQL and Friends devroom at FOSDEM 2012.

I’m giving two talks:

• MariaDB 5.3’s query optimizer: taking the dolphin to where he’s never been before
• Replication features of 2011: what they were, how to get and how to use them

In an ideal world, you should never need to use optimizer hints. In the real world, hints are necessary. We needed hints even before we’ve made a release. The first requests came from our Quality engineer who complained about it being difficult to hit particular join orders while using a particular subquery strategy.

So, why not add the needed hints? MySQL already has the STRAIGHT_JOIN and FORCE INDEX, you can add more as required. The problem is that once you have transformations that change the query to be far enough from the original SQL, you don’t have a “natural” place or syntax for hints anymore.

For example, if you have a join

SELECT ... FROM table1, table2, table3 WHERE ....

and want a join order of (table2, table3, table1), you can write it as

SELECT ... FROM table2 STRAIGHT_JOIN table3 STRAIGHT_JOIN table1

and that gives …

With MariaDB 5.3.3 Release Candidate out of the door, I could give some attention to documentation and improve the master What is MariaDB 5.3 page and pages linked from it.

I think the part about subquery optimizations should be fairly easy to read now, and our tech writer Daniel Bartholomew also did a pass over it. If you have a picture-type mind like I do, there is plenty of pictures, including the Subquery optimizations map.

For now, my goal was to just have a descriptions of all optimizations in place. We have also done substantial amount of benchmarking, but that data still waits to be …

Somebody posed the question about using a Comparator in the sorting examples provided in this earlier post on Updating Table View Columns (columns using a Varray or Nested Table of a single scalar data type). It seems the individual thought that you can’t use Java Generics inside an Oracle Database 11g’s Java libraries. It’s seems odd since they’ve been around since Java 5.

You can use Generics like those shown in the following example. It builds on explanation from the prior post. If you want to get the whole set of facts click the link above but you should have all the code you need in this post.

An example like this requires you first define a collection of strings in the database. This one uses the following definition:

1
2