Linux and H/W optimizations for MySQL

Choosing right Hardware components, configuring them properly, and optimizing Linux settings are very important for MySQL and other database server deployments. But they are frequently overlooked. I have seen many cases that H/W and Linux settings were wrongly configured on production environemnts. Changing configurations after going live is not easy. Some Linux settings such as I/O scheduler can be changed dynamically, but many of the settings can not be changed without stopping MySQL. It is important for infrastructure engineers to understand Linux and H/W tuning and monitoring best practices. Learning best practices will certainly improve performance and stability. This tutorial will mainly cover the following topics and answer your questions.

1. Choosing and optimizing H/W

• Storage(SSD/HDD), RAID, and Memory

  There are many options for selecting storage devices. Is it ok to use HDD and large enough memory? Or is it better to buy 8 Intel SATA SSDs then build Hardware RAID5? Or is it better to use FusionIO or other PCI-E SSDs? Or should I use large SAN/NAS drives? Answers depend on uncertain things such as future pricing or capacity improvements, but there are certainly good practices about how to choose and deploy SSD/HDD and Memory for MySQL. I’ll show you interesting benchmarks.

• Network

  Are you still using 100Mbps network? Then you’d better migrate to 1Gbps. 10Gbps is not so helpful in many cases. Using multiple NIC ports or using NIC that supports multiple Tx/Rx queues might help in some cases. I’ll talk these topics based on fields experiences and various kinds of MySQL and other NoSQL benchmarks.

• CPU

  Choosing proper CPU is not easy. This highly depends on workloads, memory capacity, and storages. When MySQL server is heavily HDD i/o bound, CPU does not matter so much. But if you start using PCI-Express SSDs or most of active data fit in memory, CPU matters significantly. Clock speed and the number of CPU cores do not explain everything. Have you done any benchmarking between Nehalem and Opteron? How PCI-Express SSDs’ performance are affected by CPUs? I’ll cover these interesting topics.

2. Linux configuration best practices for MySQL

• Filesystem

  Should we use ext4? xfs, or others? For faster storages like PCI-E SSDs, filesystem really matters. Filesystem also matters for database operations, such as DROP/TRUNCATE table. I’ll show a couple of examples about how bad filesystem configurations might cause system troubles.

• Swap and memory management

  In many cases people create only 2GB or 4GB swap space, or some do not create any swap space. But this is dangerous. On the other hand, allocating large swap space causes excessive swap in/out when kernel configuration is wrong. I’ll show proper configurations.

• Network tuning

  Network configurations sometimes cause significant performance impacts. When you use non-persistent connections, you are likely to suffer from waiting-for-three-seconds-for-SYN-retry issues. I’ll talk how we can mitigate problems like this.

• I/O scheduler settings

  Default cfq scheduler is bad for MySQL, use noop or deadline. Do you understand the reason? Join this tutorial!

• Practical performance monitoring tools and tips

  Linux can do lots of things. We can do “tracing packets that take more than 2000 milliseconds to respond” without modifying anything on application side. We can get per-file disk i/o statistics, too. I’ll introduce some useful tools that we use on our production environments.

• Virtualization

  Recently H/W specs get stronger. You might want to run more than one small MySQL instances within single box. How mature is Linux virtualization technology? Does it perform well on SSDs? If virtualization is not mature, it it better to run multiple MySQL instances within single OS?