Data Infrastructure Modernization:
Should I Move to the Cloud?

Management Summary

When operating your application in an on-prem data centre, you may be tempted to go with the mainstream, abandon all custom infrastructure and make use of cloud computing. In this whitepaper, we demonstrate how the decision making for migration can be backed with data. Surprising to some, moving to the cloud is not always the best solution.

benchANT got addressed by one of their customers because of performance issues with their current MySQL 5 installation backing their Web shop. During peak load, the database experiences performance issues, particularly with respect to latency causing the Web shop to be laggy and leading to frustrated users.

Their DBMS is currently operated in their on-prem data center and our customer is responsible for managing both hardware and software of that installation. The application workload consists of three different workload types:

1. Read-heavy OLTP (main): Transactional reads from shop users, employees and automated queries of background tasks.
2. Bulk INSERTS: Updates of prices and products in automated and frequent bulk jobs.
3. Ad-hoc OLAP: Complex analytical queries performed by employees.

To find a solution for the performance issues, we conduct a measurement study addressing the three main questions Q1, Q2, and Q3.

Q1: Does upgrading to MySQL 8 promise any performance improvements for any of the 3 workloads?

• Performance measurements for MySQL 8 showed slight improvements for the OLAP workload regarding throughput and latency.
• No improvements could be measured for the OLTP and INSERT workloads. On the contrary, MySQL 8 performed worse than MySQL 5 in these scenarios.
• These results were consistent across the on-prem infrastructure, AWS EC2 virtual machines, and IONOS virtual machines.

Q2: Can performance be improved by using modern Cloud IaaS or DBaaS?

• For comparable sized virtual machines on AWS and IONOS cloud the results show no clear indication to move to the cloud.
• While the results on AWS EC2 show significantly better throughput for bulk inserts and analytics workloads, the improvements for the main OLTP workload are neglectable. Throughput at IONOS cloud is only better for OLAP workloads.
• The Database-as-a-Service product AWS RDS for MySQL shows the worst performance for all cloud solutions.

Q3: Can an alternative database like PostgreSQL improve performance?

For all workload scenarios PostgreSQL 13 shows significantly better performance than MySQL 5 and MySQL 8.

Step 1: Objectives and Workload

When starting an evaluation project like this, the client needs to clarify two main things:

• The objective of the evaluation regarding goals, effort, restrictions and KPIs.
• The application workload which is used for the testing process.

Objectives

• Throughput: Our client’s mail goal to achieve with this infrastructure modernization is to handle more data and a more intense workload in the future.
• Latency: Current complaints about read and write latencies by users and shop visitors need to be solved during the modernization.

A European cloud provider could be a solution, but also AWS as the world’s leading cloud provider should be considered in the evaluation.

Note: The cost perspective is usually also a main objective for cloud migrations and data infrastructure modernizations. As we compare three different kinds of data infrastructure solutions – on-prem vs. cloud vs. DBaaS – a cost evaluation is very complex. The results of this cost evaluation are non-publishable for this case. If you are interested in a Total Cost of Ownership (TCO) analysis with a reliable price/performance analysis, please reach out.

Workload

For data-driven evaluations the starting point is very important. For performance measurements targeting infrastructure and databases, the workload of the IT application using the infrastructure and database is key. Understanding the workloads’ main characteristics and re-modelling them is the most important, but also most complex part of such an evaluation.

To ease the evaluation and allow faster project completion time, benchANT uses synthetic data and established benchmarks for comparing different set-ups. Yet, for getting meaningful, individual results tailored towards the use cases, the right benchmark suites need to be chosen first. Secondly, the benchmark suites need to be configured and mapped to the application main characteristics. For this scenario, we were using and configuring the following workloads:

• Bulk inserts are represented by the LOAD phase of the Yahoo! Cloud Serving Benchmark (YCSB).
• Traditional OLTP workload is represented by the TPC-C benchmark.
• Analytical OLAP workload is represented by the TPC-H benchmark.

The intensity of the workloads is approximated by analysing the real workload on the production servers of the customer. This is a part where experience and knowledge of the most important triggers are very helpful.

Step 2: Resource Selection and Infrastructure Configuration

The second step is to define the benchmark scenarios by fleshing out details with respect to:

• Compute resources
• Storage types and sizing
• Database version and configurations

For on-prem scenarios, we make use of a clone of the production server. The DBMS instance running there has 16 cores and 128 GB of memory available and uses NVMe SSD storage. As cloud providers, we selected AWS representing American hyperscalers and IONOS as a representative of a GDPR-compliant German cloud provider.

For the IaaS scenarios, we choose the following set-ups:

• For AWS, we use virtual machines with the i4i.4xlarge flavor. These are similar to the on-prem server and run with 16 cores and 128 GB RAM. Further, they contain NVMe storage.
• For IONOS, we use a virtual machine with an Intel Skylake processor architecture, 16 cores and 128 GB RAM. For storage, we use the premium SSD category.

In all on-prem and IaaS scenarios evaluating MySQL, we use hand-tuned MySQL configurations provided by our customer. The DBaaS scenario is also run on AWS using RDS MySQL with flavor type db.r5.4xlarge (16 vCPUs and 128 GB RAM) and GP2 storage. In this case, no custom configurations are used.

Regarding the alternative DBMS technology, we select PostgreSQL 13 as the main open-source competitor to MySQL. We run it on the IONOS IaaS and do not use any custom or tuned configuration.

Benchmarking Scenarios

Overall, we run eight configurations for each of the three defined workload types, resulting in 24 benchmarking scenarios. For each scenario three independent runs are executed to gain statistically valid results. All three runs are aggregated to a result.

Step 3: Result Analysis

In the third step of the evaluation process the results, measured by the benchANT performance benchmarking framework, are analysed with the client to discover the potential of the several options.

Objective: MySQL 5 vs MySQL 8 on Different Infrastructures

The first objective is to compare the performance impact of newer MySQL versions on the workloads, but also to evaluate if modern cloud infrastructure can have a big impact on the performance.

1. Bulk Insert Workload

As stated above, this workload is simulated using the Yahoo! Cloud Serving Benchmark (YCSB). More precisely, using the load phase of YCSB.

Throughput results for the load phase provide two core insights.

• For all three set-ups (on-prem, IONOS, AWS) there is no indication that MySQL 8 provides any significant performance benefits compared to MySQL 5, rather the opposite: for AWS and the on-prem case, we see roughly 30% higher performance for MySQL 5, for IONOS, it is 14%.
• Regarding the performance of the three set-ups, AWS achieves 2.5x-2.7x higher throughput than the on-prem set-up for both MySQL versions, while the on-prem set-up has 1.4x-1.7x more throughput than IONOS.

Regarding write latency, results are comparable to throughput.

2. Read-heavy OLTP Workload

This workload is simulated by the well-known TPC-C benchmark.

For the TPC-C workload, results are a lot less spread than for YCSB. In particular, the on-prem and AWS throughput for MySQL 5 are almost identical (only 2% difference). Throughput of the on-prem MySQL 8 case is only 2.6% lower compared to the on-prem MySQL 5 case.

MySQL 8 on AWS provides the best throughput for this kind of workload. Yet, it needs to be stated that there is a huge spread in the throughput in the three benchmark runs performed for MySQL 8 on AWS: while two of the three runs yield an average throughput of ~1000 ops/s, the third run achieves ~2000 ops/s. Only considering the two less performant runs puts MySQL 8 on AWS in the same range as MySQL 8 in the on-prem case.

In all cases, the IONOS set-ups perform worst. Here, the capabilities are 14% (MySQL 5) and 44% (MySQL 8) worse than for the on-prem case.

As with the write latency case, latency results are comparable to throughput results.

3. Analytical OLAP Workload

This workload is simulated by the well-known TPC-H benchmark.

The results for the OLAP workload are significantly different than for the other two cases. Here, a clear dominance of MySQL 8 over MySQL 5 is visible for both cloud cases (IONOS and AWS). While AWS is still superior to IONOS, the difference between MySQL 8 for IONOS and MySQL 8 for AWS is far less than the difference to MySQL 5 on IONOS and AWS respectively.

Surprisingly, the on-prem set-up cannot compete with any of the other set-ups no matter if MySQL 5 or MySQL 8 is used. In the on-prem case, MySQL 8 performs slightly better than MySQL 5.

Regarding latency, the first the places of the ranking are identical to the throughput case. MySQL 8 in the on-prem set-up comes in fourth place followed by IONOS and on-prem with MySQL 5. As with throughput, there is a huge difference between the performance of the first two places and the remaining four.

Objective: Database-as-a-Service Options

The second objective was also to evaluate if modern Database-as-a-Service products could be a performant option. Therefore, the former MySQL 8 results are compared to AWS RDS for MySQL for all three workloads.

1. Bulk Insert Workload

For the write heavy workload we can state that the AWS EC2 configuration with MySQL 8 shows the best results by far. The DBaaS offerings (RDS) is last and a factor of 10 slower than the AWS EC2 set-up; it is a factor of four slower than the on-prem solution.

2. Read-heavy OLTP Workload

For the OLTP workload, the DBaaS service also provides the worst throughput compared to the other set-ups. AWS EC2 is still best, but with less difference to the on-prem case. As with the insert workload, MySQL 8 running on EC2 provides more than 200% more throughput compared to the RDS case.

The results are also mirrored for the latency case.

3. Analytical OLAP Workload

For the OLAP workload, RDS results are also worse than the others, but compared to the insert workload and the OLTP workload, RDS is only 13% and 21% off with respect to throughout and latency respectively.

Objective: PostgreSQL, a Database Technology Alternative

In the following, we compare the performance of Postgres 13 to MySQL 5 and MySQL 8. All three configurations have been run only on IONOS cloud. In all cases PostgreSQL clearly outperforms MySQL. There is no reason why similar effects should not be visible on other infrastructure as well.

1. Bulk Insert Workload

For the write heavy workload, PostgreSQL shows a massive throughput increase from 6,000-7,000 operations per second for MySQL to more than 140,000 operations per second. In parallel the write latency drops to 0.1 milliseconds, which is an improvement factor of 35 and 50 respectively.

2. Read-heavy OLTP Workload

For the TPC-C workload, the throughput of PostgreSQL is roughly 1.7 times the performance of MySQL 5 and 2.3 times of MySQL 8. The latency data mirror this result.

3. Analytical OLAP Workload

The OLAP case does not show much difference to OLTP and write-heavy workload. PostgreSQL performance is a lot – roughly eight times – higher than MySQL 8 performance and almost 36 times higher than MySQL 5.

Conclusion

Overall, the on-prem set-up is indeed comparable to cloud-hosted solutions for the primary OLTP scenario that was evaluated by using the TPC-C benchmark. Regarding pure write performance measured with YCSB, the on-prem set-up is outperformed by all competitors. This is also the case for the analytics case.

In none of the workload scenarios, the use of modern Database as a Service (DBaaS) offerings could beat the performance of the on-prem set-up, except for the OLAP workload.

No measurable performance improvements could be found when going from MySQL 5 to MySQL 8. For transparency, it should be added that for MySQL 5 a tweaked configuration file was used. No effort was carried out tweaking the configuration for MySQL 8.

In all cases PostgreSQL in its vanilla configuration outperformed both MySQL 5 and MySQL 8. The differences are massive in some scenarios.

Recommendations and way ahead

The data as shown in this whitepaper guides the decisions of our client in several ways.

1. First, our customer would not necessarily benefit from migrating to a cloud-hosted set-up. Not only would such a move cause migration costs and bring up new operational costs that are not existent now, but it would also not add any operational benefit nor increased performance.
2. Further, operating a self-managed DBMS on cloud infrastructure would not free up any additional resources. This might be different when using DBaaS as the entire DBMS management could be outsourced.
3. The data also suggests that a migration to MySQL 8 is currently not necessary, and that some performance drawbacks are to be expected when such an update needs to be performed for some other reason (new features, availability of patches, etc.).
4. The data suggests that the first thing to do should be a separation of the analytical workload from the transactional workload, mainly as the current set-up of MySQL 5 seems to not be well-suited for analytical workloads and the situation will be worse when both types of workloads run in parallel in the production set-up.
5. The best, but also most complex option to get an immense performance gain would be a database migration to PostgreSQL. The measurement data show clear performance increases when it comes to throughput and latency for all three workload cases.

Disclaimer

All measurements were performed with the scientifically approved automated benchmarking suite of benchANT. This article was created with meticulous care and accuracy. Please notify us for any mistakes and filthiness. If you are interested in the raw data and audit information please get in touch.