MySQL is a very scalable platform which has proven robust even in the most dense and complex data environments. MySQL's indispensable replication function is 'sold' as being fail-safe so you have little to sweat about as long as your backups are running regularly. But what the ops guys aren't telling you is MySQL performs replication with tiny margins of error that could cause big problems in times of disaster.

The Scenario
Imagine the scene, you use replication to backup your data. Your secondary database is your peace of mind. It's the always-on clone of your crown jewels. You even perform backups off of it so you don't impact your live website. Your backups run without errors. Your slave database runs without errors.
Then the dreaded day comes when your primary database fails. You instruct your team to switchover your application to point to your live backup database. The site comes online again. But all is not right. You notice subtle differences and your team begins to question how deep the data divide could be.

The Problem
Although MySQL replication is fairly easy to setup, and even to keep running without error, you may have unseen problems. MySQL's core technology to replicate data between master and slave is primarily statement based. Various scenarios can cause what in other database platforms you might call database corruption, that is silent drifting of data from what tables and rows contain on the master. It is no fault of your own, or perhaps one might argue even of your operations team. It is a fundamental flaw in how MySQL performs replication.

The Solution
Fortunately there is a solution. Checksums, the wonderful computational tool for comparing things can be put to work nicely to compare database. The Percona Toolkit (formerly maatkit) includes just such a utility for use with MySQL. It can be used to check the integrity of your slave databases.
If you've never performed such a check, you should do so ASAP. If your database has been running for months at a stretch, chances are there could be differences lying undiscovered between the two systems.

Depending on the volume changing in your database, you can continue to use this tool periodically to confirm that all is consistent. If integrity checks fail, there is another tool in Maatkit to syncronize differences, and bring everything back to order.

PlanetMySQL Voting: Vote UP / Vote DOWN

One of the great things about the HTTP protocol, besides status code 418, is that it's stateless. A web server therefore is not required to store any information on the user or allocate resources for a user after the individual request is done. By that a single web server can handle many many many different users easily, and well if it can't anymore one can add a new server, put a simple load balancer in front and scale out. Each of those web servers then handles its requests without the need for communication which leads to linear scaling (assuming network provides enough bandwidth etc.).

Now the Web isn't used for serving static documents only anymore but we have all these fancy web apps. And those applications often have the need for a state. The most trivial information they need is the current user. HTTP is a great protocol and provides a way to do authentication which works well with its stateless nature - unfortunately this authentication is implemented badly in current clients. Ugly popups, no logout button, ... I don't have to tell more I think. For having nicer login systems people want web forms. Now the stateless nature of HTTP is a problem: The user may login and then browse around. On later requests it should still be known who that user is - with a custom HTML form based login alone this is not possible. A solution might be cookies. At least one might think so for a second. But setting a cookie "this is an authorized user" alone doesn't make sense as it could easily be faked. Better is to simply store a random identifier in a cookie and then keep a state information on the server. Then all session data is protected and only the user who knows this random identifier is authenticated. If this identifier is wisely chosen and hard to guess this works quite well. Luckily this is a mostly PHP- and MySQL-focused blog and as PHP is a system for building web applications this functionality is part of the core language: The PHP session module.

The session module, which was introduced in PHP 4, partly based on work on the famous phplib library, is quite a fascinating piece of code. It is open and extendable in so many directions but still so simple to use that everybody uses it, often newcomers learn about it on their first day in PHP land. Of course you can not only store the information whether the user is logged in but cache some user-specific data or keep the state on some transactions by the user, like multi-page forms or such.

In its default configuration session state will be stored on the web server's file system. Each session's data in its own file in serialized form. If the filesystem does some caching or one uses a ramdisk or something this can be quite efficient. But as we suddenly have a state on the web server we can't scale as easily as before anymore: If we add a new server and then route a user with an existing session to the new server all the session data won't be there. That is bad. This is often solved by a configuration of the load balancer to route all requests from the same user to the same web server. In some cases this works quite ok, but it is often seen that this might cause problems. Let's assume you want to take a machine down for maintenance. All sessions there will die. Or imagine there's a bunch of users who do complex and expensive tasks - then one of your servers will have a hard time, giving these users bad response times which feels like bad service, even though your other systems are mostly idle.

A nice solution for this would be to store the sessions in a central repository which can be accessed from all web servers.

Continue reading "High Performance PHP Session Storage on Scale"
PlanetMySQL Voting: Vote UP / Vote DOWN

Working with replication, you come across many topologies, some of them sound and established, some of them less so, and some of them still in the realm of the hopeless wishes. I have been working with replication for almost 10 years now, and my wish list grew quite big during this time. In the last 12 months, though, while working at Continuent, some of the topologies that I wanted to work with have moved from the cloud of wishful thinking to the firm land of things that happen. My quest for star replication starts with the most common topology. One master, many slaves.

Fig 1. Master/Slave topology

Legend

It looks like a star, with the rays extending from the master to the slaves. This is the basis of most of the replication going on mostly everywhere nowadays, and it has few surprises. Setting aside the problems related to failing over and switching between nodes, which I will examine in another post, let's move to another star.

Fig 2. Fan-in slave, or multiple sources

The multiple source replication, also known as fan-in topology, has several masters that replicate to the same slave. For years, this has been forbidden territory for me. But Tungsten Replicator allows you to create multiple source topologies easily. This is kind of uni-directional, though. I am also interested in topologies where I have more than one master, and I can retrieve data from multiple points.

Fig 3. all-to-all three nodes

Fig 4. All-to-all four nodes

Tungsten Multi-Master Installation solves this problem. It allows me to create topologies where every node replicates to every other node. Looking at the three-node scheme, it appears a straightforward solution. When we add one node, though, we see that the amount of network traffic grows quite a lot. The double sided arrows mean that there is a replication service at each end of the line, and two open data channels. When we move from three nodes to four, we double the replication services and the channels needed to sustain the scheme. For several months, I was content with this. I thought: it is heavy, but it works, and it's way more than what you can do with native replication, especially if you consider that you can have a practical way of preventing conflicts using Shard Filters. But that was not enough. Something kept gnawing at me, and from time to time I experimented with Tungsten Replicator huge flexibility to create new topologies. But the star kept eluding me. Until … Until, guess what? a customer asked for it. The problem suddenly ceased to be a personal whim, and it became a business opportunity. Instead of looking at the issue in the idle way I often think about technology, I went at it with practical determination. What failed when I was experimenting in my free time was that either the pieces did not glue together the way I wanted, or I got an endless loop. Tungsten Replicator has a set of components that are conceptually simple. You deploy a pipeline between two points, open the tap, and data starts flowing in one direction. Even with multiple masters replication, the principle is the same. You deploy many pipes, and each one has one purpose only.

Fig 5. All-masters star topology

In the star topology, however, you need to open more taps, but not too many, as you need to avoid the data looping around. The recipe, as it turned out, is to create a set of bi-directional replication systems, where you enable the central node slave services to get changes only from a specific master, and the slave services on the peripheral nodes to accept changes from any master. It was as simple as that. There are, of course, benefits and drawbacks with a star topology, compared to a all-replicate-to-all design. In the star topology, we create a single point of failure. If the central node fails, replication stops, and the central node needs to be replaced. Instead, the all-to-all design has no weaknesses. Its abundance of connections makes sure that, if a node fails, the system continues working without any intervention. There is no need for fail-over.

Fig 6. extending an all-to-all topology

Fig 7. Extending a star topology

However, there is a huge benefit in the node management. If you need to add a new node, it costs two services and two connections, while the same operation in the all-to-all replication costs 8 services and 8 connections. With the implementation of this topology, a new challenge has arisen. While conflict prevention by sharding is still possible, this is not the kind of scenario where you want to apply it. We have another conflict prevention mechanism in mind, and this new topology is a good occasion make it happen. YMMV. I like the additional choice. There are cases where a all-replicate-to-all topology is still the best option, and there are cases where a star topology is more advisable.
PlanetMySQL Voting: Vote UP / Vote DOWN

Effective MySQL: Optimizing SQL Statements

by Ronald Bradford

No Nonsense, Readable, Practical, and Compact

I like that this book is small; 150 pages means you can carry it easily.  It's also very no nonsense.  It does not dig too deeply into theory unless it directly relates to your day-to-day needs.  And those needs probably cluster heavily around optimizing SQL queries, as those pesky developers are always breaking things

Jokes aside, this new book out on Oracle Press is a very readable volume. Bradford has drawn directly from real-world experience to give you the right bite size morsels you need in your day-to-day MySQL activities.

Highlights

Chapter one, The Five Minute DBA gives you the basic methodology if you don't already know it.  Enable the slow query log, analyze it, and use the explain facility.  Then index as appropriate, or eliminate queries if you can.

Chapter two digs a little deeper into the basics, introducing explain extended, table statistics and storage engines.  You'll also learn how to use show session & global status, as well as session & global variables.  You'll also have your first look at MySQL's data dictionary - INFORMATION_SCHEMA.

Chapter three is where it starts to get meaty.  You probably know that MySQL has b-tree indexes, but did you know that it has b+tree indexes, or hash indexes?

Chapter four digs into indexes further with single & multi-column indexes using them for sorting and joining.  You'll also find out about covering indexes which are multi-column matching the where clause, but also including columns needed in the SELECT predicate.  Do you have duplicate or unused indexes?  You'll learn why they matter to performance and how to eliminate them with tools like mk-duplicate-key-checker.

Chapter five continues along the same lines, with more coverage of indexes.  Learn to identify when you are using a covering index, fulfilling the entire query by only accessing the index.  You'll also learn about partial indexes, how they can reduce the size of index storage and retrieval while still getting your data for you.

Chapter six covers configuring the server itself, hitting on the system variables such as the innodb buffer pool (innodb_buffer_pool_size) and key buffer (key_buffer_size) as well as the query cache.  You'll also learn how to set the four main session memory settings - sort buffer (sort_buffer_size) and join buffer ( join_buffer_size) as well as the lesser known read buffers (read_buffer_size and read_rnd_buffer_size).

Chapter seven is all about the process of tuning and optimizing MySQL.  Rolling all the previous sections into marching orders, and prescriptive advice, he takes you through step by step how to apply the principles.  You'll get an introduction to mk-query-digest (though strangely without attribution to Baron Schwartz), the great maatkit tool for query analysis and aggregation, as well as the microsecond precision patch, which allows your mysql shell client to display more exact timing data.  For the patch he links back to an article on his own site which seems to be not found.  The author of the high precision mysql timer patch is Stewart Smith.

I personally got the most out of Chapter eight, full of self-described hidden performance tips.  From identifying unused or duplicate indexes, to replacing inefficient data types with better ones, why it's important to use NOT NULL where possible or how to store IP addresses efficiently, this chapter has a lot of goodies.  For those still struggling with SQL statement tuning, there are a few patterns that are described, offering advice on how to rewrite a subquery as an inner join,

What you might not know

• MySQL includes Oracle's index organized tables by a different name
• Too many indexes can dramatically impact INSERT & UPDATE performance
• Many DDL operations can be done online - see oak-online-alter-table (Shlomi Noach)
• Datatypes matter - use enum, int unsigned, timestamp & not null where possible
• Covering indexes are your friend, duplicate & unused indexes are not!
• A replication slave can have different storage engines or indexes from the master. These can support different uses - such as data warehousing or non-transactional requirements.
• While a_string LIKE '%end of my sentence.' won't use an index, you can index reverse_string, then use reverse_string LIKE REVERSE '%end of my sentence.' and MySQL will use this index.  You've simulated an advanced Oracle feature, reverse key indexes!

A few small gripes

If I were to add a few complaints it would be to say that some of the examples were rather simplistic.  In many cases tuning SQL is not as simple as just adding the right index.  For instance there was no good discussion of the dreaded "using temporary, using filesort" that we see a lot in MySQL explains when sorting has to be done, but will not fit in memory.  Or what about tmpdir=/dev/shm, how will that improve things?  What about UNION versus UNION ALL where appropriate.  Why does DISTINCT do a sort?

The book was also missing a discussion of triggers, stored procedures, when or if the query cache can cause problems and so forth.  Also the article link mentioned about chapter seven isn't the only missing link.  I followed links to optimizing sql  statements and it seems to go to a generic holding page.  Also the main link effectivemysql.com/book leads to an outline of an as yet unreleased title on Backup and Recovery.

All in all, well worth your money

However, other than these few gripes the book overall is a very welcome addition to the small family of MySQL books.  Get a copy quick before they're all gone!

 

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Effective MySQL: Optimizing SQL Statements

by Ronald Bradford

No Nonsense, Readable, Practical, and Compact

I like that this book is small; 150 pages means you can carry it easily.  It's also very no nonsense.  It does not dig too deeply into theory unless it directly relates to your day-to-day needs.  And those needs probably cluster heavily around optimizing SQL queries, as those pesky developers are always breaking things

Jokes aside, this new book out on Oracle Press is a very readable volume. Bradford has drawn directly from real-world experience to give you the right bite size morsels you need in your day-to-day MySQL activities.

Highlights

Chapter one, The Five Minute DBA gives you the basic methodology if you don't already know it.  Enable the slow query log, analyze it, and use the explain facility.  Then index as appropriate, or eliminate queries if you can.

Chapter two digs a little deeper into the basics, introducing explain extended, table statistics and storage engines.  You'll also learn how to use show session & global status, as well as session & global variables.  You'll also have your first look at MySQL's data dictionary - INFORMATION_SCHEMA.

Chapter three is where it starts to get meaty.  You probably know that MySQL has b-tree indexes, but did you know that it has b+tree indexes, or hash indexes?

Chapter four digs into indexes further with single & multi-column indexes using them for sorting and joining.  You'll also find out about covering indexes which are multi-column matching the where clause, but also including columns needed in the SELECT predicate.  Do you have duplicate or unused indexes?  You'll learn why they matter to performance and how to eliminate them with tools like mk-duplicate-key-checker.

Chapter five continues along the same lines, with more coverage of indexes.  Learn to identify when you are using a covering index, fulfilling the entire query by only accessing the index.  You'll also learn about partial indexes, how they can reduce the size of index storage and retrieval while still getting your data for you.

Chapter six covers configuring the server itself, hitting on the system variables such as the innodb buffer pool (innodb_buffer_pool_size) and key buffer (key_buffer_size) as well as the query cache.  You'll also learn how to set the four main session memory settings - sort buffer (sort_buffer_size) and join buffer ( join_buffer_size) as well as the lesser known read buffers (read_buffer_size and read_rnd_buffer_size).

Chapter seven is all about the process of tuning and optimizing MySQL.  Rolling all the previous sections into marching orders, and prescriptive advice, he takes you through step by step how to apply the principles.  You'll get an introduction to mk-query-digest (though strangely without attribution to Baron Schwartz), the great maatkit tool for query analysis and aggregation, as well as the microsecond precision patch, which allows your mysql shell client to display more exact timing data.  For the patch he links back to an article on his own site which seems to be not found.  The author of the high precision mysql timer patch is Stewart Smith.

I personally got the most out of Chapter eight, full of self-described hidden performance tips.  From identifying unused or duplicate indexes, to replacing inefficient data types with better ones, why it's important to use NOT NULL where possible or how to store IP addresses efficiently, this chapter has a lot of goodies.  For those still struggling with SQL statement tuning, there are a few patterns that are described, offering advice on how to rewrite a subquery as an inner join,

What you might not know

• MySQL includes Oracle's index organized tables by a different name
• Too many indexes can dramatically impact INSERT & UPDATE performance
• Many DDL operations can be done online - see oak-online-alter-table (Shlomi Noach)
• Datatypes matter - use enum, int unsigned, timestamp & not null where possible
• Covering indexes are your friend, duplicate & unused indexes are not!
• A replication slave can have different storage engines or indexes from the master. These can support different uses - such as data warehousing or non-transactional requirements.
• While a_string LIKE '%end of my sentence.' won't use an index, you can index reverse_string, then use reverse_string LIKE REVERSE '%end of my sentence.' and MySQL will use this index.  You've simulated an advanced Oracle feature, reverse key indexes!

A few small gripes

If I were to add a few complaints it would be to say that some of the examples were rather simplistic.  In many cases tuning SQL is not as simple as just adding the right index.  For instance there was no good discussion of the dreaded "using temporary, using filesort" that we see a lot in MySQL explains when sorting has to be done, but will not fit in memory.  Or what about tmpdir=/dev/shm, how will that improve things?  What about UNION versus UNION ALL where appropriate.  Why does DISTINCT do a sort?

The book was also missing a discussion of triggers, stored procedures, when or if the query cache can cause problems and so forth.  Also the article link mentioned about chapter seven isn't the only missing link.  I followed links to optimizing sql  statements and it seems to go to a generic holding page.  Also the main link effectivemysql.com/book leads to an outline of an as yet unreleased title on Backup and Recovery.

All in all, well worth your money

However, other than these few gripes the book overall is a very welcome addition to the small family of MySQL books.  Get a copy quick before they're all gone!

 

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Recently at Surge 2011, the annual  conference on scalability  and performance, Google's CIO Ben Fried gave an illuminating keynote address. His main insight was that generalists are the people that will lead engineering teams in successfully scaling the web.

PlanetMySQL Voting: Vote UP / Vote DOWN

I’ve been seeing a few occasions where Neil J. Gunther’s Universal Scalability Law doesn’t seem to model all of the important factors in a system as it scales. Models are only models, and they’re not the whole truth, so they never match reality perfectly. But there appear to be a small number of cases where systems can actually scale a bit better than linearly over a portion of the domain, due to what I’ve been calling an “economy of scale.” I believe that the Universal Scalability Law might need a third factor (seriality, coherency, and the new factor, economy of scale). I don’t think that the results I’m seeing can be modeled adequately with only two parameters.

Here are two publicly available cases that appear to demonstrate this phenomenon: Robert Haas’s recent blog post on PostgreSQL, titled Scalability, in Graphical Form, Analyzed and Mikael Ronstrom’s post from May on MySQL (NDB) Cluster, titled Better than Linear Scaling is Possible.

Dr. Ronstrom’s post discusses the mechanics of the phenomenon, and speculates (I’m not sure it’s conclusive) that it is from a combination of partitioning and better use of CPU caches. Now someone needs to do the math to figure out how to include this factor into the equation.

The good thing about the Universal Scalability Law is how simple and applicable it is for many systems. It’s nice that this economy-of-scale factor seems to be unusual and the simpler model remains easy to apply for a large variety of tasks.

Further Reading:

• Products that scale linearly to hundreds of servers
• Response-time optimization in systems that are queued
• How to scale writes with master-master replication in MySQL
• Subtleties in the Universal Scalability Law
• A review of Guerrilla Capacity Planning by Neil Gunther

PlanetMySQL Voting: Vote UP / Vote DOWN

This year’s Surge conference was a great sophomore event to follow up last year’s inaugural conference. A lot of very smart people were there, and the hallway track was great.

I presented on three things: a lightning talk about causes of MySQL downtime; I chaired a panel on Big Data and the Cloud; and I showed how to derive scalability and performance metrics from TCP traffic. I’ve sent my slides to the Surge organizers, and I understand that they will be posting them as well as integrating them into the video of my session. In the meanwhile you can download my slides from Percona’s presentations page.

Further Reading:

• I’m speaking at Surge 2011
• Recap of CPOSC 2009, plus slides
• Book your Surge hotel now
• Speaking at Surge 2010
• Slides for my MySQL Conference talks are online

PlanetMySQL Voting: Vote UP / Vote DOWN

In 2007, Etsy made a big bet on homegrown middleware to help with the site’s scalability. A half-year after it was taken live, the company decided to abandon it. As a senior software engineer at Etsy put it, “if you’re doing something ‘clever,” you’re probably doing it wrong.”

Read the full article at Arstechnica.com

I want to focus on the important lessons from this article, about middleware and using stored procedures in this fashion for a public web application, creating unscalable design complexity (smart and “proper” according to the old enterprise design teachings…) – causing infrastructure, development and maintenance hassles.

In the process they did replace PostgreSQL with MySQL but that’s not the critical change that made all the difference. PostgreSQL is a fine database system also.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL 5.5 GA and MySQL 5.6 Development Milestone Releases have delivered many new compelling features to the MySQL users and community for testing, feedback and use.

In addition, commercial customers have access to a number of commercial extensions already included in MySQL Enterprise Edition:

•    MySQL Enterprise Monitor
•    MySQL Enterprise Backup

Continuing the business model of MySQL, we are adding three new commercial extensions to MySQL Enterprise Edition:

• MySQL Enterprise Scalability
• Thread Pool
• MySQL Enterprise High Availability
• Oracle VM Template for MySQL Enterprise Edition
• Windows Clustering for MySQL Enterprise Edition
• MySQL Enterprise Security
• External Authentication for PAM
• External Authentication for Windows

MySQL Enterprise Scalability: Thread Pool

To meet the sustained performance and scalability of ever increasing user, query and data loads MySQL Enterprise Edition provides thread pooling.  Thread Pool provides a highly scalable thread-handling model designed to reduce overhead in managing client connections and statement execution threads. The result is improved scalability and sustained performance for high-traffic online applications that service ever-growing numbers of client connections.  Thread Pool is a user configurable option that provides an efficient, alternate thread-handling model designed to fully exploit the processing power of today’s multi-core systems.  MySQL internal SysBench OLTP benchmarks show that the Thread Pool provides a significant improvement in sustained performance and scalability for applications that service a high number of concurrent connections, specifically on 16-core and higher systems.

Learn more and review the MySQL internal SysBench OLTP benchmarks.

MySQL Enterprise High Availability

MySQL Enterprise Edition offers a range of solutions for database high availability, to automatically detect and recover from failures, as well as minimize downtime resulting from scheduled maintenance activities.

Oracle VM Template for MySQL Enterprise Edition - Ensures rapid deployment and helps eliminate configuration efforts and risks by providing a pre-installed and pre-configured virtualized software image, taking advantage of Oracle VM’s features to deliver high availability.

Windows Server Failover Clustering for MySQL Enterprise Edition - With the certification and support of MySQL with Windows Server Failover Clustering (WSFC), organizations can safely deploy business-critical applications demanding high levels of availability, powered by MySQL Enterprise Edition and using native Windows clustering services.

Learn more about these new MySQL Enterprise Edition features.

MySQL Enterprise Security

MySQL 5.5 introduced a pluggable authentication API that allows users to be authenticated using external libraries, directories, etc.  Developers can use this API to build their own custom modules that integrate MySQL into an existing security infrastructure.  MySQL Enterprise Edition leverages this same API to provide ready to use external authentication modules that authenticate users via Pluggable Authentication Modules (“PAM”) or native Windows OS services.  Each is described below:

External Authentication for PAM - Enables you to configure MySQL to use PAM to authenticate users on LDAP, Unix/Linux, Kerberos, and other systems.

External Authentication for Windows – Enables you to configure MySQL to use native Windows services to authenticate client connections.  Users who have logged in to Windows can connect from MySQL client programs to the server based on the token information in their environment without specifying an additional password.

Learn more about these new MySQL Enterprise Edition features and the technical details of the MySQL 5.5 pluggable authentication API.


Download

Existing commercial customers who are entitled to a MySQL Enterprise Edition subscription can log into MyOracleSupport: and download these immediately.
For others who want to try these new capabilities, we will make them available shortly, via the 30 day
free trial of MySQL Enterprise Edition.

As always, thanks for your continued support of MySQL!

PlanetMySQL Voting: Vote UP / Vote DOWN