I previously wrote that I am studying for the MySQL 5.6 exam, and that I’m less confident in my skills as a database developer. When I went through the list of topics in the exam, one thing I knew I would have to study is stored programs.

So first, some definitions. From the manual:

Stored programs include these objects:

• Stored routines, that is, stored procedures and functions. A stored procedure is invoked using the CALL statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client program. A stored function is used much like a built-in function. you invoke it in an expression and it …

This week, we talk about Percona's Xtrabackup. Ear Candy talks about .frm files and how many fields an InnoDB table can have, and At the Movies is a talk about indexes.

Xtrabackup
Episode 38 where we previously talked about Xtrabackup

Installing Percona's Xtrabackup

Operating system permissions and database privileges needed to use xtrabackup

read more

I have been pretty quiet on the blogging front for quite a long time now.  The main reason for this is I have been working very hard on leading a small team which is developing a Stackforge project called 'Libra'.  As you can probably guess from the name, Libra is a Load Balancer as a Service system.  Many of you may not of heard of it but according to Stackalytics it was the 27th biggest project in terms of code contributed during Havana and 2nd biggest in HP (something I am especially proud of because it has been one of the smallest teams in HP Cloud).

It is based on the Atlas API specifications, creates software-based load balancers, is implemented in Python and sits on top of Nova instances rather than working under the cloud.  It also has several unique features which could be …

Naysayer within us keep on erecting new obstacles, especially when it comes to the creativity. Our database bloggers are winning this fight within and producing gems of blogs. This Log Buffer edition celebrates their victory.

Oracle:

Andy Baker is shrinking a Linux OEL 6 virtual Box image (vdi) hosted on Windows 7.

So what is Active GridLink for RAC? Ruma Sanyal tells us.

Curtis is installing Oracle Golden Gate 12c (12.1.2) along side Oracle Database 12c (12.1.0.1).

…

The MySQL optimizer is getting better. MySQL 5.6 introduced:

• File sort optimizations with small limit
• Index Condition Pushdown
• Batched Key Access and Multi Range Read
• Postponed Materialization
• Improved Subquery execution
• EXPLAIN for Insert, Update, and Delete
• Optimizer Traces
• Structured EXPLAIN in JSON format

This was in addition to the InnoDB storage engine now offering improved statistics collection, leading to more stable query plans.

In Evgeny Potemkin’s session at MySQL Connect titled ”MySQL’s EXPLAIN Command New Features”, two new features for 5.7 were …

The MySQL optimizer is getting better. MySQL 5.6 introduced:

• File sort optimizations with small limit
• Index Condition Pushdown
• Batched Key Access and Multi Range Read
• Postponed Materialization
• Improved Subquery execution
• EXPLAIN for Insert, Update, and Delete
• Optimizer Traces
• Structured EXPLAIN in JSON format

This was in addition to the InnoDB storage engine now offering improved statistics collection, leading to more stable query plans.

In Evgeny Potemkin’s session at MySQL Connect titled ”MySQL’s EXPLAIN Command New Features”, two new features for 5.7 were …

Once again, Geir Høydalsvik and I had the pleasure of hosting a MySQL mini-seminar in Trondheim. 25+ attendants from at least 7 different companies and a few professors from the computer science dept. at NTNU showed up on yesterdays event. I recognized many of these from the first seminar but there were some new faces as well.

This time, Mark Leith came on a visit from the UK. He gave an introduction to Performance Schema and ps_helper. ps_helper is a really nice tool to make sense of the overwhelming amount of data collected by PS. He also gave a very convincing demo of MySQL Enterprise Monitor (MEM). More than a few attendants now plan to give MEM a try in their environment. You can too - there's a 30 day trial, which should be more than enough to decide if you need it …

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight:

Scalability issues due to tables without primary keys

This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various places. I will only mention a few of them:

• During operations such as opening and closing table handles, or
• When accessing I_S tables, or
• During undo of a freshly inserted row, or
• During other data dictionary modification operations such as CREATE TABLE, or
• Within the “Persistent Stats” subsystem, among other things.

Of course this list is not exhaustive but should …

A little-mentioned feature on the interwebs regarding MySQL is the InnoDB monitors.

It has been around for a long time.

http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html

You have to create varying tables to enable the monitoring.  You only want to do this when your system can withstand a performance degradation.

When you are trying to improve performance or want a look under the hood, the monitors are valuable.

Monitors provide rich information about InnoDB such as File I/O, Locks, and Cardinality of indexes.  They can help to provide a shape of the overall activity on your InnoDB tables.

The amount of information can be overwhelming when you are in a scenario where performance is degrading rapidly and you are on-call to remedy the degradation of …

I have have been receiving a lot of questions about the new MySQL 5.6 DBA exam. Sadly I can not answer a lot of the questions until after my exam in early November. However I do know some good things about the new exams.

First — No True or False questions. Oracle has set the standards on that policy and I had to update the ol’ 5.0 exams to make certain there the T/F questions were removed. Why is this important? Don’t you have a fifty/fifty chance on T/F questions? The trouble is that some of these questions were a wee bit tricky and needed parsing plus a full understanding of the subject to get them right. For fairness, it is easy to do without the T/F questions.

Second — No more pick up to N answers. Once again, for fairness.

Third — One exam for the certification. In the past the costs, not just financial, of setting up the second exam and studying for it turned off many candidates.