Hello, and welcome to the 171st edition of Log Buffer, the weekly review of database blogs. Let’s get it going this week with . . .
Uwe Hesse, the Oracle Instructor look at result cache, another brilliant 11g new feature. He says, “There are many amazing New Features in the 11g version, one of them is the possibility to cache the result sets of statements, that access large tables but return relatively few rows. Think of it like automagically created materialized views inside the SGA.” Commenters contribute some thoughts on problems with result cache and latch contention.
Christian Antognini is, as always, Striving for Optimal Performance. He has a worthwhile post on instance caging, ” . . . nother small but useful feature of Oracle Database 11g Release 2. Thanks to it the database resource manager is able, for the first time, to limit the number of CPUs that can be used by a given instance.”
And here is his Tuesday report on UKOUG so far (as of Tuesday).
Kellyn Pedersen wears rugged DBA Kevlar. Here is her post, ORA-01427: single-row subquery returns more than one row. “A developer friend of mine,” she writes, “sent me an email today . . . ‘I have a report query that worked fine on Wednesday and today produced [an] error. So when I went to track it back through Toad, the query just flat out stopped working.’ . . . Here was my explanation and a few hints to correct the problem . . . ”
Ah, developers. David Aldridge, the Oracle Sponge, has some fun with DBMS_Xplan.Display. He says, “Why not amuse and insult your developers at the same time by setting up the following situation . . . ” Oh, real mature, David.
In case anyone needs reminding, Oracle RDBMS != MySQL RDBMS. So writes Mark Callaghan on High Availability MySQL. Once this precept is understood, Mark asks, “Can we get this done and return our focus to the roadmap for 5.4, 6.0 and the MySQL User Conference?”
Now that MySQL conference season is over, there are plenty of good technical posts.
In his MySQL Diary, Hazan Ilan shows how to produce random rows from a table. He writes, “A while ago, I were searching for a way to produce random rows from a table in MySQL. I found several solutions but none of them satisfied me. . . . My solution to this problem is using User Defined Variables.” The commenters offer some constructive criticism and variations on the theme.
Kevin Burton wonders, is efficient client-side paging full table scanning impossible with MySQL? “It seems to be impossible to perform client-side paging full table scans within MySQL.” Ryan Thiessen comments, “Have you used HANDLER before in MySQL? It’s a little-used command but may be exactly what you need here . . . ”
Shlomi Noach shares his thoughts on restoring a single table from mysqldump. “Given a dump file (generated by mysqldump), how do you restore a single table, without making any changes to other tables?” writes Shlomi, and he offers two approaches: a security-based solution, and a text-filtering-based one.
Giuseppe Maxia writes, “Comparing database schemas is one of those DBA tasks that occur all the time. . . . If you can afford the luxury of having a GUI, then MySQL Workbench is the tool for you. . . . If the only thing at your disposal is a command line interface . . . then this Poor man’s schema comparison tool may come handy.”
Ronald Bradford was into the general schema things: Is your database schema in sync? he wonders, and suggests a particular tool for putting yourself in the position to give the right answer.
How do you use SQL Server? That’s a question making the rounds in the SQL Server ‘sphere, and here are a couple answers. This is the Scary DBA’s one. He tagged Jeremiah Peschka and Tim Ford.
Tim replied, Good Data Saves Lives. And here’s what Jeremiah had to say (he has tagged Tim Benninghoff and Mladen Prajdic).
Here’s another blog opportunity. Adam Machanic is offering an invitation to participate in T-SQL Tuesday #001: date/time tricks. He says, “T-SQL Tuesday is the SQL Server blogosphere’s first recurring, revolving blog party. . . . If you bothered to read the title of this post you’re aware that the topic for this month is Date/Time Tricks.” Sound like a wingding! Go read Adam’s post for the full details.
Maybe Kendal Van Dyke’s post on what it means to be a production DBA could be the start of another viral blog thing. Or the next-to-start—it’s a reply to an older post by Tim and Lori Edwards. In Kendal’s opinion, the answer (for the Bobs at least) comes down to, “I keep the lights on and the wheels turning.”
Thomas LaRock, SQL RockStar, describes the finer points of restoring SQL 2005 Master using LiteSpeed. “[Do] you know how to restore master from a LiteSpeed backup? . . . What could be easier? Well, lots of things, actually. Turns out that unless you have your process rehearsed you could be fumbling for answers at 3AM. Not good times.”
From Jeff Davis’s Experimental Thoughts comes this post on Postgres and the Linux OOM Killer. “The Linux OOM Killer heuristic,” he says, “can be summed up as: 1. Run out of memory. . . . 2. Kill PostgreSQL. . . . 3. Look for processes that might be using too much memory, and kill them, hopefully freeing memory.” Yes, that would make any Postgres DBA feel kind of ranty.
But if you’re undaunted, An almost idiot’s guide to Install and Upgrade to PostgreSQL 8.4 with Yum could prove very valuable. Yum being Fedora’s package management system. The post comes from the Postgres OnLine Journal.
And that is all for now. Please leave a comment with your favourite DB blogs from the week gone by, and we’ll see each other again in a week’s time. Till then!