Liveblogging at OOW: State of the Dolphin

Posted onOctober 3, 2011by|Comments Off on Liveblogging at OOW: State of the Dolphin

Thomas Ulin, VP of MySQL Enginnering, speaks about “State of the Dolphin” at 2011 Oracle OpenWorld. There are some pretty cool new features in MySQL 5.6 development milestone release 2, and they are all quite stable, which is exciting. They want to add more features before going GA with MySQL 5.6, but the ones in there are pretty ready to go.

“The 15-minute rule [MySQL can be installed in 15 minutes] is now down to 3 minutes for the full MySQL stack.” Download one package, and a GUI helps you install and configure everything.

Ulin talked a lot about how MySQL is good on Windows, and how it is better and lower TCO than Microsoft SQL Server. They are focusing on Visual Studio, MS Office Integration, Entity Framework, Windows Administration tooling, and more.

Talks about the parts of MySQL Enterprise Edition:

MySQL Workbench

MySQL Backup – completely new from the ground up, Ulin does not have slides but there is a presentation on Wednesday I am looking forward to going to.

MySQL Enterprise Security: New a few weeks ago, new authentication modules – PAM (uses LDAP, Kerberos, etc) or Windows authentication. A good slide with how it works:

The application server sends the authentication information to MySQL, which is sent to the PAM library, which verifies the credentials, and returns “yes/no” to the mysql user. “CREATE USER joe IDENTIFIED WITH pam_plugin AS ‘joe’;” – NOTE: specific privileges are still managed on the MySQL level (e.g. not everyone gets the SUPER privilege!)

MySQL Enterprise Scalability – thread pooling. Pool contains a configurable number of thread groups (default=16) and each group manages up to 4096 re-usable threads. Each connection asssigned to a group via round robin. 20x better scalability read/write using thread pooling, 3x better scalability for read-only workloads. There is an API so the thread pooling can be implemented manually.

OVM template for MySQL – on Oracle Linux.

Announcements

5.6 DMR 2 (development milestone release)

Close enough to be an RC or GA, they are very confident. But they are not releasing as GA because they want to add more features.

New features – builds on MySQL 5.5 by improving:

optimizer – better performance, scalability

– filesort optimizations with small limits

– avoids creating intermediate sorted files by producing an ordered result set using a single table scan and sort on the fly.

index condition pushdowns

– was a 6.0 feature

– good for composite indexes

– why wouldn’t you have this turned on, btw?

batched key access and multi-range read

– Improves performance of disk-bound JOIN queries

– handles batches of keys instead of traditional nested-loop joins which do one key at a time. Takes advantage of sequential reads.

postponed materialization

– of views/subqueries in the FROM clause (aka derived tables)

– Allows fast EXPLAINs for views/subqueries

– Avoid materialization when possible, faster bail out

– A key can be generated for derived tables.

– Unsurprisingly, this optimization is huge – 240x better execution times (e.g. drop from 8 minutes to about 2 seconds)

EXPLAIN for INSERT, UPDATE, DELETE

Persistent Optimizer Statistics

Optimizer traces

performance schema: better instrumentation

– up to about 500 instrumentation points

– Statements/stages – where do my most resource-intensive queries spend the most time?

– MSQL 5.5 Server Integration – previous versions were integrated with MySQL 5.1, so in MySQL Cluster 7.2 you have access to the new features – upgrading can be done in an online fashion with the rolling upgrade.

– Multi-geographical site clustering – DR and data locality, no passive resources. Can split data nodes and node groups across data centers and have auto-failover.

– Simplified active/active replication – eliminates requirement for application and schema changes. There are transaction-level rollbacks. This helps with conflict detection when more than one master is written to.

– Consolidated privileges for easier provisioning and administration

MySQL Enterprise Oracle Certifications

– All the Oracle Fusion MiddleWare ship with the MySQL 5.x JDBC driver.

MySQL is being put into or being supported by a lot of Oracle products, including Fusion MiddleWare, including Oracle E-business suite. This means you can use MySQL as a data source.

Coming soon: using the Auditing API in MySQL 5.5, Oracle will be adding an audit plugin that can (among other things) produce an audit stream that Oracle Audit Vault can then use.