Thursday Jan 31, 2013

Tecnotree Empowers Communications Service Providers with Embedded MySQL

Tecnotree is a global provider of telecom IT solutions for the management of products, customers and revenue. Tecnotree helps communications service providers to transform their business towards a marketplace of digital services. Tecnotree empowers service providers to monetise on service bundles, provide personalized user experiences and augment value throughout the customer lifecycle. With over 1100 telecom experts, Tecnotree serves more than 100 service providers in over 70 countries. Tecnotree is listed on the main list of NASDAQ OMX Helsinki.

Challenges

  • Integrate a database well suited to power the mission critical Tecnotree solutions deployed by telecom operators in networks counting over 50 million subscribers.
  • Standardize on one database for the core Tecnotree products, while meeting the company’s technical and economic requirements.
  • Select a database delivering the configuration flexibility needed to provide the best performance and reliability in different settings.

Solution

  • Evaluation and selection of MySQL as embedded database based on its high performance, reliability, flexibility of deployment and cost-effectiveness.
  • Tecnotree leverages the built-in MySQL replication to confer up to 5 nines (99.999%) availability to its solutions; ensuring subscribers will continuously be able to access the operators’ network and billing infrastructure.
  • Implemented at the heart of front-end signaling systems transmitting information to other applications, MySQL commonly handles 20,000 queries and 2,000 transactions per second, for example allowing the real-time billing of subscribers.
  • MySQL databases powering the voice-mail and conferencing applications deployed by operators store up to a terabyte of data.
  • Tecnotree has been relying on MySQL for several years, and plans to embed it in additional products in the future, highly valuing the “hassle free” nature of the database.
  • The company also uses the Oracle database within its customer care and billing solutions.

“With MySQL we get a high performance solution that simultaneously delivers the flexibility to serve our different database needs.” Timo Ahomäki, CTO, Tecnotree


Monday Jan 28, 2013

Take MySQL Cluster Training to Meet Database Challenges

Take the authentic MySQL Cluster training to meet database challenges of next generation web, cloud and communication services with uncompromising scalability, uptime and agility.

This 3 day instructor-led class provides you a full understanding of cluster concepts as they relate to MySQL Cluster  as well as how to properly configure and manage the cluster nodes to ensure high availability, how to install the different nodes and provide a better understanding of the internals of the cluster.

Here are some of the events already on the schedule for this course:

 

 Location  Date  Delivery Language
 London, England
20 March 2013
English
 Rennes, France
26 February 2013
French
 Munich, Germany
10 June 2013
 Germany
 Stuttgart, Germany
26 March 2013
Germany
 Budapest, Hungary
 19 June 2013
Hungarian
Milan, Italy
 3 April 2013
 Italian
Warsaw, Poland
18 March 2013
 Polish
 Barcelona, Spain
4 March 2013
 Spanish
 Madrid, Spain
 25 February 2013
 Spanish
 Singapore 18 February 2013
 English
 Irvine, CA, United States
24 July 2013
 English
 Chicago, IL, United States
 27 March 2013
 English
Edison, NJ, United States
29 May 2013
 English
 Reston, VA, United States
6 February 2013
 English

 

To learn more about this course or the entire MySQL curriculum, to register for an event, or to request additional events, go to http://oracle.com/education/mysql.

Thursday Jan 24, 2013

MySQL 5.6: What's New in Performance, Scalability, Availability

With the MySQL 5.6 production-ready GA set for release in the coming days, it’s good to re-cap the key features that make 5.6 the best release of the database ever.  At a glance, MySQL 5.6 is simply a better MySQL with improvements that enhance every functional area of the database kernel, including:
  • Improved Security for worry-free application deployments
  • Better Performance and Scalability
    • Improved InnoDB storage engine for better transactional throughput
    • Improved Optimizer for better query execution times and diagnostics
  • Better Application Availability with Online DDL/Schema changes
  • Better Developer Agility with NoSQL Access with Memcached API to InnoDB
  • Improved Replication for high performance, self-healing distributed deployments
  • Improved Performance Schema for better instrumentation
  • And other Important Enhancements


Improved Security for worry-free deployments
Security is near and dear to every DBA and Sys Admin's heart.  With this in mind, MySQL 5.6 introduces a major overhaul to how passwords are internally handled and encrypted.  The new options and features include:

New alternative to password in master.info – MySQL 5.6 extends the replication START SLAVE command to enable DBAs to specify master user and password as part of the replication slave options and to authenticate the account used to connect to the master through an external authentication plugin (user defined or those provided under MySQL Enterprise Edition).  With these options the user and password no longer need to be exposed in plain text in the master.info file.
New encryption for passwords in general query log, slow query log, and binary log – Passwords in statements written to these logs are no longer recorded in plain text.
New password hashing with appropriate strength – Default password hashing for internal MySQL server authentication via PASSWORD() is now done using the SHA-256 password hashing algorithm using a random salt value.
New options for passwords on the command line – MySQL 5.6 introduces a new “scrambled” option/config file (.mylogin.cnf) that can be used to securely store user passwords that are used for command line operations.
New change password at next login – DBAs and developers can now control when account passwords must be changed via a new password_expired flag in the mysql.user table.
New policy-based Password validations – Passwords can now be validated for appropriate strength, length, mixed case, special chars, and other user defined policies based on LOW, MEDIUM and STRONG designation settings. 

Learn about these and all of MySQL 5.6 Security improvements and features, along with all technical documentation, in the MySQL docs

Better Performance and Scalability: Improved InnoDB Storage Engine

From an operational standpoint MySQL 5.6 provides better sustained linear performance and scale on systems supporting multi-processors and high CPU thread concurrency.  Key to this are improvements to Oracle’s InnoDB storage engine efficiency and concurrency that remove legacy thread contention and mutex locking within the InnoDB kernel.  These improvements enable MySQL to fully exploit the advanced multi-threaded processing power of today’s x86-based commodity-off-the-shelf hardware.

Internal benchmarks for SysBench Read/Write and Read Only workloads show a marked improvement in sustained scale over the most current version of MySQL 5.5.  The following shows that MySQL 5.6 provides “up and to the right” linear read/write transactions per second (“TPS”) scale on systems that support upwards of 48 concurrent CPU threads. 


Read only TPS workload sustained scale is also improved as demonstrated here:


Better Transactional Throughput

MySQL 5.6 improves InnoDB for better performance and scalability on highly concurrent, transactional and read intensive workloads.  In these cases performance gains are best measured by how an application performs and scales as concurrent user workloads grow.  In support of these use cases, InnoDB has a new re-factored architecture that minimizes mutex contentions and bottlenecks and provides a more consistent access path to underlying data.  Improvements include:

  • Kernel mutex split to remove a single point of contention
  • New thread for flushing operations
  • New multi-threaded purge
  • New adaptive hashing algorithm
  • Less buffer pool contention
  • Better, more consistent query execution via persistent optimizer statistics that are collected at more regular, predictable intervals

The net result of these improvements is reflected in the SysBench read/write benchmarks shown here: 


For Linux, MySQL 5.6 shows up to a 150% improvement in transactional TPS throughput over MySQL 5.5, while similar tests run on Windows 2008 reveal a 47% performance gain. 

Better Read Only Workload Throughput
New optimizations have been made to InnoDB for read only transactions that greatly improve the performance of high concurrency web-based lookups and report-generating applications.  These optimizations bypass transactional overhead and are enabled by default when autocommit = 1, or can be atomically controlled by the developer using the new START_TRANSACTION_READ_ONLY syntax:

SET autocommit = 0;
START_TRANSACTION_READ_ONLY;
SELECT c FROM T1 WHERE id=N;
COMMIT;

The results of these optimizations are shown here:


For Linux, MySQL 5.6 shows up to a 230% improvement in read only TPS throughput over MySQL 5.5, while similar tests run on Windows 2008 show a 65% performance gain.

For context, all benchmarks shown above were run on the following platform configuration:

  • Oracle Linux 6
  • Intel(R) Xeon(R) E7540 x86_64
  • MySQL leveraging:
    • 48 of 96 available CPU threads
    • 2 GHz, 512GB RAM

The SysBench benchmark tool is freely available for application use-case specific benchmarks and can be downloaded here.

You can also get in depth MySQL 5.6 performance and feature specific benchmarks by following related blogs by Mikael Ronstrom and Dimitri Kravtchuk.  Both share the test cases and configurations they use to arrive at the conclusions drawn above.

Better Performance with Solid State Drives (SSD)
Spinning disks are among the most common bottlenecks on any system, simply because they have mechanical parts that physically limiit the ability to scale as concurrency grows.  As a result, many MySQL applications are being deployed on SSD enabled systems which provide the memory-based speed and reliability required to support the highest levels of concurrency on today’s web-based systems.  With this in mind, MySQL 5.6 includes several key enhancements designed specifically for use with SSD, including:

  • Support for smaller 4k and 8k page sizes to better fit the standard storage algorithm of SSD.
  • Portable .ibd (InnoDB data) files that allow “hot” InnoDB tables to be easily moved from the default data directory to SSD or network storage devices.
  • Separate tablespaces for the InnoDB unlog log that optionally moves the undo log out of the system tablespace into one or more separate tablespaces.  The read-intensive I/O patterns for the undo log make these new tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard drive storage.

Learn about all supporting SSD optimizations here.

Better Query Execution Times and Diagnostics: Improved Optimizer
The MySQL 5.6 Optimizer has been re-factored for better efficiency and performance and provides an improved feature set for better query execution times and diagnostics.  They key 5.6 optimizer improvements include:

Subquery Optimizations – Using semi-JOINs and materialization, the MySQL Optimizer delivers greatly improved subquery performance, simplifying how developers construct queries.  Specifically, the optimizer is now more efficient in handling subqueries in the FROM clause; materialization of subqueries in the FROM clause is now postponed until their contents are needed during execution, greatly improving performance.  Additionally, the optimizer may add an index to derived tables during execution to speed up row retrieval.  Tests run using the DBT-3 benchmark Query #13, shown below, demonstrate an order of magnitude improvement in execution times (from days to seconds) over previous versions.

select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in (
                select l_orderkey
                from lineitem
                group by l_orderkey
                having sum(l_quantity) > 313
  )
  and c_custkey = o_custkey
  and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
LIMIT 100;

File Sort Optimizations with Small Limit – For queries with ORDER BY and small LIMIT values, the optimizer now produces an ordered result set using a single table scan.  These queries are common in web applications that display only a few rows from a large result set such as:

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

Internal benchmarks have shown up to a 4x improvement in query execution times which helps improve overall user experience and response times. 

Index Condition Pushdown (ICP) – By default, the optimizer now pushes WHERE conditions down to the storage engine for evaluation, table scan and return of ordered result set to the MySQL server. 

CREATE TABLE person (
      personid INTEGER PRIMARY KEY,
      firstname CHAR(20),
      lastname CHAR(20),
      postalcode INTEGER,
      age INTEGER,
      address CHAR(50),
      KEY k1 (postalcode,age)‏
   ) ENGINE=InnoDB;

SELECT lastname, firstname FROM person
   WHERE postalcode BETWEEN 5000 AND 5500 AND age BETWEEN 21 AND 22; 


Internal benchmarks on this type of table and query have shown up to 15x improved execution times with the ICP default behavior. 

Batched Key Access (BKA) and Multi-Range Read (MRR) – The optimizer now provides the storage engine with all primary keys in batches and enables the storage engine to access, order and return the data more efficiently greatly improving query execution times. 



Together, BKA and MRR show up to 280x improvement in query execution times for DBT-3 Query 13 and other disk-bound query benchmarks.

Better Optimizer Diagnostics – The MySQL 5.6 optimizer also provides better diagnostics and debugging with:

  • EXPLAIN for INSERT, UPDATE, and DELETE operations,
  • EXPLAIN plan output in JSON format with more precise optimizer metrics and better readability
  • Optimizer Traces for tracking the optimizer decision-making process.


Learn about all of MySQL 5.6 Optimizer improvements and features, in the MySQL docs.

For a deep technical dive into the implementation, how to enable/disable where applicable, related benchmarks and the use case specific performance improvements you can expect with each of these new features check out the MySQL Optimizer Engineering team blog.

Better Application Availability: Online DDL/Schema Changes
Today's web-based applications are designed to rapidly evolve and adapt to meet business and revenue-generation requirements. As a result, development SLAs are now most often measured in minutes vs days or weeks. So when an application must quickly support new product lines or new products within existing product lines, the backend database schema must adapt in kind, most commonly while the application remains available for normal business operations.  MySQL 5.6 supports this level of online schema flexibility and agility by providing the following new ALTER TABLE DDL syntax additions:

CREATE INDEX
DROP INDEX
Change AUTO_INCREMENT value for a column
ADD/DROP FOREIGN KEY
Rename COLUMN
Change ROW FORMAT, KEY_BLOCK_SIZE for a table
Change COLUMN NULL, NOT_NULL
Add, drop, reorder COLUMN


DBAs and Developers can add indexes and perform standard InnoDB table alterations while the database remains available for application updates. This is especially beneficial for rapidly evolving applications where developers need schema flexibility to accommodate changing business requirements.

Learn about all of MySQL 5.6 InnoDB online DDL improvements and features, in the MySQL docs.

Better Developer Agility: NoSQL Access to InnoDB
MySQL 5.6 provides simple, key-value interaction with InnoDB data via the familiar Memcached API.  Implemented via a new Memcached daemon plug-in to mysqld, the new Memcached protocol is mapped directly to the native InnoDB API and enables developers to use existing Memcached clients to bypass the expense of query parsing and go directly to InnoDB data for lookups and transactional compliant updates.  The API makes it possible to re-use standard Memcached libraries and clients, while extending Memcached functionality by integrating a persistent, crash-safe, transactional database back-end.  The implementation is shown here:


So does this option provide a performance benefit over SQL?  Internal performance benchmarks using a customized Java application and test harness show some very promising results with a 9X improvement in overall throughput for SET/INSERT operations:


Not only do developers and DBAs get more performance and flexibility, they also reduce complexity as it is possible to compress previously separate caching and database layers into a single data management tier, as well as eliminate the overhead of maintaining cache consistency.

You can follow the InnoDB team blog for the methodology, implementation and internal test cases that generated the above results.

Learn more about the details and how to get started with the new Memcached API to InnoDB in the MySQL docs.

Better Developer Agility: Extended InnoDB Use Cases
New MySQL 5.6 optimizations and features extend InnoDB into more use cases so developers can simplify applications by standardizing on a single storage engine.

New Full Text Search (FTS) – Provided as a better alternative to MyISAM FTS, InnoDB now enables developers to build FULLTEXT indexes on InnoDB tables to represent text-based content and speed up application searches for words and phrases.  InnoDB full-text search supports Natural language/Boolean modes, proximity search and relevance ranking.  A simple use case example looks like: 

CREATE TABLE quotes
(id int unsigned auto_increment primary key
, author varchar(64)
, quote varchar(4000)
, source varchar(64)
, fulltext(quote)
) engine=innodb;

SELECT author AS “Apple" FROM quotes
    WHERE match(quote) against (‘apple' in natural language mode);


New Transportable Tablespaces – InnoDB .ibd files created in file-per-table mode are now transportable between physical storage devices and database servers; when creating a table developers can now designate a storage location for the .idb file outside of the MySQL data directory.  This enables “hot” or busy tables to be easily moved to an external network storage device (SSD, HDD) that does not compete with application or database overhead. This new feature also enables quick, seamless application scale by allowing users to easily export/import InnoDB tables between running MySQL servers, as shown here:

Example Export:
CREATE TABLE t(c1 INT) engine=InnoDB;
FLUSH TABLE t FOR EXPORT; -- quiesce the table and create the meta data file
$innodb_data_home_dir/test/t.cfg
UNLOCK TABLES;


Corresponding Import:
CREATE TABLE t(c1 INT) engine=InnoDB; -- if it doesn't already exist
ALTER TABLE t DISCARD TABLESPACE;
-- The user must stop all updates on the tables, prior to the IMPORT
ALTER TABLE t IMPORT TABLESPACE;


The InnoDB improvements noted here are by no means exhaustive.  The complete accounting of all MySQL 5.6 features, along with all technical documentation, is available in the MySQL docs.

For a deep technical dive into the implementation, how to enable/disable where applicable and the use case specific improvements you can expect with each of these new features follow the MySQL InnoDB Engineering team blog.

Improved Replication and High Availability
Replication is the most widely used MySQL feature for scale-out and High Availability (HA) and MySQL 5.6 includes new features designed to enable developers building next generation web, cloud, social and mobile applications and services with self-healing replication topologies and high performance master and slaves.  The key features include:

New Global Transactions Identifiers (GTIDs) – GTIDs enable replication transactional integrity to be tracked through a replication master/slave topology, providing a foundation for self-healing recovery, and enabling DBAs and developers to easily identify the most up to date slave in the event of a master failure.  Built directly into the Binlog stream, GTIDs eliminate the need for complex third-party add-ons to provide this same level of tracking intelligence.



New MySQL Replication utilities – A new set of Python Utilities are designed to leverage the new replication GTIDs to provide replication administration and monitoring with automatic fail-over in the event of a failed master, or switchover in the event of maintenance to the master. This eliminates the need for additional third party High-Availability solutions, protecting web and cloud-based services against both planned and unplanned downtime without operator intervention.

New Multi-threaded Slaves - Splits processing between worker threads based on schema, allowing updates to be applied in parallel, rather than sequentially. This delivers benefits to those workloads that isolate application data using databases - e.g. multi-tenant systems. 



SysBench benchmarks using a graduated number of worker threads across 10 schemas show up to 5x in performance gain with multi-threading enabled. 

New Binary Log Group Commit (BGC) – In MySQL 5.6 replication masters now group writes to the Binlog rather than committing them one at a time, significantly improving performance on the master side of the topology.  BGC also enables finer grained locking which reduces lock waits, again, adding to the performance gain, shown here:



MySQL 5.6 shows up to a 180% performance gain over 5.5 in master server throughput with replication enabled (Binlog=1). BGC largely eliminates the trade-off users had to make between performance overhead to the master and the scale-out, HA benefits offered by MySQL replication.

New Optimized Row-based Replication – MySQL 5.6 provides a new option variable binlog-row-image=minimal that enables applications to replicate only data elements of the row image that have changed following DML operations.  This improves replication throughput for both the master and slave(s) and minimizes binary log disk space, network resource and server memory footprint.
New Crash-Safe Slaves – MySQL 5.6 stores Binlog positional data within tables so slaves can automatically roll back replication to the last committed event before a failure, and resume replication without administrator intervention. Not only does this reduce operational overhead, it also eliminates the risk of data loss caused by a slave attempting to recover from a corrupted data file.  Further, if a crash to the master causes corruption of the binary log, the server will automatically recover it to a position where it can be read correctly.
New Replication Checksums – MySQL 5.6 ensure the integrity of data being replicated to a slave by detecting data corruption and returning an error before corrupt events are applied to the slave, preventing the slave itself from becoming corrupt.
New Time-delayed Replication – MySQL 5.6 provides protection against operational errors made on the master from propagating to attached slaves by allowing developers to add defined delays in the replication stream.  With configurable master to slave time delays, in the event of failure or mishap, slaves can be promoted to the new master in order to restore the database to its previous state. It also becomes possible to inspect the state of a database before an error or outage without the need to reload a back up.

Learn about these and all of MySQL 5.6 Replication and High Availability improvements and features, along with all technical documentation, in the MySQL docs
For a rundown of the details, use cases and related benchmarks of all of these features check out Mat Keep’s Developer Zone article.

Improved Performance Schema
The MySQL Performance Schema was introduced in MySQL 5.5 and is designed to provide point in time metrics for key performance indicators.  MySQL 5.6 improves the Performance Schema in answer to the most common DBA and developer problems.  New instrumentation includes:

Statements/Stages -  What are my most resource intensive queries? Where do they spend time?
Table/Index I/O, Table Locks - Which application tables/indexes cause the most load or contention?
Users/Hosts/Accounts - Which application users, hosts, accounts are consuming the most resources?
Network I/O - What is the network load like? How long do sessions idle?
Summaries - Aggregated statistics grouped by statement, thread, user, host, account or object.

The MySQL 5.6 Performance Schema is now enabled by default in the my.cnf file with optimized and auto-tune settings that minimize overhead (< 5%, but mileage will vary), so using the Performance Schema a production server to monitor the most common application use cases is less of an issue.  In addition, new atomic levels of instrumentation enable the capture of granular levels of resource consumption by users, hosts, accounts, applications, etc. for billing and chargeback purposes in cloud computing environments.

MySQL Engineering has several champions behind the 5.6 Performance Schema, and many have published excellent blogs that you can reference for technical and practical details.  To get started see blogs by Mark Leith and Marc Alff.

The MySQL docs are also an excellent resource for all that is available and that can be done with the 5.6 Performance Schema. 


Other Important Enhancements
New default configuration optimizations – MySQL 5.6 introduces changes to the server defaults that provide better out-of-the-box performance on today’s system architectures.  These new defaults are designed to minimize the upfront time spent on changing the most commonly updated variables and configuration options.   Many configuration options are now auto sized based on environment, and can also be set and controlled at server start up.

Improved TIME/TIMESTAMP/DATETIME Data Types:

  • TIME/TIMESTAMP/DATETIME – Now allow microsecond level precision for more precise time/date comparisons and data selection.
  • TIMESTAMP/DATETIME – Improves on 5.5. by allowing developers to assign the current timestamp, an auto-update value, or both, as the default value for TIMESTAMP and DATETIME columns, the auto-update value, or both.
  • TIMESTAMP - Columns are now nullable by default.  TIMESTAMP columns no longer get DEFAULT NOW() or ON UPDATE NOW() attributes automatically without them being explicitly specified and non-NULLable TIMESTAMP columns without explicit default value treated as having no default value.

Better Condition Handling – GET DIAGNOSTICS
MySQL 5.6 enables developers to easily check for error conditions and code for exceptions by introducing the new MySQL Diagnostics Area and corresponding GET DIAGNOSTICS interface command. The Diagnostic Area can be populated via multiple options and provides 2 kinds of information:

  • Statement - which provides affected row count and number of conditions that occurred
  • Condition - which provides error codes and messages for all conditions that were returned by a previous operation

The addressable items for each are:



The new GET DIAGNOSTICS command provides a standard interface into the Diagnostics Area and can be used via the CLI or from within application code to easily retrieve and handle the results of the most recent statement execution:

mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1
-> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
mysql> SELECT @p1, @p2;
+-------+------------------------------------+
| @p1   | @p2                                |
+-------+------------------------------------+
| 42S02 | Unknown table 'test.no_such_table' |
+-------+------------------------------------+


Options for leveraging the MySQL Diagnostics Area are detailed here. You can learn more about GET DIAGNOSTICS here.  

Improved IPv6 Support

  • MySQL 5.6 improves INET_ATON() to convert and store string-based IPv6 addresses as binary data for minimal space consumption.
  • MySQL 5.6 changes the default value for the bind-address option from “0.0.0.0” to “0::0” so the MySQL server accepts connections for all IPv4 and IPv6 addresses.  You can learn more here.

Improved Partitioning

  • Improved performance for tables with large number of partitions – MySQL 5.6 now performs and scales on highly partitioned systems, specifically for INSERT operations that span upwards of hundreds of partitions.
  • Import/export tables to/from partitioned tables - MySQL 5.6 enables users to exchange a table partition or sub-partition with a table using the ALTER TABLE ... EXCHANGE PARTITION statement; existing rows in a partition or subpartition can be moved to a non-partitioned table, and conversely, any existing rows in a non-partitioned table can be moved to an existing table partition or sub-partition. 
  • Explicit partition selection - MySQL 5.6 supports explicit selection of partitions and subpartitions that are checked for rows matching a given WHERE condition. Similar to automatic partition pruning, the partitions to be checked are specified/controlled by the issuer of the statement, and is supported for both queries and a number of DML statements (SELECT, DELETE, INSERT, REPLACE, UPDATE, LOAD DATA, LOAD XML). 


Improved GIS: Precise spatial operations - MySQL 5.6 provides geometric operations via precise object shapes that conform to the OpenGIS standard for testing the relationship between two geometric values. 

Conclusion

MySQL 5.5 has been called the best release of MySQL ever.  MySQL 5.6 builds on this by providing across the board improvements in performance, scalability, transactional throughput, availability and performance related instrumentation all designed to keep pace with requirements of the most demanding web, cloud and embedded use cases. The MySQL 5.6 Release Candidate is now available for download for early adopter and development purposes.

Next Steps

As always, thanks for reading, and thanks for your continued support of MySQL!


Monday Jan 21, 2013

MySQL Webinars in the Next Few Days

Interested in the latest MySQL information across different topics? Don't miss our webinars taking place this week and next!

Thursday, January 24

What’s New in MySQL Connector/.NET 6.6

The recently released MySQL Connector/.NET 6.6 contains impressive new features and capabilities. Join this technical webinar for a quick overview of MySQL Connector/.NET fundamentals followed by a demo and a deeper dive into MySQL Connector/.NET 6.6 technologies.

Learn More and Register Now

Tuesday, January 29

MySQL Enterprise Edition Management Tools

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, and uptime. In this webinar, we will focus on the management tools, helping you better understand how you can benefit from them.

Learn More and Register Now

Wednesday, January 30

MySQL and Hadoop: Big Data Integration - Unlocking New Insights

Join this webinar to learn how integrating MySQL with Hadoop enables organizations to gain deeper insights into their customers, partners, and business processes, asking questions of their data that were never previously possible.

Learn More and Register Now

Join us and take advantage of the live Q&A during all our webinars!

Optimize MySQL Performance with the Authentic MySQL Training

The MySQL database provides top-level performance. Here are two courses to help you get the most out of your MySQL system.

Troubleshooting MySQL Performance With Sveta Smirnova

This 1 day seminar is an opportunity to interact with Sveta Smirnova, MySQL engineer, expert on MySQL Performance and author of MySQL Troubleshooting.

Sign up for this seminar scheduled for February 28th, 2013 in London, England

Sveta will start from basics, working towards more advanced cases that DBAs would usually need years of experience to identify or solve.

This seminar covers performance problems in MySQL applications. You learn why issues happen in the first place and how to react to and solve situations including:

  • Query runs slow
  • Query execution time increases during life of the application
  • MySQL server uses more and more system resources
  • Slave cannot catch up the master

MySQL Performance Tuning Course

In this 4 day courseyou learn practical, safe, highly efficient ways to optimize performance for the MySQL Server. This course prepares each student with the skills needed to utilize tools for monitoring, evaluating and tuning. Students evaluate the architecture, learn to use the tools, configure the database for performance, tune application and SQL code, tune the server, examine the storage engines, assess the application architecture, and learn general tuning concepts.

Take this class through one of the following delivery types:

  • Training-on-Demand: You can start training within 24 hours of registering, following lecture material via streaming video at your own pace and scheduling time to do lab exercises at your convenience.
  • Live-Virtual Class: Follow a live instructor-led event without the inconvenience or cost of travel. Take the course from your own desk, connecting with the instructor and lab environment virtually. There is already a wide range of events on the schedule.
  • In-Class: Travel to a classroom to take this class. Below is a sample of events already on the schedule:

 Location

 Date

 Delivery Language

 Mechelen, Belgium

 13 May 2013

English 

 London, England

 9 April 2013

English 

 Aix-en-Provence, France

 10 June 2013

French 

 Rennes, France

 23 April 2013

French 

 Budapest, Hungary

 5 February 2013

Hungarian 

 Milan, Italy

 18 February 2013

Italian 

 Rome, Italy

 2 April 2013

Italian 

 Riga, Latvia

 11 March 2013

Latvia 

 Amsterdam, Netherlands

 10 June 2013

Dutch 

 Nieuwegein, Netherlands

 11 March 2013

Dutch 

 Warsaw, Poland

 11 February 2013

Polish 

 Lisbon, Portugal

 6 May 2013

European Portugese 

 Porto, Portugal

 6 May 2013

European Portugese 

 Barcelona, Spain

 25 March 2013

Spanish

 Madrid, Spain

 27 May 2013

Spanish 

 Tokyo, Japan

 18 June 2013

Japanese 

 Tokyo, Japan

 25 February 2013

Japanese 

 Edmonton, Canada

 15 April 2013

English 

 Ottawa, Canada

 19 February 2013

English 

 Toronto, Canada

 19 February 2013

English 

 Montreal, Canada

 19 February 2013

English 

 Mexico City, Mexico

 5 February 2013

Spanish 

 Sao Paolo, Brazil

 15 April 2013

Brazilian Portugese 

To register for or learn more about this course, to express interest in additional events or learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql

Friday Jan 18, 2013

Achieve the Highest Levels of MySQL Scalability, Security & Uptime

Oracle's MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to help you reduce the cost, risk & time to deploy and manage your MySQL applications.

Access our Resource Kit to discover:

  • How to boost scalability by 20x with MySQL Enterprise Scalability
  • How to automatically detect and recover from failures with MySQL Enterprise High Availability
  • How to eliminate security vulnerabilities, improve replication and optimize performance with the MySQL Enterprise Monitor
  • How MySQL Enterprise Backup reduces the risk of data loss with online "Hot" backups of your databases
  • How to make the most of your MySQL deployments with Oracle Premier Support for MySQL

Learn how to achieve the highest levels of MySQL scalability, security and uptime, either on-premise or in the cloud.

Access demos, white papers and case studies in our Resource Kit now!

ISVs & OEMs will find out how they can increase differentiation and customer satisfaction with MySQL Enterprise Edition.

Tuesday Jan 15, 2013

Upcoming MySQL Events

Oracle's MySQL team is running/participating to a number of events during the upcoming weeks and months. Don't miss this chance to learn about the latest developments straight from the source and to get all your questions answered!

Additional events will likely be scheduled down the road and posted on our events page, but you can already register for the following ones:

MySQL Tech Tour: Big Data and High Availability with MySQL– Pleasanton, California

January 22

MySQL Tech Tour: Big Data and High Availability with MySQL– Belmont, California

January 23

FOSDEM – Brussels, Belgium

February 2-3

MySQL Tech Tour: From the Web to the Cloud – Istanbul, Turkey

February 5

Oracle & Zend LAMP Seminar - Milan, Italy

February 15

MySQL Tech Tour for Software & Hardware Vendors - Arie Petach Tikva, Israel

February 19

MySQL Tech Tour: From the Web to the Cloud – Oslo, Norway

February 21

MySQL Tech Tour: From the Web to the Cloud – Brussels, Belgium

February 21

MySQL Tech Tour: From the Web to the Cloud – Stockholm, Sweden

March 20

MySQL Tech Tour: From the Web to the Cloud – Munich, Germany

April 17

MySQL Tech Tour: From the Web to the Cloud – Baden, Switzerland

April 24

We hope to see many of you at those events!

Monday Jan 14, 2013

Take the Authentic MySQL for Developers Training Course

The authentic MySQL for Developers course will introduce you to tools used by MySQL development professionals and you get the opportunity to work through examples specific to Java and PHP development cases.

In this course you will learn to:

  • Use MySQL tools to connect to databases.
  • Examine database structure and contents.
  • Generate database structure diagrams.
  • Execute complex queries.
  • Manipulate numeric, text, spatial and temporal data.
  • Execute complex subqueries and joins.

You can take this 5 day live-instructor course via these two delivery methods:

  • Live-Virtual Class: Take this class from your own desk by choosing from the many events already on the schedule.
  • In-Class: Travel to a training center to take this class. Below is a selection of events already on the schedule.

 Location

 Date

 Delivery Language

Vienna, Austria

 4 March 2013

 German

 London, England

 4 March 2013

 English

 Gummersbach, Germany

 11 February 2013

 German

 Munich, Germany

 15 April 2013

 German

 Budapest, Hungary

 15 April 2013

 Hungarian

 Milan, Italy

 21 January 2013

 Italian

 Rome, Italy

 11 March 2013

 Italian

 Turin, Italy

 25 February 2013

 Italian

 Nieuwegein, Netherlands

13 May 2013

 Dutch

 Warsaw, Poland

 18 February 2013

 Polish

 Lisbon, Portugal

 18 February 2013

 Portugal

 Porto, Portugal

 18 February 2013

 Portugal

 Barcelona, Spain

 18 February 2013

 Spanish

 Madrid, Spain

 13 May 2013

 Spanish

 Bern, Switzerland

 11 April 2013

 German

 Zurich, Switzerland

 11 April 2013

 German

 Nairobi, Kenya

 18 May 2013

 English

 Tunis, Tunisia

 20 May 2013

 French

 Mexico City, Mexico

 28 January 2013

 Spanish

 Sao Paulo, Brazil

 11 March 2013

 European Portugese

To register for an event or to learn more about this course or other parts of the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

Tuesday Jan 08, 2013

Deep Dive into GTIDs and MySQL 5.6 - What, Why and How

Global Transaction Identifiers (GTIDs) are one of the key replication enhancements in MySQL 5.6. GTIDs make it simple to track and compare replication across a master - slave topology. This enables:

- Much simpler recovery from failures of the master,

- Introduces great flexibility in the provisioning and on-going management of multi-tier or ring (circular) replication topologies.

A new on-demand MySQL 5.6 GTID webinar delivered by the replication engineering team is now available, providing deep insight into the design and implementation of GTIDs, and how they enable users to simplify MySQL scaling and HA. The webinar covers:

- Concepts: What is a GTID? How does the server generate GTIDs? What is the life cycle of GTIDs? How are GTIDs used to connect to a master?

- Handling conflicts

- How to skip transactions using GTIDs

- What happens when binary logs are purged

- How to provision a new slave or restore from a backup

- MySQL utilities for automated failover and controlled switchover

To whet your appetite, an extract of the Q&A from the webinar is as follows. These, and many other questions were answered during the session:

Q. Which versions of MySQL support GTIDs?

A. MySQL 5.6.5 and above

Q. Is GTID ON by default in 5.6?

A. It is OFF by default

Q. What does the GTID contain?

A. It is made up of a unique ID for the server followed by an ever-increasing counter that's specific to that server

Q: Do GTIDs introduce any increased space requirements?

A: Yes, since GTIDs are stored in the binary log, the binary logs will be larger. However, we expect the overhead to be relatively small. GTIDs are written to the binary log in two places:

(1) A small header is stored in the beginning of the binary log. This contains the variable @@gtid_purged, i.e., a list of previously logged GTIDS. Since the list is range-compressed, this is expected to be small: a small fixed-size header plus 40 bytes times the number of master servers in your topology.

(2) A fixed size header is added before each transaction in the binary log. This is 44 bytes, so will typically be small compared to the transaction itself.

Q. I understand GTID's are associated with Transactions. How do they map to the events within each transaction, or do GTID's map as an event itself in a binlog file?

A. Yes, GTIDs are associated with transactions. In the binary log, the GTID is realized as an event written prior to the events that constitute the transaction. The event is called a Gtid_log_event.

Q What if a transaction spans a filtered out table and a non-filtered out table? How does it get recorded on the slave?

A. If the filters are on the master, then a partly logged transaction will be replicated with its GTID.

If filtering on the slave side, a partial image will be processed on the slave and the original GTID is logged (to the slave's binlog) with the processed transaction.

Q. Prior to GTID, to build a new slave, we use mysqldump --master-data=1 to get the slave starting sync point in the dump. With GTID enabled, does it set the gtid_executed / purged in the dump instead?

A. Yes, mysqldump will detect that the server uses GTIDs and output a SET GTID_PURGED statement. (And there is an option to turn off that, e.g., in case you want to execute the output on an old server).

Q. How do GTIDs enable failover and recovery?

A. GTIDs are using in combination with the MySQL utilities. The mysqlfailover and rpladmin utilities provide administration of GTID-enabled slaves, enabling monitoring with automatic failover and on-demand switchover, coupled with slave promotion. GTIDs make it straightforward to reliably failover from the master to the most current slave automatically in the event of a failure. DBAs no longer need to manually analyze the status of each of their slaves to identify the most current when seeking a target to promote to the new master.

Resources to Get Started

In addition to the webinar, here are some other key resources that will give you the detail you need to take advantage of GTIDs in your most important MySQL workloads:

- Engineering blog: Global Transaction Identifiers – why, what, and how

- Engineering blog: Advanced use of GTIDs

- Documentation: Setting up replication with GTIDs 

- Video Tutorial: MySQL replication utilities for auto-failover and switchover 

- Engineering Blog: Controlling read consistency with GTIDs 

If you have any comments, questions or feature requests, don't hesitate to leave a comment on this blog

Friday Jan 04, 2013

Gain MySQL Database Administrator Skills to Access Top Data Jobs

As 2013 begins, MySQL is way ahead for job openings in the data area. So perfect your MySQL database administration skills with the authentic MySQL for Database Administrator course.

In this 5 day course, you learn to install the MySQL Server, set up replication and security, perform database backups and performance tuning, and protect MySQL database.

You can take this course through one of the following delivery methods

  • Training-on-Demand: Take the course at your pace from your own desk, following the instructor delivery of lecture material through streaming video and scheduling your lab exercises at a time that suits you.
  • Live-Virtual: Follow a live event from your own desk - no travel required - with live virtual delivery
  • In-Class: Travel to a classroom to take this event. Below is a sample of in-class events on the scheduled:

 Location

 Date

 Delivery Language

 Vienna,  Austria

 25 February 2013

German

 Mechelen, Belgium

 25 February 2013

English

London, England 

 11 February 2013

English 

Paris, France 

11 February 2013 

French 

Dresden, Germany 

18 February 2013

German 

Dusseldorf, Germany 

 11 March 2013 

German 

Gummerbach, Germany 

4 February 2013 

German 

Hamburg, Germany 

28 January 2013 

German 

Munich, Germany 

11 February 2013 

German 

Budapest, Hungary

18 March 2013 

Hungarian

Belfast, Ireland

 24 June 2013

 English

 Milan, Italy

11 March 2013 

Italian 

 Rome, Italy

 18 February 2013

Italian

 Turin, Italy

 11 February 2013

Italian

 Amsterdam, Netherlands

 24 June 2013

Dutch

 Nieuwegein, Netherlands

 8 April 2013

Dutch

 Warsaw, Poland

 14 January 2013

Polish

Lisbon, Portugal 

15 April 2013 

 European Portugese

Porto, Portugal 

 15 April 2013

 European Portugese

 Barcelona, Spain

4 February 2013 

Spanish 

 Madrid, Spain

 21 January 2013

Spanish

 Bern, Switzerland

7 February 2013 

German 

 Zurick, Switzerland

 7 February 2013

German

 Istanbul, Turkey

 25 February 2013

Turkish

 Nairobi, Kenya

 28 January 2013

 English

 Johannesburg, South Africa

 9 December 2013

 English

 Tunis, Tunisia

 25 March 2013

 French

 Bangalore, India

 28 January 2013

 English

 Tokyo, Japan

 4 February 2013

Japanese

 Tokyo, Japan

25 April 2013 

Japanese 

 Petaling Jaya, Malaysia

4 March 2013 

 English 

 Singapore

28 January 2013 

 English

 Brisbane, Australia

 4 March 2013

 English

 Auckland, New Zealand

 21 January 2013

 English

 Ottawa, Canada

 28 January 2013

 English

 Toronto, Canada

 28 January 2013

 English

 Montreal, Canada

 28 January 2013

 English

 Curitiba, Brazil

7 January 2013 

 Brazilan Portugese

 Sao Paolo, Brazil

14 January 2013 

 Brazilan Portugese 

 Mexico City, Mexico

21 January 2013 

Spanish 

To find out more about this course or other courses on the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

Note, many organizations deploy both Oracle Database and MySQL side by side to serve different needs, and as a database professional you can find training courses on both topics at Oracle University! Check out the upcoming Oracle Database training courses and MySQL training courses. Even if you're only managing Oracle Databases at this point of time, getting familiar with MySQL will broaden your career path with growing job demand.

About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« January 2013 »
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
9
10
11
12
13
16
17
19
20
22
23
25
26
27
29
30
  
       
Today