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. 




Monday Jul 30, 2012

New Multi-threaded, Parallel backups in MySQL Enterprise Backup 3.8

MySQL Enterprise Backup ("MEB") has always provided a simple, reliable online backup solution for InnoDB backed applications that includes DBA-expected functionality around:

  • policies that require full, incremental, partial and logged based backup
  • point in time recovery
  • back up compression
  • back up and recovery for MyISAM, Merge, Partition, Archive

In addition, MEB also provides a scriptable "set it and forget it" command line interface that allows DBAs to schedule and control backup operations at the server and database levels.  Integration with the MySQL Enterprise Monitor ensures that any MEB related issues are quickly identified and DBAs alerted so there is no lapse in the ability to recover when needed.

In the spirit of evolving MEB to meet even greater demands for speedy online backup operations, the MEB Engineering team has just released version 3.8 for production use.  MEB 3.8 provides a substantial improvement in backup operation performance by implementing parallel processing of related internal operations. In this release, during both backup and restore operations, certain operations are now performed in parallel: reading/writing data, compression/decompression, and page checksum validation during backup. The parallel execution takes advantage of multi-threading and the resulting performance increase is particularly evident when backing up to or restoring from RAID and SSD storage, and when using compressed backups.  Internal benchmarks show that performance gains range from 20% to 1000% depending on the size and content of the data involved, so users should fully expect to see incremental performance improvements in specific use cases. 

As with previous versions, MEB 3.8 is fully compatible with backups taken using previous versions.  While the upgrade path to MEB 3.8 is straightforward, I recommend reviewing all of the related performance improving options and general bug fixes in version 3.8.  You can find all you need to know to get up and running here.

Existing MySQL Enterprise Edition customers can access 3.8 from the MyOracle Support Portal.  Evaluators can download MEB 3.8 from Oracle's Software Delivery Cloud within the next few days.

Congratulations to the MEB Engineering team on another great release.  More to come!

Thanks for your support of MySQL!




Friday Jul 27, 2012

Interview with Sarah Novotny about MySQL Connect

Dave Stokes and I tracked down Sarah Novotny Sunday night at the early bird registration for OSCON 2012. We had a few moments to interview her about MySQL Connect and her recent endeavors. I hope you enjoy:

Keith Larson: Thank you for joining us today.

Sarah Novotny: I am glad to be here.

Keith Larson: First just a general question, What were your thoughts when you heard that Oracle was going to do MySQL Connect ?

Sarah Novotny: I was very excited about it. I didn't expect Oracle to give us our own space. Even thought JavaOne had gotten that . JavaOne has existed as it's own conference before, and with the O'Reilly Conference no longer happening,  I didn't expect it and I was surprised. I know the first year after Oracle purchased MySQL, we had MySQL Sunday, which was great. We were told that this was such a big deal and it is really only a transition thing and with 140+ products nobody gets a big name like that. So when MySQL Connect came out I was very excited.

Keith Larson: Since we pulled you in as part of the MySQL Committee, what did you think of all the sessions that you saw or reviewed.

Sarah Novotny: The session looked very good. I always hope for more sessions and more opportunities to look through good sessions. That is one of the reasons I sit on conference committees, so I can learn about all the cool stuff people are working on. The sessions where pretty broadly varied and I was very excited to be able to also build some little story arcs within topics. So you could have a couple of replication sessions or a couple of backup sessions that all feed onto one another. So from story point, from the creation of the conference, there was some very interesting threads to follow through in order to get a really robust experience for people, both beginner, intermediate and advanced levels.

Keith Larson: So based on the story arcs you spoke of and saw, what type of session do you look forward to attending while your there?

Sarah Novotny: I am a total replication geek, because that is the way that you can maintain sanity within MySQL, making sure you have good replication. I love seeing all the way that people can replicate, really interesting architectures in order to meet the needs that they have for their application. So with the new replication features coming out in 5.6 it is very exciting. So the focus for me is MySQL 5.6 and the replication features in it.

Keith Larson: So I was going to ask what features do you look forward to the most in MySQL 5.6, I assume replication is it.

Sarah Novotny: Replication is a big one. There have been a lot of enhancements that have been a long time coming. We've seen them in the code tree and out there for integration to mainline someday and now we see them coming to fruition. So it is very exciting for me.

Keith Larson: You obviously travel a tremendous about of time and attend a lot of conferences. What would you recommend for people who attend this conference.

Sarah Novotny: I would recommend a couple of things. One, find that story that you want to learn at a conference. Maybe it is replication so you find three or four talks about that. Understanding what your goal is out of a conference is very important. Then making sure you have time to meet people,interact with people, ask questions, teach someone something, be able to have those interactions face to face with the community members that you know from blog posts or your support roles with Oracle. So, make sure that you make time to interact with people directly and always take advantage of the speakers. Most of the speakers who are up in front of an audience want to be engaging of the audience, you want to be understanding of what they need from your talk, and then how to engage with them to make a better talk next time.

Keith Larson: As a speaker at many events, you encourage the people to ask questions and ask questions afterwards.

Sarah Novotny: Absolutely, I actually have a couple of talks on giving presentations to technologists, to and by technologists. In those I often talk a lot about looking at the people that are engaged and even the disengaged ones. But not to spend to much time on the disengaged ones, they might have a sick kid and that is why they are texting and it has nothing to do with you. As an audience, being engaged with the speaker is part of your responsibility as well. Making sure you get what you need out of it, which could be asking questions afterwards or sending a note saying you liked it or that you didn't it, or I was interested in more things like this.

Keith Larson: So any additional tips you would give to people for handling the long hours at conferences, we typically have BOFs and after hour events as well so...

Sarah Novotny: I will give you my trick for surviving OSCON, which for me is like a ten day extravaganza . I start resting up the weekend up before. I make sure I get ten to twelve hours of sleep the two or three nights before OSCON. Then midweek I make sure I have night that I carve out time for myself. I put nothing on it. The other thing is, and this might sound absurd, but stay hydrated eat your vegetables. A salad will do wonders for your stamina in the middle of the week. It is very much just about taking care of your robot in that sense. Your brain might be willing and excited but if your body can't do it then your not going to enjoy it or even retain it. For the introverts among us, make sure you carve out quite time for you. It is important, otherwise I get way overloaded.

Keith Larson: So you have been very busy as of lately, we caught you after the big sale.

Sarah Novotny: I have, I have a couple of new exciting things yes. Blue Gecko was sold to datAvail which is very exciting. For me it is a very lovely , I founded it, I helped run it, I sold. It looks like a very nice little bow on the resume . We are making sure it is a smooth transition for the Blue Gecko internal staff as well as the external customers. We worked a long time to be able to sell this and yet still be able to maintain our culture.

Keith Larson: So are you going to go buy an island now?

Sarah Novotny: OH we will see... Terms have been undisclosed. (laughter) On a serious note, my day job which has been keeping me busy for the rest of my time, is of course Video Game Publishing. Which is going to be a lot of fun and really exciting. It is a big left turn for me but looks to be fun.

David Stokes: So if your Joe or Josephine DBA and trying to make the case to your boss to attend MySQL Connect. What is the killer argument as a boss that you would take from an employee to attend this show.

Sarah Novotny: I think what I would actually say is that this is a very important element of professional development for a few different reasons. One I get exposed to new content. Two I find additional resources that could include new people I meet that help me learn more about new content or websites or blogs I should read. Three, it gives me an opportunity to be an evangelist for my company. Four, I can recruit, trying to find a new DBA is harder than finding the Easter bunny. Everyone I know has changed jobs in the last year for reasonably large increases in salary so they are not going to jump again. It is like pulling hen's teeth.

David Stokes: Yes I have recruiters that offer money if I know anyone and I just don't.

Sarah Novotny: Yes I get that like four times a week and I just don't know anyone who is looking.

David Stokes: Anything else you want to add...

Sarah Novotny: Rock on ! I do want to say thank you to you guys for doing the community work that you do and pulling us all together.

Keith Larson & David Stokes: Oh no, thank you.



Thursday Jul 26, 2012

MySQL and Hadoop

Introduction

"Improving MySQL performance using Hadoop" was the talk which I and Manish Kumar gave at Java One & Oracle Develop 2012, India. Based on the response and interest of the audience, we decided to summarize the talk in a blog post. The slides of this talk can be found here. They also include a screen-cast of a live Hadoop system pulling data from MySQL and working on the popular 'word count' problem.



MySQL and Hadoop have been popularly considered as 'Friends with benefits' and our talk was aimed at showing how!

The benefits of MySQL to developers are the speed, reliability, data integrity and scalability. It can successfully process large amounts of data (upto terabytes). But for applications that require massive parallel processing we may need the benefits of a parallel processing system such as Hadoop.

Hadoop is a highly scalable distributed framework and extremely powerful in terms of computation. Hadoop is fault tolerant and parallelizes data processing across many nodes. Popular users of Hadoop are Yahoo, Facebook, Twitter and Netflix.

Combine the speed and reliability of MySQL with the high scalability of Hadoop and what you get is a powerful system capable of using relational data and crunching it at tremendous speeds!

Hadoop

Hadoop leverages its distributed file system - the Hadoop Filesystem (HDFS) - to cheaply and reliably replicate chunks of data. HDFS is a distributed, scalable and portable file system written in Java. Each node in a Hadoop instance typically has a single name-node and a cluster of data-nodes form the HDFS cluster. HDFS uses TCP/IP for communication and stores large files across multiple machines. One drawback of HDFS is that a single name-node stores all the meta-data in memory.

Submitting a task to a Hadoop system is done by writing a map-reduce job. A map-reduce job splits input data into independent chunks where each chunk is processed by the map task in a parallel manner. During the 'Reduce' phase, data from data nodes is merge sorted so that the key-value pairs for a given key are contiguous. The merged data is read sequentially and the valus are passed to the reduce method with an iterator reading the input file until the next key in encountered.

Sqoop

One of the popular ways to use MySQL with Hadoop is to 'Sqoop' it! Sqoop was originally developed by Cloudera and serves as an import tool for Hadoop. Recently it graduated from Apache Incubator to a Top Level Project. It allows users to easily import the data from MySQL database into Hadoop's HDFS so it can be used in Hive. Sqoop uses mysqldump to fetch the data stored in MySQL. One useful feature of Sqoop is that it can also be installed on Amazon Elastic Map Reduce (EMR).

You can find more about Sqoop here.

Demo

In the demo session, we showed a live hadoop system with 1 name node and 2 data nodes. The idea  was to show how easily a live Hadoop cluster can be brought up. We demonstrated the various phases of setting up the Hadoop system: installation, formatting the HDFS, editing the configuration files, starting the Hadoop cluster and finally running the map reduce job. We also addressed questions related to trouble shooting Hadoop deployments and common mistakes done while setting up a Hadoop cluster. After this, we wrote a basic mapreduce job for the word count problem in Java and ran the job on our Hadoop cluster. Although a 2-node cluster did not give much improvement in the time required to complete the task, the increased speed at which the map-reduce job returned the results was clearly noticeable in comparison to the same problem solved using simple SQL queries.

The following image depicts the word count problem solved using a map-reduce job:



Response

Overall the response to our session was overwhelming. Several interesting questions came up during the session and many attendees were interested to know about the other possible ways of integrating MySQL with Hadoop. Some of them came up with unique ideas and use cases in which the benefits of MySQL proved to be the perfect missing pieces in Hadoop deployments using typical non-relational databases.

Apart from MySQL, Hadoop, HDFS and Sqoop some of the related technologies that were also discussed in the session were Hive, Scribe and Thrift. Following are some helpful resources:
  • Leveraging Hadoop to Augment MySQL Deployments - Sarah Sproehnle, Cloudera
  • http://engineering.twitter.com/2010/04/hadoop-at-twitter.html
  • http://semanticvoid.com/blog/category/mysql
  • http://michael-noll.com
  • http://www.facebook.com/MySQLatFacebook
  • http://shout.setfive.com/tag/hadoop/

Tuesday Jul 24, 2012

Early Bird Registration Rate for MySQL Connect Ends on July 27

If you haven't already registered for MySQL Connect, now is the time to do it.

Looking for the right reason to attend? Here are the TOP THREE REASONS...

  • You'll get the chance to hear directly from users including PayPal, Verizon, Twitter, Facebook, Ticketmaster, Ning, Mozilla, CERN, Yahoo! and more, about best practices for deploying MySQL based solutions.
  • Don't miss this unique opportunity to meet the engineers developing and supporting the MySQL products, all in a single location. You'll be able to ask them all your questions and provide them feedback.
  • Acquire detailed knowledge about the latest innovations in InnoDB, the MySQL Optimizer, high availability strategies, performance and scalability, security improvements and numerous other topics. You'll hear it straight "from the horse's mouth" and from other MySQL experts in the ecosystem.

Early bird registration rate offering a saving of $500 ends this friday, July 27. Don't miss out...Register Now.

 

  • Monday Jul 23, 2012

    Get started building PHP applications with MySQL & PHP Training

    Learn how to develop applications in PHP and how to use MySQL efficiently for those applications! Through a hands-on approach, this instructor-led course will help you improve your PHP skills and combine them with time-proven database management techniques to create best-of-breed web applications that are efficient, solid and secure.

    You can take this 4 day class through:

    • Live-Virtual Delivery: Take this course from your own desk. Over 200 events already on the schedule for a wide range of timezones.
    • In-Class Delivery: Events on the schedule include:

     Location

     Date

     Delivery Language

     Warsaw, Poland

     15 October 2012

     Polish

     Rome, Italy

     8 October 2012

     Italian

     Lisbon, Portugal

     5 November 2012

     European Portugese

     Porto, Portugal

     3 December 2012

     European Portugese

     Barcelona, Spain

     22 October 2012

     Spanish

     Madrid, Spain

     12 November 2012

     Spanish

    For further information on this or other courses on the Authentic MySQL curriculum go to the Oracle University portal.

    Tuesday Jul 17, 2012

    MySQL Cluster Powers El Chavo from Playful Play, Latin America’s Most Popular Facebook Game

    Introduction

    Attracting over 3m subscribers in just 6 months and growing by 30,000 new users per day, Playful Play needed a database that was able to keep pace with the massive scalability and high availability demands of the wildly successful La Vecindad de El Chavo Facebook game.

    Playful Play selected MySQL Cluster CGE running on a public cloud to power their gaming platform, providing:

    • 45% improvement in performance;
    • 99.999% uptime;
    • 80% reduction in DBA overhead;
    • Local language support, 24x7.

    As a result, Playful Play has been able to maintain user growth rates and attract new advertisers to the platform, while enhancing agility and reducing cost.

    Company Overview

    Based out of Monterrey, Mexico, Playful Play has created Latin America’s #1 Facebook game based on "El Chavo del 8", the cultural and TV phenomenon that has been running across Latin America for the past four decades. The show is also extremely popular in Spain and the United States.

    El Chavo (The Kid) appeals to a broad demographic, having attracted over 3M subscribers in its first 6 months, and adding 30,000 new users per day. The game has also proved popular with advertisers who are keen to integrate social gaming into their marketing strategies in order to raise awareness and build brand loyalty.

    Through partnerships with Televisa Home Entertainment and Grupo Chespirito, Playful Play has aggressive plans to grow their business through the development of new social games targeting Latin America, Brazil, the United States and Spain.


    The Challenges of Rapid Growth

    As a start-up business, fast time to market at the lowest possible case was the leading development priority after Playful Play secured the rights to develop the El Chavo game.

    As a result, Playful Play developed the first release of the game on the proven LAMP (Linux, Apache, MySQL, PHP / Perl / Python) stack.

    To meet both the scalability and availability requirements of the game, Playful Play initially deployed MySQL in a replicated, multi-master configuration.

    The database is core to the game, responsible for managing:

    • User profiles and avatars
    • Gaming session data;
    • In-app (application) purchases;
    • Advertising and digital marketing event data.

    As La Vecidad de El Chavo spread virally across Facebook, subscriptions rapidly exceeded one million users, leading Playful Play to consider how to best architect their gaming platforms for long-term growth. They had heard about the release of MySQL Cluster 7.2, and so decided to initiate an evaluation to determine if it could meet their requirements.


    The Route to Internet-Scale with MySQL Cluster

    In addition to growing user volumes, the El Chavo game also added new features that changed the profile of the database. Operations became more write-intensive, with INSERTs and UPDATEs accounting for up to 70% of the database load.

    The game’s popularity also attracted advertisers, who demanded strict SLAs for both performance (predictable throughput with low latency) as well as uptime.

    Through their evaluation, the developers at Playful Play identified that MySQL Cluster was able to meet their needs.

    Write Performance with Real-Time Responsiveness

    MySQL Cluster’s multi-master architecture, coupled with the ability to auto-shard (partition) tables across nodes, enabled Playful Play to meet the performance and scalability demands of the El Chavo game, without changes to their applications.

    The ability to store data in memory and persist to disk delivered the sub-millisecond responsiveness and durability demanded by the game’s users.

    In addition, Playful Play was able to horizontally scale the database across low-cost commodity nodes running in the cloud, reducing their TCO and enhancing agility.

    Continuous Availability

    The shared-nothing, distributed design of MySQL Cluster coupled with integrated replication and self-healing recovery ensured high availability of the gaming platform, without DevOps intervention.

    MySQL Cluster was able to protect against downtime resulting from both failures and planned upgrades. For example, Playful Play has been able to scale the cluster on-line to support growing user volumes, without service interruption.

    Data Integrity Supporting Monetization

    As the game evolved to support in-app purchases and digital marketing, the ACID-compliance offered by MySQL Cluster afforded the transactional integrity demanded by users and advertisers.


    The Value of MySQL Cluster Consulting, Support and Management Tools

    Following their successful evaluation, Playful Play deployed MySQL Cluster with their public cloud provider.

    Playful Play wanted to ensure they were getting the most out of the deployment, so Oracle’s MySQL consulting team reviewed their Architecture and Design. The consultants were able to further optimize the database to deliver even higher levels of performance, and demonstrated how Playful Play could automatically scale the database using MySQL Cluster Manager.

    The MySQL Cluster deployment at Playful Play is growing rapidly, and currently comprised of over 25 nodes running Linux on commodity x86 servers, each configured with 24-cores and 64GB of RAM:

    • 12 x MySQL Cluster data nodes;
    • 12 x MySQL Server SQL nodes;
    • 2 x MySQL Cluster management nodes.

    Each data and SQL node is co-located to a single physical instance.

     Playful Play Deployment Architecture, built for rapid scale

    Using the architecture above, MySQL Cluster is currently supporting:

    • 3 million subscribers, with 30,000 new additions per day across Latin America, Europe and U.S;
    • 10,000 concurrent users;
    • 10,000 Transactions Per Second;
    • 99.999% uptime.

    "The MySQL support service has been essential in helping us for troubleshooting and giving recommendations for the production cluster, Thanks" Carlos Morales – DBA, Playfulplay.com México

    Playful Play has deployed MySQL Cluster Carrier-Grade Edition, providing access to technical and consultative support, in addition to the MySQL Enterprise Monitor and MySQL Cluster Manager tools.


    MySQL Enterprise Monitor Enables Continuous Service Availability

    MySQL Enterprise Monitor enables Playful Play to proactively monitor the entire cluster from a single GUI. DBAs are automatically notified if any environment variables begin to exceed pre-defined thresholds, and presented with the instructions necessary to take corrective action, before users are impacted.

    MySQL Cluster Manager is used to automate cluster configuration changes, eliminating the risk of manual errors and significantly enhancing DevOps productivity.

    The subscription has delivered terrific value to Playful Play, enabling them to:

    • Improve performance by 45% while reducing CPU utilization by 35%, providing significant headroom for continued growth;
    • Reduced DBA overhead by 80%, providing substantial cost savings;
    • Access to local language support, 24x7.


    The Future with MySQL Cluster

    Playful Play has aggressive growth plans, seeking to attract over 50M subscribers within 5 years. El Chavo del 8 is continuing to gain widespread popularity in Latin regions around the world, and will be joined by other social networking games currently in development. The next major target is Brazil, presenting a very attractive, emerging market in social gaming.

    MySQL Cluster CGE has been selected as the database platform powering this growth.

    Playful Play will be presenting their experiences and best practices with MySQL Cluster at the MySQL Connect conference, September 29th and 30th 2012 in San Francisco.


    Further Resources

    MySQL Cluster at Playful Play On-Demand Webinar (Spanish)

    MySQL Cluster Demonstration

    Guide to Scaling Web Databases with MySQL Cluster



    Monday Jul 16, 2012

    Learn to Develop Modern Applications with MySQL for Developers Course

    Learn to design and implement modern applications, such as web or cloud applications, by following the MySQL for Developers Training Course which will teach you about essential SQL statements for data design, querying, and programming.

    This 5 day instructor-led course is available via the following training methods:

    • Live-Virtual Class: Follow this live class from your own office or home machine through one of the over 700 events currently on the schedule. To find the event that suits you go to http://oracle.com/education and click on MySQL.
    • In-Class Teach: Travel to an Oracle classroom to follow this class. A sample of the schedule events is shown below:

     Location

     Date

     Delivery Language

     Prague, Czech Republic

     8 October 2012

     Czech

     Warsaw, Poland

     27 August 2012

     Polish

     Vienna, Austria

     3 September 2012

     German

     London, United Kingdom

     17 September 2012

     English

     Zurich, Switzerland

     15 August 2012

     German

     Milan, Italy

     10 September 2012

     Italian

     Rome, Italy

     15 October 2012

     Italian

     Gummersbach, Germany

     3 September 2012

     German

     Hamburg, Germany

     12 November 2012

     German

     Lisbon, Portugal

     24 September 2012

     European Portugese

     Strasbourg, France

     8 October 2012

     France

     Barcelona, Spain

     27 August 2012

     Spain

    Madrid, Spain 

     10 December 2012

     Spain

     Riga, Latvia

     29 October 2012

     Latvian

     Petaling Jaya, Malaysia

     22 August 2012

     English

     Bangkok, Thailand

     29 October 2012

     English

    For more information on this course, to register interest in an additional event or to see the other courses available in the MySQL Curriculum, go to http://oracle.com/education

    Thursday Jul 12, 2012

    MySQL Connect Attendee ToolKit Available

    Are you speaking, attending or exhibiting at MySQL Connect? Banners, buttons and other resources are at your disposal in our attendee toolkit! Some examples below:


    Would you like to help promote the event? Some others you can also use:


    Our attendee toolkit gives you access to additional MySQL Connect resources such as sample e-mail copies to help generate awareness for your participation.

    And, remember you have about 24 hours left to register with the early bird discount and save US$ 500, don’t miss it! Check out why you should attend.

    Sponsorship and exhibit opportunities are still available for the conference. You will find more information here.

    Monday Jul 09, 2012

    EOL of MySQL Forge

    Forge was intended to be a community wiki resource for sharing information with each other.   However, over the last few years, we have seen Forge used less and less by MySQL Community, and more by spammers. What happened?

    MySQL Worklogs and MySQL Internals documentation will be moved to dev.mysql.com and with new anti spam measures in place.

    The MySQL Wiki, which was the primary focus of forge.mysql.com has been migrated to https://wikis.oracle.com/display/mysql

    MySQL Forge will EOL on August 1st 2012.

    Top Reasons to Take the MySQL Cluster Training

    Here are the top reasons to take the authorized MySQL Cluster training course:

    • Take training which was developed by MySQL Cluster product team and delivered by the MySQL Cluster experts at Oracle
    • Learn how to develop, deploy, manage and scale your MySQL Cluster applications more efficiently
    • Keep your mission-critical applications and essential services up and running 24x7
    • Deliver the highest performance and scalability using MySQL Cluster best practices

    In this 3 day course, experienced database users learn the important details of clustering necessary to get started with MySQL Cluster, to properly configure and manage the cluster nodes to ensure high availability, to install the different nodes and provide a better understanding of the internals of the cluster.

    To see the schedule for this course, go to the Oracle University Portal (click on MySQL). Should you not see an event for a location/date that suits you, register your interest in additional events.

    Here is a small sample of the events already on the schedule for the MySQL Cluster course:

     Location

     Date

     Delivery Language

     Prague, Czech Republic

     17 September 2012

     Czech

     Warsaw, Poland

     1 August 2012

     Polish

     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

     Petaling Jaya, Malaysia

     10 October 2012

     English

     Singapore

     21 August 2012

     English

     Mexico City, Mexico

     23 July 2012

     Spanish

    Wednesday Jul 04, 2012

    Why You Should Attend MySQL Connect, and Register Now

    MySQL Connect is taking place on September 29 and 30 in San Francisco. The early bird discount enabling you to save US$ 500 is only running for a few more days, until July 13.

    Are you still wondering if you should sign up? Here are 10 reasons why you definitely should:

    • Learn from other companies how they tackled similar challenges to the ones you’re facing. Find out what they learned along the way, and how you can save time, money and a lot of troubles by avoiding repeating the same mistakes and applying the best practices they’ve developed. You’ll get the chance to hear from organizations including PayPal, Verizon, Twitter, Facebook, Ticketmaster, Ning, Mozilla, CERN, Yahoo! and more!
    • Don’t miss this unique opportunity to meet the engineers developing and supporting the MySQL products in a single location. You’ll be able to ask them all your questions, which can represent a huge time and money saver.
    • Acquire detailed knowledge about InnoDB, the MySQL Optimizer, High Availability strategies, improving performance and scalability, enhancing security and numerous other topics. You’ll hear it straight "from the horse’s mouth" as well as from other MySQL experts in the ecosystem.
    • Get a better understanding about Oracle’s MySQL strategy and about the MySQL roadmap, so you can better plan where to use the MySQL database and MySQL Cluster for your next web, cloud-based and other applications.
    • Get hands-on experience about improving performance with the MySQL Performance Schema, about using MySQL Utilities, MySQL Cluster and a lot more with eight different Hands-On Labs.
    • Express your ideas, engage into discussions and help influence the MySQL roadmap during Birds-of-a-feather sessions about replication, backup, query optimizations and other topics.
    • Meet partners and learn about third party tools that could be useful in your architecture.
    • Immerse yourself into the MySQL universe and hang out with MySQL experts for two days. The discussions as well as the relationships you will create can be priceless and help you execute on your next projects in a much better and faster way.
    • Register Now to save US$500 by taking advantage of the Early bird discount running until July 13. We’ll have parallel tracks so you should consider sending a few team members to make the most of the event. Are you attending or planning to attend Oracle OpenWorld or JavaOne? You can add MySQL Connect to your registration for only US$100!
    • Finally, it’s always a lot of fun to attend a MySQL conference. The passion and the energy are contagious…and you’ll likely get plenty of new ideas.

    You will find all information about the program in the MySQL Connect Content Catalog. We look forward to seeing you there!

    You can also read interviews with Tomas Ulin and Ronald Bradford about MySQL Connect.

    Sponsorship and exhibit opportunities are still available for the conference. You will find more information here.

    Tuesday Jul 03, 2012

    Case Study: Polystar Improves Telecom Networks Performance with Embedded MySQL

    Polystar delivers and supports systems that increase the quality, revenue and customer satisfaction of telecommunication services. Headquarted in Sweden, Polystar helps operators worldwide including Telia, Tele2, Telekom Malysia and T-Mobile to monitor their network performance and improve service levels.

    Challenges

    • Deliver complete turnkey solutions to customers integrating a database ensuring high performance at scale, while being very easy to use, manage and optimize.
    • Enable the implementation of distributed architectures including one database per server while maintaining a low Total Cost of Ownership (TCO).
    • Avoid growing database complexity as the volume of mobile data to monitor and analyze drastically increases.

    Solution

    • Evaluation of several databases and selection of MySQL based on its high performance, manageability, and low TCO.
    • The MySQL databases implemented within the Polystar solutions handle on average 3,000 to 5,000 transactions per second.
    • Up to 50 million records are inserted every day in each database. Typical installations include between 50 and 100 MySQL databases, up to 300 for the largest ones. Data is then periodically aggregated, with the original records being overwritten, as the need for detailed information becomes unnecessary to operators after a few weeks.
    • The exponential growth in mobile data traffic driven by the proliferation of smartphones and usage of social media requires ever more powerful solutions to monitor, analyze and turn network data into actionable business intelligence. With MySQL, Polystar can deliver powerful, yet easy to manage, solutions to its customers.
    • MySQL-based Polystar solutions enable operators to monitor, manage and improve the service levels of their telecom networks in over a dozen countries from a single location.
    • The new and innovative MySQL features constantly delivered by Oracle help ensure Polystar that it will be able to meet its customer’s needs as they evolve.

    MySQL has been a great embedded database choice for us. It delivers the high performance we need while remaining very easy to use, manage and tune. Power and simplicity at its best.” Mats Söderlindh, COO at Polystar.

    Monday Jul 02, 2012

    All New MySQL For Beginners Training on Demand Offering

    Get started on MySQL for Beginners training within 24 hours with the newly released MySQL for Beginners Training on Demand. With Training on Demand, you get:

    - Trained by top MySQL Instructors

    - Access to hands-on practice environment

    - Full classroom content available 24/7

    - And no travel expenses to worry about

    The MySQL for Beginners course covers all the basics and gets you on your way with a solid foundation. This hands-on class covers the fundamentals of SQL and relational databases, using MySQL as a teaching tool.

    In addition to the Training on Demand option, you have the choice to taking the MySQL for Beginners course as:

    • Live Virtual Training: Live, interactive, online training delivered by MySQL instructor to you anywhere you have an internet connection. 100s of events on the schedule for different timezones.
    • In-Classroom Training: Scheduled events include those listed below:

     Location

     Date

     Delivery Language

     Warsaw, Poland

     16 July 2012

     Polish

     Dublin, Ireland

    15 October 2012

     English

     Belfast, Ireland

     28 August 2012

     English

     Rome, Italy

     5 November 2012

     Italy

     Hamburg, Germany

     3 December 2012

     German

     Lisbon, Portugal

     5 November 2012

     European Portugese

     Amsterdam, Netherlands

     10 December 2012

     Dutch

     Nieuwegein, Netherlands

     17 September 2012

     Dutch

     Barcelona, Spain

     5 November 2012

     Spanish

     Riga, Latvia

     15 July 2012

     Latvian

     Petaling Jaya, Malaysia

     7 August 2012

     English

     Ottawa, Canada

     7 August 2012

     English

     Toronto, Canada

     7 August 2012

     English

     Montreal, Canada

     7 August 2012

     English

     Sao Paulo, Brazil

     10 July 2012

     Brazilan Portugese


    For more information on any of the MySQL for Beginners training options or to learn more about the Authorized MySQL curriculum go to the Oracle University portal and click on MySQL.

    NoSQL Java API for MySQL Cluster: Questions & Answers

    The MySQL Cluster engineering team recently ran a live webinar, available now on-demand demonstrating the ClusterJ and ClusterJPA NoSQL APIs for MySQL Cluster, and how these can be used in building real-time, high scale Java-based services that require continuous availability.

    Attendees asked a number of great questions during the webinar, and I thought it would be useful to share those here, so others are also able to learn more about the Java NoSQL APIs.

    First, a little bit about why we developed these APIs and why they are interesting to Java developers.

    ClusterJ and Cluster JPA

    ClusterJ is a Java interface to MySQL Cluster that provides either a static or dynamic domain object model, similar to the data model used by JDO, JPA, and Hibernate. A simple API gives users extremely high performance for common operations: insert, delete, update, and query.

    ClusterJPA works with ClusterJ to extend functionality, including

    - Persistent classes

    - Relationships

    - Joins in queries

    - Lazy loading

    - Table and index creation from object model

    By eliminating data transformations via SQL, users get lower data access latency and higher throughput. In addition, Java developers have a more natural programming method to directly manage their data, with a complete, feature-rich solution for Object/Relational Mapping. As a result, the development of Java applications is simplified with faster development cycles resulting in accelerated time to market for new services.

    MySQL Cluster offers multiple NoSQL APIs alongside Java:

    • - Memcached for a persistent, high performance, write-scalable Key/Value store,
    • - HTTP/REST via an Apache module
    • - C++ via the NDB API for the lowest absolute latency.

    Developers can use SQL as well as NoSQL APIs for access to the same data set via multiple query patterns – from simple Primary Key lookups or inserts to complex cross-shard JOINs using Adaptive Query Localization

    Marrying NoSQL and SQL access to an ACID-compliant database offers developers a number of benefits. MySQL Cluster’s distributed, shared-nothing architecture with auto-sharding and real time performance makes it a great fit for workloads requiring high volume OLTP. Users also get the added flexibility of being able to run real-time analytics across the same OLTP data set for real-time business insight.

    OK – hopefully you now have a better idea of why ClusterJ and JPA are available. Now, for the Q&A.

    Q & A

    Q. Why would I use Connector/J vs. ClusterJ?

    A. Partly it's a question of whether you prefer to work with SQL (Connector/J) or objects (ClusterJ). Performance of ClusterJ will be better as there is no need to pass through the MySQL Server. A ClusterJ operation can only act on a single table (e.g. no joins) - ClusterJPA extends that capability

    Q. Can I mix different APIs (ie ClusterJ, Connector/J) in our application for different query types?

    A. Yes. You can mix and match all of the API types, SQL, JDBC, ODBC, ClusterJ, Memcached, REST, C++. They all access the exact same data in the data nodes. Update through one API and new data is instantly visible to all of the others.

    Q. How many TCP connections would a SessionFactory instance create for a cluster of 8 data nodes?

    A. SessionFactory has a connection to the mgmd (management node) but otherwise is just a vehicle to create Sessions. Without using connection pooling, a SessionFactory will have one connection open with each data node. Using optional connection pooling allows multiple connections from the SessionFactory to increase throughput.

    Q. Can you give details of how Cluster J optimizes sharding to enhance performance of distributed query processing?

    A. Each data node in a cluster runs a Transaction Coordinator (TC), which begins and ends the transaction, but also serves as a resource to operate on the result rows. While an API node (such as a ClusterJ process) can send queries to any TC/data node, there are performance gains if the TC is where most of the result data is stored. ClusterJ computes the shard (partition) key to choose the data node where the row resides as the TC.

    Q. What happens if we perform two primary key lookups within the same transaction? Are they sent to the data node in one transaction?

    A. ClusterJ will send identical PK lookups to the same data node.

    Q. How is distributed query processing handled by MySQL Cluster ?

    A. If the data is split between data nodes then all of the information will be transparently combined and passed back to the application. The session will connect to a data node - typically by hashing the primary key - which then interacts with its neighboring nodes to collect the data needed to fulfil the query.

    Q. Can I use Foreign Keys with MySQL Cluster

    A. Support for Foreign Keys is included in the MySQL Cluster 7.3 Early Access release

    Summary

    The NoSQL Java APIs are packaged with MySQL Cluster, available for download here so feel free to take them for a spin today!

    Key Resources

    MySQL Cluster on-line demo 

    MySQL ClusterJ and JPA On-demand webinar 

    MySQL ClusterJ and JPA documentation

    MySQL ClusterJ and JPA whitepaper and tutorial

    About

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

    Twitter


    Facebook

    Search

    Archives
    « July 2012 »
    SunMonTueWedThuFriSat
    1
    5
    6
    7
    8
    10
    11
    13
    14
    15
    18
    19
    20
    21
    22
    25
    28
    29
        
           
    Today