Monday Feb 10, 2014

MySQL Cluster is a Resilient and Scalable Database Platform

MySQL Cluster is a highly resilient and scalable database platform designed to deliver 99.999% availability with features such as self-healing and online operations, and capable of performing over 1,00,000,000 updates per minute. The full feature set includes development and management platforms alongside monitoring and administration tools, all backed by Oracle Premier Lifetime Support.

To learn more about MySQL Cluster, consider taking the MySQL Cluster training. Events already on the schedule for this 3-day instructor-led course include:

 Location

 Date

 Delivery Language

 Berlin, Germany

 26 May 2014

 German

 Munich, Germany

 14 April 2014

 German

  Rome, Italy

 17 February 2014

 Italian

 Petaling Jaya, Malaysia

 16 July 2014

 English

 Warsaw, Poland

 12 March 2014

 Polish

 San Francisco, CA, United States

 28 May 2014

 English

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

Monday Jan 13, 2014

MySQL - An Excellent Choice for a Low Cost Embedded Database

MySQL is an excellent choice for a low cost embedded database. Over 3,000 ISVs, OEMs and VARs choose MySQL for this purpose and benefit from significant cost savings when compared to using a competing database product.

The MySQL for Developers training course teaches you how to plan, design and implement applications using MySQL. Expert Oracle University instructors teach you through realistic examples, interactive instruction and hands-on exercises.

This course brings you lots of interesting facts. For example, the 'libmysqld' library is effectively a MySQL server in its own right. it is shipped as part of the standard MySQL installation. Developers with C/C++ ability can embed this library easily within their applications and it can do most of what a standard cient/server installation can do, with notable exceptions being the lack of replication capability and a limit on the number of simultaneous client connections. You can find out more about using embedded databases in MySQL by downloading this white paper.

You can take the MySQL for Developers course as a:

  • Live-Virtual Event: Take this course from your own desk, no travel required. Choose from a selection of events on the schedule to suit different time-zones.
  • In-Class Event: Travel to an education center to take this course. Below is a selection of the in-class events already on the schedule.

Location

 Date

 Delivery Langauge

Brussels, Belgium

 12 May 2014

 English

 Milan, Italy

 7 April 2014

 Italian 

 Rome, Italy

 10 March 2014

 Italian

Utrecht, Netherlands

 12 May 2014

 English

 Makati City, Philippines

 17 February 2014

 English

 Warsaw, Poland

 27 January 2014

 Polish

 Lisbon, Portugal

 26 May 2014

 European Portuguese

 Porto, Portugal

 26 May 2014

European Portuguese 

 Petaling Jaya, Malaysia

21 April 2014  

English 

 Singapore

17 February 2014  

English

 Madrid, Spain

17 February 2014  

Spanish 

 Valencia, Spain

21 April 2014  

 Spanish

 Bern, Switzerland

 17 February 2014

German 

 Zurick, Switzerland

17 February 2014  

German 

Learn to develop applications with PHP and use MySQL efficiently for those applications with the MySQL and PHP - Develop Dynamic Web Applications course. You can take this course as a live-virtual event or an in-class event. Some in-class events on the schedule include:

 Location  Date  Delivery Language
 Rome, Italy
 5 May 2014
 Italian
 Turin, Italy
 17 May 2014
 Italian
 Warsaw, Poland
 3 February 2014
 Polish
 Tunis, Tunisia
 17 March 2014
 French

To learn more about the authentic MySQL curriculum, register for an event or request an additional event, please go to the MySQL Training and Certification site.

Monday Dec 09, 2013

Improve Your DBA Career Prospects with MySQL Database Administrators Training

MySQL DBA skills are highly sought after in the market place. The MySQL for Database Administrators course is a highly popular course created by the MySQL experts to give you hands-on experience with tasks ranging from installing MySQL to investigating different replication scenarios and planning for disaster recovery.

You can take this 5-day instructor-led course as a:

  • Live-Virtual Event: Take this class from your own desk - no travel required. Choose from a selection of events on the schedule to suit different timezones.
  • In-Class Event: Travel to an education center to take this class. Below is a selection of the events already on the schedule for this course.

 Location

 Date

 Delivery Language

 Brussels, Belgium

 13 January 2014

English

 London, England

16 December 2013 

 English 

 Belfast, Ireland

17 March 2014 

English 

 Milan, Italy

27 January 2014 

Italian 

 Rome, Italy

 17 February 2014

Italian

 Turin, Italy

 24 March 2014

Italian 

 Utrecht, Netherlands

 13 January 2014

 English

 Barcelona, Spain

 7 April 2014

 Spanish

 Madrid, Spain

 3 February 2014

 Spanish

 Casablanca, Morocco

 27 January 2014

 English

 Lagos, Nigeria

 7 April 2014

 English

 Sandton, South Africa

 9 December 2013

 English

Tunis, Tunisia 

 24 March 2014

French

 Jakarta Barat, Indonesia

9 December 2013 

 English 

Taking the MySQL for Database course helps prepare you for the MySQL 5.6 Database Administrator certification exam.

To register for this course, to request an additional event or to learn about other courses on the authentic MySQL curriculum, go to http://education.oracle.com/mysql.

A selection of MySQL courses are also available through training-on-demand, where you can take a course at your own pace, starting training within 24 hours of registration.

Tuesday Apr 02, 2013

Wide Selection of MySQL DBA Training Events

MySQL for Database Administrators is the foundation of the authentic MySQL Curriculum. By taking this live 5-day instructor-led course, you find out how MySQL enhancements increase performance, scalability, reliability and manageability of your Web, Cloud and embedded application requirements.

You will learn to:

  • Setup the server and databases
  • Maintain and secure data
  • Configure for performance and high availability
  • Explore real-world problems and discover best practices

You can take this course as a:

  • Live Virtual Event: Take this course from your own desk choosing from 15 events already on the schedule, delivered in English or Germany.
  • In-Class Event: Travel to an education center to take this course. Below is an impressive sample of events already on the schedule at locations around the world:

 Location

 Date

 Delivery Language

 Mechelen, Belgium

 3 June 2013

 English

 London, England

 13 May 2013

 English

 Aix en Provence, France

 8 April 2013

 French

 Dresden, Germany

 3 June 2013

 German

 Dusseldorf, Germany

 24 June 2013

 German

 Gummersbach, Germany

 1 July 2013

 German

 Hamburg, Germany

 22 April 2013

 German

 Munich, Germany

 22 April 2013

 German

Munster, Germany 

 9 September 2013

 German

Stuttgart, Germany 

8 July 2013 

 German

Belfast, Ireland 

24 June 2013

 English

Milan, Italy 

 6 May 2013 

 Italian

Rome, Italy 

17 June 2013 

 Italian

Turin, Italy 

27 May 2013

 Italian

 Budapest, Hungary

  4 November 2013

 Hungarian

 Amsterdam, Netherlands

 24 June 2013

 Dutch

 Warsaw, Poland

 20 May 2013

 Polish

 Barcelona, Spain

 6 May 2013

 Spanish

 Madrid, Spain

 6 May 2013

 Spanish

 Bern, Switzerland

 26 June 2013

 German

 Zurich, Switzerland

 26 June 2013

 German

 Istanbul, Turkey

 15 April 2013

Turkish

Cairo, Egypt 

 30 June 2013

English

Nairobi, Kenya 

20 May 2013

 English 

Johannesburg, South Africa

9 December 2013

English

 Tunis, Tunisia

13 May 2013

French

Dubai

4 April 2013

English

  Petaling Jaya, Malaysia

  6 May 2013

 English

 Makati, Philippines

 6 May 2013

 English

 Singapore

 8 April 2013

 English

 Bangkok, Thailand

 9 April 2013

 English

 Brisbane, Australia

 27 May 2013

 English

 Wellington, New Zealand

 13 May 2013

 English

 Sao Paulo, Brazil

 8 April 2013

 Brazilian Portugese

 Mexico City, Mexico

 20 May 2013

 Spanish

For more information about thsi course or other courses on the authentic MySQL Curriculum, go to http://oracle.com/education.

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!


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

Monday Oct 29, 2012

Harness MySQL's Continued Performance Tuning Improvements

To fully harness the continued improvements in performance tuning you get with MySQL, take the MySQL Performance Tuning course.

This 4 day class teaches you practical, safe, highly efficient ways to optimize performance for the MySQL Server. You will learn the skills needed to use tools for monitoring, evaluating and tuning. 

You can take this course in the following three ways:

  • Training-on-Demand: Follow this course at your own pace and from your own desk with streaming video of instructor delivery and booking time to follow hands-on exercises at your own convenience.
  • Live-Virtual: Attend a live instructor-led event from your own desk. Choose from the numerous events on the schedule.
  • In-Class:  Travel to an education center to follow this class. A sample of events on the schedule is shown below:

 Location

 Date

 Delivery Language

 Tokyo, Japan

 19 November 2012

 Japanese

 Mechelen, Belgium

 4 February 2013

 English

 London, England

 19 November 2012

 English

 Budapest, Hungary

 21 May 2013

 Hungarian

 Milan, Italy

 14 January 2013

 Italian

 Rome, Italy

 3 December 2012

 Italian

 Riga, Latvia

 10 December 2012

 Latvian

 Amsterdam, Netherlands

 7 January 2013

 Dutch

 Nieuwegein, Netherlands

 26 November 2012

 Dutch

 Warsaw, Poland

 3 December 2012

 Polish

 Lisbon, Portugal

 4 February 2013

 European Portugese

 Porto, Portugal

 4 February 2013

 European Portugese

 Barcelona, Spain

 25 March 2013

 Spanish

 Madrid, Spain

 17 December 2012

 Spanish

 Sydney, Australia

 26 November 2012

 English

 Edmonton, Canada

 10 December 2012

 English

 Montreal, Canada

 26 November 2012

 English

 Ottawa, Canada

 26 November 2012

 English

 Toronto, Canada

 26 November 2012

 English

 Vancouver, Canada

 10 December 2012

 English

 Sao Paolo, Brazil

 26 November 2012

 Brazilan Portugese

For more information on this class or to know more about other courses on the authentic MySQL curriculum. see 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.

Tuesday Jul 31, 2012

MySQL Cluster Performance Best Practices: Q & A

With its distributed, shared-nothing, real-time design, MySQL Cluster has attracted a lot of attention from developers who need to scale both read and write traffic with ultra-low latency and fault-tolerance, using commodity hardware. With many proven deployments in web, gaming, telecoms and mobile use-cases, MySQL Cluster is certainly able to meet these sorts of requirements.

But, as a distributed database, developers do need to think a little differently about data access patterns along with schema and query optimisations in order to get the best possible performance.

Sharing best practices developed by working with MySQL Cluster's largest users, we recently ran a Performance Essentials webinar, and the replay is now available, on-demand, for you to listen to in the comfort of your own office.

The webinar also accompanies a newly published Guide to optimizing the performance of MySQL Cluster.

We received a number of great questions over the course of the webinar, and I thought it would be useful to share a selection of those:

Q. How do I calculate and then monitor memory usage with MySQL Cluster?

A. If designing a completely new database, the following calculations can be used to help determine the approximate memory sizing requirements for the data nodes:

(in memory) Data Size * Replicas * 1.25 = Total Database Memory Requirements

Example: 50 GB * 2 * 1.25 = 125 GB

(Data Size * Replicas * 1.25)/Nodes = RAM Per Node

Example: (2 GB * 2 * 1.25)/4 = 31.25 GB

To see how much of the configured memory is currently in use by the database, you can query the ndbinfo.memory usage table

If using MySQL Cluster CGE then you can view this information over time in a MySQL Enterprise Monitor graph.


Q. Would enabling Disk space Table Space be an impact on the Query Performance ?

A. It can do. The only reason to use Disk based table spaces is when you do not have sufficient memory to store all data in-memory. Therefore some of your disk based data will be uncached at some time, and reads or writes which access this data will stall while the necessary pages are read into the page buffer. This can reduce throughput.


Q. I've seen that MySQL Cluster 7.2 can speed up JOIN operations by 70x. How does it do this?

A. There are two new features in MySQL Cluster 7.2, which when combined, can significantly improve the performance of joins over previous versions of MySQL Cluster:

- The Index Statistics function enables the SQL optimizer to build a better execution plan for each query. In the past, non-optimal query plans required a manual enforcement of indexes via USE INDEX or FORCE INDEX to alter the execution plan. ANALYZE TABLE must first be run on each table to take advantage of this.

- Adaptive Query Localization (AQL) allows the work of the join to be distributed across the data nodes (local to the data it’s working with) rather than up in the MySQL Server; this allows more computing power to be applied to calculating the join as well as dramatically reducing the number of messages being passed around the system.

You can learn more about AQL and a sample query here


Q. Can all JOINs use AQL?

A. In order for a join to be able to exploit AQL (in other words be “pushed down” to the data nodes), it must meet the following conditions:

1. Any columns to be joined must use exactly the same data type. (For example, if an INT and a BIGINT column are joined, the join cannot be pushed down). This includes the lengths of any VARCHAR columns.

2. Joins referencing BLOB or TEXT columns will not be pushed down.

3. Explicit locking is not supported; however, the NDB (MySQL Cluster) storage engine's characteristic implicit row-based locking is enforced.

4. In order for a join to be pushed down, child tables in the Join must be accessed using one of the ref, eq_ref, or const access methods, or some combination of these methods. These access methods are described in the documentation

5. Joins referencing tables explicitly partitioned by [LINEAR] HASH, LIST, or RANGE currently cannot be pushed down

6. If the query plan decides to 'Using join buffer' for a candidate child table, that table cannot be pushed as child. However, it might be the root of another set of pushed tables.

7. If the root of the pushed Join is an eq_ref or const, only child tables joined by eq_ref can be appended. (A ref joined table will then likely become a root of another pushed Join)

These conditions should be considered when designing your schema and application queries – for example, to comply with constraint 4, attempt to make any table scan that is part of the Join be the first clause.

Where a query involves multiple levels of Joins, it is perfectly possible for some levels to be pushed down while others continue to be executed within the MySQL Server.

If your application consists of many of these types of JOIN operations which cannot be made to exploit AQL, other MySQL storage engines such as InnoDB will present a better option for your workload.


Q. What are best practices for data model and query design?

A. The data model and queries should be designed to minimize network roundtrips between hosts. Ensuring that joins meet the requirements for

AQL and avoiding full table scans can help with this.

Looking up data in a hash table is a constant time operation, unaffected by the size of the data set

Looking up data in a tree (T-tree, B-tree etc) structure is logarithmic (O (log n)).

For a database designer this means it is very important to choose the right index structure and access method to retrieve data. We strongly recommend application requests with high requirements on performance be designed as primary key lookups. This is because looking up data in a hash structure is faster than from a tree structure and can be satisfied by a single data node. Therefore, it is very important that the data model takes this into account. It also follows that choosing a good primary key definition is extremely important.

If ordered index lookups are required then tables should be partitioned such that only one data node will be scanned.

The distributed nature of the Cluster and the ability to exploit multiple CPUs, cores or threads within nodes means that the maximum performance will be achieved if the application is architected to run many transactions in parallel. Alternatively you should run many instances of the application simultaneously to ensure that the Cluster is always able to work on many transactions in parallel.

Take a look at the Guide to optimizing the performance of MySQL Cluster for more detail


Q. What are best practices for parallelising my application and access to MySQL Cluster?

A. As mentioned MySQL Cluster is a distributed, auto-sharded database. This means that there is often more than one Data Node that can work in parallel to satisfy application requests.

Additionally, MySQL Cluster 7.2 enhances multi-threading so data nodes can now effectively exploit multiple threads / cores. To use this functionality, the data nodes should be started using the ndbmtd binary rather than ndb and config.ini should be configured correctly

Parallelization can be achieved in MySQL Cluster in several ways:

- Adding more Application Nodes

- Use of multi-threaded data nodes

- Batching of requests

- Parallelizing work on different Application Nodes connected to the Data Nodes

- Utilizing multiple connections between each Application Node and the Data Nodes (connection pooling)

How many threads and how many applications are needed to drive the desired load has to be studied by benchmarks. One approach of doing this is to connect one Application Node at a time and increment the number of threads. When one Application Node cannot generate any more load, add another one. It is advisable to start studying this on a two Data Node cluster, and then grow the number of Data Nodes to understand how your system is scaling.

If you have designed your application queries, and data model according to best practices presented in the Performance Guide, you can expect close to double the throughput on a four Data Node system compared to a two Data Node system, given that the application can generate the load.

Try to multi-thread whenever possible and load balance over more MySQL servers.

In MySQL Cluster you have access to additional performance enhancements that allow better utilization on multi-core / thread CPUs, including:

- Reduced lock contention by having multiple connections from one MySQL Server to the Data Nodes (--ndb-cluster-connection-pool=X):

- Setting threads to real-time priority

- Locking Data Node threads (kernel thread and maintenance threads to a CPU)


Q. Does MySQL Cluster’s data distribution add complexity to my application and limit the types of queries I can run?

A. No, it doesn't. By default, tables are automatically partitioned (sharded) across data nodes by hashing the primary key. Other partitioning methods are supported, but in most instances the default is acceptable.

As the sharding is automatic and implemented at the database layer, application developers do not need to modify their applications to control data distribution – which significantly simplifies scaling.

In addition, applications are free to run complex queries such as JOIN operations across the shards, therefore users do not need to trade functionality for scalability.


Q. What hardware would you recommend to get the best performance from MySQL Cluster?

A. It varies by node type. For data nodes:

- Up to 32 x x86-64 bit CPU cores. Use as high a frequency as possible as this will enable faster processing of messages between nodes;

- Large CPU caches assist in delivering optimal performance;

- 64-bit hosts with enough RAM to store your in-memory data set

- Linux, Solaris or Windows operating systems.

- 2 x Network Interface Cards and 2 x Power Supply Units for hardware redundancy.

It is important to ensure systems are configured to reduce swapping to disk whenever possible.

As a rule of thumb, have 7x times the amount of DataMemory configured for disk space for each data node. This space is needed for storing 2 Local Checkpoints (LCPs), the Redo log and 3 backups. You will also want to allocate space for table spaces if you are making use of disk-based data – including allowing extra space for the backups.

Having a fast, low-latency disk subsystem is very important and will affect check pointing and backups.

Download the MySQL Cluster Evaluation Guide for more recommendations 

The hardware requirements for MySQL Servers would be a little less:

- 4 - 32 x86-64 bit CPU cores

- Minimum 4GB of RAM. Memory is not as critical at this layer, and requirements will be influenced by connections and buffers.

- 2 x Network Interface Cards and 2 x Power Supply Units for hardware redundancy.


Q. I heard that MySQL Cluster doesn't support Foreign Keys, how can I get around that?

A. Foreign keys are previewed in MySQL Cluster 7.3 Early Access release which you can download and evaluate now. In MySQL Cluster 7.2 and earlier, you can emulate foreign keys programmatically via triggers.


Summary

If you are thinking about using MySQL Cluster for your next project, it is worth investing a little bit of time to get familiar with these performance best practices. The Webinar replay, the MySQL Cluster Performance Guide and the MySQL Cluster Evaluation Guide will give you pretty much everything you need to build high performance, high availability services with MySQL Cluster. 




Wednesday Mar 28, 2012

Announcing MySQL Enterprise Backup 3.7.1

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.1, a maintenance release version that includes bug fixes and enhancements to some of the existing features.

The most important feature introduced in this release is Automatic Incremental Backup. The new  argument syntax for the --incremental-base option is introduced which makes it simpler to perform automatic incremental backups. When the options --incremental & --incremental-base=history:last_backup are combined, the mysqlbackup command  uses the metadata in the mysql.backup_history table to determine the LSN to use as the lower limit of the incremental backup. You no longer need to keep track of the actual LSN (as in the option --start-lsn=LSN) or even the location of the previous backup (as in the option --incremental-base=dir:directory_path)

This release also incudes various bug fixes related to some options used in MEB. The most important are few of them as listed below,

1. The option --force now allows overwriting InnoDB data and log files in  combination with the apply-log and apply-incremental-backup options, and replacing the image file in combination with the backup-to-image and backup-dir-to-image options.

2. Resolved a bug that prevented MEB to interface with third-party storage managers to execute backup and restore jobs in combination with the SBT interface and associated --sbt* options for mysqlbackup.

3. When MEB is run with the copy-back option,  it now displays warnings as existing files are overwritten.

For more information about other bug fixes, please refer to the change-log in http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/meb-news.html

The complete MEB documentation is located at http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html.

You will find the binaries for the new release in My Oracle Support,  https://support.oracle.com
Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB 3.7.1 recently, please do so now and let us know how MEB works for you. Send your feedback to mysql-backup_ww@oracle.com.


Friday Feb 24, 2012

Where Would I Use MySQL Cluster?

MySQL Cluster has long been used in telecommunications network services for Subscriber Data Management (HLR/HSS), Service Delivery Platforms and Value-Added Services, and has also been deployed in certain parts of general web infrastructure.

Following the announcements of MySQL Cluster 7.2 General Availability, including new benchmarks demonstrating MySQL Cluster delivering 1 Billion Queries per Minute, I thought it might be worthwhile to highlight examples of use cases for MySQL Cluster .

Web-Based Payment & Financial Services Platforms

MySQL Cluster can be deployed across a range of applications including payment gateways, trading systems and customer service infrastructure.

Payment Gateways

- These are used by merchants to process customer payments

- The gateways need to integrate with multiple credit and debit card systems

- Multiple payment channels have to be supported, i.e. ePayment, mPayment, In-store, etc.

- MySQL Cluster can be used to record full transaction data, including customer & product information

- This data is persisted for set time periods to enable auditing and fraud detection

Web-Based Trading Systems

- MySQL Cluster can be deployed to support the trading engine, persisting the details of each trade

- MySQL Cluster also provides the storage layer for the store–and-forward messaging system used by traders and customers to track transactions

Customer Service Systems

- MySQL Cluster can be used as a command and control system, providing telephony, web portal and call desk integration

- Inbound calls are routed to customer services representatives and customer account details are retrieved in real-time

- Additional support for Integrated Voice Response systems enabling customer self-service

Core database requirements of these platforms include:

· ACID compliance to support transactional integrity

· Rapid scale-out to support growth in merchants, traders, customers and payment channels

· Very high insert and update rates

· Low, predictable latency to support real-time trading and customer experience

· 99.999% availability to guard against both outages and support on-line maintenance operations needed to seamlessly evolve services (i.e. adding nodes, upgrading schema, etc.)

· Low TCO to maximize trading margins

Session Management and eCommerce

Providing the back-end to on-line retail sites is an area where MySQL Cluster has a strong track record, providing the following services:

- Enabling a seamless experience as users log-in, search and browse products, and then place orders.

- Managing user accounts, storing each new user session, updating customer profiles and maintaining shopping carts

- Recording and tracking user behavior to integrate with merchandizing systems, enabling real-time cross-sell and upsell promotions

Database requirements for eCommerce include

· ACID compliance to support transactional integrity

· Elastic, on-demand scale-out using commodity hardware to support growing user and order volumes, and holiday season peaks

· Low, predictable latency to support a real time user experience

· High availability to avoid downtime resulting in lost sales and compromised customer satisfaction

· On-line schema changes to support the additions of new product categories or customer profiling attributes

Take a look at the MySQL Web Reference Architectures for best practices in scaling highly available, on-line retail sites

On-Line Gaming

With a huge growth in gamers, and gaming platforms, MySQL Cluster can be used to support the core gaming persistence layer:

- MySQL Cluster manages user accounts, gaming entitlements and session state (life-force, weapons, scores, etc.), along with leaderboards, all in real time

- Manages the eCommerce and billing platform (for in-game purchases)

- Command and control system across gaming platforms, integrating multiple services with avatars and devices

Again, the core requirements of the database include:

· Linear, on-demand scalability of both read and write operations to support the ramp in demand when new games gain traction

· High availability

· Low latency for a real-time gaming experience

Event Data and Content Management

Digital Advertising and Customer Relationship Management

MySQL Cluster can be used to capture customer campaign responses in real time

- Campaign responses are consolidated across multiple channels, including web, social media, SMS, and in-store responses.

- Data is replicated in batches to the MySQL InnoDB storage engine for analysis and reporting

Event Data Capture

MySQL Cluster is used to capture real-time data feeds & metadata from environmental sensors, devices and satellites. Data is then replicated to analysis platforms for transformation and processing

Database requirements include:

· The ability to support high volume insert and update rates, with zero data loss

· Scaled-out on commodity hardware

· Flexible replication topologies to other database engines and across data centers

How to Get Started

The above examples illustrate how MySQL Cluster can be used across range of web-based services deployed on-premise or in the cloud.

If you have workloads that have similar demands, it’s worth taking a look at MySQL Cluster 7.2. The new MySQL Cluster Evaluation Guide provides best practices in quickly provisioning proof-of-concepts and benchmarking MySQL Cluster with your application.

We’d love to hear more about they types of workloads that you think would benefit from MySQL Cluster, so please use the comments section below and provide feedback.

Tuesday Jan 10, 2012

Announcing MySQL Enterprise Backup 3.7.0

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.0, with several exciting and advanced features to benefit a wide audience. Included in this release are,

 The gist and usefulness of all these new features are described in short below,

Redo Log Only Incremental Backup:
This is a new type of incremental backup that copies only the InnoDB redo log accumulated since the previous full or incremental backup. The original incremental backup technique copies from the InnoDB data files only those pages that were modified since the previous backup. This incremental backup technique based on the redo log is much faster in most cases than incremental backups that read the data files, if incremental backups are taken frequently. You can choose this new method by specifying the --incremental-with-redo-log-only option on the mysqlbackup command line.

Incremental Backup without specifying LSN:
The new option --incremental-base=dir:/  helps you to perform incremental backup without needing to specify lsn value. The input to --incremental-base=dir:/ option is location of any previous backup over which incremental backup is to be done.

Performance Improvements:
Performance of backup-related I/O operations is improved, particularly on Windows, by reusing I/O library code and best practices from the MySQL Server product. To avoid memory fragmentation and overhead from frequent malloc() / free() sequences, the mysqlbackup command now does all read, compress, uncompress, and comparison operations within a fixed-size buffer that remains allocated while the command runs.

Validation of Backup Image using Checksums:
The new validate option of the mysqlbackup command tests the individual files within a single-file backup using a checksum mechanism. Validation helps to verify the integrity of the single-file backup image as the image file is moved between servers and thereby ensure that backups are reliable and consistent.

Hot Backup of InnoDB .frm files
:
With this new feature, you do not have to manually copy the .frm files to perform restore. The new option --only-innodb-with-frm performs an InnoDB-only backup and backs up even the .frm files of InnoDB tables in non locking mode. Formerly, the InnoDB-only backup required putting the database briefly into a read-only state and copying the .frm files within your own backup script.

Enhancements to Third-Party Media Managers
:
 To customize the interactions between MySQL Enterprise Backup and media management software (MMS), the --sbt-environment option lets you pass application-specific environment settings to the MMS (for example, Oracle Secure Backup). Each vendor that uses the SBT programming interface could implement its own set of environment variables. The --sbt-environment variable lets you pass environment variable values from any invocation method (for example, a Makefile) rather than setting and unsetting the variables within a wrapper shell script.

For more information about MEB features and examples, please see the MEB documentation located <http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html>. My sincere thanks to Lars Thalmann, Sanjay Manwani and all the MEB team members, who have provided valuable features and improvements for every release.

Download the MEB 3.7.0 package from the Oracle Software Delivery Cloud web site <https://edelivery.oracle.com/>. MySQL Enterprise customers can begin deploying MEB 3.7.0 immediately. Users without a MySQL Enterprise license can evaluate MEB 3.7.0 for free for 30 days; please try it out and send your feedback to mysql-backup_ww@oracle.com.



Friday Oct 07, 2011

MySQL Cluster 7.2 (DMR2): NoSQL, Key/Value, Memcached

70x Higher Performance, Cross Data Center Scalability and New NoSQL Interface

Its been an exciting week for all involved with MySQL Cluster, with the announcement of the second Development Milestone Release (7.2.1) at Oracle Open World. Highlights include:

- Enabling next generation web services: 70x higher complex query performance, native memcached API and integration with the latest MySQL 5.5 server

- Enhancing cross data scalability: new multi-site clustering and enhanced active/active replication

- Simplified provisioning: consolidated user privileges.

You can download the DMR for evaluation now from: http://dev.mysql.com/downloads/cluster/ (select Development Milestone Release tab).

You can also read up on the detail of each of these features in the new article posted at the MySQL Developer Zone. In this blog, I’ll summarize the main parts of the announcement.

70x Higher Performance with Adaptive Query Localization (AQL)

Previewed as part of the first MySQL Cluster DMR, AQL is enabled by a new Index Statistics function that allows the SQL optimizer to build a better execution plan for each query.

As a result, JOIN operations are pushed down to the data nodes where the query executes in parallel on local copies of the data. A merged result set is then sent back to the MySQL Server, significantly enhancing performance by reducing network trips.

Take a look at how this is used by a web-based content management to increase performance by 70x

Adaptive Query Localization enables MySQL Cluster to better serve those use-cases that have the need to run real-time analytics across live data sets, along with high throughput OLTP operations. Examples include recommendations engines and clickstream analysis in web applications, pre-pay billing promotions in mobile telecoms networks or fraud detection in payment systems.

New NoSQL Interface and Schema-less Storage with the memcached API

The memcached interface released as an Early Access project with the first MySQL Cluster DMR is now integrated directly into the MySQL Cluster 7.2.1 trunk, enabling simpler evaluation.

The popularity of Key/Value stores has increased dramatically. With MySQL Cluster and the new memcached API, you have all the benefits of an ACID RDBMS, combined with the performance capabilities of Key/Value store.

By default, every Key / Value is written to the same table with each Key / Value pair stored in a single row – thus allowing schema-less data storage. Alternatively, the developer can define a key-prefix so that each value is linked to a pre-defined column in a specific table.

Of course if the application needs to access the same data through SQL then developers can map key prefixes to existing table columns, enabling Memcached access to schema-structured data already stored in MySQL Cluster.

You can read more about the design goals and implementation of the memcached API for MySQL Cluster here.

Integration with MySQL 5.5

MySQL Cluster 7.2.1 is integrated with MySQL Server 5.5, providing binary compatibility to existing MySQL Server deployments. Users can now fully exploit the latest capabilities of both the InnoDB and MySQL Cluster storage engines within a single application.

Users simply install the new MySQL Cluster binary including the MySQL 5.5 release, restart the server and immediate have access to both InnoDB and MySQL Cluster!

Enhancing Cross Data Center Scalability: Simplified Active / Active Replication

MySQL Cluster has long offered Geographic Replication, distributing clusters to remote data centers to reduce the affects of geographic latency by pushing data closer to the user, as well as providing a capability for disaster recovery.

Geographic replication has always been designed around an Active / Active technology, so if applications are attempting to update the same row on different clusters at the same time, the conflict can be detected and resolved. With the release of MySQL Cluster 7.2.1, implementing Active / Active replication has become a whole lot simpler. Developers no longer need to implement and manage timestamp columns within their applications. Also rollbacks can be made to whole transactions rather than just individual operations.

You can learn more here.

Enhancing Cross Data Center Scalability: Multi-Site Clustering

MySQL Cluster 7.2.1 DMR provides a new option for cross data center scalability – multi-site clustering. For the first time splitting data nodes across data centers is a supported deployment option.

Improvements to MySQL Cluster’s heartbeating mechanism with a new “ConnectivityCheckPeriod” parameter enables greater resilience to temporary latency spikes on a WAN, thereby maintaining operation of the cluster.

With this deployment model, users can synchronously replicate updates between data centers without needing conflict detection and resolution, and automatically failover between those sites in the event of a node failure.

Users need to characterize their network bandwidth and latencies, and observe best practices in configuring both their network environment and Cluster. More guidance is available here.

User Privilege Consolidation

User privilege tables are now consolidated into the data nodes and centrally accessible by all MySQL servers accessing the cluster.

Previously the privilege tables were local to each MySQL server, meaning users and their associated privileges had to be managed separately on each server. By consolidating privilege data, users need only be defined once and managed centrally, saving Systems Administrators significant effort and reducing cost of operations.

Summary

The MySQL Cluster 7.2.1 DMR enables new classes of use-cases to benefit from web-scale performance with carrier-grade availability.  We also have a great webinar coming up on Wednesday October 19th  where the engineering and product management team will discuss the enhancements in more detail, and how you can use them today. You can sign up here.

You can download the DMR for evaluation now from: http://dev.mysql.com/downloads/cluster/ (select Development Milestone Release tab).

You can learn more about the MySQL Cluster architecture from our Guide to scaling web databases

Let us know what you think of these enhancements directly in comments of this or the associated blogs. We look forward to working with the community to perfect these new features.

Monday Oct 03, 2011

Synchronously Replicating Databases Across Data Centers – Are you Insane?

 

Well actually….no. The second Development Milestone Release of MySQL Cluster 7.2 introduces support for what we call “Multi-Site Clustering”. In this post, I’ll provide an overview of this new capability, and considerations you need to make when considering it as a deployment option to scale geographically dispersed database services.

You can read more about MySQL Cluster 7.2.1 in the article posted on the MySQL Developer Zone

MySQL Cluster has long offered Geographic Replication, distributing clusters to remote data centers to reduce the affects of geographic latency by pushing data closer to the user, as well as providing a capability for disaster recovery.

Multi-Site Clustering provides a new option for cross data center scalability. For the first time splitting data nodes across data centers is a supported deployment option. With this deployment model, users can synchronously replicate updates between data centers without needing to modify their application or schema for conflict handling, and automatically failover between those sites in the event of a node failure.

MySQL Cluster offers high availability by maintaining a configurable number of data replicas.  All replicas are synchronously maintained by a built-in 2 phase commit protocol.  Data node and communication failures are detected and handled automatically.  On recovery, data nodes automatically rejoin the cluster, synchronize with running nodes, and resume service.

All replicas of a given row are stored in a set of data nodes known as a nodegroup.  To provide service, a cluster must have at least one data node from each nodegroup available at all times.  When the cluster detects that the last node in a nodegroup has failed, the remaining cluster nodes will be gracefully shutdown, to ensure the consistency of the stored databases on recovery.

Improvements to the heartbeating mechanism used by MySQL Cluster enables greater resilience to temporary latency spikes on a WAN, thereby maintaining operation of the cluster. A new “ConnectivityCheck” mechanism is introduced, which must be explicitly configured. This extra mechanism adds messaging overheads and failure handling latency, and so is not switched on by default.

When configuring Multi-Site clustering, the following factors must be considered:

Bandwidth
Low bandwidth between data nodes can slow data node recovery.  In normal operation, the available bandwidth can limit the maximum system throughput.  If link saturation causes latency on individual links to increase, then node failures, and potentially cluster failure could occur.

Latency and performance
Synchronously committing transactions over a wide area increases the latency of operation execution and commit, therefore individual operations are slowed. To maintain the same overall throughput, higher client concurrency is required.  With the same client concurrency level, throughput will decrease relative to a lower latency configuration.

Latency and stability
Synchronous operation implies that clients wait to hear of the success or failure of each operation before continuing. Loss of communication to a node, and high latency communication to a node are indistinguishable in some cases.  To ensure availability, the Cluster monitors inter-node communication.  If a node experiences high communication latency, then it may be killed by another node, to prevent its high latency causing service loss.

Where inter-node latencies fluctuate, and are in the same range as the node-latency-monitoring trigger levels, node failures can result.  Node failures are expensive to recover from, and endanger Cluster availability. 

To avoid node failures, either the latency should be reduced, or the trigger levels should be raised.  Raising trigger levels can result in a longer time-to-detection of communication problems.

WAN latencies
Latency on an IP WAN may be a function of physical distance, routing hops, protocol layering, link failover times and rerouting times. The maximum expected latency on a link should be characterized as input to the cluster configuration.

Survivability of node failures
MySQL Cluster uses a fail fast mechanism to minimize time-to-recovery. Nodes that are suspected of being unreachable or dead are quickly excluded from the Cluster.  This mechanism is simple and fast, but sometimes takes steps that result in unnecessary cluster failure.  For this reason, latency trigger levels should be configured a safe margin
above the maximum latency variation on inter-data node links.

Users can configure various MySQL Cluster parameters including heartbeats, Connectivity_Check, GCP timeouts and transaction deadlock timeouts. You can read more about these parameters in the documentation

Recommendations for Multi-Site Clustering
- Ensure minimal, stable latency;
- Provision the network with sufficient bandwidth for the expected peak load - test with node recovery and system recovery;
- Configure the heartbeat period to ensure a safe margin above latency fluctuations;

- Configure the ConnectivtyCheckPeriod to avoid unnecessary node failures;

- Configure other timeouts accordingly including the GCP timeout, transaction deadlock timeout, and transaction inactivity timeout.

Example
The following is a recommendation of latency and bandwidth requirements for applications with high throughput and fast failure detection requirements:
- latency between remote data nodes must not exceed 20 milliseconds;
- bandwidth of the network link must be more than 1 Gigabit per Second.

For applications that do not require this type of stringent operating environment, latency and bandwidth can be relaxed, subject to the testing recommended above.

As the recommendations demonstrate, there are a number of factors that need to be considered before deploying multi-site clustering. For geo-redundancy, Oracle recommends Geographic Replication, but multi-site clustering does present an alternative deployment, subject to the considerations and constraints discussed above.

You can learn more about scaling web databases with MySQL Cluster from our new Guide.  We look forward to hearing your experiences with the new MySQL Cluster 7.2.1 DMR!

Thursday Sep 29, 2011

MySQL HA Solutions: New Guide Available

Databases are the center of today’s web, enterprise and embedded applications, storing and protecting an organization’s most valuable assets and supporting business-critical applications. Just minutes of downtime can result in significant lost revenue and dissatisfied customers. Ensuring database highly availability is therefore a top priority for any organization.

The new MySQL Guide to High Availability solutions is designed to navigate users through the HA maze, discussing:

- The causes, effects and impacts of downtime;

- Methodologies to select the right HA solution;

- Different approaches to delivering highly available MySQL services;

- Operational best practices to meet Service Level Agreements (SLAs).

As discussed in the new Guide, selecting the high availability solution that is appropriate for your application depends upon 3 core principles:

- The level of availability required to meet business objectives, within budgetary constraints;

- The profile of application being deployed (i.e. concurrent users, requests per second, etc.);

- Operational standards within each data center.

Recognizing that each application or service has different operational and availability requirements, the guide discusses the range of certified and supported High Availability (HA) solutions – from internal departmental applications all the way through to geographically redundant, multi-data center systems delivering 99.999% availability (i.e. less than 5 ½ minutes of downtime per year) supporting transactional web services, communications networks, cloud and hosting environments, etc.

By combining the right technology with the right skills and processes, users can achieve business continuity, while developers and DBAs can sleep tight at night! Download the guide to learn more.

Monday Jul 18, 2011

Simpler and Safer Clustering: MySQL Cluster Manager Update

Clustered computing brings with it many benefits: high performance, high availability, scalable infrastructure, etc. But it also brings with it more complexity.

Why?

Well, by its very nature, there are more “moving parts” to monitor and manage (from physical, virtual and logical hosts) to clustering software to redundant networking components – the list goes on. And a cluster that isn’t effectively provisioned and managed will cause more downtime than the standalone systems it is designed to improve upon.

When it comes to the database industry, analysts already estimate that 50% of a typical database’s Total Cost of Ownership is attributable to staffing and downtime costs. These costs will only increase if a database cluster is not effectively monitored and managed.

Monitoring and management has been a major focus in the development of the MySQL Cluster database, and as part of this focus, the latest release of MySQL Cluster Manager (MCM) hit General Availability last week. You can read all about it in Andrew Morgan's blog.

MySQL Cluster Manager 1.1.1 makes it much simpler to get up and running, to manage the cluster and to allow multiple clusters to be managed from a single process.

MySQL Cluster Manager is part of the commercial Carrier-Grade Edition but anyone is free to download and use MySQL Cluster Manager without obligation for 30 days. This is a great way for those new to MySQL Cluster to rapidly configure and provision their first cluster.

All you need do is:

1. Go to Oracle eDelivery

2. Enter some basic details and click through the agreement

3. Select “MySQL Product Pack”, then your platform, then Go

Not only does MCM make the management of MySQL Cluster simpler, it also makes it safer. One of the largest causes of downtime is administrator error, and here MySQL Cluster Manager can significantly reduce risk.

Consider the task of upgrading rom one release of MySQL Cluster to another. This can be performed as an on-line operation, using rolling restarts to apply upgrades while still serving read and write requests. Its just one of the many operations users can perform on line (ie adding data nodes, upgrading schema, backups, etc) all of which enable MySQL Cluster to achieve 99.999% uptime.

Using a manual upgrade method on a cluster configured with 4 x data nodes, 2 x MySQL Server application nodes and 2 x management nodes, the administrator would be typing 46 x manual commands in an operation that would take around 2 ½ hours to complete. The steps are shown below:

1 x preliminary check of cluster state

8 x ssh commands per server

8 x per-process stop commands

4 x scp of configuration files (2 x mgmd & 2 x mysqld)

8 x per-process start commands

8 x checks for started and re-joined processes

8 x process completion verifications

1 x verify completion of the whole cluster.

Excludes manual editing of each configuration file.

Now compare this to using MySQL Cluster Manager:

upgrade cluster --package=7.1 mycluster;

Just 1 command and walk away and leave it.

Note – both of the processes above exclude the preparation steps of copying the new software package to each host and defining where it's located. The total operation times are based on a DBA restarting 4 x MySQL Cluster Data Nodes, each with 6GB of data, and performing 10,000 operations per second.

You can learn more about MySQL Cluster Manager from our new whitepaper and on-line demo.

We also have an on-demand webinar which covers MySQL Cluster Manager as well as other complimentary methods to managing a MySQL Cluster environment:

* NDBINFO: released with MySQL Cluster 7.1, NDBINFO presents real-time status and usage statistics, providing developers and DBAs with a simple means of pro-actively monitoring and optimizing database performance and availability.

* MySQL Cluster Advisors & Graphs: part of the MySQL Enterprise Monitor and available in the commercial MySQL Cluster Carrier Grade Edition, the Enterprise Advisor includes automated best practice rules that alert on key performance and availability metrics from MySQL Cluster data nodes.

While managing clusters will never be easy, it keeps getting a whole lot simpler !

About

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

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today