Why MySQL Fails to Scale - and What E-commerce Merchants Can Do About It
November 13, 2015

Mike Azevedo
Clustrix

Share this

Tremendous temporary surges in Web purchase activity are a fact of life in retail. If prognosticators are right, e-retailers may be under more pressure to keep their sites up and running this holiday season; eMarketer predicts that 2015 online holiday spending rates will increase 13.9 percent.

And while some of these spikes can easily be predicted (e.g., Black Friday, Cyber Monday and Diwali), steep short-term online sales fluctuations are increasingly common throughout the year as a result of flash promotions, seasonal events and the like.

Unfortunately, e-commerce sites can fail to handle a highly concentrated number of transactions. According to BI and Statista, site crashes and slow checkouts will cost businesses $1 trillion in lost sales this year.

The relational database underlying the e-commerce site is often the bottleneck. Relational databases are built for online transactions (OLTP). They provide the technical foundation to ensure transactions are completed properly and can also roll back the transaction, if interrupted. MySQL, for example is a popular choice for e-commerce sites but can struggle to handle the high shopping volume large e-tailers see during holiday and other seasonal peak periods.

What's needed is a relational database designed to scale to handle very large transactional workloads. In particular, there is a need to scale writes and not just reads. As cart conversion rate or volume goes up, scaling writes becomes especially important.

Yesterday's Methods for Scaling Present Great Difficulties

It's not that it is entirely impossible to scale a traditional relational database like MySQL. However, each of the common workarounds database administrators (DBAs) employ comes with significant complexity, hassle and/or monetary costs.

Here are some of the most commonly used methods and the headaches that come with it:

Scale-up: Adding more powerful servers is a straightforward solution, but one that will set you back financially and will only take you so far. Once you're using the biggest box available on the Cloud, you need to start using purpose-built hardware, which often costs many times more for incrementally greater performance.

Sharding: Sharding is the process of dividing the data along a specific application boundary among multiple database instances (e.g., dividing user names by their alphabetical order, with last names starting with A through H, I through Q and R through Z going on different database instances). Sharding requires a deep understanding of the application, careful planning and detailed integration execution, as well as a thorough alignment between the partition scheme, database schema and types of queries that are made. The application almost always has to be modified and the application layer becomes responsible for ACID (Atomicity, Consistency, Isolation, Durability) compliance requirements. As traffic grows, sharded databases become more fragile and expensive to maintain. And, it can significantly increase the number of single points of failure, which will lead to failures that result in lost revenues and angry customers. (Oh, and by the way, it also costs a fortune in CapEx and OpEx outlays to support large expensive servers for the primary and backup systems.)

Read-only slaves: This tactic of replicating a master relational database to a series of slave databases works to scale reads but not writes. More frequent writes means numerous updates/mirrors to the read slaves, which increases latency. The read-slave approach also results in the master serving as a single point of failure, which means DBAs could be on the hook to promote a slave to master during an outage. If that slave is not completely in-synch with the master, you risk losing critical data.

Apparent Alternatives

NoSQL Databases: Switching from a relational to a non-relational database is a radical and ill-advised alternative. Financial transactions are not appropriate for NoSQL databases. Non-relational, or “NoSQL,” databases have the advantage of huge scale but here's the tradeoff: NoSQL databases achieve such high scale by abandoning the requirements to structure the data and to ensure reliability for transactions. NoSQL databases by design are not ACID compliant and/or don't support complex JOINs or referential integrity, and thus are not appropriate for OLTP workloads. ACID transactions are important for purchases and other critical e-commerce activities.

Real Alternatives

DBAs need not be forced to pick their poison and effectively choose whether to dedicate additional time, money and/or energy to maintain the database's performance as traffic soars. They can, instead, move off of MySQL to a more modern RDBMS suited to their fast growing transactional workloads.

A checklist of RDBMS features required for large-scale applications would include:

■ Horizontal scale - ability to scale with the addition of commodity hardware

■ Simplicity - easy to manage, add and remove capacity. No deep customization or partitioning expertise needed. Easy migration from an existing MySQL database.

■ Compatibility - works with existing applications without rewriting queries.

■ Reliability - no single point of failure.

■ Elasticity - can add and remove capacity with the addition of commodity resources. Allocates available resources without manual intervention.

■ Cost effective - reduce labor costs, efficiently use commodity resources and allow temporary increases and decreases in capacity to respond to changing workloads without paying for overcapacity.

A new generation of RDBMS — sometimes called scale-out, distributed RDBMS — deliver on these requirements. Designed from the ground up to achieve these goals, such a solution allows e-tailers to scale easily, reliably and with confidence. Moving from MySQL to a scale-out database is a smart move for growing retailers.

Mike Azevedo is CEO of Clustrix.

Share this

The Latest

January 26, 2023

As enterprises work to implement or improve their observability practices, tool sprawl is a very real phenomenon ... Tool sprawl can and does happen all across the organization. In this post, though, we'll focus specifically on how and why observability efforts often result in tool sprawl, some of the possible negative consequences of that sprawl, and we'll offer some advice on how to reduce or even avoid sprawl ...

January 25, 2023

As companies generate more data across their network footprints, they need network observability tools to help find meaning in that data for better decision-making and problem solving. It seems many companies believe that adding more tools leads to better and faster insights ... And yet, observability tools aren't meeting many companies' needs. In fact, adding more tools introduces new challenges ...

January 24, 2023

Driven by the need to create scalable, faster, and more agile systems, businesses are adopting cloud native approaches. But cloud native environments also come with an explosion of data and complexity that makes it harder for businesses to detect and remediate issues before everything comes to a screeching halt. Observability, if done right, can make it easier to mitigate these challenges and remediate incidents before they become major customer-impacting problems ...

January 23, 2023

The spiraling cost of energy is forcing public cloud providers to raise their prices significantly. A recent report by Canalys predicted that public cloud prices will jump by around 20% in the US and more than 30% in Europe in 2023. These steep price increases will test the conventional wisdom that moving to the cloud is a cheap computing alternative ...

January 19, 2023

Despite strong interest over the past decade, the actual investment in DX has been recent. While 100% of enterprises are now engaged with DX in some way, most (77%) have begun their DX journey within the past two years. And most are early stage, with a fourth (24%) at the discussion stage and half (49%) currently transforming. Only 27% say they have finished their DX efforts ...

January 18, 2023

While most thought that distraction and motivation would be the main contributors to low productivity in a work-from-home environment, many organizations discovered that it was gaps in their IT systems that created some of the most significant challenges ...

January 17, 2023
The US aviation sector was struggling to return to normal following a nationwide ground stop imposed by Federal Aviation Administration (FAA) early Wednesday over a computer issue ...
January 13, 2023

APMdigest and leading IT research firm Enterprise Management Associates (EMA) are teaming up on the EMA-APMdigest Podcast, a new podcast focused on the latest technologies impacting IT Operations. In Episode 1, Dan Twing, President and COO of EMA, discusses Observability and Automation with Will Schoeppner, Research Director covering Application Performance Management and Business Intelligence at EMA ...

January 12, 2023

APMdigest is following up our list of 2023 Application Performance Management Predictions with predictions from industry experts about how the cloud will evolve in 2023 ...

January 11, 2023

As demand for digital services increases and distributed systems become more complex, organizations must collect and process a growing amount of observability data (logs, metrics, and traces). Site reliability engineers (SREs), developers, and security engineers use observability data to learn how their applications and environments are performing so they can successfully respond to issues and mitigate risk ...