Yet another Database Blog

Blog relocation

I've decided to move on from TypePad, and I'm now blogging at www.guyharrison.net/blog (RSS http://guyharrison.squarespace.com/blog/rss.xml )

See you there, where you can check out details of my new book!

Cloud computing and database artices

This has been a very exciting year for cloud computing - especially as relates to database technologies.  Amazon announced SimpleDB, Microsoft announced SQL Server Data Services and Google unveiled their big-table based Google App Engine.

For the first time in a long time, it looks like database technologies are a hot topic again. 

I've written some articles on cloud computing for Database trends and applications.  One on Amazon AWS,  one on Google App Engine and a two part article on next generation database technologies.  The first part of the article discusses the rise of the cloud database and the second part looks at Mike Stonbraker et al's proposals for next generation DBMS.

Uploading Oracle data to Google App Engine

Over the past 6 months, we've seen 3 significant cloud computing database announcements:

• SimpleDB from Amazon
• BigTable inside  Google App Engine
• Microsoft SQL Server Data Services (SSDS)

These cloud databases provide non-relational simple but scalable data services to applications that want to live in a public cloud.  While they currently exist in a rapidly expanding hype-bubble, it may well be that cloud databases represent the biggest paradigm shift in DBMS since the relational revolution in the 80s.

Anyway, I was lucky enough to get a Google App Engine account and have been playing with the data access API.  I wanted to load up some data I had in Oracle tables and after a bit of mucking around decided I'd write a general purpose loader which others might fine useful.

Using _spin_count to reduce latch contention in 11g

Back in 2003, I published the results of experimenting with various values of _spin_count to improve throughput on latch congested systems. You can find the original paper here.

The study involved varying spin count on an Oracle 8.1.6 database suffering from heavy cache buffer chains latch contention.  The database was running on a Windows 2000 server.  The results of this study confirmed that _spin_count can be very effective in improving the throughput of latch contention-bound systems.  In the test, throughput almost doubled when spin_count was raised from the default of 2000 to 10000.

I recently repeated these tests for Oracle 11g.  This time, the database was experiencing shared pool and library cache latch contention and was running on RHEL 5.0.  The results will be published soon in my contention series at ToadWorld.   To summarize however,  increasing spin_count in 11g was equally effective in reducing latch contention.  As _spin_count increased, latch  waits reduced and throughput increased, up until CPU saturated, at which point no further improvements were achieved.

Accessing Oracle from Powershell

I've used perl extensively - and other scripting languages from time to time - to write little unitities or simulations for Oracle.  Perl is available on windows, but it doesn't provide easy access to all the Windows API and .NET utilities (such as accessing WMI counters).  For that PowerShell would be the best choice.

It's not entirely obvious how to connect to oracle from Powershell.  Here's how I did it.

Systematic Oracle Performance Tuning

When faced with an obviously IO-bound database, its tempting to deal with the most obvious (symptom)  the IO subsystem  immediately.  Unfortunately, this usually treats symptoms rather the cause, is often expensive and usually futile.  Here's my article on a more systematic approach:

Systematic Oracle tuning (pdf)

and here's the powerpoint from the recent NOCOUG conference.

Grid control therapy

I've had a lot of bad experiencing installing and managing the OEM grid control.  Today has been the worst day ever and - as an alternative to self-mutilation - I decided that I would document my woes as they occur as a sort of therapy

Using EXPLAIN EXTENDED to see view query rewrites

At the MySQL Mini Conference in Sydney this week we discussed how to use EXPLAIN EXTENDED to view the rewrites undertaken by the MySQL optimizer.  IN particular, to see if MySQL performs a merge of the query into the view definition, or if it creates a temporary table.

D.I.Y. MySQL 5.1 monitoring

I wrote recently about using events and the new processlist table in MySQL 5.1 to keep track of the number of connected processes.  Although having the PROCESSLIST available as an INFORMATION SCHEMA table is usefull, it seemed to me that having SHOW GLOBAL STATUS exposed in a similar fashion would be far more useful.  So at the MySQL UC last year, I asked Brian Aker if that would be possible.  I know how many enhancement requests MySQL has to deal with, so I was really happy to see that table appear in the latest 5.1 build (5.1.14 beta). 

This table, together with the EVENT scheduler, lets us keep track of the values of status variables over time without having to have any external deamon running.  This won't come anywhere near to matching what MySQL have made avalable in Merlin, but still could be fairly useful.  So lets build a simple system using events to keep track of "interesting" status variables....

10g tracing quick start

Oracle’s released a few new facilities to help with tracing in 10g,  here’s a real quick wrap up of the most significant: