Welcome to the 110th 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.
One new feature not on MySQL’s immediate horizon is a TYPEOF() function. On Xaprb, Baron Schwartz sets about to make up for this shortfall with his piece on how to emulate the TYPEOF() function in MySQL.
Johan Andersson introduces a new monitoring tool for MySQL clusters, called CMON. The précis: “CMON aggregates information from MySQL Cluster that earlier was only accessible from the cluster log or the management client, such as: cluster state, node state, backup statistics, statistics, cluster events (cluster log basically), and [lets] you access the information using SQL, because CMON logs the information into ordinary MYISAM tables!”
Before we leave the MySQL world, Ivan Zoratti offers 101 MySQL stories, a little parable about MySQL and another DBMS. It begins, “Hi, My name is Larry and I am an Oracle DBA.”
Hrm . . . Well, I’m sure Ivan’s choice of the name “Larry” was purely arbitrary. But it does segué very nicely with the latest installment in The Adventures of Ace, DBA, in which Ace wonders, “. . . what would Redwood City look like if Larry Ellison bought the whole thing?” The answer — Larryland, featuring attractions such as Ellison Manor, Ellison Manor (standby), and The 1st National Bank of Ellison, all looking out over picturesque Ellison Bay.
Oh, and Steve Karam, the Oracle Cartoonist Alchemist reports, 12g Says Goodbye to Raw: “. . . Oracle 12g will not support raw volumes for Datafiles, controlfiles, redo logs, OR voting disks and OCR. Those of us acquainted with RAC might be shouting ‘WHAT?!?!’ at about this time. This is because without raw volumes, our only choices for OCR and voting disk placement seems to be a CFS (such as OCFS).”
On My Oracle World, Mihajlo Tekic writes, “Last Friday there was one post on OTN forums that brought my attention. The OP was wondering ‘Is Data Guard Buggy’ with attention to Fast-Start Failover (FSFO) feature . . .” The answer to the original poster’s question, according to Mihajlo’s tests is that fast-start failover is reliable.
Tanel Poder offers up a script to display valid values for multioption parameters.
Arup Nanda has a story of a successful investigation into expensive full table scans on RAC, complete with molasses-like performaance and ignored indexes. Real Oracle sleuthing here.
Another guy wearing the deerstalker hat was Richard Foote. He has an excellent look at how the CBO deals with indexes and NOT EQUAL and NOT IN conditions.
I should also mention that Pythian’s Grégory Guillou has published the seventh part of his series on Oracle silent mode.
We haven’t heard from our friend Doug Burns in a while, so here are two from him. The first is Part One of a series, An Infinite Capacity for Waiting, on the discrepancy between times reported by Statspack and what appears to be the “real” time taken. Doug also has a book review of Oracle Press’s Oracle Automatic Storage Management.
Let’s move on to things SQL Server-ish. On PSS SQL, Bob Ward of MS follows up on the problems with the install of 2008 alongside Visual Studio: SQL Server 2008, Visual Studio 2008 SP1, and .Net Framework 3.5 SP1 explained.
On With CLUE as (Select * from Random_Thought ORDER BY Common_Sense DESC), Geoff Hiten links to the feature Pack for SQL 2008: “Cool goodies include stand-alone installers for SQLCMD and the SQL Native Client, SQL 2008 Server Management Objects, SQL 2008 pre-defined Policies, and lots more.”
Madhivanan offers a quick fix for new users’ common migration issues between version 2000 to 2005.
Over on SQLblog.com, Tibor Karaszi asks the question, are inserts quicker to heap or clustered tables?. He begins, “One might think that a heap has lower overhead because it is a … heap. But hang on for a second and think about what happens when you do an insert . . .” He conducts a test to provide the answer.
Joe Webb looks into how a sleeping process can still holds SQL Server locks.
In PostgreSQL blogs this week, we start by going back to Sheeri, on her Technocation blog, where she has on offer OSCon 2008 Video: Josh Berkus’s Safe Data is Happy Data, which she writes, will also be applicable to non-Postgres platforms.
On the always-reliable select * from depesz;, Hubert Lubaciewski looks at what at first appears to be a bug in Postgres’s NULLs vs. NOT IN().
Andrew Dunstan also has a can of Raid in hand. He has a bug related to “ERROR: could not open relation with OID 256266229” and recovery of a table.
Also staring into the gaping maw of nothingness was Jeff Davis, with his item on None, nil, Nothing, undef, NA, and SQL NULL, in which he looks at the mismatch between these concepts in the Postgres DBMS and at the application level.
Finally, Lewis Cunningham has some suggestions for new database taglines. He writes, “Do you know what a tag line is? It’s those nifty phrases that come after a product or brand to get your attention. Like ‘Quality is job 1’ or ‘Have it your way’. . . . Here are a few tag lines I’d like to see.” Excerpts: “Oracle – Expensive. Because we can be.”; “DB2 – Secure. Scalable. Robust. #2. WTF?!”; “PostgreSQL – It’s all academic anyway.”; “MySQL – Everywhere you want to be. We don’t even believe some of the places we are.” Very funny stuff, Lewis!
That’s all for now. See you in a week’s time.
No comments