Wednesday Jun 27, 2012

Interview with Ronald Bradford about MySQL Connect

Ronald Bradford,  an Oracle ACE Director has been busy working with  database consulting, book writing (EffectiveMySQL) while traveling and speaking around the world in support of MySQL. I was able to take some of his time to get an interview on this thoughts about theMySQL Connect conference.

Keith Larson: What where your thoughts when you heard that Oracle was going to provide the community the MySQL Conference ?

Ronald Bradford: Oracle has already been providing various different local community events including OTN Tech Days and  MySQL community days. These are great for local regions both in the US and abroad.  In previous years there has been an increase of content at Oracle Open World, however that benefits the Oracle community far more then the MySQL community.  It is good to see that Oracle is realizing the benefit in providing a large scale dedicated event for the MySQL community that includes speakers from the MySQL development teams, invested companies in the ecosystem and other community evangelists.

I fully expect a successful event and look forward to hopefully seeing MySQL Connect at the upcoming Brazil and Japan OOW conferences and perhaps an event on the East Coast.

Keith Larson: Since you are part of the content committee, what did you think of the submissions that were received during call for papers?

Ronald Bradford: There was a large number of quality submissions to the number of available presentation sessions. As with the previous years as a committee member for the annual MySQL conference, there is always a large variety of common cornerstone MySQL features as well as new products and upcoming companies sharing their MySQL experiences.

All of the usual major players in the ecosystem will in presenting at MySQL Connect including Facebook, Twitter, Yahoo, Continuent, Percona, Tokutek, Sphinx and Amazon to name a few.  This is ensuring the event will have a large number of quality speakers and a difficult time in choosing what to attend.

Keith Larson: What sessions do you look forwarding to attending?

Ronald Bradford: As with most quality conferences you can only be in one place at one time, so with multiple tracks per session it is always difficult to decide. The continued work and success with MySQL Cluster, and with a number of sessions I am sure will be popular. The features that interest me the most are around the optimizer, where there are several sessions on new features, and on the importance of backups. There are three presentations in this area to choose from.

Keith Larson: Are you going to cover any of the content in your books at your MySQL Connect sessions?

Ronald Bradford: I will be giving two presentations at MySQL Connect. The first will include the techniques available for creating better indexes where I will be touching on some aspects of the first Effective MySQL book on Optimizing SQL Statements.  In my second presentation from experiences of managing 500+ AWS MySQL instances, I will be touching on areas including SQL tuning, backup and recovery and scale out with replication.   These are the key topics of the initial books in the Effective MySQL series that focus on performance, scalability and business continuity.  The books however cover a far greater amount of detail then can be presented in a 1 hour session.

Keith Larson: What features of MySQL 5.6 do you look forward to the most ?

Ronald Bradford: I am very impressed with the optimizer trace feature. The ability to see exposed information is invaluable not just for MySQL 5.6, but to also apply information discerned for optimizing SQL statements in earlier versions of MySQL.  Not everybody understands that it is easy to deploy a MySQL 5.6 slave into an existing topology running an older version if MySQL for evaluation of many new features.  You can use the new mysqlbinlog streaming feature for duplicating master binary logs on an older version with a MySQL 5.6 slave.  The improvements in instrumentation in the Performance Schema are exciting.   However, as with my upcoming Replication Techniques in Depth title, that will be available for sale at MySQL Connect, there are numerous replication features, some long overdue with provide significant management benefits. Crash Save Slaves, Global transaction Identifiers (GTID)  and checksums just to mention a few.

Keith Larson: You have been to numerous conferences, what would you recommend for people at the conference?

Ronald Bradford: Make the time to meet and introduce yourself to the speakers that cover the topics that most interest you. The MySQL ecosystem has a very strong community.  The relationships you build with presenters, developers and architects in MySQL can be invaluable, however they are created over time. Get to know these people, interact with them over time.  This is the opportunity to learn more then just the content from a 1 hour session.

Keith Larson: Any additional tips to handling the long hours ?

Ronald Bradford: Conferences can be hard, especially with all the post event drinking.  This is a two day event and I am sure will include additional events on Friday and Saturday night so come well prepared, and leave work behind. Take the time to learn something new.   You can always catchup on sleep later.

Keith Larson: Thank you so much for taking some time to do this I look forward to seeing you at the MySQL Connect conference.

 Please stay tuned here for more updates on MySQL. 


Monday Jun 25, 2012

Meet The MySQL Experts Podcast: MySQL Utilities

Managing a MySQL database server can become a full time job. In many occasions, one MySQL DBA needs to manage multiple, even tens of, MySQL servers, and tools that bundle a set of related tasks into a common utility can be a big time saver, allowing you spend more time improving performance and less time executing repeating tasks. While there are several such utility libraries to choose, it is often the case that you need to customize them to your needs. The MySQL Utilities library is the answer to that need. It is open source so you can modify and expand it as you see fit.

In the latest episode of the "Meet the MySQL Experts" podcast series, Chuck Bell, Sr. MySQL Software Developer at Oracle, introduces a variety of recently released MySQL Utilities, and how DBAs can save significant time using the utilities.

Listen to the podcast and learn the highlights in 10 minutes. If you want to gain further details, attend the on-demand webinar for a more complete introduction, including:

  • Use cases for each utility
  • How to group utilities for even more usability
  • How to modify utilities for your needs
  • How to develop and contribute new utilities

 Enjoy!

Honing Performance Tuning Skills on MySQL

Get hands-on experience with techniques for tuning a MySQL Server with the Authorized MySQL Performance Tuning course. 

This course is designed for database administrators, database developers and system administrators who are responsible for managing, optimizing, and tuning a MySQL Server.

You can follow this live instructor led training:

  • From your desk. Choose from among the 800+ events on the live-virtual training schedule.
  • In a classroom. A selection of events/locations listed below

 Location

 Date

 Delivery Language

 Prague, Czech Republic

 1 October 2012

 Czech

 Warsaw, Poland

 9 July 2012

 Polish

 London, UK

 19 November 2012

 English

 Rome, Italy

 23 October 2012

 Italian

 Lisbon, Portugal

 17 September 2012

 European Portugese

 Aix-en-Provence, France

 4 September 2012

 French

 Strasbourg, France

 16 October 2012

 French

 Nieuwegein, Netherlands

 3 September 2012

 Dutch

 Madrid, Spain

 6 August 2012

 Spanish

 Mechelen, Belgium

 1 October 2012

 English

 Riga, Latvia

 10 December 2012

 Latvian

 Petaling Jaya, Malaysia

 10 September 2012

 English

 Edmonton, Canada

 27 August 2012

 English

 Vancouver, Canada

 27 August 2012

 English

 Ottawa, Canada

 26 November 2012

 English

 Toronto, Canada

 26 November 2012

 English

 Montreal, Canada

 26 November 2012

 English

 Mexico City, Mexico

 9 July 2012

 Spanish

 Sao Paulo, Brazil

 2 July 2012

 Brazilian Portugese

To find a virtual or in-class event that suits you, go or http://oracle.com/education and choose a course and delivery type in your location.

 

MySQL Connect Content Catalog Live

The MySQL Connect Content Catalog is now live and you can check out the great program the content committee put together for you.

We received a lot of very good submissions during the call for papers and we’d like to thank you all again for those, it was a very difficult job to choose.

Overall MySQL Connect will in two days include:

  • Keynotes, with speakers such as Oracle Chief Corporate Architect Edward Screven and Vice President of MySQL Engineering Tomas Ulin
  • 66 conference sessions, enabling you to hear from:
    • Oracle engineers on MySQL 5.6 new features, InnoDB, performance and scalability, security, NoSQL, MySQL Cluster…and more
    • MySQL users and customers including Facebook, Twitter, PayPal, Yahoo, Ticketmaster, and CERN
    • Internationally recognized MySQL community members and partners on topics such as performance, security or high availability
  • 6 Birds-of-a-feather sessions, in which you’ll be able to engage into passionate discussions about replication, backup and other subjects, and help influence the MySQL roadmap
  • 8 Hands-On Labs designed to give you hands-on experience about MySQL replication, MySQL Cluster, the MySQL Performance Schema…and more
  • Demo pods about MySQL Workbench, MySQL Cluster, MySQL Enterprise Edition and other technologies and services

We’ll also have networking receptions on both Saturday and Sunday evening, enabling you to discuss with the Oracle engineers developing and supporting the MySQL products, as well as with other users and customers. Additionally, you’ll have the opportunity to meet and learn from our partners in the exhibition hall.

Some of the MySQL Connect speakers such as Henrik Ingo and Andrew Morgan have already blogged about their presence at MySQL Connect, and you can find more information about their sessions or their thoughts about the conference in their blogs. We also published an interview with Tomas Ulin a few weeks ago.

In summary, don’t miss MySQL Connect! And you only have about 3 weeks left to register with the early bird discount and save US$500. Don’t wait, Register Now!

Interested in sponsorship and exhibit opportunities? You will find more information here.

Tuesday Jun 19, 2012

MEB: Taking Incremental Backup using last successful backup

Introduction

In MySQL Enterprise Backup v3.7.0 (MEB 3.7.0) a new option '–incremental-base' was introduced. Using this option a user can take in incremental backup without specifying the '–start-lsn' option. Description of this option can be found here. Instead of '–start-lsn' the user can provide the location of the last full backup or incremental backup using the 'dir:' prefix. MEB would extract the end LSN of this backup from the mysql.backup_history table as well as the backup_variables.txt file (for verification) to use it as the start LSN of the incremental backup.

Because of popular demand, in MEB 3.7.1 the option '-incremental-base' has been extended further. The idea is to allow the user to take an incremental backup as easily as possible using the '–incremental-base' option. With the new option MEB queries the backup_history table for the last successful backup and uses its end LSN as the start LSN for the new incremental backup. It should be noted that the last successful backup is used irrespective of the location of the backup.

Details

A new prefix 'history:' has been introduced for the –incremental-base option and currently the only permissible value is the string "last_backup". So using the new option an incremental backup can be taken with the following command:

$ mysqlbackup --incremental --incremental-backup-dir=/media/mysqlbackup-repo/ --incremental-base=history:last_backup backup

When MEB attempts to extract the end LSN of the last successful backup from the mysql.backup_history table, it also scans the corresponding backup destination for the old backup and tries to read the meta files at this backup destination. If a valid backup still exists at the backup destination and the meta files can be read, MEB compares the end LSN found in the mysql.backup_history table with the end LSN found in the backup meta files of the old backup. Assuming that the host MySQL server is alive and mysql.backup_history can be accessed by MEB, the behaviour of MEB with respect to verification of the old end LSN can be summarized as follows:

If 'BD' is the backup destination of the last successful backup in mysql.backup_history table and 'BHT' is the mysql.backup_history table

if can_read_files_at_BD:
    if end_lsn_found_at_BD == end_lsn_of_last_backup_in_BHT:
        continue_with_backup()
    else
        return_with_error()
else
    continue_with_backup()

Advantages

Apart from ease of usability an important advantage of this option is that the user can do repeated incremental backups without changing the command line. This is possible using the '–with-timestamp' option along with this new option. For example, the following command

$ mysqlbackup --with-timestamp --incremental --incremental-backup-dir=/media/mysqlbackup-repo/ --incremental-base=history:last_backup backup

 can be used to perform successive incremental backups in the directory /media/mysqlbackup-repo .

Limitations

The option '--incremental-base=history:last_backup'

  • should not be used when the user takes different kinds of concurrent backups on the same MySQL server (say different partial backups at multiple locations).
  • should not be used after any temporary or experimental backups performed on the server (which where successful!).
  • needs to be used with precaution since any intermediate successful backup without the –no-connection will be used as the base backup for the next incremental backup. 
  • will give an error in case a valid backup exists at the location of the last successful backup and whose end LSN is different from that of the last successful backup found in the backup_history table.

Date: 2012-06-19

HTML generated by org-mode 6.33x in emacs 23

Monday Jun 18, 2012

Three Ways to Take Official MySQL for Database Administrators course

The MySQL for Database Administrators course is a 5 day course that teaches the key skills essential for MySQL Database Administrators.

You can take this course in one of the following three ways:

  • Training on Demand: Get Instructor-led training within 24 hours through streaming-video from your desk.
  • Live Virtual Class: Live instructor-led training from your desk. Over 1000! LVC events on the schedule for the MySQL for Database Administrator course.
  • In Class: See below for a selection of locations where you can take this training

For more information on this course or teaching schedule, go to the Oracle University portal and click on MySQL or search under your country/location.

A selection of the In-Class schedule for the MySQL for Database Administrator course:

 Location

 Date

 Delivery Language

 Mechelen, Belgium

 10 Sept 2012

 English

 Prague, Czech Republic

 27 Aug 2012

 Czech

 Nice, France

 24 Sept 2012

 French

 Paris, France

 24 Sept 2012

 French

 Strasbourg, France

 10 Sept 2012

 French

 Dresden, Germany

 20 Aug 2012

 German

 Gummersbach, Germany

 27 Aug 2012

 German

 Hamburg, Germany

 23 July 2012

 German

 Munich, Germany

 16 July 2012

 German

 Munster, Germany

 6 Aug 2012

 German

 Stuttgart, Germany

 9 July 2012

 German

 London, Great Britan

 9 July 2012

 English

 Belfast, Ireland

 27 Aug 2012

 English

 Rome, Italy

 30 July 2012

 Italian

 Windhof, Luxembourg

 26 Nov 2012

 English

 Nieuwegein, Netherlands

 1 Oct 2012

 English

 Oslo, Norway

 10 Sept 2012

 English

 Warsaw, Poland

 9 July 2012

 Polish

 Lisbon, Portugal

 3 Sept 2012

 European Portugese

 Madrid, Spain

 25 Jun 2012

 Spanish

 Baden Dattwil, Switzerland

 19 Nov 2012

 German

 Zurick, Switzerland

 8 Aug 2012

 German

 Istanbul, Turkey

 27 Aug 2012

 Turkish

 Petaling Jaya, Malaysia

 25 Jul 2012

 English

 Singapore

 16 July 2012

 English

 Brisbane, Australia

 30 July 2012

 English

 Bangkok, Thailand

 30 July 2012

 Thai

 Edmonton, Canada

 10 Sept 2012

 English

 Vancouver, Canada

 10 Sept 2012

 English

 Ottawa, Canada

 30 July 2012

 English

 Toronto, Canada

 30 July 2012

 English

 Montreal, Canada

 30 July 2012

 English

 Mexico City, Mexico

 25 Jun 2012

 Spanish

With these three delivery options and an impressive LVC and In-Class schedule you should find an event to suit your needs.

If you are interested in another date or location you can register your interest on the Oracle University portal.

Wednesday Jun 13, 2012

Hiring MySQL Curriculum Developer

If you want to be part of the team that creates the Official Oracle Training on MySQL and meet the following criteria:

  • Experience of Course Design and Development
  • Experience of database such as MySQL
  • Fluent in English - written and spoken
  • Keen to keep on learning

Then this is the opportunity for you!

Learn more about our open position for MySQL Curriculum Developer here.

Tuesday Jun 12, 2012

Interview with Tomas Ulin at the MySQL Innovation Day

MySQL Innovation Day held on June 5, 2012 was a great event for the MySQL engineers, users and customers to gather, share and network. I was able to get a few minutes with Tomas Ulin, Vice President of MySQL Engineering at Oracle, to ask him some questions. Here are the highlights of my interview with Tomas.


Monica: This was the first MySQL Innovation Day, correct?  Why now, what was the strategy behind hosting this kind of event?

Tomas: In the last year, we have rolled out an incredible number of MySQL events worldwide – some targeted at developers that are new to MySQL and others for the MySQL savvy. At the MySQL Innovation Day, our first event of this kind,, we had a number of our key engineers presenting lightning talks delivering previews of key new features as well as discussing roadmap. Our goal is to keep an open dialogue with the MySQL community.

In fact, we are hosting a two-day conference, another first, for the MySQL community called MySQL Connect on Sept. 29-30 in San Francisco. If you attended the MySQL Innovation Day and liked what we did, you are going to love MySQL Connect. We’ll have a lot more of our engineers and many users and community members presenting hour long sessions and hands on labs. Our engineers will be presenting new MySQL features as well offer previews of upcoming enhancements.

Monica: What's the big take-away from today's MySQL Innovation Day?

Tomas: I hope the most important takeaway for attendees was to see that Oracle has been driving, and continues to drive MySQL innovation with a steady stream of new great GA and Development Milestone releases.

Monica: What were attendees most interested in? What feedback did they have?

Tomas: Feedback from attendees was incredibly positive and encouraging. In particular, they liked the interaction with the MySQL engineers and were also excited about the new early access features in MySQL 5.6 and MySQL Cluster 7.3. In addition, sessions delivered by MySQL users like Facebook, Pinterest and Twitter were very well received. For example, Pinterest talked about using MySQL to scale from 0 to billions of page views/month, Twitter talked about “Scaling twitter with MySQL” and Facebook discussed the many options to implement MySQL master failover solutions. The presentations are already available for download while some of the session videos will be made available on the MySQL Innovation Day web page shortly.

Monica: How would you distinguish the use of MySQL vs. Oracle Database? What key factors should customers consider?

Tomas: MySQL and Oracle Database complement each other. They are very different products, best suited to different use cases. Customers can choose world-class solutions from Oracle to fulfill a variety of needs. MySQL is a great choice for enterprise web-based, custom and embedded apps. Oracle Database is the leading choice for enterprise packaged applications such as ERP, CRM as well as high-end data warehousing and business intelligence applications.

Monica: What are the highlights of the current MySQL 5.6 Development Milestone Release and early access features for MySQL Cluster 7.3?

Tomas: MySQL 5.6 development milestone release builds on MySQL 5.5 by improving:

  • Optimizer for better Performance, Scalability
  • Performance Schema for better instrumentation
  • InnoDB for better transactional throughput
  • Replication for higher availability, data integrity
  • NoSQL options for more flexibility

We announced some new early access features in MySQL 5.6, including binary log group commit. We also announced early access features in MySQL Cluster 7.3 including support for foreign key constraints.

Monica: How do people get these releases?

Tomas: You can access development milestone releases by going to: http://dev.mysql.com/downloads/mysql
Then select the “Development Release” tab.

The MySQL Cluster 7.3 and other early access features can be downloaded at: http://labs.mysql.com

Monica: What's coming up next for MySQL?

Tomas: Our development team is working in overdrive, cranking out new features with community feedback. Don’t miss the MySQL Connect conference being held in San Francisco on Sept. 29 and 30th. My team and I will be there. I hope you can join us!

Monica: Thank you for your time, Tomas. I look forward to seeing you at the MySQL Connect conference. To our followers, I hope you found this interview informative. I welcome your comments. Please stay tuned here for more updates on MySQL.

Note: Monica Kumar is Senior Director of product marketing for Linux, Virtualization and MySQL at Oracle.

Monday Jun 11, 2012

Official MySQL Cluster Training Available Near You!

Oracle is the official provider of MySQL Training.

To learn more about MySQL Cluster, you can register for the MySQL Cluster training at a large selection of locations and often you will find the course delivery in your local language! For example:

 Where

 When

 Delivery Language

 Prague, Czech Republic

 17 September 2012

 Czech

 Warsaw, Poland

 1 August 2012

 Polish

 Wien, Austria

 27 August 2012

 German

 London, United Kingdom

 18 July 2012

 English

 Lisbon, Portugal

 3 December 2012

 European Portugese

 Nice, France

 8 October 2012

 French

 Barcelona, Spain

 25 September 2012

 Spanish

 Madrid, Spain

 20 August 2012

 Spanish

 Denver, United States

 17 October 2012

 English

 Chicago, United States

 22 August 2012

 English

 New York, United States

 20 June 2012

 English

 Petaling Jaya, Malaysia

 18 July 2012

 English

 Singapore

 21 August 2012

 English

 Melbourne, Australia

 13 June 2012

 English

 Mexico City, Mexico

 23 July 2012

 Spanish

To learn more or register your interest in another course, location, or date, go to Oracle University's official portal.

Tuesday Jun 05, 2012

MySQL Cluster 7.3 Labs Release – Foreign Keys Are In!

Summary (aka TL/DR):

Support for Foreign Key constraints has been one of the most requested feature enhancements for MySQL Cluster. We are therefore extremely excited to announce that Foreign Keys are part of the first Labs Release of MySQL Cluster 7.3 – available for download, evaluation and feedback now! (Select the mysql-cluster-7.3-labs-June-2012 build)

In this blog, I will attempt to discuss the design rationale, implementation, configuration and steps to get started in evaluating the first MySQL Cluster 7.3 Labs Release.

Pace of Innovation

It was only a couple of months ago that we announced the General Availability (GA) of MySQL Cluster 7.2, delivering 1 billion Queries per Minute, with 70x higher cross-shard JOIN performance, Memcached NoSQL key-value API and cross-data center replication.  This release has been a huge hit, with downloads and deployments quickly reaching record levels.

The announcement of the first MySQL Cluster 7.3 Early Access lab release at today's MySQL Innovation Day event demonstrates the continued pace in Cluster development, and provides an opportunity for the community to evaluate and feedback on new features they want to see.

What’s the Plan for MySQL Cluster 7.3?

Well, Foreign Keys, as you may have gathered by now (!), and this is the focus of this first Labs Release.

As with MySQL Cluster 7.2, we plan to publish a series of preview releases for 7.3 that will incrementally add new candidate features for a final GA release (subject to usual safe harbor statement below*), including:

- New NoSQL APIs;

- Features to automate the configuration and provisioning of multi-node clusters, on premise or in the cloud;

- Performance and scalability enhancements;

- Taking advantage of features in the latest MySQL 5.x Server GA.

Design Rationale

MySQL Cluster is designed as a “Not-Only-SQL” database. It combines attributes that enable users to blend the best of both relational and NoSQL technologies into solutions that deliver web scalability with 99.999% availability and real-time performance, including:

  • Concurrent NoSQL and SQL access to the database;
  • Auto-sharding with simple scale-out across commodity hardware;
  • Multi-master replication with failover and recovery both within and across data centers;
  • Shared-nothing architecture with no single point of failure;
  • Online scaling and schema changes;
  • ACID compliance and support for complex queries, across shards.

Native support for Foreign Key constraints enables users to extend the benefits of MySQL Cluster into a broader range of use-cases, including:

- Packaged applications in areas such as eCommerce and Web Content Management that prescribe databases with Foreign Key support.

- In-house developments benefiting from Foreign Key constraints to simplify data models and eliminate the additional application logic needed to maintain data consistency and integrity between tables.

Implementation

The Foreign Key functionality is implemented directly within MySQL Cluster’s data nodes, allowing any client API accessing the cluster to benefit from them – whether using SQL or one of the NoSQL interfaces (Memcached, C++, Java, JPA or HTTP/REST.)

The core referential actions defined in the SQL:2003 standard are implemented:

  • CASCADE
  • RESTRICT
  • NO ACTION
  • SET NULL

In addition, the MySQL Cluster implementation supports the online adding and dropping of Foreign Keys, ensuring the Cluster continues to serve both read and write requests during the operation.

An important difference to note with the Foreign Key implementation in InnoDB is that MySQL Cluster does not support the updating of Primary Keys from within the Data Nodes themselves - instead the UPDATE is emulated with a DELETE followed by an INSERT operation. Therefore an UPDATE operation will return an error if the parent reference is using a Primary Key, unless using CASCADE action, in which case the delete operation will result in the corresponding rows in the child table being deleted. The Engineering team plans to change this behavior in a subsequent preview release.

Also note that when using InnoDB "NO ACTION" is identical to "RESTRICT". In the case of MySQL Cluster “NO ACTION” means “deferred check”, i.e. the constraint is checked before commit, allowing user-defined triggers to automatically make changes in order to satisfy the Foreign Key constraints.

Configuration

There is nothing special you have to do here – Foreign Key constraint checking is enabled by default.

If you intend to migrate existing tables from another database or storage engine, for example from InnoDB, there are a couple of best practices to observe:

1. Analyze the structure of the Foreign Key graph and run the ALTER TABLE ENGINE=NDB in the correct sequence to ensure constraints are enforced

2. Alternatively drop the Foreign Key constraints prior to the import process and then recreate when complete.

Getting Started

Read this blog for a demonstration of using Foreign Keys with MySQL Cluster. 

You can download MySQL Cluster 7.3 Labs Release with Foreign Keys today - (select the mysql-cluster-7.3-labs-June-2012 build)

If you are new to MySQL Cluster, the Getting Started guide will walk you through installing an evaluation cluster on a singe host (these guides reflect MySQL Cluster 7.2, but apply equally well to 7.3)

Post any questions to the MySQL Cluster forum where our Engineering team will attempt to assist you.

Post any bugs you find to the MySQL bug tracking system (select MySQL Cluster from the Category drop-down menu)

And if you have any feedback, please post them to the Comments section of this blog.

Summary

MySQL Cluster 7.2 is the GA, production-ready release of MySQL Cluster. This first Labs Release of MySQL Cluster 7.3 gives you the opportunity to preview and evaluate future developments in the MySQL Cluster database, and we are very excited to be able to share that with you.

Let us know how you get along with MySQL Cluster 7.3, and other features that you want to see in future releases.

* Safe Harbor Statement

This information is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracles products remains at the sole discretion of Oracle.

HTTP, JSON, JavaScript, Map and Reduce built-in to MySQL

Oracle MySQL Engineer Ulf Wendel delivered yesterday a talk about his Proof of Concept at the International PHP Conference in Berlin entitled: “HTTP, JSON, JavaScript, Map and Reduce built-in to MySQL - make it happen, today.”

The presentation has been featured today on the home page of SlideShare, both into the “Hot on Facebook” and “Hot on Twitter” sections. Well done, Ulf! Especially just before the wedding…:)

Presentation abstract: See how a MySQL Server plugin can be developed to build all this into MySQL. A new direct wire between MySQL and client-side JavaScript is created. MySQL speaks HTTP, replies JSON and offers server-side JavaScript. Server-side JavaScript gets access to MySQL data and does Map&Reduce of JSON documents stored in MySQL. Fast? 2-4x faster than proxing client-side JavaScript request through PHP/Apache. Reasonable results...

Slides available here.

And, talking about innovation… today is Oracle’s MySQL Innovation Day, you can still attend the event online. Register Now, it starts at 9.00 am PT.

Friday Jun 01, 2012

Configuring MySQL Cluster Data Nodes

In my previous blog post, I discussed the enhanced performance and scalability delivered by extensions to the multi-threaded data nodes in MySQL Cluster 7.2. In this post, I’ll share best practices on the configuration of data nodes to achieve optimum performance on the latest generations of multi-core, multi-thread CPU designs.

Configuring the Data Nodes

The configuration of data node threads can be managed in two ways via the config.ini file:

- Simply set MaxNoOfExecutionThreads to the appropriate number of threads to be run in the data node, based on the number of threads presented by the processors used in the host or VM.

- Use the new ThreadConfig variable that enables users to configure both the number of each thread type to use and also which CPUs to bind them too.

The flexible configuration afforded by the multi-threaded data node enhancements means that it is possible to optimise data nodes to use anything from a single CPU/thread up to a 48 CPU/thread server. Co-locating the MySQL Server with a single data node can fully utilize servers with 64 – 80 CPU/threads. It is also possible to co-locate multiple data nodes per server, but this is now only required for very large servers with 4+ CPU sockets dense multi-core processors.

24 Threads and Beyond!

An example of how to make best use of a 24 CPU/thread server box is to configure the following:

- 8 ldm threads

- 4 tc threads

- 3 recv threads

- 3 send threads

- 1 rep thread for asynchronous replication.

Each of those threads should be bound to a CPU. It is possible to bind the main thread (schema management domain) and the IO threads to the same CPU in most installations.

In the configuration above, we have bound threads to 20 different CPUs. We should also protect these 20 CPUs from interrupts by using the IRQBALANCE_BANNED_CPUS configuration variable in /etc/sysconfig/irqbalance and setting it to 0x0FFFFF.

The reason for doing this is that MySQL Cluster generates a lot of interrupt and OS kernel processing, and so it is recommended to separate activity across CPUs to ensure conflicts with the MySQL Cluster threads are eliminated.

When booting a Linux kernel it is also possible to provide an option isolcpus=0-19 in grub.conf. The result is that the Linux scheduler won't use these CPUs for any task. Only by using CPU affinity syscalls can a process be made to run on those CPUs.

By using this approach, together with binding MySQL Cluster threads to specific CPUs and banning CPUs IRQ processing on these tasks, a very stable performance environment is created for a MySQL Cluster data node.

On a 32 CPU/Thread server:

- Increase the number of ldm threads to 12

- Increase tc threads to 6

- Provide 2 more CPUs for the OS and interrupts.

- The number of send and receive threads should, in most cases, still be sufficient.

On a 40 CPU/Thread server, increase ldm threads to 16, tc threads to 8 and increment send and receive threads to 4.

On a 48 CPU/Thread server it is possible to optimize further by using:

- 12 tc threads

- 2 more CPUs for the OS and interrupts

- Avoid using IO threads and main thread on same CPU

- Add 1 more receive thread.

Summary

As both this and the previous post seek to demonstrate, the multi-threaded data node extensions not only serve to increase performance of MySQL Cluster, they also enable users to achieve significantly improved levels of utilization from current and future generations of massively multi-core, multi-thread processor designs.

A big thanks to Mikael Ronstrom, Senior MySQL Architect at Oracle, for his work in developing these enhancements and best practices.

You can download MySQL Cluster 7.2 today and try out all of these enhancements. The Getting Started guides are an invaluable aid to quickly building a Proof of Concept

Don’t forget to check out the MySQL Cluster 7.2 New Features whitepaper to discover everything that is new in the latest GA release

About

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

Twitter


Facebook

Search

Archives
« June 2012 »
SunMonTueWedThuFriSat
     
2
3
4
6
7
8
9
10
14
15
16
17
20
21
22
23
24
26
28
29
30
       
Today