Lenz Grimmer's blog

I am happy to announce that mylvmbackup version 0.10 has been released.

You can download the updated package from the project home page or via the openSUSE Build Service.

This version fixes some bugs and includes new functionality:

• Applied patch from Marc Haber: added option --keep_snapshot that will skip the removal of the backup snapshot before terminating the script. Providing the option --backuptype=none will now skip creating a backup using the builtin backup modules. Both options provide more flexibility when using hooks for performing the actual backup tasks or when the snapshot is considered to be the actual backup.
• Added two new hooks: "backupsuccess" and "backupfailure" which are called respectively upon success of failure of the backup operation (Bug #264089)
• Make sure that binaries are being found ($PATH may not include /sbin when called from cron), added missing entry for "lvs" to mylvmbackup.conf (Bug #255703)
• Updated documentation

Greetings from the University of Latvia in Riga, where the local activities related to Software Freedom Day 2008 are in full swing! We've just finished the introductionary talk "Software freedom in Latvia"  by Evijs Taube (LATA) and Leo Trukšāns (Linux Centre). Currently, Mark Callaghan from Google Inc. is talking about "Running a database when your business depends on it". We've more talks scheduled for the rest of the day and there also is an ongoing workshop about MySQL Performance tuning by Jay Pipes, which takes place in the University's Linux Lab. The full agenda is published on the MySQL Forge Wiki and I just published a first batch of pictures on my gallery. Enjoy!

This is more of an "behind the scenes" update and I hope that you won't see any (negative) changes on the PlanetMySQL front page or the RSS feeds: I just finished and commited the conversion of the backend script that performs the parsing and aggregation of feeds from requiring MagpieRSS to SimplePie.

This will provide better support for a wider range of feed types and should also fix a few quirks, e.g. that some postings (for example the one from Kevin Burton) only showed up as an "A" in the Planet's RSS feed. It hopefully also fixes a weirdness with time zones that some people were reporting, but this requires further investigation.

The size and complexity of the script was reduced significantly because of this change - SimplePie is a breeze to use in comparison to MagpieRSS and it's very well-documented. The developers provide some more reasons and a comparison on why you should also make this switch!

And in case you notice anything broken or weird on PlanetMySQL that might be related to the change, please let me know! This change was an important step for future improvements of the site.

In case you are processing and working with geospatial data on MySQL, you may be interested in the following UDF (plugin) for MySQL 5.1: Koji Okumura from Oki Labs Japan has ported two functions from PostGIS into a MySQL UDF:

• distance_sphere(point, point): Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points
• distance_spheroid(point, point, spheroid): Returns linear distance between two lat/lon points given a particular spheroid. Currently only implemented for points.

Since it's an UDF, it can be easily added to an already installed server. These two functions actually complement the improved precise GIS functions nicely (which provide a 2-dimensional DISTANCE() function).

You can download the UDF source tarball from here. Compiling it is pretty straightforward and requires a local copy of the MySQL 5.1 sources and the ususal build environment. For my testing, I used the 5.1 source tree that includes the additional precise geospatial functions. Below I just list the commands required to build and create the binary tarball and omit the lengthy output in between some of these commands:

$ bzr branch lp:~mysql/mysql-server/mysql-5.1-wl1326
$ cd mysql-5.1-wl1326
$ BUILD/compile-pentium-max
$ make bin-dist

You should now have a binary tarball mysql-5.1.26-rc-linux-i686.tar.gz that you can install or deploy by using the MySQL Sandbox, which I used for my further testing:

$ tar zxvf mysql-5.1.26-rc-linux-i686.tar.gz -C ~/opt/mysql
$ cd ~/opt/mysql
$ mv mysql-5.1.26-rc-linux-i686 5.1.26
$ make_sandbox 5.1.26
$ cd ~/sandboxes/msb_5_1_26
$ ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.26-rc Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {msandbox} ((none)) >

Success! Let's see if the new GIS functions work:

mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE (GEOMFROMTEXT('POINT(0 0)'),GEOMFROMTEXT('POINT(1000 1000)')) AS distance;
+-----------------+
| distance        |
+-----------------+
| 1414.2135623731 |
+-----------------+
1 row in set (0.00 sec)

Now let's compile and install the UDF that provides the new spherical distance functions. Download the sources and extract the tarball in the same directory in which you branched the MySQL server sources:

$ tar zxvf mysql-udf-distance_spheroid-1.0.tar.gz
$ cp distance_spheroid/*.{cc,h} mysql-5.1-wl1326/sql
$ cd !$
$ g++ -DMYSQL_SERVER -shared -o udf_distance_spheroid.so -I../regex -I../sql -I../include spatial.cc calc_distance_spheroid.cc udf_distance_spheroid.cc

This will build the shared object udf_distance_spheroid.so that you now have to copy into the plugins directory of your server:

$ install -D udf_distance_spheroid.so ~/opt/mysql/5.1.26/lib/mysql/plugin

Now we have to load the UDF and make the new functions known to the server. Get back into your sandbox or fire up the commandline client again:

mysql [localhost] {msandbox} ((none)) > CREATE FUNCTION distance_sphere RETURNS REAL SONAME "udf_distance_spheroid.so";
mysql [localhost] {msandbox} ((none)) > CREATE FUNCTION distance_spheroid RETURNS REAL SONAME "udf_distance_spheroid.so";

Now you're ready to experiment with these new functions! For my test case, I calculated the distance between Hamburg, Germany (where I currently live) and Heidelberg, Germany (where I grew up) by providing the Lat/Lon coordinates as points:

mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE_SPHERE(GEOMFROMTEXT('POINT(53.583333 9.983333)'),GEOMFROMTEXT('POINT(49.412222 8.71)')) AS 'Distance (m)';
+-------------------+
| Distance (m)      |
+-------------------+
| 479037.4799112912 |
+-------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE_SPHEROID(GEOMFROMTEXT('POINT(53.583333 9.983333)'),GEOMFROMTEXT('POINT(49.412222 8.71)'),'SPHEROID["GRS_1980",6378137,298.257222101]') AS 'Distance (m)';
+-------------------+
| Distance (m)      |
+-------------------+
| 479345.1884839106 |
+-------------------+
1 row in set (0.00 sec)

Interestingly, using the virtual ruler on Google Earth gave me a distance of 471881.44 meters for the same coordinates. Not sure where the difference comes from, I assume they are using a slighly different projection system? It would be interesting to see, if these results match what you would get out of PostGIS using the same queries.

In any case, these two functions may come in handy, if your application needs to calculate distances between two points on a map. So give it a try! If you have tested this functionality and want to give feedback to Koji, please reply to his post on the MySQL GIS Forum.

As I mentioned some time ago, Software Freedom Day 08 will take place on Saturday, 20th of September 2008.

Coincidentally, the a large number of Sun/MySQL Engineers and other Sun folks will be in Riga, Latvia for an internal developer meeting around this day. To make use of this opportunity, we plan to give a number of sessions and presentations (in english) about various topics and to contribute to this global celebration of Open Source Software.

We've set up a Team Page on the Software Freedom Day web site for this event - the venue will be the Cafeteria Conference room in the basement of the University of Latvia, Riga, which can accomodate 60-80 people:

Raiņa bulvāris 19
Rīga, LV-1586

There is no entrance fee and you don't have to register - just come by and meet with us! There will be free coffee, refreshments and cake during the breaks.

In the evening, Sun will host a social event (incl. free drinks and food) in the SAS Radisson Daugava hotel, starting at 19:30:

Radisson SAS Daugava Hotel
Kugu 24, Rīga, LV-1048, Latvia
Tel.:+371 6706 1147; Fax: +371 6706 1101

We've set up a tentative schedule (45 minutes per session plus 15 minutes of Q&A), please check the Wiki for eventual last-minute changes!

11:00-12:00: MySQL/Open Source in Latvia (Evijs Taube, Sun Microsystems)
12:00-13:00: Open Source Business Models: how to build a business around free software (Speaker TBD)
13:00-13:30: Lunch Break / Ask the Guru your tech questions
13:30-14:30: MySQL in the Enterprise: Customer references, commercial offerings (Rob Young/Robin Schumacher, Sun Microsystems)
15:00-16:00: MySQL Community Overview: How to engage and contribute (Giuseppe Maxia/Jay Pipes/Lenz Grimmer, Sun Microsystems)
16:15-17:15: MySQL Performance tuning best practices (Jay Pipes, Sun Microsystems)
17:15-18:15: Maintaining your Open source project with Bazaar and Launchpad (Lenz Grimmer/Giuseppe Maxia, Sun Microsystems)
19.30: Social event: Software demonstration, buffet and free beer in the SAS Radisson Daugava hotel

We'd like to thank Leo Trukšāns, Michael Dexter and Georg Richter for their help and support in getting this event arranged and organized! I look forward to being there and help to spread the word about the stuff that keeps me occupied for more than 13 years now

While Colin beat me in blogging about Project Kenai, I think I can still provide some additional background information about this new project hosting service from Sun.

If you are a maintainer of an Open Source project, you currently have plenty of choice when it comes to getting your project hosted for free. One criterion could be your software configuration management system (SCM) of choice.

Some of the hosting services that I am currently aware of and the choice of SCM they offer include:

• BitBucket (Mercurial)
• Collab.net (Subversion)
• github (Git)
• GoogleCode (Subversion)
• Launchpad (Bazaar)
• Sourceforge.net (CVS, Subversion)

As disclosed by Tim Bray some days ago, there now is another option - Kenai is open for project hosting (currently by invitation only)! In his blog post, he interviews Nick Sieger, one of the developers behind this project about their motivation and intentions:

We need to demonstrate credibility in building on top of more traditional LAMP/SAMP web stacks (not just Java EE); and we need to show viability of Sun technologies and hardware for next-generation web applications.

In a nutshell, Kenai is a platform for:

• Developer collaboration
• Communities of connected developers
• Integrated collaboration services stack

Some of the features that are currently available include:

• SCM services using Subversion and Mercurial
• Bug Tracking (Bugzilla)
• Forums
• Wikis
• Mailing Lists (using Sympa)

Reading the interview with Nick and looking at some presentations slides for RailsConf from Fernando Castano (a jRuby and Database performance engineer at Sun and another member of the project team),  I was able to gather a list of the tools and technologies they used to build Kenai:

• Apache2, mod_proxy_balancer
• Ruby on Rails (running on jRuby)
• Glassfish Java application server
• MySQL 5.0
• Apache Solr for search
• memcached

I found it interesting that they decided to deploy and run the Rails application as a war file within the Glassfish application server (using Warbler). By the way, the fabolous OpenSUSE Build Service is a Rails application, too! So far, the entire site is powered by a single MySQL instance with query cache enabled.

The project is hosted on the following infrastructure:

• Sun Fire T2000 for (for web and application serving)
• Sun Fire X4500 for Storage
• OpenSolaris Nevada (Zones/Containers, SMF)
• ZFS (9.7 TB RAIDz, Snapshots, NFS)
• Coolstack & Blastwave packages

You should check out Fernando's presentation for more technical details, tuning info and how they benchmarked the setup - it contains a number of useful tuning hints and performance graphs.

Last time I checked, 27 Projects have joined so far (e.g. jRuby, xVM Server). Kenai itself is developed on Kenai. It's going to be interesting what other projects will find their home there.

Nick also talked a bit about their future near term plans: to improve the usability and feature set, incrementally improve the site navigation and layout and adding support for hosting files/release downloads. They also consider offering Jira as an option to Bugzilla for bug tracking and Git as another SCM option.

There is an IRC channel #projectkenai on freenode.net, to get in touch with the developers directly. The mailing list for the Project Kenai site itself, is users@help.kenai.com - you can subscribe to this list here.

Tomorrow (Thursday, 11th of September) at 9:00 PST/16:00 UTC/17:00 GMT/18:00 CET, there will be an new free MySQL University Session. MySQL University started as an internal training program for MySQL engineers, to share and spread knowledge about their areas of expertise and has been available to the public for quite some time now. It covers a wide range of technical topics around the MySQL Server and usually takes place once per week.

For the first time, the presentation will not be performed by (former) MySQL employees/developers, but by two of our "Sun Classic" colleagues: Jyri Virkki (OpenSolaris Web Stack community lead) and Murthy Chintalapati (Sr Engineering Manager, Web Stack development) will talk about the OpenSolaris Web Stack:

OpenSolaris Web Stack is an OpenSolaris project and community building an integrated stack of popular open source web tier infrastructure technologies such as Apache HTTP server, MySQL, memcached, PHP and Ruby On Rails optimized for Solaris platform. This session introduces OpenSolaris Web Stack, its status and future development including addition of newer technologies such as lighttpd, Varnish etc., as well as the ease of use features for developers and deployers. We will also be discussing an experimental web stack IPS package repository and it could be leveraged to build and make available popular end user applications such as Drupal.

MySQL University sessions are free to attend - all you need is an IRC client (to post your questions and comments) and an audio player capable of playing back an OGG audio stream, so you can listen to what is being said. See the Instructions for Attendees on the MySQL University pages for more information on how to log in and attend. The audio stream will be recorded and published on the MySQL University pages for later consumption, in case you can't make it or want to listen to a previous session.

One of the sessions at DrupalCon I attended was Larry Garfield's talk about "Drupal Databases: The Next Generation", which gave me a good insight into the current state of the Drupal database layer and how they plan to overhaul it for Drupal 7. The key points that I took away:

• A new API based on PDO
• Object-oriented, requiring PHP5
• Support for using prepared statements
• A unified access API
• A query builder
• More support for other database systems (currently Drupal supports MySQL and PostgreSQL only). In particular, they are keen on adding SQLite support, to ease local development.
• Support for master-slave replication (by randomly distributing reads among the hosts)
• Support for using different database types in parallel (e.g. using SQLite for read-only tables, MySQL for everything else)

The slides and a video of the presentation are available, if you want to check it out. There is a task list on the Drupal.org web site that keeps track of the ongoing activities.

The MySQL 5.1 Use Case Competition is in full swing - we've already received a number of cool and interesting submissions, which we will turn into articles that will be published on the MySQL Developer Zone over the course of the coming weeks. Today we received a note from Jakub Vrána from the Prague, Czech republic. He's the author of phpMinAdmin, a MySQL management tool written in PHP. Here's what he wrote:

In the beginning of September 2008, I have implemented MySQL 5.1 Events to the database management tool phpMinAdmin. I've used the Windows version of MySQL 5.1.26 for the development.

As phpMinAdmin tries to be full-featured, yet compact management tool, I have implemented Events to allow users of MySQL 5.1 manage it.

The implementation was quite straightforward, it took only 4.5 kB of PHP code. Events management is well described in MySQL documentation and easy to understand.

During the development, I've reported three bugs: Bug#39163, Bug#39165, Bug#39173. I have been positively surprised by the speed of reaction to these reports

Thank you very much for your submission and the support, Jakub! We appreciate it.

If you're reading this and are using MySQL 5.1 and any of it's new features: have you considered telling your story yet? You may even win something when doing so!

I had a nice chat with Kieran from Acquia at DrupalCon last week - we discussed how people running local Drupal user groups could expand their outreach into other communities, in particular into the MySQL User Groups. Scott Mattoon captured our conversation on video, which is now available on blip.tv:

Click To Play

The gist of what we talked about: if you are organizing a local Drupal User Group Meetup, check out http://mysql.meetup.com to find out if there is a local MySQL user group nearby. Chances are high that there is! And if not, you may find at least people in the area that would be interested in meeting about this subject. We also maintain list of user groups on the MySQL Forge Wiki. Consider extending your invitation for your next meetup to these folks as well! It's very likely that someone would be interested to learn more about Drupal. The same applies to other user groups, e.g. from the PHP community.

I personally run a MySQL User Group here in Hamburg, and I usually extend my invitations to a number of channels and mailinglists, including the local PHP, Perl and Linux User Groups. Every once in a while, a new member from these communities shows up.

So this thing works the other way around, too: if you are the organizer of a MySQL Meetup, have you thought about looking at http://groups.drupal.org/ yet? Maybe you will find a Drupal User Group in your very own town that you could invite to learn more about MySQL and exchange contacts? If you are looking for more tips on how to run and expand your User Group, I've created a page with useful hints about this topic on the MySQL Forge Wiki. Your feedback and additions are very welcome!

I'm back home from DrupalCon 2008 now - it has been a great event! I met a lot of nice people from the Drupal Community and learned a lot about this CMS. I've been very busy in uploading the remaining pictures from the event to my gallery - so here's for your viewing pleasure:

• Impressions from Szeged
• Day One - 2008-08-27
• Day Two - 2008-08-28
• Day Three - 2008-08-29
• Day Four - 2008-08-30

I also gave two talks and held a BoF there - the slides have now been attached to the session nodes, one of them (the HA session) even includes a video recording:

• bzr - The Bazaar source revision control system
• MySQL Backup and Security - Best practices
• High availability solutions for MySQL: An Overview and practical demo (Video)

I've also uploaded some pictures from FrOSCon to my Gallery now, hope you enjoy them! The slides of my FrOSCon talks are now uploaded to the conference system as well:

• OpenSolaris für Linuxer
• bzr - The Bazaar source revision control system