Monday Mar 24, 2014

Use MySQL API Extensions to Create Dynamic Web Pages

To create dynamic web pages, you can use PHP, a server-side, HTML-embedded scripting language. PHP is available for most operating systems and can access the MySQL database. PHP provides MySQL API extensions, MySQL Improved Extension and MySQL Functions.

To learn more about using MySQL and PHP, you can consult the MySQL and PHP reference manual. You can also take the MySQL and PHP: Developing Dynamic WebApplications training course.

Through a hands-on approach, this 4-day 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 course as a:

  • Live-Virtual Event: Attend a live class from your own desk - no travel required. Choose from the events on the schedule such as April 28th, July 22nd, and October 21st 2014.
  • In-Class Event: Travel to an education center to take a class, for example in Warsaw, Poland on the 22nd of April 2014.

To request additional events, or learn more about the authentic MySQL curriculum, go to http://education.oracle.com/mysql.

Monday Nov 11, 2013

Join Companies in Web and Telecoms by Adopting MySQL Cluster

Join Web and Telecom companies who have adopted MySQL Cluster to facilitate application in the following areas:

Web:

  • High volume OLTP
  • eCommerce
  • User profile management
  • Session management and caching
  • Content management
  • On-line gaming

Telecoms:

  • Subscriber databases (HLR/HSS)
  • Service deliver platforms
  • VAS: VoIP, IPTV and VoD
  • Mobile content delivery
  • Mobile payments
  • LTE access

To come up to speed on MySQL Cluster, take the 3-day MySQL Cluster training course. Events already on the schedule include:

 Location

 Date

 Delivery Language

 Berlin, Germany

 16 December 2013

 German

 Munich, Germany

 2 December 2013

 German

 Budapest, Hungary

 4 December 2013

 Hungarian

 Madrid, Spain

 9 December 2013

 Spanish

 Jakarta Barat, Indonesia

 27 January 2014

 English

 Singapore

 20 December 2013

 English

 Bangkok, Thailand

 28 January 2014

 English

 San Francisco, CA, United States

 28 May 2014

 English

 New York, NY, United States

 17 December 2013

 English

For more information about this course or to request an additional event, go to the MySQL Curriculum Page (http://education.oracle.com/mysql).

Monday Jul 08, 2013

MySQL and PHP as a Foundation for Web Applications

Learn to design web-based applications with MySQL and PHP - Developing Dynamic Web Applications. In this 4-day live, instructor-led class, you will learn to:

  • Design schemas based on MySQL
  • Use include files to make code easier to maintain
  • Use PHP 5 and takes advantage of its advanced features
  • Build applications following a precise flow
  • Authenticate users in a secure way against a database
  • Handle errors in your PHP applications efficiently and gracefully
  • Writes composite queries using JOINs and subqueries
  • Use indexing efficiently in order to manipulate large amounts of data efficiently
  • Uses joins to extract data from multiple tables
  • Uses GROUP BY clauses and aggregate functions
  • Write applications whose components can be scaled to meet increased demand
  • Build a complete application that includes authentication and session management
  • Understand how PHP, MySQL and the Apache web server work together to deliver dynamic web content

You can take this class as a:

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

 Location

 Date

 Delivery Language

 Hamburg, Germany

 21 October 2013

German 

 Rome, Italy

4 November 2013 

Italian 

 Warsaw, Poland

 16 September 2013

Polish

 Barcelona, Spain

26 August 2013 

Spanish 

 Madrid, Spain

2 September 2013 

Spanish

Jakarta, Indonesia 

 15 July 2013

English 

To register for this class, or to learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

Friday Apr 26, 2013

MySQL Web Reference Architectures: On Demand

Last week we ran a live webinar presenting the new MySQL Web Reference Architectures, a set of documented and repeatable best practices for building highly available, scaleable and secure database infrastructure to power new generations of web and mobile services.

The webinar replay is now available on-demand so you can listen in from the comfort of your own desk…or commute.

As a taster - we discuss sizing and design patterns - you can see a sample below:




We received some great questions during the Q&A session which I felt would be useful to you, so I've included them below:

Q. You are talking a lot about MySQL 5.6. We are on MySQL 5.1 - what are the reasons for us to upgrade:

There is a great summary in the MySQL 5.6 developer / DBA guide. You will see the advantage of 5.6 for web and cloud applications in performance, scaling, agility,security, instrumentation and reliability.

Q. When using replication, the master sends only the log file for the slave?

A. The binary log contains all of the database writes on the master. The master then sends the data from this log file to each of the slaves so that they can apply the same writes. It doesn't send the log file itself but sends the changes as they're written to the file (or if a slave disconnects and then reconnects the master will send over all changes that it missed). You can find out more in the introduction to MySQL replication paper 

Q. Are there any open-source utilities to provide Virtual IP failover that can be integrated with mysqlfailover utility? Or should we use corosync and pacemaker with mysqlrpadmin?

Certainly you could use corosync and pacemaker - in which case you're right that mysqlrpladmin might be the best option as then you only have one brain deciding when to fail everything over. Alternatively you can bind your own scripts into mysqlfailover and you could use those to trigger a VIP failover

Q. What should we do to address any potential replication lag?

A. Fast cores on the slave would help. If you're able split your tables between different schemas (databases in MySQL terms) then you can exploit mutil-threaded slaves (parallel slave apply) in MySQL 5.6. Also take a look at optimized row-based replication in MySQL 5.6

Q. Does MySQL 5.6 offer any replication related improvements over 5.5?

A. You bet. Performance, reliability, flexibility. See the introduction paper referenced in the answer above 

Q. Any resources you know of on implementing the Medium-size MySQL web reference architecture with Ruby on Rails apps in the application layer?

A. General best practices outlines in the MySQL web reference architectures guide will apply. There are more specific MySQL / Ruby on Rails best practices here. You can also use community-developed MySQL Ruby connectors

Q. How can we integrate MySQL with Hadoop?

A. You have several options which can be used independently or together. Apache Sqoop provides batch transfers between MySQL and Hadoop, and is also fully bi-directional, so you can replicate the results of Hadoop Map Reduce jobs back to MySQL tables. There is also the new MySQL Applier for Hadoop which enables the streaming of events in real-time from MySQL to Hadoop.

You can learn more about all of the option from the MySQL and Hadoop guide to big data

Q. MySQL Cluster looks interesting, but when we looked at it in the past, it didn't support Foreign Keys, so not sure how you can suggest it as an option for eCommerce apps?

A. There are many eCommerce providers already running MySQL Cluster - Shopatron is a great example. The latest development milestone release of MySQL cluster 7.3 adds native support for Foreign Keys as well - so worth taking another look! 

Q. is it possible to customise auto-sharding when using MySQL Cluster?

A. Yes, you can choose which column within the primary key is to be used as the 'sharding key' - that way you can for example make sure that all data for a specific user will be in the same data node; this can help ensure linear scalability. Details in the MySQL Cluster Performance Optimization guide 

Q. Any advice on UserID generation for sharding?

A. One design pattern is to just use an auto-increment column and then hash on that. Another option is to use a UUID - you can then shard by ranges or by hashing

Q. Can we choose one : clustering or sharding? which one better for HA?

A. It depends on a number of factors such as what level of availability you need, how your application is architected, etc. A good resource to help you weigh up the pros and cons of the different approaches is the MySQL guide to high availability

Q. My company is using 5.5 with one master and slave for our web application. We are currently looking into a new system infrastructure using VM technology. What would you recommend?

A. There are many options available to you on multiple hypervisors. We have created a VM Template for MySQL running on Oracle VM and Oracle Linux. This is a pre-installed, pre-configured and certified software stack which you download as a single file and then provision to an Oracle VM Server farm. You can learn more about it from the Oracle VM Template for MySQL Enterprise Edition guide

Q. I'm interested in the MySQL monitoring tools and in the audit plug-in - how do I get these?

A. These are available as part of MySQL Enterprise Edition which also packages support, backup, security plug-ins, etc in an annual subscription.

Q. How much would it cost to run MySQL Enterprise Edition on that "perfect server"?

A. Pricing is available in the on-line store. Note that the list price is the same regardless of number of cores unless you exceed 4 CPU *sockets*

Q. i am sorry for this very novice question, but could I have some help regarding references that would allow me to digest the" base" of these technologies presented her?

A. Assuming it is case studies you are after, we have a lot of them published at the MySQL customers page. In the "Industry" drop down box, take a look at Web eCommerce, games, SaaS/Hosting and social networks


Thursday Apr 18, 2013

Adzuna Relies on MySQL to Support Explosive Growth

Adzuna is a fast growing search engine for classified ads specialized in jobs, properties and cars. Headquartered in the UK and launched in 2011, Adzuna searches thousands of sites and tens of millions of ads to make it very easy to find the perfect job, home or car locally. It furthermore provides a wealth of statistics such as salaries trends graphs and comparisons, geographic jobs maps, house prices...and more. Additionally, Adzuna is integrated with Facebook and LinkedIn and shows open vacancies one is connected to through his/her own network. The search engine powers a number of government applications and is integrated into the UK's Prime Minister economic dashboard.

Challenges

  • When Adzuna's founders were selecting the database powering the search engine's architecture, they were planning for scalability and reliability. Not only did they expect fast growth but also unpredictable growth. The number of users could indeed jump from ten thousand to one million in a single day, and any downtime or scalability was simply not an option as it could turn away new users from the site forever, and undermine its reputation form the start.
  • As a Web startup, low Total Cost of Ownership was essential, and the team also desired to implement a database solution they would be able to customize and tailor to their specific needs.


Solution

  • The Adzuna team selected MySQL as the database powering their search engine. They had very positive previous experiences of the world’s most popular open source database, and particularly appreciated its performance and scalability, reliability and ease of use, including the quality of its technical documentation. They were confident it would scale according to their requirements, and were ready to bet their business on the database.
  • Key MySQL strong points included its overall suitability for highly demanding web-based applications, its Geographic Information System (GIS) support and integration with the Perl programming language used by the company.
  • Adzuna's search infrastructure uses MySQL to normalize on a minute by minute basis the data from tens of millions of ads from thousands of websites across four continents, resulting in a database size exceeding 100 GB. The Adzuna engine constantly scrolls the Web and receives XML feeds from its partners, and a key to its success is its ability to turn a massive amount of unstructured data into structured data that can be stored, understood and searched by it users. The team decided to use the Apache Solr open source search platform as front end, leveraging its NoSQL features. Data is subsequently transferred into MySQL. The following diagram describes the Adzuna architecture:
  • In order to help ensure scalability and reliability while focusing its resources on developing its business, The Adzuna team decided to rely on MySQL in the cloud, working with cloud services provider Media Temple.
  • The startup has experienced explosive growth since its launch, currently serving 2 million unique visitors per month, and it recently expanded in Germany, Australia, Canada, South Africa and Brazil.  MySQL totally fulfills the expectations of the Adzuna team, who is confident the database will scale to support their ambition to expand in 30 more countries and become the leading search engine for jobs, homes and cars worldwide.
  • As Taleo, Oracle's talent management cloud service, is increasingly used by Adzuna's partners, the company is currently looking at optimizing data transfer from Taleo to MySQL.

“My advice to young startups is to use MySQL, especially if you have high growth expectations. You’ll need to plan for unpredictability and to have a very robust backbone to support it, and that’s exactly what MySQL provides.” Andrew Hunter, Co-Founder, Adzuna

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.

Friday Aug 05, 2011

Scaling Web Databases, Part 3: SQL & NoSQL Data Access

Supporting successful services on the web means scaling your back-end databases across multiple dimensions. This blog focuses on scaling access methods to your data using SQL and/or NoSQL interfaces.

In Part 1 of the blog series , I discussed scaling database performance using auto-sharding and active/active geographic replication in MySQL Cluster to enable applications to scale both within and across data centers.  

In Part 2, I discussed the need to scale operational agility to keep pace with demand, which includes being able to add capacity and performance to the database, and to evolve the schema – all without downtime.

So in this blog I want to explore another dimension to scalability -  how multiple interfaces can be used to scale access to the database, enabling users to simultaneously serve multiple applications, each with distinct access requirements.

Data Access Interfaces to MySQL Cluster

MySQL Cluster automatically shards tables across pools of commodity data nodes, rather than store those tables in a single MySQL Server. It is therefore able to present multiple interfaces to the database, giving developers a choice between:

- S    -  SQL for complex reporting-type queries;

- S    -  Simple Key/Value interfaces bypassing the SQL layer for blazing fast reads & writes;

- S    -  Real-time interfaces for micro-second latency, again bypassing the SQL layer

With this choice of interfaces, developers are free to work in their own preferred environments, enhancing productivity and agility and enabling them to innovate faster.

SQL or NoSQL - Selecting the Right Interface

The following chart shows all of the access methods available to the database. The native API for MySQL Cluster is the C++ based NDB API. All other interfaces access the data through the NDB API.

At the extreme right hand side of the chart, an application has embedded the NDB API library enabling it to make native C++ calls to the database, and therefore delivering the lowest possible latency.

On the extreme left hand side of the chart, MySQL presents a standard SQL interface to the data nodes, and provides connectivity to all of the standard MySQL connectors including:

- Common web development languages and frameworks, i.e. PHP, Perl, Python, Ruby, Ruby on Rails, Spring, Django, etc;

- JDBC (for additional connectivity into ORMs including EclipseLink, Hibernate, etc)

- .NET

- ODBC

Whichever API is chosen for an application, it is important to emphasize that all of these SQL and NoSQL access methods can be used simultaneously, across the same data set, to provide the ultimate in developer flexibility. Therefore, MySQL Cluster maybe supporting any combination of the following services, in real-time:

- Relational queries using the SQL API;

- Key/Value-based web services using the REST/JSON and memcached APIs;

- Enterprise applications with the ClusterJ and JPA APIs;

- Real-time web services (i.e. presence and location based) using the NDB API.

The following figure aims to summarize the capabilities and use-cases for each API.

Schema-less Data Store with the memcached API

As part of the MySQL Cluster 7.2 Development Milestone Release , Oracle announced the preview of native memcached Key/Value API support for MySQL Cluster enabling direct access to the database from the memcached API without passing through the SQL layer. You can read more about the implementation and how to get going with it in this excellent post from Andrew Morgan.

The following image shows the implementation of the memcached API for MySQL Cluster 


Implementation is simple - the application sends read and write requests to the memcached process (using the standard memcached API). This in turn invokes the Memcached Driver for NDB (which is part of the same process), which in turn calls the NDB API for very quick access to the data held in MySQL Cluster’s data nodes.

The solution has been designed to be very flexible, allowing the application architect to find a configuration that best fits their needs. It is possible to co-locate the memcached API in either the data nodes or application nodes, or alternatively within a dedicated memcached layer.

The benefit of this approach is that users can configure behavior on a per-key-prefix basis (through tables in MySQL Cluster) and the application doesn’t have to care – it just uses the memcached API and relies on the software to store data in the right place(s) and to keep everything synchronized.

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.

Summary

MySQL Cluster provides developers and architects with a huge amount of flexibility in accessing their persistent data stores - a reflection that one size no longer fits all in the world of web services and databases.

You can learn more about this, and the other dimensions to scaling web databases in our new Guide. 

As ever, let me know your thoughts in the comments below. 


Thursday Jul 21, 2011

Scaling Web Databases, Part 2: Adding Nodes, Evolving Schema with Zero Downtime

In my previous post, I discussed scaling web database performance in MySQL Cluster using auto-sharding and active/active geographic replication - enabling users to scale both within and across data centers.  

I also mentioned that while scaling write-performance of any web service is critical, it is only 1 of multiple dimensions to scalability, which include:

- The need to scale operational agility to keep pace with demand. This means being able to add capacity and performance to the database, and to evolve the schema – all without downtime;

- The need to scale queries by having flexibility in the APIs used to access the database – including SQL and NoSQL interfaces;

- The need to scale the database while maintaining continuous availability.

All of these subjects are discussed in more detail in our new Scaling Web Databases guide.

In this posting, we look at scaling operational agility. 

As a web service gains in popularity it is important to be able to evolve the underlying infrastructure seamlessly, without incurring downtime and without having to add lots of additional DBA or developer resource.

Users may need to increase the capacity and performance of the database; enhance their application (and therefore their database schema) to deliver new capabilities and upgrade their underlying platforms.

MySQL Cluster can perform all of these operations and more on-line – without interrupting service to the application or clients.  

On-Line, On-Demand Scaling

MySQL Cluster allows users to scale both database performance and capacity by adding Application and Data Nodes on-line, enabling users to start with small clusters and then scale them on-demand, without downtime, as a service grows. Scaling could be the result of more users, new application functionality or more applications needing to share the database.

In the following example, the cluster on the left is configured with two application and data nodes and a single management server.  As the service grows, the users are able to scale the database and add management redundancy – all of which can be performed as an online operation.  An added advantage of scaling the Application Nodes is that they provide elasticity in scaling, so can be scaled back down if demand to the database decreases.

When new data nodes and node groups are added, the existing nodes in the cluster initiate a rolling restart to reconfigure for the new resource.  This rolling restart ensures that the cluster remains operational during the addition of new nodes.  Tables are then repartitioned and redundant rows are deleted with the OPTIMIZE TABLE command.  All of these operations are transactional, ensuring that a node failure during the add-node process will not corrupt the database.

The operations can be performed manually from the command line or automated with MySQL Cluster Manager , part of the commercial MySQL Cluster Carrier Grade Edition.

On-Line Cluster Maintenance

With its shared-nothing architecture, it is possible to avoid database outages by using rolling restarts to not only add but also upgrade nodes within the cluster.  Using this approach, users can:

- Upgrade or patch the underlying hardware and operating system;

- Upgrade or patch MySQL Cluster, with full online upgrades between releases.

MySQL Cluster supports on-line, non-blocking backups, ensuring service interruptions are again avoided during this critical database maintenance task.  Users are able to exercise fine-grained control when restoring a MySQL Cluster from backup using ndb_restore. Users can restore only specified tables or databases, or exclude specific tables or databases from being restored, using ndb_restore options --include-tables, --include-databases, --exclude-tables, and --exclude-databases.

On-Line Schema Evolution

As services evolve, developers often want to add new functionality, which in many instances may demand updating the database schema.  

This operation can be very disruptive for many databases, with ALTER TABLE commands taking the database offline for the duration of the operation.  When users have large tables with many millions of rows, downtime can stretch into hours or even days.

MySQL Cluster supports on-line schema changes, enabling users to add new columns and tables and add and remove indexes – all while continuing to serve read and write requests, and without affecting response times.

Unlike other on-line schema update solutions, MySQL Cluster does not need to create temporary tables, therefore avoiding the user having to provision double the usual memory or disk space in order to complete the operation.

Summary

So in addition to scaling write performance, MySQL Cluster can also scale operational agility.  I'll post more on scaling of data access methods and availability levels over the next few weeks.

You can read more about all of these capabilities in the new Scaling Web Databases guide.  

And of course, you can try MySQL Cluster out for yourself - its available under the GPL:

The GA release is 7.1 which can be downloaded here, but I'd recommend taking a look at the latest Development Milestone Release for MySQL Cluster 7.2 which has some great new capabilities (localized JOIN operations, simpler provisioning, etc) which can be downloaded from here (select the Development Releases tab).

As ever, let me know if there are other dimensions of scalability that I should be discussing 

Tuesday Jun 14, 2011

Scaling Web Databases: Auto-Sharding with MySQL Cluster

The realities of today’s successful web services are creating new demands that many legacy databases were just not designed to handle:

- The need to scale writes, as well as reads, both within and across geographically dispersed data centers;

- The need to scale operational agility to keep pace with database load and application requirements. This means being able to add capacity and performance to the database, and to evolve the schema – all without downtime;

- The need to scale queries by having flexibility in the APIs used to access the database;

- The need to scale the database while maintaining continuous availability for both failures as well as scheduled maintenance events.

Each of the requirements above warrant their own dedicated blog, which I’ll find time to write over the next few weeks.

But to get started, I wanted to discuss how the MySQL Cluster database addresses the first point – scaling writes to the database with automatic sharding and geographic replication.

Auto-Sharding

MySQL Cluster is implemented as a distributed, multi-master database with no single point of failure. Tables are automatically sharded across a pool of low cost commodity nodes, enabling the database to scale horizontally to serve read and write-intensive workloads, accessed both from SQL and directly via NoSQL APIs (memcached, REST/HTTP, C++, Java, JPA and LDAP). Up to 255 nodes are supported, of which 48 are data nodes. You can read more about the different types of nodes here.

By automatically sharding tables in the database, MySQL Cluster eliminates the need to shard at the application layer, greatly simplifying application development and maintenance.

Sharding is based on the hashing of the primary key, though users can override this by telling MySQL Cluster which fields from the primary key should be used in the hashing algorithm. Hashing on the primary key generally leads to a more even distribution of data and queries across the cluster than alternative approached such as range partitioning.

Figure 1 demonstrates how MySQL Cluster shards tables across data nodes of the cluster.

Figure 1: Auto-Sharding in MySQL Cluster

You will see from the figure above that MySQL Cluster automatically creates “node groups” from the number of replicas and data nodes specified by the user. Updates are synchronously replicated between members of the node group to protect against data loss and enable sub-second failover in the event of a node failure.

Figure 2 shows how MySQL Cluster creates primary and secondary fragments of each shard.


Figure 2: Eliminating Data Loss with Cross-Shard Fragments

MySQL Cluster is an active/active architecture with multi-master replication, so updates made by any application or SQL node accessing the cluster are instantly available to all of the other nodes accessing the cluster.

Unlike other distributed databases, users do not lose the ability to perform JOIN operations or sacrifice ACID-guarantees. In the Development Release of MySQL Cluster (7.2), Adaptive Query Localization pushes JOIN operations down to the data nodes where they are executed locally and in parallel. We've seen 20-40x higher throughput from the community members that have tested it.

Geographic Replication

Of course, web services are global and so developers will want to ensure their databases can scale-out across regions. MySQL Cluster offers Geographic Replication which distributes clusters to remote data centers, serving to reduce the affects of geographic latency as well as provide a facility for disaster recovery.

Figure 3: Geographic Replication with MySQL Cluster

Geographic Replication is asynchronous and based on standard MySQL replication – with one important difference – it is active/active so supports the detection and resolution of conflicts when the same row is updated across different clusters. This does currently require the addition of a timestamp column in the application, but that is expected to be eliminated in future releases.

Where the Rubber Meets the Road

Auto-sharding and geographic replication are all great technologies, but what do they mean in terms of delivered performance ?

The MySQL Cluster development team recently ran a series of benchmarks that characterized performance across 8 x dual socket 2.93GHz, 6 core commodity Intel servers, each equipped with 24GB of RAM. As seen in the figure below, MySQL Cluster delivered just under 2.5 million updates per second with 2 x data nodes configured per server.

Figure 4: MySQL Cluster performance scaling-out on commodity nodes.

Across 16 Intel servers, MySQL Cluster achieved just under 7 million read operations per second. We ran out of time in the test cluster before being able to complete the test of write performance, but will return to those efforts soon.

Wrap-Up

So what does all of this mean ? There is an ever-growing array of options for developers to choose from when scaling out new generations of web applications. Don’t assume that relational databases can’t scale, or offer the kind of operational agility demanded by today’s highly dynamic services. MySQL Cluster is already proven as one such option….and you don’t have to throw away ACID guarantees or the ability to run complex queries to get scalability or schema agility.

You can learn about how MySQL Cluster implements auto-sharding, along with other key features for web services such as online schema updates and NoSQL interfaces from a new on-demand webinar.

And of course MySQL Cluster is open source, so you are free to download, develop and deploy with it. The latest GA release is here.

The MySQL Cluster 7.2 Development Milestone Release including Adaptive Query Localization is here (select the Development Release tab):

Finally, if you wanted to try out MySQL Cluster with the memcached API, you can get it from the latest build on the MySQL labs site.

As ever, let us know how these technologies work for you, either in the comments below or via the MySQL Cluster forum.

Wednesday May 18, 2011

Unlocking New Value from Web Session Management

Join us for a live webinar and download a new whitepaper where we discuss how to realize new value from data collected during web session management.

Session management has long been a key component of any web infrastructure – enhancing the user browsing experience through improved reliability, reduced latency and tighter security.

Increasingly organizations are looking to unlock more value from session management to further improve user loyalty (i.e. making the web service more “sticky”) and improve monetization of web services.  There are two distinct developments that offer the promise of unlocking more value from session data:
1.    Provide highly personalized browsing experiences by recognizing repeat visitors and making real-time recommendations based on previous browsing behavior
2.    Enhance insight into user behavior through analysis of how they interact with the web service, enabling organizations to optimize web experiences  

There are many approaches to session management, and technology selection has become critical in ensuring the full value of data collected from user sessions can be realized.

For rapidly growing web properties, higher volumes of session data need to be managed and persisted in real-time while also demanding very high levels of availability, coupled with the flexibility of relational data management.

In such cases, it makes sense to evaluate the MySQL Cluster database.

To further discuss the challenges and solutions to session management, we are hosting a live webinar on Tuesday May 31st at 0900 Pacific Time / 1700 UK.  In this webinar, we discuss the challenges and solutions to session management, covering:

* The demands of session management
* How MySQL Cluster is well placed to meet the demands from session management
* Configuring session management with PHP and MySQL Cluster
* Configuring session management with with memcached and MySQL Cluster
* Real Time analysis of session data with MySQL Cluster
* Case studies

You can register for the webinar here

You can download the associated whitepaper here

Let us know your recommendations for unlocking more value from web session data in the comments below

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