PlanetMysql.ru — информация о СУБД MySQL

As pointed out by Schlomi Noah on my last blog, MySAR was already taken by a project related to Squid reports with MySQL. I decided then to look for a new name, and as I posted initially, I want to keep the sar prefix to describe the script’s purpose by association with the OS utility of the same name. I brainstormed many names. I liked Dave Edwards’s suggestion: SARkila, but it sounds too close to tequila, so I settled with Sheeri Cabral’s suggestion: sar-sql.

The title of the Launchpad page already reflects the change. What remains to be done is: a) change the name of the Perl script and documentation; and b) change the Launchpad URL. It is likely that I will change the name of the script when I release version 1.x (see below). I’m not sure of all the implications in Bazaar regarding the URL change, so that task will have to wait for now.

Now a little more info on the status of the project.

Bug Fixes

In the latest trunk there are two patches. One corresponds to Bug #455870, which should be fixed. I’m excited since this bug was posted by a user. (Yay!)

The 2nd patch refers to a bug that came up during an implementation in a client, in a master-slave configuration (in hindsight, I should have thought about it earlier). The snapshots from the master were being replicated to the slave, so when the script ran on the slave, the autoincrement values were in conflict and stopped replication. In the latest patch (build 16), I have added a column that records the server id, as in SHOW GLOBAL VARIABLES LIKE 'server_id' to distinguish the master and slave data. I like the fact that it is possible to query the master and slave snapshots side by side to diagnose slave lag. This change makes the last version in the trunk incompatible with the newer ones, which triggered the creation of the first README file.

I will be posting a new tarball soon.

Road Map

Believe or not, there are enough ideas in the queue to justify a road map. Here’s a summary list and you’re welcome to comment on it. I promise to review all the suggestions.

Code Review

As I mentioned above, the script currently works, but it’s far from optimal. So I’ll be changing the code base with two objectives. 1) Increase overall efficiency; and 2) facilitate future improvements. The more profound code changes will be implemented in version 0.x (current version). I believe that messy code leads to bugs, so the cleaner it is, the less likely I’ll break existing functionality while adding new one.

Command Line Syntax Changes

Adding new functionality implies that the script will perform more tasks which in turn will add overhead to each one of them. To minimize this overhead, I am working on a different command line syntax and the corresponding underlying code. This change will happen in version 1.x (the next version) since the code has to be cleaned up to be effective and works as it is. The command line syntax change will imply rewriting any wrapper script that might be in place to invoke in the crontab, so this will be the opportunity to rename the script as well.

Installer

The script is so simple that distributing a tarball is enough for now, but I’d like to have an installer and possibly a package that will take care of the Perl modules dependencies, schema initialization and user credentials verification / creation. I have no target version for this. I’ll shoot for 1.x, but it’s likely to go into 2.x.

Other

I’m still working on plenty of use cases, best practices, and companion scripts examples. In time, the example scripts and documentation will be part of the complete package and installed along side with the main utility. In the meantime, just follow the blogs and the links to them in the Launchpad announcements page.

Community Participation

Having a user file a bug, Schlomi’s bringing up the name issue, and Sheeri contributing with her sanity checks are a great help, but I invite all of you out there to participate through comments to my blogs, bug reports, questions in the Launchpad page, and replying to my tweets.

I will be participating in OpenSQL Camp in Portland, OR next month. I’m sure I will have an opportunity to review my ideas with the old friends I’ll be meeting there. Stay tuned.

PlanetMySQL Voting: Vote UP / Vote DOWN

This time of the year is usually a very busy one, as there are plenty of events and conferences to attend. Just take a look at our calendar of OSS events on the MySQL Forge to see what I mean! Here's a quick summary of the ones that I will attend and speak at until the end of this year:

On November 14-15, I'll attend the openSQL Camp in Portland (OR), USA. I missed the first one that took place in Charlottesville (VA) in 2008, but had a lot of fun organizing the European Edition earlier this year. The upcoming one will be more like an unconference again - the list of proposed sessions looks very interesting and the attendee list reads like a "who is who" list of the OSS database community.

On December 3-5, I'll be joining Giuseppe at SAPO Codebits in Lisbon, Portugal, which is going to be a very cool event: "3 days. 24 hours a day. 600 attendees. Talks. Workshops. Lots of food and beverages. 24 hour programming/hacking competition. Quizz Show. Rock Band Contest. Lots of gaming consoles. More food. More beverages. More coding. Sleeping areas. More fun. An unforgettable experience". I will be talking about my favorite topic of MySQL High Availability (I'm currently working on revising my slides based on several excellent discussions about MySQL HA that happened on Planet MySQL in the past weeks) and about the benefits (both social and technical) of using a distributed revision control system (DVCS) like bazaar, git or mercurial for your open source project.

Shortly after Codebits, I will attend SLAC 09, the "Secure Linux Administration Conference" in Berlin, Germany (December 10-11), where I will give two MySQL-related talks (in German) - my usual suspects, but in revised and extended form: MySQL High Availability solutions and MySQL Backup & Security best practices.

PlanetMySQL Voting: Vote UP / Vote DOWN

FOSDEM, the Free and Opensource Conference, will again take place in Brussels, Belgium on Saturday and Sunday (6th and 7th February, 2010). Now happening for the 10th time (congratulations!), it is one of the largest Open Source conferences in Europe, with a strong focus on developers. Sun/MySQL have been regular sponsors of and contributors to the event in the past and it is alway a great experience to be there. It's very rare to meet so many well-known and bright people from such a wide range of OSS projects.

They have now opened their Call for Participation - the organizers are seeking input on talks for the main conference tracks (deadline: 2009-11-22) , lightning talks (deadline 2009-12-28) and project stands (deadline: 2009-11-22).

As for the last conference, we plan to apply for a MySQL developer room (and maybe a project stand, anybody interested to join? Please contact me!). However, the web site currently states that they are "slightly reworking the concept of developer rooms", so it remains to be seen what this will turn into. In any case, we will set up our own call for papers, once the developer room allocations have been finalized (and we were lucky). I was initially thinking about running another OpenSQL Camp, but it's probably too short after the upcoming one in Portland...

If you have an interesting talk about a MySQL-related subject, consider submitting your proposal now! We will gladly review and comment on your proposal in advance, if you would like to get our input or need suggestions about topics! Thanks.

PlanetMySQL Voting: Vote UP / Vote DOWN

OpenSQL Camp Portland 2009 is coming up on the 14th and 15th of November. Eric Day (of the Drizzle project) is the lead organiser this time around.

I went to the first edition in Charlottesville VA last year which was organised by Baron Schwartz (Percona). It was a great event, like other unconferences but with specific focus on database technologies. Monty (MySQL), Brian (Drizzle), Richard (SQLite), Jim (Interbase/Firebird/Falcon), Bruce (PostgreSQL) were all these, as were various storage engine builders. Very interesting, and lots of informal fun. If you’re anywhere near, do go!

Even though noone from our gang is able to make it to this one, Open Query is sponsoring this event – for all the above reasons. It rocks and deserves every support.

PlanetMySQL Voting: Vote UP / Vote DOWN

The next OpenSQLCamp will be held in Portland, Oregon, USA. It is being organized by Eric Day, well known to the open source community for his active and productive participation to several projects (especially Drizzle and Gearman).

The event is public and free. Therefore, it needs public sponsoring. I don't know yet if I can attend, but I have already donated something to the organizers, and I am officially a sponsor. You can be one too. Simply go to the sponsors page and donate a minimum of $100 as in individual or $250 as an organization.
And of course, if you plan to participate, register yourselfand eventually propose a session.
OpenSQLCamp is a fun, equal level event. If you have something to say, write a proposal, and the other participants will tell you if they want to hear it or not. Either way, you will learn something.
PlanetMySQL Voting: Vote UP / Vote DOWN

It's almost two weeks now since FrOSCon and the OpenSQL Camp subconference have taken place in Sankt Augustin, Germany — about time for a summary and update from my side!

First off, I would like to thank all of the participants and supporters, particularly my colleagues Regina Steyer and Iris Musiol for the perfect logistics and co-sponsoring as well as Uli Graef, Thorsten Frueauf, Matthias Schmidt, Alexander Rubin and Joerg Moellenkamp for manning the Sun booth and the help on site.

Another big Thank You goes out to my team mates Giuseppe and Colin as well as to Sheeri K. Cabral, who were a big help in keeping the OpenSQL Camp on track and by supporting the event by giving talks. In addition to that, Sheeri recorded most of the OpenSQL Camp sessions on video and published them in record time!

So here's a quick summary of both events from my side, starting with the main conference.

Sun was a Gold sponsor of the event and we had a booth right at the main entrance area; it could hardly be missed. It consisted of two large and two small desks as well as a divider behind them. For demos, we had a (slightly noisy) Sun Fire X2200 M2 Server and four SunRay 2 Thin Clients (which by themselves triggered a lot of questions and curiosity by many visitors). The booth was flanked by rollup-banners on both sides as well as various posters attached to the divider. Here's a picture of our booth before the event opened:



We demoed Open Solaris, Open HA Cluster, NetBeans/Java and MySQL. We also had a lot of brochures about various products, OpenSolaris 2009.06 Live-CDs incl. booklets as well as some MySQL-T-Shirts to hand out. We distributed over 300 CDs and received a lot of positive feedback about the distribution.

We also had a number of talks in the main conference track (both German and English):

• Lenz Grimmer: Working for a Virtual Company - How we do it at MySQL (OGG Video)
• Giuseppe Maxia: MySQL Sandbox 3 - Making your life with databases easier than ever (OGG Video)
• Ulrich Graef: ZFS - Neue Funktionen und Technologien
• Thorsten Früauf: Hochverfügbarkeit mit minimalem Cluster - Open HA Cluster on OpenSolaris (OGG Video)
• Jörg Möllenkamp: Insights to Opensolaris (OGG Video)

The comments and ratings of these sessions were generally very positive. Our booth was well attended, especially during the session breaks. In total, there were over 1.400 visitors at the conference over the two days.

I personally did not attend many sessions in the main conference tracks, as I was too occupied with the OpenSQL Camp and the booth organization. However, I managed to listen to Uli Graef's talk, which was a very technical and interesting session about ZFS features and internals. Being a big fan of ZFS myself, this was a very worthwhile session to be at and my impression was that it encouraged others to take a closer look at this truly amazing file system.

The second talk I attended was Sunday's keynote by Dries Buytaert from the Drupal project about "The Secrets of Building and Participating in Open Source
Communities". Dries is a great speaker with visually stunning slides. He is funny, too — if you have a moment, you should watch the video recording of his keynote. An uncut "pre-release" version of his talk is already available as an OGG Video file.

As for previous FrOSCons (is that the proper plural?), there was a social event scheduled for Saturday evening, providing barbecue (Steaks and Sausages as well as vegetarian dishes) and drinks. This event usually takes place outside and is always an excellent opportunity for networking and talking with key people from other OSS communities and projects. And there was plenty of time for talking - the queues for the grilled food were long...

Here is a list of other blogs and articles about FrOSCon that are worth a read (in no particular order and both German and English):

• http://www.c0t0d0s0.org/archives/5878-FrOSCon-2009-first-day.html
• http://www.c0t0d0s0.org/archives/5879-FrOSCon-09-Nachlese.html
• http://blogs.sun.com/TF/entry/hochverf%C3%BCgbarkeit_mit_minimalem_cluster
• http://robilad.livejournal.com/53194.html
• http://blogs.sun.com/bblfish/entry/froscon_the_free_and_open
• http://www.linux-magazin.de/content/view/full/43032
• http://community.oreilly.de/blog/2009/08/25/das-war-die-froscon-2009/
• http://www.naturalnik.de/wordpress/2009/08/froscon-2009/
• http://www.alexander-stelter.de/blog/archives/361-FrOSCon-2009-Review-+-Bilder.html

OpenSQL Camp, European Edition

In addition to the main conference tracks, FrOSCon also provided a number of so-called "Developer Rooms" to OSS projects, so that they could organize sub-conferences or hackfests of their own. We applied for a room to set up a conference dubbed "OpenSQL Camp", related to the topic of Open Source databases, which was approved.

We then sent out a call for papers and invited people from the many OSS database communities to join us and talk about their projects. Every session proposal was published on the OpenSQL Camp web site and people were able to vote on the sessions they were most interested in via email or twitter:

• http://opensqlcamp.org/Events/2009/Proposed_Sessions
• http://opensqlcamp.org/Events/2009/Reviewing_and_Voting

The organization and scheduling of the talks and speakers was done via the FrOSCon conference system (Pentabarf), which made it very easy to perform this task and also made sure that the OpenSQL Camp sessions were included in the main conference program. Below is a full list of sessions at our subconference (see the FrOSCon Program page for abstracts, speaker info, links and slides). We had two cancellations by speakers on short notice, but were able to cover the gaps with ad-hoc presentations. I'd like to send a special thanks to Geert Vanderkelen, who gave a great presentation about MySQL Cluster despite the very short notice and some technical difficulties at the beginning!

• Sheeri K. Cabral: A Better mysqltuner (Video)
• Linas Virbalas: Bringing Master/Slave into the 21st Century using Tungsten Database Clustering (Video)
• Holger Klemt: Firebird - a really free database used in free and commercial projects (Video)
• Seppo Jaakola: Galera Replication (Video)
• Kristian Waagan: Getting acquainted with Apache Derby (Video)
• Stephane Combaudon: Minimizing data access with covering indexes
• Lenz Grimmer: MySQL High Availability Solutions (Video)
• Jan Kneschke: MySQL Proxy: a MySQL toolbox (Video)
• Felix Schupp: New kid on the block: The BlackRay Data Engine (Video)
• Vladimir Kolesnikov: PBXT: Technology trends that affect your database (Video)
• Panel Discussion: The OSS Toolshed Shootout (Video)
• Darren Cassar: Securich - MySQL user administration and security made easy!
• Giuseppe Maxia: Sharding for the masses (Video)
• Geert Vanderkelen: An introduction to MySQL Cluster

Most talks attracted between 20-50 attendees and we had a great mix of topics from several different database projects (with a slight majority of MySQL-related talks). The Panel Discussion (moderated by me), called the "OSS Toolshed Shootout" went quite well and the speakers had a good time answering questions on various topics about their projects. Thanks again to all OpenSQL Camp speakers for making this event a success!

All in all I think that both FrOSCon and OpenSQL Camp were well worth supporting and attending - we were able to provide insight and trigger some interesting discussions among the OSS enthusiasts and developers in the audience. It was also a good opportunity in get in touch with many people of other OSS communities, fostering the MySQL (and other Sun OSS projects) ecosystem.

Here is a Flickr slide show of my own pictures - more photos can be found in the FrOSCon Gallery and the links page on the Wiki.

I personally look forward to next year's FrOSCon - a Big Thanks to the organizers for another great event!

PlanetMySQL Voting: Vote UP / Vote DOWN

I got to meet, and share a meal with a most interesting Darren Cassar at FRoSCon/OpenSQLCamp, who’s the mastermind behind SecuRich. Some sparse notes, while we await his slides. I think there’s some great potential here, and SecuRich is exciting and should be given some more love.

Designed to work with Sybase and MySQL in mind (because he’s hacking on migrating Sybase to MySQL).

How often do we audit user privileges and access levels? How often do we forget temporary elevated privileges?

What you have in MySQL today: Authentication against ‘username’@'hostname’, and the password is hashed by PASSWORD() function. There is wide range of privileges, and the granting of privileges is controlled.

What are limitations in MySQL today: Password limits are not available (password size limit, password history, password complexity meter, password minimum age), its quite complex to manage, there are no roles, it is easily unsecured (if you provide an access to the MySQL database, you can try brute force attacks, etc.). Once you drop the database, the grants are still there – obsolete grants are not removed.

SECURICH has password limits, reduces complexity to manage, has roles, is a lot more secured, and soon, there will be removal of obsolete grants.

Compatible with MySQL 5.0 and later, as it uses INFORMATION_SCHEMA extensively. It requires I_S.processlist, which is only available in MySQL 5.1 though.

I don’t see why this wouldn’t work on Windows, besides some scripts written in BASH. My only experience with this is however on Linux and OSX.

PlanetMySQL Voting: Vote UP / Vote DOWN

This is the Sharding for the masses: Introducing the SPIDER storage engine by Giuseppe Maxia, given at OpenSQLCamp, at FrOSCon, in August 2009. These are somewhat live notes, and the slides are available too.

Sharding for the masses

View more documents from Giuseppe Maxia.

Why sharding? Scaling, of course. The MySQL way to solve this, is replication (even Yahoo! and Google use this).

When the master doesn’t have enough resources to cope with what you do (i.e. large data sets), replication chokes.

You can use proxies for sharding. There exists MySQL Proxy (can be programmed using a scripting language – Lua), HSCALE (built on top of MySQL Proxy), SpockProxy (a fork of MySQL Proxy, without LUA scripting, specialised for sharding), in the market these days. This however, is the single point of failure – everything has to pass through one proxy.

Enter SPIDER – a MySQL storage engine, built on top of the partitions engine. It associates a partition with a remote server, and is transparent to the user. Its developed by Kentoku Shiba.

Installation: Get 5.1.37 sources, then get the source code for Spider 1.0, and then get the patch for condition pushdown.

Why the condition pushdown patch? Remote server works less, by receiving the condition. The SPIDER engine without the condition pushdown patch is still fast, but it can be more than 10x faster with condition pushdowns.

http://dev.mysql.com/doc/refman/5.1/en/condition-pushdown-optimization.html (works with NDBCLUSTER), http://dev.mysql.com/doc/refman/5.4/en/condition-pushdown-optimization.html (works with MyISAM). The patch by Kentoku, will add cond_push and cond_pop, to ha_partition – so now, every storage engine that uses table partitioning can get condition pushdown through ha_partition.

You need to setup the engine first: http://datacharmer.org/downloads/spider_setup.sql (the SQL is also available in the DOCS).

spider_remote_employees.sql – use this in conjunction with http://launchpad.net/test-db/ – a good example of how to use the SPIDER storage engine.

PlanetMySQL Voting: Vote UP / Vote DOWN

In record time, less than a week after the conference (thanks to the free Pinnacle Video Spin and YouTube), all 11 videos that were taken at OpenSQLCamp Europe are online.

For those who missed the sessions, or just want to relive the fun!

Almost all the sessions were filmed; regrettably Darren Cesar’s Securich - MySQL user administration and security made easy! and Stephane Combaudon’s Minimizing data access with covering indexes were not.

The YouTube videos have the descriptions and resources from the official conference pages, and links to pages. If there is more information to add (for example, the slides from a talk are now online), or if you spot an error, please feel free to add a comment on the YouTube video, or as a comment to this blog post.

• YouTube Playlist for all 11 OpenSQLCamp videos

Individual presentations:

• Spider: Sharding for the Masses - Giuseppe Maxia
• A Better mysqltuner - Sheeri Cabral (my session!)
• Getting acquainted with Apache Derby - Kristian Wagaan
• New kid on the block: The BlackRay Data Engine — Felix Schupp
• MySQL High Availability Solutions — Lenz Grimmer
• Bringing Master/Slave into the 21st Century using Tungsten Database Clustering — Linas Virbalas
• PBXT: Technology trends that affect your database — Vladimir Kolesnikov
• MySQL Proxy: a MySQL toolbox - Architecture and concepts of misuse — Jan Kneschke
• Galera Replication, multi-master synchronous replication for MySQL — Seppo Jaakola
• Panel Discussion: The OSS Toolshed Shootout Speakers and technologies: Sheeri K. Cabral - MySQL; Holger Klemt - Firebird; Felix Schupp - BlackRay; Kristian Waagan - Apache Derby (JavaDB); Lenz Grimmer - moderator; Kore Nordmann - CouchDB; Andreas ‘ads’ Scherbaum - PostgreSQL (this was a fun panel to be on)
• Firebird - a really free database used in free and commercial projects — Holger Klemt

Enjoy!

PlanetMySQL Voting: Vote UP / Vote DOWN

FrOSCon and the OpenSQLCamp are about to start. I am packing for Sankt Augustin, where I will attend the fourth edition of FrOSCon and the second OpenSQLCamp. I will have two sessions, Sharding for the masses, about the Spider storage engine and MySQL Sandbox 3, about one of my favorite tools.

The program is very rich. There will be several tracks in the main event and in the associated conferences. If you have any involvement or simply some curiority in open source matters, You will find something interesting at FrOSCon.
PlanetMySQL Voting: Vote UP / Vote DOWN