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

July 18, 2024

As software development grows more intricate, the challenge for observability engineers tasked with ensuring optimal system performance becomes more daunting. Current methodologies are struggling to keep pace, with the annual Observability Pulse surveys indicating a rise in Mean Time to Remediation (MTTR). According to this survey, only a small fraction of organizations, around 10%, achieve full observability today. Generative AI, however, promises to significantly move the needle ...

July 17, 2024

While nearly all data leaders surveyed are building generative AI applications, most don't believe their data estate is actually prepared to support them, according to the State of Reliable AI report from Monte Carlo Data ...

July 16, 2024

Enterprises are putting a lot of effort into improving the digital employee experience (DEX), which has become essential to both improving organizational performance and attracting and retaining talented workers. But to date, most efforts to deliver outstanding DEX have focused on people working with laptops, PCs, or thin clients. Employees on the frontlines, using mobile devices to handle logistics ... have been largely overlooked ...

July 15, 2024

The average customer-facing incident takes nearly three hours to resolve (175 minutes) while the estimated cost of downtime is $4,537 per minute, meaning each incident can cost nearly $794,000, according to new research from PagerDuty ...

July 12, 2024

In MEAN TIME TO INSIGHT Episode 8, Shamus McGillicuddy, VP of Research, Network Infrastructure and Operations, at EMA discusses AutoCon with the conference founders Scott Robohn and Chris Grundemann ...

July 11, 2024

Numerous vendors and service providers have recently embraced the NaaS concept, yet there is still no industry consensus on its definition or the types of networks it involves. Furthermore, providers have varied in how they define the NaaS service delivery model. I conducted research for a new report, Network as a Service: Understanding the Cloud Consumption Model in Networking, to refine the concept of NaaS and reduce buyer confusion over what it is and how it can offer value ...

July 10, 2024

Containers are a common theme of wasted spend among organizations, according to the State of Cloud Costs 2024 report from Datadog. In fact, 83% of container costs were associated with idle resources ...

July 10, 2024

Companies prefer a mix of on-prem and cloud environments, according to the 2024 Global State of IT Automation Report from Stonebranch. In only one year, hybrid IT usage has doubled from 34% to 68% ...

July 09, 2024

At the forefront of this year's findings, from the Flexera 2024 State of ITAM Report, is the critical gap between software asset management (SAM) and FinOps (cloud financial management) teams. This year, 32% of SAM teams reported having significant interactions with FinOps teams. While this marks an improvement from last year's 25%, it highlights the persistent challenge of integrating these two essential functions ...

July 08, 2024

Information technology serves as the digital backbone for doctors, nurses, and technicians to deliver quality patient care by sharing data and applications over secure IT networks. To help understand the top IT trends that are impacting the healthcare industry today, Auvik recently released a companion analysis for its 2024 IT Trends Report ...