Storage
Mobility
Protection
Analysis and control
TOPICShare
Are you looking for consistently good performance for your MySQL database? Really, who is going to answer no to that question? With or without snapshots, whether you are accessing your primary database or a snapshot copy, you can expect excellent, consistent performance from MySQL with NetApp® Cloud Volumes Service.
Performance is of course not the whole story. Because data is a crown jewel of any enterprise, your database also needs the durability that Cloud Volumes Service offers. And as consumers increasingly demand protection against theft of their personal data, Cloud Volumes Service provides and manages the strong encryption that you need. Add to these benefits the advantage of accessing database volumes from any Amazon Web Services (AWS) Availability Zone within the region—without the need to replicate content. All together, these benefits make NetApp Cloud Volumes Service an optimal solution for your MySQL needs.
For details about how NetApp Cloud Volumes Service provides data durability, strong encryption, and high availability, as well as some compelling specifics about Cloud Volumes Service Snapshot technology, read our first blog post in this series.
Here, in our second blog post, we focus on MySQL performance.
When you evaluate database workloads, you should always keep in mind the impact of server memory. Ideally, queries find their data resident therein, because latency from memory is orders of magnitude lower than from any disk query. So, what does this latency aspect mean for you? Although storage latency matters, memory hit percentage matters more. When your database administrators say that they need a latency of X, keep in mind what they’re talking about. With Cloud Volumes Service, you can meet or even exceed their expectations.
But don’t just take our word for it. Our test results prove it.
To test MySQL with Cloud Volumes Service, we used the TPC Benchmark C (TPC-C) workload generator. TPC-C is an industry standard OLTP benchmark that leverages actual MySQL databases and their I/O paths. TPC-C standardized on an 80/20 read/write workload, so our test results in this section are based on load.
We tested two scenarios in this environment. In the first, we evaluated the capabilities of a single instance to drive MySQL I/O, and in the second, we set out to determine the edges of a single cloud volume. The following graph shows the results of both scenarios. The gray line represents the single-instance environment, and the blue line represents the multi-instance environment. The latency that’s shown in the graph represents storage latency as reported by the database.
When we ran TPC-C against the single instance, it generated approximately 4.5Gbps of I/O with Cloud Volumes Service, which approaches the Amazon inter–Virtual Private Cloud limit that’s imposed per network connection. When we ran TPC-C against multiple instances, it generated close to 16Gbps of throughput against a single cloud volume. This exceptional rate can help speed up your business and dramatically lower query wait time.
As a best practice, we used the following parameters in the MySQL /etc/my.cnf configuration file:
[MySQLd]
datadir=/opt/data/instance1
innodb_data_home_dir=/opt/instance1_log/
innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend:max:50G
socket=/opt/data/instance1/MySQL.sock
innodb_buffer_pool_size=23622320128
innodb_log_buffer_size=4294967295
innodb_log_file_size=1073741824
innodb_flush_log_at_trx_commit=2
innodb_open_files=4096
innodb_page_size=4096
innodb_read_io_threads=64
innodb_write_io_threads=64
performance_schema
innodb_doublewrite=0;
max_connections=1000
innodb_thread_concurrency=128
innodb_max_dirty_pages_pct=0
Start getting the high performance and durability that you need from your MySQL database. And make it easy by deploying NetApp Cloud Volumes Service. To get started, sign up for NetApp Cloud Volumes Service for AWS today.
NetApp is the data authority for hybrid cloud. We provide a full range of hybrid cloud data services that simplify management of data across cloud and on-premises environments to accelerate digital transformation. Together with our partners, we empower global organizations to unleash the full potential of their data to expand customer touchpoints, foster greater innovation and optimize operations. For more information, visit https://bluexp.netapp.com.
Performance is of course not the whole story. Because data is a crown jewel of any enterprise, your database also needs the durability that Cloud Volumes Service offers. And as consumers increasingly demand protection against theft of their personal data, Cloud Volumes Service provides and manages the strong encryption that you need. Add to these benefits the advantage of accessing database volumes from any Amazon Web Services (AWS) Availability Zone within the region—without the need to replicate content. All together, these benefits make NetApp Cloud Volumes Service an optimal solution for your MySQL needs.
For details about how NetApp Cloud Volumes Service provides data durability, strong encryption, and high availability, as well as some compelling specifics about Cloud Volumes Service Snapshot technology, read our first blog post in this series.
Here, in our second blog post, we focus on MySQL performance.
Benchmark Testing Proves Faster MySQL Performance
When you evaluate database workloads, you should always keep in mind the impact of server memory. Ideally, queries find their data resident therein, because latency from memory is orders of magnitude lower than from any disk query. So, what does this latency aspect mean for you? Although storage latency matters, memory hit percentage matters more. When your database administrators say that they need a latency of X, keep in mind what they’re talking about. With Cloud Volumes Service, you can meet or even exceed their expectations.
But don’t just take our word for it. Our test results prove it.
To test MySQL with Cloud Volumes Service, we used the TPC Benchmark C (TPC-C) workload generator. TPC-C is an industry standard OLTP benchmark that leverages actual MySQL databases and their I/O paths. TPC-C standardized on an 80/20 read/write workload, so our test results in this section are based on load.
We tested two scenarios in this environment. In the first, we evaluated the capabilities of a single instance to drive MySQL I/O, and in the second, we set out to determine the edges of a single cloud volume. The following graph shows the results of both scenarios. The gray line represents the single-instance environment, and the blue line represents the multi-instance environment. The latency that’s shown in the graph represents storage latency as reported by the database.
When we ran TPC-C against the single instance, it generated approximately 4.5Gbps of I/O with Cloud Volumes Service, which approaches the Amazon inter–Virtual Private Cloud limit that’s imposed per network connection. When we ran TPC-C against multiple instances, it generated close to 16Gbps of throughput against a single cloud volume. This exceptional rate can help speed up your business and dramatically lower query wait time.
Best Practices
As a best practice, we used the following parameters in the MySQL /etc/my.cnf configuration file:
[MySQLd]
datadir=/opt/data/instance1
innodb_data_home_dir=/opt/instance1_log/
innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend:max:50G
socket=/opt/data/instance1/MySQL.sock
innodb_buffer_pool_size=23622320128
innodb_log_buffer_size=4294967295
innodb_log_file_size=1073741824
innodb_flush_log_at_trx_commit=2
innodb_open_files=4096
innodb_page_size=4096
innodb_read_io_threads=64
innodb_write_io_threads=64
performance_schema
innodb_doublewrite=0;
max_connections=1000
innodb_thread_concurrency=128
innodb_max_dirty_pages_pct=0
Improve MySQL Performance Today
Start getting the high performance and durability that you need from your MySQL database. And make it easy by deploying NetApp Cloud Volumes Service. To get started, sign up for NetApp Cloud Volumes Service for AWS today.
About NetApp
NetApp is the data authority for hybrid cloud. We provide a full range of hybrid cloud data services that simplify management of data across cloud and on-premises environments to accelerate digital transformation. Together with our partners, we empower global organizations to unleash the full potential of their data to expand customer touchpoints, foster greater innovation and optimize operations. For more information, visit https://bluexp.netapp.com.