Don’t know how many of you actually have heard of, or use, Mugshot, but I just started playing with it after a long hiatus, and decided that it’s pretty cool. Something the MySQL community will probably enjoy being part of (currently, to take full advantage, you want to be a Linux or Windows XP user).

By virtue of looking for the next new community hangouts, I figure we create a MySQL Mugshot Group. And before folk wonder what Mugshot’s all about, I suggest reading the feature list. Keep in mind that Mugshot is completely open source, and its a very live social experience, in this “notification era”. Its a whole lot of fun, and from what I can tell, the signups are now open to the public so what’s keeping you?

The site has i386 …

mysql> select * from zeroconf;
+------------------+-----------+------+
| NAME | HOSTNAME | PORT |
+------------------+-----------+------+
| crawler | purgatory.local | 3308 |
| slave | zim.local | 3306 |
| master | hell.local | 3306 |
+------------------+-----------+------+
1 row in set (0.00 sec)


Not really fit yet for use... probably a bunch of memory leaks. I need to rework it, but the basics work.

And what is it?

It is a plugin for MySQL that has two pieces, one an information schema, and another which sets up a daemon thread to publish the server via zeroconf (via the Avahi library). The information schema just allows one server to see what others are available on the network.

What does this mean?

A few ideas:
All tools now can just "find" the local available servers.
Foundation for slaves …

From the keynote presentation this past Tuesday:
http://krow.net/talks/ScalingVancouver2007.pdf

I have an audio recording of the talk as well but it is in poor shape. If I can clean it up I will post it as well.

It's a tiny company. I think the revenues from MySQL are between $30 million and $40 million. Oracle's revenue next year is $15 billion --Larry Ellison, 2006

When Larry Ellison starts disparaging MySQL, you know that it is now a serious contender.

The data warehouse processes, whether transforming data, loading data into the DW, or querying data from the DW, operate on large sets of data. A large set of data, for example, is all the sales transactions for a particular product that happen in a store in a day. Compare this to a sales transaction that represents the checkout basket of one customer, a relatively small set of data. Now, SQL is a language that deals very efficiently with sets of data, at least conceptually, whether large or small. Some data warehouses are indeed constructed primarily with SQL along with some scripting language, such as Perl.

MySQL is …

When I heard the JavaPosse interview about Groovy I became really interested in it. The fact that it offers a scripting language based on the JVM and that it can be seamlessly integrated with Java fascinated me.

So I decided to buy the Groovy in Action book and try it out.

This is my first attempt at something that might become useful later:

def foundtypes = [:]
def sum = 0              
def stats = ""

bugs = new XmlSlurper().parse(new File("/home/ds/findbugs.xml"));

bugs.BugInstance.findAll { instance -> 
    instance.-AT-category =~ 'CORRECTNESS|BAD_PRACTICE|MT_CORRECTNESS'
}.each { instance ->
 type = instance.@type-DOT-toString()
 foundtypes[type] = foundtypes.get(type,0) + 1
}
foundtypes.each { sum += it.value }

println "Found ${foundtypes.size()} …

When I heard the JavaPosse interview about Groovy I became really interested in it. The fact that it offers a scripting language based on the JVM and that it can be seamlessly integrated with Java fascinated me.

So I decided to buy the Groovy in Action book and try it out.

This is my first attempt at something that might become useful later:

def foundtypes = [:]
def sum = 0              
def stats = ""

bugs = new XmlSlurper().parse(new File("/home/ds/findbugs.xml"));

bugs.BugInstance.findAll { instance -> 
    instance.-AT-category =~ 'CORRECTNESS|BAD_PRACTICE|MT_CORRECTNESS'
}.each { instance ->
 type = instance.@type-DOT-toString()
 foundtypes[type] = foundtypes.get(type,0) + 1
}
foundtypes.each { sum += it.value }

println "Found ${foundtypes.size()} …

The 32nd edition of Log Buffer, the weekly review of database blogs, has been published by Lewis Cunningham on An Expert’s Guide to Oracle Technology. Over to you, Lewis — Log Buffer #32.

I've updated the Skeleton Engine. It is the example engine from inside of MySQL placed in a skeleton project with a complete setup for Automake done for you already (and an example plug.in file for compiling). I've added a flag I found that PBXT had added to the ac_mysql.m4 file and I have also incorporated the changes sent to me for adding Windows support. Several engines now make use of its initial framework and I am happy to take back examples for it:

http://hg.tangent.org/skeleton-mysql-engine

This week I gave a presentation at the PHP Vancouver conference. I showed off how to write a pluggable I_S (wrote it the span of about two hours). You can get the code from here:
http://download.tangent.org/vmstat_information_schema-0.1.tar.gz
…

One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction.

Consider we're running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and there are also bunch of fields which sorting can be performed on such as seller, price, date added etc.

Such configuration often causes serious challenge choosing proper index configuration as it is hard to add all combinations of restrictions and order by to be fully indexed.

An extra problem comes from the fact MySQL prefers when it is possible to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering for further restrictions. Here is example:

PLAIN TEXT SQL:

1. CREATE TABLE …

jan.kneschke.de is back online again. The old server got old and rusty and it has taken while to get everything setup again.

The most important articles are online again:

• Optimizing ORDER BY RAND()
• Groupwise MAX()

The old photo-storage is disabled, but a few new pics got uploaded to flickr, e.g. from my trip to Argentina:

• http://www.flickr.com/photos/jankneschke/sets/72157594494461199/