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
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
and Memory for MySQL. I’ll show you interesting benchmarks.
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.
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
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
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 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.
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.
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?