Power the Database that Powers the Internet
Not only is Oracle MySQL the most popular open-source relational database for web workloads, but it also powers many online transactional processing (OLTP) workloads. While it is relatively simple to implement and deploy, achieving the highest performance and best business results requires the power of HGST-brand and SanDisk®-brand flash technologies.
Oracle MySQL, and its offspring MariaDB® and Percona Server™, power the Internet by providing simple and performant open source database solutions. You can scale their performance by using SanDisk- and HGST-brand SSDs.
OLTP and Web Processing
OLTP uses a database to securely record transactions for an application. For example, purchases from a web shopping cart or keycard scans for a secure site are processed using OLTP. These workloads vary in intensity, but as a rule, they are defined by their high business value and need for an absolute guarantee of their recording. In many cases, there is only a single database of record; so to avoid a bottleneck your entire business transaction set needs to be serialized quickly and safely to storage at high speed.
By contrast, web processing is often all about scale and speed. For that reason, database administrators use the technique of sharding. This breaks an individual database into partitions, or multiple, smaller, faster, and more manageable databases (known as shards) across a plethora of independent servers, while still logically using all servers as a single database. Business applications at a higher level combine the output of these multiple databases to answer business questions and support web properties. In this case, storage capacity and form factor are critical, as many servers need to be configured with the same type of storage.
MySQL, MariaDB, Percona Server, and Others
MySQL has spawned multiple “forks,” essentially forming the basis for additional databases such as MariaDB, Percona Server, and others. While each database has its own performance characteristics and ecosystem niche, they all share the need for careful attention to be paid to storage, so it can perform at the highest potential.
Business benefits of using an open source database like MySQL or MariaDB are self-evident: individual servers can be set up quickly and integrated into larger scale-out clusters when needed. Also, by tapping into the open-source ecosystem, companies that use MySQL have access to a pool of talented people with extensive experience building and scaling such systems.
Customers use MySQL for many aspects of their business, both transactional and web-scale. Sharding, as described previously, can be applied to MySQL installations to grow the capacity and performance of the database. However, with that comes the potential for massive server sprawl and performance problems as data managed under each server grows.
Scaling Up MySQL Painlessly
The simplest way to speed up a single MySQL instance is to scale up that instance by applying more processing power or storage. Careful examination of system load can help pinpoint the bottleneck, with a focus on CPU utilization and I/O wait times. Often a simple top command can be used to identify the cause.
Pain Point: Speeding up MySQL without Sharding
If queries are slow but MySQL server CPUs are idling, then MySQL is most likely suffering from high I/O wait times. The simplest way to speed up operations is to increase the underlying storage performance. For example, industry-standard SATA interface SSDs, such as SanDisk-branded CloudSpeed™ drives, can be used as drop-in replacements to boost I/O bandwidth and reduce latency. For the ultimate in MySQL performance you can also use NVM Express™ (NVMe™)-based SSDs. The NVMe-based SkyHawk™ SSD from the SanDisk brand, which is directly connected to the CPU via PCIe interface, minimizes I/O latency while providing multiple gigabytes per second of bandwidth for MySQL databases.
Pain Point: Backups and Analytics Degrade MySQL Performance
The additional performance afforded by SSDs in this application can have benefits beyond simply speeding up existing transactions. For the ultimate in single-server MySQL performance under mixed workloads, the HGST-brand Ultrastar® SN200 and SN260 SSDs provide the ultimate in NVMe SSD performance. They can allow your business to do more with the same servers. For example, I/O-intensive processes such as online backups can be done more regularly and with minimal impact on real-time transactions. Analytics, which are often run on a separate server with an older copy of the database, can be handled on the main MySQL instance in many cases.
Scaling Out MySQL Efficiently
In modern web businesses, there is often no choice but to scale out MySQL to achieve performance or resiliency, or to handle sheer dataset size. In this case, sharding is used to break the logical database into multiple physical instances. Each instance can then run in parallel on its own dataset, allowing the processing load to be spread over many more CPU cores and disks.
Scale-out is a lower-cost way of solving database performance issues, but it can lead to server sprawl. While adding individual servers to increase performance was initially a boon, it can become a bane as networking, maintenance, colocation, power, and cooling costs eat into the savings.
Pain Point: Sharding and Server Sprawl
Keeping scale-out from turning into server sprawl requires high-capacity, high-performance storage to maximize individual MySQL shard performance in the minimum space. The HGST-brand Ultrastar SS200 is a 2.5” SFF SAS SSD with a capacity of over 7 terabytes (TB) and full support for industry standard RAID controllers. This large-capacity drive allows 1-U or even ½-U servers to support large, high-performance datasets. As an added benefit, SSDs have no moving parts, so maintenance costs and downtime may be reduced, increasing the savings.
MySQL databases are critical business components with performance that directly impacts the bottom line. You can scale up single-instance performance with HGST or SanDisk-brand SSDs. This delivers a pain-free speed increase to users, potentially expanding the database capabilities. In cases where a single instance of MySQL isn’t enough, and sharding is necessary, using these SSDs enables you to extract the maximum performance from the minimum number of servers, thereby avoiding server sprawl.
|SanDisk SSD||HGST SSD||SanDisk SSD||HGST SSD|
|Pain Point||CloudSpeed™ SATA||Ultrastar® SS2200 SAS||SkyHawk™ NVMe||Ultrastar® SN200 NvMe|
|Speeding up MySQL without sharding||★ ★ ★||★ ★||★ ★ ★|
|Backups and analytics degrade MySQL performance||★||★ ★||★ ★ ★|
|Sharding without server sprawl||★ ★ ★||★ ★ ★|
|Legend: ★ Good ★★ Better ★★★ Best|