Pythian Group

Hi all, and welcome back to blogrotate. It’s been a busy week here at Pythian which reduced the amount of time I had for cruising the news, so this weeks edition will be a short one. Here’s a few of the stories that tweaked our interest this week.

Operating Systems

The Machine SID Duplication Myth is an article on the Microsoft Technet blog by Mark Russinovich. It goes through an in depth explanation of what SID’s are used for, and notes that Sysinternals has officially retired the NewSID utility as of Nov 03, 2009. This is of particular interest to anyone who created desktops and laptops via saved images as NewSID was a staple utility after the machine was imaged to ensure it did not conflict with other machines on the network.

Michael Larabel published some CentOS 5.4 vs. OpenSuSE 11.2 vs. Ubuntu 9.10 Benchmarks on the Phoronix site. I was suprised to see that CentOS beat the others on the majority of tests run, at least in part due to issues with the ext4 filesystem that both SuSE and Ubuntu use as their defaults.

Over at the Computerworld blog, Steven J. Vaughan-Nichols writes about 5 Reasons why Ubuntu 9.10 is better than Windows 7. I agree with most of what he says, but generally Linux is still not an easy conversion for a die hard Windows user. It sure did spark a huge amount of debate in the commentary.

Mandriva Linux 2010 is out. Check out the release information and feature set at the Mandriva blog site. I’ll have a closer look at this if I ever get the time.

Hardware

In the world of hard drives the trend has always been to bump up the amount of bits the drive can hold to combat the constant increase in data size. Another way of dealing with this could be deduplication of data which should reduce the amount of storage required for the same information. Could a hard drive dedupe data has more on this subject.

Security

Ryan Paul writes HTTPS, SSL attack vector discovered; fix is on the way. This vulnerability was discovered by Marsh Ray and Steve Dispensa from security company PhoneFactor but not publicized pending a fix. There is a temporary workaround from the OpenSSL team, hopefully it’ll be resolved quickly.

Not long after Windows 7 was released, John Leydon at The Register writes that Naked Win 7 still vulnerable to most viruses. He’s reporting on testing done by the Sophos security firm which showed that 7 out of 10 of the malware tested still managed to run in the default configuration. So even if you upgraded to Windows 7 you still need to run that anti virus.

Even Linux is not safe from security threats (nothing ever is IMHO). Bug in latest Linux gives untrusted users root access by Dan Goodin gives you the details. Patches for RedHat linux are already out, keep your system up to date to make sure you get the patch as soon as it’s available.

Virtualization

Red Hat takes on VMware with server virtualization solution by Ryan Paul discusses RedHat’s newest foray into the virtualization market with their solution called Enterprise Virtualization for Servers. This solution uses RedHat’s recently acquired KVM and is prominent in the recent RedHat Enterprise Linux 5.4 release.

So as not to be left out, Cisco, EMC, and VMware join hands and plunge into cloud with their new joint venture called Acadia. You can also read more about this in Cisco, EMC, VMware & Intel Form Acadia.

In the “I totally called it” department

I mentioned in a recent version of this blog that Microsoft was backing a Family Guy episode. I said at the time that I did not see how they could funny it up, apparently Microsoft could not see it either. Joe Fay gives us the skinny in Microsoft drops Family Guy like a hot deaf guy joke. Apparently the humour was not in keeping with the clean, family friendly image that Microsoft wants to convey. Seriously? I suggest someone at Microsoft watch any of the Seth McFarlane shows before signing on with him. I am guessing it was not a matter of foul language however, expletives are an occupational hazard when using Windows.

Til next time, keep your cache full and your swap empty.
Brad

Last Friday in the Sydney Oracle Meetup I talked about Oracle Flashback technology and how it helps to reduce downtime. The session generated great interest among the attendees, which led to interesting discussions and many questions about the subject.

Some of the question couldn’t be answered during the meeting so I’ve followed up on them and I’m posting the answers here since they may be of interest for many others.

Q: Is there a separate background process for writing flashback logs?

A: Yes. RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.

Q: Do I need to shutdown and mount the database to turn flashback on?

A: NO! This is a very common misconception. ALTER DATABASE FLASHBACK ON is an online operation.

To turn flashback on, all Oracle needs to do is to start saving the before-images of the database blocks before they are changed. This guarantees that the database can be flashbacked to the exact point-in-time when the flashback mode was turned on.

Q: What happens if RVWR cannot write to disk?

A: It depends on the context where the write error occurs:

• If there’s a Guaranteed Restore Point, the database crashes to ensure the restore point guarantee is not voided.
• If there isn’t a Guaranteed Restore Point and it’s a primary database, the Flashback Mode will be automatically turned off for the database, which will continued to operate normally.
• If there isn’t a Guaranteed Restore Point and it’s a standby database, the database will hang until the cause of the write failure is fixed.

Q: Is it possible to specify the size of the Flashback Buffer in the SGA?

A: Yes, but indirectly. The size of the Flashback Buffer is set to 2 * LOG_BUFFER.
For performance reasons, it’s recommended to set LOG_BUFFER to at least 8MB for
large databases running in Flashback Mode.

Q: Can RMAN be used to backup flashback logs?

A: No. Flashback Logs are not backed up. Even if the command BACKUP RECOVERY AREA is used to backup the contents of the FRA to tape only the following file types are backed up: full and incremental backup sets, control file autobackups, datafile copies, and archived redo logs.

Flashback Logs are considered to be transient files and cannot be backed up by RMAN. They are not needed for media recovery.

Q: When are the flashback logs deleted?

A: Flashback logs are managed by Oracle only. Oracle will try to keep as much Flashback logs as needed to satisfy the DB_FLASHBACK_RETENTION_TARGET parameter. However, if there’s space pressure in the Flash Recovery Area (FRA), flashback logs may be deleted to make room for other things, like backups and archived logs, for example.

• If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.
• If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is reused.
• If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
• If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
• No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.

Other than that flashback logs are deleted according to the below:

• When flashback mode is turned off all flashback logs are deleted ONLY if there’s no guaranteed restore points. If there’s at least one guaranteed restore point, no flashback logs are deleted.
• When the oldest guaranteed restore point is deleted and flashback mode is off, all flashback logs older than the second oldest guaranteed restore point are deleted. If flashback mode is on for the database OR the guaranteed restore point is not the oldest no flashback logs are deleted.

Q: How to list restore points in RMAN?

A: You can use either the RC_RESTORE_POINT view in the recovery catalog or the command
LIST RESTORE POINT [ALL|restore_point_name] in RMAN.

Q: After flashback’ing to a point-in-time before a RESETLOGS operation is it possible to flash forward to the incarnation after the RESETLOGS?

A: Yes, it’s perfectly possible.

Q: Can you see the progress of a FLASHBACK DATABASE operation?

A: Yes, you can. During a FLASHBACK DATABASE operation you can query V$SESSION_LONGOPS from another session to see the progress of the flashback.

The FLASHBACK DATABASE operation has two distinct phases: the actual flashback and the media recovery that happens afterwards to bring the database to a consistent state.

While the actual flashback is running you’ll see the following message in V$SESSION_LONGOPS:

Flashback Database: Flashback Data Applied : 238 out of 282 Megabytes done

During the media recovery, the following messages will be seen:

Media Recovery: Redo Applied : 263 out of 0 Megabytes done
Media Recovery: Average Apply Rate : 1164 out of 0 KB/sec done
Media Recovery: Last Applied Redo : 626540 out of 0 SCN+Time done
Media Recovery: Elapsed Time : 232 out of 0 Seconds done
Media Recovery: Active Time : 116 out of 0 Seconds done
Media Recovery: Active Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Maximum Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Log Files : 15 out of 0 Files done
Media Recovery: Apply Time per Log : 7 out of 0 Seconds done

Q: How should I set the database to improve Flashback performance?

A: Oracle’s recommendations are:

• Use a fast file system for your flash recovery area, preferably without operating system file caching. It is recommended to use a file system that avoids operating system file caching, such as ASM.
• Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.
• If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
• For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.

All over the web I’d read that DTS packages could be stored on SQL Server 2005 64-bit, but not executed on this server. Workarounds I’ve seen range from creating SSIS packages with Execute DTS tasks, migrations to SSIS using the wizard or third party tools, and running the DTS Packages from a 32-bit server against the 64-bit target.

Recently (and much to my embarrassment after making that statement), a colleague demonstrated that this is not correct. DTSRun.exe can be found on SQL Server 2005 64-bit installations (although it might not be supported by Microsoft).

Upon investigating and testing on my own, I found that on the servers I checked, DTSrun.exe is located in: C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\.

I mocked up a simple DTS package that picks up a flat file, creates a table, and then imports the data into it with a transformation step. I saved it as a structured storage file, which I copied to the 64-bit server, generated a DTS Run line (F.Y.I. – DTSRunUI.exe is not located on these servers), then executed it from a command prompt on the 64-bit server. And it worked!

SSIS is a good platform with many noticeable improvements over DTS (also some new quirks), but if you need a workaround or just can’t justify a complete re-write of your DTS packages to your manager, this may be an option for you.

I tested some simple commands, (not the entire functionality) so I strongly recommend you test your packages before relying on this in a production environment.

Please note that DTSRun.exe is not included in SQL Server 2008 installations.

This is the 168th edition of Log Buffer, the weekly review of database blogs. Let’s give the wheel a spin and see who comes first . . . 

MySQL

Brian “Krow” Aker has something to say about Drizzle, InfiniDB, and column-oriented storage: “I have been asked a number of times ‘do you think there is a need for a column oriented database in the open source world?’ The answer has been yes!  . . .  I was very happy to see Calpont do their release of Infinidb last week.”

Vadim of the MySQL Performance Blog said, “As Calpont announced availability of InfiniDB I surely couldn’t miss a chance to compare it with previously tested databases in the same environment.” And he didn’t, as shows his post Air traffic queries in InfiniDB: early alpha. Bob Dempsey and Jim Tommaney of InfiniDB are in on the discussion.

Back to Drizzle for a moment, and Jay Pipes’ item, The Great Escape. “This week, I am working on putting together test cases which validate the Drizzle transaction log’s handling of BLOB columns.  . . .  I ran into an interesting set of problems and am wondering how to go about handling them. Perhaps the LazyWeb will have some solutions.  . . .  The problem, in short, is inconsistency in the way that the NUL character is escaped (or not escaped) in both the MySQL/Drizzle protocol and the MySQL/Drizzle client tools.”

Baron Schwartz has been catching erroneous queries, without MySQL proxy, having been inspired by Chris Calender’s post, Capturing Erroneous Queries with MySQL Proxy.

Nick Goodman promises instant relief from slow MySQL reporting queries using dynamoDB. And no gooey applicator!

Robert Hodges of the Scale-Out Blog looks at replicating from MySQL to Drizzle and beyond. “I am . . . delighted that Marcus Erikkson has published a patch to Tungsten that allows replication from MySQL to Drizzle. He’s also working on implementing Drizzle-to-Drizzle support, which will be very exciting.  . . .  This brings up a question–what about replicating from MySQL to PostgreSQL? What about other databases?”

And back to xaprb, where Baron confesses, I’m a Postgres user, as it turns out.

PostgreSQL

Bruce Momjian has published a new security talk, Securing PostgreSQL From External Attack.

Andrew Dunstan delves into recursion in Recursion, n. See recursion. “Never,” he says, “underestimate the usefulness of silly demos (this is written for a talk next week) to teach things worth knowing.”

Bernd Helmle shares a walk-through of cloning Slony nodes. “The new stable branch 2.0 of Slony-I is out for a while now. Time to blog about one of my favorite new features there, cloning an existing node without doing an initial SUBSCRIBE command.”

SQL Server

It was PASS Summit this week. On Home of the Scary DBA, Grant Fritchey covers the event with several good posts, including PASS Summit 2009 Key Note 3. (Grant is also Geek of the Week! Congratulations, Grant! I guess. Quote: “I think most DBA’s have adminhood thrust upon them. I think the ‘accidental’ DBA is the most prevalent path into becoming a DBA. I became a full time Admin by opening my mouth once too often.”)

Aaron Bertrand also has his summary Blogging from the PASS Keynote: 2009-11-03. (Grant and Aaron both have to specify which keynote they mean, because there’s more than one keynote. This, I guess, is “keynote redundancy”, but I still think PASS needs to normalize.)

Greg Low announces the launch at the PASS Summit of a new book, SQL Server MVP Deep Dives. “This is no ordinary book,” he writes. “Paul Nielsen took up Steve Ballmer’s challenge at a recent MVP summit to do something notable to give back to the community. He organised a large group of SQL Server MVPs to create a unique book and worked with Manning to get it published. The money made on the book was to go directly to a charity and the charity chosen was WarChild.”

Ben Nevarez asks, Are You Using Scalable Shared Databases? “Did you know that you can share read-only databases between several instances of SQL Server?  . . .  Scalable Shared Databases is a very interesting SQL Server feature that many of us seem to almost have forgotten about . . . ”

Here’s Roman Rehak reporting an issue with restoring 2000 backups on 2008. He writes, “Recently we’ve been experiencing a lot of headaches with SQL Server 2008 crashing while restoring a backup taken on a SQL Server 2000 production server. The crash resulted in a stack dump but SQL Server would continue running, although less stable, and sooner or later needed a reboot.”

Meanwhile, Adam Machanic reports on SQL Server 2008: lock escalation, INSERTs, and a potential bug. Adam says, “Lock escalation is a funny thing. I’ve found myself on numerous occasions waging war against its concurrency-sapping existence, and rarely have I found myself wishing that it would work more aggressively. But there is a time and place for everything, and yesterday I discovered that a major change has occurred with regard to lock escalation in SQL Server 2008.”

Oracle

Mohammed Mawla on the Pythian Blog bridges the gap with his item on running the same query against multiple SQL Server AND Oracle instances.

Surachart Opun shares his HOWTO on using DUPLICATE without a connection to target database: “ . . . that’s a 11gR2 Feature. DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.”

Here’s another HOWTO, this one from the great grandson of Husnu Sensoy: How to Install Oracle 11g Release 2 on OEL 5.4 on VirtualBox: Installing Grid Infrastructure. He begins, “In Oracle 11g Release 2 you will find that things have changed even for single instance database installation. I will try to illustrate in this series of posts how to install a single instance Oracle 11g Release 2 database to your Linux machines.”

But let’s step back a bit. Ronny Egners says, Oracle on linux – yes of course – but what linux?. “There is a discussion from December 2008 what Linux (SLES vs. Red vHat vs. Oracle Enterprise Linux) to use for running oracle on Linux by Yann Neuhaus.  . . .  After nearly one year i wanted to catch up the article and check if the pros and cons are still valid or if there changed anything.”

Chen Shapira offers The Senile DBA Guide to Troubleshooting Sudden Growth in Redo Generation, which begins, “I just troubleshooted a server where the amounts of redo generated suddenly exploded to the point of running out of disk space.  . . .  The problem was found and the storage manager pacified, I decided to save the queries I used.  . . .  It was very embarrassing to discover that I actually have 4 similar but not identical scripts . . .  Now I have 5.”

Embarrassing, Chen! But do you have guilty feelings like Martin Widlake does? He makes a guilty confession. The sin? “I use the Buffer Cache Hit Ratio.”

Last, Tyler Muth introduces Logger, A PL/SQL Logging and Debugging Utility.

That is all for now. Please let’s hear your favourite database blogs in the comments. Until next time!

Installing a 32-bit version of SQL Server 2005 Reporting Services on a Windows Server 2003 64-bit could be a bit of a challenge, as it requires IIS. I have seen several customers who purchased servers with Windows Server 2003 64-bit pre-loaded while they only have a license for a 32-bit SQL Server 2005.

It is always recommended to have a 64-bit application running on a 64-bit OS to take full advantage of the 64-bit platform. IIS, by default, runs 64-bit on a 64-bit Windows Server system. SQL Server Reporting Services requires ASP.NET which can be manually installed on top of IIS.

When you install the .NET Framework on a 64-bit machine, you have both the 32- and 64-bit versions. Nonetheless, you will not be able to install ASP.NET 32-bit version on a 64-bit IIS, which will be needed by SQL Server Reporting Services.

To be able to install ASP.NET 32-bit on a 64-bit IIS, you need to configure IIS to run 32-bit web applications. With Windows Server 2003 Service Pack 1, IIS can be enabled to run 32-bit applications on a 64-bit Windows using the Windows32-On-Windows64(WoW64) compatibility layer. This makes it possible to run ASP.NET 32-bit and other 32-bit web applications as well as allow creation of 32-bit worker processes.

To enable IIS 6.0 to run 32-bit web applications on a 64-bit Windows, navigate to the %windir%\Inetpub\AdminScripts directory. Run the adsutil.vbs script with the following parameters:

csript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 "true"

This will enable IIS to run 32-bit web applications. You can then install SQL Server 2005 Reporting Services 32-bit which will install the corresponding ASP.NET version as part of the .NET Framework.

If, however, you already have the .NET Framework installed on the server, you will need to manually install ASP.NET 2.0 32-bit on IIS after it has been configured. Doing so before configuring IIS will throw an exception stating that you cannot run 32-bit ASP.NET on 64-bit IIS. Navigate to the %WINDIR%\Microsoft.NET\Framework\v2.0.50727 folder (the 64-bit version of the .NET Framework will be at the %WINDIR%\Microsoft.NET\Framework64\ folder). Run the aspnet_regiis.exe utility on the command line.

aspnet_regiis -i

Now that all the groundwork has been prepared, you are ready to run SQL Server 2005 Reporting Services 32-bit on a Windows Server 2003 64-bit.

If, however, you decide to install it on a Windows Server 2008 64-bit, there’s more groundwork to do. By default, IIS 7 as well as ASP.NET are not installed. You have to add the web server role and further configure IIS. SQL Server 2005 Reporting Services is dependent on the IIS 6 or earlier metabase, and thus requires installation of the IIS 6 Metabase Compatibility and IIS 6 WMI Compatibility. Installing these two would also configure the IIS Default Application Pool to run on 32-bit similar to running the adsutil.vbs script for Windows Server 2003.

A Microsoft KB outlines the detailed steps in preparing IIS7 to run SQL Server 2005 Reporting Services on Windows Vista, but the steps work pretty well in Windows Server 2008. In case you still see the installation error on required 32-bit ASP.NET, you can switch the configuration of the Default Application Pool to Enable 32-bit applications. You don’t really need to do this once all the IIS 7 groundwork has been completed but, then again, you’ll never know. It might come in handy.

This is the first post in a series dedicated to exploring the backup and availability options in SQL Server 2005 and 2008. It is aimed at anyone unfamiliar with the database backup options in SQL Server 2005 and 2008. I’m not going to explore every single option or scenario, the goal is to give you the language and the tools to do deep dives where you need to.

SQL Server 2005 has several DBA-job-saving options available to the would-be administrator. Think of a Database Backup as the technology to save data and Database and Availability as the technology to keep it online and available to it’s consumers.

A very brief introduction to SQL Server databases

Its important to have a few SQL Server database basics in order to understand the backup options. If you know what a recovery model is, and the difference between an .ldf and .mdf file, you can skip this section. If this is as good as a foreign language to you, read on.

Files

By default, every SQL Server database has two files: one data file (with the .mdf extension), and one transaction log file (with the .ldf extension). The data file (.mdf) holds your database objects (tables, views, etc) and the data. The transaction log file (.ldf) contains the transactions in your database. Depending on the recovery model selected, it may hold uncommitted transactions, or it may hold every transaction since the last truncation.

Wait – What’s a transaction?

According to Wikipedia, a transaction is defined as a unit of work performed against the database. If you have a particularly large database, someone may split your databases into several files, or even put them on different disks. This is often done to balance the disk activity, and it can be part of your backup strategy. A DBA might put tables that are infrequently or never changed into their own file database file.

Recovery Models

There are three recovery models to choose from: Simple, Full, and Bulk-Logged.

This is set per-database and it doesn’t affect options for the other databases on this server. The recovery model chosen affects some backup, availability, and redundancy options.

Simple
Basically, when Simple recovery is in use, the data from any committed transaction will be discarded after each checkpoint is issued. These transactions cannot be backed up or restored. The transaction log for these databases will be smaller.

Full
All transactions will be stored in the transaction log until the log is truncated or backed up. Please note that, contrary to popular belief, only transaction log backups and truncating the log remove entries from the log, full backups do not. More here.

This recovery model is required for certain backup and availability options. In order to avoid extremely large transaction logs and/or filling up the disk, you must schedule regular transaction log backups to use this recovery model.
The reason this recovery model is used is that it allows you to perform point-in-time restores (discussed more later).

Bulk-Logged
Very similar to Full recovery, except some operations are minimally logged. A list of details regarding which operations are minimally logged can be found here. This recovery model also allows point-in-time restores, but not to any point of a non logged operation.

Now that you’re up to speed on files, transactions, and recovery models . . . 

What are the backup options?

Full backups — These are exact copies of your database at the time they’re taken. They “stand alone” and can be restored back to your server, or onto other SQL Servers. The output of a full backup will be one file, but will include the transaction log and all data files for the database.

Differential backups — These are backups that include all of the changes since the last full backup that was taken. The result will be one backup file and will only hold the changed data. You will require the most recent full backup in order to restore the differential backup(s).

Transaction Log Backups — These are backups of all of the transactions in the exact order of occurrence, since the last full backup, OR the last transaction log backup. Transaction log backups require the database to be in “Full” recovery mode.

Backups can be set up, created, and scheduled via SSMS or scripted and run as code. If someone has added several files to a database or has invoked some of the high-availability options, there are additional options for consideration. This brings me to a couple special circumstance options.

Copy-Only backups

Introduced in SQL Server 2005 as an option and in SQL Server 2008 as an option in the GUI, copy-only backups do not affect the chain of backups. This is a great addition to the platform as it allows backups to be taken in special circumstances, merely for the purpose of duplication.

Partial Backups

Partial backups are new to SQL Server 2005, and are intended to be used on databases where the tables have been segmented into filegroups, and some of them set to read-only. In this situation, the partial backup would back up all changed data from the non-read-only filegroup.

This is a great feature for large data-warehouse type databases but won’t be used by everyone. The ideal candidate is someone who has a large data warehouse or a similar database where there is archive type date in the database.

This feature is not accessible from SSMS, which means you will need to write SQL to use it.

Differential Partial Backups

Only to be used with partial backups, all of the same prerequisite circumstances exist for partial backups, with the additional requirement that your must be using partial backups.

SQL Server Enterprise Edition brings a new option for compression of backups. Any edition of SQL Server 2008 can restore compressed backups, but only Enterprise Edition can create them. This option will reduce the size of your database backups.

How do I decide which backups to use, and when to use them?

Next post . . . 

It’s only one week to go now and the program for the AUSOUG National Conference Series 2009 is out. I’ll be presenting on the first day in Perth (Nov 10th) about Oracle Flashback technology.

I’m looking forward to attending the conference in Perth, not only because I’ll be presenting there but also because it’s my first time in Western Australia. All going well my presentation will be honed before the weekend and I’ll be arriving in Perth still this week, on Friday, to enjoy an extended weekend in Perth and Margareth River wine region with my wife before the conference begins.

It is not unusual that a DBA needs to run the same query across multiple instances. The query can be anything from a simple line to retrieve a specific value (such as an instance version), to others that involve data modifications or schema changes.

SQL Server 2008 brought the ability to execute Statements Against Multiple Servers Simultaneously by simply creating a local server group or broadly using a Central Management Server and one or more server groups. Inside these groups there should be one or more registered servers.

There is also the commercial Red-Gate SQL Multi Script with an Unlimited edition license that allows you to run your code against any number of SQL server instances.

Before SQL Server 2008, there was no native support for running the same query against multiple instances using shipped tools, so it can be approached in one of these ways:

• Manually connecting to each instance to run the code; this is very time consuming.
• Using batch files to call OSQL or SQLCMD to loop against a defined set of servers; this involves a bit more work and control of the batch files.
• Using linked servers and loops inside a T-SQL query.
• Using DTS or SQL server Integration services.

I’ve used nearly all of them, but I have found that linked servers provide a higher degree of control on the target servers, error handling, and most important, the ability to use SQL server Encryption to store the credentials of source servers if some of the servers are using SQL authentication.

I’ve also managed to make this run against Oracle databases, but had to take the extra step of configuring configuring Oracles as linked servers as described in KB 280106.

The code has the following characteristics:

1. It uses encryption to store and retrieve credentials for Oracle and SQL server instances that use SQL authentication. There is a way to connect to Oracle using windows authentication but I didn’t test that.
2. The code uses an ASYMMETRIC KEY to encrypt the credentials.
3. It uses SQL cursors to retrieve list of servers against which I will run the code.
4. Use of TRY…CATCH for error handling.
5. Code against SQL server is run using Sp_executesql. The supplied code is liable for any restrictions imposed by Sp_executesql; I found very long queries in particular to be sometimes a problem.
6. Code run against Oracle instances are done through Openquery. I found that using direct Linked server name exposes some problems especially with metadata. The supplied code must be Oracle-compatible.

Remarks

• You need at least SQL server 2005 to use TRY..CATCH and Encryption.
• You need to format the source code to be syntax error-free, especially when it contains single quotes.
• When it comes to retrieving data only, Openrowset looks like a fast alternative to creating then dropping a linked server. OpenRowset is a fast way to access remote data on the fly using an OLE DB data source.
• I’ve not used the code much against Oracle—just simple tasks, but it worked for me.
• The code can be used against any datasource (DB2, MySQL, etc.) that can be accessed using Linked servers.

Here is the code. sql-oracle.sql.txt I welcome your feedback and additions.

Welcome to the all hallowed eve eve edition of Blogrotate. It was a relatively quiet week this week but the 2 standouts are from the OS department with more reviews of the just released Windows 7 and the release of Ubuntu 9.10. Here’s some of the stories that we took note of this week.

Operating Systems

Ubuntu 9.10 is released. Anyone who reads my blogs knows by now that I am a Kubuntu user and I think that it’s the best desktop Linux available right now. They’ve put a lot of work into this one and it’s the best version of Ubuntu yet, easy to install and use with all the features you could ask for. Ryan Paul at Ars Technica has his own review called Ubuntu 9.10 brings web sync, faster bootup, GNOME 2.28, check it out.

Here’s a short list of some types of Ubuntu you can get, and their niche.

• Ubuntu – The standard desktop featuring Gnome.
• Ubuntu Server Edition – Just how it sounds.
• Ubuntu Netbook Remix – A version of Ubuntu designed to work on your netbook.
• Kubuntu – The KDE desktop version of Ubuntu. With KDE it’s an easier conversion for Windows users in my opinion.
• Edubuntu – Edubuntu is an educational operating system that is designed for kids, parents, teachers and schools. I have not tried this one yet, but my 3.5 year old is ready for it.
• Mythbuntu – A replacement for Windows Media Center featuring MythTV. I use this for a PVR at home, easy install and great interface.
• XUbuntu – A version of Ubuntu using the xfce desktop, and designed for older or less powerful machines that have trouble with the Gnome or KDE desktops.

Windows 7 is still fresh in the minds of many. If you want an exhaustive review of all the pros and cons of Windows 7, how about trying to get through a 15 page review by Peter Bright. For the impatient, he sums it up at the end saying “…Windows 7 is, overall, a fantastic OS. It builds on a solid platform, and just makes it even better”. Read the full review in Hasta la Vista, baby: Ars reviews Windows 7.

PC Pro has an interesting article up called The Crapware Con. This article has some interesting information on what sort of extra software each of the major manufacturers are adding to your laptop, and what sort of effect this has on your performance. If you have an Acer, Sony or HP laptop they are apparently the worst offenders.

Security

Dan Goodin has an interesting article about a free Microsoft product that can identify and harden applications against common avenues of attack without even needing access to the source code itself. Read the scoop in Free Microsoft security tool locks down buggy apps.

Dan Goodin reports on a new Mozilla site that will check the plugins in your FireFox for old versions which may have security issues and allow you to update them easily. Mozilla service detects insecure Firefox plugins has the full story, and the plugin check page is here.

Software

Paul Lorimer, Group Manager for Microsoft Office Interoperability, writes in his blog that “In order to facilitate interoperability and enable customers and vendors to access the data in .pst files on a variety of platforms, we will be releasing documentation for the .pst file format”. This will open up the specifications for the pst file, used by MS Outlook to store email, making it easier for other software vendors to tap into the file format. See more in Roadmap for Outlook Personal Folders (.pst) Documentation.

Internet

The Internet celebrated its second 40th birthday on Thursday marking the date that the first word, “Lo”, was sent between 2 machines at UCLA on October 29, 1969. Get more of the story in Internet pops champagne on (second) 40th birthday. On an unrelated note, this happened 40 years after the 1929 stock market crash.

Hardware

Neil Mcallister at InfoWorld has an interesting article on the rise of the ARM processor as a competitor to the Intel’s Atom for mobile devices. Read on in ARM vs. Atom: The battle for the next digital frontier.

Computerworld has an article about the recent Intel release and recall of it’s SSD firmware update due to issues with data corruption. Intel pulls firmware for SSDs just a day after release has more details. Ars Technica also covered the story in Intel’s SSD firmware brings speed boost, mass death (again).

That’s all we have time for this week folks. Be sure to tune in again next week. Same bat time. Same bat channel.

For very personal reasons that don’t belong in this article, I decided a few weeks ago that it’s time for me to move on. The year and a half that I worked for Pythian have been a wonderful experience, and this is article is my tribute to this great company.

The MySQL Team

Being able to work side by side with two MySQL experts like Sheeri Cabral and Augusto Bott has been a great experience. I have learned a lot, not only about MySQL, but also about what a great DBA should be like. Both of them are recognized MySQL Community members and regular speakers at the MySQL Users Conference and other events. Sheeri has been named MySQL Community Member of the Year twice in a row. Both are a guarantee of excellent service.

My Predictions For Pythian

I believe that the IT services industry will undergo a change that is continuation of the the transformation that the IT industry as a whole has been going through over the last few years: commoditization.

Very few will argue at this point that hardware and software are commodities, especially when they support a company’s infrastructure. This trend has put MySQL in the position it has today in the database arena. In this scenario, managing the IT infrastructure, whether it’s running in The Cloud, a hosting company or internally, no longer adds value to the core business. As in any economic downturn, companies reconsider their overhead and look for solutions that are more flexible and cost-effective that their in-house services. This is when they start looking into outsourcing alternatives.

This time, however, is different—they have a designated fixed budget and very specific needs. The traditional consulting model doesn’t fit this scenario since it doesn’t have the required flexibility. This is where companies like Pythian come in.

Pythian’s business model and practices allows the clients to control their budgets while keeping a healthy infrastructure, and concentrating on the tasks that add real value to their business. Under the direction of Andrew Waitman, CEO, and Paul Vallée, Pythian’s founder, the company is in a an excellent position to satisfy the new market needs.

What’s Next

I will continue participating as actively as I can in the MySQL Community and continue with the development of sar-mysql. My blogs will move to a new home, but will be a continuation of the of the articles I have been writing here (without Dave Edwards cleaning up my English though); and they will also be syndicated in Planet MySQL. I will do my best to keep an eye on the articles I posted in the Pythian blog to keep answering any new comments that may be posted there. If you have the patience to deal with my non-technical tweets, feel free to follow me through Twitter: @seattlegaucho.

I will see you around!

P.S. to the MySQL DBAs in the wild: This might be a good opportunity to polish your résumé and submit it to Pythian. Who knows, you may end up working for a company on the rise, side-by-side with some of the best MySQL DBAs in the market.

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

Since all that OOW news forced Gerry to give them short shrift last week, let’s begin with blogs on . . . 

SQL Server

Jeremiah Peschka gets our week going with his refresher introduction to SQL Server system databases.

Likewise, Pinal Dave reviews the difference between candidate keys and primary key.

Mladen Prajdic says, “Ladies and gentlemen, boys and girls, the STP is back to rock your world!” Why? Because SSMS Tools Pack 1.7 is out with a new feature: SQL Snippets.

Michael Swart, The Database Whisperer followed up, and reports SQL Snippets is my new favourite thing.

Martin Bell was also gettin’ good and GUI. He shares his tips on how to display long text in SSMS.

Home of the Scary DBA also exposes some snags with Profiler GUI.

In general, when you hit snags, the question that arises, says The Rambling DBA, Jonathan Kehayias, is Have you got air in your spare tire? (Have you checked your DR/HA plans?).

MySQL

Let’s start our look at MySQL blogs with a trivia challenge, courtesy Arjen Lentz: identify this query profile. “You do SHOW PROCESSLIST, and you see one of your web apps issue the following query: SELECT … WHERE … AND 1=2 UNION SELECT … What does this tell you, and what do you do next?”

Also, identify this replication failure.

Baron Schwartz also has a question for you, what do the InnoDB insert buffer statistics mean?:

Ever seen this in SHOW INNODB STATUS and wondered what it means?
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 4634, seg size 4636,

Baron has a review of The Art of Capacity Planning by John Allspaw, too.

Dave Stokes has a related offer: Want to know how Yahoo does their capacity planning? “Well, if you are in the Dallas / Fort Worth area on Monday, November 2nd you can find out exactly how they do it!  . . .  Strategic MySQL Planning for Complexity & Growth (i.e. MySQL Scaling for Dummies) will be presented by Tommy Falgout at the North Texas MySQL Users Group Meeting.”

Roland Bouman reports, Calpont opens up: InfiniDB Open Source Analytical Database (based on MySQL), beginning, “Open source business intelligence and data warehousing are on the rise!  . . . Calpont has just released InfiniDB, a GPLv2 open source version of its analytical database offering, which is based on the MySQL server.”

On the MySQL Performance Blog, Vadim examines Galera – synchronous replication for InnoDB.

On the Pythian blog, Sheeri Cabral shared a brief survey of large data sets and their hardware stats in MySQL and hardware information.

Oracle

On Irrelevant thoughts of an oracle DBA, Freek D’Hooge gives us the unwelcome reminder that it’s (nearly) wintertime (again), in the northern hemisphere, at least. And with the season comes Standard Time. Freek writes, “In this post I would like to investigate how the session timezone settings affect the sysdate, current_date, systimestamp and current_timestamp variables during the switchover to or from daylight saving time.  . . .  A long running session is sufficient to pollute your data, certainly if you are using current_date as it has no timezone information.”

Richard Foote and his readers discuss another time-based ritual of old, rebuilding indexes every sunday afternoon. “I just had to share this amusing article on ‘Scheduling Oracle Index Rebuilding’.  . . .  if you’re like me and now work on sites where there is no such Sunday maintainance window because your users actually require and demand 24 x 7 access to their applications, because organisations still want to sell their products and services online during Sunday afternoons  . . .  etc. etc. etc. . . .  then perhaps the article may not be that useful to you afterall.”

The Oracle Instructor, Uwe Hesse, looks into “Total Recall”: Brief introduction into Flashback Data Archive. Writes Uwe, “With Oracle Database 11g, we have a new Option available, called Total Recall. This option extends the possibility to do Flashback Query, introduced in 9i already.  . . .  We can now designate particular tables for being able to track back all the changes on them even after years.”

Miladin Modrakovic, meanwhile, lays out the basics of ORION (Oracle I/O Calibration Tool) included in 11g R2.

Martin Widlake illustrates that partitions are not for performance: “There is a myth that Partitions in Oracle magically aid performance, even a general assumption that the main role of partitioning is to aid performance.  . . .  The myth is so strong that when asked at interview, most DBAs {and even some performance specialists} will site performance as the main (and occasionally only) benefit of partitioning.”

Chet Justice could sure use an interview. He is billing himself as a free Oracle developer/dba: “There is a serious lack of work in the Tampa market and desperate times call for desperate measures.  . . .  Now, I’ve always wanted to do this, but was never in a position to do so financially…I’m still not, but something is way better than nothing.  . . .  I’m going to offer my services for free.”

PostgreSQL

Josh Berkus tells Postgres admins, Alpha2 is out, and we need YOU to test it. “Version 8.5, alpha 2 is now out and available for your download. This means that you get a nice present: a preview of 8.5’s features for your entertainment, interest, and application design. Try the new features now! Of course, this places an obligation on you as well; to test the alpha, and let us know about the bugs.”

Pavel Stehule is on the case. Here he introduces named function parameters – a feature of PostgreSQL 8.5.

On select * from depesz;, Hubert Lubacziewski explains calculating backlog of events. The problem: “We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 minutes we can handle at most 100 tasks. Given the history of number of tasks added every 5 minutes, calculate backlog at any given moment.”

Ropert Kalmar brings the news of EnterpriseDB + Red Hat “This press release from Reuters,” he writes, “about Red Hat investing in EnterpriseDB is great news. Since Red Hat once manage to bring Linux to the room of Enterprise IT I’m hoping this would increase the acceptance of PostgreSQL adoption in the Enterprise world.”

That’s it for now. Thank you for tuning in, and please leave a link to your favourite DB blog from this week in the comments. See you in a week!

Good morning folks and welcome to chapter 3 in the ongoing saga of TOra and Oracle support for Ubuntu. In this edition we’re faced with a new Ubuntu, new TOra 2.0, and new and exciting adventures, all of which I have stripped out so you can get this up and running quickly.

All in all, the build process turned out to be simpler than it had on previous versions all thanks to a much smarter build system. The scripts that debian-ize the packages are much more robust and also much more complex at first glance. There was no configure line to change in this one, it’s smart enough to pick up all the elements you need provided they are where the build expects them to be. One such item is the Oracle include path, which I will say more about shortly. Enough snappy patter, let’s get on with . . . 

Installing TOra with Oracle support on Ubuntu 9.10 (Karmic Koala)

Conventions and Caveats

• I use sudo for everything because logging in to root shells is just bad practice.
• I plug vi whenever possible, because vi is the greatest (high five).
• Commands issued are in pre-formatted text without any prompt gunk in front of them, so cut and paste to your heart’s content.
• Output is also in pre-formatted text and I use it sparingly where relevant. Apt is chatty.
• Some instructions are pasted from the previous versions so you will not need to refer to my last howto to get this working. I shamelessly did not rewrite some bits if there was no change.
• When I say “dep”, I actually mean dependancy.

Environment

This is based on a 64-bit install of Ubuntu 9.10 using the Oracle 11 clients. The system itself is an AMD Athlon 64 7750 Black Box Editions at 2.7GHz with 4GB DDR2. I also touch on the 32-bit install using the 11.2 client because it was mentioned in comments for the previous edition. For the record, the only difference is in the environment variables.

The desktop I am running this time is the KDE4.3.2 that is shipped with Kubuntu 9.10 (one day away as I write this, but I am running the release candidate patched to current which should be close to the final release). I like it. Nuff said. There was no issue with KDE development libraries this time around since I did not hack and slash the desktop together. The scripts also detect KDE for you, so no changes to the configure script necessary.

Get the packages

Find an acceptable build location in your filesystem, cd to there and then get the tora source deb package. This bit should not be done with sudo or else you’ll run into build problems later because all the files are owned by root.


mkdir -p /path/to/deb/source/
cd /path/to/deb/source/
apt-get source tora


That should drop and unpack the TOra source in your current directory. Now get the Oracle packages. Get them from the Oracle site..

Again, the login you need is free to register. The files we want this time around are:

• oracle-instantclient11.1-basiclite-11.1.0.7.0-1.x86_64.rpm
• oracle-instantclient11.1-devel-11.1.0.7.0-1.x86_64.rpm
• oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.x86_64.rpm

For those using 32-bit, the lastest packages are these:

• oracle-instantclient11.2-basiclite-11.2.0.1.0-1.i386.rpm
• oracle-instantclient11.2-devel-11.2.0.1.0-1.i386.rpm
• oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.i386.rpm

Install the prerequisites and development libraries

Next we’ll want to install the build dependencies via apt. To do this, run the following simple command.


sudo apt-get build-dep tora


From there, we’ll get all the other things that we need to prevent the build from failing. The list is a bit slimmer than last time, and weighed in around 68MB if I recall correctly.


sudo apt-get install libqt3-mt-dev libqt3-compat-headers libqscintilla-dev build-essential g++ gcc autoconf automake flex zlib1g-dev docbook-xsl debhelper alien libaio1 dpatch fakeroot xsltproc texi2html texinfo libqt3-mt-psql libqt3-mt-odbc config-package-dev cmake qt4-dev-tools


Next install the Oracle clients. In the directory where you installed them run the following to convert and install the packages in one fell swoop.


cd /path/to/oracle/rpms


When I ran it this time around, I found that I had both 32- and 64-bit packages in the same directory, and alien did not like that. Best to filter down to just the arch we want.


sudo alien -i oracle*x86_64.rpm


or for 32-bit:


sudo alien -i oracle*i386.rpm


This will take a bit of time, but should turn out okay.

Now that we have the packages, the deps, and the sources, it’s time to set up

Environment Variables

Thanks to the new build scripts there is no longer a need to set the CFLAGS and CPPFLAGS environment variables. That was a nice surprise. We’re also not going to change the dynamic link library paths as we have in the past as it seems to be overkill, we’ll just specify it in the environment.

Here are the environment variables you’ll need. This is the only place where the instructions change depending on your version of Oracle and architecture.

For a 64-bit machine you’ll want to set this:


export ORACLE_HOME="/usr/lib/oracle/11.1/client64"
export LD_LIBRARY_PATH="${ORACLE_HOME}/lib"
export TNS_ADMIN="${ORACLE_HOME}"


For 32-bit using the new 11.2 instant client you need this instead:


export ORACLE_HOME="/usr/lib/oracle/11.2/client"
export LD_LIBRARY_PATH="${ORACLE_HOME}/lib"
export TNS_ADMIN="${ORACLE_HOME}"


For 32-bit and the 11.1 client use the above, but change 11.2 to 11.1.

Again, you will want to add the Oracle environment info (the last three lines above) to your .bashrc, so that they will be used after the required logout/login.

Again, for 64-bit use this:


echo export "ORACLE_HOME=/usr/lib/oracle/11.1/client64" >> ~/.bashrc
echo export "TNS_ADMIN=${ORACLE_HOME}" >> ~/.bashrc


And for 32-bit with 11.2 use this:


echo export "ORACLE_HOME=/usr/lib/oracle/11.1/client64" >> ~/.bashrc
echo export "TNS_ADMIN=${ORACLE_HOME}" >> ~/.bashrc


This will only take effect on next log-in. Until then, you can run TOra from the shell you install it in, as the menu will not work until you log out and in again.

Note: My install of Karmic did not have a .bashrc or .profile in my home directory by default. If this fails to work on next log-in, that’s a good place to look. To test it, log out and in again then run this in a terminal.


$ echo $ORACLE_HOME
/usr/lib/oracle/11.1/client64


Include path oddity

The last piece of the puzzle, which caused me the most grief, was the fact that the script could not find oci.h, which is an include header provided by the Oracle client. After much grinding of teeth and plumbing the depths of the scripts, I found that it was only looking in certain places for the file, places which did not exist on my system. It made some assumptions that may be right in certain conditions but not mine. I found the oci.h file and after a couple tries concocted a solution that does not require making any changes to the source or script—simply create a symbolic link to put the file where the script is looking.


sudo ln -s /usr/include/oracle/11.1/client64/ ${ORACLE_HOME}/include


Building and installing TOra

Go to your build directory and you’ll see there is a tora-2.0.0 directory. Change to this directory.


cd /path/to/deb/source/tora-2.0.0.0


Run the script to build the package.


fakeroot debian/rules binary


Those of you who have done this before will notice a marked difference in the look of the compile, complete with nifty progress counters that update as each file is processed. The results, however, are the same. On the test 64-bit machine, it took me seven minutes to compile the whole thing (while browsing and listening to music and other tasks); on my laptop it took about 30 (otherwise idle) minutes.

Once you are done, you should see the new deb package in the directory below where you are.


$ ls -al ../*.deb
-rw-r--r-- 1 hudson hudson 4900786 2009-10-28 18:47 ../tora_2.0.0-4build2_amd64.deb


And for you 32-bit people it would be tora_2.0.0-4build2_i386.deb.

Install it using the following (32-bit users can now extrapolate):


dpkg -i ../tora_2.0.0-4build2_amd64.deb


The Update Issue Again

This rehashed text brought to you compliments of me, the lazy blogger. A simpler way to stop the package from updating.


echo 'tora hold' | sudo dpkg --set-selections


End Game Redux

Don’t forget your tnsnames.ora. We set up the environment to use TNS_ADMIN=/usr/lib/oracle/11.1/client64 which means that tora will look for tnsnames.ora there. The easiest way I found was to get the production tnsnames.ora file from the Oracle server itself, and place it in the $TNS_ADMIN directory. Once you have done so, start TOra and enjoy. Remember to start it from the xterm session that has the environment variables set if you have not yet logged out/in.

Nifty KDE trick

I thought this was useful enough to repeat. I wanted it to work with my current menu without logging off. Time is money, and logging off means too much lost productivity. This still works the same in KDE 4.3.2 on Karmic. Here is what I did.

1. Right click on the big blue “K” menu button.
2. Select “Menu Editor”.
3. In the KDE menu editor, on the left side, find the Development item and click the + to expand the tree.
4. Right click the TOra and select Copy.
5. Right click the TOra icon again and select Paste.
6. Left click on the TOra-2 icon.
7. In the command box enter
   
ORACLE_HOME=/usr/lib/oracle/11.1/client64 LD_LIBRARY_PATH=/usr/lib/oracle/11.1/client64/lib TNS_ADMIN=/usr/lib/oracle/11.1/client64 tora

8. Click the File menu, then Quit.
9. Click the Save button to save your changes.

Now in the “K” menu click K->Applications->Development->TOra-2 and enjoy your selecting.

I have done a bit of testing and it’s working great. The last time I did this I had issues with advanced functions like the schema browser, which work properly now. I am not sure if this is because they fixed something, or I broke something. Either way, it’s all good.

Please feel free to ask questions or post your own experiences in the comments. My test environments are limited, so there may be issues that did not arise for me. Also, I am game to tackle writing up similar guides for other distributions like CentOS or Fedora, so let me know if there is interest. I do it on Ubuntu since it makes a better desktop than most others. Next stop, Lucid Lynx (in April ‘10).

See you all on Friday for another exciting edition of Blogrotate.

References

The TOra homepage
Installing TOra with Oracle support on Ubuntu 8.04LTS (Hardy Heron)
Installing TOra with Oracle support on Ubuntu 9.10 (Jaunty Jackalope)
Kubuntu linux

As pointed out by Schlomi Noah on my last blog, MySAR was already taken by a project related to Squid reports with MySQL. I decided then to look for a new name, and as I posted initially, I want to keep the sar prefix to describe the script’s purpose by association with the OS utility of the same name. I brainstormed many names. I liked Dave Edwards’s suggestion: SARkila, but it sounds too close to tequila, so I settled with Sheeri Cabral’s suggestion: sar-sql.

The title of the Launchpad page already reflects the change. What remains to be done is: a) change the name of the Perl script and documentation; and b) change the Launchpad URL. It is likely that I will change the name of the script when I release version 1.x (see below). I’m not sure of all the implications in Bazaar regarding the URL change, so that task will have to wait for now.

Now a little more info on the status of the project.

Bug Fixes

In the latest trunk there are two patches. One corresponds to Bug #455870, which should be fixed. I’m excited since this bug was posted by a user. (Yay!)

The 2nd patch refers to a bug that came up during an implementation in a client, in a master-slave configuration (in hindsight, I should have thought about it earlier). The snapshots from the master were being replicated to the slave, so when the script ran on the slave, the autoincrement values were in conflict and stopped replication. In the latest patch (build 16), I have added a column that records the server id, as in SHOW GLOBAL VARIABLES LIKE 'server_id' to distinguish the master and slave data. I like the fact that it is possible to query the master and slave snapshots side by side to diagnose slave lag. This change makes the last version in the trunk incompatible with the newer ones, which triggered the creation of the first README file.

I will be posting a new tarball soon.

Road Map

Believe or not, there are enough ideas in the queue to justify a road map. Here’s a summary list and you’re welcome to comment on it. I promise to review all the suggestions.

Code Review

As I mentioned above, the script currently works, but it’s far from optimal. So I’ll be changing the code base with two objectives. 1) Increase overall efficiency; and 2) facilitate future improvements. The more profound code changes will be implemented in version 0.x (current version). I believe that messy code leads to bugs, so the cleaner it is, the less likely I’ll break existing functionality while adding new one.

Command Line Syntax Changes

Adding new functionality implies that the script will perform more tasks which in turn will add overhead to each one of them. To minimize this overhead, I am working on a different command line syntax and the corresponding underlying code. This change will happen in version 1.x (the next version) since the code has to be cleaned up to be effective and works as it is. The command line syntax change will imply rewriting any wrapper script that might be in place to invoke in the crontab, so this will be the opportunity to rename the script as well.

Installer

The script is so simple that distributing a tarball is enough for now, but I’d like to have an installer and possibly a package that will take care of the Perl modules dependencies, schema initialization and user credentials verification / creation. I have no target version for this. I’ll shoot for 1.x, but it’s likely to go into 2.x.

Other

I’m still working on plenty of use cases, best practices, and companion scripts examples. In time, the example scripts and documentation will be part of the complete package and installed along side with the main utility. In the meantime, just follow the blogs and the links to them in the Launchpad announcements page.

Community Participation

Having a user file a bug, Schlomi’s bringing up the name issue, and Sheeri contributing with her sanity checks are a great help, but I invite all of you out there to participate through comments to my blogs, bug reports, questions in the Launchpad page, and replying to my tweets.

I will be participating in OpenSQL Camp in Portland, OR next month. I’m sure I will have an opportunity to review my ideas with the old friends I’ll be meeting there. Stay tuned.

People often ask “what’s the best hardware to run a database on?” And the answer, of course, is “it depends”. With MySQL, though, you can get good performance out of almost any hardware.

If you need *great* performance, and you have active databases with a large data set, here are some statistics on real life databases — feel free to add your own.

We define “large data set” as over 100 Gb, mostly because smaller data sets have an easier time with the available memory on a machine (even if it’s only 8 Gb) and backups are less intrusive — InnoDB Hot Backup and Xtrabackup are not really “hot” backups, they are “warm” backups, because there is load on the machine to copy the data files, and on large, active servers we have found that this load impacts query performance. As for how active a database is, we’ve found that equates to a peak production load of over 3,000 queries per second on a transactional database — that is, normal production load gets the server to over 3,000 queries per second at peak times — and a flat average of over 500 queries per second if there are definite quiet and peak times, or if the server is used for reporting or a combined reporting/transactional load. This flat average should be taken over a period of a week or more.

We’re only showing the hardware here, not the configurations, for our three busiest/largest environments. All the configurations shown here have 2 machines, for an active primary and either an active secondary (for read-only queries) or a passive secondary (quiet until needed):

A music distribution company runs the following for primary production (Data size in the 360-380GB range):
2x Sun SunFire X4600 M2 Servers using 4xDual Core Opteron 8220 with 32GB
of RAM attached to a Hatachi DF600F SAN. The SunFire X4600 is scalable
to 8 Sockets (32 Core) and 512GB of RAM.

National post office for a G8 nation = 1.2T of data (and growing fast!).
The primary site has 2 machines connected to the same LUNs on a HA
setup. We have an ‘active’ and a ‘passive’ master configured to kick-in
if the other node fails (only one mounts the LUNs with the data). Both
these servers have 4 x Quad-core Intel Xeon processors and 16G of RAM each.

An online marketing firm has ~600GB of Data
2x Dell PowerEdge R710 with 36GB of RAM and two Intel Xeon L5520 CPUs (Quad Core) – Servers supports up to 144GB of RAM and max of 2 Sockets
Storage: Combination of Local Storage (logs, etc) and an DELL PowerVault
MD 3000 Direct Attached Storage (shared).

What are your details?

Welcome to volume 3 of Blogrotate. This is a short one this week, which is mostly dominated by the release of Windows 7. I have not had a chance to use it as yet but intend to give it a once over as soon as I get a chance. So, without further ado, on to the roundup.

Operating Systems

The big story this week was obviously the release of Microsoft’s Windows 7.  There are a number of good articles we’ve seen that cover different aspects of the release.  Some of our favourites are below.

Emil Protalinski over at Ars Technica has a look at the things you should know about Windows 7 in his article Windows 7 is here including pricing information, editions available and upgrade vs. fresh install.

Andrew Binstock at InfoWorld has an interesting look at the changes made to the Windows 7 kernel, specifically with regards to multithreaded performance in Windows 7 on multicore: How much faster.

Do you want to know if your system will be able to run Windows 7? See  Emil Protalinski’s article Are you Windows 7 compatible for more.

And on an amusing note, Apple premiered a new Mac ad last night (the same day Windows 7 was released) poking fun at Windows’ broken promises of the past.  This is a hoot, even if you love your Windows.  Careful if you have web filtering, some comments are NSFW.

Canonical and IBM team up on Ubuntu-based Win 7 alternative is an interesting article by Ryan Paul about an attempt to divert companies from using Windows.  The idea is that using a free OS like Ubuntu, and IBM’s Client for Smart Work can allow a company to extend its IT dollar by reducing licensing costs and keeping their old hardware, on which Ubuntu will run just fine.

Happy 5th anniversary to Ubuntu Linux. 5 years later, 5 ways that Ubuntu has made Linux more human is a look at some of the ways Ubuntu has impacted the OS world.

The H has posted an announcement that states CentOS 5.4 released to the public. We’ve been waiting for this since RedHat 5.4 was released in September. Time to upgrade your servers. The official release announcement can be found on the CentOS-Announce mailing list archive post Release for CentOS-5.4 i386 and x86_64.

Networking

Better wifi for mobile computers? Slashdot has an interesting post about a town in Virgina that has launched the first ever network employing unused frequencies, or white space, from the TV spectrum to run the network. See First Public White-Space Network Is Alive and the links therein for more.

Hardware

Slashdot has a post about NCSU’s Fingernail-Size Chip Can Hold 1TB of data. This is really something, but I can barely afford a 16GB now, these will cost a fortune.

Operating Systems (again?)

Last but certainly not least, be on the lookout for the release of Ubuntu 9.10 Karmic Koala which is slated for release on October 29. I’ve mentioned previously that I have been running the beta of Kubuntu for a couple of weeks now and I have to say, even with the beta bugs, I like what I see. More about that next week.

This week the Log Buffer is a little more challenging for two reasons: a) Oracle Open World 2009 and b) the controversy around Monty Widenius‘ opposition to Oracle owning MySQL due to the Sun acquisition, so let’s go straight to the articles.

Oracle – Oracle Open World 2009

There is so much material about OOW09, that I’m giving a full subtitle to it.

Let’s start with a quick recap of the keynotes by Scott McNealy and Larry Elison in this article by Andrew Clarke: The return of The Scott And Larry Show. The recap suggests that the presentations aimed to show how Sun & Oracle (aka Team Red) would challenge IBM (aka Big Blue) head-on. Larry didn’t fail to mention Oracle’s intention to invest in MySQL.

Of course the conference wasn’t short on technical issues, and these articles prove it. Jason Arneil talks about 11 Things about 11gR2. Chen Shapira discusses one of the lessons she learned, and shares with us some Shell Script Tips.

Apparently one of the most exciting event was the Bloggers’ Meetup, and Pythian’s T-shirts—which had to be signed by the attendees to win a netbook—were a great success. You can read all about it in Doug Burns’s article OOW 2009 – Blogger’s Meetup (don’t miss the links at the bottom for more of his articles); and in Richard Foote’s Day 3 Highlights. And of course our own Alex Gorbachev’s blog Pythian OOW09 Diaries: Bloggers Meetup with plenty of pictures.

Last but not least is Oracle Magazine’s DBA of the Year: Husnu Sensoy. Congratulations from the folks at Pythian, Hunsu!

Oracle

Let’s start with Chris Foot’s The Art of Being a Successful DBA – Finding Information Quickly.

I agree 100% with all Chris’s criteria, so here are my links for the MySQL DBAs: 1) Planet MySQL or it’s Twitter counterpart @mysql_community, 2) MySQL online documentation (remember to read the manuals), 3) MySQL Forums & 4) Twitter’s #mysql RSS feed. I have more resources bookmarked, but these are a great starting point. Jonathan Lewis’s article Experts is somewhat related and has great advice as well.

How many DBAs face the question of recovering data that has been accidentally deleted? Luis Moreno Campos presents Reversing the effect of a TRUNCATE TABLE in Oracle 11gR2. And still on typical DBAs’ tasks, we also have articles by Richard Foote: How To Rebuild And Make An Index Bigger, Not Smaller (Carry That Weight), Miladin Modrakovic’s Blocking locks history, Marco Gralike’s HOWTO: Partition Binary XML, XMLType Storage and Slavik Markovich’s important recommendation on security Blind SQL Injection in Oracle. The latter one has Oracle in the title, but I believe the concepts he discusses can be applied to any database.

MySQL … Or Should That Be Oracle?

Monty Widenius, one of the three founders and creators of MySQL wrote an interesting press release concerning Oracle/Sun explaining why he believes that the European Unionconcerns about Oracle owning MySQL are justified. This created quite a stir in the MySQL community, with many people writing their own open letters, recommendations, and editorials on the matter.

Zak Greant’s Letter to the EC on the Oracle/Sun Takeover, Lukas Kahwe Smith’s MySQL needs to be saved from Oracle? and Brian Aker’s RMS, GPL, The Peculiar Institution of Dual Licensing are just a sample of what’s out there. If you are interested in the subject, I recommend you browse the articles in Planet MySQL.

In my opinion, I don’t believe that the community and customers should be concerned—there are enough companies in the ecosystem with MySQL branches and and/or providing services, that any Oracle action to de-activate or damage MySQL would be pointless. The only companies that could be affected, are those who use MySQL embedded in products which are not released under GPL (OEM customers). These companies hold commercial licenses, but could still open their software under GPL or compatible licenses and concentrate in their core values to avoid any issues that Oracle may cause.

MySQL

To finish on a high note, I’d like to highlight a few good MySQL-related articles that I came accross while preparing this blog.

Percona has published two good articles that help us to understand some of the decisions that a typical MySQL DBA has to face sooner or later. The first article describes when storing the data in the DB might not be a good idea and memcached would, in MySQL-Memcached or NOSQL Tokyo Tyrant – part 1. The other article discusses different High Availability strategies to use with MySQL in Finding your MySQL High-Availability solution – The questions with plenty of comments. Kris Buyaert decided to comment on the article in a blog of his own: Nines , Damn Nines and More Nines.

Finally, Lenz Grimmer invites us to submit talks for FOSDEM 2010 in Brussels, Belgium: FOSDEM Call For Participation opened – submit your talks now!. This might be a good excuse to visit Europe.

I hope the SQL Server folks don’t feel dissapointed that we left them out on this edition, but Oracle Open World and Monty’s press release gave us too much material to sift through. I’m sure Dave will come up with plenty of cool highlights for you on the next edition of the Log Buffer.

Yes, it’s almost that time of the year when one of the best Oracle conferences in the world opens its doors to attendees in Birmingham — UKOUG Conference 2009: Technology & E-Business Suite. The lineup of speakers will be fantastic as usual and agenda is full of juicy bits — You will have usual troubles scheduling sessions to attend and hate to make compromises between presentations you want to see badly but that’s kind of problems you’d rather have at a good conference.

The past year was very eventful so I feel like I haven’t been at the UKOUG Conferences for years even though I did come to the UKOUG Conference 2008. This conference is something special for me — it’s the first conference I attended and presented on so it’s set the tone for the whole conferencing experience of my life and I’m very grateful for that! So far, I haven’t missed a single year since my first UKOUG conference and I hope I keep it this way for years to come.

The conference is different this year and I support the changes very much. Oracle family of products has become so large over the last few years that UKOUG conference kept growing and growing every year. This year, it was decided to split the technologies and leave only the core Oracle Server and E-Business Suite Technology with the conference scaling down to 3 days. In the past, anything more that 3 days was quite difficult as those days are very intense and the last day I felt quite tired which is really unfortunate considering that masterclasses are running during that last day. I think it might also be a timely decision in the light of current economic conditions when most organizations had to cut their educational budgets — attending a 3 days conference is easier to justify than a whole week.

I’m doing few sessions this year:

• Mon, 30-Nov 11:50 — Oracle ASM 11g – the Evolution.
• Tue, 1-Dec 16:50 — Managing Oracle Connection in WebLogic with RAC and Data Guard.
• Mon, 30-Nov 16:25 — Server Manageability Roundtable

I have already done the presentation about ASM few times but it was always incomplete (especially when I got access to 11gR2 beta program and learned new ASM features) so now I can include all the new cool stuff that’s come with Oracle Database 11gR2.

You might know that I’ve been doing few presentations about connection management with Oracle RAC but this time I decided to focus on a specific application tier. I think WebLogic server and its specifics in connection handling with Oracle RAC has not been covered at all so why don’t I try that?

Finally, it will be the first time that I’ll be organizing a roundtable at UKOUG conference — Server Manageability Roundtable. I’m really looking forward to it and to contributions from the attendees. The success of any roundtable depends on the interest of attendees and hitting the right topics. I encourage you to attend and post here what are the hottest issues you experience in server manageability area and what are the areas you would like to hear most about.

Early bird registration finishes on 30th of October so don’t miss that if cost is a big factor.

That’s all folks. See you in Birmingham!

I’m sure that most people have at least one of the tools listed in Ronald Bradford’s article: Monitoring MySQL Options. Many of these tools, such as Nagios and Cacti, also monitor the operating system. However, in the same way that a quick look at sar’s output can give you some insight on the OS, with MySAR you can do the same for the MySQL server. This is especially useful when  it is not possible to access a monitoring tool’s graphic interfaces.

What Was Going On Around 2:30pm?

This is a question a customer asked us. To answer it we ran MySAR for a few days and queried the results for analysis. Looking at the data, we determined that the number of INSERT operations was significantly higher than any other, so we queried for the Com_insert status values. Com_insert is a counter that accumulates the number of INSERTs issued since the last server start (or since the last FLUSH STATUS command). For details on the variables available check Chapter 1. mysqld Options/Variables Reference.

The initial query we used was:

select value_stat_name name, value_stat_timestamp ti, value_stat_number val, value_stat_delta delta from value_stat
where value_stat_name like "Com_insert"
and HOUR(value_stat_timestamp) between 13 and 15
and date(value_stat_timestamp) between "2009-08-22" and "2009-08-25"
order by 1, 2
+------------+---------------------+-----------+-------+
| name       | ti                  | val       | delta |
+------------+---------------------+-----------+-------+
| Com_insert | 2009-08-22 13:00:01 | 163777289 | 16051 |
| Com_insert | 2009-08-22 13:10:01 | 163794262 | 16973 |
| Com_insert | 2009-08-22 13:20:01 | 163810327 | 16065 |
| Com_insert | 2009-08-22 13:30:01 | 163826242 | 15915 |
....
| Com_insert | 2009-08-25 15:20:01 | 168362221 | 13835 |
| Com_insert | 2009-08-25 15:30:01 | 168376888 | 14667 |
| Com_insert | 2009-08-25 15:40:01 | 168392410 | 15522 |
| Com_insert | 2009-08-25 15:50:02 | 168408476 | 16066 |
+------------+---------------------+-----------+-------+
72 rows in set (1.56 sec)

72 rows of numbers are not very easy to interpret, so the next step was to create a graphic to visualize the results. Taking the ideas from Shlomi Noach’s blog, SQL pie chart, I decided to try and create the charts using the Google Charts API.

Easy Graphics How-To

The first step was to create the string of values needed for the chart. I achieved this using GROUP_CONCAT, adding some formulas to scale the values properly:

select day(value_stat_timestamp) as day,
GROUP_CONCAT((value_stat_delta-12000)/70) as series from value_stat
where value_stat_name like "Com_insert" and HOUR(value_stat_timestamp) between 13 and 15
and date(value_stat_timestamp) between "2009-08-22" and "2009-08-25"
group by day(value_stat_timestamp);
+------+------------------------------------------------------------------------------------------
| day  | series
+------+-----------------------------------------------------------------------------------
|   22 | 57.8714,80.3000,81.2571,94.3857,85.2571,66.0857,94.6429,74.2857,91.1000,...
|   23 | 74.4857,95.5857,103.4571,101.3000,75.9429,75.3571,79.3000,83.0571,94.7714,...
|   24 | 25.6286,38.8429,31.7000,36.3857,50.0143,44.3286,46.1571,39.4714,34.3857,21.5571,...
|   25 | 6.8000,12.9714,33.0429,13.8286,26.2143,38.1000,50.3143,19.4429,13.0857,3.2143,7.9143,...
+------+------------------------------------------------------------------------------------------
4 rows in set (0.34 sec)

(To make the output more readable, I’m not including the whole lines.) Using the values corresponding day 22, the most simple line graphic can be created with following HTML tag:

<img src="http://chart.apis.google.com/chart?cht=lc&chs=400x200&
chd=t:57.8714,80.3000,81.2571,94.3857,85.2571,66.0857,94.6429">

Resulting in:

In the URL string, cht, chs, and chd represent chart type, chart size, and chart data respectively; you can check the details in the Google Chart API URL I quoted above.

You can copy and paste the <img> tag above into an HTML document (no need to add any text) and open it with any browser. For Firefox, the URL to open a file on disk would look similar to this:

file:///path/to/file/file_name.html

Go ahead, open any text editor, copy and paste the example above in any file, save it with extension .html and open it with your browser.

The chart with the complete data set and and some decorations looks like this—each color represents a different day from the dataset above:

Using the function CONCAT,  it is possible to create the complete <img src=”…”> tag directly from the SQL statement by adding the proper sub-strings.

Conclusion

Using MySAR, it is possible to to collect the data, query the data for diagnosis. and create graphics to visualize those values.

By the way, there was nothing significant happening in the database at 2:30pm.

Welcome to week 2 of Blogrotate. It was a short week due to Thanksgiving (Canada) and Columbus Day (US), but the world of IT is always buzzing. So as they say at the race track, pitter-patter, let’s get at ‘er.

Internet

Have you ever wondered how much trouble can be caused by a single typo? This week a single typo in a script to update all zone files for the .se (sweden) TLD (top level domain), dropping the entire .se domain off the internet for almost 2 hours. Royal Pingdom has the full story in “Sweden’s Internet broken by DNS mistake”. This is why we need tight controls on change management. It’s called testing guys. Sweden. Give me a call.

Facebook now has 30,000 servers and produces 25TB (that’s tera-byte kids) of log data per day. The Data Center Knowledge site has some interesting details in “Facebook now has 3000 Servers”.

Cloud

Lot’s of buzz this week about T-Mobile’s service disruption and subsequent loss of users data. Discussion over whether the problem was a cloud failure or not was one hot topic. Data Center Knowledge discussed it here in “The Sidekick Failure and Cloud Culpability”. Ars Technica had some more on the cloud debate with “T-Mobile and Microsoft/Danger data loss is bad for the cloud”. It looks like most or all users will have lost their data due to the lack of backups, see “Some Sidekick Users May Recover Data” for more. I am sure there will be more fallout from this one.

Enterprise Storage Forum has an interesting evaluation of the limitations of cloud computing for corporations, specifically due to bandwidth limitations and hardware error rates. See Henry Newman’s article titled “Why Cloud Storage Use Could Be Limited in Enterprises”.

Nate Anderson over at Ars Technica has an interesting read about fear mongers who say our beloved intertubes are going to die in “The Internet is about to die. Literally die!”.

Operating Systems

IT Wire claims “Microsoft teams up with Family Guy to sell Windows 7″. That’s just sad. If they are going to glorify Windows then I really can’t see how they can funny it up. I am guessing Seth will get to pan Microsoft just to spread word that Windows 7 is coming.

VMWare has announced that their new “VMware Fusion will support Windows 7 in more Mac-like way” says IT Wire. This “Unity” feature looks a lot like VirtualBox’s “seamless” mode. Check out the You Tube video “Unity in VMware Fusion for Mac OS X” to see it in action.

Jim Zemlin, the executive director of the Linux Foundation gave the keynote address at the Maemo Summit and said that he thinks Linux could be the dominant OS for mobile phones and devices. Ars Technica has more in “Will Linux be the dominant OS for consumer electronics?”.

And from the wicked cool idea department

An interesting study from McCormick University on using your PC’s existing hardware as a sort of sonar to detect when you are there. See “Research Group Uses Sonar for Computer Power Management”. They plan to use this as a method of detecting if you are close to your computer and to turn off your screen if you are not, then turn back on again when you return. The group is currently looking for guinea pigs testers to evaluate if there is any real world power savings. The link to the software is in the article. Hey, if my TV remote control can do it, why not a laptop?

That’s all we’ll have time for this week. Come back again next week for more Blogrotate and, as always, feel free to speak your mind or post your interesting stories in the comments.

This presentation was be done by Sheeri Cabral of The Pythian Group and went into how to use SHOW ENGINE INNODB STATUS to get more information about your Innodb tables, foreign keys and transactions. This is a great presentation to learn how InnoDB works.

It also went through how to use SHOW ENGINE INNODB STATUS to tune several InnoDB variables:

innodb_adaptive_hash_index
innodb_commit_concurrency
innodb_concurrency_tickets
innodb_file_io_threads
innodb_log_buffer_size
innodb_max_purge_lag
innodb_sync_spin_loops
innodb_thread_concurrency
innodb_thread_sleep_delay

The slides can be downloaded from:

http://technocation.org/files/doc/ShowEngineInnoDBStatus.pdf

(note that the slides open up to the middle section, which has slides + notes, but if you just want the slides without notes, that starts on page 1 of the PDF)

The video can be watched below, or directly on YouTube at http://www.youtube.com/watch?v=ocdjspoLM58