Click here to close now.

Welcome!

Linux Containers Authors: Carmen Gonzalez, Pat Romanski, Wayne Lam, Elizabeth White, Liz McMillan

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 17th International Cloud Expo has announced that its Call for Papers is open. 17th International Cloud Expo, to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, APM, APIs, Microservices, Security, Big Data, Internet of Things, DevOps 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 your speaking proposal today!
The basic integration architecture, as defined by ESBs, hasn’t changed for more than a decade. Most cloud integration providers still rely on an ESB architecture and their proprietary connectors. As a result, enterprise integration projects suffer from constraints of availability and reliability of these connectors that are not re-usable across other integration vendors. However, the rapid adoption of APIs and almost ubiquitous availability of APIs amongst most SaaS and Cloud applications are rapidly redefining traditional integration approaches and their reliance on proprietary connectors. ...
SYS-CON Events announced today that Secure Infrastructure & Services will exhibit at SYS-CON's 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Secure Infrastructure & Services (SIAS) is a managed services provider of cloud computing solutions for the IBM Power Systems market. The company helps mid-market firms built on IBM hardware platforms to deploy new levels of reliable and cost-effective computing and high availability solutions, leveraging the cloud and the benefits of Infrastructure-as-a-Service (IaaS...
The 5th International DevOps Summit, co-located with 17th International Cloud Expo – being held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the world's largest enterprises – and delivering real results. Among the proven benefits, DevOps is corr...
17th Cloud Expo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, 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 form of XaaS – software, platform, and infrastructure as a service.
It is one thing to build single industrial IoT applications, but what will it take to build the Smart Cities and truly society-changing applications of the future? The technology won’t be the problem, it will be the number of parties that need to work together and be aligned in their motivation to succeed. In his session at @ThingsExpo, Jason Mondanaro, Director, Product Management at Metanga, discussed how you can plan to cooperate, partner, and form lasting all-star teams to change the world and it starts with business models and monetization strategies.
Today air travel is a minefield of delays, hassles and customer disappointment. Airlines struggle to revitalize the experience. GE and M2Mi will demonstrate practical examples of how IoT solutions are helping airlines bring back personalization, reduce trip time and improve reliability. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Dr. Sarah Cooper, M2Mi’s VP Business Development and Engineering, will explore the IoT cloud-based platform technologies driving this change including privacy controls, data transparency and integration of real time context wi...
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Architect for the Internet of Things and Intelligent Systems, described how to revolutionize your archit...
WebRTC converts the entire network into a ubiquitous communications cloud thereby connecting anytime, anywhere through any point. In his session at WebRTC Summit,, Mark Castleman, EIR at Bell Labs and Head of Future X Labs, will discuss how the transformational nature of communications is achieved through the democratizing force of WebRTC. WebRTC is doing for voice what HTML did for web content.
The Internet of Things is not only adding billions of sensors and billions of terabytes to the Internet. It is also forcing a fundamental change in the way we envision Information Technology. For the first time, more data is being created by devices at the edge of the Internet rather than from centralized systems. What does this mean for today's IT professional? In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists addressed this very serious issue of profound change in the industry.
Internet of Things (IoT) will be a hybrid ecosystem of diverse devices and sensors collaborating with operational and enterprise systems to create the next big application. In their session at @ThingsExpo, Bramh Gupta, founder and CEO of robomq.io, and Fred Yatzeck, principal architect leading product development at robomq.io, discussed how choosing the right middleware and integration strategy from the get-go will enable IoT solution developers to adapt and grow with the industry, while at the same time reduce Time to Market (TTM) by using plug and play capabilities offered by a robust IoT ...
"We have a tagline - "Power in the API Economy." What that means is everything that is built in applications and connected applications is done through APIs," explained Roberto Medrano, Executive Vice President at Akana, in this SYS-CON.tv interview at 16th Cloud Expo, held June 9-11, 2015, at the Javits Center in New York City.
Internet of Things is moving from being a hype to a reality. Experts estimate that internet connected cars will grow to 152 million, while over 100 million internet connected wireless light bulbs and lamps will be operational by 2020. These and many other intriguing statistics highlight the importance of Internet powered devices and how market penetration is going to multiply many times over in the next few years.
To many people, IoT is a buzzword whose value is not understood. Many people think IoT is all about wearables and home automation. In his session at @ThingsExpo, Mike Kavis, Vice President & Principal Cloud Architect at Cloud Technology Partners, discussed some incredible game-changing use cases and how they are transforming industries like agriculture, manufacturing, health care, and smart cities. He will discuss cool technologies like smart dust, robotics, smart labels, and much more. Prepare to be blown away with a glimpse of the future.
SYS-CON Events announced today that BMC will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. BMC delivers software solutions that help IT transform digital enterprises for the ultimate competitive business advantage. BMC has worked with thousands of leading companies to create and deliver powerful IT management services. From mainframe to cloud to mobile, BMC pairs high-speed digital innovation with robust IT industrialization – allowing customers to provide amazing user experiences with optimized IT per...
There will be 150 billion connected devices by 2020. New digital businesses have already disrupted value chains across every industry. APIs are at the center of the digital business. You need to understand what assets you have that can be exposed digitally, what their digital value chain is, and how to create an effective business model around that value chain to compete in this economy. No enterprise can be complacent and not engage in the digital economy. Learn how to be the disruptor and not the disruptee.
The Internet of Things is not only adding billions of sensors and billions of terabytes to the Internet. It is also forcing a fundamental change in the way we envision Information Technology. For the first time, more data is being created by devices at the edge of the Internet rather than from centralized systems. What does this mean for today's IT professional? In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists will addresses this very serious issue of profound change in the industry.
Business as usual for IT is evolving into a "Make or Buy" decision on a service-by-service conversation with input from the LOBs. How does your organization move forward with cloud? In his general session at 16th Cloud Expo, Paul Maravei, Regional Sales Manager, Hybrid Cloud and Managed Services at Cisco, discusses how Cisco and its partners offer a market-leading portfolio and ecosystem of cloud infrastructure and application services that allow you to uniquely and securely combine cloud business applications and services across multiple cloud delivery models.
In his General Session at 16th Cloud Expo, David Shacochis, host of The Hybrid IT Files podcast and Vice President at CenturyLink, investigated three key trends of the “gigabit economy" though the story of a Fortune 500 communications company in transformation. Narrating how multi-modal hybrid IT, service automation, and agile delivery all intersect, he will cover the role of storytelling and empathy in achieving strategic alignment between the enterprise and its information technology.
Buzzword alert: Microservices and IoT at a DevOps conference? What could possibly go wrong? In this Power Panel at DevOps Summit, moderated by Jason Bloomberg, the leading expert on architecting agility for the enterprise and president of Intellyx, panelists peeled away the buzz and discuss the important architectural principles behind implementing IoT solutions for the enterprise. As remote IoT devices and sensors become increasingly intelligent, they become part of our distributed cloud environment, and we must architect and code accordingly. At the very least, you'll have no problem fillin...