The latest from the MySQL community

Week 6 (30th June – 7th July 2008 )

Key Accomplishments Last Week

1. Implemented a feature to rearranger.c to allow it to accept multiple input files (as requested by Brian). There are a few problems with this feature, however, and I plan on rectifying them soon.

2. Learnt some basics of Lua.

3. Learnt a bit about the ddd.

4. Started learning about the internals of mysql and bought a book, “Expert MySQL” by Charles A. Bell. I already have a fair idea about the working of mysqlslap. Once I get a good grasp of the ddd debugger I should be able to understand mysqlslap more thoroughly.

Key Tasks that stalled last Week

None

Key Concerns

The upcoming midterm evaluation gives me a sense of urgency in my work. On looking back, I feel I have given my best but it could definitely have been better. Hope …

Week 6 (30th June – 7th July 2008 )

Key Accomplishments Last Week

1. Implemented a feature to rearranger.c to allow it to accept multiple input files (as requested by Brian). There are a few problems with this feature, however, and I plan on rectifying them soon.

2. Learnt some basics of Lua.

3. Learnt a bit about the ddd.

4. Started learning about the internals of mysql and bought a book, “Expert MySQL” by Charles A. Bell. I already have a fair idea about the working of mysqlslap. Once I get a good grasp of the ddd debugger I should be able to understand mysqlslap more thoroughly.

Key Tasks that stalled last Week

None

Key Concerns

The upcoming midterm evaluation gives me a sense of urgency in my work. On looking back, I feel I have given my best but it could definitely have been better. Hope …

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.