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.
MySQL Enterprise HA: Windows Server Failover Clustering, uses Windows Server Failover Clustering from Microsoft and the cluster is managed through the Windows tools.
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?
– TAble/Index I/O, Table Locks – which tables/indexes cause the most load/contention?
– Users/Host/accounts – using the most resources
– Network I/O
– Sumaries – agg by thread, user, host, account or object
innodb – better transactional throughput
– new I_S tables: Metrics, Systems, Buffer pool info
– Dump/restore buffer pool
– Limit insert buffer size (ibuf)
replication for HA, data integrity
– Better data integrity: crash-safe slaves, replication checksums, crash-safe binlog
– Better performance: multi-threaded slaves, reduced binlog size for row-based binary logging
– Extra flexibility – time-delayed replication
– Simpler troubleshooting – row-based replication logging of original query
– Enhanged monitoring/management.
“NotOnlySQL” (NoSQL) for more flexibility
Misc
– ipv6 improvements
– Unicode support for Windows commandline client
– import/export to/from partitioned tables
– explicit partition selection
– GIS/MyISAM: Precise spatial operations
MySQL Cluster 7.2: DMR 2
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.
– 70x higher complex query performance using Adaptive Query Localization (example was an 11 way join)
– native memcached API – with no application changes. reuses standard memcached clients & libraries. Also eliminates cache invalidation slamming MySQL
– 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.
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.
MySQL Enterprise HA: Windows Server Failover Clustering, uses Windows Server Failover Clustering from Microsoft and the cluster is managed through the Windows tools.
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?
– TAble/Index I/O, Table Locks – which tables/indexes cause the most load/contention?
– Users/Host/accounts – using the most resources
– Network I/O
– Sumaries – agg by thread, user, host, account or object
innodb – better transactional throughput
– new I_S tables: Metrics, Systems, Buffer pool info
– Dump/restore buffer pool
– Limit insert buffer size (ibuf)
replication for HA, data integrity
– Better data integrity: crash-safe slaves, replication checksums, crash-safe binlog
– Better performance: multi-threaded slaves, reduced binlog size for row-based binary logging
– Extra flexibility – time-delayed replication
– Simpler troubleshooting – row-based replication logging of original query
– Enhanged monitoring/management.
“NotOnlySQL” (NoSQL) for more flexibility
Misc
– ipv6 improvements
– Unicode support for Windows commandline client
– import/export to/from partitioned tables
– explicit partition selection
– GIS/MyISAM: Precise spatial operations
MySQL Cluster 7.2: DMR 2
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.
– 70x higher complex query performance using Adaptive Query Localization (example was an 11 way join)
– native memcached API – with no application changes. reuses standard memcached clients & libraries. Also eliminates cache invalidation slamming MySQL
– 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.