Click here to close now.


Linux Containers Authors: Anders Wallgren, XebiaLabs Blog, Pat Romanski, 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

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 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 and IBM's DeveloperWorks Grid Computing site; is a core member of the 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
SYS-CON Events announced today that IBM Cloud Data Services has been named “Bronze Sponsor” of SYS-CON's 17th Cloud Expo, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. IBM Cloud Data Services offers a portfolio of integrated, best-of-breed cloud data services for developers focused on mobile computing and analytics use cases.
In his session at @ThingsExpo, Tony Shan, Chief Architect at CTS, will explore the synergy of Big Data and IoT. First he will take a closer look at the Internet of Things and Big Data individually, in terms of what, which, why, where, when, who, how and how much. Then he will explore the relationship between IoT and Big Data. Specifically, he will drill down to how the 4Vs aspects intersect with IoT: Volume, Variety, Velocity and Value. In turn, Tony will analyze how the key components of IoT influence Big Data: Device, Connectivity, Context, and Intelligence. He will dive deep to the matrix...
When it comes to IoT in the enterprise, namely the commercial building and hospitality markets, a benefit not getting the attention it deserves is energy efficiency, and IoT’s direct impact on a cleaner, greener environment when installed in smart buildings. Until now clean technology was offered piecemeal and led with point solutions that require significant systems integration to orchestrate and deploy. There didn't exist a 'top down' approach that can manage and monitor the way a Smart Building actually breathes - immediately flagging overheating in a closet or over cooling in unoccupied ho...
Scott Guthrie's keynote presentation "Journey to the intelligent cloud" is a must view video. This is from AzureCon 2015, September 29, 2015 I have reproduced some screen shots in case you are unable to view this long video for one reason or another. One of the highlights is 3 datacenters coming on line in India.
“The Internet of Things transforms the way organizations leverage machine data and gain insights from it,” noted Splunk’s CTO Snehal Antani, as Splunk announced accelerated momentum in Industrial Data and the IoT. The trend is driven by Splunk’s continued investment in its products and partner ecosystem as well as the creativity of customers and the flexibility to deploy Splunk IoT solutions as software, cloud services or in a hybrid environment. Customers are using Splunk® solutions to collect and correlate data from control systems, sensors, mobile devices and IT systems for a variety of Ind...
SYS-CON Events announced today that ProfitBricks, the provider of painless cloud infrastructure, 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. ProfitBricks is the IaaS provider that offers a painless cloud experience for all IT users, with no learning curve. ProfitBricks boasts flexible cloud servers and networking, an integrated Data Center Designer tool for visual control over the cloud and the best price/performance value available. ProfitBricks was named one of the coolest Clo...
You have your devices and your data, but what about the rest of your Internet of Things story? Two popular classes of technologies that nicely handle the Big Data analytics for Internet of Things are Apache Hadoop and NoSQL. Hadoop is designed for parallelizing analytical work across many servers and is ideal for the massive data volumes you create with IoT devices. NoSQL databases such as Apache HBase are ideal for storing and retrieving IoT data as “time series data.”
Clearly the way forward is to move to cloud be it bare metal, VMs or containers. One aspect of the current public clouds that is slowing this cloud migration is cloud lock-in. Every cloud vendor is trying to make it very difficult to move out once a customer has chosen their cloud. In his session at 17th Cloud Expo, Naveen Nimmu, CEO of Clouber, Inc., will advocate that making the inter-cloud migration as simple as changing airlines would help the entire industry to quickly adopt the cloud without worrying about any lock-in fears. In fact by having standard APIs for IaaS would help PaaS expl...
Organizations already struggle with the simple collection of data resulting from the proliferation of IoT, lacking the right infrastructure to manage it. They can't only rely on the cloud to collect and utilize this data because many applications still require dedicated infrastructure for security, redundancy, performance, etc. In his session at 17th Cloud Expo, Emil Sayegh, CEO of Codero Hosting, will discuss how in order to resolve the inherent issues, companies need to combine dedicated and cloud solutions through hybrid hosting – a sustainable solution for the data required to manage I...
Apps and devices shouldn't stop working when there's limited or no network connectivity. Learn how to bring data stored in a cloud database to the edge of the network (and back again) whenever an Internet connection is available. In his session at 17th Cloud Expo, Bradley Holt, Developer Advocate at IBM Cloud Data Services, will demonstrate techniques for replicating cloud databases with devices in order to build offline-first mobile or Internet of Things (IoT) apps that can provide a better, faster user experience, both offline and online. The focus of this talk will be on IBM Cloudant, Apa...
Mobile messaging has been a popular communication channel for more than 20 years. Finnish engineer Matti Makkonen invented the idea for SMS (Short Message Service) in 1984, making his vision a reality on December 3, 1992 by sending the first message ("Happy Christmas") from a PC to a cell phone. Since then, the technology has evolved immensely, from both a technology standpoint, and in our everyday uses for it. Originally used for person-to-person (P2P) communication, i.e., Sally sends a text message to Betty – mobile messaging now offers tremendous value to businesses for customer and empl...
As more and more data is generated from a variety of connected devices, the need to get insights from this data and predict future behavior and trends is increasingly essential for businesses. Real-time stream processing is needed in a variety of different industries such as Manufacturing, Oil and Gas, Automobile, Finance, Online Retail, Smart Grids, and Healthcare. Azure Stream Analytics is a fully managed distributed stream computation service that provides low latency, scalable processing of streaming data in the cloud with an enterprise grade SLA. It features built-in integration with Azur...
SYS-CON Events announced today that HPM Networks will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. For 20 years, HPM Networks has been integrating technology solutions that solve complex business challenges. HPM Networks has designed solutions for both SMB and enterprise customers throughout the San Francisco Bay Area.
The enterprise is being consumerized, and the consumer is being enterprised. Moore's Law does not matter anymore, the future belongs to business virtualization powered by invisible service architecture, powered by hyperscale and hyperconvergence, and facilitated by vertical streaming and horizontal scaling and consolidation. Both buyers and sellers want instant results, and from paperwork to paperless to mindless is the ultimate goal for any seamless transaction. The sweetest sweet spot in innovation is automation. The most painful pain point for any business is the mismatch between supplies a...
The broad selection of hardware, the rapid evolution of operating systems and the time-to-market for mobile apps has been so rapid that new challenges for developers and engineers arise every day. Security, testing, hosting, and other metrics have to be considered through the process. In his session at Big Data Expo, Walter Maguire, Chief Field Technologist, HP Big Data Group, at Hewlett-Packard, will discuss the challenges faced by developers and a composite Big Data applications builder, focusing on how to help solve the problems that developers are continuously battling.
As enterprises capture more and more data of all types – structured, semi-structured, and unstructured – data discovery requirements for business intelligence (BI), Big Data, and predictive analytics initiatives grow more complex. A company’s ability to become data-driven and compete on analytics depends on the speed with which it can provision their analytics applications with all relevant information. The task of finding data has traditionally resided with IT, but now organizations increasingly turn towards data source discovery tools to find the right data, in context, for business users, d...
SYS-CON Events announced today that MobiDev, a software development company, will exhibit at the 17th International Cloud Expo®, which will take place November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. MobiDev is a software development company with representative offices in Atlanta (US), Sheffield (UK) and Würzburg (Germany); and development centers in Ukraine. Since 2009 it has grown from a small group of passionate engineers and business managers to a full-scale mobile software company with over 150 developers, designers, quality assurance engineers, project manage...
Learn how IoT, cloud, social networks and last but not least, humans, can be integrated into a seamless integration of cooperative organisms both cybernetic and biological. This has been enabled by recent advances in IoT device capabilities, messaging frameworks, presence and collaboration services, where devices can share information and make independent and human assisted decisions based upon social status from other entities. In his session at @ThingsExpo, Michael Heydt, founder of Seamless Thingies, will discuss and demonstrate how devices and humans can be integrated from a simple clust...
SYS-CON Events announced today that Cloud Raxak has been named “Media & Session Sponsor” of SYS-CON's 17th Cloud Expo, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Raxak Protect automates security compliance across private and public clouds. Using the SaaS tool or managed service, developers can deploy cloud apps quickly, cost-effectively, and without error.
Who are you? How do you introduce yourself? Do you use a name, or do you greet a friend by the last four digits of his social security number? Assuming you don’t, why are we content to associate our identity with 10 random digits assigned by our phone company? Identity is an issue that affects everyone, but as individuals we don’t spend a lot of time thinking about it. In his session at @ThingsExpo, Ben Klang, Founder & President of Mojo Lingo, will discuss the impact of technology on identity. Should we federate, or not? How should identity be secured? Who owns the identity? How is identity ...