This is the 149th edition of Log Buffer, the weekly review of database blogs.
Let’s begin with the big-picture stuff. Jeremy Zawodny laid out his view of the state of MySQL in Linux Magazine’s blog, noting the rough transition between versions 5.0 and 5.1, the status of storage engines, and outside contributions.
Like Baron Schwartz’s Maatkit, for example. Baron announced this week that he is writing a book about Maatkit, and also seeking input for a new tool to verify MySQL upgrades.
Percona is surely one of the foremost outside contributors in the MYSQL world. This week on their MySQL Performance Blog, Peter Zaitsev and his readers discuss some ad hoc ways to copy a database, in his item on using netcat to copy MySQL Database.
Darren Cassar, MySQL Preacher, delved into the particulars of replicating from MySQL to *. “Unsurprisingly there are quite a few different tools to do it from any platform towards MySQL but very few which do it the other way round . . . I decided to have a look at doing the job myself. Although it might look an overkill to do so, it took me a few hours to find a solution and implement it and a couple more to test it and here is a simple description.”
As for engines, Mark Callaghan contributed a fine blog on the performance impact of prefetching in InnoDB. Mark writes, “There isn’t much data to indicate when this feature should be enabled. I have published data for a few IO-bound benchmarks. On these tests, the prefetching done by InnoDB reduces performance.”
A extensive discussion of security—vis-a-vis MySQL—took place this week. Ronald Bradford got it started with his item on basic OS/MySQL security. Followed by more basic MySQL security: “Never store the MySQL ‘root’ user password in a ~root/.my.cnf file.” That does seem pretty basic, doesn’t it. And yet it needs restating.
Lenz Grimmer responded with his thoughts on providing passwords on the command line. And with more on MySQL password security.
Ronald also got a lot of entries to his (now closed) contest for MySQL DBA fame and glory. The matter: what five settings would you add to redeem a very minimal my.cnf. (Here’s Ronald’s post with the winner.)
Sticking, for a moment, with the contest theme, Adam Machanic announced and explained the winning entries to his grouped string concatenation challenge from February.
On Claypole’s World, James Roland Jones presents The Curious Case of the Dubious Deadlock and the Not So Logical Lock. The scene is set thus: “My latest engagement has involved working with one of the largest Teradata installations in the world . . . To give you an idea of the scale; this system has over 40 Billion rows in just one of the fact tables… 40. Billion. That’s a lot of rows. During this engagement we encountered a rather bizarre issue that doesn’t seem to be well understood so I thought I’d blog about it. Get ready for a mind job…”
The rusano.com blog gave their response with %%lockres%% collision probability magic marker: 16,777,215.
On the Less Than Dot blog, SQLDenis asserts: your testbed has to have the same volume of data as on production in order to simulate normal usage, and shows exactly why.
Paul S. Randal was clearing up misconceptions around the log and log backups, which he begins so: “There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens. This is one of the reasons why I’m doing a whole spotlight session on this at PASS this year – the transaction log and its behavior is IMHO one of the most misunderstood parts of SQL Server.”
Lubor Kollar of the SQL Server Development Customer Advisory Team compared several methods of fast ordered delete, one of MySpace’s Senior Database Developer Kevin Stephenson’s “pain points”.
Let’s create a little friction and jump straight to Postgres-vs.MySQL. Robert Treat shares the first rule of Postgres Club. No, I’m not going to give it away—you’ll just have to read Robert’s post. Suffice it to say, though, that is has to do with Postgres advocacy and knowing when not to do it.
Robert also shares video of his PGCon ’09 presentation, Guide to Postgres 8.4.
Magnus Hagander describes his way of getting a range of entries centered around a point.
Nuno Souto says that size sometimes does matter. I’m way above getting attention from that sort of double-entendre, but what he’s talking about, in fact, is optimal memory limits for Oracle under AIX, at which he takes a long, hard look.
Dion Cho writes, “Similar cursor sharing and multiple child cursors are very common problems . . . What makes people confused about simiar cursor sharing is this. What the hell does ‘SIMILAR’ mean exactly?”
Tanel Poder, for his part, was diving into the shared pool and coming up with answers, his post, ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql
Here on the Pythian blog, Alex Gorbachev showed how to dynamically call a PL/SQL procedure.
On the AMIS Blog, Gerwin Hendriksen discussed enhancing fast queries using “tapio-indexes”, a.k.a “fat indexes”.
And you know what? I’ve got more blogs, but no more time. Let’s hear your favourite blogs in the comments! See you for LB#150!