johndz

Coolest Picture Ever

2012-01-18T14:21:00.001-07:00

USS Doyle Decommissioning.

2011-07-27T00:23:00.005-06:00

This week, the USS Doyle will be decommissioned after 28 years of service. During my time aboard, we made her first Med cruise which took us to Sicily, Italy, Greece, Spain, France, Rumania, and Israel. Here are a few pictures from my scrapbook.

Exporting / importing in the Pentaho Data Integration 4.0.1 Repository

2010-11-19T04:58:00.004-07:00

After upgrading from PDI 3.x to 4.0.1, I've found that the old way of exporting an repository subfolder in a single step is gone. It's still important for me to be able to do this since I use separate repositories for Development, Test, and Production, and these systems must remain isolated.

As a work around, here is what I am doing for now:

You can still keep your jobs and transforms organized in repository folders/sub-folders. But you have to now export each (open) job and transformation individually using File -> Export -> To XML

I just save these files to an empty temporary directory on the file system.

Then I disconnect from the source ( development) repository and connect to the destination repository, ( ie. Test or Production).

In there, use :

Tools -> Repository -> Import Repository

In the dialog that displays:
Navigate to your temporary file system directory, and select all files you want to import. You can select more then one / all files by holding the shift key,
much like selecting multiple rows in a spreadsheet.

Click on the "Open" button

This will import your jobs and transformations at once. It will even create your sub-folders in the repository if they don't exist.

---------------------------------------------------
The first step, of course, is to backup your repository. My repositories use MySQL databases, so here is the form of the command I use:

mysqldump --user=youruser --host=your.host --password=your.password --skip-opt --add-locks --create-options --databases DATABASENAME > backupfilename.out

Between release cycles, I verify my backups by using them to restore/refresh my Development and Test repositories from Production.

---------------------------------------------------

I attended the Pentaho Agile-BI tour in Denver this week and met some other folks using PDI and the BI Suite, which was great. One of the discussions some of us had was about forming a Pentaho user group in the Denver / Boulder Area. If anyone is interested look me up on LinkedIn.com or send me an email and lets at least start an online discussion group.

Connecting Pentaho Data Integration to hive / hadoop

2010-08-11T07:33:00.005-06:00

My latest data integration challenge has been with a new node in my data landscape: a hadoop/hive installation. Since PDI has become my favorite hammer for many different tasks, I thought it would be handy to get connected to the hive database via jdbc. With that ability, I can enhance hive output by including lookups and joins with operational ( MySQL ) databases.

Unfortunately, I didn't have much luck using standard connections with jdbc and table input steps. I suppose this is because the hive jdbc driver is still in the embryonic stage.

The turning point for my effort was the discovery of the new User Defined Java Class in Pentaho 4.0 GA. I struggled a bit before getting this to work, but I now have a simple working example that returns the result of a hive query to the stream. There was quite a bit of late night thrashing, so excuse the un-refined code.

In summary, the keys to getting the udjc to work were:

Copy the ${HIVE_HOME}/lib/*.jar and $HADOOP_HOME/hadoop-*-core.jar files into the data_integration/libext directory.
The test case ktr file posted at the bottom of this thread on the Pentaho forum was also helpful.
Matt Casters blog on udjc for the intro to using this step.

Here is the un-refined code for the udjc step:



import java.sql.SQLException;
import java.sql.Connection;
import  java.sql.ResultSet;
import java.sql.Statement;
import  java.sql.DriverManager;


public boolean  processRow(StepMetaInterface smi, StepDataInterface sdi) throws  KettleException
{
    Object[] inrow = getRow();
    if  (inrow == null) {
        setOutputDone();
       return false;
    }

    try {
       String driverName =  "org.apache.hadoop.hive.jdbc.HiveDriver";
        Class.forName(driverName);
      } catch (Exception e) {
      e.printStackTrace();
     System.exit(1);
   }

try {  
Connection  con =  DriverManager.getConnection("jdbc:hive://localhost:10000/default", "",  "");
   Statement stmt = con.createStatement();
   String sql ;
    String msg ;

   // select * query
   sql = "select sum(id)  from jd_test " ;
   ResultSet res = stmt.executeQuery(sql);
    long idval ;
   Object[]  outrow = createOutputRow(inrow,  data.outputRowMeta.size());

   while (res.next()) {
       outrow = RowDataUtil.createResizedCopy(outrow,  data.outputRowMeta.size());
      idval = res.getLong(1) ;
       msg = "result id: " + idval ; 
      logBasic(msg);
      get(Fields.Out,  "id").setValue(outrow, idval );     
       putRow(data.outputRowMeta, outrow);
   }
} catch (  SQLException se ) { se.printStackTrace() ; System.exit(1) ;}
    return true;
}

Farewell to Themistocles

2010-07-12T22:46:00.014-06:00

They say that the happiest day of your life is when you buy your boat, and the second happiest day is when you sell it.

In this case, I'm not sure they are right. Tonight I did the unthinkable and sold my boat to a gentleman who will give Themistocles a good home on the Texas Gulf Coast. As much as I loved this boat, I just don't find much time for boating now that I live in Colorado, and I hated to see it just sitting in the driveway.

The name Themistocles happened to be the name of the ship that brought my grandfather to Ellis Island from Greece in 1917, and a fitting name for my boat. I painstakingly painted the name in Greek on the stern.

A quick picture with my kids after catching some Spanish Mackerel.

And a day on Carter Lake, in Colorado.

Cruising of the coast of Tarpon Springs, Florida, near Anclote Key.

Insert...Select...On Duplicate key with timestamps

2010-05-12T21:56:00.021-06:00

In the past, I've joked with colleagues about the trap doors hidden in the TIMESTAMP column, and today I fell through one of them.

I think there are some advantages to using timestamps, especially in large tables as part of a key. If you are not too worried about its limited range, the meager 4 bytes it uses can be an advantage.

But there are some issues with this datatype, since it also comes with some default behaviors to watch out for.

Today I was adding in some data from a stage table to an aggregation table using INSERT...SELECT...ON DUPLICATE KEY UPDATE. In my case, both tables had identical structure, including column names.

I've worked up an example of todays puzzle. Here is the prerequisite table definitions and pre-population:

create table jd_stage (
customer_id int not null ,
order_date timestamp not null ,
order_amount decimal(10,4) not null ,
primary key ( customer_id , order_date )
) ;

create table jd_agg (
customer_id int not null ,
order_date timestamp not null ,
order_amount decimal(10,4) not null ,
primary key ( customer_id , order_date )
) ;

insert into jd_agg values
( 1 , "2010-05-01 09:00:00" , 49.25 ) ,
( 1 , "2010-05-01 10:00:00" , 75.50 ) ,
( 2 , "2010-05-01 09:00:00" , 95.40 );

insert into jd_stage values
( 1 , "2010-05-01 09:00:00" , 50.75 ) ,
( 1 , "2010-05-01 10:00:00" , 24.50 ) ,
( 2 , "2010-05-01 09:00:00" , 4.60 );

In this example, I want to aggregate the data in jd_stage to jd_agg, and I've set the data up so that the final result will be 100.00 for the order_amounts.

My first attempt failed because of how I composed the query, which seems innocent enough:

insert into jd_agg ( customer_id, order_date , order_amount )
select customer_id , order_date , order_amount
from jd_stage
on duplicate key update
order_amount = order_amount + values(order_amount) ;

My punishment for this was :

ERROR 1052 (23000) at line 39: Column 'order_amount' in field list is ambiguous

So, I resolved this by trying....

insert into jd_agg ( customer_id, order_date , order_amount )
select customer_id , order_date , order_amount
from jd_stage
on duplicate key update jd_agg.order_amount = jd_agg.order_amount + values(order_amount) ;

And my reward was:

ERROR 1062 (23000) at line 49: Duplicate entry '1-2010-05-12 20:50:15' for key 1

What? I don't have any data with the date/time of "2010-05-12 20:50:15". Plus, that just so happened to be the time that I executed the query.

Once again, fooled by timestamps.

According to MySQL Scripture, which I've read a hundred times, but somehow seem to keep forgetting: If you don't want automatic initialization or updating of a timestamp column, then set the default to 0. So I should have done this:

create table jd_agg (
customer_id int not null ,
order_date timestamp not null DEFAULT 0,
order_amount decimal(10,4) not null ,
primary key ( customer_id , order_date )
) ;

Then the second form of the insert, shown above, is error free:

select * from jd_agg ;
+-------------+---------------------+--------------+

| customer_id | order_date | order_amount |

+-------------+---------------------+--------------+

| 1 | 2010-05-01 09:00:00 | 100.0000 |

| 1 | 2010-05-01 10:00:00 | 100.0000 |

| 2 | 2010-05-01 09:00:00 | 100.0000 |

+-------------+---------------------+--------------+

(sorry for formatting...blogger is not cooperating).

So, what do you do if the evil DBA won't let you add that DEFAULT? I found that adding the order_date in the "update" section also works:

insert into jd_agg ( customer_id, order_date , order_amount )
select customer_id , order_date , order_amount
from jd_stage
on duplicate key update
jd_agg.order_date = jd_agg.order_date ,
jd_agg.order_amount = jd_agg.order_amount + values(order_amount) ;

Part 2: Comparing Numerics in Pentaho Data Integration

2010-05-04T22:13:00.001-06:00

As a followup to my previous post about comparing numeric values, I've since discovered a little more about the problem. To repeat my original problem: certain numeric field values that should be equal are being detected as different in the Filter rows step. I think it's important to be able to perform accurate comparisons since it is a frequent task in data quality analysis.

Originally, I assumed this had something to do with jdbc. However, since I can re-produce the issue without any SQL, I'm sure this has nothing to do with the version of the MySQL Connector/J jdbc driver. I tried the 5.0.8 version of the driver and I observed the same behavior. I couldn't even get my transform to work correctly with the 5.1.12 version of the connector -- it does not recognize column aliases in my SQL query.

Now for the rest of the story:

My comparison of numeric data was between 2 fields from two data streams, initiated by two separate SQL table inputs.

The first data stream is from a "raw" table. From the input step it is passed through a "sort rows" and then a "group by" step to aggregate the numeric values.

The second data stream is from a "rollup" table where the raw data is summarized.

The two streams are then merged ( by a unique id) and compared in order to validate the data in the rollup.

At this point, the problem seems more related to the metadata of the fields. I found two resolutions to choose from:

Use "group by" in SQL. The data types of the output numerics are magically set to BigNumber.

or

Place a "Select/Rename Values" step after the sort and before the "Group By" step to coerce the metadata of the fields to be of BigNumber type.

Personally, I prefer the second option because I like to extract the data as quickly as possible. Pentaho can handle sorting and grouping of somewhat large datasets just fine.

I have a new example transform here: numeric_compare_filter_values_try2.ktr

Here is a picture of the example transform:

Grilled Wild Duck with Spicy Orange Glaze

2010-04-26T12:32:00.002-06:00

This isn't really a recipe, but rather a chronicle of my experience grilling wild duck. I've tried a few different ways of grilling duck and up to now the results have been "ok", but less than perfect.

Last night was different, possibly my finest hour at the grill.

The lucky duck was a small mallard hen that I shot during hunting season. For those of you who have never had wild game, it is not like any domestic bird you can buy. The meat is lean and dark red. The skin has a layer of fat and is quite thick. I'm sure that a big reason why ducks have no trouble swimming in near-freezing water.

Cooking these birds can be tricky because if you over-cook the meat it tastes like liver. To compound this, the juices flow red/pink if you cook it properly, and we all know that a big no-no when grilling chicken. I know that I had a tendency to overcook duck because of the "clear juice" hang-up. Conventional wisdom says that wild duck should be cooked until it appears like rare steak. While there are recipes on the internet, there's not a lot of information about specific cooking techniques.

Here's how I did it. I'm the only one in the family who will eat game birds, so this recipe feeds one:

Step one, the spice. There's no need to re-invent the wheel here. My favorite spice is Emeril's Essence. I made a batch for some seafood pasta a few months ago and I've since found it to be useful for many dishes. You can make it by following the instructions here:

http://www.foodnetwork.com/recipes/emeril-lagasse/emerils-creole-seasoning-essence-recipe3/index.html

Before you start the next steps, get the duck cleaned up and resting to room temperature. Don't cook a cold duck. They just don't like it.

Next, prepare the orange sauce. Combine 2/3 cup Orange Juice and a chicken bouillon cube in a pan and heat until there is no evidence that you stooped so low as to use a bouillon cube instead of chicken stock. The spice is going to make up for it. Nobody will know.

Add about 1/3 cup of Orange Marmalade to the pan and simmer over medium heat until the sauce becomes syrup-like, stirring frequently.

Once that done, add two teaspoons of the Emeril Essence spice. Watch the orange sauce come to life.

Get the grill started and ready. HOT, HOT, HOT. About 450 degrees. Keep the lid down.

Prepare the Duck: Use shears or a strong knife to split the bottom, and crack the bones so that it lays flat, breast side up. This means there is no “cavity”. You don't want to cook this like a whole chicken because the cavity will require you to cook it longer and that guarantees the liver taste. Use a sharp knife and "gently" make some criss-cross cuts on the skin. This is going to help the fat flow a little. It's also going to make Mrs. Mallard flammable. Make sure the skin is dry too by patting with a paper towel.

Now brush on the orange spicy sauce on both sides, especially the skin. Don't be shy. Put on a good layer.

Pour your beer now, and get a stop watch. You will want to time this carefully, without distraction.

Ready. Set. Grill! Place the duck on the hot grill, Breast side up/Bone side down. Work quick and get that lid closed ASAP. Cook for 10 minutes and then flip. Quickly so you don't lose too much heat!

Now things may get violent. I did have a few times when the duck actually caught on fire. A little bit is ok, but you've got to pay attention and move it to a higher rack for a few moments, but always try to keep it in a high temperature with the lid closed. After about 6 minutes, it should be done. The skin will be slightly burnt, but still quite savory, and the juices will flow red. The meat will look like rare steak.

Some points to ponder:

No need for butter or oil. The fat in the skin is more then enough.

Cook fast and hot. Turning only once. I think this made the difference.

No body cavity. By laying flat on the grill, the duck cooks evenly and much faster.

Most of the wild game sauces that I've tried seem to mask the taste of the duck. The spice coupled with a simple, mild orange sauce really helped highlight the dish.

Comparing Numerics in Pentaho Data Integration / Kettle

2010-04-13T20:55:00.000-06:00

While working on a transformation I ran into a problem with comparing two (seemingly) identical numbers using the Filter Rows step. I had a case where a transformation selected two DECIMAL(13,5) values from the database and compared them.

I could see that the numbers were identical in the MySQL database, but the Filter Rows step returned false when comparing. To troubleshoot, I tried multiplying the difference of the two numbers by 10,000,000 ( in the transform) and I actually discovered a very small difference beyond the 5th decimal place. This datatype in MySQL is considered an "exact" datatype, not to be confused with a FLOAT.

My solution is to convert the two fields to Strings and do the comparison. If you don't like that, then explicitly round the numbers using the Calculator step. The Select and Alter step, doesn't seem to truncate the numbers and I don't think it was intended to alter the raw data anyway.

I haven't had time to figure out if or how PDI is converting the numbers, but my gut feeling is that the road to flakiness passes through the MySQL jdbc driver installed with PDI 3.2. Its somewhat old, and my experiments with newer versions (of the driver) introduced new problems.

The link below is an example tranformation ( version 3.2) showing what I am talking about. See the Generate rows step and notice the very small difference between the two numbers

> numeric_compare_filter_values.ktr

Enjoy.

Sea trials begin for new data warehouse

2010-03-04T05:03:00.002-07:00

Over the last few months I've been working on the ETL for the first phase of a new data warehouse. Today this will be deployed to production and subjected to a burn in period. During this time, I'll be watching performance and data quality. Surely there will be some refinements in store.

The ETL is performed exclusively using Pentaho Data Integration ( 3.2.0 GA) on an Open Solaris / MySQL 5.1.43 platform. Source data is extracted from 3 separate production systems to a staging area, and then posted to a fact table with 8 dimensions of various types, including slowly changing hybrids.

Although I haven't blogged in a while, I have been keeping notes of my travels and I'll be publishing those little by little.

Data Quality -- Manufacturing Perspective

2009-11-09T21:10:00.005-07:00

Over the years I’ve had many conversations, and a few arguments, about the subject of Data Quality. The topic often arises after an executive suffers a brain explosion during a how-could-this-happen? meeting.

Some folks look at Data Quality as the effort to find problems in the database, and once they are found and fixed(maybe), case closed, game over. Actually, to truly improve the quality of data, you'll need a bit of a corporate lifestyle change, and some pain and resistance to go with it. To realize what you are getting into, it's probably helpful to view information systems in a different light. So lets step away from the computer for a moment and let this thought simmer:

If you have data intensive operations going on in your organization then you are manufacturing one or more information products.

Reflect on this analogy [1] and notice how it changes the view of your company. The software, operating systems, and databases are the machinery. The entire staff becomes Customers – functionally dependent consumers of at least some of your information products for day to day ops or strategic decision making. Your paying customers suddenly become consumers of products you don’t even sell such as web-based order entry or any website with a “Customer Login” screen.

The manufacturing perspective expands your product line too. Think of every quantifiable unit of information provided to the Customers and you’ll see a whole new product list. Consider every information product that your organization manufactures and ask yourself a simple question: Is it right?

For Reference
1. The manufacturing perspective isn’t my idea. I stumbled on this back in 2003 and I think it is still very relevant as a foundation for a data quality system:
A Product Perspective on Total Data Quality Management ( Richard Y. Yang)

Setting up Development and Production Pentaho PDI Repositories

2009-10-26T22:40:00.000-06:00

I’ve been setting up a Pentaho Data Integration system with the goals of supporting collaboration with my team, allowing easy deployment to test or production, and enabling remote monitoring and troubleshooting of jobs and tranformations.

I’ve finally figured out a way to achieve these goals, so I’ll try to pass this on now. I found the book "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL", by Roland Bouman and Jos van Dongen to be a big help in figuring out how to export/import. It definitely helped me get up and running quickly.

My first decision was to bet the farm on the use of a repository. A file based system would probably work, but I felt that it would require too much file distribution and usage of remote terminals. So I’ve setup two separate repositories hosted on MySQL databases: One for development (DEV), and one for Production (PRD). Here are the steps I followed.

Create the DEV repository first and then use the Repository Explorer to configure the database connections. Simply add each connection, one each for each database referenced by your ETL process. Configure these connections point to the development database instances. The gotcha I found was that any local connections I had defined outside the repository were eventually loaded in the repository. The workaround is to simply stop maintaining connection information in your user Kettle configuration, which is maintained in the file: ~/.kettle/shared.xml. I removed this file after deciding to “Go Repo”. So we have Recommendation #1:

-- Use the repository to store database connections and do not use localy defined connections.

In the repository explorer, create your user accounts. I created separate “administrative” accounts for my colleagues, and a special “etl_user” account to be used only for execution ( using kitchen.sh) of the jobs.

Create the Production (PRD) Repository. You can cheat a bit, by using mysqldump to backup your development database and simply restore it on the host database that will be used for the production repository.

Connect to the new PRD repository and open the repository explorer. Then modify the existing database connections so that they have the same name, but point to production instances of the databases. It is very important that you not change the name of the connections. Your jobs and transformations will reference these “standardized” names. When you deploy a job to PRD ( explained below), you will not have to change the database connections if you do it this way.

Now you have two (almost) identical repositories, one for Development, and one for Production. So what are you waiting for? Start developing!

In the new development repository, create a subdirectory for your first real job and its transformation steps. This will help exploit the export functionality of Spoon. It is very easy to dump all of the jobs in a repository subdirectory. And that brings me to my next recommendation:

-- Bundle a job and its individual steps ( other jobs and transformations) into a single repository subdirectory. Basically anything that is used by a single line invocation of kitchen.sh belongs in this subdirectory.

After development and testing of the job is done, you are ready to deploy this to production. This is a 2 phase process: Export your job and its components from the DEV repository, then import into the PRD repository. First some prerequisites. We need to tidy up the global options of Spoon. In spoon, choose the menu option:

Edit -> Options

Make sure the following two options are checked:
“Only save used connections to XML”
“Ask about replacing existing connections on open/import”

And UNCHECK ( please or this whole process will break) :
“Replace existing connections on open/import”

Now you are ready to export your development job. Using the repository explorer on your DEV repository, expand the “Jobs” folder. Select the sub-folder you want to export. Right click on it and select “Export all objects to an XML file” . Using the displayed dialog box, browse to a temporary directory where you want to save to, and give the file a name. Any name will do. For good luck, call it johndz. Click OK. This automatically includes the transformations that are in the jobs. A very cool feature in my opinion.

Disconnect from your DEV repository.

Connect to your PRD repository, and open the repository explorer. The repository explorer has a single menu item: File. Select the menu option:

File -> Import repository into directory

Navigate to the file that you just exported and Click OK. A dialog “Directory Selection dialog” appears. Select the root “/” line, and click OK.

For first time imports, a new dialog appears: “Create Directory” . This will ask to create a new repository directory. Answer “Yes” .

The import will proceed. Sometimes it can take a few minutes. When the last line of the “Repository Import” window says “Import finished”, you are done.

To invoke your new job using kitchen, reference the repository, and the job. Here is a sample kitchen.sh line out of my script. ( remember that kitchen.sh references ~/.kettle/repositories.xml ) :

#!/bin/ksh
installdir=/usr/local/pentaho/data-integration/
${installdir}/kitchen.sh -rep=dev_pentaho -user=etl_user -pass=whatever -dir='source_customers_to _staging' -job='job_load_stage_customers' -level=Basic

Displaying stored procedure result set meta data in Pentaho

2009-10-15T12:06:00.002-06:00

This probably won't be a very well written post since I am working frantically on a proof of concept using Pentaho Data Integration / Kettle for the etl in a new data warehouse project. I have just a couple days to get it to work or I'll end up having to use perl...which will make me hurl.

I want to use a mysql stored procedure for the transformation input, which is easy to do with the "Table Input" step ( just CALL the stored proc in the SQL section), but the field names of the result set don't show up downstream in subsequent steps. When I right click on a downstream step and select "show input fields", an "I Can't find any fields" messagebox pops up.

Some may find this a minor annoyance, but it makes subsequent steps difficult to deal with if you cant visualize the structure of the data stream in the transformation.

I saw some posts recommending the use of a "Select Values" Step, but for some reason, I could not get that to work. I spent way too much time trying several different permutations in this step.

This can't really consider this a bug since it seems unreasonable to expect software to reverse engineer a stored procedure to extract result set metadata.

RESOLUTION:

My work around seems to be working ok so far:

1) Create a "Generate Rows" Step, with 0 ( zero) rows output. In this step, configure the fields and datatypes to match the result set of your stored procedure. This step is essentially nothing more than a specification.

2) Create a "Table Input" step that calls your stored procedure.

3 ) Create an "Append Streams" step that merges 1) and 2) above. Set the Head Hop to 1) above, and the Tail Hop to the step from 2) above.

4) create your downstream step and create a hop to it from the "Append Streams". If you right click on this step and select "show input field fields", you can now see the structure of the stream.

Here is a rough picture:

Grilled Dove

2009-09-21T23:03:00.000-06:00

Dove season opened on September 1 for Colorado hunters, and I was anxious to get my new 12 Ga. out in the field. I've never hunted for doves before, but my friend Mark promised a fun day and something good to take home for dinner. 130 or so shots later, I have some nice dove breasts in the freezer.

Sadly, it looks like I will be eating them myself. It turns out that friends and family alike find doves to be similar to pidgeons: slow, ugly, sidewalk grazing, garbage eating, and rather un-appetizing.

For some reason, The Dove never made it into the class of sexy game birds such as Pheasant, Grouse, or Quail. I think I'll try to give a little boost to my new friend, the bird of peace.

First of all, they fly fast and furious, rarely stopping, except to laugh at me after I've emptied my gun. So lets call "the slow thing" Myth #1.

As far as eating habits go, the 35 acre property that we hunted on was lush with wild seed bearing plants, and when we cleaned the doves we'd shot it was amazing to see how gorged they were with these seeds. So, I'd say that qualifies as Free-Range, 100% Organic.

On to my favorite part of hunting, which happens to take place in the kitchen. To me, wild game is a treat, and should be prepared accordingly. I couldn't really find a good recipe on the internet, but I did find one that sounded good for the ever popular Chicken, which just so happens to be the gold-standard for determining fitness for human consumption.

This recipe comes from "Webers Big Book of Grilling" by Jamie Purviance and Sandra S. McRae. Its called Orange-Pine nut Chicken Breasts, but I've adapted it for the far more tasty Dove.

Combine 1/2 cups each of Orange Juice, white wine, and throw in a tablespoon each of minced garlic, finely chopped Italian Parsley, and Olive Oil. Finally, add a teaspoon each of grated orange zest, dried thyme leaves and a half teaspoon of Kosher Salt.

Mix all of those ingredients together and marinate the dove breasts for at least an hour ( I used a plastic sealed bag).

After that long hour, I skewered the breasts and lightly brushed on some olive oil. (Save the Marinade!! )

While the breasts were grilling on indirect medium heat, I toasted 1/3 cup of pine nuts and then added the entire contents of the marinade, along with a few squirts of honey. This was simmered very gently until it thickened. I also brushed some of this on the dove breasts when they were close to being done.

In spite of slightly over-cooking the doves, I have to say this was my finest hour on the grill. The taste was pleasant, not too gamey ( even though I like "gamey" ), tender and lean. And the pine nut sauce defintely added an exotic flavor.

file io tracing on centos ( part 1)

2009-08-26T19:53:00.000-06:00

I'm now working in a CentOS Linux environment, and since my Solaris days are over, I'm feeling a bit handicapped by not having DTrace and iosnoop. It still amazes me that monitoring MyISAM table data and index IO is not more common, especially in heavy duty environments.

My search for an analogous tracing system on Linux lead me to strace. I have been playing with this a little and although I am disappointed overall, I have been able to figure out how to gather what I need. It isn't pretty.

So far, my favored invocation of strace on an active mysqld process is :

strace -q -tt -e trace=open,close,read,write,pread64,pwrite64 -s 256 -f -v -p 2>&1

strace by default directs it's output to STDERR, unless you specify a file ( -o ). I wanted the output to go to STDOUT so I can pipe it into a script that will roll up the output and report by the minute. My goal will be to print an example output something like :
(blogger doesn't usually honor my formatting..not sure how this will look)

time file MbReadMin ReadMin MbWriteMin WriteMin
==== ========================
13:04 /mnt/mysql1/customers.MYI 26 134 0 0
13:04 /mnt/mysql1/sales_summary.MYD 0 0 15 124

This involves a bit of parsing of the output depending on the syscall. The open() syscall gives the actual file name and the "id" , which can be cross referenced with the pread64() and pwrite64() functions, where I am getting the size.

The problem is that you can only get the file name if the table was not open before you invoke strace. And beware, this is a cpu hog.

parting shots

2009-08-15T08:52:00.000-06:00

This was my last week working at Wiland Direct and as a parting shot, I really wanted to get this project out of the way. The adventures started on Saturday with the rebuilding of a data warehouse server. The goal was to convert the existing ZFS file system to UFS, and standardize the disk layouts of the MyISAM tables to a new scheme that was more friendly to our day to day processes. This dw server, one of 5, was an experiment with ZFS. My beef with ZFS was that I could not trace table level data and index IO using Dtrace ( http://opensolaris.org/jive/thread.jspa?threadID=70175 ) .

The game plan was pretty simple:

Halt Processing
Backup
Validate backup
Shutdown MySql
Capture "before" catalog of all database files. ( my backup system does this).
Remove existing file systems and database files.
Create new file systems.
Touch and symbolic link "empty" database files to the default data directory.
Restore ( cp -p honors symbolic links)
Validate restore.

This went well. And it was so much fun, that we decided to do the remaining 4 servers, at the same time, on Tuesday night. It was a long day, but I went fishing during the backup phase and caught this guy:

When all was said and done, 5 transaction servers, with 53,000 tables consuming 2.5 Tera-bytes of MyISAM data and index were backed up, rebuilt, and restored.
-----------------------------------------------------

Friday was my last day before starting a new job at Lijit Networks. The many kind wishes from my former colleagues leaves me truly humbled. This was a really great gig with some awesome folks.

On-the-fly Mysql Query tracing with DTrace

2008-11-14T07:07:00.001-07:00

Hearing that the MySQL database server is delivering corrupt data is not how I like to start the day, but that was the greeting when I arrived to work a couple days ago.

The claim was that an INSERT...SELECT query buried in a batch process was producing a different result than running the same query manually via the mysql client.

Since this is a production system, and a busy one at that, we don't keep the general log enabled. Restarting the server to enable logging is not an option during peak loads either.

Luckily, I stumbled on a DTrace script out there in internet land and it let me see exactly what was happening. I wish I can remember where I got it so I could give due credit. Here is the script:

#!/usr/sbin/dtrace -qs
pid$1::*dispatch_command*:entry
{
printf("%d::%s\n",tid,copyinstr(arg2));
}

Thats it. Just execute it on your Solaris 10 server passing the mysqld pid as a command line parameter and you see each query issued to the server. When you have seen enough, just cntl + c to quit.

I used this to trace the batch process and in no time, we determined that there was not one, but two inserts buried within. MySQL was acquitted. Case closed.

----------
This hardly scratches the surface of what I believe DTrace can be used for. I've used it a bit to trace some function calls in mysqld ( using the pid provider), and it does offer an interesting glimpse into MySQL's inner self.

Preventing self-inflicted wounds in MySQL

2008-09-25T20:43:00.001-06:00

My 8 year old son is learning to ride a skateboard and his most recent trick was to skip wearing a helmet. As a father, I had no choice but to enforce that safety requirement. I'll gladly be unpopular in order to avoid seeing my son with a head injury.

Today, a user accidentally deleted 100 or so rows from a critical table in the MySQL database, with the "aid" of one of the popular and free GUI query utilities available. While I loath the use of these utilities, I understand that sometimes ad-hoc updates are unavoidable, especially in new systems.

It wasn't a problem for me to recover, and actually in a sick way, I enjoy recoveries. Wierdly, I even practice them sometimes, mostly so I don't look like a fool when it's showtime for real. Fun stuff as long as the backups are good!

But the fact is that I would be negligent as a DBA if I didn't try to find ways to motivate our teams to operate safely. I wish the popular query utilities had built in safety mechanisms, but since they don't, I have asked the teams to execute these statements in our InnoDB databases before performing uncontrolled data manipulation:

-- requires explicit "commit" for changes to take affect.
SET autocommit = 0 ;

-- forces "well formed" dml queries
SET sql_mode='TRADITIONAL';

-- requires that you specify key values in the WHERE clause of your update statements
SET sql_safe_updates=1 ;

START TRANSACTION ;

insert...

update....

select row_count() as "rows_updated" ;

delete ....

select row_count() as "rows_deleted" ;

-- review results:
select....from...where....

-- Flip commented lines after initial review.
rollback ;
-- commit ;

Finally, I try to encourage folks to contact me ahead of time so we can coordinate a backout plan. Often I can make a hot backup of the data before the change so we can backtrack if things go wrong.

Every DBA's Nightmare -- Got backups?

2008-09-16T08:28:00.000-06:00

Here's a wakeup call to all the good folks on planet MySQL :

Denver Post: Gone: Comments...

Today's a great day to:

1) Review your backup logs.
2) Perform a recovery drill on a development server.
3) Validate the backed up data by running checksums, comparing tables or file sizes, etc.

MyISAM table level IO on Solaris using Dtrace

2008-08-15T06:03:00.000-06:00

I've stumbled around for awhile searching for a way to observe IO at the table or file level on a MyISAM engine. Since I'm running on Solaris 10 I decided to explore dtrace and in my travels I found some handy scripts that made it easier.

I used iosnoop which can be found here : http://brendangregg.com/dtrace.html#DTraceToolkit

iosnoop can be pointed at a specific pid, in my case the pid of the mysqld process:

iosnoop -v -p123456

Since the probe fires when IO happens, this will send out quite a bit of output to STDOUT. So I wrote a perl script to call iosnoop and summarize Read and write IO Counts and Bytes by the minute. I still have more testing and refinements but it seems to work correctly. I have probably over-scaled the bytes by converting to Mbytes.

Here is a sample output:

HR:MN FILE R_Mbyte R_count W_Mbyte W_count
00:08 /p732/cust/cust_xref_3.MYI 0 0 0 3
00:08 /p749/cust/sales_fact_6.MYI 1 152 0 0
00:08 /p744/cust/sales_fact_2.MYD 2 326 0 0
00:08 /p745/cust/sales_fact_3.MYD 27 3527 0 0
00:08 /p746/cust/sales_fact_4.MYD 12 1554 0 0
00:08 /p743/cust/sales_fact_1.MYD 4 572 0 0
00:08 /p738/cust/cust_xref_9.MYI 0 1 0 5
00:08 /p747/cust/sales_fact_4.MYI 1 214 0 0
00:08 /p741/cust/sales_fact_9.MYD 11 1546 0 0
00:08 /p740/cust/sales_fact_7.MYI 1 175 0 0
00:08 /p747/cust/sales_fact_5.MYD 4 596 0 0
00:08 /p743/cust/sales_fact_0.MYI 0 130 0 0
00:09 /p745/cust/sales_fact_2.MYI 1 166 0 0
00:09 /p737/cust/cust_xref_8.MYI 0 0 0 7
00:09 /p742/cust/sales_fact_0.MYD 15 1963 0 0
00:09 /p749/cust/sales_fact_7.MYD 4 653 0 0
00:09 /p742/cust/sales_fact_9.MYI 0 114 0 0
00:09 /p748/cust/sales_fact_6.MYD 11 1514 0 0
00:09 /p744/cust/sales_fact_1.MYI 1 220 0 0

Valley Cover Gasket Replacement

2008-02-03T00:21:00.000-07:00

My Dodge Diesel developed an oil leak on the drivers side of the engine, just below the head gasket. At first I thought it might actually be a blown head gasket, but after looking deeper I learned that it was leaking around the gasket of the "valley cover" also known as the Pushrod Cover.

This was definitely the most daunting repair I've ever attempted, mostly because I also had to remove the vp44 injection pump. I looked around on the internet and found this instruction video, and I followed this procedure as precisely as a could...right down to replacing the camshaft position sensor as he recommends.

What made this project so interesting was:

This was my first winter in Colorado and it was freakin' cold. I had to do the repairs in my driveway between snow storms.

I got to do it twice. Yes, twice. After I got the entire system back together, I started the engine and it still leaked oil. As it turned out, the actual cover was slightly warped. So the second time around, I replaced the cover as well as the gasket. The cost of the pushrod cover was something like $40...so if you have to do this repair, I'd recommend you splurge and do the same.

I took some pictures, mostly to help in case I forgot how something went together.

Here the intake manifold is gone, next is the injection lines:

The injection pump is gone...the pushrod cover, with the rust stain is behind it.

I bought plastic caps to cover the ports on the injection pump. I placed the injection lines in plastic garbage bags and sealed them shut.... One spider can ruin a very expensive injection system:

Pushrods exposed after I removed the cover:

Running Engine after I got it back together.

Unusable space in MySQL Key Buffers

2008-01-31T20:33:00.000-07:00

What started out as a key buffer efficiency study turned to a fruitless search for ways to overcome a somewhat high ( ~20%) amount of unusable key buffer space. This behavior seems to be platform and version independent as I observed this with versions 5.0.45, 5.0.52, and 5.1.22 on Solaris 10, Windows XP and Linux.

I'm not the first person to discover this, but I haven't had much luck finding an explanation. Just like this poor soul whose post fell into the swirling vortex of the un-interesting:
http://forums.mysql.com/read.php?20,127934

Finding usage, and physical read/write information about individual key buffers is slightly obscure. The only way I know of is to run "mysqladmin debug" and view the information in the MySQL server error log.

The output looks like this:

Key caches:
default
Buffer_size: 16777216
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 0
not flushed: 0
w_requests: 0
writes: 0
r_requests: 0
reads: 0

custcache
Buffer_size: 27262976
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 23513
not flushed: 0
w_requests: 0
writes: 0
r_requests: 24479
reads: 24479

I experimented by creating and populating an indexed MYISAM table , and then binding the index to key caches of a varying sizes. My hopes where to stumble on a way to harvest that other 20 percent. I tried adjusting the division_limit, which changes the replacement strategy, but found this had no effect.

As a result, I've given up trying to use the entire configured key buffer size...for now. Instead, I am exploring how the "MAX_ROWS" option in the CREATE TABLE statement affects index size. I'd rather get some more empirical data before saying much more. But I have found that by sizing MAX_ROWS to be slightly higher than the actual number of rows, the index size is reduced and therefore can fit in a smaller key buffer.

The gory details of my experiments:
---------------------------------
TEST 1: KEY BUFFER TOO SMALL:
---------------------------------
customers index file is 52 meg. created custcache with 8 meg. loaded customers index.

set @@global.custcache.key_buffer_size=8*1024*1024 ;
cache index customers in custcache ;
load index into cache customers ;

Result from MySQL error log after running " mysqladmin debug":

custcache
Buffer_size: 8388608
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 7173
not flushed: 0
w_requests: 0
writes: 0
r_requests: 51795
reads: 51795

Ran query to see hit rate ( explain executed to show plan) :

select sql_no_cache customer_id from customers ;
mysql> explain select sql_no_cache customer_id from customers ;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
id select_type table type possible_keys key key_len ref rows Extra
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 SIMPLE customers index NULL PRIMARY 4 NULL 921338 Using index
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+

Result from MySQL error log after running " mysqladmin debug":

custcache
Buffer_size: 8388608
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 7173
not flushed: 0
w_requests: 0
writes: 0
r_requests: 113466
reads: 62146

Interpretation: The index was not completely cached so there were some physical index reads during the select statement.

---------------------------------
TEST 2: KEY BUFFER TOO BIG:
---------------------------------
restarted mysql server. created custcache with 80 meg. loaded customers index.

set @@global.custcache.key_buffer_size=80*1024*1024 ;
cache index customers in custcache ;
load index into cache customers ;

Result from MySQL error log after running " mysqladmin debug":

custcache
Buffer_size: 83886080
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 51795
not flushed: 0
w_requests: 0
writes: 0
r_requests: 51795
reads: 51795

ran query to see hit rate:

select sql_no_cache customer_id from customers ;

Result from MySQL error log after running " mysqladmin debug":

custcache
Buffer_size: 83886080
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 51795
not flushed: 0
w_requests: 0
writes: 0
r_requests: 113466
reads: 51795

Interpretation: The index was completely cached so there were no physical index reads during the select statement. Note also that the blocks used section is very close to the actual index file (customers.MYI) size.

---------------------------------
TEST 3: KEY BUFFER SLIGHTLY LARGER THAN THE INDEX:
---------------------------------

restarted mysql server. created custcache with 54 meg. loaded customers index.

set @@global.custcache.key_buffer_size=54*1024*1024 ;
cache index customers in custcache ;
load index into cache customers ;

Result from MySQL error log after running " mysqladmin debug":

custcache
Buffer_size: 56623104
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 48346
not flushed: 0
w_requests: 0
writes: 0
r_requests: 51795
reads: 51795

ran query to see hit rate:

select sql_no_cache customer_id from customers ;

Result from MySQL error log after running " mysqladmin debug":

custcache
Buffer_size: 56623104
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 48346
not flushed: 0
w_requests: 0
writes: 0
r_requests: 113466
reads: 53099

Interpretation: There were some physical reads. Even though the key buffer should be big enough to hold the index, there is some empty space.

MySQL version 5.0.52 Upgrade

2008-01-14T21:06:00.000-07:00

Last night I performed an upgrade of 5 of our production servers to version 5.0.52 ( solaris 10 / 64 bit x86). We have been testing for over a month and in the 11th hour discovered that there is a bug in the mysql command line utility.

I was creating a blank copy of one of our production databases ( schema only) on an upgraded test system using a script generated by our design tool. During this, I encountered an interesting error that prevented the creation of a table with a column named "extra_file_delimiter". Further testing revealed that the 5.0.52 through 5.0.54 releases have a bug in the mysql client which parses the word "delimiter" incorrectly.

As a work-around, I've also deployed the 5.0.50 release in a separate directory and replaced the 5.0.52 mysql utility with a symbolic link to the 5.0.50 version. Ugly for sure, but we decided to do that since we had so much time invested in the pre-deployment testing of the 5.0.52 version.

MySQL tech support has created a bug for it so I'll have to wait and see where that goes.

Nevertheless, the upgraded servers appear to be performing well. The only real glitch I have been fighting is the fact that the MySQL Enterprise Monitor agent won't run on one of the upgraded servers...the other 4 work fine so right now I'm baffled.

SQL_MODE and MySQL Data Quality

2008-01-02T22:08:00.000-07:00

As my former boss will attest, I have a reputation for being a bit of a data quality zealot. The storage of data that is unfit for use leads to many problems, but I suppose that’s another subject for another day.

It’s tough enough to manage data quality problems introduced by source code errors, system failures, and requirements misunderstandings…But a default installation of MySQL introduces a new and exciting way to give us data quality evangelists fits: It allows unfit data to be inserted in the database. That’s the bad news. The good news is that by making a simple configuration change you can prevent this, and override the setting when you don’t care.

In a default MySQL installation, the value of the SQL_MODE system variable is set to ‘’. This allows you to force inserts and updates that may violate the intended design of the table. This point is more philosophical than technical, but in a mission critical database I believe that your first line of defense against poor data is requiring well formed data manipulation statements.

Here is an example:

Create table jd_test ( id int NOT NULL , name varchar(30) NOT NULL ) ;

Insert into jd_test ( id ) values ( 1) ;

Select * from jd_test ;

In my opinion, this insert is ill-formed since it is missing non-optional columns in the specification. Therefore, I prefer that the statement fail and return an error to the application. In this example, with SQL_MODE = ‘’, MySQL returns a warning but the data still gets inserted with an empty string assigned to the name column.

Try that example with a DATETIME column and you will see similar behavior.

Recommendations:

First of all, check the value of SQL_MODE to see what it is set at. This is defined at both the global ( server-wide) and session level. Some third party utilities ( like SQLYog) set the session level value to ‘’, overriding the server value.

Set the global value of the SQL_MODE to ‘TRADITIONAL’ in the server configuration file ( /etc/my.cnf). Hold it! Be careful here because if your MySQL database serves an existing production system, then you may have to test the effects of this in a development or test environment first to see what breaks. For new databases, start off with this setting on your development, testing, and production systems. It’s best to be in the habit of working with this setting before your system goes live in production.

Encourage your developers to explicitly check the session value of this variable :

Select @@session.sql_mode

Discourage development of application code that specifically overrides the value of SQL_MODE at the session level with the exception of clearly defined processes requiring it.

When developing stored procedures and triggers, explicitly set the SQL_MODE to ‘TRADITIONAL’ at the top of the source code file before the “CREATE…” statement. These server objects enforce the value of SQL_MODE in effect at the time of creation.

Set session SQL_MODE = ‘TRADITIONAL’ ;
CREATE PROCEDURE myproc()
…..

Recommended Reading:

Section 5.2.6 “SQL Modes” of the Mysql 5.1 Reference Manual