Linux and H/W optimizations for MySQL

Tutorial
Location: Ballroom D
Average rating: ****.
(4.60, 15 ratings)

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?

Photo of Yoshinori Matsunobu

Yoshinori Matsunobu

DeNA

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.

Comments

Gergely Hodicska
05/29/2011 7:13am PDT

Thank you very much putting so much work into this presentation, the slides were very amazing and helpful!

Picture of Mark Callaghan
Mark Callaghan
04/15/2011 10:51pm PDT

The slides are amazing.

Philip Turner
04/13/2011 10:42am PDT

Awesome to see all the data backing up his findings.

John Schulz
04/11/2011 7:34pm PDT

I have attended this conference every year since 2004 except for last year, and I think this was the most useful conference session I have ever attended.

  • EnterpriseDB
  • Amazon Web Services
  • Clustrix
  • Continuent
  • Facebook
  • HTI Consultoria e Tecnologia
  • Monty Program
  • Percona
  • Rackspace Hosting
  • Schooner Information Technology
  • SkySQL
  • Xeround

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at yromaine@oreilly.com

Media Partners Opportunities

For media partnerships, contact mediapartners@ oreilly.com

Press & Media

For media-related inquiries, contact Maureen Jennings at maureen@oreilly.com

O'Reilly MySQL Conference Bulletin

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).

Contact Us

View a complete list of O'Reilly MySQL Conference Contacts