Welcome!

Linux Containers Authors: Elizabeth White, Liz McMillan, Pat Romanski, Yeshim Deniz, Flint Brenton

Related Topics: Linux Containers

Linux Containers: Article

Book Rookery: High Performance MySQL

Sharing lessons learned over time

In this installment of the Book Rookery, High Performance MySQL authors Jeremy Zawodny and Derek J. Balling share some of the MySQL lessons they've learned over the years and offer insight into the performance gains possible when you use the techniques covered in the book.

What kinds of applications do people use MySQL for? Is it really free?

People are using MySQL in all types of applications, both personal and corporate, from "quickie one-off applications" to helping power Sabre's ticketing reservation system.

For almost all users, it really is free, since it is released under the GPL. There has been some confusion about licensing issues, namely commercial versus noncommercial use and what constitutes "redistribution." Their Web site likes to make the analogy "If you make money, you need a license," which is simply not the case, since the GPL'ed code is free for use in both commercial and noncommercial applications. The only time it would not be free is if you intend to redistribute code based on the MySQL code, and don't intend to release your own code as open source. In that case, you would need to purchase a commercial license from MySQL AB, to permit you to redistribute binary-only versions of your derivative work. For almost all Web-based applications, though, that restriction is moot since you almost never redistribute your code to an outside party.

And, in reality, most companies will buy a support agreement for the software, which helps to ensure they get help when needed and MySQL AB is able to stay in business.

Can you tell us more about the intention of the book and what's covered inside?

Jeremy first conceived the book when he was encountering growth problems while deploying MySQL at Yahoo. They weren't necessarily "MySQL problems," per se, but they were gotchas and best-practice configurations that hadn't really been documented anywhere (or at least not very well). Our book was conceived as a place to write down the lessons learned over time, and try to put them all in one place so that future administrators didn't have to scour the Web, or mailing lists, or even worse, try to pick through the source code, in order to find the answer to their problem.

We cover a wide range of topics, starting from baseline decisions like "what storage engine should I use for my data," and progressing further into query optimization, hardware configurations, replication, and load balancing. We also touch on, because it's important, the security and backup situations that a large installation will encounter.

How much of a performance increase do you think you can make through using the techniques outlined in your book?

That depends a great deal on what you're starting out with in the first place. If you've got a fairly well-designed database, on decent hardware, maybe you don't see much improvement at all. On the other hand, if you're like many installations where MySQL made its inroads "through the back door," and there's not been a lot of formal DBA experience in the organization, it's possible the optimizations we discuss can give you performance increases of several thousand percent.

If you could only tweak one system within MySQL to get the best performance, what would you tweak?

The key_buffer setting for the MyISAM storage engine. Once you set up the correct indexes, MySQL needs sufficient memory to keep the most actively used indexes cached in memory. For primarily InnoDB users, the answer is the innodb_buffer_pool for very similar reasons.

You must have experienced lots of different databases of information over the years - what was your favorite use for a database system?

That's a tough one. I (Jeremy) really like the aviation database that Jeremy Cole (MySQL AB's training manager) has been building. He's combined freely available information from the FAA and NTSB with MySQL and a simple Web interface in a way that brings together previously separate and hard-to-find information. If you want to know which airports or airlines experience the most delays, it'll tell you. If you want to know what types of planes American Eagle flies, it'll tell you.

What is the relative performance using the various underlying database technologies with MySQL (InnoDB vs ISAM, etc.)?

That's going to depend a great deal on the type of data you have, how you access it (write once read often? write often read seldom?), and how complex your needs are (for example, do you need transactions, or are you just simply using the database as your Apache server log?).

Do you believe that MySQL can compete with more commercial systems, like SQL Server?

Over time, yes. MySQL doesn't have all the bells and whistles that SQL Serve, Oracle, or DB2 have. But what it lacks in features it generally makes up for in simplicity and raw performance, both of which often translate into cost savings.

As times goes on, MySQL will get closer and closer to the "big name" databases.

How much of a performance hit is there in using a remote database versus a local one via sockets?

If the "remote" database is fairly close, on a network-scale the performance impact is pretty marginal (a few milliseconds). Most of the bottlenecks in MySQL (or any database, really) are in disk accesses - how quickly can the database engine find the right spot on the disk to give you the information you asked for. Almost everything you optimize in a database server centers around speeding up that basic act. The added time for accessing a database via TCP/IP instead of via a Unix socket, is fairly negligible. If you have the server truly "remote" (like, the other side of the country), then you might have issues with network latency.

What is your favorite cartoon daily?

Derek says: Depending on my mood, either "Dilbert" or "Doonesbury"... I guess it just depends on which is frustrating me more lately, work or politics.

Jeremy says: "Dilbert" or "The Far Side," which is sadly no longer being written.

About the Authors
Jeremy Zawodny is Yahoo's resident MySQL Geek and the lead author of High Performance MySQL. He lives in San Jose, California and flies gliders around Northern California and Nevada in his spare time. He also maintains a Weblog at http://jeremy.zawodny.com/blog.

Derek J. Balling has been a Perl programmer and Unix/Linux system administrator since 1996, having helped build two different ISPs from the ground up in the midwestern United States. He spent several years of his career at Yahoo!, working in their Infrastructure Group, where he worked on tools to help improve system uptime. He presently works at a healthcare supply company, helping infiltrate the open source virus into their infrastructure.

More Stories By Martin C. Brown

Martin C. Brown is a former IT director with experience in cross-platform integration. A keen developer, he has produced dynamic sites for blue-chip customers, including HP and Oracle, and is the technical director of Foodware.net. Now a freelance writer and consultant, MC, as he is better known, works closely with Microsoft as an SME; has a regular column on both ServerWatch.com and IBM's DeveloperWorks Grid Computing site; is a core member of the AnswerSquad.com team; and has written books such as XML Processing with Perl, Python and PHP, and the Microsoft IIS 6 Delta Guide.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
The essence of data analysis involves setting up data pipelines that consist of several operations that are chained together – starting from data collection, data quality checks, data integration, data analysis and data visualization (including the setting up of interaction paths in that visualization). In our opinion, the challenges stem from the technology diversity at each stage of the data pipeline as well as the lack of process around the analysis.
SYS-CON Events announced today that Commvault, a global leader in enterprise data protection and information management, has been named “Bronze Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Commvault is a leading provider of data protection and information management...
Designing IoT applications is complex, but deploying them in a scalable fashion is even more complex. A scalable, API first IaaS cloud is a good start, but in order to understand the various components specific to deploying IoT applications, one needs to understand the architecture of these applications and figure out how to scale these components independently. In his session at @ThingsExpo, Nara Rajagopalan is CEO of Accelerite, will discuss the fundamental architecture of IoT applications, ...
SYS-CON Events announced today that Tintri Inc., a leading producer of VM-aware storage (VAS) for virtualization and cloud environments, will exhibit at the 18th International CloudExpo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York and Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty ...
Companies can harness IoT and predictive analytics to sustain business continuity; predict and manage site performance during emergencies; minimize expensive reactive maintenance; and forecast equipment and maintenance budgets and expenditures. Providing cost-effective, uninterrupted service is challenging, particularly for organizations with geographically dispersed operations.
As cloud and storage projections continue to rise, the number of organizations moving to the cloud is escalating and it is clear cloud storage is here to stay. However, is it secure? Data is the lifeblood for government entities, countries, cloud service providers and enterprises alike and losing or exposing that data can have disastrous results. There are new concepts for data storage on the horizon that will deliver secure solutions for storing and moving sensitive data around the world. ...
18th Cloud Expo, taking place June 7-9, 2016, at the Javits Center in New York City, NY, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Meanwhile, 94% of enterprises are using some...
SYS-CON Events announced today that MangoApps will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. MangoApps provides modern company intranets and team collaboration software, allowing workers to stay connected and productive from anywhere in the world and from any device. For more information, please visit https://www.mangoapps.com/.
SoftLayer operates a global cloud infrastructure platform built for Internet scale. With a global footprint of data centers and network points of presence, SoftLayer provides infrastructure as a service to leading-edge customers ranging from Web startups to global enterprises. SoftLayer's modular architecture, full-featured API, and sophisticated automation provide unparalleled performance and control. Its flexible unified platform seamlessly spans physical and virtual devices linked via a world...
In his keynote at 18th Cloud Expo, Andrew Keys, Co-Founder of ConsenSys Enterprise, will provide an overview of the evolution of the Internet and the Database and the future of their combination – the Blockchain. Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life ...
In his session at 18th Cloud Expo, Bruce Swann, Senior Product Marketing Manager at Adobe, will discuss how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects). Bruce Swann has more than 15 years of experience working with digital marketing disciplines like web analytics, social med...
SYS-CON Events announced today that BMC Software has been named "Siver Sponsor" of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. BMC is a global leader in innovative software solutions that help businesses transform into digital enterprises for the ultimate competitive advantage. BMC Digital Enterprise Management is a set of innovative IT solutions designed to make digital business fast, seamless, and optimized from mainframe to mo...
SYS-CON Events announced today that EastBanc Technologies will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. EastBanc Technologies has been working at the frontier of technology since 1999. Today, the firm provides full-lifecycle software development delivering flexible technology solutions that seamlessly integrate with existing systems – whether on premise or cloud. EastBanc Technologies partners with p...
Join us at Cloud Expo | @ThingsExpo 2016 – June 7-9 at the Javits Center in New York City and November 1-3 at the Santa Clara Convention Center in Santa Clara, CA – and deliver your unique message in a way that is striking and unforgettable by taking advantage of SYS-CON's unmatched high-impact, result-driven event / media packages.
SYS-CON Events announced today that ContentMX, the marketing technology and services company with a singular mission to increase engagement and drive more conversations for enterprise, channel and SMB technology marketers, has been named “Sponsor & Exhibitor Lounge Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York. “CloudExpo is a great opportunity to start a conversation with new prospects, but what happens after the...
The 19th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Containers, Microservices and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit y...
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 19th International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world and ThingsExpo New York Call for Papers is now open.
The IoTs will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, will demonstrate how to move beyond today's coding paradigm and share the must-have mindsets for removing complexity from the development proc...
The IoT is changing the way enterprises conduct business. In his session at @ThingsExpo, Eric Hoffman, Vice President at EastBanc Technologies, discuss how businesses can gain an edge over competitors by empowering consumers to take control through IoT. We'll cite examples such as a Washington, D.C.-based sports club that leveraged IoT and the cloud to develop a comprehensive booking system. He'll also highlight how IoT can revitalize and restore outdated business models, making them profitable...