This talk was presented to the NYC CTO Club on April 14th, 2004.
Update: Database War Stories is an excellent series of articles describing real-life problems and solutions. The link is to the last article, which contains links to the other articles in the series.
These online notes will be updated based upon feedback received during the presentation.
"As far as I've seen, once those XML worms eat into your brain, it's hard to ever get anything practical done again. To an XML person, every nail looks like a thumb. Or something like that."
– fejj on advogato.org
Know your options and your tools.
Update: Astute reader Curt Sampson points out, "[T]he relational model is in no way a data storage technique, but is a data modelling technique. It's entirely orthogonal to something such as in-memory storage versus disk storage, for both relational and non-relational models, you have the choice of in-memory or not-in-memory storage."
Curt is, of course, entirely correct. I mean the term "data storage" not in its technical sense but in the sense of a broad approach to modeling, storing, and retrieving data. The talk was given to a room full of CTOs and senior-level technology managers, not database architects. I opened with an informal introduction that hopefully explained what I meant.
Each reason has its own requirements, be it speed, flexibility, or reliability.
What to keep in mind when selecting a data storage technology. What is good enough?
Many OO databases available, both free and proprietary
All emphasis is mine and is used purely to draw your eye to the interesting bits.
"For the past 5 years or so, the project I work on has been using an Object-Oriented database (Versant, storing Java objects). By all accounts, it has been a success for us. We are now looking at moving to JDO, keeping Versant as a choice, but being able to profile and have other database choices in deployment. JDO is very similar to the proprietary J/VI interface we have been using, but there are some slight differences."
ODI's ObjectStore was in use at Sotheby's before the auction site was built. The original auction site code used ObjectStore. The numbers showed that reads were much faster than writes, though that was not the major factor in deciding not to use the system developed around it. Another problem was the need to write custom code for every query. Reporting was very difficult; there were no good tools back in '99 (though there may be now). ObjectStore is still in use at Sotheby's for other purposes.
Dan Woods used ODI at Time, Inc. They had a few problems and had to dump and reload the database. It was blazingly fast.
From Let's Use an Object Oriented Database:
"Yesterday I was in the audience for a presentation given by a Content Management System vendor. They were very pleased of their model for content, object this and meta that and inheritance the other. And it runs over Oracle. Up goes my paw: why not use an OODBMS? Chuckles all round and there came out the story of another content management outfit who did use an OODBMS, and have nearly gone bust since very few customers were prepared to have the thing in the building. Even for web content, not legacy data, people won't touch an OODBMS. Astounding." – Keith Braithwaite.
"What the hell is everyone so afraid of? GemStone has saved me lots of development work. GemStone has saved me lots of response time. I intend to continue using it for competitive advantage over those who insist on doing it the hard way." – Randy Stafford
From Let's Use an Object Oriented Database:
"I just wrote a GemStonej application that can switch between object persistence and relational persistence at runtime, based on a property read at startup. At present I'm seeing an order of magnitude difference in response time between the two persistence modes (object persistence being faster, of course) - and this says nothing of the extra development effort you have to go through to do O/R mapping. But in fairness we have more optimization to do on the relational side. And for a simpler domain model than we have, relational persistence may be OK." – Randy Stafford
From Let's Use an Object Oriented Database:
"My empirical touchstone here was a comparison we conducted at AC Nielsen in 1996. We tried every OODB on the market then, about a dozen including, I seem to recall, Gemstone. We found similar performance constraints on all. Regular OS paging mechanisms just couldn't keep up with the specialized disk access techniques of the RDBMSes." – Peter Merel
"My experience is that while GemStonej is typically faster than an RDBMS and more flexible, the work required to "roll your own" indexing, searching & querying framework can be complex for some developers not accustomed to dealing with the performance problems of dealing with thousands to millions of objects.
...
"I think this comes to the abstraction principle: an RDBMS is a black box with a big label on it that says: "This is REALITY. :thud of SQL manual falling on desk: You must think inside of this box." An OODBMS says: "Here's a set of minimal rules. Now you can create the rest of your own reality."
...
"My opinion: In application areas where it pays to "think outside of the box", an OODBMS is the proper choice, assuming you work with people that can handle the power properly" – Stu Charlton
"Efficiently traversing relationships: Relational databases don't do this well because it's hard to arrange locality of storage. If I read an employee, then his department, then his office, etc, etc... I'm going to have to return to the database several times. Object databases, on the other hand, let you cluster related data together, enabling you to traverse relationships more efficiently.
"So what? Well, the GemStone application I worked on was a case management system. It's never going to be used to grind over tables and generate reports. The only thing anybody ever does with it is traverse object relationships. I seriously doubt that it would have worked even half as well with a relational database behind it." – Anthony Lander
Container can
store Things, where Thing is an interface
implemented by Widget, Gadget, and
Customer?All emphasis is mine and is used purely to draw your eye to the interesting bits.
Daniel Seltzer worked on a high-load Oracle installation where they could
not interject an O/R mapping. He introduced the util.concurrent
classes by Doug Lee. The cache manager uses a lookup key for each object. One
cache per JVM. He had tools to monitor the cache's performance.
Objects expired by time. This was simpler than anything else. They slightly randomized the time so that everything didn't expire at the same time.
I chose Cayenne for the Preclick PhotoWiki, replacing a custom O/R mapping layer. This cleaned up the code and made it much simpler to modify. It also removed lots of code from the application that worried about the persistence state of objects.
Cayenne performs object caching at three levels (all optional): between data contexts (essentially database connections), within a single JVM, and between JVMs.
Cayenne couldn't handle a few tricky relationships. I found a few bugs in Cayenne having to do with foreign keys that were almost immediately fixed by the developer. The community was quite helpful, even if the answer was, "We can't do that yet. Here's a quick workaround."
Cayenne comes with a good GUI design tool that lets you reverse engineer a database, create one from scratch, generate the Java code, generate the SQL for the database, and generate the database itself.
There are still some meaningless coding and design requirements (from object's point of view), like having to specify reverse relationships.
Cayenne is also used on the NHL's site.
open()close()fetch(key)delete(key)store(key, value)first_key()next_key()Berkeley DB is in use everywhere. Sleepycat believes that 90% of people in the U.S. touch Berkeley DB every day and don't know it. Berkeley DB is used in Sendmail, Apache Web server, Jabber, Sun (LDAP directory servers), EMC (Celerra and Centera products), Electronic Clearing House, Inc., Netscape browser bookmark management, Mozilla news client, Google accounts, the largest U.S. stock market, AOL client software, RSA's Keon Secure VPN, a Sony set-top box, cable TV, Amazon.com (as a cache to speed retrieval of data), telecom industry, DNS.
I feel like a kid in one of those '50s science films: "You see, Timmy, you use plastic every day! Your dog uses plastic! It's here, in the rims of your thick black glasses!"
SnapshotPrevayler handles startup/shutdown housekeepingSeralization readObject methodAll emphasis is mine and is used purely to draw your eye to the interesting bits.
It took me a while to find links to users on the Prevayler site to Prevayler pioneers and Major companies using Prevayler. The latter list is short: Lehman Brothers, Microsoft (Ensemble Studios game company), Blizzard Entertainment, Petrobrás (Brazil's largest oil company), Vivo (large Brazilian cell phone operator), and TV Record (third largest TV network in Brazil).
Jon Tirsen wrote: "The morning after (I stumbled in late and hungover actually) my pair-programming mate had read all about it at the Prevayler site I left open in the browser and already ript[sic] out our then OJB-persistence layer and replaced it with Prevayler. Later that day we experienced the sensational 10k performance gain and we're probably never going back again. (Remember our system was very performant[sic] from the start.)"
Smalltalk has been in use in both research and corporate environments for decades. The Smalltalk community always pointed towards Wall Street when asked where it was used. XP was invented for a Smalltalk project.
Note: need to beef up this section.
--- name: Dirk Hockeybranch date: 2004-04-14 minutes spent: - 1.02 - 1.34 - 0.7 - 0.89 - 0.94
"One of my last employers, XXXX, may have a story for you. Their background – a database marketing agency who provides direct mail and email leads to catalogers, telemarketers, mailers, or any marketing organization... Their database was huge – several terabytes. It was comprised of Experian's Gold File and several other consumer files, equally[sic] two hundred million consumer records.
"Originally the data was stored in relational tables. However, generating counts was a lengthy process in terms of time and computing resources. A decision was made to transform the database into one huge flat file (yep, start gasping for air) and index it using a product called Omnidex. Then a custom app was developed to generated counts and voila, we had a faster, more reliable and cheaper to operate database.
"I had come into the scene after the conversion had occurred. Being used to large scale ERP and DW implementations, I flipped when I heard what was happening. The CIO was able to convince me on the performance gains they made under the arrangement. It works. Furthermore, there was no negative impact on other data processing, such as ETL (we used Ab Initio)."
Steve Jacobs wrote: "At Poindexter we have built our ad server without a traditional database. We were a small scrappy company for a while with limited resources and the cost of scaling a database infrastructure to support billions of ads served per month was prohibitive. There are 3 areas where a database is needed: data on what ads are in an advertiser's campaign, data we've gathered about the user (what ads they've seen, for example) and reporting data about what ads have been shown.
"For campaign data we take snapshots of the Oracle database that our web based campaign management tool connects to and push the most recent changes to the ad servers. This used to be in the form of XSL documents that encoded both the data and the targeting required for the campaign. The user data was a XML document stored in the users cookie. Serving an ad was simply by performing a transformation on the XML cookie. When an ad was chosen, the reporting information was written out to flat files. These flat files would then be loaded into an Oracle database on a nightly basis for reporting purposes.
"Eventually the cost of doing the transformation with the style sheets became prohibitive – we just couldn't get transactions per second we needed with XML/XSL. We've since migrated to actual Java code generation to replace the XSL where the Java code reads the users cookie. The cookie itself is no longer in XML either; we've switched to a proprietary format that is faster to parse for use by the Java code. When I first suggested to my architect that we generate Java code rather than XSL I prefaced it by saying, 'I can't believe I'm about to suggest this, but...' Nonetheless, it's worked out well, giving us a 3x performance improvement overall.
"I don't like the fact that we've mixed data and code together in one big stew. The benefit is that there's no parsing XML trees or the like – the data is all there built in to the code. The drawback is that changes to the code may end up meaning we have to regenerate the code/data for all campaigns."
Daniel Seltzer was on a project at a non-profit organization a few years ago. They lacked the development process, system admin, and money for traditional IT services. They ended up using the file system as the data store. Daniel developed a publishing tool in Ant. There was a small amount of metadata in a database. An Ant task periodically synched the database with the file system.
They indexed the text with Lucene.
Mark Uhrmacher has used SyncSort (I'm checking the URL) on flat files with billions of transactions.
The xBASE family of databases are structured similar to ISAM, with data files and index files. Applications read the files directly.
"When adding records with ascending adjacent keys, the add process would get slower and slower because overflow records were kept in an unblocked linked list and the next record had to be added to the end of the list.
"As a system programmer, I was called about twice a year because 'the computer is running slow'. The fix is to add the records in descending key sequence so each record is added at the head of the linked list. It was fun at first being hailed as a genius. But as Yogi Berra said, it was deja vu all over again."
This list of resources is in addition to those presented inline. I need to flesh out this list.