Welcome!

Linux Authors: Carmen Gonzalez, Pat Romanski, Victoria Livschitz, Elizabeth White, Ignacio M. Llorente

Related Topics: Linux

Linux: 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 3rd International Internet of @ThingsExpo, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that its Call for Papers is now open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.
An entirely new security model is needed for the Internet of Things, or is it? Can we save some old and tested controls for this new and different environment? In his session at @ThingsExpo, New York's at the Javits Center, Davi Ottenheimer, EMC Senior Director of Trust, reviewed hands-on lessons with IoT devices and reveal a new risk balance you might not expect. Davi Ottenheimer, EMC Senior Director of Trust, has more than nineteen years' experience managing global security operations and assessments, including a decade of leading incident response and digital forensics. He is co-author of t...
The Internet of Things will greatly expand the opportunities for data collection and new business models driven off of that data. In her session at @ThingsExpo, Esmeralda Swartz, CMO of MetraTech, discussed how for this to be effective you not only need to have infrastructure and operational models capable of utilizing this new phenomenon, but increasingly service providers will need to convince a skeptical public to participate. Get ready to show them the money!
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges. In his session at @ThingsExpo, Jeff Kaplan, Managing Director of THINKstrategies, will examine why IT must finally fulfill its role in support of its SBUs or face a new round of...
One of the biggest challenges when developing connected devices is identifying user value and delivering it through successful user experiences. In his session at Internet of @ThingsExpo, Mike Kuniavsky, Principal Scientist, Innovation Services at PARC, described an IoT-specific approach to user experience design that combines approaches from interaction design, industrial design and service design to create experiences that go beyond simple connected gadgets to create lasting, multi-device experiences grounded in people's real needs and desires.
Enthusiasm for the Internet of Things has reached an all-time high. In 2013 alone, venture capitalists spent more than $1 billion dollars investing in the IoT space. With "smart" appliances and devices, IoT covers wearable smart devices, cloud services to hardware companies. Nest, a Google company, detects temperatures inside homes and automatically adjusts it by tracking its user's habit. These technologies are quickly developing and with it come challenges such as bridging infrastructure gaps, abiding by privacy concerns and making the concept a reality. These challenges can't be addressed w...
The Domain Name Service (DNS) is one of the most important components in networking infrastructure, enabling users and services to access applications by translating URLs (names) into IP addresses (numbers). Because every icon and URL and all embedded content on a website requires a DNS lookup loading complex sites necessitates hundreds of DNS queries. In addition, as more internet-enabled ‘Things' get connected, people will rely on DNS to name and find their fridges, toasters and toilets. According to a recent IDG Research Services Survey this rate of traffic will only grow. What's driving t...
Connected devices and the Internet of Things are getting significant momentum in 2014. In his session at Internet of @ThingsExpo, Jim Hunter, Chief Scientist & Technology Evangelist at Greenwave Systems, examined three key elements that together will drive mass adoption of the IoT before the end of 2015. The first element is the recent advent of robust open source protocols (like AllJoyn and WebRTC) that facilitate M2M communication. The second is broad availability of flexible, cost-effective storage designed to handle the massive surge in back-end data in a world where timely analytics is e...
Scott Jenson leads a project called The Physical Web within the Chrome team at Google. Project members are working to take the scalability and openness of the web and use it to talk to the exponentially exploding range of smart devices. Nearly every company today working on the IoT comes up with the same basic solution: use my server and you'll be fine. But if we really believe there will be trillions of these devices, that just can't scale. We need a system that is open a scalable and by using the URL as a basic building block, we open this up and get the same resilience that the web enjoys.
We are reaching the end of the beginning with WebRTC, and real systems using this technology have begun to appear. One challenge that faces every WebRTC deployment (in some form or another) is identity management. For example, if you have an existing service – possibly built on a variety of different PaaS/SaaS offerings – and you want to add real-time communications you are faced with a challenge relating to user management, authentication, authorization, and validation. Service providers will want to use their existing identities, but these will have credentials already that are (hopefully) i...
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
How do APIs and IoT relate? The answer is not as simple as merely adding an API on top of a dumb device, but rather about understanding the architectural patterns for implementing an IoT fabric. There are typically two or three trends: Exposing the device to a management framework Exposing that management framework to a business centric logic Exposing that business layer and data to end users. This last trend is the IoT stack, which involves a new shift in the separation of what stuff happens, where data lives and where the interface lies. For instance, it's a mix of architectural styles ...
Cultural, regulatory, environmental, political and economic (CREPE) conditions over the past decade are creating cross-industry solution spaces that require processes and technologies from both the Internet of Things (IoT), and Data Management and Analytics (DMA). These solution spaces are evolving into Sensor Analytics Ecosystems (SAE) that represent significant new opportunities for organizations of all types. Public Utilities throughout the world, providing electricity, natural gas and water, are pursuing SmartGrid initiatives that represent one of the more mature examples of SAE. We have s...
P2P RTC will impact the landscape of communications, shifting from traditional telephony style communications models to OTT (Over-The-Top) cloud assisted & PaaS (Platform as a Service) communication services. The P2P shift will impact many areas of our lives, from mobile communication, human interactive web services, RTC and telephony infrastructure, user federation, security and privacy implications, business costs, and scalability. In his session at @ThingsExpo, Robin Raymond, Chief Architect at Hookflash, will walk through the shifting landscape of traditional telephone and voice services ...
The Internet of Things is tied together with a thin strand that is known as time. Coincidentally, at the core of nearly all data analytics is a timestamp. When working with time series data there are a few core principles that everyone should consider, especially across datasets where time is the common boundary. In his session at Internet of @ThingsExpo, Jim Scott, Director of Enterprise Strategy & Architecture at MapR Technologies, discussed single-value, geo-spatial, and log time series data. By focusing on enterprise applications and the data center, he will use OpenTSDB as an example t...
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 Internet of @ThingsExpo, James Kirkland, Chief Architect for the Internet of Things and Intelligent Systems at Red Hat, described how to revolutioniz...
Bit6 today issued a challenge to the technology community implementing Web Real Time Communication (WebRTC). To leap beyond WebRTC’s significant limitations and fully leverage its underlying value to accelerate innovation, application developers need to consider the entire communications ecosystem.
The definition of IoT is not new, in fact it’s been around for over a decade. What has changed is the public's awareness that the technology we use on a daily basis has caught up on the vision of an always on, always connected world. If you look into the details of what comprises the IoT, you’ll see that it includes everything from cloud computing, Big Data analytics, “Things,” Web communication, applications, network, storage, etc. It is essentially including everything connected online from hardware to software, or as we like to say, it’s an Internet of many different things. The difference ...
Cloud Expo 2014 TV commercials will feature @ThingsExpo, which was launched in June, 2014 at New York City's Javits Center as the largest 'Internet of Things' event in the world.
SYS-CON Events announced today that Windstream, a leading provider of advanced network and cloud communications, has been named “Silver Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Windstream (Nasdaq: WIN), a FORTUNE 500 and S&P 500 company, is a leading provider of advanced network communications, including cloud computing and managed services, to businesses nationwide. The company also offers broadband, phone and digital TV services to consumers primarily in rural areas.