Oracle + Open Source

two worlds inexorably colliding

Archive for category mysql

review: High Performance MySQL 2nd Ed.

Nov 13

Posted by Sean Hull in high availability, migration, mysql, open-source, oreilly, replication, review | No Comments

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

Posted by Sean Hull in databasejournal, mysql | No Comments

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!

Oracle DBAs Guide to MySQL Databases

Nov 7

Posted by Sean Hull in abstract, collaborate, mysql, oracle | 3 Comments

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.

My Drupal – MySQL high performance optimizations

Nov 6

Posted by Sean Hull in abstract, mysql | 1 Comment

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.

MERGE, Views, or Partition Tables – Choices for Archival Databases

Nov 6

Posted by Sean Hull in abstract, mysql | No Comments

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.

Bulletproofing MySQL Replication

Nov 6

Posted by Sean Hull in abstract, mysql | 1 Comment

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.

review: Backup & Recovery by W. Curtis Preston

Oct 22

Posted by Sean Hull in mysql, open-source, oracle, review | 1 Comment

Just finished up O’Reilly’s Backup & Recovery by W. Curtis Preston.  The title is wide-reaching, covering backups at the operating system as well as on all the popular database platforms, including Oracle, MySQL, Postgres, Sybase, SQL Server and DB2.  Preston has an amazing grasp of a spectrum of technologies and platforms, and as an Oracle & MySQL DBA myself, I’d use this as my backup reference text any day.

I’ve posted my review of Backup and Recovery over at Amazon.

facebook, mysql & memcache

Oct 13

Posted by Sean Hull in mysql | No Comments

Was scanning through the facebook team’s engineering blog recently.  Although it’s not updated very frequently, there’s some good technical discussion of the engineering behind Facebook, and what it takes to make the site work.

In particular I found this discussion Scaling Out by Jason Sobel.   He talks about the cache coherency challenges they had, and how they used MySQL & memcache to get the job done.

Sun Shines on MySQL

Jan 16

Posted by Sean Hull in mysql, open-source, oracle | No Comments

As many of you may have already heard, Sun officially announced their purchase of  MySQL today.  After the last few years with Oracle pushing Linux and commodity hardware, Sun has certainly taken the hit.  I guess this is their turn to hit back.

With MySQL 6.0 out, increasingly we find the full compliment of sophisticated database features in MySQL.  But a lot of the devil is in the details.  Where Oracle has had problems with the sheer size of the codebase, and addressing security vulnerabilities, and other bugs in a timely manner, MySQL has the problem of a mature codebase.  Some of these features are newly available, and if my experiences with replication are any indication, often have hidden gotchas and “features” which are not emphasized in the literature.

The next question on my mind is, how does Oracle’s purchase and now ownership of Innobase impact the above purchase.  It means a direct competitor owns a core component which provides transactional support to your database.  A very good question.

Time will tell, so stay tuned.

Webinar: Migrate to MySQL

Dec 11

Posted by Sean Hull in backup, migration, mysql, oracle | No Comments

The folks over at zmanda who specialize in MySQL backup are doing a webinar on December 13th, this Thursday.  They specifically mention it as relevant for Oracle DBAs or anyone moving to MySQL.  Looks to be an interesting event.