Learning

Cluster Talk at March 2008 Boston User Group -- Slides and Video Are Up!

Well, nobody from Sun showed up in person, but we got a great rendition of "Where were you when you heard THE NEWS that Sun bought MySQL?" from Mark Rubin, the MySQL Sales Engineer for the New England area, and from Giuseppe Maxia, who revealed something very interesting and riveting. From there, Giuseppe gave a short talk on what it's like to work at MySQL, and then we moved on to the topic of the user group meeting, "What is MySQL Cluster Good For?" The slides for the talk are downloadable in PDF Format 61kB and Flash (SWF) format, 31kB The video can be played at http://technocation.org/node/572/play and downloaded (404 Mb) at http://technocation.org/node/572/download

Backups: A Video Presentation By Keith Murphy From the June 2008 Boston MySQL User Group

The Boston MySQL User Group was lucky enough to get Keith Murphy to speak at the June User Group meeting, about backups. Links referred to in the presentation: MyLVMBackup by Lenz Grimmer http://lenz.homelinux.org/mylvmbackup/ InnoDB Hot Backup: Prices are at: http://www.innodb.com/hot-backup/order/ and at the time of this writing are: 1-Year License € 390 USD$ 605 per server Perpetual License € 990 USD$ 1540 per server Zmanda prices are at: http://network.zmanda.com/shop/home.php?cat=1,3 Current prices are between the range of $100-$450, and a license is needed for each server and each client. So if you have 1 server and 3 clients, you need 4 licenses. Online Backup This is interesting reading about online backup, an article about how the online backup works at: http://blogs.mysql.com/peterg/2008/05/19/mysql-60-feature-2-online-backup/ This page (at the bottom, under "Replication") states that the desired behavior is that the binary log does not log the restore or the backup. However, it does state that this is not implemented yet, nor even decided upon. There's also a great page on Online Backup at the MySQL Forge: http://forge.mysql.com/wiki/OnlineBackup And if you're short on time, the manual has a short page at http://www.nongnu.org/rdiff-backup/ duplicity: http://duplicity.nongnu.org/ It was mentioned that some people use a code repository to backup their database: http://krow.livejournal.com/593424.html

Video: A Match Made in Heaven? The Social Graph and the Database

At the 2008 MySQL Conference and Expo, Jeff Rothschild of Facebook.com delivered a keynote entitled "A Match Made in Heaven? The Social Graph and the Database". See people's blog posts on the Forge at http://forge.mysql.com/wiki/MySQLConf2008ThursdayNotes#A_Match_Made_in_Heaven.3F_The_Social_Graph_and_the_Database

Video: Falcon for Innodb Users

At the 2008 MySQL User Conference and Expo, Ann Harrison and Kevin Lewis spoke about "Falcon for Innodb Users". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008WednesdayNotes#Falcon_for_InnoDB_Users

Video: Architecture of Maria: A New Storage Engine with a Transactional Design

At the 2008 MySQL User Conference and Expo, Monty Widenius spoke on "Architecture of Maria: A New Storage Engine with a Transactional Design ". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008WednesdayNotes#Architecture_of_Maria:_A_New_Storage_Engine_with_a_Transactional_Design

Video: Testing PHP/MySQL Applications with PHPUnit/DbUnit

At the 2008 MySQL Users Conference and Expo, Sebastian Bergmann presented "Video: Testing PHP/MySQL Applications with PHPUnit/DbUnit". See all the blog posts others have written about the tutorial from the Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008TuesdayNotes#Testing_PHP.2FMySQL_Applications_with_PHPUnit.2FDbUnit.

Replication Presentation at the November 2007 Boston MySQL User Group

Patrick Galbraith, former MySQL employee who helped write replication, came to the November 2007 Boston MySQL User Group and did a fantastic presentation on replication, including showing how to set up replication and walking us through the steps in real-time, setting up an example server. Smaller, 320x240, 293M WMV file for download References mentioned in the video: Brian Aker's DBIx::Password Giuseppe Maxia's article about federated tables -- I think that's the right one, feel free to comment if it's not. MySQL Toolkit -- renamed Ma'atkit -- lives at http://sourceforge.net/projects/maatkit sample my.cnf files for 2 different dual-master replication scenarios

Teaching Thinking Patterns

How do you teach a thinking pattern? In my previous job I worked with a data analyst, who was really good at thinking about how our data correlates among itself. He was good at the data analysis, good at thinking up relationships, and good at coming up with complex comparisons.
However, he wrote some pretty funky SQL. We had him take a course in basics of MySQL, and from time to time I’d take a gander in the slow query logs and pick a few queries and point out the good and bad things. Most of these are optimization tips, such as “Indexes don’t apply to columns when you’re applying a function to that column”.

MySQL Proxy Presentation at the September 2007 Boston MySQL User Group

I have finally managed to watch and slightly edit the September 2007 Boston MySQL User Group presentation I did on the MySQL Proxy. It's geared towards beginners, and has lots of examples, including explaining some of the examples that come bundled with the MySQL Proxy. Direct Play Download video (.wmv file, 612 Mb) Download video (.wmv file, 76.10 Mb) Enjoy! Some resources: Presentation Slides PowerPoint (ppt) or PDF or Flash (swf)
Official Documentation for MySQL Proxy
Proxy information on the MySQL Forge Wiki, including getting started guide, link to FAQ, forums, and lots more articles.
MySQL Proxy Download
Articles about MySQL Proxy written by Giuseppe Maxia Giuseppe Maxia will be leading Introducing Lua for MySQL Proxy scripting for the [free] MySQL University on Thursday, December 13th at 16:00 CET (15:00 URC, 10:00 EDT, 07:00 PST). Make sure to sign up and read Instructions for Attendees. (From his blog, "MySQL University is a series of online expert lessons that you can join for free and attend from the comfort of your home or office. The slides are provided in either PDF of wiki pages, the audio is an ogg stream, and you can interact with the lecturer via IRC. If you have heard of MySQL Proxy but haven't got the time of getting involved with it yet, this session is for you. If you were interested but you thought that another scripting langiage would be too difficult, give this session a chance.") Note: the wishlist at the end includes startup scripts and integraton with my.cnf, my.cnf, which I am informed will be possible with the chassis currently under development for version 0.70.

The Real Reason Why Oracle Costs So Much

http://www.oracle.com/openworld/2007/appreciation.html
Now, Billy Joel is one of my all-time favorite pop musicians. I saw him in concert and nosebleed seats at the Boston Garden cost me USD $100 per ticket, and I bought 4 tickets (my twin brother is a die-hard Billy Joel fan, they were a holiday surprise 2 years ago!)
Billy Joel regularly sells out sports arenas. I can only imagine how much Oracle paid to have a concert with him.
And don’t get me wrong, the rest of the list is also stellar. Which only adds to my disbelief.

Log Buffer #72 — a Carnival of the Vanities for DBAs

Welcome to the 72nd edition of Log Buffer, the weekly review of database blogs.
Oracle OpenWorld (OOW) is over, and Lucas Jellema of the AMIS Technology blog notes the OOW Content Catalog has been updated with most of the presentations available for download.
On his way home from OOW, Chris Muir of the appropriately titled One Size Doesn’t Fit All blog notes how OOW and the Australian Oracle User Group Conference and OOW compare with regards to 99% fewer attendees in AUSOUG Perth conference - from 45k down to 350.

Top 10 MySQL Best Practices

So, O’Reilly’s ONLamp.com has published the “Top 10 MySQL Best Practices” at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.
For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.

“kill” Oddness

So, at midnight I got a call from customer service saying our site was slow. I narrowed it down to one of our auxiliary databases, that seems to have gotten wedged just about midnight. Normal queries that took less than 4 seconds started taking longer and longer, moving up to 5 seconds and past 30 seconds in the span of a minute or so.
In the moment, I thought killing off all the queries would be a good move. My kill script, which consists of:

<br />
for i in `/usr/bin/mysql -u user -pPass -e ’show full processlist’ | grep appuser | cut -f1`<br />
do<br />
mysql -u user -pPass -e “kill $i”<br />
done<br />