PlanetMysql.ru — информация о СУБД MySQL

A very warm welcome to the Log Buffer, the premier medley of fresh information culled from the blogs related to the technology which stores the world, yes, the databases.

In this edition, the Log Buffer #205, we have yet again found the pulse of the industry.

Oracle:

On the Oracle front, leading Oracle technologist Andrey Goryunov carries on his hands-on experiments of newest version of the Oracle database. This time he slices away chopt.

It’s always very informative and exciting to know about internals of RAC Stuff like what actually is maintained in the Voting Disk . Riyaj has it here.

Jonathan Lewis does a little thought experiment with list partitioning.

HugesPages almost always provide value to the Oracle databases on the Linux Systems, and many people wonder why they are not the default. Kevin Closson touches some points regarding HugesPages, and he also notes down some finer points like the dislike of AMM and Hugespage for each other.

Hardly anyone would refuse a gift consisting of chocolate, ice cream, flower and designer watch. Yes, now you can have Tanel Poder, Cary Millsap, Jonathan Lewis, and Kerry Osborne at one Virtual Oracle Conference.

Tim Hall, like many other people is perturbed over the plagiarism of his articles.

Oracle recommends that you use JRockit JDK with your Oracle products and the reasons are described by JaySenSharma at his Weblogic Wonder blog.

DB2:

Now, RPM and DEB packages for DB2 Express-C are available for the download. Get it from here.

Troy Coleman, blogs about the ripple-creating news that Last week IBM announced the general availability of DB2 10 for z/OS.

The keynote session for the third day of the IOD conference features the authors of Freakonomics, Steven Levitt and Stephen Dubner, and Craig Mullins highly recommend that.

SQL Server:

If you are curious about the title “Plumbing The Depths of SQL Server / PowerShell Integration, then don’t miss SQL Server Connections conference on Nov 1-4 in Las Vegas and attend the session by Bob Beauchemin.

Though not earth-shattering and sky-ripping, but very valuable nonetheless this post by Jeff about calculating the median.

And following is the ever-green SQL Server Myth buster posts by Euan.

MySQL:

Zack Urlocker rambles on how open source software, cloud and software as a service are helping to bring about the consumerization of IT.

Here is one more effort where the bencmarking of MariaDB is being done.

Have a nice weekend.

PlanetMySQL Voting: Vote UP / Vote DOWN

Welcome to Log Buffer, the weekly roundup of happenings in the database world.

Lots to cover this week, so let’s get on with Log Buffer #204. Enjoy!

Oracle:

Pythian’s Gwen Shapira dabbles with MySQL and explores MySQL troubleshooting for the Oracle DBA.

Venkat Janakiraman explores how connectivity works for BI EE 11g on Microsoft SQL Server Analysis Services 2008.

Iggy Fernandez explores SQL 101: Which Query is better, in part II to a post he covered in summer of this year.

Chet Justice, on Oraclenerd reviews how to use forgotten function OBIEE: Evaluate

Tanel Poder announces last chance for early-bird rates to sign up for the virtual conference on Systemic Oracle SQL Optimization featuring himself, Cary Millsap, Jonathan Lewis & Kerry Osbourne.

DB2:

Lots going on at the IOD 2010 conference over the past week. Craig Mullins covers the event with news, a video of attendees, and the final keynote.

MySQL:

Sheeri Cabral shares how she determines MySQL fragmentation.

Baron Schwartz posts the third in a series of posts on MySQL limitations – one thread per connection. In case you missed them, part 1 covered single-threaded replication, part 2, the binary log, and part 3, subqueries.

SQL Server:

On In Recovery, Paul S. Randal invites readers to participate in a survey to determine wait times on systems. Chime in with your feedback by commenting on his blog post or sending him an email after reading the instructions. Paul is also calling for participants for T-SQL Tuesday #12 – Why are DBA skills necessary.

And lastly in Postgres news, PG West 2010 is happening next week. There are a number of posts on the need for replication in PostgreSQL 9.0. Joshua Drake stirred the pot, responded and created a Replication poll to find out what you really think. Cast your vote!

Happy Haunting weekend.

PlanetMySQL Voting: Vote UP / Vote DOWN

This is the 182nd edition of Log Buffer, the weekly review of database blogs. Make sure to read the whole edition so you do not miss where to submit your SQL limerick!

This week started out with me posting about International Women’s Day, and has me personally attending Confoo (Montreal) which is an excellent conference I hope to return to next year. I learned a lot from confoo, especially the blending nosql and sql session I attended.

This week was also the Hotsos Symposium. Doug’s Oracle Blog has a series of posts about Hotsos. If all this talk about conferences has gotten you excited, Joshua Drake notes that 14 days and the hotel is almost full for postgresql conference east which is March 25th-28th in Philadelphia. And the Oracle database insider notes that the Oracle OpenWorld call for papers is now open.

According to Susan Visser this week (ending tomorrow) is also read an e-book week. So if you have not already done so, read an e-book! She links a coupon for an e-book in the post.

Craig Mullins notes that the mainframe is a good career choice in Mainframes: The Safe IT Career Choice. He notes that the mainframe is still not dead:

People having been predicting the death of the mainframe since the advent of client/server in the late 1980s. That is more than 20 years! Think of all the things that have died in that timespan while the mainframe keeps on chugging away: IBM’s PC business, Circuit City, Koogle peanut butter, public pay phones, Johnny Cash… the list is endless.

In other career-related news, Antonio Cangiano is looking for [2] top-notch student hackers for a 16-month internship at IBM in Toronto starting in May. All the details, including how to apply, are in Cangiano’s blog post.

Willie Favero wants to know how you “solve the batch dilemma” for issues like “shrinking your batch window, designing your batch to play nicely with … OLTP” in how’s your batch workload doing? Perhaps Favero should read the updated batch best practices posted by Anthony Shorten.

Bryan Smith surveys a more personal question by asking if you go both ways and “manage both DB2 for Linux, UNIX, and Windows and DB2 for z/OS” in don’t ask, don’t tell, bi-platform DBAs. This week’s Log Buffer editor admits to being a tri-platform DBA — she has tried many platforms, and in fact, many databases (MySQL, Oracle, DB2, SQL Server, Sybase, Postgres and Ingres)!

Hari Prasanna Srinivasan promotes a patching survey in Oracle really wants to hear from you! Patching Survey.

Henrik Loeser explains what a deadlock and a hot spot are by using a real life analogy taken from a police report in deadlock and hot spot in real life.

Jamie Thomson asks why do you abbreviate schema names?. Shlomi Noach tries to solve the issue that “there is no consistent convention as for how to write [about table aliases in] an SQL query” in proper sql table alias use conventions. Noach also gives us a tip: faster than truncate.

Leons Petrazickis reminds us that “rulesets are chains” and it is important to have your rulesets in the proper order in iptables firewall pitfall.

Anyone interested in the history of MySQL AB will be informed after reading Dries Buytaert’s article.
Gavin Towey shares his software that helps centrally manage 120 MySQL servers in qsh.pl: distributed query tool For those who want to learn more about column-oriented databases, particularly in MySQL, Robin Schumacher of the InfiniDB blog announces that there is a MySQL University session recording on MySQL column databases now available. MySQL join-fu expert Jay Pipes has moved his blog to www.joinfu.com and starts with An SQL Puzzle and of course a follow up on the sql puzzle.

Ivan Zoratti is happy that finally, slides posted for the MySQL DW breakfast. Venu Anuganti gives you tips on one of the most common MySQL frustrations: optimizing subqueries in how to improve subqueries derived tables performance. Justin Swanhart posts the way in which he Gets Linux performance information from your MySQL database without shell access and emulates a ‘top’ CPU summary using /proc/stat and MySQL using the same method.

The Oracle Apps blog has an introduction to Oracle user productivity kit (UPK). Even though in this editor’s opinion the article is very sales-pitchy, it has valuable information, and does indeed live up to its promise:

UPK is a software tool that can capture all the steps in a system process. It records every keystroke, every click of the mouse, each menu option chosen and each button pressed. All this is done in the UPK Recorder by going through the transaction and pressing “printscreen” after every user action. From this, without any further effort from the developer, UPK builds a number of valuable outputs.

Allen White gives a great tip on how to optimize queries in keep your data clean.

Mike Dietrich reminds you to remove “old” parameters and events from your init.ora when upgrading, “as keeping them will definitely slow down the database performance in the new release.” He shows evidence of slowness when this is not done. Dietrich also shows how you can be gathering workload statistics “to give the optimizer some good knowledge about how powerful your IO-system might be”, especially “a few days after upgrading to the new release…while a real workload is running.”

Brian Aker shows the exciting features coming soon in Drizzle in Drizzle, Cherry, Roadmap for our Next Release.

Maybe you are thinking of migrating, not upgrading…..The O’Reilly Radar shows how to asses an Oracle to MySQL migration in MySQL migration and risk management. Actually, that article interviews Ronald Bradford on the subject — Bradford has been prolific lately, updating free my.cnf advice series and “Don’t Assume”: MySQL for the Oracle DBA series. Nick Quarmby also talks about migrating Oracle, but not to a new database, just to a new platform, in his primer on migrating Oracle Applications to new platforms. And the big news comes from Carlos of dataprix that Twitter will migrate from MySQL to Cassandra DB.

Paul S. Randal explains his way of benchmarking: 1 Tb table population on SQL Server.

Pete Finnigan shares his slides from a webinar on how to secure oracle, and Denis Pilipchuk shares his approaches for discovering security vulnerabilities in software applications.

Jeff Davis shares his thoughts about scalability and the relational model. Robert Treat responds actually, the relational model doesn’t scale and Baron Schwartz counters with NoSQL doesn’t mean non-relational.

Buck Woody explains “whenever you want to know something about SQL Server’s configuration, whether that’s the Instance itself or a database, you have a few options” — and of course what those options are — in system variables, stored procedures or functions for meta data.

This week’s T-SQL Tuesday topic was I/O. There are many links to great blog posts in the comments; three random posts I chose to highlight: Michael Zilberstein talks about IO capacity planning, while Kalen Delaney talks about using STATISTICS IO in I/O, you know, and Merrill Aldrich chimes in with information on real world SSD’s. Aldrich also begs folks not to waste resources and make more work for developers and DBAs in dear ISV, you’re keeping me awake nights with your VARCHAR() dates.

And we end with a bit of fin: Paul Nielsen wants us all to have a bit of fun; he has posted an SQL limerick and asks readers to create there own in there once was in Dublin a query.

PlanetMySQL Voting: Vote UP / Vote DOWN

Hello and welcome to Log Buffer #180. Time’s a-wastin’, so let’s go!

Oracle

There was so much Oracle stuff this week that I’ve decided to cram a little more of it into Log Buffer by providing a little less context than usual.

Jonathan Lewis shares an explication of aliases: “I was asked the following question recently: ‘Does the use of table aliases affect performance?’ To which the best answer is probably ‘Yes, though in general you probably won’t notice the difference and there are reasons more imporant [sic] than performance for using table aliases.’”

Doug Burns continues his most recent series: Statistics on Partitioned Tables – Part 2, and Statistics on Partitioned Tables – Part 3.

Charles Schultz demonstrates how VPD + bad ANYDATA practices can really bite: “The point of my blog was that using CAST can really screw up your data. Oracle Support is filing a bug on this behavior, as it looks like an overflow problem.”

Pythian’s Gleb Otochkin begins a series on Oracle GoldenGate installation.

Guy Harrison provides a thorough introduction and recommendations on memory management for Oracle databases on VMWare ESX.

Robert Vollman returns to blogging and offers his 10-point plan on improving your SQL queries.

Jared Still sheds some light on a cool but unknown RMAN feature.

Richard Foote knocks holes in another myth: “One of the great myths in Oracle is that bitmap indexes are only suitable and should only be used with columns that have so-called low cardinality (few distinct) values.”

Alexander Kornbrust shares a link to a really good whitepaper about “Hacking Oracle from the Web” by Sumit Siddarth.

Eddie Awad shares a link to a SQL injection prevention cheat sheet.

Charles Hooper answers the question, What is the meaning of the %CPU column in an explain plan?.

Meanwhile, Harald van Breederode does the same for this one: Why does the size of my ORACLE_HOME increase?

SQL Server

Thomas LaRock gives an recap of MS’s 2010 MVP Summit. Quotable take-away: “If I had to compare SQL 2008 R2 to SQL Server 4.0, I would say the difference is the same as comparing an F1 race car to a Chevy Vega.”

Half a world away, there is the SQLSocial Event – London March 16th, as advertised by Simon Sabin.

Simon also shares a script to get indexes and their included columns, beginning, “I get increasingly frustrated with the lack of visibility of included columns in management studio and from the system stored procedures sp_… This is a query that returns all indexes and there key and include columns[.]”

Andy Leonard throws us another nourishing SSIS snack: conditional split outputs.

Here’s Rob Farley with a book review of an oldie but a goodie: Inside SQL 2005 Query Tuning and Optimization, by Kalen Delaney et al. “If you spend any time tuning SQL Server databases, then this book will feel much thicker than it really is, and you’ll be finding useful information on just about every page.”

Thomas LaRock, meanwhile, writes that SQL Server 2008 Query Performance Tuning Distilled is
a good way to start your day. “Each morning, while I wait for my desktop to boot, I pick up their book, turn to any page, and just start reading.”

MySQL

Sticking with the theme a little longer, here is Baron Schwartz with a review of Understanding MySQL Internals by Sasha Pachev. “I should have read this book a long time ago, and it’s my loss that I didn’t.  . . .  Overall, this book is easily a high 4 stars on a scale of 5, and again, anyone seriously using MySQL should have it.”

Baron also shares a link to Oracle guy Cary Millsap’s Thinking Clearly about Performance paper.

Brian “Krow” Aker starts an extensive conversation with his post, Protocols, The GPL, Influences from MySQL. His thesis, “MySQL was the company that had the most influence on how companies and investors viewed the GPL.”

Paul Vallée of Pythian responds with his ideas on product management, effective developers, and the future of MySQL. “ . . . the future of MySQL, Drizzle, Monty Program, the Percona fork, etc.” to be more precise.

Colin Charles provides news of what’s been happening recently in MariaDB #1.

Mohammad Lahlouh wonders, can I use latin1 to store utf8 data? and gets several answers from his readers.

He might have asked Ronald Bradford, who knows this stuff. Here is his post on migrating MySQL latin1 to utf8 – character set options.

Pursuing a similar matter (collations), Roland Bouman opines, the best stored routine is the one you don’t write.

PostgreSQL

Baron Schwartz again! He announces, mk-query-digest now supports Postgres logs.

David Fetter says, part(ition)ing is such sweet sorrow. “There are excellent references on partitioning tables that depend on one table, but what happens when you need to partition the referenced table? Let’s find out!”

Bruce Momjian is here with news on the Python driver confusion.

Jon Jensen of End Point’s Blog posts a HOWTO on PostgreSQL EC2/EBS/RAID 0 snapshot backup.

NoSQL, Etc.

Chen Shapira has been at the compass and protractor, mapping the NoSQL space and returns from terra incognita unscathed.

Ronald Bradford has been getting started with Cassandra, one of the outposts on Chen’s map, and shares his steps.

Arnie Rowland says, “Mark your calendar! Portland SQLSaturday/CodeCamp/Barcamp 2010 is scheduled for May 22, 2010, at the University of Portland campus.  . . .  Portland SQLSaturday is encouraging presentations related to interoperability of any of the SQL platforms, including T-SQL (SQL Server), PostgreSQL, MySQL, and PL-SQL. Abstracts for Platform specific sessions are also encouraged.”

Okay, that is all for this edition. You guys are running me ragged! Fortunately, Gary Myers picks it up next week on his Sydney Oracle Lab. Till then!

PlanetMySQL Voting: Vote UP / Vote DOWN

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

MySQL

Blame it on MyISAM, says Mark Callaghan of High Availability MySQL, on considering sql_mode and type coercion. “I think that MyISAM has its place,” writes Mark. “It does fast table scans, but InnoDB is much faster on just about everything else. I am just not thrilled with the impact it has had on MySQL.”

Not that those other engines are without flaw. Peter Zaitsev reports on an InnoDB performance gotcha with larger queries.

Here on the Pythian Blog, Singer Wang unearthed a MySQL 5.1 and InnoDB hot backup gotcha.

Eric Bergen offers his InnoDB deadlock count patch, which he introduces thus: “[Deadlocks] usually aren’t a problem until they start happening too frequently.  . . .  [SHOW ENGINE INNODB STATUS] can be useful for debugging but it’s almost impossible to get the rate at which deadlocks are occurring. [This patch] adds a counter to show table status that tracks the number of deadlocks.”

Baron Schwartz, had a script snippet to relative-ize numbers embedded in text to share.

From Planet Geek! came a fix for a OSX Snow Leopard MySQL startup problem.

SQL Server

While we’re on the subject of flaws, let’s begin our look at SQL Server blogs with Musings on Database Security and its post on passwords leakage from MS SQL Server. “Turns out that SQL Server saves in memory in clear text user credentials (passwords) of users logging in using SQL Server native authentication.  . . .  We . . . were convinced that SQL Server administrators out there should be aware of the danger and also should have a way to mitigate it so we’ve decided to publicize it and release a free tool to remove the clear text passwords from memory.”

Anyone who reads code of any kind can tell you that unreadable code is a sometimes fatal flaw. With that is mind, Buck Woody of Carpe Datum collected some T-SQL prettifiers.

Jamie Thomson examined extracting insert, update, delete rowcounts from T-SQL MERGE. Jamie writes, “Just lately I’ve been using T-SQL’s MERGE statement . . . and one thing that I needed to do was extract rowcounts for each DML operation . . . conducted by a MERGE. I was surprised to find that while @@ROWCOUNT is supported for MERGE, it only returns the total number of affected rows and there are no built in functions for getting the counts for each DML operation . . . ” Jamie’s workaround code follows.

Pythian’s André Araujo shared his procedure making for easier SQL Server database restores.

Kalen Delaney wondered, What’s a SQL Server Education Worth?. It’s not just a rhetorical question, either. It’s addressed to you. Yes, YOU!

Maybe conferences such as the PASS Summit are worth it? But the cost! Here’s Jeremiah Peschka with his tips on getting to PASS on the cheap.

Oracle

11gR2 was released. Let’s see what Oracle bloggers had to say about it. Here’s Doug Burns’s take on the 11.2 release—two highlights: Parallel Query, and changes to ASH.

The Oracle Security Blog itemizes new security features Oracle 11g Release 2.

On the AMIS Technology Blog, Marco Gralike writes, “I noticed yesterday a new feature that could have the potential to be a small, by me unnoticed, gem called ‘Flash Cache’.

It was quiz night again at the Oracle Scratchpad. In this second one, Jonathan Lewis and his contestants submit their explanations for bundle of statements in the library cache using bind variables.

Kevin Closson related his experience using Linux /proc To identify ORACLE_HOME and instance trace directories. In the post, Kevin writes, “ . . . you’ll see how to find the ORACLE_HOME and trace directories for an instance by looking at /proc//fd and /proc//exe of the LGWR process.”

DB2

Rav Ahuja says, moving to DB2 is easy, sharing a video that he writes, “ . . . highlights some of the new features in DB2 9.7 that make it very easy to enable Oracle applications to DB2.”

Easy and fun, apparently. Willie Favero, for instance, knows how to have fun and learn about DB2 LUW. It involves tuning in to the new “DB2night Show” on September 24^th.

Over and out, for now. Please add your favourite DB blogs from this week in the comments, and we’ll meet again next week. Till then!

PlanetMySQL Voting: Vote UP / Vote DOWN

This is the 158^th edition of Log Buffer, the weekly review of database blogs.

SQL Server

Simon Sabin has a TSQL Challenge - counting non zero columns. He says, “I’m working on a project where I need to cycle a flag amongst a set of columns. To achieve this I am storing a position value in each column which allows me to cycle them . . .  So the challenge is to find out the how many non zero columns there are, the twist is to use as little code as possible.”

On a cue from Simon, Aaron Bertrand shares a quick experiment in Unicode Compression on SQL Server 2008 R2. “ . . . what is going to happen.” Aaron writes, “is that NCHAR / NVARCHAR . . . columns, in objects that are row- or page-compressed, can benefit from additional compression, where realistically you can cut your storage requirements in half, depending on the language / character sets in use.  . . .  The difference is astounding: a space savings of roughly 60%, FOR FREE.”

Kimberly L. Tripp is here to tell us, Column order doesn’t matter… generally, but - IT DEPENDS! “SQL Server doesn’t care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last.  . . .  It’s all in the cost of the variable array’s offset values.”

Joe Webb of WebbTech Solutions exposes some some not-so-obvious side-effects UNION in a SQL query.

Aaron Alton, The HOBT, informs us that the Transact-SQL OVER clause is not just for ranking functions. “Prior to the OVER clause, we would have needed to create a derived table which GROUPed the query by our partition columns, then joined said table back to our parent query. This method is much cleaner, and much more efficient . . . ”

Jason “Hutch” Massie blows his cover by revealing his secrets of SQL Server consultant. (Well, not really, but the badge is kind of a giveaway.) Jason writes, “Well, I hope you are not looking for them from me. All of my secrets are common knowledge now. I was hoping you would share yours with us. Just leave them in the comments. I promise not to tell anyone. They can be our little secret, dawg.”

MySQL

On the MySQL Performance Blog, Morgan Tocker explains why you don’t want to shard. (It has nothing to do with The Dark Crystal, I already checked.) A good post—lots of debate and discussion.

You don’t want to shard, but you might want a slice of SQL pie—Shlomi Noach’s SQL-generated pie chart, that is.

Jayant Kumar provides a nice backgrounder on document oriented data stores, and compares some examples—TokyoTyrant, MongoDb, and CouchDb—to MySQL 5.4.

Let’s go back to Morgan Tocker for a moment, and his post helping everyone in understanding the MySQL forks, complete with a handy family tree. Thank you, Morgan.

Mark Callaghan of High Availability MySQL has found a reason to use 5.1: “I can reduce the size of the patch I need to maintain extreme performance with MySQL.” Mark also has some remarks on four new features from InnoDB, Percona, and Google.

Oracle

Jonathan Lewis writes, “Here’s a thought for the weekend . . . ” When people talk about ‘index fragmentation’, what do they mean, and why do they care?  . . . ”would you let me know what you mean, and how you measure [it]. (I can think of three or four interpretations for the term – but I’m interested to hear from people who actually use it.)”

On So Many Oracle Manuals, So Little Time, Iggy Fernandez shares Great Expectations: An Interview with Tanel Poder, covering, among other subjects, Tanel’s background, Oracle ACE and certification, and OSs.

Let’s stay with Iggy for The Tenth Solution—that is, his own solution to the NoCOUG Challenge.

Here’s Alex Fatkulin demonstrating how to install Oracle Grid Control agents on a Windows failover cluster with no downtime.

On Striving for Optimal Performance, Christian Antognini discusses 11g’s improvements to system managed extent size.

DB2

From his DB2 News and Personal Views, Conor O’Mahony is calling all DB2 users in India. The message? “The third annual IDUG India Conference will take place at the Chancery Pavillion Hotel in Bangalore on 24-26 September.” Click through for more detail.

Other

To close, here is The Rambling DBA, Jonathan Kehayias, with his book review of SQL in a Nutshell. In a nutshell: “I’d recommend this book hands down to anyone who works in one or more of the RDBMS’s covered in the book; SQL Server, Oracle, MySQL, and PostgreSQL.”

That’s all for now. Till next time!

PlanetMySQL Voting: Vote UP / Vote DOWN

Welcome to the 157^th edition of Log Buffer, the weekly, cross-platform review of database blogs.

SQL Server

We start with Michelle Ufford, the SQL Fool, who gives us the poor (wo)man’s graph, a fast and ingenious way to create handsome text-based graphs.

What is the importance of running regular consistency checks? Paul S. Randal returns with some survey results and analysis. He writes, “The results are actually surprising - I didn’t expect so many people to be running consistency checks so frequently . . . ”

On SQLblog.com, Aaron Bertrand offers his nuanced approach to processing a list of integers. Aaron points to some other approaches; and his readers kick it around.

Jonathan Kehayias appears this week with with a rant: Got Performance Problems? Buy bigger hardware!. “ . . . The title of this thread is very tongue in cheek . . .  However, it is the most common answer I seem to get from application vendors these days when dealing with performance issues, despite the fact that I can point out a dozen reasons why their application design/code is the problem.  . . .  How does this relate to SQL Server?  . . . [When] I get to look at badly performing code, I often find the same problems . . .  Correlated subqueries, user defined functions that perform data access, table valued parameters holding thousands of rows of data . . . seem to be the most common killers to TSQL performance . . . ”

Here’s a question from SQL in the Wild—is a scan a bad thing? “Let’s imagine a hypothetical DBA who’s doing some performance tuning. He looks at a query plan for a moderately complex query and panics because there’s a couple of index scans and he wants to rather see index seeks.  . . .  Is that correct, are index scans bad and index seeks good?” As the author and readers point out, things are rarely that simple.

Tibor Karaszi warns, watch out for that autogrow bug. “Under some circumstances, autogrow for database files can be set to some 12000 percent.  . . .  So, if you have a reasonably sized database and autogrow kicks in, you can do the maths and realize that pretty soon you are out of disk space.”

From Charles’s Host Integration Server Ramblings came a thorough, illustrated HOWTO on replicating NTEXT and IMAGE data to DB2.

DB2

Dave Beulke has some thoughts on performance features in DB2 V9 for z/OS. He writes, “These many improvements result in less CPU and also allow you to do on-line reorgs during non-peak processing helping to eliminate those late night or off hours reorgs that drive our schedules crazy as a DBA.”

If you’re going ahead with that, you will appreciate DB2 9.7 Migration: Achieving Maximum Benefit (Particularly Compression) on Diary of a Database Geek.

Elsewhere, DB2 bloggers seemed, coincidentally, to have Oracle on their minds. Says Leon Katsnelson, those who use Oracle like DB2 Express-C v9.7 a lot.

Never Ending Baustelle says: DB2 Express-C 9.7: very, very cool.

Conor O’Mahony’s DB2 News and Personal Views shares Truth in Advertising – Advanced Data Compression in Oracle 11g. “The point of this blog post is not to question whether Oracle can achieve high compression rates or whether performance is acceptable when compression is turned on. The point of this blog post is to make sure you are aware that compression rates and performance are highly dependent on the nature of your data and the nature of your environment, and don’t let vendors like Oracle tell you otherwise.”

Oracle

The results of the First International NoCOUG SQL Challenge were announced this week by its creator, Iggy Fernandez. Log Buffer congratulates the winner, Alberto Dell’Era. (Congrats also are due Pythian’s André Araújo for his runner-up entry.)

Chen Shapiro shares her thoughts on the NoCOUG results: “[The] winning solution is just plain SQL. It is algorithmically brilliant . . . but still just plain (very long) SQL. And I was wondering – this is a SQL challenge and not a math challenge. Shouldn’t the winning solution demonstrate more SQL brilliance and less math brilliance?”

On his Oracle Scratchpad, Jonathan Lewis has the third in his series, Philosophy. This editions precept: “The performance of a query should be related to the size of the data set you’re interested in, not to the size of the database.”

Kerry Osborne wants to know, Why isn’t Oracle using my outline/profile/baseline? “I seem to have spent a lot of time during the last week having conversations about why Oracle is not locking plans, even when an Outline or SQL Profile has been created. I mean, their whole purpose in life is to keep the optimizer from changing plans, right?”

MySQL

Domas Mituzas was hunting down that ol’ evil replication management. He writes, “When one wants to script automated replication chain building, certain things are quite annoying, like immutable replication configuration variables. For example, at certain moments log_slave_updates is more than needed . . .  [There] are few options, roll in-house fork (heheeeee!), restart your server, and keep warming up your tens of gigabytes of cache arenas, or wait for MySQL to ship a feature change in next major release. Then there are evil tactics . . . ”

On High Availability MySQL, Mark Callaghan pursued fast count(*) for InnoDB. He writes, “Why must SELECT COUNT(*) FROM FOO run fast?  . . .  When there isn’t a where clause, MyISAM executes SELECT COUNT(*) FROM FOO fast. When there is a WHERE clause, MySQL has limited support for combining index scans but nothing like bitmap indexes.  . . .  If you must, the following will make SELECT COUNT(*) FROM FOO fast for InnoDB . . . ”

Shlomi Noach stepped up with reasons to use InnoDB plug-in. “The plugin is a drop-in replacement for “normal” InnoDB tables; enabling many new features. It is the outcome of a long termed silence from InnoBase (Oracle), which were thought to be neglecting the InnoDB engine.”

Lenz Grimmer has news on OpenSQL Camp 2009: Session schedule published - pre-register now!

Dups has published a poll on social networks for MySQL community: which do you use to keep up with the community?

Tony Bain argues that the NoSQL community needs to engage the DBAs. “I know the majority of people discussing NoSQL platforms today are web developers. In fact there is almost a sense of trying to trying to keep this under the radar of DBAs. If we don’t talk to the DBAs about this stuff then they won’t bother us with all that jabber about consistency, data integrity, robustness and recovery.  . . .  Actually, many of the NoSQL projects are touting one of the key benefits of a NoSQL platform is you can do big data without the need of a costly DBA.  . . .  Baloney.” Good point!

Finally, Josh Berkus has a video and some thoughts on wrecking your database. Yes, so you usually leave that task to your clients, but isn’t it always edifying to see things from the other side.

That’s all for now. Please add your favourite DB blogs from this week in the comments. Till next time!

PlanetMySQL Voting: Vote UP / Vote DOWN