A new article is up on Database Journal where I discuss some query optimizations that work well with MySQL applications.Five Query Optimizations in MySQL
Archive for category mysql
The MySQL Query Cache is a powerful piece of engineering that users of the popular open source database can take advantage of to speed up throughput of their applications. MySQL’s cache operates a little bit differently from other database engines. It does not just cache query plans, but the query data as well. What this means is that the size and number of queries that your database manages will be variables when tuning the query cache. We discuss all of these items in our new article at DatabaseJournal.
Database Journal – Optimizing the MySQL Query Cache
Our latest article over at Database Journal is hot off the presses. Advanced MySQL Replication – Improving Performance discusses some of the best ways to improve the performance of your MySQL slave setup. If the slave is constantly getting further and further behind the master database, we discuss a number of techniques which may help you bring it under control.
review: MySQL Cookbook
Nov 14
Last year O’Reilly released the 2nd Edition of the MySQL Cookbook by Paul DuBois. You can read my review here.
Whenever a publisher releases a 2nd Edition of a book, you know it was well received the first time around. So that’s a good sign that the material has gotten people buying. I would say in the computer reference and howto market, that’s a very good indication that the material is well written and relevant. I certainly found it to be the case with this title.
If you’re looking for a quick & no nonsense howto book on MySQL development, look no further. The book focuses on Ruby, Perl, PHP, Python and Java as examples. So if you’re doing development, specifically web development, you’ll get a lot out of this title.
I just finished reading the recently released 2nd Edition of High Performance MySQL by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy Zawodny, Arjen Lentz & Derek Balling. I’ve posted a review here on Amazon.
Wow, that’s quite a list of authors, but when you look at the material, you see why. This book is a very indepth look at the MySQL server. Intended for the intermediate to advanced DBAs and developers who want to know the inner workings of the server, as well as how to use many of it’s advanced features.
For instance the chapter on replication was quite good. Given that you probably setup replication in five minutes, and are wondering weeks or months later why it’s not working, this chapter will give you some answers. Using non-deterministic functions? Mixing MyISAM and InnoDB tables in the same transactions? Seeing some errors in your slave error log that don’t make sense? After finding out that there is something wrong, you may be more surprised that your slave can be out of sync with the master, and not even let you know about it. The chapter recommends Maatkit’s mk-table-checksum as an assistant to identifying these problems.
All in all the book is superb, so take a look at the review for details, and go get yourself a copy!!
DBJ: Replication Pitfalls
Nov 13
In this month’s Database Journal article we write about Replication Pitfalls with MySQL.
Replication is fairly straightforward to setup, however your slave databases can get out of sync, or throw errors. We investigate some of the reasons why, and help you identify those before they come back to bite you!
Presentation Abstract
More and more MySQL databases are sneaking their way into the Oracle environment. If you’re being asked to
managing these new databases, you may not know where to start.
We’ll give and overview of MySQL specifically for Oracle DBAs. Since you already have the requisite conceptual
framework as an Oracle DBA, a quickstart will help you get up to speed with how things are done in the MySQL
world.
Outline
Introduction
1. Installation
2. User Management, Authentication + Privileges
3. Backup and Recovery
4. Replication Solutions Compared to DataGuard
5. Transactions, Query Optimization + Profiling
6. Views and Partitioning
7. Server Tuning and Optimization
Conclusions
Learning Objective 1
Bring Oracle DBAs up to speed with MySQL
Learning Objective 2
Feature comparison between Oracle + MySQL, what’s there, what’s not etc.
Learning Objective 3
Tuning and Profiling MySQL from an Oracle perspective.
Attendee Prerequisites
Intermediate Oracle DBA experience required.
Just submitted a couple proposals for O’Reilly’s MySQL Conference in April 2009, Santa Clara, CA. Fingers crossed!
Proposal details
Proposer
Sean Hull (hullsean@gmail.com)
Speakers
Sean Hull (hullsean@gmail.com)
Description
Drupal is an open-source CMS that has a huge user base and community. I recently managed the MySQL side of the equation for a Drupal-based website of a large television brand. There are some documents out there for high performance MySQL with Drupal, I found them lacking. So I did extensive benchmarking, and profiling of queries, to find the best overall setup.
Session type: 45 minute conference session
Topics: Business and Case Studies
Abstract
Drupal is the open-source CMS du-jour. It has become very popular very quickly, but performance isn’t necessarily tip-top out of the box.
In our implementation of MySQL for a drupal-based website of a large television brand, we went down the path of doing our own benchmarking for our expected pageviews, and ares of the site we thought would get the most traffic. We benchmark the base set of modules, and our own extensions as well using apachebench, http_load, supersmack, and sql-bench. We then optimized the queries inside those modules using various MySQL profiling tools such as EXPLAIN, innotop, slow query log, and show status.
In this presentation we discuss the site, the business requirements, the benchmarking tools, profiling tools, and the final results. It provides a window into the workings of a large high-traffic internet website.
Just submitted a couple proposals for O’Reilly’s MySQL Conference in April 2009, Santa Clara, CA. Fingers crossed!
Proposal details
Proposer
Sean Hull (hullsean@gmail.com)
Speakers
Sean Hull (hullsean@gmail.com)
Description
MySQL has a number of great features for managing archival data, and very large tables. We look at the MERGE storage engine first. Then we look at views which can hide many tables behind the scenes. And lastly we look at partitioned tables.
Session type: 45 minute conference session
Topics: Data Warehousing and Business Intelligence
Abstract
MySQL has some very powerful features for large databases now available in 5.1.
We’ll look at the MERGE storage engine first. Then we’ll take a look at views, and the strengths and weaknesses there. And lastly we’ll consider partitioning as an option.
Each of these solutions has pros and cons. When you consider performance, ease of administration, and manageability, they all have tradeoffs.
Just submitted a couple proposals for O’Reilly’s MySQL Conference in April 2009, Santa Clara, CA. Fingers crossed!
Proposer
Sean Hull (hullsean@gmail.com)
Speakers
Sean Hull (hullsean@gmail.com)
Description
MySQL Replication is powerful technology, but many things can trip you up. Sometimes the slaves get out of sync with the master, or dump errors into their logfiles. Sometimes replication just can’t keep up. In this presentation will explain how to verify with checksums, avoid traps in your code, monitor, and even help you improve the performance of your replication setup.
Abstract
There are a few big gotchas with MySQL’s statement based replication. Non-deterministic functions can cause the slave to get out of sync, mixing innodb and MyISAM tables, to name just a few. What you need to do is watch for errant and incompatible SQL, and then verify that your setup is correct. Verification can be done with checksums on the tables, and comparing those against the slave copies.
We’ll discuss all this, and the tools and techniques you can use to bulletproof your own replication setup. We’ll also discuss performance tuning. If your slave is getting slowly further and further behind, there are techniques, parameters, and re-architecting that can improve its speed and performance.