Clearspace Server Syndication Feed

Manhattan Skylines

dbirmingham — Tue, 02 Mar 2010 16:59:58 GMT

Marcus Gray watched in consternation as the viral program cranked up. He knew that in moments the band of hackers would once again take over the Manhattan power grid. For now, they were doing it as a prank. But he also realized it could be a test run for something even bigger. Like a grid-by-grid shutdown of the entire system, opening the door for untold mayhem on the darkened streets.

Moments later, messages from the hacker gang started appearing on all their terminals. Taunting barbs letting everyone know that they were in complete control and nobody could stop them. Gray shook his head and closed his eyes, hoping that this would pass quickly. Losing power even in one part of the grid could spell pandemonium and place lives and fortunes at risk. The weight on his shoulders was crushing.

"I think I can help," said a voice from behind. Lane McBride from the Federal Counter-Terrorism Unit based in Manhattan, leaned over to regard Gray's terminal.

Gray turned to the voice, recognizing it with hope in his eyes, and said, "They're at it again."

"I saw the precursors," McBride noted, "That they were entering the system."

"Yeah, but it doesn't matter if we can't find exactly where they are," Gray sighed, shaking his head, "They're in a hundred different buildings, including the Empire State. You guys have agents standing by at all of them, but they have to search the buildings floor-by-floor to find them. The problem is, we have to shut down communications for the building so that they can't warn each other. So even if we could catch a few, do you have any idea how long a floor-to-floor search takes in the Empire State? We can't keep that building offline from communication for that long."

"Not to worry," McBride grinned, "I have an algorithm that will directly pinpoint their floors. All we have to do is send our officers up to the floor, and I bet we can round them up in minutes."

"Wow," Gray whistled, "I'd like to see that."

McBride whipped out a flash stick, plugged it in and let the program do its work. Within seconds, it had pinpointed each hacker, the building their signal was coming from and the floor of the building. "Here we go."

"I like it," Gray grinned.

McBride touched several buttons on his phone and dispatched the information, and monitored as each of the officers acknowledged the information and the plan. "We'll know soon enough."

Gray noted, "The problem has always been that they could hear us coming and could shift floors anytime they wanted."

"Not this time," McBride smirked, "At least, not if we do it right."

The first officer to report back was from the Empire State. Two of the hackers had been stationed there on separate floors. Both were now in custody and unable to warn their cohorts in the other buildings. Gray listened in awe as one by one, the officers reported in, having captured their respective quarries with minimal effort.

"That was brilliant," Gray stared at the screen as the weight seemed to lift from his shoulders, "How did you come up with the algorithm?"

"Simple process of elimination. I just looked at the problem from a very-large-scale search. The most important information is where the perps aren't - not where they are. The algorithm zones in on the candidate floor by understanding which floors are not candidates. Process of elimination leads the way. So we can search the Empire State and Chrysler buildings just as quickly as a single-story, capture the floor number and we're done."

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

Some of you already see the parallels. It's how a zone map works. But how does it apply?

When we take a look at the Record Distribution option in the Netezza Admin GUI, we're often happy with a "ragged edge" for all the SPUs. And a "flat top" is the ticket. But what about the case of a "Manhattan Skyline", where we have high peaks and low valleys? This is higher than normal skew (something we're supposed to avoid, right?) People see those and shun them. However, these are often the natural result of an intermediate table produced by an ELT operation, and often a result of multi-pass queries in a BI tool. These usually leverage the mainstay workhorse CTAS (Create-Table-As-Select), so in many cases, people are tempted to turn on "random" for all CTAS operations. Or just maybe - one of our regular static supporting tables is deliberately distributed as a Manhattan Skyline just because we want to regularly perform co-located joins with it on larger master table using the same distribution key.

In any case, a primary reason we would get this kind of Manhattan Skyline distribution is if we are trying to preserve an existing distribution in order to perform a follow-on operation with tables on the same distribution. Whew! And why would we allow this to continue? Isn't a random distribution better than a Manhattan Skyline? Our problem remains: if the table has such a Manhattan Skyline distribution, we have higher than normal skew. Any full-scan on the table will cause the query to perform as slow as the "tallest bar" (the SPU with too much of the table's data). As the table grows in size, the problem worsens. It is not a scalable distribution in its latent form, so don't embrace one without a plan.

Well, random distribution has a risk too, especially at the BI level, of negatively affecting concurrency performance. Even if our individual queries are not hindered by the data-broadcast incurred by the random distribution, they could just be a one-hit-wonder, because running many of these operations side-by-side can sometimes saturate the inter-SPU fabric, affecting concurrency. If we can keep the processing on the SPUs, we can avoid this problem entirely. So the issue is one of user scalability, something that all of us care about and that the other vendors (sometimes) turn a blind eye to. Netezza has it covered, and as usual, it's so simple a cave man could do it (now I'll get mail!)

So now we have two options, neither of which seem good - (a) keep the Manhattan Skyline distribution or (b) use a random one. Let me say that random is not always bad, but it poses a potential danger for concurrency. Likewise the Manhattan Skyline can often be a latent result of an intermediate CTAS so is unavoidable anyhow. And why would we want to preserve an existing distribution on a CTAS? The answer - because it will be a co-located write (blazingly fast). But wait! Don't we get a co-located write by default?

Maybe.

I have noted in prior posts how the default distribution for a CTAS might not be what we want or expected, so here's a quick recap:

(a) For simple single-table CTAS, it will preserve the source distribution key - (co-located write)

(b) For simple multi-table-join CTAS, it will leverage the first column result in the "select" clause (maybe a co-located write)

(c) For CTAS using summaries/group functions in the select, it will leverage the columns in the "group-by" clause (rarely a co-located write)

If any of the above are not the original distribution of the source(s), we could inadvertently sacrfice our co-located write. But we can preserve it if we specifically use "distribute on" with the CTAS execution. With co-located writes, this means the data never leaves the SPUs. If we distribute the CTAS on anything else, the data must leave its current SPU and find its way to another one. This initiates a data broadcast (and can negatively affect concurrency). Preserving the distribution, we get the benefit of a co-located write (avoiding broadcast to make the table) and set up the next operation for a co-located read (also avoid the broadcast to leverage the table). Short answer: preserving the distribution preserves concurrency performance. Now the SPUs are working for us at physics-speed.

Rather than just live with the latent effects, lets embrace and harness them for the good of all mankind. Well - er - at least for our user base.

What we really want is threefold -

(1) preserve the distribution with a co-located write (preserve concurrency, potential Manhattan Skyline as latent artifact)
(2) leverage the result with a co-located read (preserve concurrency, potential penalty from Manhattan Skyline)
(3) mitigate the Manhattan Skyline with a zone map (ahh, best of all worlds)

So to get the first two, we can simply preserve the distribution with a "distribute on (key)" clause and make sure the distribution key is part of the "where/join" operations.. This is the simple part.

To get the third, we need to either (a) sort the data as it is created, or (b) make a materialized view after-the-fact to get the zone map effect for selected columns. The first one (sorting) is often easier than it sounds, and with strongly filtered intermediate tables is also very scalable. The second one (materialized view) has some caveats but is very fast to create. What does the zone map actually do? It effectively stripes each SPUs portion of the table so that only the section in the zone is actually addressed. Like McBride's algorithm, it's as though the rest of the data isn't even there, because the zone map has guided the optimizer to completely ignore it. So whether the SPU's data has a tall bar or a short bar, the performance is the same. We need all three of the above and the zone map mitigates the potential problem of unexpectedly high skew from an intermediate distribution - or an outlier table that we need to distribute on a common key. Even if (1) and (2) above give us a good distribution today, it could always "go Manhattan" in the future.

Another obvious question is "If this is an intermediate result, why bother? Just filter out the stuff I don't want and then there's no issue, right?" Well, technically yes, for a single operation, but I know of at least a dozen cases where the intermediate table is used for a lot of downstream activity, not just a one-off throwaway. So our stewardship rule is: make the data better. For the next downstream process or the ultimate data consumer, the data should get better every time we touch it.

Rather than rewrite or re-design a carefully tested and detailed process, adding a simple "order by" or MV is easy and preserves the existing logic, and data model, with little impact and high return. This is especially true of a static supporting table, because we can install what we need on the table's creation. The consuming processes all benefit from it with no more than regular query execution (materialized views are transparent).

In the end, we can still leverage the plain-vanilla parts of the Netezza performance model (zone maps, co-location) without having to over-engineer the data using indexes, intersection tables or summaries. This preserves something more - the ongoing resilience and adaptability of the model itself.

Recap:

Apply the "distribute on" clause of the CTAS to avoid the latent effect of default distribution.
Preserve co-location for reads and writes in intermediate tables.
If a potential Manhattan Skyline distribution is the CTAS result, rather than go random, sort the CTAS result by a selected column or use a materialized view.
As always, apply strong filters to the CTAS creation so that it's not simply copying one table's contents to another (carve the data out).
Experiment for the best fit, but remember that Netezza is an appliance.
We don't need to engineer the queries, only apply simple performance model alignments in the data itself, to leverage the machine's physics

Half-day Best Practices session led by David Birmingham - included with Enzee Universe registration

Brian — Mon, 01 Mar 2010 17:23:27 GMT

Enzee Universe 2010 attendees can sign up to participate in a pre-conference Best Practices forum led by David Birmingham - author of the Netezza Underground book and Gather 'round the Grill blog, and Senior Principal Consultant at Brightlight Consulting.

During this half-day session from 8:30am to 2:30pm EST on Monday, June 21, 2010, David will share insights and best practices on the topics outlined below and then open each for discussion and QA.

Space is limited, so be sure to sign up for the Pre-conference Best Practices session when you register for Enzee Universe.

Topics for discussion will include:

Migrating an Existing Data Warehouse to Netezza
Super-charged Data Models
Holistic Performance Planning
Flexible Hierarchies, Dynamic Alignment, Snapshot Fact Views
How-to Examples
MViews, Views, and Aggregations
ELT How-to's
Recovery
Data Intake and Staging Strategies
Schema Switching, Replica Tables
Data Management, Horizontal and Vertical Partitioning, High-traffic Denormalization

Register today to hold your spot.

Training Complete

mbaum — Fri, 19 Feb 2010 18:25:43 GMT

Training is now complete. Our tribe is formed and we are ready to perform our mission. Over these first few months of active duty, I have received specialized training on how to perform my specific job, as well as more 'general' training that allows me to work with the rest of the team when we are outside the wire. Now I'm itching to get to work.

I can best describe that training time as building an airplane while we flew it. Seemed like every week, new people joined the team from somewhere else, and had to integrate with the rest of the group. Of course, the group was already running at a fast pace, so the noobs had to work hard to catch up! This is where we learned what to do and what NOT to do. Or in Rumsey-speak, we learned a little about the 'known unknowns'.

From previous posts, it should be obvious that I've become very fond of my team. It's a special group, with people from various commands, different parts of the US (and some parts not in the USA too!), active and reserve military services, even different cultures. We got it together quickly and rocked this training from start to finish. I LIKE these people. We work hard, we play hard and when the times comes, we'll fight hard. These are the kind of people, when thrown together by random circumstance, become lifelong friends. That doesn't always happen. I'm glad it happened here.

We can't finish our mission until we start. Fortunately, we've got the right people, in the right place, with the right gear and the right attitude, to make it a great start.

There be dragons - the SQL

dbirmingham — Fri, 19 Feb 2010 17:17:53 GMT

"You didn't kill it," fumed the customer, "You said you would kill it."

"We've had some, er, labor setbacks," said Bjorn, head of DragonSlayers Inc, a startup boutique firm from several valleys away.

"I don't see an excuse clause in the contract," the customer shot back, "Kill the dragon or we're done."

"The dragon can't be killed," said a rich Scottish voice striding up to meet them.

Bjorn recognized the stealthy character, by name of Connery, from the Information Superhighway Roadside Assistance Service.

"I didn't realize that RAS was in the area," Bjorn quipped, offering a hand to Connery.

Connery grasped Bjorn's hand and shook it once, "We're all over. Been doing a little cleanup of this or that."

"What's this about the dragon," asked the customer, "That it can't be killed?"

"Of course not," Connery smiled, "It's a dragon. It's immortal."

"Did you know this?" the customer glared at Bjorn, "Have you been stringing us along?"

"No," Bjorn defended, "We kill dragons. It's what we do."

"Well," Connery chuckled, "Not real dragons, anyhow."

The customer's lackey approached them with a small flagon of tea, poured a stein for each of them, and departed.

"The dragon is immortal," Connery muttered, sipping his tea.

"That's impossible," Bjorn said through a long gasp, "We've killed dragons before - we "

"But of course you have," Connery smiled dismissively, drawing another casual sip.

Bjorn stared at him, unable to form another word.

"If the dragon can't be killed," asked the customer, "Then what?"

"In the nether worlds, beyond the mapped regions, you'll see little notation There Be Dragons," Connery said softly, "And whether there be dragons or not, it's uncharted territory. Places no man has ventured, but rest assured danger lurks. Unknown to the uninitiated."

"So you know what lies in the uncharted territories?" Bjorn sneered.

"It's why I'm a guide and you're a dragonslayer," Connery huffed, "Whether you know your way or not, dragon chow comes in many shapes and sizes," he put his hands up as if to size-up Bjorn, "Many shapes and sizes."

"Funny," Bjorn quipped, but it clearly wasn't funny, "All we have to do is get close enough."

"Reminds me of a time," Connery said wistfully, "Once I knew a man who you could skewer a hundred times and he'd still get right back up."

"Ahh, the Highlander," said Bjorn, "I've heard of him."

"Well, he never lost his head," Connery huffed, "Or that would've been the end of him."

"What are you saying?"

"The treacheries of the lands beyond are many. You have to keep your wits about you. Keep your head."

"Keep my head, got it," Bjorn said sarcastically, "Anything else?"

"You need to deal with the whole dragon," Connery advised, "Not just the part you wrap with that silly leash. It won't hold the dragon. Only a dungeon will."

"So we need an enchanter?" Bjorn smirked.

"In no uncertain terms," Connery said, laughing, "You have a go at that dragon on your own. Go in there with no more than an enchanter's bag of tricks, and he'll make an ash out of you!"

Bjorn gulped, "We'll see about that!"

One of the lackeys turned to the other and chortled, "He thinks he's James Bond!"

"What do you know about it?" Connery shot back with piercing eyes, "The dragon sends your consultants to the street and you send the dragon to the morgue. Is that how it's done in data warehousing?"

"Basically, yes," snickered a lackey.

Connery whirled, "No morgue will hold him." He turned to the customer and glared hotly, "What are you prepared to do?"

"Sign the contract," said the customer, quickly applying a signature. He stuffed the papers into Connery's hands and hastily departed, leaving the men to set sail and dispatch the dragon as soon as possible.

The boat ride to the dragon's coast was uneventful until the boat ran aground near the shore, screeching loudly against the rocks as its keel protested with a deep, gutteral groan.

"That's noise will stir the dragon," Bjorn bemoaned. He'd hoped for a more stealthy entrance.

"Hopefully only stirred," Connery quipped as he snatched up his bag, "Not shaken. Won't do to have him awake when we approach, right?"

"Coastline is enormous," Bjorn complained, "How will we ever pinpoint his location?"

"To find the dragon, you'll need to think his thoughts. Know your adversary. Know his heart."

"Yeah, Dragonheart," chuckled a lackey, "Seen the movie."

Connery ignored him and leapt from the boat onto the dry shore. "Welcome to the Rock," and then looked out over the vast, scorched wasteland, a product of the dragon's handiwork. He led the team up the rocky slope to the first rise, whipped out his spyglass and waved his hand to the others to belay their ascent.

"What's he doing?" asked one lackey to another.

"Lookin' around, I guess," smickered one, "Guess nobody told him that the dragon sleeps all day."

"What was that?" Connery whispered loudly enough for them to hear, "You think the dragon sleeps all day? Who are you kidding? Maybe you only struggle with him in his lair at night, but he breathes fire all day long. He never sleeps. He never dies."

"Where did we find this kook?" asked another, "He's as nutty as a fruitcake."

"He'll eat you alive," Connery sneered, trying to spot motion anywhere along the landscape before proceeding. In the distance, a dank mist arose from the ground near some caves. Connery zoomed in and spied dragon scales littering the ground. "Let's go."

The team made the tedious crossing without incident, until they stood before the open, reeking maw of the dragon's lair.

"Who wants to go first?" Connery chuckled.

"I will," said a lackey fearlessly, "I've taken down enough of these."

"But of course you have," Connery strode to the nearest large boulder while the others scattered for cover. After several tedious minutes, all of them could now feel the impact tremors shaking the ground, growing in intensity as the beast ascended from his lair to the cave's mouth.

Then the horns appeared, fifty feet from point-to-point as they slowly rose from the hole. Then the head,larger than a common city bus and almost twice as long. The dragon stared down the lackey for a long moment, then continued to ascend from the hole, growing larger and more hideous with each passing second until his entire upper body was revealed, from his head down to his midsection, standing over ten stories tall. He burst-extended his massive wing membranes with a loud, deafening snap, and then pointed his head straight up to gather a deep breath of air.

Connery reached down to pick up one of the many dragon scales scattered all over the ground. Five inches across and eight inches long, made of the most impervious stuff on earth. He flipped it over and shuddered to realize the dragon's age, betrayed in the scale's growth rings. Four thousand years, this animal had been eating and breathing fire.

The young lackey had forgotten to breathe. This dragon was orders-of-magnitude larger than any dragon he'd ever dealt with. In fact, the sheer scale of the dragon made him feel light-headed. Gathering his presence of mind, he took a defiant stance and shouted, "Begone, Dragon!"

Connery turned away, trying to hold back a snicker that could reveal his location to the dragon's attenuated senses.

The dragon pointed his nose straight down, cocked his head to the side, opened his mouth and released his breath. The column of high-intensity chemical fire blasted downward on the lackey, instantly reducing him to ash and causing the rocks all around where he'd stood to glow and almost melt.

Connery glanced over to the rest of the team, cowering behind the rocks in hiding, not believing that the dragon was so huge and powerful, and feeling completely beyond their depth. They stared, partly in awe and partly in concern, as Connery stepped out from behind his hiding place and boldly strode up to the dragon's cave.

The dragon once again drew breath into his nostrils to recharge his furnace, when Connery simply placed his hands behind his back and stared deeply into the dragon's eyes.

The dragon stared back, unable to comprehend the feeling of drowsiness suddenly overtaking him. He slowly lowered his head, then his body, down to the ground to gently lay next to Connery, unable to break his eyes away from Connery's deep, mesmerizing gaze.

Once completely settled, Connery reached out to tap the dragon's front jawbone as it drifted off to sleep, "There now," Connery said soothingly, "That's a good lad."

"How is this possible?" Bjorn gasped, stunned at how easily Connery had mastered the beast.

"Your friend told the dragon to leave," Connery huffed, "But the dragon isn't going anywhere. He lives here and you people don't. In fact, he's been around so long, and you people come and go so often, that he sees you as decorations, not even permanent fixtures in his home."

"But he just laid his head down and went to sleep," Bjorn noted, "How did you do it?"

"The dragon serves me," Connery said slowly, "Not the other way around. If the dragon needs to breathe fire, it's because we've not done a good job harnessing the dragon, not just because the dragon is mean."

"So dragon's aren't mean?"

"Oh, their born mean," Connery chuckled, "And they bite. Whom they bite and when, is ours to control. That's why we have dungeons. Places where the dragon will survive but under our control. Think about putting that dragon's breath to work in boiling water, making steam to run a turbine. Now the dragon is working for us."

"Can't be a happy existence for him."

"Happy? Perhaps not. Necessary? Most definitely. You came here to kill him or banish him. He knows his place. He only responds to someone who knows it as well as he does."

"You're an enchanter, aren't you?" Bjorn said, realizing Connery's identity.

"Some call me, Tim."

Post-conference education - included with Enzee Universe registration

Brian — Mon, 08 Feb 2010 19:02:31 GMT

Netezza users and partners that attend Enzee Universe 2010 have the option to take one of three post-conference training courses, regularly valued at $1950.

Netezza Data Warehouse Appliance Usage – for DBAs, system administrators, application developers and data warehouse architects that are new to Netezza
Advanced Analytics Inside Netezza – a brand new, limited availability course that will show analytic developers and data miners how to write and use compute-intensive analytics within Netezza
Mantra for Data Compliance – another newly available course to teach Netezza users how to leverage the Mantra product for data compliance

Space is limited, so register today to hold your spot.

Post-conference training will take place between 12 noon EST Wednesday, June 23 and 5pm EST Friday, June 25, 2010. Click here for more information about this year’s post-conference training courses.

Multi-Pass SQL and BI-Tool SQL Generation

dbirmingham — Mon, 08 Feb 2010 04:59:30 GMT

Many of those who integrate the mainstream BI tools into various underpinning data sources find subtle nuances. Not the least of which is how the database will respond to the queries presented. In Netezza data access especially, the power is not found in the query, but in the hardware. We can certainly degrade our experience with bad queries, but we would not tune queries in the same manner as with an SMP/RDBMS.

For example, I've watched RDBMS engineers work black-magic with a query by simply rearranging this-or-that in the monolithic query to provide boosts in the orders-of-magnitude. This is because the query is being used to guide the general-purpose physics. In Netezza, however, the purpose-driven physics snips the query apart. The physics then guides the query's mechanics. I've watched newbie Netezza folks nearly pull their hair out - and their eyelashes too! - when trying to "make the machine do what I want". Hmm, no, the machine does what it does. It's an appliance. We get what we want when we conform the data to the physics. The query is just along for the ride.

How does all this apply to multi-pass SQL in a BI Tool? Well, most BI tools come to the table with a pre-conceived notion that all databases are created equal. Unless they have specific VLDB hooks, and unless those hooks fully embrace VLDB principles, the BI tool will not experience the expected lift and we'll likely have to help it out. In fact, little about a BI tool is purpose-built in regards to its data source. It regards data sources as general purpose interfaces so it can be as vendor-neutral as possible.

Unlike a standard star-schema, many VLDB tables are fact-sized tables containing billions of rows, as are their dimensional counterparts. So a single one-shot query will sometimes provide the functional answer but with unacceptable performance. Many of us have seen multi-page (hey, 100+ page) queries that try to do everything in one shot. The average RDBMS leaves us few options. The VLDB and especially Netezza is not so constrained. We can make multiple passes on the data often with little penalty. The danger here is in the inefficiency of the passes, not whether multi-pass is okay. Multi-pass, or more appropriately multi-stage SQL, is a necessary approach with large-scale tables. Netezza makes it simple and fast, using built-in concepts of its performance model.

Here is a spot case-study - a BI tool needed to access several tables that were each in the many billions of records. The end result was a summary of user-selected values. The temp-table creation here is done automatically by the BI-Tool, so we may have limited options in getting it to shape them as needed. In the examples below, I'll label the queries so we can reference them later.

A typical BI tool, upon realizing it needs a summary, will often divide the answer into multiiple stages of work. Each stage will store its result in a temporary table using a CTAS, leveraged in one or more following passes. Unfortunately these passes are sometimes inefficient. In the case below (this is pseudo-SQL, so bear with me here)

(1a) create t1 as select region, district, store, sum(transaction_amt) sumtran, sum(transaction_tax) sumtax from transactions where district_id=4 group by region, district, store; (1 million records)

(1b) create t2 as Select employee_id, employee_name, t2.store_id from employee_master t2, employee_lookup t3 where store_id=6 and t2.store_id=t3.store_id (500 records)

(1c) select store_id, employee_id, employee_name, sumtran, sumtax from t1, t2 where t1.store_id = t2.store_id and t2.region_id in (41,42) and t1.store_id = 6; (450 records)

Note how in the above, the filter effects are largely applied last (1b and 1c) with the summaries applied first (1a). In this case, it is summarizing over a million values but it throws away over 90 percent of this result on the last operation, reducing 1 million records to 450. It is still accessing the larger table (transactions) only once. It just does it at the wrong time.

If we invert this chain and regard the filters first, we might see queries like this:

(2a) create t1 as select region, district, store, transaction_amt, transaction_tax from transactions where district_id=4 and region_id in (41,42) and store_id=6; (15,000 raw records)

(2b) create t2 as Select employee_id, employee_name, t2.store_id from employee_master t2, employee_lookup t3 where store_id=6 and t2.store_id=t3.store_id (500 records)

(2c) select store_id, employee_id, employee_name, sum(transaction_amt) sumtran, sum(transaction_tax) sumtax from t1, t2 where t1.store_id = t2.store_id; (450 records)

In the above, the filters are pushed into the first part of the query chain (2a) to squeeze down the data sizes, but to also glean out the raw values for the final summary (transaction_amt, transaction_tax). The (2b) query is still a filter, but by the time we get to (2c) all we really need to do is summarize based on the intermediate table results. We don't have to "go back to the well" of the larger table. Everything we need for the final result is already in our hands, and a much smaller workload.

The simple inversion of the query order has significantly reduced the workload of the entire chain of events. This of course, does not answer whether our BI tool will actually implement the query in this order or manner. Anecdotally, with the above tables the original "transactions" table was over 30 billion very wide rows. The first query chain (1a-1c) takes no less than a minute, but only because key1 is zone mapped. The second query chain (2a-2c) takes 6 seconds or less, and it better represents a flow of data from larger-to-smaller, like a common source-to-target flow. It is easier to visualize and manage, and is more efficient.

Note: Can our BI tool shape a query chain in this manner? Can it glean out in the raw columns to an intermediate table, later summarizing on the intermediate? Or will it always require us to summarize at the outset and then squeeze out from there? Some BI tools are very close to this model already.

Yet another pernicious issue is not obvious from the above - temp table distribution. This last query chain, though 6 seconds in duration, is still a one-hit wonder. Once two or more users start hitting the machine, concurrency will reveal all. The machine is quickly saturated and all of the queries start to take more and more time. In one case of just five users on the machine, all of the queries took over a minute, and one took over five minutes. Concurrency tuning is a bread-and-butter issue, too, so what's going on here?

In both query chains, the CTAS is not being given explicit instructions on how to distribute its results. The outcome is unpredictable from the BI tool's perspective, but very predictable for us. When the CTAS result remains distributed on its original distribution, we get a co-located write. If the CTAS does not use the original distribution, it will have to redistribute the data, broadcasting it all over the SPUs. We need to avoid this because co-located writes are desireable and muey caliente.

The original distribution key for the transaction table is (transaction_id). This doesn't do us much good if we are later focusing on the store_id (2b, 2c) as the primary distribution. In order for the final activities to be as quick as possible, we need to bridge the transactions into the store_id. We could set up data structures to do this, but in the end with so few records coming off the transaction table in the (2a-2c) chain, an intermediate broadcast is already in the mix. We can do it deliberately under our control, or allow it to use CTAS defaults. In this case, the CTAS default is worse.

In the first chain of queries (1a-1c), we would expect to see the following CTAS defaults:

(1a) - distributed on (region, district, store) because this is the group-by clause. It cannot use transaction_id for a co-located write because it's not even in the result set. Those who understand distribution keys know that this is not an optimal state of affairs.
-

(1b) - distributed on (employee_id) because it happens to be the first column in the select-clause. This query uses two tables in the join, so
CTAS will opt for using a column in the select clause.

So in this case, the CTAS will not preserve the original distribution or even a useful distribution. Don't get me wrong here. CTAS defaults are acceptable in over 90 percent of cases. This example is offered as a typical one-off of BI automated query construction. The first query (1a) will produce a million records (and honestly, some cases it produced a couple of billion records) we really need some optimization here.

If we were to take (2a) and (2b) above to deliberately enforce the distribution, we would use the "distribute on (store_id)", but we would have to include store_id in the result set. In each case, this would prepare both tables for the final query (2c) for a co-located join.

Note: This brings up another BI tool issue, in that we need to affect the order of the sequence, and also provide for columns that are adminstrative (like store_id) but not part of the final result. Some BI tools are picky this way. If the column is not required in the final reporting output, it trims or ignores the need for the column in the intermediate tables.

To continue, we have now pushed the workload into the physics, not the query itself. But as noted, concurrency is the test. This final chain of co-located queries then returned in less than 3 seconds, and did not grow beyond 4 seconds until 20 users were running the same query at the same time, and even then tended to hover between 3 and 5 seconds as even
more users were added. Isn't this the kind of scalable performance we want?

Additional note: If we really want to push this harder, it would be best for us to manufacture a "store_transactions" table that is distributed on the store_id already (for the 2a query). This would be a report-facing table that essentially mirrored the transactions table, but only carrying the high-traffic reporting columns. In this way, the store_id becomes the universal distribution even for the very first query. Keep in mind that while this strategy may cost disk space, it will further eliminate concurrency issues. I am not a big fan of preserving disk space when performance issues are in play. We will still need to perform a "distribute on (store_id)" for each (2a,2b) but it will preserve the distribution with a co-located write.

But we can see, the two protocols we will need in play from the BI tool is to use capture-filtration-summary, and then also apply distribution keys deliberately to the first passes to preserive distribution. We often apply these very same protocols in ELT because they make sense. But we have complete, detailed control of query construction in ELT, not so in the BI Tool world.

Conclusion: Rather than use a BI tool's default of summary-filter chain, what we need is capture-filter-summary chain. This guarantees that we can leverage the VLDB physics, but also moves the data from larger-to-smaller in the most efficient manner.

Recap for Multi-Stage SQL:

especially for summary data, should perform the summary as the final operation, with capture-and-filtration in the first passes. This allows the final operation to be a simple summary, since all the filtration has already been applied. In other words, no more where-clause activity apart from the join criteria.
Organize the tables (including additional tables) on the distribution key in play. Bridging one distribution to another can give us the performance, but if broadcasting it can eventually create a concurrency problem
the chain should not address the same large table more than once. Get everything we will need and get out - don't keep coming back for something the first pass did not get.
the chain should capture raw information into an intermediate table, foregoing the summary until the final operation.
should provide a means to bridge one distribution key into another, for maximum efficiency, rather than using CTAS defaults.
should perform filtration at the outset, as a method toward attacking the larger table(s) with zone maps etc.. Move from larger data sets to smaller ones.
should preserve distribution to leverage co-located write and read where possible. This maximizes overall performance but also optimizes concurrency.

What if the BI tool will not, as a general-purpose tool, perform these deliberate and purposeful query chains? At this point, we need to have a heart-to-heart with the BI Tool vendor stating our concerns. Assume the best, that the tool vendor may eventually fix the issue, just not in time to help us now. We then need to consider two purpose-built options, each of which has its own issues. These are offered in the spirit of temporary adaptation until the BI tool is smart enough to bypass them.

Summary tables: These are often constructed to prop up database performance issues. They are just as viable for functional reasons, such as providing data in a form that is only available and most efficient when summarized, or to intersect details with pre-summarized data. But if used as a performance prop or BI Tool helper, put some effort into making it an adaptation that could be deprecated when the BI Tool is smarter. This way, we're not committed to it forever.

Stored procedures: Used in an appliance as an adaptation mechanism (in this context). Effectively bridges the BI tool to the data with a temporary procedural construct (the procedure) rather than a more permanent structure (like a summary table). Stored procedures pull application features down to the database level and adapt the BI tool into the Netezza performance model.

When or whether to use either of the above is always a design decison, not necessarily dictated by the tools themselves. But keep in mind the idea of temporary adaptation. I am always of the mindset that the warehouse and BI environment must exist with the expectation of change, so in general, adaptability and adaptation concepts are always desireable. They allow us to be more responsive to future requirements

Catching up (and catching my breath!)

mbaum — Tue, 26 Jan 2010 15:05:51 GMT

Hello everyone, sorry for the huge delay but it couldn't be avoided. I'm at a training site that emulates a base in Afghanistan, and they take the limited bandwidth situation to the extreme. Both in terms of available downtime for me, as well as in terms of throughput to the InterWeb!

Since my lasdt update I have visited Fort Gordon where I completed the Brigade S-6 Communications Officers course. An interesting course, it taught me the ins and outs of the role I will play while downrange in Afghanistan and what will be expected of me and my staff while were are there. It was challenging, as I learned more about Antenna and RF theory then I care to admit, and the boring minutae of virtualized servers. Glad I did it of course, but happier still to graduate and move one step closer to starting my mission.

I arrived at an Army Camp in Indiana at the beginning of December. Here I joined the main element of my team and began training as a whole unit, rather than developing individual skill sets. The people have been great and I'm excited to be working with them. Safe to say that all walks of life are covered by this group, and they come from every corner of the US and its Territories worldwide.

In fact, I feel bad for some of the folks from the warmer climes, since we've had some shockingly cold and damp weather since getting here. It's almost a cliche about 'Army' training, but as it turns out, this base has the facilities and staff we need, but the terrain and weather couldn't be further from what we'll experienience in-country!

So far I've spent time training on driving and maintaining various vehicles, crew served weapons, combat lifesaver medical techniques and plenty of language training. To be honest, the language stuff is taking me a bit longer than I had hoped. We won't even talk about learning to read and write Dari at this point! I'll get there, hopefully sooner than later...

Probably worth mentioning this one observation: I am so impressed by my Sailors, Soldiers and Airmen. They have such positive attitudes, endure the rigors of road marches in the bitter cold, miss no opportunity to laugh and in general make me see what a privilege it is to be an American. If someone doubts if the coming generations have any promise, I'm here to tell you with absolute certainty; They Do. Great people, and I'm glad to be rolling with them on this mission.

I've been in contact with a few of my Netezza friends, and I'll be sure to call them at least one more time before I board the plane. I hope to make more frequent updates to the blog, and of course, everyone should feel free to ping me at narmychief@gmail.com if they so inclined.

Best of luck for a strong close to FY2010 and for a great start on FY2011! I'm looking forward to surfing the wave when I get back!

Enzee Universe 2010 - June 21-23 in Boston - Mark your calendars!

Brian — Tue, 26 Jan 2010 05:05:44 GMT

Enzee Universe 2010 is returning to its roots. This year, Netezza will host a single, multi-day user conference for customers, partners and prospects across the globe to get together in Boston from June 21-23, 2010. Here are some basic details that might get you revved up for the event:

WHO:	Netezza users, partners, and employees from around the globe, along with esteemed industry analysts and thought leaders in business intelligence and analytics
WHAT:	The premier data warehousing and analytics conference of 2010
WHEN:	June 21-23, 2010 (click to save to your Outlook calendar)
WHERE:	Westin Boston Waterfront, 425 Summer St., Boston MA 02210
WHY:	A value-packed agenda, complimentary pre- and post-conference education, priceless networking opportunities, and a free t-shirt!
HOW MUCH:	$475 More details, including a shell agenda, are available at www.enzeeuniverse.com...

Riding the Waves

dbirmingham — Wed, 20 Jan 2010 15:17:01 GMT

I've been noticeably quiet over the past weeks as I've switched horses, so to speak, and joined Brightlight Consulting. I had already been following Brightlight for a number of years, encountering their significantly talented people at various Netezza sites across the fruited plain.

the press release is here:

http://www.brightlightconsulting.com/news_2010_DavidBirmingham.htm

At the Netezza conferences this year, many of you saw the slow-motion videos of surfers mastering those monstrous waves. Also during this season, I happened to attend another conference where the speaker shared some famous words from Shakespeare's Julius Caesar in a similar context to what I was now experiencing right there on the conference room floor:

There is a tide in the affairs of men. Which taken at the flood, leads on to fortune;
Omitted, all the voyage of their life Is bound in shallows...

I was standing on the top of the wave, so to speak, and had a choice before me. Ride the wave, or return to the shallows.

Now, I don't put a lot of stock in epiphany-styled revelations, but in this case a tingle went up my spine, realizing that the TwinFin had completely changed the game - it was time to seriously get on the wave and ride it, or commit to the shallows of the everyday. As many of you know, Brightlight has stood out for a number of years as being a go-to partner for all-things Netezza, and their VLDB consultants have solved large-scale problems where others feared to tread.

"You have some serious thrill issues, dude," Crush the Turtle, Finding Nemo

As I have been inundated with pings and kudos from many of you who already know the story, I thought it was worth sharing, especially for you Shakespeare and surfing aficionados, a rare breed indeed.

And to surfin' Enzees everywhere - here's to a "so totally awesome" 2010 and all the promises it offers. All the best.

See you on the waves!

Deployment

mbaum — Fri, 20 Nov 2009 17:17:00 GMT

I'll be on an extended leave of absence, as most folks already know. In a unique twist, this Navy Chief will deploy with an Army unit just about 500 miles from the nearest significant body of water. And I'll be working for NATO too! I'll be serving as a Communications Officer for a Provincial Reconstruction Team. The goal is to deliver infrastructure improvements (roads, schools, hospitals etc) that make everyday life for everyday citizen better. A happy citizenry is toxic to the bad guys!

In early October, I reported for active duty at the Naval Station in Newport RI. From there, I went to Gulfport MS (Home of the Seabees!) for a week of administrative 'stuff'. Had my records checked, got a bunch of nasty shots, issued Army uniforms etc. It's where I began my transformation from a Navy Chief, to a Narmy Chief. Next step is about a month at Fort Gordon for Radio, Antenna and Computer training. With that almost out of the way, I've got a few months of combat skills training here in the USA before I go 'downrange.' Should be good, wholesome, sweaty fun!

On a more serious note: I'd like to thank everyone who has shown me so much positive support. For all the beers I've waived off, the lunches I've rain-checked and the rounds of golf I've deferred...THANK YOU! It means a lot to me knowing that when I look left or I look right, I'm surrounded by such great people. I'll work HARD to be worthy of all those positive vibes, and shout from the rooftops just how special this company really is. I'm looking forward to my mission, but I cannot wait to return and get back to work!

SAS Goes Inside the Netezza Appliance

pfrancisco — Mon, 16 Nov 2009 15:43:14 GMT

“The best vision is insight.” -- Malcolm Forbes (1919-1990), publisher of Forbes magazine, New Jersey state senator and adventure hobbyist.

A couple of big announcements from our friends at SAS today. For the industry at large, SAS’ commitment to in-database analytic processing is a confirmation of trends that we have been discussing for over two years: more and more, the “data warehouse” is becoming the hub of all analytics processing for the enterprise. While that announcement covers multiple database vendors, today’s other announcement from Cary, NC on the availability of the “SAS Scoring Accelerator for Netezza” means that we and SAS are immediately putting this recommitted strategy into action.

Of primary importance to Netezza’s customers is the fact that with SAS’ intensification of In-Database functionality, SAS and Netezza will continue working together to deliver ever more advanced analytic capabilities inside the Netezza appliance. And the first step on that path is an excellent one: the availability SAS Scoring Accelerator for Netezza means that Netezza’s customers are able to execute SAS scoring models directly within the Netezza appliance and in-line with other SQL query processing on their data. The SAS Scoring Accelerator for Netezza will be Generally Available in early 2010, and Netezza and SAS are already working with a small number of early adopter customers such as Catalina Marketing, as they begin to benefit from this powerful functionality.

These scoring models are used in virtually every vertical market in which Netezza sells our products for fraud detection, credit and risk analysis and market segmentation. By embedding them in the Netezza appliance, customers will get the same 10-100X market-leading performance on scoring their data as they do on query processing. By running in-database customers can score all their data and not be reliant on only using samples or aggregates for expediency. And the in-database scoring also means that the inherent delays, or latency, in getting at the data to score it has been eliminated. The best way to deal with the large amounts of data being loaded in today’s data warehouse systems is not move it unless necessary, so Netezza’s AMPP architecture and method of moving the data processing as close as possible to where data is stored delivers huge performance gains for in-database analytics.

The on-going partnering work with SAS, and specifically the Scoring Accelerator, are part of the conversation with customers, partners and the market in general that Netezza began back with our Enzee Universe world tour in September regarding our vision for the industry and for Netezza. It’s known as “Netezza Insight” and CEO Jim Baum used his keynote addresses in seven cities around the world to begin the dialogue of taking Netezza and the concept of data warehousing “deeper”, “higher”, “wider” in a “unified” enterprise-wide platform approach together with other partners in the community. In smaller settings with customers, partners and analysts since then, we’ve continued that dialogue since the Enzee Universe and generated real excitement as they come to understand the full breadth of what Netezza is enabling in the market.

In coming days, we’ll be writing more about Netezza Insight and how it is manifest in product platforms, features and applications. But for today, let’s just say that SAS and Netezza customers are already able to do more, faster, with our combined products than ever before and that this is just a step toward even more powerful capabilities.

As Rick (Humphrey Bogart) said in the closing scenes of Casablanca, “I think this is the beginning of a beautiful friendship.”

[UPDATE: Rather than just reading what I have to say, you can watch SAS Executive Vice President and CTO Keith Collins describe his take on the value of in-database processing and the Scoring Accelerator for Netezza in the following video from the Enzee Universe 2009 show in Boston.]

A Tastier Float

dbirmingham — Thu, 05 Nov 2009 20:11:50 GMT

In one of our primary tables, we'll call it a fact table, it contained a number of columns that had arrived through some pretty hairy ELT-based math algorithms. In all the crunching, we would see spontaneous overflow errors, so we converted some of them to float. More explosions occurred, and we converted more to float. After several more iterations, we converted them all to float. Then we discovered that the reporting layer also had to perform some hairy on-demand calculations, so it was a good thing we had float values to give them. Now everyone was safe.

However, as this table grew, and they always do, the floats became "bloats". Netezza does not compress a float data type. One day we looked up and the table was approaching 20 TB in size, with no end in sight. The theory was, that we could reduce these float values to numeric data types, we could save half the storage right away, and even more so with Netezza's compression, but it would put the reporting layer in danger of a spontaneous overflow explosion.

Once we performed the conversion of the table (as a test case) and saw it reduce in size to about 7 TB, we were hooked on the possibilities of compression but vexed as to the impact this would have on the consumers of the data.

We had experimented with surgically casting the data from numeric to float on-the-fly, but this would create a lot of headaches for the users if they always had to wrap every field with a casting notation. It did however, prove out one thing, that the time to cast the numeric-to-float is inconsequential when compared to the amount of I/O required to pull a float value from the SPU's disk "as is". In essence, we traded the time we saved in compression, and converted it to time used in casting.

So the next step would be, put a special view on top of the fact table, such that it would automatically cast every numeric column into a floating point value. Thus, whenever a reporting layer query required data, it would automatically and transparently leverage the view, pull less data from the disk, covert it to float in the CPU and then leverage it as float in memory. We effectively eliminated the cycles spent in I/O to rip the float value from the disk drive. We spent a little of it in the cast of the data to a float. We made the operation transparent to the reporting layer.

old way:

FLOAT ->>>>>>>>>>>>>>>>>>>>>>>CPU -> QUERY MATH

(16 bytes, no compression

new way:

NUMERIC ->>>>>>>>>>>>>>>>>>>>FLOAT -> CPU -> QUERY MATH

(8 bytes or less, with compression)

All of the CPU-level math then becomes inconsequential when we move to the Twinfin, since it has its own floating-point processor and can handily deal with the float type. But we can continue to mitigate the I/O hit for the data by storing it in a compressible numeric format, and coverting this on-demand to a float at the CPU level.

We Interrupt this Broadcast...

dbirmingham — Tue, 13 Oct 2009 14:49:59 GMT

Famous words, or some such like, uttered by Orson Welles as he launched into a scary parody of alien terror on national radio. Really scary for some. And proferred on Halloween night in 1938, so dare I say, 'tis the season (almost).

Ahh, not to fear, this purports to be a painless foray. But I do have a story to tell.

Several projects ago (I always start this way, so you won't think I'm talking about you!) - I worked with some really sharp data engineers on boiling out a solution for retail operational reporting. The data arrived every five minutes or more, or less, and sometimes in parallel loads, with 24x7 regularity. More and more Netezza implementations are going this way, and you too, should look into processing data at the speed of thought. In any case, the reporting users wanted to plumb the depths of this data store, to the tune of eighty billion records and growing. (Okay, small I know (for some of you) but humor me).

Well and good, except rather late in the game, the reporting users spontaneously expressed a desire to review the detail through metadata-based "lens", that is, set up some drilling levels and other metadata-based entry points, such that the entire operational model would be seen through this reporting "lens" and it would provide all the context for the consumers.

Now, such a model as described, would require such enormous power from a standard SMP/RDBMS-styled system, that we might well cause structural damage on the raised floor for sheer physical weight of said system. That is, if we really expected a report to return within a day or two of the request. Ahem! as I facetiously clear my literary throat.

But the worst-case for any given query for the above was around 8 minutes, and over 99 percent of the thousands of queries submitted, returned in less than 30 seconds. Oh, yeah, it was smokin' hot. In most queries using zone maps and the like, we saw returns in mere multiple seconds. Pshaw! Says the tick-tock-man, chocolate and vanilla, don't waste my time.

However (and there's always a catch) many of the larger reports were actually conglomerations of these smaller queries, and their aggregate time would occasionally exceed ten minutes or more. And even though this was a far cry from the "days away" we would expect from an SMP/RDBMS system, it was still 'too slow' for the users. Now, this is true adrenalin-junkie stuff, sort of like the old Far-Side cartoon of a young man standing with a fork in front of a waffle iron, captioned "Wendell Zurkowitz, slave to the waffle light". I recall how one man noted that many years ago we would wait hour(s) for a traditional oven to finish cooking, and now get impatient when the microwave instructions are greater than five minutes.

Perspective.

And rather than punt to the users and say, "Hey guys, this is just unrealistic" and degenerate into "expectation management" - the challenge was to actually achieve faster turnaround times on the reports. And here, I'm talking about getting these ten-minute reports into the 30-second zone. Would we have to embrace some extreme engineering for this feat? Methinks not - but the form of the process to get there was quite instructive.

Now recall I noted that the above model had operational tables, which were to be the detailed source, and a retail reporting hierarchy that was largely metadata-based. This reporting hierarchy had some significant size as well, perhaps a fourth the size of the eighty-billion-record fact table it had to link into. Yet both of these were on separate distribution keys. Queryng one meant broadcasting another.

And now, for broadcasting.

Whenever two tables are distributed on different keys, a join between them cannot be initially co-located. To support the co-location, Netezza will broadcast the salient information from one table's context to the other. This means the physical data has to move from its home SPU, out onto the inter-SPU network fabric, and find its way to the target SPU where it will be further examined. Broadcasting for small tables is inconsequential and barely a blink on the radar. For larger tables it can have strange effects. For example, we saw one query return consistently in ten seconds. Yet when running side-by-side with itself (multiple users) it could take several times longer.

The reason is that both queries were competing for bandwidth on the inter-SPU fabric, among other things. The simplest solution, of course, is to get our metadata table distributed on the same key as the operational tables. The problem was simply in the complexity of this metadata table and how it mapped to the core information. "Blowing it out" into a materialized form of information would require significant planning and design, because a misstep could easily make the reports turn out wrong, and this was unthinkable. In all this, the maintainability had to be considered, because if our initial complexity is too high, the maintainability is in jeopardy - by design.

Of course, we would spend most of our time in testing this scenario. Coding and implementation in most BI shops is a nit compared to the testing we have to execute to validate the outcome. Netezza is no different, except we can close the testing loop sooner if we have more power. And of course, for something of this magnitude, to test the change from minutes to seconds, we would need a powerful machine to measure the difference. Whenever we ran the new solution on a smaller machine, the difference couldn't even be measured. No, the power of the machine makes the testable difference visible and measurable.

As I noted, the form of this exercise was the most instructive part. Rather than form a means to align these two tables for co-located joins, the first effort was in attempting to tune the queries. You know, "query engineering", which is the mainstay of performance engineering on an SMP/RDBMS platform, and old habits are hard to break. The data engineers were somehow in denial that they would receive extraordinary power from configuring the data. Rather they trusted their instincts and chose to attack the queries.

Now, in any platform, regardless of shape, size or vendor, power is always and forever the domain of hardware. Software cannot manufacture more CPUs or network speed. If the physical plant is not ready, the software can only use what it has at its disposal. The software itself is largely a cost center, because it can only drain the machine's energy through inefficiency. In an SMP/RDBMS machine, the only option we have is to engineer the queries, because the physical plant is configured to be general purpose.

In a purpose-built machine, however, the query is simply a controlling mechanism to Netezza's resources. The host will chop it apart into snippets and dispatch these to the component that they will serve. Extreme query engineering on the other hand, assumes that jockeying around with the query can actually affect our fate. (contrast; a poorly written query is different from directly engineering a well-written query). And besides, do we really want to spend our time carefully engineering the query to the point of functional brittleness? In an SMP/RDBMS machine we will see queries that extend for tens of pages in a very daunting complexity. Maintaining these is a full-time job for our consultants. They swarm on the machine, and carefully tune their handiwork to avoid breakage.

Yet, we purchased a Netezza machine to get away from this complexity. To reduce, clarify and simplify our administration and consumption of the data. So as I watched these engineers bat themselves against the problem, no differently than a fly batting against a window, I watched them pull out their hair in generous tufts when little they did offered the significant gains they expected. This outcome was entirely counter-intuitive to their training. They were acccustomed to using and tuning software to make things work faster.

Sweeping the hair from the floor one evening, I mentioned (for the x-teenth time) that the broadcast effect was killing them. Once our engineers grasped the broadcasting problem, I thought we would make headway, but things actually got worse. They started trying try to control the broadcast as the root cause rather than the symptom. In one test, I saw one of the largest tables leap into a broadcast and we just killed the query outright (it would probably still be running, even today). The engineers lamented: How do we make sure the larger table doesn't broadcast? How do we control the broadcasting to our benefit? Answers exist to all of these, but it's like talking to a drug addict, one who is addicted to the drug of SMP/RDBMS and claims he can 'quit anytime'.

And then the truth came out, "David, if we can make this 10100 machine process data like a 10400 machine, we'll look like heroes!" To which I ask "How?" to which the response is: "We can save them all that money they would have spent on the hardware..." Well, not really. You've just chosen something else to spend the money on, namely performance engineering, the cost of time-to-market, the cost of a marginal implementation and the cost of human labor (the most expensive asset you have, by the way). But since the only way to get a 10100 to perform like a 10400 is to actually be a 10400, well, you see the futility. 432 SPUs versus 108 SPUs? And they really, truly thought they could - I mean - seriously. Let's keep in mind that the opposite is true. If we can't make the 10100 process data like a 10400, perhaps our approach is flawed? Heroes or goats. Take your pick. In my estimation, there's only one hero in the room. The big black box.

So the broadcast is the symptom, not the root cause. How about, we quit broadcasting, cold turkey? Take the data model through a detox program and the engineers through a series of deprogramming seminars to - well - it's not that bad. Typically the average engineer only has to see it operate in an adverse manner to become a believer. But a believer they must be, or they will not take action to correct the problem, correctly.

So one of them finally decided to produce a map table, one that would map the metadata into the operational tables such that all core joins would become co-located, with a common distribution. And lo, the first test of this blew their minds. Even the complex reports were now coming back in single-digit times, and the reports that had been running ten minutes or longer were now under a minute, even with multiple users. In fact, they saw the performance and scalability practically handed to them - simply because they configured the data correctly. It had little to do with query engineering.

Now one may ask the obvious question, and please do so now: Why don't you just build out some user-facing tables and forget leveraging the operational tables? After all, we don't build our non-Netezza reporting systems on top of operational data, do we? We build-out dimensional models and other handy structures to postively affect the user experience and simplify the flow (and the maintenance). This functional decoupling is a mainstay of reporting environments. (Okay, the next entry will focus on this). But in this case, suffice to say that the owner of the machine had placed down a hard-mandate on disk utilization. At no time could we foray into replicated detail, or even summary of detail without a plan to access the operational detail on a drill-down and the like. Interestingly, the required reporting tables would have only cost mere fractions of the cost (on disk) of the time/labor and effort put into making the operational tables viable. This is why it deserves its own treatment in a separate rant - er - essay. Stay tuned, and don't touch that radio dial.

Back to the drama - A telltale symptom that we're doing something wrong, is when we start down the engineering path. It's an appliance. We don't engineer toasters, blenders or laundry machines. But the difference here seems to be subtle. It's not. In this case, the culprit was the broadcast, something to be eliminated rather than managed. And no amount of creative query hoop-jumping would overcome this. Get the joins onto the SPUs. It seems obvious to those who have been around the machine for bit. But for those who have not, the learning curve is upon them. Be patient with them for as long as it takes to get it right. Once we have a believer, we'll never have the conversation again. As long as we stay in a theoretical zone, however expect them to stay in the spin cycle. This is like many things scientific. Seeing is believing.

Whenever I (and others like me) observe a ritual of performance engineering, each participant holding out the hope that "just one thing" will offer stratospheric boost so they can all wipe their foreheads and go home - this is the surest sign of one of two things: Either the data is poorly configured and is causing the queries to be ineffcient, or the data is properly configured and the machine does not have enough physics to achieve the goal. If the focus is on query engineering, they are wasting time. If the focus is on data engineering, at some point it will reach a "diminishing return". Either the machine has the power or it doesn't. Time to switch to Netezza, or if using Netezza, time to add some physics (a frame or two) to make it happen.

Moral of the story: Performance is found in the physics, not the carefully engineered queries. If we find ourselves "engineering" our queries for performance reasons - we should take a step back, take a deep breath - click our heels together and say softly: "There's no power like SPU power. There's no power like SPU power." Repeat as necessary.

And pay no attention to the man behind the curtain. I'll bet he and Orson Welles never even met.

Happy Belated Birthday Oracle Exadata (aka Database Machine)

tyoung — Thu, 01 Oct 2009 01:41:08 GMT

The sky did not fall in for Netezza as Oracle predicted. Instead, we've gone from strength to strength. TwinFin extends our lead and at our current run rate, TwinFin customers will soon outnumber Exadata’s. My sense is that Oracle has fewer than 25 Exadata installations although I suspect many of these are not paid-for customers in the strict "hand over the money" sense of the word. For a company with the inside track on over 250,000 customers, 25 installations in a year is hardly stellar success -- it's what a statistician may call a "rounding error." This is also low when you consider that in the same time period over twice this number of Oracle customers churned to Netezza, presumably pausing to consider Exadata on their way.

Oracle's challenge is greater than people imagine: their portability advantage is now their Achilles’ heal. Portability across different hardware systems -- Oracle's first killer app -- is a hindrance in the appliance model whose advantage lies in very specific software designed for a specific hardware configuration. For Oracle, generalization means that all the baggage that comes with an Oracle database for performance tuning and data management -- such as results caching, buffers, indexing, partitioning, tablespaces (ie the stuff you don’t need in Netezza) — is standard in Exadata. This increases costs, slows throughput and makes for the very same unwieldy solution people are trying to escape.

Oracle is responding to Netezza (and Teradata) in the same way that the incumbent database vendors responded to Oracle in the 1980s: they are bolting-on "me too" features to existing products and hoping their customers won't be tempted by alternative solutions. Cullinet's IDMS/R is a great example. It was their attempt to be seen to embrace the relational model by adding relational features to their CODASYL database. But history shows no one was convinced and it was too little too late. Another spanner jamming Oracle's works is that the value of thoroughbred appliances is so visible and easily realized, their "PL/SQL standardization" trump card is easily discredited: when the business suffers, there's not much to discuss!

So what does the coming year hold for Exadata? Well, a new hardware platform announcement sends Oracle back to the starting line. There's a switch in tone from 'the world’s fastest data warehouse that can do OLTP' (circa 2008) to 'the world’s fastest OLTP database that can do data warehousing." But regardless of anything else, a sure fire thing is Oracle will claim victory and performance leadership. The full-page ads are probably already commissioned.

Decompression... the World Tour Closes, Alas

dbirmingham — Wed, 30 Sep 2009 19:07:55 GMT

A number of months ago I wrote about how the World Tour Awaits, and all the buzz in the air about the new TwinFin. I was honored to moderate the best practices forums in North America and London, and many thanks to the rather effervescent participation by the panelists. Kudos goes out to David from Brightlight, David from Edge Associates, and Jeff from Quantisense, each of whom have those over-the-top kind of personalities that turn the session into an "experience" more than just a discussion.

But all in all, the sessions flew like lightning. If any of you have additional questions or insights, may I invite you to post them here on the Netezza community. The discussion never ends, you know.

It is interesting to note that many of the questions coming from Enzees in every venue, struck a common chord and followed a common thread. In that Enzees are unique and have a rarefied problem and solution domain. And are able to approach it with the confidence of Spartacus in the arena, or Jackie Chan on the streets of New York. Comments often began with, "I have a table with <seventy, eighty, ninety, your number here> billion records and I want to..." I mean, seriously, those on the outside lookin' in will also look askance at such an opening statement, and marvel at the ensuing, rather casual discussion about it. Nothing is casual about these data sizes, on the outside world.

It goes like this: Bring it on, baby. Because the question of whether it can be done is behind me, now I just want to know how to do it well. The audacity!

Kudos also for the Enzee crowd members who injected their insights and wisdom into the discussion, freely sharing their technical and political battleground knowledge for the betterment of all. This was not the same as "iron sharpening iron", because at this scale of data processing, iron crumbles. No, this was a lot like titanium sharpening titanium, and was exciting to participate in, to say the least.

Many thanks also to Netezza for inviting me to the tour. It was a whirlwind to be sure, but well worth the ride. Tim, Olga, Courtney and Karina made it easy for me (actually all of us) to participate. Thanks to all for your hard work and a World Tour Well Done!