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.

MariaDB can now print query’s EXPLAIN into the slow query log! The feature is based on SHOW EXPLAIN technology and its output looks like this:

# Time: 131017 21:26:02
# User@Host: root[root] @ localhost []
# Thread_id: 2  Schema: dbt3sf1  QC_hit: No
# Query_time: 5.535819  Lock_time: 0.000350  Rows_sent: 1  Rows_examined: 65633
## <explain>
##   id select_type     table   type    possible_keys   key     key_len ref     rows    Extra
##   1  SIMPLE  nation  ref     PRIMARY,n_name  n_name  26      const   1       Using where; Using index
##   1  SIMPLE  customer        ref     PRIMARY,i_c_nationkey   i_c_nationkey   5       dbt3sf1.nation.n_nationkey      25124   Using index
##   1  SIMPLE  orders  ref     i_o_custkey     i_o_custkey     5       dbt3sf1.customer.c_custkey      7       Using index
## </explain>
SET timestamp=1382030762;
select count(*) from customer, orders, nation …

Do you have the background necessary to take full advantage of Tungsten Replicator in your environments? Tungsten offers enterprise-quality replication features in an open source package hosted on Google Code. This virtual course will teach you how to set up innovative topologies that solve complex replication problems. We start with a list of the main problems that Tungsten can solve, then show

MySQL 5.6 added support for EXPLAIN INSERT/UPDATE/DELETE. MySQL 5.7 made some improvements to EXPLAIN code. Also, 5.7 added support for EXPLAIN FOR CONNECTION, which looks very similar to MariaDB’s SHOW EXPLAIN. I was working on putting EXPLAIN INSERT/UPDATE/DELETE into MariaDB 10.0, so I’ve dug up some details and thought I’d share them:

EXPLAIN UPDATE/DELETE

EXPLAIN UPDATE looks like regular EXPLAIN SELECT:

MySQL [dbt3sf1]> explain update orders set o_orderpriority='1-URGENT' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using …