Crisis has struck! This week’s Log Buffer editor had to beg off at the eleventh hour when his time vanished. It happens. But, in every crisis, an opportunity (well . . .  maybe, maybe not). The opportunity — an open discussion of this week’s best database blog articles. Readers in control.

Log Buffer is always looking for editors, so if you’d like to step forward and publish one on your own blog, read the Log Buffer guidelines and send me an email.

I’m going to go through my bookmarks and add my own presently. I hope to hear from you!

Welcome to the 110^th edition of Log Buffer, the weekly review of database blogs.

From the MySQL side, Jeremy Zawodny gets things going this week. He writes about his doubts over the long-term performance of InnoDB, specifically the cost of multiversion concurrency control, particularly in a master-slave arrangment or a DW. Jeremy comments, “[The] disk bloat, fragmentation, and ongoing degradation in performance may be an argument for having some slaves that keep the same data in MyISAM tables.” His readers, however, point out some diagnostics and tools to remedy this concern.

Not that MyISAM is without foibles. Case in point, on the MySQL Performance Blog, Peter Zaitsev says, Beware of MyISAM Key Cache mutex contention. “. . . inserting in parallel into different tables when indexes fit in memory results in very bad contention causing hundreds of thousands of context switches per second with far less work done than one would hope.” Happily, as Peter reports, multiple key caches mitigates.

Peter also surveys the choices for picking a datatype for STATUS fields — “. . . ’new’, ‘confirmed’, ‘in production’, ’shipped’ status of job, message etc. People use variety of ways to handle them often without giving enough thought to the choice which can cause problems later.” VARCHAR(N), ENUM, TINYINT — which is right for the job?

Here on the Pythian Group Blog, Nicklas Westerlund demonstrates adding a unique constraint on a table with duplicate data.

Also here at home, Sheeri Cabral’s item on why you want to switch to MySQL 5.1. She argues that additions such as online ALTER TABLE, online table-based logging, and INFORMATION_SCHEMA.PROCESSLIST are compelling features worth an upgrade.

(more…)

It’s time again for another edition of the weekly review of database blogs, Log Buffer. Since it was a big week for SQL Server, let’s start there, shall we?

The big news — SQL Server 2008 is released, as reported by SqlServer-qa.net, in seven different versions. Aaron Bertrand introduces a new kid on the block: SQL Server 2008 Web Edition — “. . . designed for highly available Internet facing web serving environments for the next generation Windows Server,” according to MS. (I’ve heard it rains quite a lot in Seattle. I guess it also Drizzles, too.) Says Aaron, “Basically, it has higher scalability than Express or Workgroup Editions : it supports up to 4 CPUs, no artificial limit on RAM, and unlimited database size.”

(Before you crack open your server case, Tara Kizer on Ramblings of a DBA has some advice on how to get physical CPU count on a server.)

The SQL Server Express Express blog helpfully offers a table outlining the differences in the features of the differences between SQL Server 2008 Express editions.

A couple little issues with the release have already emerged. Here’s Euan Garden on SQL Server 2008 Installation Confusion, VS 2008 Sp1 and NetFx 3.5 Sp1: “SQL Server 2008 has dependencies on (and includes) VS 2008 SP1 and its components (BIDS is just VS 2008, SSMS uses components), plus NetFx 3.5sp1. SQL includes the RTM versions of both of these, HOWEVER they are not broadly released yet. There is a check in SQL Server Setup that if you have an old version (Beta, RC, whatever) it will bounce the install and ask you to upgrade to the RTM bits…which of course are not yet available independent of SQL.” Click through for the KB and some other help. Denis Gobo and his readers have some more to say about this.

In all this change, you may lose sight of an important fact or two, and you may ask yourself, how did I get here . . . what version of SQL Server am I running? A couple resources are shared by Kevin Kline.

(more…)

Welcome to the 108^th edition of Log Buffer, the weekly review of database blogs.

With almost no ado at all, let’s begin with the bad news–from StatisticsIO and Jason Massie: The Death of the DBA. And who is the perpetrator of this crime? The Cloud! It sounds like something from a John Carpenter movie, doesn’t it?

Let’s see what Jason is thinking. “I’d like to retire a SQL Server DBA with 40 years experience but I don’t think that will happen. The cloud is coming and it is bad news administrators, database or otherwise.  . . . Let’s make some assumptions. The features get there. The availability gets there. The platform basically matures . . . Now put yourself in the IT decision maker’s shoes. No upfront capital expenses, no managing backups, and no patch management.  . . . If they can remove their focus from managing and deploying IT, they sell and service more widgets.”

Scary stuff, right? Well, the commenters don’t entirely agree. I think it will be at least a factor, but I wonder how many managers will look at “The Cloud” and feel uncomfortable about privacy, data retention, and the like. (For myself, I couldn’t even endorse the idea of putting this blog’s comments into “The Cloud”.) What do you think?

Elsewhere on StatisticsIO, Jason has a note about MSDN’s SQL Heroes contest, whose aim is to, “. . . create a community project in CodePlex based on SQL Server 2008.” Jason also links to a list of CodePlex’s active SQL Server projects.

Turning to matters technical, Jeff’s SQL Server Blog offers a lesson on converting input explicitly at your client: don’t rely on the database to “figure it out”. Jeff takes the example of formatting dates, and show both the right and the wrong way, writing, “I’ve said it over and over and I’ll say it again: The concept of formatting dates should never be something that your database code should ever worry about.”

On the Less Than Dot blog, SQLDenis observes that converting columns to date from datetime does not result in a scan in SQL Server 2008. What you get instead is a seek, as he demonstrates.

Indexing Foreign Keys - should SQL Server do that automatically? So asks Greg Low on the The Bit Bucket. “By adding indexes on the foreign keys on three tables,” he writes, “we saw a reduction of 87% in total I/O load.  . . . it really struck me that having SQL Server do this by default would avoid a lot of apparent performance problems.  . . .  Should SQL Server simply do this by default when you declare a foreign key reference?”

Kent Tegels of Enjoy Another Sandwich — riddle me this, riddle me that! “When is a bug not a bug?” I give up, Kent. When is a bug not a bug? (more…)

Andrew Clarke has published to 104^th edition of Log Buffer, the weekly review of database blogs, on Radio Free Tooting, marking LB’s second year. Happy Birthday, LB!

Log Buffer always needs editors, so if you you’d like to present your view of the week that was in DB blogs, contact me, the Log Buffer coordinator. You’ll be joining some of the best bloggers around, and making yourself and your blog a little better known to readers around the world.

And now, here’s Andrew Clarke’s Log Buffer #104.

P.S.: To our readers in the U.S. — Happy 4^th of July!

Welcome to the 103^rd edition of Log Buffer, the weekly review of database blogs.

Starting with Oracle stuff, Chen Shapira (just a simple DBA on a complex production system) is looking for great PL/SQL. Why? To become a better PL/SQL programmer. “But,” she writes, “for PL/SQL , I’m a bit stuck. I can still read my own code for bad examples, but where can I find examples for great code?  . . .  Somehow, there is simply no open-source code written in PL/SQL that I can read to get a good idea of how PL/SQL should be written.” Niall Litchfield recommends the contents of $ORACLE_HOME/rdbms/admin. Any other ideas to help Chen?

There was no shortage of people offering their help, as always. Beginning here on the Pythian blog, Grégory Guillou posted the fifth of his ten-part series on Oracle silent mode, this part covering adding a node to a 10.2 RAC. Says Grégory, “Even if you don’t leverage RAC’s ability to add or remove nodes to gain in agility, it’s still very likely you’ll come to these techniques when you want to upgrade some of your Servers or Operating Systems.” The post includes links to the earlier articles in the series.

On his Core IT for geeks and pros, Tanel Poder expounds another use case for WaitProf. He begins, “I recently diagnosed a performance issue where the ‘events in waitclass Other’ occasionally took significant part of the session’s response time.  . . . So, what to do when this ‘events in waitclass Other’ wait becomes significant in the response time profile?” This is where waitprof, his custom tool, comes into play.

Jeff Hunter of the So What Co-operative posts Something to be aware of, Part II, in which he and Oracle Support tussle over NUMA in Linux, and where he is heard to say, “My next step is talking to a Duty manager. I really don’t want to do that.” And Part III, which Jeff begins, “I escalated my issue to a duty manager . . .”

On Oracle Applications DBA, Aviad Elbaz knows how to do tricks with a pipe, as he shows in his article on how to execute TKPROF on trace files larger than 2GB. Aviad, I think Bilbo and Gandalf would be proud.

Are you like me, shamefully ignorant about the whole Business Intelligence thing? Do you, too, secretly believe it might be an oxymoron? Mark Rittman is here to help, with an introduction to the Oracle BI Applications.

Vivek Sharma of Database/SQL Experiences informs us that he will be contributing a seminar on “Real Time Performance Tuning” to the very first event mounted by the IOUG (All India Oracle User Group). This is their TechNight, taking place in Hyderabad on Friday, July 18th.

(more…)

Welcome to the 102^nd edition of Log Buffer, the weekly review of database blogs.

Since it was DB2’s 25^th birthday this week, as Anant Jhingran reports, let’s start with it.

From ZDNet this week came a story that IBM was considering the open-sourcing of DB2 — big news, naturally, whether true or not. Matthew Aslett of 451 CAOS Theory says, Open source DB2? I don’t think so, suggesting that it was merely theorizing on the part of one IBM executive, and hype on the part of ZDNet. “Then of course,” Matthew continues, “there is the issue of why IBM would open source DB2. Where is the business driver? Despite solid competition from Oracle and Microsoft, the company is doing pretty well with DB2 as it stands, thanks very much, and open source databases have had minimal impact on the established vendors.”

Matt Asay of The Open Road likewise comes down on the uh-uh side of this. “It was . . . no surprise to see IBM quickly follow up ZDNet’s article with a blunt statement: ‘IBM has no plans to open source DB2.’ Of course it doesn’t.  . . . The day that it needs DB2 to undermine Oracle’s database, however, we may see IBM making a similarly bald statement…in the other direction.”

On An Expert’s Guide to DB2 Technology, Chris Eaton offers some news on DB2 videos, including ChannelDB2’s video tutorials, IDUG’s video competition, and a funny video from IBM on the making of a “viral” industrial video. Such as itself.

On to MySQL now. Keith Murphy made his debut on the Pythian blog this week, with a link to some video tutelage of his own — his presentation to the Boston MySQL Users Group on backups. Jay Pipes also offers the slides for his address on Join-Fu: The Art of SQL (I and II). (I rarely cover presentation materials when I do Log Buffer, but these ones of Jay’s looks fantastic.)

Mark Schoonover reports that Keith’s and his 2008 MySQL Magazine survey is officially closed, adding, “Now the fun begins compiling all the data and pretty charts made. We’ve had 432 responses!”

Baron Schwartz of xaprb describes how to write a lazy UNION in MySQL. No, it doesn’t deal with posting a complaint about undefended job benefits. Baron explains: “Something occurred to me a couple of weeks ago: why not write a UNION that stops executing as soon as one part of it finds a row? Then you can UNION to your heart’s content and not incur the overhead of that second lookup unless you need it. For lack of a better term, I’m calling this a lazy UNION.”

(more…)

Welcome the the 101^st edition of Log Buffer, the weekly review of database blogs.

This edition was originally claimed by Ward Pond for his SQL Server Blog. Unfortunately, Ward is, in his own words, “dealing with the aftermath of a burst appendix,” which is a very good reason not to spend your time at the computer. Ward, heal up soon! We’ll see you on LB before too long.

In lieu of the normal Log Buffer, I throw it open to our readers. Please leave a comment mentioning your favourite database blog items from the week that was, and anything else you care to say about them.

LB will be back to normal next Friday. See you then!

Log Buffer, the weekly review of database blogs, is 100 editions (and almost two-years) old today! Lewis Cunningham has returned to LB to publish The Big 100^th edition of LB on An Expert’s Guide to Oracle Technology.

No speech, but I would like to thank Log Buffer’s readers and especially all of Log Buffer’s editors for making LB a worthwhile and fun stop in the database “blogosphere”. It’s very easy to see why LB editors are successful in what they do — they are consistently enthusiastic, diligent, and adaptable. And I enjoy working with them.

Okay, okay — I can hear the orchestra starting to play me off, so let’s get to it. Here’s Lewis’s Log Buffer #100.

Or, How to Be a Command-Line Commando

Does it surprise you to learn that I’m a Linux guy? I’ve been using Linux, to the exclusion more-or-less of everything else, since about 1999. In the past, I’ve done a little programming and some junior system administration. I’m even LPI-certified.

With this background, I’m quite comfortable working in the shell (AKA the command-line), the natural habitat of the sysadmin^[1]. I frequently open a shell to do some quick work, and when I do, I use GNU’s Bash, which is the default on most Linux distributions. (I believe it’s also the default shell in Mac OS X.)

One of Bash’s features is editable command-line history, which makes your current command-line and its entire history available to you as an editable buffer. That offers a great way to streamline your work in the shell.

I suspect, however, that many shell users don’t even know about this better way. And it baffles me that many SAs I have seen in action — including some of Pythian’s own — don’t use this. They almost seem to prefer unnecessary effort — smashing away at their keyboards, repeating themselves, deleting with the Backspace key, scrolling, forwarding their cursor one character at a time, copying and pasting with the mouse, and so on. That’s a lot of elbow grease.

With Bash, or any other shell that uses the GNU readline library, you can use the following Emacs-like key-chords to make your life better. The point of this (as with so many things sysadmins and programmers do) is to save you effort, viz. typing. These aren’t all of them; they’re the ones I use:

(more…)