How to Handle Seasonal Database Load

If your business relies on your database to support your mission-critical applications, you have probably experienced some of the pain of what happens when seasonal peaks hit. This happens for retail sites during the holiday shopping rush, or healthcare providers during open enrollment or for finance professionals doing end of year close or handling tax season. If unprepared, pages take too long to load, users can’t check out, replication lags, and downtime costs you money.

Imagine instead, a database that can keep pace with the changing needs of your business, where scaling out and scaling in are simple operations that don’t require extensive downtime or complicated application changes, all the while ensuring high availability and fault tolerance. This is where MariaDB Xpand shines. Xpand is purpose-built for scale, availability, and features built-in data redundancy.

MariaDB Xpand is a distributed SQL database that can help you scale your:

  • Number of reads
  • Number of writes
  • Number of transactions
  • Number of connections

All with a simple SQL interface. MariaDB Xpand automatically distributes data across a cluster of nodes (no sharding needed) and then compiles SQL statements into distributed programs that can be executed on any node. Xpand was designed with a distributed, shared-nothing architecture that is always transactional, fault tolerant, and provides both read and write scale (no read/write partitioning needed).

Xpand scaling image

 

Replication is a powerful tool for migrating your existing applications to Xpand, but also achieving higher heights. With Xpand parallel replication, the solution to replication lag is to just add nodes, to add additional capacity.

Now that it’s spring, it’s time to plan for your fall and winter seasonal peaks. You can leverage Xpand to stay online through your peaks, and sleep well, knowing that Xpand is fault tolerant and self-healing. With Xpand, you can maintain a more modest-sized cluster for your off-peak times, then for peaks, you can add capacity on the fly, while staying online.

One e-commerce user of Xpand was previously on MySQL, but was able to use Xpand to flex up to 14 database nodes (240 web nodes) in November. They estimated that during Cyber Monday, an hour of downtime could cost them more than $500k, and chose Xpand to guarantee they could meet customer demand. Deploying Xpand allowed them to serve:

  • 1M daily visitors
  • 4200 shopping carts per hour
  • 100k purchases per day

This allowed them to achieve a 3x increase of their peak workload, with zero downtime!

With Xpand, expanding your cluster is simple. To add nodes, simply install Xpand on additional nodes, then perform:

ALTER CLUSTER ADD NODE <list of ip addresses>;

And Voila! The nodes are online and part of the cluster, new nodes can serve traffic immediately. Slices of data are automatically redistributed, so nodes rapidly increase cluster capacity after being added.

Scaling in SkySQL is even simpler, with auto-scale or self service options for managing your service.

Once their seasonal peak was over in January, that e-commerce customer went back down to 6 nodes. The procedure for scaling in is simply:

ALTER CLUSTER SOFTFAIL nodeid [, nodeid] ...

Which directs the database to copy data off the nodes being removed. This happens in the  background, while the database stays online. Once the data is copied off those nodes, they can be safely removed with no data loss. This is a great way to minimize your cloud spend during off-peak.

Schedule for Planning:

Spring

Review the previous year’s traffic, and particular areas of pain. Come up with a plan on how to mitigate. Hint: Xpand can help!

Summer

Tune queries and optimize your application in preparation for growth. Work with your business to come up with projections on data and workload growth. Come up with a game plan to scale out.

Fall

Sit back, and relax. Add capacity as needed, simply by adding nodes. Traffic more than you thought? As an example, you can add 3 more nodes with zero downtime for a scale-out configuration of 12 nodes.

Winter

When traffic subsides after your peak season, scale back in. If you don’t need the full 12 nodes, you can scale in easily to 6 nodes. 

As you plan for a busy season, choosing MariaDB Xpand can prevent you from experiencing unexpected downtime during the critical peaks in your business. Be prepared for whatever scale you need, and experience the simplicity and high availability of a database built to handle high throughput. Xpand in SkySQL can help you with ease of deployment and automatic scaling.

 

To get started with Xpand distributed SQL: