In my Introduction to Terracotta, I alluded to the possibility of running jobs across the cluster but I didn’t really explain how. My first attempts involved using the tim-messaging package, but there wasn’t much there geared towards running the same job across all nodes. It mainly addresses dividing jobs across many workers (local or remote).

There are a few cases where the same job on all nodes is useful. The first time I ran into it was trying to pull some custom statistics from each node. Each node kept a moving average of requests per second for a specific user action I wanted to track. The other use case was notifying long poll HTTP clients. Server push could be initiated from one node but the client may be listening on another. Since Socket instances aren’t shareable, I needed a simple way to server push on all nodes.

I cast around until I found the old tclib forge project that predated many of the current TIMs. I modified the below class starting from that code.

To solve the problem of knowing what nodes are in the cluster, I simply have them register() themselves at startup. A ServletContextListener works great for that. That solves the problem of submitting jobs before the node is ready.

Of course, then you have to worry about nodes that may suddenly disappear. One can’t count on them unregistering themselves since they might crash or something. To fix that I have each node hold a lock the entire time they’re running. One of the necessities that Terracotta must have had to address early on is cleaning up cluster-wide locks when a client exists. To test if a node is still around, all the caller has to do is attempt to acquire the lock. If it’s successful, then that node somehow exited it’s run loop.

In practice this works pretty well. It’s much simpler than the JMX solution, although it doesn’t bother trying to resubmit jobs or some of the other neat features of tim-messaging. The tryLock() below was plenty fast enough for my purposes. Of course, it’s easy to envision adding a thread to clean up the queues instead of forcing the caller to wait for the tests. Really, that would depend on the size of your cluster.

Depending on it’s use, you might tweak the thread pool used for this. This code also depends on the tim-annotations project but you can easily configure tc-config.xml.

I guess all my arguments (read: complaining) finally paid off. Our old database is starting to fall apart under the load. Too much data and too many users is an equation that won’t last, no matter who makes the software. And eventually my aggressive caching scheme didn’t matter much when the server began to run out of disk space. So I was given the go ahead to build a reporting database with Postgres to ease the load on the production box.

See the inspiration for this post.

I’m not a database expert but I have learned plenty enough about them in order to make my applications run as best as possible. If you have questions please use the comments below

Createdb

This Postgres database would store the complete databases from three sources every month end. Every night it’d also update a cache of the production data. The first task was to resolve the schema differences between them. They’d started off with the same design but over time programmers have added to them.

Since the entire schema was roughly 160 tables * 3 databases, I wasn’t going to do that by hand and I certainly didn’t want to have to update it when new projects changed the schema. Automation would be key.

Fortunately, this is only for reporting. That means I wouldn’t have to port the packages, functions or views. I also ignored foreign key constraints since it’d do little good. Only the table structure itself was needed. I ended up writing a process that’d simply loop through all the tables and columns of all databases and resolve types between them, writing DDL statements to stdout.

The benefit to that approach meant I could review the changes before piping the result to psql. Actually, I ended up using streaming to psql for everything – it’s fast and easy.

Partitioning

Right away I’d decided to use Postgres partitioning. Even though most people will tell you that a single Postgres table is usually fast enough, my report queries wouldn’t be running over the entire table. They’d only ever run on one at a time so it seemed obvious to eliminate as much data processing as possible. Plus, I’d be dropping tables every night and it was much faster than DELETE. Partitioning will also allow me to move older data to slower archive disks in the future.

Postgres partitioning doesn’t work like anything else I’m familiar with. It uses table inheritance and at first the benefits weren’t clear. I’d expected to have set up storage definitions and range partitioning after my experience with Oracle.

But it’s far, far better than I’d thought. There were no problems inserting to the right partition (since you can just INSERT INTO the table you want), “moving” a partition could be as easy as renaming it, and dropping one is nothing more complicated than DROP TABLE. You can even backup and restore a single partition without all the fuss Oracle requires.

Table inheritance lets me keep several tables together under one large storage-less parent table. I was able to issue ALTER TABLE ADD COLUMN statements against the parent table and update all the children, which really helped when writing my schema tool.

Indexing

One drawback of the table partitioning and inheritance scheme was any index created on the parent table didn’t span the child tables. I had to recreate them each time on every child table.

That one ended up not being such a problem though, since not having indexes on the table while importing millions of rows was another performance win. I ended up simply writing a script to create indexes on the child tables to match any indexing on the parent. Indexes on the parent tables are pointless (they have no data in my case) but I could define them once and simply run my script after the import finished. Postgres made it quick and easy to get the index definitions, change the names and recreate.

Data

For my data dumping tool I wrote another mode in my tool to simply SELECT * FROM each table and write CSV to stdout. My goal was to read from the database, perform any necessary transforms and write to the reporting server in a stream. Any stage that required dumping to disk first would be too slow.

Of course, Postgres’ COPY FROM is crazy fast and it didn’t take long to figure out the right CSV format. It was pretty easy. I toyed with dblink or writing some pljava to make the remote calls from the database itself but it seems a lot of type information is lost in the process. In the end piping to psql was the best route.

Lessons Learned

A few quick points about Postgres I learned along the way:

Postgres is wicked fast. So fast that System.currentTimeMillis() is not accurate enough to measure its fastness. (And why I don’t have any more graphs to show off.)

Since I was generating SQL for the reports already, it ended up being easier to use the child partition table directly. That meant several fewer SQL criteria statements per query just to use the right partition, even though performance was about the same.

Use check constraints. They’re limited in a lot of ways but they can really speed up queries dramatically.

Examine your query plans for type casts. I found several that made a massive impact to query speed. (It’ll show up as a :: style cast in the explain plan.)

Don’t use numeric for ID columns. My schema tool had mistakenly created a single table with this type and all other comparisons were cast to numeric. Altering the column to int4 was a 2x query time gain.

Postgres partitioning is unfortunately verbose. Instead, write scripts to generate SQL statements for you. Postgres makes scripting the database painless.

Follow the best practices for tuning your database. Increasing the shared memory helped a lot in my case. I also disabled fsync during development since I was dropping and loading a lot of data and didn’t need any guarantees.

Comparisons

The final product is processing about ten times the data I had on Oracle, two to three times faster… Postgres certainly exceeded even my high expectations.

And while I don’t mean to knock on Oracle too much, I feel very much like I’m wearing an 80’s era polyester polo shirt with orange and brown stripes every time I declare a VARCHAR2.

One issue was constantly upping the temp tables and rollback space, as well as writing on-off procedures to do simple things like delete a few thousand rows. There was no limit to the amount of disk it seemed to need. Operations on a lot of data took an exceptionally long time. I spent several late nights rescuing a downed Oracle job because it’d run out of rollback space – and the solution, committing in intervals, is horribly slow. Of course, this is a well-known, oft-encountered problem. And yet it’s still a complete waste of time.

I did also try increasing the rollback segment sizes but having a lot of data in a rollback log wasn’t any faster than committing in intervals. I’m not an Oracle expert by any means, but it didn’t seem like a problem a mere mortal could solve.

Postgres is much better suited for large queries. There’s quite a lot of interesting information available on Postgres’ MVCC system and I think made the difference on this project. For example, which I discovered one of the tables had hundreds of thousands of NULL values I didn’t need, I simply issued a DELETE FROM on the parent table and went on a coffee break. No commit intervals, no rollback, no nothing. It was done processing when I got back.

Another problem on Oracle was when I’d accidentally written a ton of data to the wrong table partitions while fixing something else. Oracle has a solution for this, alter table mytable enable row movement and then issue an UPDATE statement to modify the rows. Unfortunately this means it’s using an insert and a delete statement for every row, so you’re once again stuck writing procs to commit in intervals. That time fixing it took such an awful long time that I was forced to cancel the job when users began logging in the next morning. Much of the data is still in the wrong partition.

A final nail in the coffin for Oracle was LOBs. They suck. Not kinda suck, but turning-grey-at-23 suck. You’re not allowed to have more than one defined per table and that would have been a nightmare for this project, since several of the Postgres source tables used the TEXT type where it made sense. Porting that data to Oracle would have taken a lot of time and probably would have made updating the schema automatically impractical.

Of course, I haven’t even mentioned price. Since this project is available externally, Oracle licensing would have been prohibitive. Contrary to what many think, you really do have to pay for CPU licenses, partitioning, full text searching, and a whole lot more that you get for free with Postgres. As it stands, all the company had to pay for was some hardware and a small amount of development time. I’m happy to not allow Oracle to dictate how many cores I can use.

I’d faced some opposition to running Postgres in an enterprise setting. It seems Oracle has a free pass while Postgres must prove itself all over again despite great past experiences. In this case at least, Postgres did so in spades. I’m fortunate to have smart management that doesn’t mind listening to me drone on every once in a while. Total time to write all of the tools, port the old data and update the report queries was about two weeks.

Neil Mcallister says our days of rote coding are over. He seems to say that instead of actually producing something, we should focus on guiding a project and, well, just talking to people.

That makes little sense to me. If you’re a coder, programmer or developer — whatever you want to call yourself — your primary function is to code.

We talk a lot about what makes a great developer. So-and-so is thorough, he uses tabs not spaces, she runs Linux. We ruminate on the best tools, debate how much knowledge a great developer must have or the best languages. We’ll even get into debates on the best office chair or the relative merits of office layouts.

We do this not because we’re all that interested in the correct size of a cubicle. We’re interested because having a big enough monitor or desk makes us better at writing code.

Focusing too much on one skill would be like GM deciding that wheels are such an integral part of it’s product that they’re becoming a wheel company. After all, nothing works without the wheels.

Communication skills are great, don’t get me wrong. I’ve dealt with plenty of people who’d fail basic English or typing tests. Their lack of communication skill certainly impeded their code, but it’s still a wheel skill. You can’t be a successful developer just by talking.

His examples make little sense, either. He actually compares software development to a secretarial skill. You know, like typing a letter. That’s a hopelessly flawed argument. After all, while my bosses do write their own email, I have little fear that they’ll ever start writing their own programs.

I was beaten to the punch. Love my Android! I’m trying to convince my company that a mobile app would draw customers.

Google has a hit on their hands. Google Apps is popular with small businesses and personal users (they claim more than 100,000 accounts), and now with Android we can get great email hosting, push email, calendaring and automatic over-the-air synchronization for free.

That’s a pretty great combination for small businesses and personal users. Until recently the big name has been Blackberry. Of course, that requires the enterprise server and presumably Microsoft Exchange. It’s not something your average person can setup. But it’s an easier sell to pickup a cool new G1 and get the same capabilities on the cheap.

All it requires is a phone and a free Google account. For $50 a year, add your own domain and Google Apps. The iPhone can’t boast that, since you need either a MobileMe account or an Exchange Server. I doubt many will add Exchange just to have domain email hosting with push email for mobile devices.

So when it came to finding a provider for my new (somewhat more professional sounding) domain, Google was the first choice. I threw down for a trial Google Apps account and did some DNS fiddling. Pretty easy.

Switching Google accounts on the Android was not immediately obvious though. The phone is not setup to allow you to change accounts. I did find some help with enough searching, however. Quite a few people mentioned the Any Cut app will let you setup links to any Activity on the phone.

Simply run Any Cut and add a shortcut for the Setup Wizard. That’ll allow you to rerun the wizard without resetting your phone. I found the GMail app confused the new account so it was necessary to wipe out the user data (Settings -> Applications -> Manage Applications, tap GMail and GMail Storage and hit “Clear Data”).

After clearing the GMail unceremoniously crashed. A quick restart fixed that.

The real trouble was getting push email to work again with my new account. It would work for the first message and stop for unknown reasons. Even tapping “refresh” would fail to find new email.

After some digging I found the message “received tickle for non-existent feed” using the USB Debugging feature. The custom URL that I’d setup (it’s just a DNS cname record) caused some confusion since it was using my domain address and not the typical Google URL, which didn’t match the account the GMail app was using.

Disabling it was easy enough. Simply go to the Dashboard, click Email, then Change URL and reset it to the default. It won’t be missed — I’d only added that in the foolish hope I could work around some over-anxious web filtering at work.

I’m very happy with this setup so far. Although I doubt push email will mean I’ll actually answer any faster. Don’t get your hopes up!

Programmers get burnt out. Like the end of “Office Space,” some find more fulfilling careers doing something completely different. Reasons vary but the result is the same. I know, I left programming to join the Marine Corps.Now, I can see many of your faces scrunching up as you get ready to shout, “What!?”

First, let me answer a few common questions. No, I’m not a Republican (at all), or a fan of President Bush, for the war, and I was not on some crazy death wish. There are many great reasons to join the Marine Corps. Here are some of mine:

• They were hiring.
• I wanted to work outside. (Said while shoveling sandbags, in the rain, in ankle deep mud. I got to shovel a whole lot more for saying that.)
• All the terrorists went to Iraq so I had to go there in order to shoot them.
• Why not?
• I got sick of programming in a heated office where people never yell and we get off at 1700 (or get paid overtime!), for a boss who never once asked me to wear 150 lbs of gear and climb the side of a mountain. It was horrible.

As my Drill Instructor said, “Everybody joins for a stupid reason.” Okay, that decision was more personal, and I won’t discuss all of it here, but certainly burn-out was a factor.

Doing it wrong

As my former platoon is now readying to leave again, it’s hard for me to remember all the things that lead up to quitting programming. I had a great boss who was very technical and who shielded his team from most company politics. I was implementing a new webapp that’d manage a portfolio approaching a billion dollars, and I had pretty much carte blanche to implement systems the best way I could.

In the early days there was just so much to do that I spent nearly every waking moment working. I’d wake up on a work day and login. I’d read syslog messages and overnight reports and put out any fires that sprung up. Then it was time to rush through a shower and head to work. I was always the last to leave, too. Not that it mattered since most nights I could be found home at my desk, hammering away at something until midnight or later.

I lived for work.

This probably went on those last four years. I wasn’t required to do any of this but I felt a great need to prove myself with the massive task I’d been given. I never seemed to have enough time to implement anything. The more I finished the more work I saw ahead.

I was crazy productive. I’d not only managed to write large parts of the financial system, several supporting webapps, but also migrated most of the servers to Linux, created NIS servers and then replaced them with a home-grown LDAP system, JEPP, replaced a crazy expensive proprietary firewall, and on…. And on.

For some odd reason I felt trapped after a while. I felt that if I lost my job it’d simply take too long to sort through the ridiculous stuff most job posts require and I wouldn’t find another programming position. And after a while, I was tired and wary of proving myself all over again. I figured I’d never leave that job, which spurred me to work even harder.

It wasn’t as if I had a reason to leave, really. I had no personal life to interfere with work. But all of this lead to a deep desire to do something, anything else.

I think the real break came when I began to gain weight. I was like looking down and not recognizing yourself. I’d grown up in the mountains of Colorado. I’d always been athletic. I hiked Mt. Elbert and other fourteeners. I was on the football and track teams. Gaining weight was not something I was ready for and I flipped. There was a lot of time spent on the treadmill after that.

Oorah!

So I decided to join the Marine Corps. I come from a family with a lot of military background, so it wasn’t as unheard of as it seemed to be around San Francisco (go figure). They say boot camp changes you and my Drill Instructors quickly set about fixing me.

In my interview with the company I was quiet and unsure of myself. They told me later that they barely understood what I was saying. I don’t think anybody meeting me after the Marine Corps would have that same impression. My girlfriend tends to elbow me when I’m being too gregarious. Before I was self-absorbed but afterwards I would joke that I’d willingly walk down main street naked if it wouldn’t get me arrested. Other Marines tend to laugh knowingly at that. There’s just some things you stop caring about after boot camp.

After boot camp and schools, Marines are placed with their platoons for training. Even in a time of war you end up with time off. Like most I spent a fair amount of time drinking away the day in the off-hours, but it turned out that my programming itch hadn’t died at all. Every time they’d scream at us to, “Go to the barracks and standby,” I’d sit around and hack for a while.

It wasn’t the hours I’d spent before. It was for me, for fun. I worked on TOra after the original author left, played games and still got in plenty of time at the bar with the guys.

So, even though I was in the Infantry and my job involved a somewhat different set of skills, I was still a programmer at heart. I love coding.

I’m out!

I once worked for a product design firm that had some fantastically creative people. The designed many of the sexy computers and gadgets any geek would recognize on sight. What is interesting though, is that they allowed their senior engineers to take sabbaticals.

This company realized that even though they were engineers, they weren’t on the manufacturing line. In fact, their jobs required enormous creativity. It was expected that all of their employees would burn out given enough time.

But they had a way out, a way to take an extended time off and return, refreshed. Now I think of my time in the Marine Corps as an odd kind of sabbatical. I don’t think it’s necessary to live without stress to solve burn out or I would be a bottle of nerves. Instead, maybe the important thing was to turn off the creativity spigot for a while.

After long enough, that creativity burst out of me.

Now, I’m not recommending that every one struggling with burn-out go join the Marines. You’ll regret it while you’re in and miss it after you leave. But there was all this discussion lately if one could really leave programming. What, are we whining too much?

No, burning out is perfectly normal. Especially if you did everything wrong like I did. So I would encourage people to take all of those vacation days you’ve saved up. Or take a job that’s radically different. Maybe writing programs for a different industry would do the trick.

Now I’m purposefully trying to work smarter. No more crazy hours unless absolutely necessary. I’m writing again. I keep a personal life now, and I cannot wait until my danged Android Dev Phone gets here. In my own time I do things I like. And you know what, I think I’m writing better code than ever.

I’ve been struggling to explain how
much of a leap forward Terracotta
is for Java programming. Of course, Terracotta is a cluster in the
fullest sense of the word, but "cluster" brings to mind
many painful years of parallel development. They required extensive
setup and a lot of code changes. Terracotta is different.

Terracotta works at the JVM level,
silently modifying the behavior of some classes you choose to share.
Suddenly you can use a ConcurrentHashMap across the cluster, without
code changes, in an efficient and transactional way. Now not only do
you have all of the benefits of a cluster environment, but you can
run your own code without change — or even a vendor’s product. They
have support for many common Open Source projects and you can even
run containers from Tomcat to WebLogic on your cluster. Or all at
once, if you prefer.

Terracotta is also sometimes described
as "network attached memory." That’s apt, but it doesn’t,
you know, sound cool enough. For many, it takes a bit of
experimentation with Terracotta until they realize: share memory and
do anything. It’s not a very
interesting program that doesn’t use memory. In fact, most programs
exist to do some computation on memory or data.

The
pain starts when you need to share that memory with another process
or system. There have been all sorts of methods. We had COBRA, but
that was a bummer because of the IDL files. We’ve had RMI and Mule
big enterprisey XML
and 10,000 other ways to simply move a piece of data. Many
programmers even threw up their hands and used the database as an
Inter-Process Communication (IPC) method, storing memory that had no
business in the database.

Those systems can
often be replaced by a simple LinkedBlockingQueue. If every node on
your cluster starts a thread that waits on the queue, then you can
simply put data in the queue for other nodes to retrieve. Checkout
the tim-messaging
(TIM: Terracotta Integration Module) project on the forge for a great
library that adds all of the goodies needed for a reliable solution.

Even better,
Terracotta lets you program as if you’re on a single JVM and deploy
to the cluster. Sharing memory in your cluster is no more difficult
than sharing in a single process. That means setting up and testing a
message bus or writing for the cluster can easily be done on a single
developer’s machine.

If that doesn’t
sound like a huge leap forward, consider that I once spent several
weeks trying to get my personal OpenMOSIX cluster to migrate
processes. I never did figure out what black magic they used, but
finally, after a week or two of hacking, I started seeing my jobs
move across the cluster. And for all my effort… it took twice as
long as the single process version.

Of course, that was
long before I’d even used Java and when I still thought C was the
only language I’d ever need. Never did I imagine declaring a
LinkedBlockingQueue<Runnable> and literally executing code on
remote nodes. Simply put: share memory, do anything.

Caching

The Achilles heel
of parallel and cluster programming is usually the data. If a job to
be executed across the cluster requires every node to run a SQL
statement, then any benefits to the cluster model are quickly lost.
The database will quickly become the bottleneck that no amount of
cluster optimizations will solve.

That’s why I
recommend the first step to Terracotta is to use it as a distributed
cache. After all, once your data is in the cluster, then acting on it
from several nodes becomes a real possibility. Instead of shipping
data around (and probably spending far too much time deciding which
node gets what chunk and sending it to them), the program can simply
act on the cache. Terracotta has already spent the time making that
as efficient as possible.

Terracotta
makes for a great distributed cache solution, too. It makes sure that
all of your transactions are ACID (Atomicity, Consistency, Isolation,
Durability), the same standard databases are held to, but it also
ensures programmers can use familiar Java language constructs to
manage memory. This makes it possible to rely less on or even consider
killing
the database altogether.

Unlike many
solutions, Terracotta doesn’t depend on the slow Java serialization
process to manage objects in the cache. It’s tightly integrated with
the JVM so it can do far smarter optimizations. For example, when a
cluster node requests a map entry from the cache, Terracotta doesn’t
have to ship the entire object tree. It only makes available the
entries as they’re needed. That alone saves metric tons of network
usage.

Terracotta has many
other great features as a cache. Their documentation is quite fond of
calling the cluster nodes the L1 layer and the cluster server the L2,
similar to how a CPU cache works, for a good reason. As memory fills
up on the cluster node, the client can flush it’s less frequently
used memory. The server is responsible for the consistency of the
data so the cluster client is not relied on for data integrity. The
server process also keeps track of it’s hot memory and can persist
it’s own data to disk as memory fills up on the server JVM and remove
old data from it’s heap. You can even configure checkpoints on the
disk storage for backup purposes.

All this means a
node can easily access a 2G Map instance with a smaller client heap.
Of course, performance won’t be as great as there will be overhead
depending on how the memory is used, but the most frequently
requested data on a JVM will be in main memory. Rather than memcached
or other solutions, getting a value is nearly as fast as using the
Java heap because it often is on the heap.

Terracotta’s
redundancy also allows programmers to depend on their cache. After
all, if the program is running, then the cache is working. This is a

far cry from many other solutions that discourage design patterns
like asynchronous database writes or even enumerating the cache’s
keys because the cache is not persistent. With the reliability of
Terracotta backing the cache, such a design is easy to envision.
There’s even an integration
module aimed at just such a use.

High Availability

All this effort
adding caching didn’t go to waste. While you weren’t looking your web
applications also gained some great High Availability (HA) features.
Terracotta’s engineers were probably bored after they solved
concurrent programming.

Terracotta Sessions
will store and manage session data, meaning moving from one server to
another doesn’t mean users must log into the application again.
Session state can be preserved and trusted. With the addition of a
nice load balancer, scaling is simply a matter of adding another node
to the cluster. Not only does the distributed cache ease the load on
the database layer, but scaling the application layer is completely
transparent to the end user.

Also, since session
data is managed by Terracotta, server admins can now bring down
cluster nodes for maintenance without disrupting the application. In
fact, you can even patch and upgrade Terracotta itself without
stopping the whole cluster.

For
the cluster itself, Terracotta can be configured
to withstand the loss of a server. Data integrity is ensured at all
levels.

Open Source

As if
creating a complete end-to-end solution for some of the most
difficult scaling problems in Java applications wasn’t enough, Terracotta is also
Open Source, has great documentation, and their developers respond
quickly to forum questions.

It is
a much easier sell to management and other developers when there’s no
up-front cost to experimenting. Of course, before moving to
production most organizations will require some kind of training and
support, which Terracotta also provides.

I
encourage you to download and play with the example applications.
It’ll provide a good taste of what’s possible. Terracotta is truly a
leap forward. With a little experimentation, you’ll quickly discover
simple solutions for whole classes of difficult problems.

Watch
this space for upcoming articles on Terracotta. The first is
tentatively titled, "Terracotta: a tale of two projects."

So, my little rant about Oracle got a lot more attention than I’d expected.

While I was mainly focused on the price of Oracle, now I’d like to explore my favorite alternative a little more: PostgreSQL (PG). I’ve been busy the last few days using the excellent Ora2Pg to convert my old 8i database. What I’ve done is a quick and dirty off-hours conversion for a project that I’ve been working on for several months just to see how PG would work.

The project is a dashboard, a business reporting tool that is all the rage these days. It includes every business indicator they could think of from hours a deal spent waiting for approval, to projected totals of deals not yet approved, to charts and graphs of deals long ago approved. Gone are the days when you could give the users a copy of Crystal Reports and let them at it. They expect much more.

In the beginning I was saddled with an enormous spec that delineated only some of what the application should do and a number of existing reports. Unfortunately, not one of them ran in under 10 minutes and they were each a knotted mess of SQL depending on an untold number of views and procs. It took months to streamline the SQL and create a new data warehousing project. When it was all said and done, 51 queries dominated the main screen of the dashboard.

And it still took 30 seconds to draw for simple searches.

That was when I began caching like mad. There were hundreds of thousands of possible search term combinations, meaning I couldn’t simply populate the cache ahead of time, but at least it only needed to run so often. Terracotta, which I’ll write about soon, was a huge help. It’s ten pounds of awesome in a five pound sack.

Caching is probably the only reason my users haven’t gone running into the hands of Oracle, thrusting dollars at them. It alleviates the load and allows only queries that haven’t yet been run to actually touch the database. I can get away with a whole lot less horsepower than I would need otherwise.

Porting

This isn’t meant to be a benchmark because that would be fantastically unfair to Postgres. I’ve spent an enormous amount of effort in Oracle optimization. It’s big job filled with expensive consultants who post weird pictures of themselves.

The schema contains materialized views built on partitioned tables. This greatly helps cut down the amount of data Oracle must sort through to fetch rows. The indexing has been deliberate and extensively tested, some times only to gain a .10 second difference. It’s been de-normalized as much as possible but the structure of the data made that difficult. A couple queries still touch as many as five tables. The queries themselves have also been written and rewritten with Oracle in mind, using inline views, subselects or whatever Oracle happened to fetch faster. I also made some use of optimizer hints like /*+ RULE */ and /*+ JUST_USE_MY_INDEX_ALREADY(table) */. I think of them as an implicit admission that the optimizer is broken if I have to tell it what to use.

But for Postgres I generally let Ora2Pg do it’s thing. I didn’t even bother changing the types on many of the columns, some of which defaulted to the slower arbitrary precision type numeric. Postgres supports more sensible integral types like int2 and int4. I also didn’t bother partitioning the tables similar to my Oracle setup, and I didn’t even bother compiling it myself to my architecture for the best performance. I pulled 8.3 using apt-get from the Ubuntu repository.

In short, I did about as little as possible. The only changes I made to the queries were the simple syntax differences between the two. Such as Postgres requires the "AS" keyword in the SELECT expression. Or a few type casts here and there. I found that a few of my indexes depended on Oracle’s trunc function, so I created my own function that simply returned the result of a ::date cast and indexed that. There’s a lot of room to improve these results.

Results

So without further ado, here’s how an unoptimized PG faired:

That’s pretty impressive.

Please also see the update I’ve posted. I ported my most demanding application to Postgres just to see how it’d handle.

I have to rant. Nobody reads this so who cares.

I work for a department that has been running 8i since 8i was shiny and new. Users don’t care about the database and they shouldn’t have to, but today I had to ask them for over $40,000 for a single database because it has applications accessible externally. I think of that as 1/2 a person that could be here working, and I think of ol’ Larry.

He doesn’t need another yacht but I do need another programmer.

We have four main applications and only one of which runs Oracle. Years ago I’d managed to convince them to use Postgres for the new deployments because they were new and there was no data porting to do. Somehow I’d convinced them to okay that. Nowadays Oracle is the standard and it has been decreed that all things will run Oracle.

Of course, years ago the only argument against Postgres was a general, unspecified concern about reliability.

$ ps -Tef  | egrep ‘[p]ostmaster|[S]TIME’
UID        PID  SPID  PPID  C STIME TTY          TIME CMD
postgres 20195 20195     1  0  2006 ?        07:41:24 /usr/local/pgsql/bin/postmaster
$ uptime
 17:23:17 up 764 days,  4:20,  3 users,  load average: 0.65, 0.50, 0.49

Back then they were concerned about the reliability of Linux, too. Yeah. The last time this box was shut down was to move it.

If I worked anywhere else, Oracle would be a non-starter. Every database is going to have some data that people are going to want to give access to partner businesses, customers, etc. Oracle’s business model makes no sense, yet somehow people pay it out of some nebulous concern or lack of knowledge.

If I was starting a new project or new company, Oracle would be a non-starter. Who in the world has money to spend thousands per CPU for anything that touches the Internet, for a company that doesn’t make money yet? Nobody. That’s why you see MySql and Postgres uptake. Because they’re solid, they’ve got the features people actually use, and because Oracle is too damned expensive.

TOra’s 2.0 release has gone well so far. That is to say it’s been quiet. When it’s quiet that means people are going about getting work done and generally not complaining. It’s a good thing.

However, several people have been having trouble installing TOra with Oracle. Since it’s proprietary we’re not able to ship the Oracle client along with our binaries or add a package dependency like we do with the Open Source databases we support. Happily, installing Tora only requires a few simple steps.

Downloading

Firstly, you need to download and unpack the Oracle instant client. All of our builds have been compiled against the instant client. While they are generally compatible with the full Oracle installation (of the same version), there have been reports of crashes and odd errors. Even if you run a different version database, using the instant client works great. For example, I use the 10g instant client against an old 8i database daily. The client and server will agree on a protocol. TOra also has special support for using instant client connection strings. That means tnsnames.ora and setting ORACLE_HOME is not required.

Download the instant client version from Oracle that matches the version of the .deb package for your system. For example, tora_2.0.0-1-11g-intrepid_amd64.deb is built against 11g. For the -10g deb packages, make sure to grab the 10.2.0.4 zip. You only need the basic package for running TOra. Also, unless you need Oracle errors translated in another language, the basiclite package is much smaller.

Also download TOra while you’re at it (of course!). Actually go ahead and download it a couple times. Help out the stats.

Lastly, I had to disable Debian’s automatic dependencies to build TOra against proprietary software, and so I’d forgotten to add libqscintilla2-3 to the list. Please apt-get that, too.

Installing

Unpack the instant client folder wherever you like. Location doesn’t matter on a single-user system. To install system-wide, make sure to unzip somewhere all users can access.

If you do have the full Oracle installation, then the following instructions may conflict with your existing settings. You’d have two copies of the same or nearly same libraries. In that case, removing all Oracle entries from ld.so.conf and using LD_LIBRARY_CONFIG would probably make the most sense. Simply add `export LD_LIBRARY_PATH=/path/to/full/install` to the Oracleuser’s .bashrc. Add the same for your user account with the instant client path for TOra.

Otherwise, add the instant client folder to your /etc/ld.so.conf. The easiest way to do this is to create a new conf file in the /etc/ld.so.conf.d folder:

$ sudo bash# echo /path/to/instantclient > /etc/ld.so.conf.d/oracle# ldconfig

Double-click the .deb for TOra to install or run:

$ sudo gdebi tora_2.0.0-1-11g-intrepid_amd64.deb

Running

Before running make sure you don’t have an ORACLE_HOME set. This may also conflict with the instant client. Run `unset ORACLE_HOME` to make sure you don’t have this environment variable. While the full Oracle still needs it, the instant client doesn’t require any more setup.

Run TOra and have a lot of fun!