Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 



Forgot your password?
typodupeerror
Data Storage Books Media Databases Programming Software Security Book Reviews

High Performance MySQL 307

Posted by timothy
from the turning-the-tables dept.
swsuehr (Steve Suehring) writes "Knowing that Jeremy Zawodny works with MySQL in a highly demanding environment at Yahoo! Finance, I have to admit that I was somewhat anxious to crack open a copy of the book High Performance MySQL published by O'Reilly and written by Jeremy and Derek J. Balling. With such high expectations, it would be easy to be disappointed with the content or level of the material in the book. Far from disappointing, High Performance MySQL was exactly what I had hoped it would be and more." Read on for the rest of Suehring's review.
High Performance MySQL
author Zawodny & Balling
pages 294
publisher O'Reilly
rating Excellent
reviewer Steve Suehring
ISBN 0596003064
summary Optimization, Backups, Replication, Load Balancing & More

With a title like High Performance MySQL, the reader should not expect assistance with tasks such as installation or the basics of SQL. The authors acknowledge that the book isn't meant for the MySQL beginner. Even so, the book does spend a little time going over a refresher of MySQL. If the reader is looking for a book on the basics of MySQL, though, I'd recommend picking up a different title. I might be so brazen as to suggest looking at the book on MySQL that I wrote a couple years ago, but I'll leave it for the reader to find an appropriate title for their level of knowledge with MySQL.

The book is broken out into ten chapters, beginning with a chapter on the basics of MySQL as seen from someone with some level of MySQL experience. (And since I am someone with some level of MySQL knowledge, this chapter didn't seem to be too painful of a read.) That statement begs for a further explanation. I find it painful to read books that claim to have advanced topics but find it necessary to spend (or waste) time by giving yet another explanation of how to type ./configure; make; make install in the beginning chapter. In this regard, I find High Performance MySQL to be quite good at providing the appropriate level of explanation for the audience.

The first pages alone are a great starting point for those who are wondering about the performance as it relates to the different versions of MySQL. A discussion of the binary versions of MySQL versus the source code versions gives the reader a good understanding of the implications of this important decision. Choosing correctly between the versions of MySQL can help to maximize performance.

Though only the first chapter is titled "Back to Basics," most chapters include some basic or background information pertinent to that chapter. For example, both the query and index performance-related chapters include sections with the word 'Basic' in their titles. These two chapters, "Index Performance" and "Query Performance," cover topics often misunderstood when considering performance on a database. Naturally, any database administrator worth their pay (insert your own joke here) will tell you that an index can help to improve performance. But High Performance MySQL takes it a step further by diving into why you might use a particular index given your database needs. This type of practical information is common throughout the book. Rather than just give the reader a listing of the indexes (which are mostly common among RDBMS anyway), the authors choose to tell you why you might choose one method over another.

Other chapters within High Performance MySQL include chapters on server performance tuning, replication, load balancing, and high-availability, backup and recovery, and security. I've followed MySQL forums and mailing lists over the years and some of the more common questions relate to MySQL security. The chapter on security gives the reader a great explanation of the MySQL GRANT tables and includes a sidebar on the significance of 'localhost' in MySQL which nearly always seems to be a source of confusion, though maybe not for the reader of this particular book. Like other chapters, the security chapter includes tips for the underlying environment upon which MySQL operates. For example, regardless of how good the MySQL database permissions are configured, if another piece of software on the server isn't kept up to date then the entire server, including the MySQL data, could be compromised.

Looking back at the book, I might have laid the chapters out in a different order or provided more structure through sections of the book. However, this can be dismissed as personal opinion and is non-substantive anyway. I believe High Performance MySQL is an excellent choice for the MySQL administrator or developer not only looking to improve their database performance but also to improve his knowledge of MySQL.


You can purchase High Performance MySQL from bn.com. (And the book that Suehring alludes to is the MySQL Bible . Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

This discussion has been archived. No new comments can be posted.

High Performance MySQL

Comments Filter:
  • by xxxJonBoyxxx (565205) on Wednesday October 20, 2004 @01:38PM (#10578136)
    Sometimes MySQL indexes are bad. For example, if you infrequently do lookups based on an index but you often add or remove many records from a database, an MySQL index can actually hurt performance.

    I hope there's something about that in the book...but then again, this is 2004. Who still reads (non-electronic) books about technical topics?
  • by Anonymous Coward on Wednesday October 20, 2004 @01:40PM (#10578169)
    Really, either the fourth or fifth item on the list would, with VERY high probability, imply all the rest but the first.

    Your list has an actual information content of roughly 2.4 items, as opposed to 6.
  • by smclean (521851) on Wednesday October 20, 2004 @01:47PM (#10578256) Homepage
    The important thing is, does it tell you how to set up and manage highly distributed system managing gigabytes of data under high transaction volumes?
    The article did mention that it covers some related issues: "Other chapters within High Performance MySQL include chapters on server performance tuning, replication, load balancing, and high-availability, backup and recovery, and security."

    But I agree, the review doesn't give any indication whether the book spends all its time repeating whats already available in the MySQL manual, or giving the reader new ideas and insight into 'professional' MySQL optimization.

  • by Anonymous Coward on Wednesday October 20, 2004 @01:48PM (#10578270)
    Maybe, but understand that some of us who are MySQL admins may have been thrust into that position as a result of our jobs and not necessarily because we were trained on databases.

    My background is in Electrical Engineering, but as part of my duty I am in charge of maintaining our company's data base. I'm probably the most qualified person here to do it too; but my knowledge of databases doesn't extend much farther than select/insert/update. Sound to me like this book could help me learn a thing or two about what I'm "administrating".
  • mysql backup (Score:5, Interesting)

    by InsaneCreator (209742) on Wednesday October 20, 2004 @01:49PM (#10578284)
    I see one of the topics covered is backing up data. Would somebody care to explain how to make a consistent backup of a mysql database?

    Plain mysqldump does not export consistent data across tables. While you could lock all tables for the duration of backup, that's not likely going to be possible if you're aiming for "high performance". Mysqlhotcopy only works for myisam tables and simply tar-ing up data files is just asking for a disaster to happen. So how do you make a consistent backup without setting up a dedicated replication slave for this purpose?

    (No, this is not a troll; I am in fact looking for a good way to back up mysql data on my server)
  • by HerculesMO (693085) on Wednesday October 20, 2004 @01:50PM (#10578291)
    In what type of application do they use it? I would not imagine as a primary database, do they?
  • by AKAImBatman (238306) * <akaimbatman AT gmail DOT com> on Wednesday October 20, 2004 @01:57PM (#10578379) Homepage Journal
    Okay, as an industry professional, I tire of books calling themselves "for professionals". Don't most professionals already know about index types and choosing their usage?

    More importantly, what self-respecting professional would be caught dead using MySQL?

    No data integrity
    Completely non-standard SQL
    No extensibility in the engine (functions, stored procedures, etc.)
    No subselects
    Weird handling of '0' vs. null
    etc.

    Back when MySQL was created, it was a great option for when you needed a simple database to store non-critical data. But then everyone decided that MySQL was super-1337 and that all other databases were simply part of "the evil empire".

    As a result, REAL alternatives like PostgreSQL were just ignored when they came on the scene. After all, you have to perform *gasp* maintenance on Postgres! *sigh*
  • ./Configure (Score:3, Interesting)

    by tokenhillbilly (311564) on Wednesday October 20, 2004 @02:00PM (#10578419)
    Actually, I would have thought that a section on how to best configure MySQL for particular performance situations would have been very helpful. I have not read the book, so it may very well have such a section, but this posting implied that configuration was assumed to be a non-advanced topic.
  • Performance Dropoff (Score:1, Interesting)

    by Anonymous Coward on Wednesday October 20, 2004 @02:00PM (#10578420)
    I'm currently working on a web based intranet app that could most likely contain a lot of data (but only used by a small number of people).

    I'm thinking about buying this book (although a professional book with typos worries me).. but it does me no good if running these performance enhancements mean squat once the DB gets over a gig or something. Anyone know where the performance dropoff is?
  • by Serveert (102805) on Wednesday October 20, 2004 @02:07PM (#10578502)
    The read only slave got corrupt and in order to create the replica apparantly you have to pretty much disable the master while it creates a new replica so it was unacceptable.

  • by smclean (521851) on Wednesday October 20, 2004 @02:15PM (#10578576) Homepage
    I think that most of us here understand core database concepts (including ACID) just fine.
    Oh? Read some of the other comments on this article and see :)

    I agree that MySQL is overused. It's the M in LAMP, and so people assume its what they have to use for everything.

    But what are these alternatives you mention that are so much better suited for every possible scenario? For me, MySQL is the perfect mix of speed and features. Give me some leads on other databases I should consider replacing it with, I'll check them out.

  • MySQL Performance (Score:4, Interesting)

    by rodrigo.avila (797846) <<rb.ite.aliva> <ta> <ogirdor>> on Wednesday October 20, 2004 @02:24PM (#10578683) Homepage
    If you need extreme performance in small commercial site, why you need an great and cpu-expensive Referential Integrity? In my opinion, in this case, MySQL is better. Comments?
  • His blog (Score:2, Interesting)

    by Anonymous Coward on Wednesday October 20, 2004 @02:46PM (#10578882)
    Jeremy has a blog at http://jeremy.zawodny.com/blog/
  • OLAP? (Score:3, Interesting)

    by techsoldaten (309296) * on Wednesday October 20, 2004 @02:59PM (#10579008) Journal
    Okay, every time I hear about some new feature in MySQL and start making the argument the database may be finally be sufficent for the backend of an OLAP system, someone immediately responds, 'BUT THERE IS NO ROLLUP PEROGATIVE - HOW CAN YOU HAVE OLAP WITHOUT ROLLUP?' Responses tend to differ from there on, but the basic point is that few people agree MySQL can be used for data analysis.

    One argument that I have been turning to is that not that many people take the time to become great MySQL developers. It is easy to be good with MySQL, it is terribly difficult to be great with MySQL. Indexes alone are a problem for many people to grasp - the whole leftmost column rule is easily overlooked unless someone is having a problem with a specific query.

    Books like this give me hope people will begin looking deeper at MySQL as a development platform and that a wider base of developers will begin to emerge.

    Anyways, OLAP? Can this book be used to help people get there?

    M

    (P.S. I have built an almost-generic OLAP system for my company using a MySQL backend. I really want to know what other people's thoughts are.)

  • Re:MySQL Performance (Score:4, Interesting)

    by Just Some Guy (3352) <kirk+slashdot@strauser.com> on Wednesday October 20, 2004 @03:24PM (#10579295) Homepage Journal
    Here's the deal: you have to put the "integrity layer" somewhere, unless you're OK with every other page load returning a 500 error. So, the real question is whether you want to put that layer close to the data (eg with foreign keys and other restrictions inside the database) or close to the user (eg with a lot of code in PHP or other languages not ideally suited to such things).

    For example, say that you're running a shopping cart and want to guarantee that each available item has a unique identifier. Is it more efficient to make the "itemid" field a unique key, or to fire off two queries every time you want to add an item: the first to attempt to fetch a row with the newly-generated itemid and the second to insert the new data? Even more importantly, how thread-safe is your website code? Can you prove that it's impossible for someone to insert an itemid the instant after you've tried to verify that the exact same itemid hasn't already been used?

    Again, you must and will put some sort of integrity checking into your system, unless you're really curious how well unemployment insurance pays in your city. Do you want to hand-roll your own half-assed solution, or would it be easier to say "here's a list of constraints, Mr. Database! Make sure I don't accidentally break them, would you?"

  • Re:MySQL Performance (Score:3, Interesting)

    by poot_rootbeer (188613) on Wednesday October 20, 2004 @03:24PM (#10579297)

    Maybe it's just me, but I can't conceive of any relational database scenario where maintaining referential integrity WOULDN'T be a requirement.

    Garbage In, Garbage Out. Who cares how fast your queries run if the data has gotten munged?
  • by DrSkwid (118965) on Wednesday October 20, 2004 @03:40PM (#10579509) Homepage Journal

    I prefer to FAPP

    (FreeBSD, Apache, PostgreSQL & PHP)

    but that makes me sound a bit of a wanker

    The thing about MYSQL and speed is that "it's really quick at returning rows" isn't the only metric of speed.

    Let's see how fast you can work out how to do an INTERSECT!

    or how about a huge join with LIMIT 5, mysql (and postgres) do the whole join and then return the 5 records. With Postgres you can utilize stored procedures to do the join *after* the required records have been worked out and return the rows via a stored procedure. I got a 3 minute query down to 3 seconds like that !

    You may as well turn your LAMPs off, they are dim.

  • by jadavis (473492) on Thursday October 21, 2004 @03:44AM (#10584166)
    MySQL has a modular design, allowing you to use any of several database backends

    Do they actually have constraints yet? For all the RDBMS apps that I can think of, I wouldn't go without constraints. Last I heard, MySQL thinks 2004-02-31 is a date.

    Also, I'm a little wary of the mix-and-match feature policy, perhaps because I don't understand it. Are there any features not available in the InnoDB tables? Can I do master-master replication and everything, all with transactions? I would be really troubled if each feature is it's own table type or something crazy like that, I want to be able to use all the features at once.

If you're not careful, you're going to catch something.

Working...