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

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

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

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

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 processed and published.

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

I am an Oracle database administrator and over the last few years I have found myself becoming more involved in MySQL database administration.

I knew absolutely nothing about MySQL and had to figure it out using books and blogs.

I realised there must be many other people in my position so I developed a presentation titled “MySQL: the least an Oracle DBA needs to know”. My aim was to get people up to speed with the basics as quickly and simply as possible, mostly through live demonstrations.

I have had the privilege of presenting this for Oracle user groups in the UK, USA, New Zealand and Finland. It’s a great feeling as the audience moves from sceptical to excited, with lots of “Can it do ….?” questions at the end of the presentation.

To accompany the presentation I wrote

The deadline for proposals has passed, and there are 37 talk proposals and 12 time slots. In order to have the best possible program, we need your help. There is public voting on proposals, votes are accepted via twitter or via email. Please Vote for MySQL and Friends at FOSDEM 2012 devroom talks. Thank you!

If a DBA needs to reset the mysql root password – one method of doing this is to run mysqld with -skip-grant-tables as a command line parameter. This is a bad practice for many reasons and as a DBA friend of mine says – it’s a lot like locking your door with the window open. If you are using mysql for especially secure data you should consider your options.

One option, and likely preferred, is to replace the default mysql from various sources with one built with this feature disabled – see http://dev.mysql.com/doc/refman/5.1/en/source-configuration-options.html#option_configure_disable-grant-options

I am not aware of any distro’s that are built with this option – but seems like a fair

Following the tradition, there will be a “MySQL and Friends” devroom at FOSDEM 2012. The devroom is scheduled for Sunday 5th February 2012, whole day. If you are interested to give a talk, please submit it before December 26th through this submission form.

Link to original announcement by Frédéric Descamps

I work at ideeli and wrote up a two parter for our Tech Blog about finding problem queries in MySQL…

Finding Problem Queries, Part 1: The Slow Stuff

I was giving a presentation of the MySQL’s Enterprise Monitor (http://www.mysql.com/products/enterprise/monitor.html)* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

A friend of mine was asking me for my recommendation of a good desktop database program to use to keep track of his inventory of cargo containers. I suggested to him that he should use MySQL and write a web page interface to do everything that he needed. He then reminded me that he is a lawyer by trade, and that he doesn’t have any computer programming experience. Then I remembered that he has almost zero computer skills. And his Texas Hold-Em skills are even worse, but I don’t mind taking his money. In his case, he should just use a notepad and a pencil. (As for the question – what is a lawyer doing with cargo containers? – that is a different story.)

If he did decide to broaden his horizons a bit, he could easily write his own software web application for creating and storing almost any

Part Two of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part Two: Monitoring the activity via Perl and SHOW ENGINE INNODB STATUS. (part of the InnoDB Monitors)

In part one, I showed you how to use a Perl script to insert a million rows of dummy data into a table. I needed a large database in order to test a Perl script that I would use to monitor the activity when running a large DML statement.

The original reason for creating both of these scripts was to find a quick way to see if a large

Part One of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part One: Inserting a million rows into a database.

A friend of mine had asked a question – “Is there any way you can track how far you have advanced in a slow-moving ALTER or OPTIMIZE statement?”. A customer was performing some modifications on a database with tens of millions of rows, and they wanted to be able to see if the command was making any progress.

Since the customer was using the InnoDB storage engine, I thought of a way that you could check on the progress – but only given