7 keys to better MySQL performance

As size and load grow, performance tends to slow. Keep these keys in mind to keep MySQL running smoothly

1 2 Page 2
Page 2 of 2

Some “experts” still advocate the use of spinning disks for cost or reliability reasons. Frankly, when it comes to operational databases, these arguments tend to be outdated or simply wrong. Today, SSDs offer impressive performance and reliability at a great price point.

Not all SSDs are created equal, however. For database servers, you should use an SSD that is designed for server workloads and that takes great care to protect your data (from corruption during power loss, for example). Avoid using commercial SSDs designed for consumer desktop computers and laptops.

An SSD directly attached through NVMe or Intel Optane technology offers the best performance. Even if attached remotely as a SAN, NAS, or cloud block device, SSDs still offer far superior performance compared to spinning disks.

MySQL optimization key #6: Scale out

Even the highest performance server has its limits. There are two ways to scale: up and out. Scaling up means buying more hardware. This can be expensive, and hardware gets obsolete quickly. Scaling out to handle more load has several benefits:

  1. You can take advantage of smaller and less costly systems.
  2. It’s much faster and easier to scale linearly by scaling out.
  3. Because the database is spread across more than one physical machine, the database is protected from a single point of hardware failure.

Although scaling out comes with advantages, it also has certain limitations. Scaling out requires replication, such as basic MySQL Replication or Percona XtraDB Cluster, for data synchronization. But in return you gain both additional performance and high availability. If you need even higher scale, use MySQL sharding.

You also need to make sure that applications connecting to the clustered architecture can find the needed data—usually through some proxy servers and load balancers such as ProxySQL or HAProxy.

While planning to scale out, avoid scaling out too early. Working with distributed databases tends to be more complicated. Modern hardware and MySQL servers can go quite far using just a single server. The recently available MySQL 8 release candidate has shown itself capable of handling more than 2 million simple queries on a single system.

MySQL optimization key #7: Embrace observability

The best-designed systems are designed with observability in mind—MySQL is no exception. 

Once you get your MySQL environment up, running, and properly tuned, you can’t just set it and forget it. Database environments can be fairly organic in that they are affected by changes to the system or workload. Be prepared for surprises such as traffic spikes, application bugs, and MySQL glitches. These things can and will happen.

When they do, you need to resolve them quickly and effectively. The only way to do this is to have some sort of monitoring solution set up and properly instrumented. This allows you to see what is happening in your database environment as it runs in production and to analyze server data when something goes wrong. Ideally the system allows you to prevent issues before they happen, or before they grow to the point that their impact becomes visible to the user.

Examples of monitoring tools are MySQL Enterprise Monitor, Monyog, and our own Percona Monitoring and Management (PMM), which has the additional advantage of being free and open source. These tools provide excellent operational visibility for monitoring and troubleshooting. 

As more companies turn to open source databases (especially MySQL) to manage and serve their business data in large-scale production environments, they’ll need to focus on keeping these databases tuned and running at optimum efficiency. As with all things crucial to your business goals, your database performance can make or break how your year turns out. MySQL is an excellent database solution for powering your applications and websites, but it needs to be tuned to match your needs and monitored to find and prevent bottlenecks and performance issues.

Peter Zaitsev is the co-founder and CEO of Percona, a provider of enterprise-class MySQL and MongoDB solutions and services. Co-author of “High Performance MySQL” published by O’Reilly, one of the most popular books on MySQL performance, Zaitsev blogs regularly at PerconaDatabasePerformanceBlog.com and speaks frequently at conferences worldwide.

New Tech Forum provides a venue to explore and discuss emerging enterprise technology in unprecedented depth and breadth. The selection is subjective, based on our pick of the technologies we believe to be important and of greatest interest to InfoWorld readers. InfoWorld does not accept marketing collateral for publication and reserves the right to edit all contributed content. Send all inquiries to newtechforum@infoworld.com.

1 2 Page 2
Page 2 of 2