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
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.
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/TRUNCATE table. I’ll show a couple of examples about how bad filesystem configurations might cause system troubles.
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!
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?
Yoshinori Matsunobu is a database and infrastructure architect at DeNA (http://www.dena.jp/en/index.html), living in Tokyo. Yoshinori’s primary responsibility at DeNA is to make our database infrastructure more reliable, faster and more scalable. Before joining DeNA, Yoshinori worked at MySQL/Sun/Oracle as a lead consultant in APAC for four years. Yoshinori has written eight MySQL related technical books so far and has published technical articles about MySQL, Linux, and Java for a monthly database magazine since 2004.
Comments on this page are now closed.
For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at firstname.lastname@example.org
For media partnerships, contact mediapartners@ oreilly.com
For media-related inquiries, contact Maureen Jennings at email@example.com
To stay abreast of conference news and to receive email notification when registration opens, please sign up for the O'Reilly MySQL Conference Bulletin (login required).
View a complete list of O'Reilly MySQL Conference Contacts