How to Scale MySQL and Leverage New Apps

The pace of new application development and the rate of enhancements to build intelligent applications is increasing rapidly thanks to ChatGPT, GitHub-Copilot, OpenAI Codex, and more. The user adoption, popularity, and growth of these enterprise applications result in stretching the limits of backend traditional database infrastructure for MySQL & Postgres. It Impacts the application performance, resulting in longer wait times for business user queries and transactions. Performance scaling of the database infrastructure at a lower cost is extremely critical. For business applications’ performance, reliability, scalability, and security continue to be fundamental design components to improve customer experience and satisfaction. So how can enterprises maintain these design principles while keeping up with evolving requirements with new functionality to develop and deployment of intelligent applications?  More importantly, It must be accomplished while still maintaining lower deployment and operational costs. The answer lies in architecting database infrastructure with the right technology stack.

 

A Database Storage Foundation Where Your Applications Can Thrive

At Pliops we have developed a modern data services platform that is designed to accommodate the needs of applications to constantly evolve. Let’s take a look at how to configure and realize the benefits of Pliops Extreme Data Processor (XDP) for MySQL database infrastructure.  A testing environment is configured with MySQL primary (Master) and replica (Slave) on identical Dell PowerEdge R7525 servers.  The servers are set up with AMD EPYC 7543 32-Core processor and 512GB of Memory and storage is configured with traditional SW RAID 10 supported by four Gen4 Samsung PM9A3 NVMe 3.84TB SSDs. This storage setup ensures both data protection and higher performance. Software RAID 10 tolerates storage level failures and MySQL replica tolerates failure at the server level.  The second pair of MySQL primary and replica servers are configured with identical server setup however for the data storage layer, Pliops XDP is configured with the same number of SSDs.

 

The global transaction identifiers (GTIDs) replication method is employed over the Binary log since it is transactional and does not require working with log files or positions within these files. It also greatly simplifies many common replication tasks and guarantees consistency between the source and replica if all transactions committed on the source have also been applied to the replica.

MySQL Instance Scaling:  2 MySQL vs 4 MySQL Instances

With the traditional deployment model using RAID10, we could set up only two MySQL database instances with a 1.8TB dataset each. However, the modern data storage platform configuration with Pliops XDP enabled to configure of four MySQL Instances with identical 1.8TB database size due to built-in data capacity expansion benefits. Pliops XDP setup enabled to double the number of MySQL DB instances.

Innodb buffer pool for the traditional model is configured with 128GB per instance and the Pliops setup buffer pool is reduced by half, which is 64GB per instance to support 4 MySQL

Figure 2: MySQL Instance scaling with Pliops XDP

As shown in Figure 2, Sysbench TPC-C is used to evaluate the scalability and performance benefits of the 2 MySQL instances with Traditional setup and 4 instances with Pliops for identical system configuration.  Packing or scaling 2X more database instances looks good, but does it improve the MySQL performance per instance with this new approach?  It’s precisely the same question a couple of our customers asked when we have shown the above scalable setup diagram before walking through performance details.

Performance Acceleration & Scaling:

Figure 3: Performance acceleration and Scaling

We executed Sysbench TPC-C with an identical number of 128 threads per MySQL instance for 60 minutes duration after the stabilization period of 120 minutes. The goal was to ensure capturing the results with a steady state that is minimum performance deviation. What we witnessed as shown in Figure 3 is that there was a 44% increase in TPM per MySQL instance for Pliops XDP setup over the traditional RAID 10 setup and 2.9X higher TPM for an aggregate of 4 instances with Pliops setup over the traditional setup. We concluded that Pliops XDP in-line compression enables to scale of more MySQL Instances but also per each MySQL instance, there is an increase in performance and massive 2.9X performance gains. The key reason Pliops XDP setup drives higher performance is due to the 4X I/O acceleration provided to MySQL instances at the data storage layer.

DBAs and database architects are tasked with database consolidation with minimal or no performance loss for supported applications and can now look into Pliops XDP deployment to accomplish organizational goals.

2X User Scaling and Latency Reduction:

Figure 4: Performance acceleration and Scaling

As shown in figure 4, serving 2X the number of users with 43% latency reduction is another significant benefit based on Sysbench testing with Pliops XDP.  Another key criterion for database consolidation is to ensure the response time of the user is not compromised. Typically, DBAs use resource management to prioritize and serve critical business applications first and lower priority applications with a compromise on QoS. When all applications need priority access, DBAs are left with no choice but to serve each application on a dedicated server infrastructure raising capital and operational expenses. Deploying Pliops XDP for enterprises can accomplish database consolidation goals with no compromise on application quality of service.

Cost Reduction and HW Refresh Cycles

In these current economic conditions, Enterprises will be challenged with maintaining low TCO and high ROI of technology solutions and innovating with the same to fewer resources. While many organizations are also scrambling to modernize to meet today’s customer user experience needs and ensure their applications are highly responsive.  Pliops XDP deployment as data storage for MySQL applications provides a 2.6X reduction in $/Transaction and a 72% reduction in $/Terabyte to meet TCO and data growth challenges.

 

Key Advantages of Pliops Optimized Setup:

To summarize the benefits of Pliops XDP as modern data storage for MySQL Applications:

  • Performance Scaling: Database operations teams can minimize the complexity of adding and managing many MySQL server nodes to server performance demands. Pliops XDP setup delivers higher TPM to minimize the need for adding additional nodes.

 

  • Serve 2X Users with High QoS:  Serve a higher number of databases and users in the same hardware footprint with no compromise on application SLAs.

 

  • Higher Database Consolidation Density:  Achieve a higher DB consolidation ratio and serve priority business applications with no compromise on performance, capacity, or latency.

 

  • Significant TCO Reduction:  Massive reduction in Capex and OpEx and achieve environmental sustainability goals.

 

  • Resilient to Failures and Continuously Available: Critical MySQL application services remain available during storage failures (SSDs). For server failures, and/or data center failures, organizations can continue to leverage existing Primary-Replica and DR setups.

 

The Path Forward – Reducing Costs While Advancing Applications

 

In these current economic conditions, enterprises will be challenged with maintaining low TCO and high ROI of technology solutions while innovating to build intelligent applications with the same to fewer resources. When these intelligent applications’ user adoption picks up many organizations are scrambling to modernize to meet today’s customer user experience needs and ensure their applications are highly responsive.

 

Pliops XDP allows enterprises to build and scale intelligent applications to meet response times and increase customer experience with MySQL. Pliops XDP deployment as data storage for MySQL applications provides a 2.6X reduction in $/Transaction and a 72% reduction in $/Terabyte to meet TCO and application response challenges.

 

Share

Category

Talk to a Product Expert!

Speak with a data expert to learn how Pliops XDP can exponentially increase your business needs.