Oracle + Open Source
two worlds inexorably colliding

My O’Reilly webcast is now online at youtube. Comments welcome!

MySQL Replication is fairly simple to setup for the first time.  However over time maintenance can become troublesome.  At times errors show up in the error logs, or it can stop running altogether.  In fact MySQL Replication can fail in a much more insidious way, that is silently.  So what to do?

As it turns out there are specific reasons and causes for MySQL replication to get out of sync.  We’ll discuss what is and isn’t compliant in a MySQL Replicated environment, and then most importantly, we’ll discuss a tool that can help you verify your environment, and show you what is or isn’t in sync and why.

I’m presenting this webcast with O’Reilly on Thursday January 22.  If you’re interested, click here to register.

Over the holidays I had the chance to catch up on some podcasts I hadn’t listened to.  In particular Financial Times has an excellent weekly one called “Digital Business”.  In the last one for 2008, Peter Whitehead interviews a few people to get their forcasts for 2009.

Of particular interest to our readers was his mention that Open Source will continue to grow in importance, as budgets get squeezed.  Limited budgets mean looking at every dollar, so this month’s Open Insights issue 51, we talk about stretching your database dollar, and where and when open source database technologies may be right for you.

Part II in our series on MySQL picks up where we left off from talking about some of the challenges, and potential issues and problems that come up with MySQL replication after you have it setup and running for some time. DBJ: Fixing MySQL Replication

In this piece we talk about some of the ways to verify your setup, and make sure your slave does not drift out of sync with the master, and to alert you if it does.

Our 50th issue of Open Insights Newsletter is out.  We think you’ll enjoy the analogy!!

I was interviewed recently for an article in IT World: “In The Workplace: Tips for managing different generations”.

I do see differences in how younger people adopt social networks into their working habits versus older people who may be stuck with their existing habits that work for them.  On the other hand, I also think it has to do with personalities, as to whether someone finds the answers on their own, versus using social means & conversation to get to the answer.

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!!

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.

Presentation Abstract
Technology consulting is always one part science, one part thinking on your toes, and one part client relationship.
In a recent engagement at a large educational software services company we were faced with a very challenging
problem.  They were running up against a wall as their user base steadily grew.  They were growing at a steady
rate, but their database systems were becoming a bottleneck to the application performance and thus the service
they offered their customers.

We looked at the technology involved to scale up based on their growth rates, but found it was becoming cost
prohibitive.  They asked us how it could be done cheaper.

Stepping back from the problem, we devised a very unorthodox solution, which upon inspection may seem rather
obvious.  Yet the business had been doing things a certain way for so long, they had not considered this alternate
view.

Outline

Introduction
1. Initial meetings with client, discuss problems
2. Review of database systems
3. Cost estimate based on current growth patterns.
4. Discussions with client, and further review
5. Creative problem solving to do more with less hardware.
Conclusions

Learning Objective 1
Measuring growth of database applications

Learning Objective 2
Discussing business needs, and projecting requisite technology needs and requirements.

Learning Objective 3
Balancing technology costs with business requirements, and using creative problem solving to increase

Attendee Prerequisites
Beginning DBA skills.
Basic understanding of internet platform, client, webserver, and database.

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.

Our latest newsletter Open Insights is out.   Issue 49 - Things Fall Apart takes a stab a discussing the recent economic meltdown, and paralleling how all complex systems, particularly computing systems, have a tendency to fail as well.

This point is not made to be cynical.  Rather it asks us to take a hard look at complicated and interconnected pieces, accept that sometimes  there are things hidden in that complexity that we can’t know about, and to plan for that.  Plan for trouble, monitor (ie regulate) things as best you can, but not so much that the system doesn’t function.  And be as proactive as possible.

These truths probably hold for all complex relationships, whether they are financial, or complex technical systems.

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.

Just had a chance to read O’Reilly’s introductory book on Oracle 11g, Oracle Essentials.  I thought it was quite good and gave it four out of five stars.  Take a look at my review of Oracle Essentials on Amazon.com.

The book covers a wide range of topics, and is one part new features, one part Oracle 101, and one part summary of all of the Oracle products.  It is primarily targeted to DBAs, Developers and System Administrators, albeit on the novice side, however it’s writing style, and technical depth are not unweildy, so IT managers, and business users can surely learn a lot from this book as well.  The writing style is quite good, and overall it is an enjoyable read.

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.

If you’re interested in getting started with Oracle’s AWR facility, roughly the new statspack that’s built in, and easier to use, take a look at this new article over at Database Journal.

Intro to Oracle’s Automatic Workload Repository (AWR) 

For a while the new features in database automation had some DBAs scared that their jobs would somehow become obsolete in short order.

Paul Boutin’s recent article in Valley Wag discusses Robert Cringley’s declaration of the Death of the Database  that has everyone all excited.  At root is the idea of cloud computing, and the likes of the Google’s of the world storing all of our data, and managing all the dirty messy database storage seemlessly for us.

Yes, I’ll give you that for many applications, and small websites, this will certainly be the future.  Who wants to manage a database for every website.  But for the large clients of Oracle databases, the terrabyte datastores, datawarehouses, Oracle applications, and Financials, the backend datastore will remain a requirement.  This isn’t necessarily because a third party can’t do the job better, or that it wouldn’t make a business sleep better at night leaving the database management to the experts.   Nor is it that security couldn’t be implemented properly, to make the data available only to the business, and invisible to the prying eyes of the DBAs down the line.  No all of those problems are solvable.

The problem is one of handing over the keys to the kingdom.  Take the worldwide GPS system, for example.   Currently Europeans, Russians, and Chinese alike rely on a US built satellite system for GPS service.  Imagine military operations relying on US technology were the US to get into a scuffle with the Russians or the Chinese.  In the end business wants to see their data, if not physically, then confident of where those servers are, and who touches the data, the hardware, the backups etc.

I do agree with Cringley and Boutin that cloud computing will change things, and continue to put pressure on the big database vendors like Oracle, but I don’t think it’ll put them out of business anytime soon.

In this month’s newsletter Open Insights 48 - Balancing Time & Money, we talk about the important and delicate balancing act of using your time wisely.  One simple equation to evaluate a project, or use of your time can sometimes shed a lot of light on how much effort and energy you may want to put in that direction.

Our recent issue of Open Insights was a little delayed by a much needed vacation, but we’re finally back.

This month we discuss difficult, and intractible problems, and how sometimes if you can’t find a solution, you might consider changing the problem!

Open Insights 47 - Change the Problem

If you’re already building LAMP applications (linux/apache/mysql/php), and you’re looking to port them to Oracle, or you’re new to this technology stack, and you want to use Oracle as your database, this article is for you.  We cover the basics of gettings started, and where to look for more information.

Intro to PHP + Oracle - Database Journal - Aug 14, 2008

In our newest issue of Open Insights, Issue 46 Interests Aligned we talk a bit about interests.  Whether you’re a nation state, or a company looking to hire a new resource, you have interests, and you naturally try to improve or maximize them.  But the best situation in business is not opposing interests, where the trickiest or wiliest wins out.  The ideal situation is when interests of both parties are aligned, and thus everyone wins.

In our latest database journal piece, we cover some useful Oracle-supplied pl/sql stored procedures to help you instrument and debug your code, dump metadata descriptions of your database objects (a la MySQL dump –no-rows) and much more.

Take a look at  DBA Insider - Useful PL/SQL Packages.

Hard to believe we’ve reached our 45th issue of our monthly newsletter.  Time flies, that’s for sure.

This month’s topic is fixed fee contracts.  We tell a story of a recent business experience, the trouble, the challenge, and the resolution.  Hopefully it’ll help folks out there who are also looking at fixed fee project-based billing, and how to manage scope, and client expectations.

Open Insights 45 - Contractualities

What is ASM, and why do we need it?  In this piece, we cover the impetus for it’s creation, and what you can do with it.  Oracle Automatic Storage Management

If you’ve ever wanted to write scripts to talk to your database, perhaps to perform a routine maintenance operation, or to build a more full-fledged application, you may have considered Perl.  It’s a flexible language, with a huge collection of prebuilt modules, and components to choose from.

In this May article over at databasejournal.com, we give you an insiders introduction to the goodness that is Perl + Oracle:  Programming, Perl + Oracle.

Oracle Indexing - What, Where, When?

This piece was published in April over at databasejournal.com.  In it we discuss some of the myriad different index types available to you in Oracle, and try to sift through to the most important ones.

In March 2008, we published this article over at Database Journal: Create a Database Manually - When & Why?

In Oracle you have the option to use the GUI database creation assistant tool, or you can script the process.  We discuss how to do this manually, when you may want to, and hopefully learn a few new things along the way.