In our latest database journal piece, we cover some useful Oracle-supplied pl/sql stored procedures to help you instrument and debug your code, dump metadata descriptions of your database objects (a la MySQL dump –no-rows) and much more.

Take a look at  DBA Insider - Useful PL/SQL Packages.

?WL4271 Encrypted Online Backup: Preview 3? branch in Launchpad

Now with Windows support. Many thanks to Chuck Bell for helping get the code going on Windows.

We can however, all sit around dumbfounded as to how Windows has so little of a POSIX like layer and yet doesn’t define ENOTSUP.

As a refresher, this tree implements:

• Encryption for MySQL Online backup
• Algorithms and keysizes supported:
  • 3DES
  • AES (128, 192 and 256bit)
• World peace

(world peace not included)

UPDATE: If you’re wondering why the branch isn’t there, it’s still pushing to launchpad. Yes, that’s over 7 hours to push a branch. ick. Can’t be too …

available now: Flexviews for MySQL 1.0.2-alpha

bug fixes:
ensure_validity() was failing when an incremental refresh mview did not contain an AVG()
the delta application phase of refresh was not cleaning up the delta log
removed serialization overhead, improving dml performance. needs further improvement.

Get it at:
http://sourceforge.net/projects/flexviews/

One of our customers was setting up box for MySQL LVM backups. The box had 2 RAID volumes (internal drives and enclosure) with database being stored on enclosure drives and internal drives used pretty much for OS and logs (database grew too large and so was moved to enclosure completely).

As database was large and active I thought it would make sense to have LVM undo space located on the internal drives so LVM and backup has less backup on the running system.

As the system was configured creating snapshot volume with 100G of undo space failed complaining there is not enough space available on the volume….

The problem was internal and external volumes were configured as different “volume groups”. Volume groups in LVM live completely isolated life and you can’t use space from one volume group for snapshot for another.

What should have done instead is creating single volume group and having RAID volumes as …

Today Sun announced release of X4540 Open Storage server (a.k.a Thor). We were fortunate to get early access to Thor to certify Amanda Enterprise and Zmanda Recovery Manager (ZRM) for MySQL. Both solutions are optimized for backing up to disk, are already certified with Solaris 10, and leverage capabilities of ZFS. So it made perfect sense to certify them on Thor, effectively creating a high-performance and yet relatively inexpensive backup appliance.

The unit we were using is powered by 8 CPUs operating at 2300 MHz and provides 48 SATA drives with total capacity close to 50TB in 4U enclosure. That is good packing; a typical EMC unit with so much capacity will take the whole rack.

While looking at the performance of multiple datafiles I noticed something kind of interesting.  A query searching for a integer based PK value that had the value escaped with single quotes was faster then another lookup on a PK without the single quotes.  That seemed a little counterintuitive… if anything I would expect escaping an integer to take a little longer.  But because they were searching for different keys, its not really all that meaningful, but i thought I would try and see just how much of an impact quoting your integer will cause.  So I used the same query tool I put together in perl to do the multi-datafile tests to run 2 queries searching for an integer PK.

See here:

mysql> select qryid, note, avg(averagequeryruntime),  count(*), querytext from Logger_single group by qryid, note; …

Over the past couple of months I have seen a few different clients with innodb setup to have multiple datafiles opposed to using file per table or a single datafile. While I generally do not think twice about a handful of datafiles, I started seeing clients with 50-100 or more 2-4GB datafiles on a single RAID set ( or in some cases a single disk ), and this started to raise the little alarms in my head. Asking these various clients the key question, why? The answers were surprisingly similar. Things like: “we always did it this way” , “their used to be a 2GB file limit”, “we did this in oracle”, etc. Their was not a ton of compelling answers ( like manageability and performance ).

Looking at these systems it seemed like they are really causing “self-induced” fragmentation. They all have large tables, and are doing scans over large amounts of the data… for instance a 40GB table in a database with 2GB datafiles. In the best …

I’m looking for some more unstoppable hackers to work with at Canonical, and doing the usual thing of going through CV/resume submissions. What’s different from when I was doing this last year? YouTube! In the past I’ve been impressed when someone not only had a resume but links to a personal site or profile that highlighted their professional activities in much more detail than a plain resume would - perhaps links to open source contributions that they are proud of, or designs that they’ve done, etc. One resume I just got links to a YouTube video showing a demo of a robot that the applicant built and programmed in C and Python. This definitely got my attention.

I can’t say I’ve used the ARCHIVE storage engine before, but at the NY MySQL Meetup last night there was discussion of the improvements to ARCHIVE in 5.1 and the fact that you could not DELETE from archive. A simple test confirmed this indeed throws an error.

DROP TABLE IF EXISTS url_log;
CREATE TABLE url_log(
log_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
log_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_id INT UNSIGNED NULL,
url VARCHAR(100) NOT NULL,
PRIMARY KEY (log_id))
ENGINE=ARCHIVE;

DELETE FROM url_log;
ERROR 1031 (HY000): Table storage engine for 'url_log' doesn't have this option

However, part of MySQL 5.1 which is RC status, there is partitioning. Thinking that one …

VMWare announced that their 2008 revenue growth outlook would be "modestly below" its earlier stated target of 50% and that CEO and founder Diane Greene would be resigning immediately. That double whammy sent VMWare stock tumbling by more than 25%. VMWare appears to have increased competition, not only from Microsoft's recently released near final Hyper-V technology, but also from open source variants from Red Hat, Citrix, Sun and others based on the Xen hypervisor virtual machine monitor. All of these choices are pretty much free, which will be a tough price for VMWare to match. In an odd twist of... READ MORE