High Performance Ruby on Rails and MySQL David Berube (Berube Consulting) From the official conference description is at http://www.mysqlconf.com/mysql2009/public/schedule/detail/6942 MySQL is among the fastest relational databases commonly available today; unfortunately, the database alone is only part of the picture. For todays web applications, the one weak link in the entire performance chain from the network to the web application and ending in the database can cause an entire application to seem slow. Unfortunately, Ruby on Rails on exacerbates this problem: Rails makes it easy to develop complicated web applications fast, but it also makes it easy to access your databases in an extremely inefficient manner. Thousands of queries may be generated when just a few are necessary. Even if you can get past that problem, ActiveRecord itself can be a problem: it may create thousands or even millions of  []

Using the Event Scheduler: The Friendly Behind-the-Scenes Helper Giuseppe Maxia (Sun Microsystems Inc), Andrey Hristov (SUN Microsystems) Using the Event Scheduler: The Friendly Behind-the-Scenes Helper Giuseppe Maxia (Sun Microsystems Inc), Andrey Hristov (SUN Microsystems) Download the PDF slides at http://assets.en.oreilly.com/1/event/21/Using%20the%20Event%20Scheduler_%20The%20Friendly%20Behind-the-Scenes%20Helper%20Presentation.pdf From the official conference description at http://www.mysqlconf.com/mysql2009/public/schedule/detail/7115 The Event Scheduler is a framework for executing SQL commands at specific times or at regular intervals. The basics of its architecture are simple. An event is a stored routine with a starting date and time, and a recurring tag. Once defined and activated, it will run when requested. Unlike triggers, events are not linked to specific table operations, but to dates and  []

What's new, in a nutshell: http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html Release notes: http://dev.mysql.com/doc/refman/5.1/en/news-5-1-x.html (In the video, it's the page entitled "Changes in release 5.1.x"). And yes, very early on (at about 2 minutes in) I talk about my take on Monty's controversial post at http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html The slides can be downloaded as a PDF at http://technocation.org/files/doc/2008_12_New51.pdf or in Open Office presentation format at http://technocation.org/files/doc/2008_12_New51.odp

The ScaleDB Storage Engine: Enabling High Performance and Scalability Using Materialized Views and a Shared-Disk Clustering Architecture Moshe Shadmon (ScaleDB) Slides can be downloaded at http://assets.en.oreilly.com/1/event/21/The%20ScaleDB%20Storage%20Engine_%20%20Enabling%20High%20Performance%20and%20Scalability%20Using%20Materialized%20Views%20and%20a%20Shared-Disk%20Clustering%20Architecture%20Presentation.ppt The official conference page is at http://www.mysqlconf.com/mysql2009/public/schedule/detail/7112

If You Love It, Break It: Testing MySQL with the Random Query Generator by Philip Stoev (Sun Microsystems) The description is at: http://www.mysqlconf.com/mysql2009/public/schedule/detail/6363 Download the presentation slides (ppt) at http://assets.en.oreilly.com/1/event/21/If%20You%20Love%20It,%20Break%20It_%20Testing%20MySQL%20with%20the%20Random%20Query%20Generator%20Presentation.ppt

Sheeri Cabral speaks about the changes in MySQL 5.1 and everything you need to know about upgrading. Get the slides at http://technocation.org/files/doc/2008_12_New51.pdf See the accompanying blog post with more information at http://www.pythian.com/news/1414/new-in-mysql-51-sheeris-presentation

Chasing Bottlenecks by Morgan Tocker Description: The best way to performance tune a system is to find out what your bottlenecks are, and attacking those first. In the first part of this session, I'll be looking at some of the issues faced with common database workloads. From there, I'll then be showing how you can get more information out of MySQL and your Operating System to find out about your workload. This session is designed for beginner to intermediate MySQL users.

At the May 2009 Boston MySQL User Group, Giuseppe Maxia of Sun Microsystems gave a presentation about MySQL 5.4 with use cases and benchmarks to show how it outperforms all other current MySQL releases (including the Percona, OurDelta, and Google releases/patches). The slides are at http://www.slideshare.net/datacharmer/mysql-54-theory-and-practice

Advanced Query Manipulation with MySQL Proxy Kay Roepke (Sun Microsystems) From the official conference description at http://www.mysqlconf.com/mysql2009/public/schedule/detail/7040 Currently MySQL Proxy only comes with an incomplete tokenizer for a subset of the MySQL dialect. Many use cases require more knowledge about the query that a stream of tokens can provide and users are force to create their own parsers, most of which are handwritten and simplistic, in Lua. While this is often sufficient for special cases and specific applications, it cannot serve as an extensible and robust framework. For the purpose of query formatting in the MySQL Enterprise Monitor I have written a new parser using ANTLR, where I am a committer in the project. Due to the nature of ANTLR generated recognizers, it is possible to target different implementation languages with little effort and thus has been integrated with  []

Use Dtrace to monitor your queries.

A working "progress bar" for ALTER TABLE. Until MySQL / Drizzle can do this "natively", this is a pretty neat trick

Shows how to start MySQL Cluster 7.0 with 2 management nodes using the new ndb_mgmd behavior caching the configuration and distributing it to the other management nodes.

Shows how to do a rolling restart after a configuration change using MySQL Cluster 7.0 with two management nodes.

MySQL DML Statistics using mysqlbinlog - 1 liner

A year ago, the outline was being written. A lot of work was crammed into the intervening months, and I am happy and proud to announce that the MySQL Administrator's Bible has been published, and is sitting on the shelf at many major booksellers already. The official publication date is today -- Monday, May 11th, 2009 -- although some stores have had copies for a week, including Amazon.com.The MySQL Administrator's Bible, published by Wiley Press (available on Amazon.com at

(one note, I have updated a previous blog post by adding the video for the LISA presentation I gave entitled "How to Stop Hating MySQL")OpenSQL Camp is in full swing! Baron Schwartz has done an amazing job organizing this free unconference.We are well into the 2nd session of the day, and the quality of the presentations is excellent (though I expected that!) and it is always great to see people.Some pictures....

So you want to store URLs in MySQL, and the URLs have those annoying "%20%27%7C%26%5E%2B%2D%25" symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:%20 - space%27 - '%7C - |%26 - &%5E - ^%2B - +%2D - -%25 - %So, how about we do some search'n'replace on that?mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';Query OK, 0 rows affected (0.00 sec)mysql> SELECT @url as original, -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> @test,'%20',' '), -> '%27','\\''), -> '%7C','|'), -- REPLACE() is case sensitive -> '%7c','|'), -- so we have -> '%26','&'), -> '%5E','^'),  []

Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from "make test" showed, was that certain symbols was missing:# Tried to use 'DBD::mysql'.# Error: Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefixFair enough, this is related to a 64-bit issue with MySQL---at least with my Perl version, which is now: Summary of my perl5 (revision 5 version 10 subversion 0) configuration: Platform: osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2levelIf you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from  []

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.Database administrators of other DBMS may be familiar with the concept of a "redo" log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as "dirty", and eventually get flushed and written to disk.If MySQL crashes, t

Over the weekend, I worked on a client's two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:a) how this never should have happened in the first place.b) how "slave drift" could be kept from happening.c) how this is probably keeping some businesses from using MySQL.d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.I'll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line "CHANGE MASTER" command to set up a new slave. Even so, it makes sense.It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log  []