Click here to close now.

Welcome!

Linux Authors: Carmen Gonzalez, Roger Strukhoff, Gregor Petri, Elizabeth White, Ian Khan

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
As organizations shift toward IT-as-a-service models, the need for managing and protecting data residing across physical, virtual, and now cloud environments grows with it. CommVault can ensure protection &E-Discovery of your data – whether in a private cloud, a Service Provider delivered public cloud, or a hybrid cloud environment – across the heterogeneous enterprise. In his session at 16th Cloud Expo, Randy De Meno, Chief Technologist - Windows Products and Microsoft Partnerships, will discuss how to cut costs, scale easily, and unleash insight with CommVault Simpana software, the only si...
Analytics is the foundation of smart data and now, with the ability to run Hadoop directly on smart storage systems like Cloudian HyperStore, enterprises will gain huge business advantages in terms of scalability, efficiency and cost savings as they move closer to realizing the potential of the Internet of Things. In his session at 16th Cloud Expo, Paul Turner, technology evangelist and CMO at Cloudian, Inc., will discuss the revolutionary notion that the storage world is transitioning from mere Big Data to smart data. He will argue that today’s hybrid cloud storage solutions, with commodity...
Every innovation or invention was originally a daydream. You like to imagine a “what-if” scenario. And with all the attention being paid to the so-called Internet of Things (IoT) you don’t have to stretch the imagination too much to see how this may impact commercial and homeowners insurance. We’re beyond the point of accepting this as a leap of faith. The groundwork is laid. Now it’s just a matter of time. We can thank the inventors of smart thermostats for developing a practical business application that everyone can relate to. Gone are the salad days of smart home apps, the early chalkb...
Cloud data governance was previously an avoided function when cloud deployments were relatively small. With the rapid adoption in public cloud – both rogue and sanctioned, it’s not uncommon to find regulated data dumped into public cloud and unprotected. This is why enterprises and cloud providers alike need to embrace a cloud data governance function and map policies, processes and technology controls accordingly. In her session at 15th Cloud Expo, Evelyn de Souza, Data Privacy and Compliance Strategy Leader at Cisco Systems, will focus on how to set up a cloud data governance program and s...
Roberto Medrano, Executive Vice President at SOA Software, had reached 30,000 page views on his home page - http://RobertoMedrano.SYS-CON.com/ - on the SYS-CON family of online magazines, which includes Cloud Computing Journal, Internet of Things Journal, Big Data Journal, and SOA World Magazine. He is a recognized executive in the information technology fields of SOA, internet security, governance, and compliance. He has extensive experience with both start-ups and large companies, having been involved at the beginning of four IT industries: EDA, Open Systems, Computer Security and now SOA.
The industrial software market has treated data with the mentality of “collect everything now, worry about how to use it later.” We now find ourselves buried in data, with the pervasive connectivity of the (Industrial) Internet of Things only piling on more numbers. There’s too much data and not enough information. In his session at @ThingsExpo, Bob Gates, Global Marketing Director, GE’s Intelligent Platforms business, to discuss how realizing the power of IoT, software developers are now focused on understanding how industrial data can create intelligence for industrial operations. Imagine ...
We certainly live in interesting technological times. And no more interesting than the current competing IoT standards for connectivity. Various standards bodies, approaches, and ecosystems are vying for mindshare and positioning for a competitive edge. It is clear that when the dust settles, we will have new protocols, evolved protocols, that will change the way we interact with devices and infrastructure. We will also have evolved web protocols, like HTTP/2, that will be changing the very core of our infrastructures. At the same time, we have old approaches made new again like micro-services...
Operational Hadoop and the Lambda Architecture for Streaming Data Apache Hadoop is emerging as a distributed platform for handling large and fast incoming streams of data. Predictive maintenance, supply chain optimization, and Internet-of-Things analysis are examples where Hadoop provides the scalable storage, processing, and analytics platform to gain meaningful insights from granular data that is typically only valuable from a large-scale, aggregate view. One architecture useful for capturing and analyzing streaming data is the Lambda Architecture, representing a model of how to analyze rea...
SYS-CON Events announced today that Vitria Technology, Inc. will exhibit at SYS-CON’s @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Vitria will showcase the company’s new IoT Analytics Platform through live demonstrations at booth #330. Vitria’s IoT Analytics Platform, fully integrated and powered by an operational intelligence engine, enables customers to rapidly build and operationalize advanced analytics to deliver timely business outcomes for use cases across the industrial, enterprise, and consumer segments.
Today’s enterprise is being driven by disruptive competitive and human capital requirements to provide enterprise application access through not only desktops, but also mobile devices. To retrofit existing programs across all these devices using traditional programming methods is very costly and time consuming – often prohibitively so. In his session at @ThingsExpo, Jesse Shiah, CEO, President, and Co-Founder of AgilePoint Inc., discussed how you can create applications that run on all mobile devices as well as laptops and desktops using a visual drag-and-drop application – and eForms-buildi...
Containers and microservices have become topics of intense interest throughout the cloud developer and enterprise IT communities. Accordingly, attendees at the upcoming 16th Cloud Expo at the Javits Center in New York June 9-11 will find fresh new content in a new track called PaaS | Containers & Microservices Containers are not being considered for the first time by the cloud community, but a current era of re-consideration has pushed them to the top of the cloud agenda. With the launch of Docker's initial release in March of 2013, interest was revved up several notches. Then late last...
SYS-CON Events announced today that Dyn, the worldwide leader in Internet Performance, 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. Dyn is a cloud-based Internet Performance company. Dyn helps companies monitor, control, and optimize online infrastructure for an exceptional end-user experience. Through a world-class network and unrivaled, objective intelligence into Internet conditions, Dyn ensures traffic gets delivered faster, safer, and more reliably than ever.
CommVault has announced that top industry technology visionaries have joined its leadership team. The addition of leaders from companies such as Oracle, SAP, Microsoft, Cisco, PwC and EMC signals the continuation of CommVault Next, the company's business transformation for sales, go-to-market strategies, pricing and packaging and technology innovation. The company also announced that it had realigned its structure to create business units to more directly match how customers evaluate, deploy, operate, and purchase technology.
In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect at GE, and Ibrahim Gokcen, who leads GE's advanced IoT analytics, focused on the Internet of Things / Industrial Internet and how to make it operational for business end-users. Learn about the challenges posed by machine and sensor data and how to marry it with enterprise data. They also discussed the tips and tricks to provide the Industrial Internet as an end-user consumable service using Big Data Analytics and Industrial Cloud.
The explosion of connected devices / sensors is creating an ever-expanding set of new and valuable data. In parallel the emerging capability of Big Data technologies to store, access, analyze, and react to this data is producing changes in business models under the umbrella of the Internet of Things (IoT). In particular within the Insurance industry, IoT appears positioned to enable deep changes by altering relationships between insurers, distributors, and the insured. In his session at @ThingsExpo, Michael Sick, a Senior Manager and Big Data Architect within Ernst and Young's Financial Servi...
Performance is the intersection of power, agility, control, and choice. If you value performance, and more specifically consistent performance, you need to look beyond simple virtualized compute. Many factors need to be considered to create a truly performant environment. In his General Session at 15th Cloud Expo, Harold Hannon, Sr. Software Architect at SoftLayer, discussed how to take advantage of a multitude of compute options and platform features to make cloud the cornerstone of your online presence.
Almost everyone sees the potential of Internet of Things but how can businesses truly unlock that potential. The key will be in the ability to discover business insight in the midst of an ocean of Big Data generated from billions of embedded devices via Systems of Discover. Businesses will also need to ensure that they can sustain that insight by leveraging the cloud for global reach, scale and elasticity.
IoT is still a vague buzzword for many people. In his session at @ThingsExpo, Mike Kavis, Vice President & Principal Cloud Architect at Cloud Technology Partners, discussed the business value of IoT that goes far beyond the general public's perception that IoT is all about wearables and home consumer services. He also discussed how IoT is perceived by investors and how venture capitalist access this space. Other topics discussed were barriers to success, what is new, what is old, and what the future may hold. Mike Kavis is Vice President & Principal Cloud Architect at Cloud Technology Pa...
Even as cloud and managed services grow increasingly central to business strategy and performance, challenges remain. The biggest sticking point for companies seeking to capitalize on the cloud is data security. Keeping data safe is an issue in any computing environment, and it has been a focus since the earliest days of the cloud revolution. Understandably so: a lot can go wrong when you allow valuable information to live outside the firewall. Recent revelations about government snooping, along with a steady stream of well-publicized data breaches, only add to the uncertainty
The explosion of connected devices / sensors is creating an ever-expanding set of new and valuable data. In parallel the emerging capability of Big Data technologies to store, access, analyze, and react to this data is producing changes in business models under the umbrella of the Internet of Things (IoT). In particular within the Insurance industry, IoT appears positioned to enable deep changes by altering relationships between insurers, distributors, and the insured. In his session at @ThingsExpo, Michael Sick, a Senior Manager and Big Data Architect within Ernst and Young's Financial Servi...