a:4:{s:5:"child";a:1:{s:0:"";a:1:{s:3:"rss";a:1:{i:0;a:6:{s:4:"data";s:3:" ";s:7:"attribs";a:1:{s:0:"";a:1:{s:7:"version";s:3:"2.0";}}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";s:5:"child";a:1:{s:0:"";a:1:{s:7:"channel";a:1:{i:0;a:6:{s:4:"data";s:217:" ";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";s:5:"child";a:1:{s:0:"";a:6:{s:5:"title";a:1:{i:0;a:5:{s:4:"data";s:12:"Planet MySQL";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"link";a:1:{i:0;a:5:{s:4:"data";s:27:"http://www.planetmysql.org/";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:7:"pubDate";a:1:{i:0;a:5:{s:4:"data";s:31:"Thu, 15 Dec 2011 12:00:01 +0000";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:8:"language";a:1:{i:0;a:5:{s:4:"data";s:2:"en";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:11:"description";a:1:{i:0;a:5:{s:4:"data";s:42:"Planet MySQL - http://www.planetmysql.org/";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"item";a:50:{i:0;a:6:{s:4:"data";s:53:" ";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";s:5:"child";a:3:{s:0:"";a:6:{s:5:"title";a:1:{i:0;a:5:{s:4:"data";s:45:"MySQL Cluster 7.1.18 is available to download";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"guid";a:1:{i:0;a:5:{s:4:"data";s:32:"http://www.clusterdb.com/?p=2178";s:7:"attribs";a:1:{s:0:"";a:1:{s:11:"isPermaLink";s:5:"false";}}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"link";a:1:{i:0;a:5:{s:4:"data";s:182:"http://www.clusterdb.com/mysql-cluster/mysql-cluster-7-1-18-is-available-to-download/?utm_source=rss&utm_medium=rss&utm_campaign=mysql-cluster-7-1-18-is-available-to-download";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:11:"description";a:1:{i:0;a:5:{s:4:"data";s:331:" The binary version for MySQL Cluster 7.1.18 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version) A description of all of the changes (fixes) that have gone into MySQL Cluster 7.1.18 (compared to 7.1.17) will appear in the 7.1.18 Change log soon.";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:7:"pubDate";a:1:{i:0;a:5:{s:4:"data";s:31:"Thu, 15 Dec 2011 10:32:55 +0000";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:8:"category";a:3:{i:0;a:5:{s:4:"data";s:13:"MySQL Cluster";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:1;a:5:{s:4:"data";s:5:"MySQL";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:2;a:5:{s:4:"data";s:17:"MySQL Cluster 7.1";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}}s:40:"http://purl.org/rss/1.0/modules/content/";a:1:{s:7:"encoded";a:1:{i:0;a:5:{s:4:"data";s:1106:"

The binary version for MySQL Cluster 7.1.18 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version)

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.1.18 (compared to 7.1.17) will appear in the 7.1.18 Change log soon.

Normally I write for the MySQL DBA audience. But this blog entry is different. Print out a hard copy and hand it to friends and family when they ask you what sort of presents you want for Christmas, Hanukkah, Festivus or other end of the year gift exchange instead of grunting ‘I do not know¹‘ and ending up with something you will have to pretend to like.

Yes, your MySQL DBA is hard to shop for during the holiday season. There needs are few² or expensive. So what can you get them on the cheap that will make them happy? Jack;s First Christmas

Gift Ideas

T-shirts are probably the majority of your DBA’s attire, despite your best efforts.
1. Zazzle offers a World’s Best DBA t-shirt. You may also be able to get away with remark that the Nobel Prize committee had to cut costs due to the economy being what it is and this is what they sent.
  Zazzle also offers I am a DBA in third normal form , SELECT FROM friends WHERE lame = ‘no’, and a SELECT SUM(WOOD_QTY) FROM WOOD_CHUCK WHERE CHUCK = TRUE t-shirts feature snarky DBA related humor. Do not ask for an explanation from your DBA on the humor³.
2. Printfection offers Have you ____ your MySQl DBA today?
3. I am the DBA not the help desk, DBAs do it on tables, and Bars, scars and varchars — the life of a DBA are available at Cafepress
4. There’s no place like 127.0.0.01 can be purchased at Thinkgeek
5. And Amazon sells ROSES ARE #FF0000, VIOLETS ARE #0000FF ALL MY BASE BELONG TO YOU.
Books — Very few new MySQl Books this year. The most notable is Effective MySQL Optimizing SQL Statements by Ronald Bradford. In 2012 we will see many more new titles. So buy a gift card to your DBA’s favorite book seller so they can get:
1. A new edition of High Performance MySQL: Optimization, Backups, Replication, and More will be a must buy. The first edition is a classic.
2. Oracle press will be publishing MySQL Workbench Data Modeling and Development, Effective MySQL Advanced Replication Techniques, and Effective MySQL Backup and Recovery.
3. Mastering phpMyAdmin 3.4 for Effective MySQL Management is due out May 1st, 2012 from Packt.
You can also go non-DBA specific for presents such as movie passes, gift cards, and other generic gifts. But the above are your best choices for your DBA this time of year. And if you have special DBA gifts, please share them here!!

Notes:
1. No, you are not going to get a Large Hadron Collider if you ask. There is only one and in a year there will be a fully functioning Lego kit or iPhone app that will prove all the Higgs bosons particles the world needs — and it will not be that many.
2. Maslow’s hierarchy of needs shows MySQL DBA are already at the top of the needs pyramid just by being MySQL DBAs.
3. Unless you really need a nap!

I've been spending some time lately familiarizing myself with EC2, setting up some MySQL servers & clusters here and there, and doing some really basic configuration testing. One situation you'll run into when interacting with EC2 is that it gets unwieldy to use the AWS Management Console web interface for interacting with your instances. There ends up being lots of scrolling, lots of staring, and lots of sighs. Since I'm using SSH to connect to and interact with my instances, I want a reasonable way to find information about them on the Unix command line.

Amazon has an official set of tools [http://aws.amazon.com/developertools/351] that give you this information , at least theoretically. It is some gigantic distribution of shell scripts and Java madness that, if you are very patient, will eventually give you some information about your instances, in a format that is very difficult to work with.

$ time ./bin/ec2-describe-instances i-83c5d4e0
Unable to find a $JAVA_HOME at "/usr", continuing with system-provided Java...
RESERVATION     r-7db4731c      801025846226
INSTANCE        i-83c5d4e0      ami-31814f58                    stopped skysql-ec2      0               m1.small        2011-12-09T20:41:39+0000        us-east-1c    aki-805ea7e9                    monitoring-disabled             10.0.0.164      vpc-cd4fafa5    subnet-c44fafac ebs                                  paravirtual      xen             sg-134b547f     default
BLOCKDEVICE     /dev/sda1       vol-19ec6174    2011-12-10T01:30:32.000Z
TAG     instance        i-83c5d4e0      Name    ndb32-02

real    0m7.693s
user    0m10.119s
sys     0m0.451s

OK, it takes me about 7.5 seconds to get data about an instance, and it's given to me in 4 lines. If I get information about all of my data, I have no idea how I would be able to successfully grep through that to interact with any of it programatically. I went looking for a different solution, preferably one that would be faster, more flexible, and easier to use.

I found a great script called, simply, aws, written by Timothy Kay [http://timkay.com/aws/].

$ du -hsc ec2-api-tools*
 14M    ec2-api-tools-1.5.0.1-2011.11.30
 11M    ec2-api-tools.zip
 26M    total

$ ls -sk aws 
 76 aws

Ahem. I'll take a 76K perl script over a 14M mess any day. Let's see how it performs.

$ time aws din i-83c5d4e0
+------------+--------------+----------------------+------------------------------------------+------------+--------------+--------------------------+---------------------------------------------+--------------+----------------+-----------------+--------------+------------------+-----------------+---------------------------------------------+-------------------------------------------------------------------------------------------------+--------------+----------------+----------------+------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------+------+----------+
| instanceId |   imageId    |    instanceState     |                  reason                  |  keyName   | instanceType |        launchTime        |                  placement                  |   kernelId   |   monitoring   |    subnetId     |    vpcId     | privateIpAddress | sourceDestCheck |                  groupSet                   |                                           stateReason                                           | architecture | rootDeviceType | rootDeviceName |                                                         blockDeviceMapping                                                         | virtualizationType | tagSet | key  |  value   |
+------------+--------------+----------------------+------------------------------------------+------------+--------------+--------------------------+---------------------------------------------+--------------+----------------+-----------------+--------------+------------------+-----------------+---------------------------------------------+-------------------------------------------------------------------------------------------------+--------------+----------------+----------------+------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------+------+----------+
| i-83c5d4e0 | ami-31814f58 | code=80 name=stopped | User initiated (2011-12-10 01:29:51 GMT) | skysql-ec2 | m1.small     | 2011-12-09T20:41:39.000Z | availabilityZone=us-east-1c tenancy=default | aki-805ea7e9 | state=disabled | subnet-c44fafac | vpc-cd4fafa5 | 10.0.0.164       | true            | item= groupId=sg-134b547f groupName=default | code=Client.UserInitiatedShutdown message=Client.UserInitiatedShutdown: User initiated shutdown | i386         | ebs            | /dev/sda1      | item= deviceName=/dev/sda1 ebs= volumeId=vol-19ec6174 status=attached attachTime=2011-12-10T01:30:32.000Z deleteOnTermination=true | paravirtual        |        |      |          |
|            |              |                      |                                          |            |              |                          |                                             |              |                |                 |              |                  |                 |                                             |                                                                                                 |              |                |                |                                                                                                                                    |                    |        | Name | ndb32-02 |
+------------+--------------+----------------------+------------------------------------------+------------+--------------+--------------------------+---------------------------------------------+--------------+----------------+-----------------+--------------+------------------+-----------------+---------------------------------------------+-------------------------------------------------------------------------------------------------+--------------+----------------+----------------+------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------+------+----------+

real    0m1.546s
user    0m0.123s
sys    0m0.035s

Well, the output format isn't exactly any more appealing than what you get from the Amazon tool, but it sure gives it to you a lot faster! A little poking around showed me that the aws tool allows you to forego the pretty-printing and get the actual XML that the tool receives from the AWS API.

$ aws --xml din i-83c5d4e0
<?xml version="1.0" encoding="UTF-8"?>
<DescribeInstancesResponse xmlns="http://ec2.amazonaws.com/doc/2011-11-01/">
    <requestId>4e1bf76d-ad02-439b-b255-108e09713251</requestId>
    <reservationSet>
        <item>
            <reservationId>r-7db4731c</reservationId>
            <ownerId>801025846226</ownerId>
            <groupSet/>
            <instancesSet>
                <item>
                    <instanceId>i-83c5d4e0</instanceId>
                    <imageId>ami-31814f58</imageId>
                    <instanceState>
                        <code>80</code>
                        <name>stopped</name>
                    </instanceState>
                    <privateDnsName/>
                    <dnsName/>
                    <reason>User initiated (2011-12-10 01:29:51 GMT)</reason>
                    <keyName>skysql-ec2</keyName>
                    <amiLaunchIndex>0</amiLaunchIndex>
                    <productCodes/>
                    <instanceType>m1.small</instanceType>
                    <launchTime>2011-12-09T20:41:39.000Z</launchTime>
                    <placement>
                        <availabilityZone>us-east-1c</availabilityZone>
                        <groupName/>
                        <tenancy>default</tenancy>
                    </placement>
                    <kernelId>aki-805ea7e9</kernelId>
                    <monitoring>
                        <state>disabled</state>
                    </monitoring>
                    <subnetId>subnet-c44fafac</subnetId>
                    <vpcId>vpc-cd4fafa5</vpcId>
                    <privateIpAddress>10.0.0.164</privateIpAddress>
                    <sourceDestCheck>true</sourceDestCheck>
                    <groupSet>
                        <item>
                            <groupId>sg-134b547f</groupId>
                            <groupName>default</groupName>
                        </item>
                    </groupSet>
                    <stateReason>
                        <code>Client.UserInitiatedShutdown</code>
                        <message>Client.UserInitiatedShutdown: User initiated shutdown</message>
                    </stateReason>
                    <architecture>i386</architecture>
                    <rootDeviceType>ebs</rootDeviceType>
                    <rootDeviceName>/dev/sda1</rootDeviceName>
                    <blockDeviceMapping>
                        <item>
                            <deviceName>/dev/sda1</deviceName>
                            <ebs>
                                <volumeId>vol-19ec6174</volumeId>
                                <status>attached</status>
                                <attachTime>2011-12-10T01:30:32.000Z</attachTime>
                                <deleteOnTermination>true</deleteOnTermination>
                            </ebs>
                        </item>
                    </blockDeviceMapping>
                    <virtualizationType>paravirtual</virtualizationType>
                    <clientToken/>
                    <tagSet>
                        <item>
                            <key>Name</key>
                            <value>ndb32-02</value>
                        </item>
                    </tagSet>
                    <hypervisor>xen</hypervisor>
                </item>
            </instancesSet>
            <requesterId>058890971305</requesterId>
        </item>
    </reservationSet>
</DescribeInstancesResponse>

Sweet, sweet data! Hold on, though, I can't use grep to get at that. I'm going to have to remember how to interact with XML documents; I decided I had better see if I could dig up any XPath knowledge.

The next question was what tool I wanted to use to execute some XPath expressions against. I was not very keen on having to write an entire perl or python script to read the XML, build it into some DOM, and then loop several times over crusty data structures to get the data I wanted. I wanted to be able to do some more generalized things that are very easily accomplished in XPath, such as getting a list of instances based on a prefix of their Name, get a list of "stopped" instances, get a list of instances with public IP addresses, et cetera.

I figured there must be some command-line tool that would let me execute arbitrary XPath against an XML file. After poking around a while, I found XMLStarlet [http://xmlstar.sourceforge.net/]. Installing this on my MacBook Pro using Homebrew [http://mxcl.github.com/homebrew/] and I was off to the races.

After grappling for a very annoying amount of time with XML namespaces, I eventually figure I'd just strip the thing out so that I didn't have to deal with it. (If you leave the namespace in, you have to give it an alias and then specify that before every tag in your XPath expressions. No, thanks.)

$ cat strip_xmlns 
sed 's/ xmlns="[^>]*"//'

The xmlstarlet/xmlstar/xml tool works by specifying a template that includes some expression to match and some expressions to generate output. The tool does a lot, so some of the options can appear to be a bit verbose at first. Here's a very basic use of the tool to get just a list of instance IDs:

$ aws --xml din | strip_xmlns | xml sel -T -t -m '//instancesSet/item' -v 'instanceId' -n
i-d1dbceb2
i-afdacfcc
i-cbc2d7a8
i-99bfaafa
i-1d40547e
i-f7c5d494
i-83c5d4e0
i-77c4d514
i-75c4d516
i-47feee24
i-707d9512

You can see the XSLT that the tool is applying internally by using the -C option:

$ aws --xml din | strip_xmlns | xml sel -C -t -m '//instancesSet/item' -v 'instanceId' -n
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output omit-xml-declaration="yes" indent="no"/>
<xsl:template match="/">
<xsl:for-each select="//instancesSet/item">
<xsl:value-of select="instanceId"/>
<xsl:value-of select="'&#10;'"/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

OK, so, there's a tool that will let me execute some XPath and get back information about my instances, that's nice. Instead of trying to parse some formatted output, I should be able to select the XML elements I want for a particular task.

Say I want the instance IDs of all instances that are stopped:

  aws --xml din | strip_xmlns | xml sel -T -t -m '//instancesSet/item[instanceState/name="stopped"]' -v 'instanceId' -n

Or maybe I want all instances with Names that start with the string "ndb":

  aws --xml din | strip_xmlns | xml sel -T -t -m '//instancesSet/item[starts-with(tagSet/item[key="Name"]/value, "ndb32")]' -v 'instanceId' -n

Instead of having to write several loops in perl or python, I'm able to write a very straightforward expression that matches just the nodes I want. Instead of writing that XPath every time, of course, I'll put a few of the more popular ones into a script along with some flexibility to provide arbitrary filtering. (I call this WHERE in the script because that's the first thing my DBMS-addled brain came up with!)

#!/bin/bash
while getopts "p:s:w:" OPTION
do
    case $OPTION in
        p)
            WHERE="[starts-with(tagSet/item[key='Name']/value, '$OPTARG')]"
            ;;
        s)
            WHERE="[instanceState/name = '$OPTARG']"
            ;;
        w)
            WHERE="$OPTARG"
            ;;
    esac
done

MATCHEXPR="/DescribeInstancesResponse/reservationSet/item/instancesSet/item$WHERE"

aws --xml din | strip_xmlns | xml sel -T -t -m "$MATCHEXPR" -o "instanceId    " -v instanceId -n -o "instanceName    " -v tagSet/item[key=\"Name\"]/value -n -o "privateIp    " -v privateIpAddress -n -o "ipAddress    " -v ipAddress -n -o "instanceState    " -v instanceState/name -n -n

$ ec2-ls -p ndb
$ ec2-ls -s stopped
$ ec2-ls -w "[instanceType='m1.small']"

My script returns several items that may or may not be of interest to others. Further extension to the script could easily make the list of items returned a bit more useful. From that basically reasonable if limited script, I vastly overreached my bash skills and turned it into this monstrosity:

#!/bin/bash
OUTPUT="instanceId;instanceName:tagSet/item[key='Name']/value;privateIp:privateIpAddress;ipAddress;instanceState:instanceState/name"
DELIM=" " #there might be a <tab> in there!
declare -a XMLARGS

push()            # Push item on stack.
{
        if [ -z "$1" ]    # Nothing to push?
        then
          return
        fi
        XMLARGS[${#XMLARGS[*]}]="$1"
        return
}


while getopts "p:s:w:o:d:D" OPTION
do
        case $OPTION in
                p)
                        WHERE="[starts-with(tagSet/item[key='Name']/value, '$OPTARG')]"
                        ;;
                s)
                        WHERE="[instanceState/name = '$OPTARG']"
                        ;;
                w)
                        WHERE="$OPTARG"
                        ;;
                o)
                        OUTPUT="$OPTARG"
                        ;;
                d)
                        DELIM="$OPTARG"
                        ;;
                D)
                        DEBUG=1
                        ;;
                
        esac
done
shift $((OPTIND-1)) #something about argument processing, supposedly

for i in "sel" "-T" "-t" "-m"; do
        push "$i"
done

MATCHEXPR="/DescribeInstancesResponse/reservationSet/item/instancesSet/item$WHERE"
push "$MATCHEXPR";

OLDIFS=$IFS;
IFS=";"
for f in $OUTPUT; do
        FIELDNAME=$(echo $f | cut -d':' -f 1)
        FIELDEXPR=$(echo $f | cut -d':' -f 2)
        if [[ -z $FIELDEXPR ]]; then
                FIELDEXPR=$FIELDNAME
        fi
        push "-o";
        push "$FIELDNAME$DELIM";
        push "-v";
        push "$FIELDEXPR";
        push "-n";
done
push "-n";
IFS=$OLDIFS


if [[ $DEBUG -eq 1 ]]; then
        echo "$MATCHEXPR" >&2 
        echo "${XMLARGS[@]}" >&2
fi

aws --xml din | strip_xmlns | xml "${XMLARGS[@]}"

I'm sure there are plenty of problems with that script, but at least now I can finally get the information I want about my EC2 instances!

$ ec2-ls -p ndb32 -o "instanceId;privateIp:privateIpAddress"

Merry Christmas and Happy New Year!

As Christmas holidays are drawing closer, we offer you to get 20% off any product license order.

Don’t think twice, just save 20%. And what more, you get free access to all future releases of the respective products for one year.

Get 20% discount!

Hurry up, this offer is available only from December 01, 2011 till January 10, 2012. Decide and order right now.

We wish you all the best in the coming year, let joy and happiness be your constant partners and friends!

Best Christmas Wishes,
Devart Team

Jive goes public. webOS goes open source. Cloud Foundry goes .NET. And more.

# Jive Software started IPO at $12 a share, closing the day up nearly 30%.

# HP announced that it plans to release webOS under an open source license. Details are thin on the ground, although Fedora is reportedly an inspiration. Joel West’s post pretty much summed up my thoughts.

# Tier 3 announced that it has created Iron Foundry, and open source .NET Framework implementation of Cloud Foundry.

# Xeround raised $9m funding for its MySQL-as-a-service cloud database.

# Microsoft released the Windows Azure SDK for Node.js as open source and made available a preview of the Apache Hadoop on Windows Azure, amongst a slew of other open source-related announcements.

# Red Hat, Canonical, Cisco, IBM, Intel, NetApp, and SUSE created the oVirt project, based around Red Hat’s Enterprise Virtualization technology for managing KVM environments.

# Nuxeo announced the availability of Nuxeo Platform 5.5.

# Joyent launched its SmartMachine Appliance for MongoDB.

Red Hat announced JBoss Enterprise Portal Platform 5.2 and JBoss Operations Network 3.0.

# Novell announced the availability of Novell Open Enterprise Server 11.

# Couchbase claimed thousands of open source deployments and 150 commercial deployments, but has rethought its product line-up for 2012, having “confused the heck” out of potential users in 2011.

# Univention released Univention Corporate Server 3.0.

# SuccessBricks announced that its ClearDB distributed MySQL-based database service is now available through Heroku.

# Ember.js is the new name for the SproutCore 2.0 JavaScript framework.

# HEnrik Ingo examined the recent spate of MySQL authentication plug-ins.

A year ago I posted a blog on The state of MySQL forks: co-operating without co-operating. (Also Giuseppe wrote about the topic at that time, and Peter Zaitsev covers it in his conference keynotes.) So I've been wondering if it would be good to write an update on the topic now, and in that case what to write.

My first week at Mozilla was relatively uneventful. I spent it at the Mountain View office, meeting people in person, having a few meetings, and actually doing a few tasks in addition to all of the setup and overhead that comes with being a new employee.

After traveling back home to Boston, my second week of work was a bit more eventful. We had a RAID array fail when we went to replace a disk. The RAID array held all of our e-mail, so until we could restore a backup and work on getting as much post-backup data we could out of corrupt databases (LDAP and MySQL), nobody had e-mail.

E-mail is a very big deal in any company, and Mozilla, with around 600 employees, is no exception. It was fascinating to watch my new coworkers deal with a crisis of this magnitude. A person's true self is brought out under pressure, and I got to see how everyone acted and reacted.

Folks who wanted to help but could not stepped aside to let those with the requisite knowledge/experience fix things. Higher-ups asked for statuses. Employees found ways to communicate without e-mail.

I watched this all unfold with an observant eye - especially within my team. I am happy to learn that my coworkers (including my boss) are smart, helpful, know when to jump in and when to stay out of the way, sensitive to those who are under pressure, do not spend time rat-holing on the past, and avoid the blame game.

Of course, as Tim Callaghan pointed out at the December Boston MySQL User Group on Monday evening, what would I have done if I found out that my team was a bunch of finger-pointers, my boss was a jerk, and everyone complained selfishly? Luckily, that's not a scenario I have to worry about.

A few days ago Codership announced their new version Galera v1.1 - synchronous Replication Cluster for MySQL. Before we look at the new feature of Rolling Online Schema Upgrade (OSU) we have a look at how to upgrade to the new Galera release.

A rolling upgrade of your synchronous Galera Replication Cluster from version 1.0 to 1.1 is quite easy when you stay at the same MySQL version (5.5).

To not lose the availability of your database service during the upgrade you should have at least 3 Galera nodes in your Cluster.

For further details please also look at MySQL/Galera cluster upgrade.

Hint: If you can do without rolling upgrade, you better avoid it and take your Galera Cluster down.

Check the version

Check first the version you are currently running on:

SHOW GLOBAL VARIABLES LIKE 'version';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| version       | 5.5.15-wsrep_21.1-log |
+---------------+-----------------------+

We can see that we are using MySQL 5.5.15 with the wsrep API Version 21 and the wsrep patch 21.1.

SHOW GLOBAL STATUS LIKE 'wsrep_provider_version';
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| wsrep_provider_version | 21.1.0(r86) |
+------------------------+-------------+

Here we see that we are using the Galera Replicator (plug-in) 1.0 (revision 86) based on the wsrep API Version 21.

Some rules to Galera versioning. We have 4 different version numbers we should care about:

MySQL version (5.5.15)
Wsrep API version (21, 22, ...)
The wsrep API versions will always be single monotonically increasing numbers: 21, 22, ... That indicates API compatibility between MySQL and Galera.
Wsrep Patch version (21.1)
The wsrep patch versions has the form 21.1 where the 21 represents the API version and the 1 represents bug-fix of that API version.
Galera Replicator (= provider, plug-in) (1.0(r86))
Galera versions will be in the form <major>.<minor> with minor meaning: bug-fixes and small features and major: major features, which involve a lot of code change.

Galera 22.1.1 is backward compatible with Galera 21.1.0. I was told that Galera should be at least ONE version backward compatible. So 1.0 should be for 0.8 and 1.1 for 1.0 and 1.2 for 1.1 and 2.0 for 1.2 etc.

Preparation

Download the packages for your preferred installation method from here:

In my case there was only a binary tar ball provided for Codership-MySQL but not for the Galera Plug-in v1.1. So I extracted it from the Debian package as follows:

dpkg-deb -x galera-22.1.1-amd64.deb /tmp/oli/
cp /tmp/oli/usr/bin/garbd /home/mysql/product/mysql-5.5.17-wsrep-22.3/bin
cp /tmp/oli/usr/lib/galera/libgalera_smm.so /home/mysql/product/mysql-5.5.17-wsrep-22.3/lib/plugin/

For RPM's it should work in a similar way:

rpm2cpio package.rpm | cpio -idmv

Precautions

Make sure, that during upgrade from 5.1 to 5.5 no DDL's are allowed!

Upgrade

Then upgrade your Galera Cluster as follows:

Shift load away from this node.
Shutdown node (/etc/init.d/mysql stop)
Uninstall or remove the old Galera plug-in.
Uninstall or remove the old Codership-MySQL Binaries
Install the new Codership-MySQL binaries with the wsrep API version 22
Install the new Galera plug-in v1.1
Check if wsrep_provider in my.cnf is pointing to the correct new location.
Start node (/etc/init.d/mysql start)

Check if node came up properly:

SHOW GLOBAL STATUS LIKE 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced (6)                           |
| wsrep_cluster_size         | 3                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 1                                    |
| wsrep_provider_version     | 22.1.1(r95)                          |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

If this is the case shift load back to this node.
If you have already troubles up to here we recommend to solve the problems first and NOT to continue with the upgrade procedure. Otherwise you risk the loss of you complete service.
If your reached this step you can upgrade the next node in your Galera Cluster.

When you have upgraded all your nodes in the Galera Cluster you should notice, that the Protocol version will switch automatically from 1 to 2:

111212 17:32:24 [Note] WSREP: Quorum results:
        version    = 1,
        component  = PRIMARY,
        conf_id    = 6,
...
111212 17:34:33 [Note] WSREP: Quorum results:
        version    = 2,
        component  = PRIMARY,
        conf_id    = 7,

Configuration for Rolling restart

In Galera Cluster configurations you see often that the Cluster is still set to its initial start configuration which is inappropriate for a rolling restart operation:

Galera configuration for an initial Cluster start

Galera node 1: wsrep_cluster_address: gcomm://
Galera node 2: wsrep_cluster_address: gcomm://192.168.1.101:4567
Galera node 3: wsrep_cluster_address: gcomm://192.168.1.102:4567

In this case Node 2 and 3 are OK for a rolling restart but Galera Node 1 will fail to restart.

Galera configuration for normal operations and a Cluster rolling restart

This is the way we recommend to have a Galera configuration for normal operations and a rolling restart:

Galera node 1: wsrep_cluster_address: gcomm://192.168.1.103:4567
Galera node 2: wsrep_cluster_address: gcomm://192.168.1.101:4567
Galera node 3: wsrep_cluster_address: gcomm://192.168.1.102:4567

Every Galera node points to its "left" neigbour.

Upgrade from MySQL 5.1/Galera 0.8 to MySQL 5.5/Galera 1.1

Upgrading from MySQL 5.1/Galera 0.8 to MySQL 5.5/Galera 1.1 has to be done in 2 steps because Codership only provides backwards-compatibility for one minor version jumps (0.8 -> 1.0 -> 1.1 -> 1.2 -> 2.0).

We have 2 possibilities now:

5.1/0.8 -> 5.1/1.0 -> 5.5/1.1

5.1/0.8 -> 5.5/1.0 -> 5.5/1.1

Which one you choose is up to you.

A rolling upgrade on a running system is impossible without a snapshot state transfer (SST) at the moment. So be prepared it takes a while and causes some load on the systems.

In our case we chose the way via 5.5/1.0 (2^nd way).

To upgrade from 5.1/0.8 to 5.5/1.0 proceed as follows:

Shift load away from this node to the other 2 nodes.
Shutdown this node (/etc/init.d/mysql stop)
Set wsrep_provider = none in my.cnf
Uninstall or remove the old Galera plug-in.
Uninstall or remove the old Codership-MySQL Binaries
Install the new Codership-MySQL binaries
Install the new Galera plug-in
Start this node (/etc/init.d/mysql start)

Then you will get some error messages:

111214 11:44:59 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
111214 11:44:59 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
...
111214 11:44:59 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
111214 11:44:59 [Note] Event Scheduler: Loaded 0 events
111214 11:44:59 [Note] WSREP: wsrep_load(): loading provider library 'none'

Run mysql_upgrade (see MySQL upgrade instructions and consider that a MySQL binary upgrade is not officially supported/recommended (this is not a problem, because SST with mysqldump will do a logical restore anyway)).
Set wsrep_provider in my.cnf to the new plug-in location.
Prepare SST upgrade script on (all) the donor(s) node(s).
```
cp wsrep_sst_mysqldump wsrep_sst_mysqldump_upgrade
```

Change the script wsrep_sst_mysqldump_upgrade that it dumps all databases except the mysql database:

diff wsrep_sst_mysqldump wsrep_sst_mysqldump_upgrade
59c59
< --skip-comments --flush-privileges --all-databases"
---
> --skip-comments --flush-privileges --databases test foodmart"

Caution: Be careful with Stored Procedures, Stored Functions, Triggers and Events! This upgrade procedure will NOT work completely if you use some of those MySQL features. This upgrade procedure will further not work completely if you have differences in the mysql schema of your Galera nodes (for what ever reason).
Set wsrep_sst_method = mysqldump_upgrade in my.cnf
Start this node (/etc/init.d/mysql start). Keep in mind that one of the remaining Galera nodes will act as a SST donor and during the synchronization he is not available for queries!
Check if node came properly up: SHOW GLOBAL STATUS LIKE 'wsrep%';
If this is the case shift load back to this node.
If you have already troubles up to here we recommend to solve the problems first and NOT to continue with the upgrade procedure. Otherwise you risk the loss of you complete service.
Set wsrep_sst_method back to its original value (mysqldump).
If your reached this step you can upgrade the next node in your Galera Cluster.

If you finally manged to upgrade from MySQL 5.1/Galera 0.8 to MySQL 5.5/Galera 1.0 you can follow the procedure mentioned above to upgrade to Galera 1.1

Findings

To identify our different Galera Clusters we name them:

wsrep_cluster_name             = "Galera-0.8 wsrep-21"

In this upgrade scenario this naming convention is very non-optimal because the name of our Galera Cluster should change as well. But the value of wsrep_cluster_name should be the same on all Galera nodes otherwise a node is not capable to join the Cluster (this is to make sure that a Galera node is not connecting by accident to a/the wrong Galera Cluster).
To change the wsrep_cluster_name parameter you have to bring down the whole Galera Cluster. This is not possible at the moment during a rolling restart.
Hopefully this constraint is released in a later Galera version.

PlanetMySQL Voting: Vote UP / Vote DOWN";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}}}}i:8;a:6:{s:4:"data";s:53:" ";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";s:5:"child";a:3:{s:0:"";a:6:{s:5:"title";a:1:{i:0;a:5:{s:4:"data";s:53:"Creating tables now faster and easier with SQLyog 9.5";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"guid";a:1:{i:0;a:5:{s:4:"data";s:34:"http://www.webyog.com/blog/?p=3366";s:7:"attribs";a:1:{s:0:"";a:1:{s:11:"isPermaLink";s:5:"false";}}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"link";a:1:{i:0;a:5:{s:4:"data";s:92:"http://www.webyog.com/blog/2011/12/14/creating-tables-now-faster-and-easier-with-sqlyog-9-5/";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:11:"description";a:1:{i:0;a:5:{s:4:"data";s:1847:"Hello! We are delighted to announce the release of SQLyog 9.5 GA. This release is geared towards enhancing the user experience. The major changes are: The conversion of modal dialogue boxes to a tabbed interface for creating and altering tables, and defining table properties. Creating a table involves – defining columns, and often, creating indexes and foreign keys. Before, these actions had to be done one after the other, which was annoyingly slow. Now, you can define columns, and create indexes and foreign keys – all in a single interface, which gives you a single CREATE/ALTER TABLE query to execute. Not only is creating tables easier, it is also much faster! Modal dialogue boxes limit one from multitasking. Previously, when in the middle of creating a foreign key, if some information had to be looked up from a different table (or even the same table), it required one to close all the dialogue boxes, get the information, and then start again from scratch! Now, while working on creating a table, you can go to another tab, check for some details and return back to creating the table. History and Info tabs now appear alongside the Query tab. As the content of these two tabs is not dependent on a specific Query tab, it is logical to not have these under the Query tab. Additionally, these tabs consume one third of the system resource that they did before. Content of History and Info tabs is now searchable. The all new create table interface Refer the following posts for detailed release notes: SQLyog 9.5 Beta 2 Released SQLyog 9.5 Beta 1 Released SQLyog customers can download SQLyog 9.5 GA from the Customer Area. To evaluate SQLyog 9.5 GA please download a 30-day Trial. We are very excited about this release, and hope that you will like it. We would love to hear from you! Cheers, Team SQLyog Tweet";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:7:"pubDate";a:1:{i:0;a:5:{s:4:"data";s:31:"Wed, 14 Dec 2011 14:01:48 +0000";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:8:"category";a:3:{i:0;a:5:{s:4:"data";s:5:"MySQL";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:1;a:5:{s:4:"data";s:8:"Releases";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:2;a:5:{s:4:"data";s:6:"SQLyog";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}}s:40:"http://purl.org/rss/1.0/modules/content/";a:1:{s:7:"encoded";a:1:{i:0;a:5:{s:4:"data";s:3142:"

Hello!

We are delighted to announce the release of SQLyog 9.5 GA. This release is geared towards enhancing the user experience.

The major changes are:

The conversion of modal dialogue boxes to a tabbed interface for creating and altering tables, and defining table properties.
- Creating a table involves – defining columns, and often, creating indexes and foreign keys. Before, these actions had to be done one after the other, which was annoyingly slow. Now, you can define columns, and create indexes and foreign keys – all in a single interface, which gives you a single CREATE/ALTER TABLE query to execute. Not only is creating tables easier, it is also much faster!
- Modal dialogue boxes limit one from multitasking. Previously, when in the middle of creating a foreign key, if some information had to be looked up from a different table (or even the same table), it required one to close all the dialogue boxes, get the information, and then start again from scratch! Now, while working on creating a table, you can go to another tab, check for some details and return back to creating the table.
History and Info tabs now appear alongside the Query tab. As the content of these two tabs is not dependent on a specific Query tab, it is logical to not have these under the Query tab. Additionally, these tabs consume one third of the system resource that they did before.
Content of History and Info tabs is now searchable.

The all new create table interface

Refer the following posts for detailed release notes:

SQLyog customers can download SQLyog 9.5 GA from the Customer Area.

To evaluate SQLyog 9.5 GA please download a 30-day Trial.

We are very excited about this release, and hope that you will like it. We would love to hear from you!

Cheers,
Team SQLyog

2012 is near… and so is the next FOSDEM edition !

This year again, MySQL will be represented by its Community.

If you want to discuss with friend’s of MySQL it’s the place to be in February !

Like every year, FOSDEM takes place the first week-end of February in Brussels.

The MySQL and Friends devroom is Room H.1309 (150 seats). We (the MySQL Community) will have the room on Sunday 5th February 2012, all day.

We are organizing a MySQL & Friends dinner on Saturday night, more to come later on this.

If you want to propose a talk (you are in fact invited to propose one !), the deadline is December 26th.
Like we did on past editions, as soon as the talks are submitted, we will ask everyone to vote on the talks via Twitter or email.

Propose your talk now() here.

Thank you and see you soon in Brussels to talk MySQL and/or have a nice Belgian Beer

Christmas time, time for presents! Version 1.2.0-alpha of the free and open source PHP mysqlnd replication and load balancing plugin has been made available on PECL. PECL/mysqlnd_ms makes using any kind of MySQL database cluster easier featuring:

Read-write splitting: automatic, SQL hints, can be disabled
Load balancing: random, round robin, user defined
Fail over
Global transaction ID support: client-side emulation
Service levels: eventual consistency, session consistency, strong consistency

The last two features are new. The motto/theme of the 1.2 series is: Global Transaction ID injection and quality-of-service concept.

For many years MySQL has adviced PHP developers to implement all kinds of logic needed to use MySQL Replication or MySQL Cluster in the application. PECL/mysqlnd_ms is changing this. Take any PHP MySQL application using any PHP MySQL extension (mysql, mysqli, PDO_MySQL), compile PHP to use the mysqlnd library, install the PECL module, drop it in and, be ready to migrate the application from a single database to a database cluster. At any time, it shall be possible to overrule all automatic decisions of the plugin, whenever needed.

Service levels

One of the biggest challenges when switching from a single database to an asynchronous database cluster, such as a MySQL Replication cluster, is the change in data consistency. A single server delivers strong consistency: all clients see each others changes. A MySQL replication cluster defaults to eventual consistency for a slave access: a client may or may not see its own changes, which is a significantly lower service level. However, in many cases its OK, why else would have an asynchronous approach have gained such popularity.

Version 1.2.0-alpha of mysqlnd_ms introduces a quality of service filter. The application says what service level it needs from the cluster and the plugin delivers:

Eventual consistency
- optional parameter: maximum age, don’t read from slaves that lag too far behind
Session consistency (read your writes)
- optional parameter: global transaction ID for reading from "up-to-date" slaves
Strong consistency

A simple function call - mysqlnd_ms_set_qos() - sets the service level.

Global Transaction ID support

In its most basic form a global transaction ID can be described as a transaction counter maintained on the master. If the master fails, the most current slave for master promotion can easily be found by searching the slave with highest transaction ID. The MySQL 5.6 Development Release contains this feature, however, it is neither production ready nor fully functional yet. And, so far, master fail over remains a manual task of the database administrator. Whenever the server solution is ready, mysqlnd_ms users are prepared to make the most out of it.

Beginning with version 1.2.0-alpha, PECL/mysqlnd_ms can to a client-side emulation of the feature and transparently maintain a global transaction ID table on the master. Client-side emulation is a second best solution but one of the few options one has in heterogenous environments with servers that do not have the functionality built-in.

A global transaction ID can also be used for improving session consistency (read your writes) load balancing for MySQL replication. Without a global transaction ID a client cannot say for sure in advance if a slave has already replicated a change. To read the own writes, the client must query the master. By help of a global transaction ID one can find if a certain write has been replicated already by a slave. Read your writes becomes possible on slaves. Read load is taken away from the master.

An open House

While the event was branded as a "Congressional Facebook Developer Hackathon," what emerged more closely resembled a loosely organized conference or camp.

Facebook executives and developers shared the stage with members of Congress to give keynotes to the 200 or so attendees before everyone broke into discussion groups to talk about constituent communications, press relations and legislative data. The event might be more aptly described as a "wonk-a-thon," as Sunlight Foundation's Daniel Schuman put it last week.

This "hackathon" was organized to have some of the feel of an unconference, in the view of Matt Lira, digital director for the House Majority Leader. Lira sat down for a follow-up interview last Thursday.

"There's a real model to CityCamp," he said. "We had 'curators' for the breakout. Next time, depending on how we structure it, we might break out events that are designed specifically for programming, with others clustered around topics. We want to keep it experimental."

Why? "When Aneesh Chopra and I did that session at SXSW, that personally for me was what tripped my thinking here," said Lira. "We came down from the stage and formed a circle. I was thinking the whole time that it would have been a waste of intellectual talent to have Tim O'Reilly and Clay Shirky in the audience instead of engaging in the conversation. I was thinking I never want to do a panel again. I want it to be like this."

Part of the challenge, so to speak, of Congress hosting a hackathon in the traditional sense, with judging and prizes, lies in procurement rules, said Lira."There are legal issues around challenges or prizes for Congress," he explained. "They're allowed in the executive branch, under DARPA, and now every agency under the COMPETES Act. We can't choose winners or losers, or give out prizes under procurement rules."

Whatever you call it, at the end of the event, discussion leaders from the groups came back and presented on the ideas and concepts that had been hashed out. You can watch a short video that EngageDC produced for the House Majority Leader's office below:

What came out of this unprecedented event, in other words, won't necessarily be measured in lines of code. It's that Congress got geekier. It's that the House is opening its doors to transparency through technology.

Given the focus on Facebook, it's not surprising that social media took center stage in many of the discussions. The idea for it came from a trip to Silicon Valley, where Representative Cantor said he met with Facebook founder Mark Zuckerberg and COO Sheryl Sandberg, and discussed how to make the House more social. After that conversation, Lira and Steve Dwyer, director of online communications and technology for the House Democratic Whip, organized the event.

For a sense of the ideas shared by the working groups, read the story of the first congressional "hackathon" on Storify.

"For government, I don't think we could have done anything more purposeful than this as a first meeting," said Lira in our interview. "Next, we'll focus on building this group of people, strengthening the trust, which will prove instrumental when we get into the pure coding space. I have 100% confidence that we could do a programming-only event now and would have attendance."

A Likeocracy in alpha

As the Sunlight Foundation's John Wonderlich observed earlier this year, access to legislative data brings citizens closer to their representatives.

"When developers and programmers have better access to the data of Congress, they can better build the databases and tools that let the rest of us connect with the legislature," he wrote.

If more open legislative data goes online, when we talk about what's trending in Congress, those conversations will be based upon insight into how the nation is reacting to them on social networks, including Facebook, Twitter, and Google+.

Facebook developers Roddy Lindsay, Tyler Brock, Eric Chaves, Porter Bayne, and Blaise DiPersia coded up a simple proof of concept of what making legislative data might look like. "LikeOcracy" pulls legislation from a House XML feed and makes it more social. The first version added Facebook's ubiquitous "Like" buttons to bill elements. A second version of the app adds more opportunities for reaction by integrating ReadrBoard, which enables users to rate sections or individual lines as "Unnecessary, Problematic, Great Idea or Confusing." You can try it out on three sample bills, including the Stop Online Piracy Act.

Would "social legislation" in a Facebook app catch on? The growth of civic startups like PopVox, OpenCongress and Votizen suggests that the idea has legs. [Disclosure: Tim O'Reilly was an early angel investor in PopVox.]

Likeocracy doesn't tap into Facebook's Open Graph, but it does hint at what integration might look like in the future. Justin Osofsky, Facebook's director of platform partnerships, described how the interests of constituents could be integrated with congressional data under Facebook's new Timeline. Citizens might potentially be able to simply "subscribe" to a bill, much like they can now for any web page, if Facebook's "Subscribe" plug-in was applied to the legislative process.

Opening bill markup online

The other app presented at the hackathon came not from the attendees but from the efforts of InSourceCode, a software development firm that's also coded for Congressman Mike Pence and the Republican National Committee.

Rep. Darrell Issa, chairman of the House Committee on Oversight and Government Reform, introduced the beta version of MADISON on Wednesday, a new online tool to crowdsource legislative markup. The vision is that MADISON will work as a real-time markup engine to let the public comment on bills as they move through the legislative process. "The assumption is that legislation should be open in Congress," said Issa. "It should be posted, interoperable and commented upon."

As Nick Judd reported at techPresident, the first use of MADISON is to host Issa and Sen. Ron Wyden's "OPEN bill," which debuted on the app. Last week, the congressmen released the Online Protection and Enforcement of Digital Trade Act (OPEN) at Keepthewebopen.com. The OPEN legislation removes one of the most controversial aspects of SOPA, using the domain name system for enforcement, and instead places authority with the International Trade Commission to address enforcement of IP rights on websites that are primarily infringing upon copyright.

Issa said that his team had looked at the use of wikis by Rep. John Culberson, who put the healthcare reform bill online in a wiki. "There are some problems with editors who are not transparent to all of us," said Issa. "That's one of the challenges. We want to make sure that if you're an editor, you're a known editor."

MADISON includes two levels of authentication: email for simple commenting and a more thorough vetting process for organizations or advocacy groups that wish to comment. "Like most things that are a 1.0 or beta, our assumption is that we'll learn from this," said Issa. "Some members may choose to have an active dialog. Others may choose to have it be part of pre-markup record."

Issa fielded a number of questions on Wednesday, including one from web developer Brett Stubbs: "Will there be open access or an API? What we really want is just data." Issa indicated that future versions might include that.

Jayson Manship, the "chief nerd" at InSourceCode, said that MADISON was built in four days. According to Manship, the idea came from conversations with Issa and Seamus Kraft, director of digital strategy for the House Committee on Oversight and Government Reform. MADISON is built with PHP and MySQL, and hosted in RackSpace's cloud so it can scale with demand, said Manship.

"It's important to be entrepreneurial," said Lira in our interview. "There are partners throughout institutions that would be willing to do projects of different sizes and scopes. MADISON is something that Issa and Seamus wanted to do. They took it upon themselves to get the ball rolling. That's the attitude we need."

"We're working to hold the executive accountable to taxpayers," said Kraft last week. "Opening up what we do here in these two halls of Congress is equally important. MADISON is our first shot at it. We're going to need a lot of help to make it better."

Kraft invited the remaining developers present to come to the Rayburn Office Building, where Manship and his team had brought in half a dozen machines, to help get MADISON ready for launch. While I was there, there were conversations about decisions, plug-ins and ideas about improving the interface or functionality, representing a bona fide collaboration to make the app better.

There's a larger philosophical issue relating to open government that Nick Judd touched upon over at techPresident in a follow-up post on MADISON:

The terms for the site warn the user that anything they write on it will become public domain — but the code itself is proprietary. Meanwhile, OpenCongress' David Moore points out that the code that powers his organization's website, which also allows users to comment on individual provisions of bill text, is open source and has been available for some time. In theory, this means the Oversight staff could have started from that code and built on it instead of beginning from scratch. The code being proprietary means that while people like Moore might be able to make suggestions, they can't just download it, make their own changes and submit them for community review — which they'd happily do at little or no cost for a project released under an open-source license.

As Moore put it, "Get that code on GitHub, we'll do OpenID, fix the design."

When asked about whether the team had considered making MADISON code open source, Manship said that "he didn't know, although they weren't opposed to it."

While Moore welcomed MADISON, he also observed that Open Congress has had open-source code for bill text commenting for years.

@seamuskraft @mattlira glad to chat, will email. We see first step as liberating full #opengovdata (API & bulk) for MADISON & OC & open Web.
— David Moore (@ppolitics) December 9, 2011

The decision by Issa's office to fund the creation of an app that was already available as open-source software is one that's worth noting, so I asked Kraft why they didn't fork OpenCongress' code, as Judd suggests. "While there was no specific budget expense for MADISON, it was developed by the Oversight Committee," said Kraft.

"While we like and support OpenCongress' code, it didn't fit the needs for MADISON," Kraft wrote in an emailed statement.

What's next is, so to speak, an "OPEN" question, both in terms of the proposed SOPA alternative and the planned markup of SOPA itself on December 15. The designers of OPEN are actively looking for feedback from the civic software development community, both in terms of what functionality exists now and what could be built in future iterations.

THOMAS.gov as a platform

What Moore and long-time open-government advocates like Carl Malamud want to see from Congress is more structural change:

Re: #hackwetrust, while we do seek leg. version control, public bill markup isn't ultimate goal. Exhaustive #opengovdata & open API is (2/2)
— David Moore (@ppolitics) December 8, 2011

@MattLira @DarrellIssa @SeamusKraft MADISON is much-welcomed, but PPF's #opengov ultimate goal is open API for @THOMASdotgov -cc @digiphile.
— David Moore (@ppolitics) December 9, 2011

They're not alone. Dan Schuman listed many other ways the House has yet to catch up with 21st century technology:

We have yet to see bulk access to THOMAS or public access to CRS reports, important legislative and ethics documents are still unavailable in digital format, many committee hearings still are not online, and so on.

As Schuman highlighted, the Sunlight Foundation has been focused on opening up Congress through technology since the organization was founded. To whit: "There have been several previous collaborative efforts by members of the transparency community to outline how the House of Representatives can be more open and accountable, of which an enduring touchstone is the Open House Project Report, issued in May 2007," wrote Schuman.

The notion of making THOMAS.gov into a platform received high-level endorsement from a congressional leader when House Minority Whip Steny Hoyer remarked on how technology is affecting Congress, his caucus and open government in the executive branch:

For Congress, there is still a lot of work to be done, and we have a duty to make the legislative process as open and accessible as possible. One thing we could do is make THOMAS.gov — where people go to research legislation from current and previous Congresses — easier to use, and accessible by social media. Imagine if a bill in Congress could tweet its own status.

The data available on THOMAS.gov should be expanded and made easily accessible by third-party systems. Once this happens, developers, like many of you here today, could use legislative data in innovative ways. This will usher in new public-private partnerships that will empower new entrepreneurs who will, in turn, yield benefits to the public sector.

One successful example is how cities have made public transit data accessible so developers can use it in apps and websites. The end result has been commuters saving time every day and seeing more punctual trains and buses as a result of the transparency. Legislative data is far more complex, but the same principles apply. If we make the information available, I am confident that smart people like you will use it in inventive ways.

Hoyer's specific citation of the growth of open data in cities and an ecosystem of civic applications based upon it is further confirmation that the Gov 2.0 meme is moving into the mainstream.

Making THOMAS.gov into a platform for bulk data would change what's possible for all civic developers. What I really want is "data on everything," Stubbs told me last week. "THOMAS is just a visual viewer of the internal stuff. If we could have all of this, we could do something with it. What I would like is a data broker. I'd like a RESTful API with all of the data that I could just query. That's what the government could learn from Facebook. From my point of view, I just want to pull information and compile it."

If Hoyer and the House leadership would like to see THOMAS.gov act as a platform, several attendees at the hackathon suggested to me that Congress could take a specific action: collaborate with the Senate and send the Library of Congress a letter instructing it to provide bulk legislative data access to THOMAS.gov in structured formats so that developers, designers and citizens around the nation can co-create a better civic experience for everyone.

"The House administration is working on standards called for by the rule and the letter sent earlier this year," said Lira. "We think they will be satisfactory to people. The institutions of the House have been following through since the day they were issued. The first step was issuing an XML feed daily. Next year, there will be a steady series of incremental process improvements. When the House Administrative Committee issues standards, the House Clerk will work on them. "

Despite the abysmal public perception of Congress, genuine institutional changes in the House of Representatives driven by the GOP embracing innovation and transparency are incrementally happening. As Tim O'Reilly observed earlier this year, the current leadership of the House on transparency is doing a better job than their predecessors.

In April, Speaker Boehner and Majority Leader Cantor sent a letter to the House Clerk regarding legislative data release. Then, in September, a live XML feed for the House floor went online. Yes, there's a long way to go on open legislative data quality in Congress. That said, there's support for open-government data from both the White House and the House.

"My personal view is that what's important right now is that the House create the right precedents," said Lira. "If we create or adopt a data standard, it's important that it be the right standard."

Even if open government is in beta, there needs to be more tolerance for experiments and risks, said Lira. "I made a mistake in attacking We the People as insufficient. I still believe it is, but it's important to realize that the precedent is as important as the product in government. In technology in general, you'll never reach an end. We The People is a really good precedent, and I look forward to seeing what they do. They've shown a real commitment, and it's steadily improving."

A social Congress

While Sean Parker may predict that social media will determine the outcome of the 2012 election, governance is another story entirely. Meaningful use of social media by Congress remains challenged by a number of factors, not least an online identity ecosystem that has not provided Congress with ideal means to identify constituents online. The reality remains that when it comes to which channels influence Congress, in-person visits and individual emails or phone calls are far more influential with congressional staffers.

As with any set of tools, success shouldn't be measured solely by media reports or press releases but by the outcomes from their use. The hard work of bipartisan compromise between the White House and Congress, to the extent it occurs, might seem unlikely to be publicly visible in 140 characters or less.

"People think it's always an argument in Washington," said Lira in our interview. "Social media can change that. We're seeing a decentralization of audiences that is built around their interests rather than the interests of editors. Imagine when you start streaming every hearing and making information more digestible. All of a sudden, you get these niche audiences. They're not enough to sustain a network, but you'll get enough of an audience to sustain the topic. I believe we will have a more engaged citizenry as a result."

Lira is optimistic. "Technology enables our republic to function better. In ancient Greece, you could only sustain a democracy in the size of city. Transportation technology limited that scope. In the U.S., new technologies enabled global democracy. As we entered the age of mass communication, we lost mass participation. Now with the Internet, we can have people more engaged again."

There may be a 30-year cycle at play here. Lira suggested looking back to radio in the 1920s, television in the 1950s, and cable in the 1980s. "It hasn't changed much since; we're essentially using the same rulebook since the '80s. The changes made in those periods of modernization were unique."

Thirty years on from the introduction of cable news, will the Internet help reinvigorate the founders' vision of a nation of, by and with the people? "I do think that this is a transformational moment," said Lira. "It will be for the next couple of years. When you talk to people — both Republicans and Democrats — you sense we're on the cusp of some kind of change, where it's not just communicating about projects but making projects better. Hearings, legislative government and executive government will all be much more participatory a decade from now. "

In that sweep of history, the "People's House" may prove to be a fulcrum of change. "If any place in government is going to do it, it's the House" said Lira. "It's our job to be close to the public in a way that no other part of government is. In the Federalist Papers, that's the role of the House. We have an obligation to lead the way in terms of incorporating technology into real processes. We're not replacing our system of representative government. We're augmenting it with what's now possible, like when the telegraph let people know what the votes were faster."

Strata 2012 — The 2012 Strata Conference, being held Feb. 28-March 1 in Santa Clara, Calif., will offer three full days of hands-on data training and information-rich sessions. Strata brings together the people, tools, and technologies you need to make data work.

Save 20% on registration with the code RADAR20

Global Transaction IDs - simplifying replication management

There will be a webinar this Wednesday where you can get the latest information on all of the great new content that has been included in the MySQL 5.6 Development Releases as well as some features that are still being developed. As always, the webinar is free but you need to register here in advance. Even if you can’t attend the live event it’s worth registering so that you get sent the replay.

Some of the topics we’ll be discussing are:

Enhanced data integrity: Global Transactions Identifiers, Crash-Safe Slaves and Replication Event Checksums;
High performance: Multi-Threaded Slaves, Binlog Group Commit and Optimized Row-Based Replication;
Improved flexibility: Time Delayed Replication, Multi-Source Replication, Binlog API and Informational Log Events

The event starts on Wednesday, December 14, 2011: 09:00 Pacific time; 17:00 UK; 18:00 CET.

There has been a lot going on in MySQL community and I didn’t blogged about MySQL for some time. So this is a small update regarding MySQL in openSUSE Build Service and in openSUSE in general. This post is intended to let you know what, where and in which version we’ve got in Build Service And as I recently dropped server:database:UNSTABLE repo, everything is now in server:database, so the where part is quite easy

MySQL Community Server

First, let’s take a look at MySQL Community Server provided by the open source company we all love – Oracle. There are several versions around. Which one is the right one for you? That question you have to decide by yourself, but whichever it is, we’ve got in in obs

MySQL 5.5

MySQL 5.5 was in the in repositories for quite some time. Currently there is a version 5.5.18. You can get it by installing package mysql-community-server from server:database repository. As 5.5 is around a long time already and widely tested (was running my blog for several months) it is also the version shipped with openSUSE 12.1. Although version included there is just 5.5.16. But that’s good anyway, isn’t it?

MySQL 5.6

Do you think that MySQL 5.5 is old and not cool anymore? I kind of agree. Where is the fun in stable things So if you want, you can start testing the new cool branch people are speaking about – MySQL 5.6.2. You can find it in server:database repository as package mysql-community-server_56.

MariaDB

Now let’s talk a little bit about the stuff that folks from Monty Program are working on.

MariaDB 5.2

As for MariaDB, there is version 5.2.10 available in the server:database repository and version 5.2.9 made it to openSUSE 12.1. So you can choose from who do you want your MySQL even in distribution itself

MariaDB 5.3

As I said already, I don’t like stable stuff, I like living on the edge. The bleeding edge So if you like Maria but want the newest and coolest, we’ve got that too. You can get MariaDB 5.3.2 from server:database repository (package mariadb_53) right now and try it out! That is the MySQL version I’m using for this blog right now But who knows what variant I will be using next month or year?

MySQL Cluster

Do you want more options? Do you want to play with cluster? We’ve got that too

MySQL Cluster 7.1

For the conservative folks, we’ve got MySQL Cluster 7.1.17 in server:database repository and version 7.1.15 was included in openSUSE 12.1. As you may have read already, I dropped MySQL Cluster from openSUSE Factory, so there will be probably no MySQL Cluster in next openSUSE. But don’t worry, it will still be here in server:database repository

MySQL Cluster 7.2

Do you want to play some more with clusters? We’ve got also MySQL Cluster 7.2.1 in server:database repository (package mysql-cluster_72). So you can try new beta, play with new features and try to make it crash. I think Oracle will be happy if you will manage to and will tell them about it

Switching around

Thanks to the setup we’ve got in openSUSE it is quite easy to switch between all variants as you like. Is there really cool new feature you are dying to try in MySQL 5.6? No problem if you have server:database repository. Just type in:

# zypper in mysql-community-server_56

Have Oracle done something bad and are you angry at them? Do you want your symbolic virtual revenge? Just type in:

# zypper in mariadb

What will happen? Well regardless of what would you try to install, zypper will tell that it is not possible to have two different variants at the same time. And it will let you choose. Either you will continue with installation and deinstall your old variant, or you will cowardly decide to stick with what you have. Quite simple, isn’t it? There is one little catch… Your old configuration will be saved as my.cnf.rpmsave and you’ll end up with default configuration. But you can solve this one easily
What do we still miss from widely used stuff in our repositories is Percona. But I created Google Code-In task, so maybe some student will step-up and fix this. And even if nobody will, you can always try to be that volunteer I tried to put together some HowTo contribute to MySQL in openSUSE, so it shouldn’t be hard

Overall, if you want to play with MySQL, I think we are well prepared

ScaleBase Releases Database TPC-C Performance Results

Technology achieves unprecedented transaction speed for a MySQL database at a low cost

Boston, Mass., December 12, 2011 – ScaleBase, Inc. today announced the results of its MySQL database benchmark, based on the industry-standard TPC-C test. ScaleBase has achieved an unmatched 180,000 Transactions per Minute – the highest result for a MySQL database – while running on an Amazon RDS environment. Cost per Transaction was reported to be 50 cents, which demonstrates the cost-effectiveness of the ScaleBase solution on the Amazon EC2 cloud. Full details of the benchmark can be found at http://www.scalebase.com/resources/performance/.

TPC, the Transaction Processing Performance Council, defines transaction processing and database benchmarks and delivers reliable, independent results to the industry. The TPC-C benchmark is a popular yardstick for comparing Online Transaction Processing (OLTP) performance on various hardware and software configurations.

The ScaleBase Database Load Balancer is a packaged solution for transparently scaling MySQL databases. ScaleBase utilizes two techniques for scaling: read-write splitting and transparent sharding (a technique for massively scaling-out relational databases). The software enables MySQL to scale transparently, without forcing developers to change a single line of code or perform a long data migration process. The technology is ideally suited for any application in which scalability, performance and speed are critical, including: gaming, e-commerce, SaaS, machine-generated data, Web 2.0 and more.

“Some people feel that by using MySQL they stand the chance of limiting their performance options, however, these TPC-C results proves that this simply is no longer the case,” said Rob Levine, ScaleBase’s VP of Sales. “Without writing specialized code you can still get top performance – perhaps optimal performance – at an affordable rate, accounting for the requisite hardware and infrastructure resources. Especially in today’s economy, getting such great performance and optimizing every dollar spent can save companies substantial amounts of money.”

ScaleBase’s Database Load Balancer solution has been successfully used by numerous customers since its official release in August 2011.

About ScaleBase

ScaleBase has developed an innovative database load balancing technology that enables MySQL users to achieve scalability and high availability, without changing a single line of application code. ScaleBase utilizes two techniques for scaling: read-write splitting and transparent sharding, which is a method for massively scaling-out relational databases. The ScaleBase technology is ideally suited for any application in which scalability, performance and speed are critical, including: gaming, e-commerce, SaaS, machine-generated data and more. The company is privately-held and headquartered near Boston, Mass. Follow @SCLBase on Twitter.

Media Contact

Candice Perodeau

508-475-0025 x112

cperodeau@rainierco.com

[Update: need to take more breaks: now NOT crashing my servers! See clarifications below]

INFORMATION_SCHEMA Optimizations are meant to make your INFORMATION_SCHEMA queries lighter and safer.

For example, if you're going to query the COLUMNS table for just the columns of a single table, then the following:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='sakila' AND TABLE_NAME='rental'

makes for an optimization: specifying a literal on TABLE_SCHEMA avoid scanning the directories of other schemata. Specifying a literal on TABLE_NAME avoids checking up on other tables. So it's a one-schema-one-table read operation, as opposed to "first read every single column from all and any single schema and table, then return only those I'm interested in".

Here's the execution plan for the above query:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: COLUMNS
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned 0 databases

What I tried to do is to read the entire COLUMNS table, one schema at a time, one table at a time. I'm good with this taking longer time.

I have a production system on which reads from COLUMNS consistently crash the servers. Well, one read at a time can't do harm, right?

Unfortunately, as the title of this posts reveals, even sequential read of COLUMNS using INFORMATION_SCHEMA optimization does not help: a minute into the process and the client lost connection. The server crashed.

~~I was expecting that table locks would be released, buffers released etc. One at a time, there wouldn't be a congestion of locks, reads, table cache suffocation etc.~~

~~Was actually having high hopes for this to succeed. I have to find a way in which INFORMATION_SCHEMA tables are not dangerous.~~

A few hours later, and I have both conclusions and achievements.

There are indeed memory issues with querying from INFORMATION_SCHEMA tables. I've found that VARCHAR(64) columns can consume 64K each: I'm reading from large tables of more than 1,000 columns each, while monitoring MySQL's memory consumption. By dividing the increase in memory by the number of rows resulting from a query I sent, and which was for one single columns, I got an almost exact 64K value per row.

So a query on INFORMATION_SCHEMA consumes much more memory than it should. The good news is that this memory is released once the query terminates. So there is no leak into the session memory.

This is combined with a mistake of mine in the way I iterated the tables, such that the problem was amplified: I happened to query much more than I needed, and so got my query's memory bloated. That is to say, I used the INFORMATION_SCHEMA optimizations only partly right, and so got only part of the savings it could offer me.

With better pinpointing I'm now actually able to read from COLUMNS, without crashing my servers, consistently.

I will further look into the 64K issue. That in itself still drains a lot of memory: on my mycheckpoint schema tables a singe table read means > 64MB of query memory down the drain.

I previously posted blog about the topic of a MySQL Cluster set up and replication to a MySQL Server. The first blog was just the installation, now lets test the cluster and get replication started. Both are extremely easy.

Some people might ask, “Why set up replication from a cluster? It is a HA system why replication?”

The ability to move data out of the cluster and allow others to do reporting, data exports, simple backups all can be done easily with it replicated into another MySQL server. Yes a Cluster can do all of those queries, but taking advantage of the replication to keep some users and queries out of your production database is rarely a bad thing.

Testing the Cluster Install. Of course more MySQL Documentation:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-quick.html


mysql> SHOW ENGINES\G 
*************************** 1. row ***************************
Engine: ndbcluster
Support: YES
Comment: Clustered, fault-tolerant tables
Transactions: YES
XA: NO
Savepoints: NO

*************************** 3. row ***************************
Engine: ndbinfo
Support: YES
Comment: MySQL Cluster system information storage engine
Transactions: NO
XA: NO
Savepoints: NO

mysql>  USE test;
Database changed
mysql>;  CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.28 sec)

mysql>  SHOW CREATE TABLE ctest \G
*************************** 1. row ***************************
Table: ctest
Create Table: CREATE TABLE `ctest` (
`i` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

That was easy enough...Table is built and using the ndbcluster engine.

So lets set up replication from a cluster to a MySQL Innodb Server.

1^st create a user for the replication.


GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'10.%' IDENTIFIED BY '

<password>

';
flush privileges;

For this example I am jut using a Windows Virtual Box instance. The MySQL windows installer worked great. For a guy who ever runs windows, it was nice that this was so easy. (http://dev.mysql.com/downloads/installer/)

On the Cluster server, I made sure to have my server_id and big logs set.


vi /etc/my.cnf 
[mysqld]
server-id=1
log-bin                                 = /var/lib/mysql/mysql_demo-bin

restarted the sql server and confirmed it was running:


mysql> show master status\G< 
*************************** 1. row *************************** 
File: mysql_demo-bin.000001 
Position: 112 Binlog_Do_DB:  
Binlog_Ignore_DB:   1 
row in set (0.00 sec)

So lets create a simple table that we can use for test later.

PLEASE take note... This is just a table to show it replicated. If you create tables for real use in a Cluster and replicated ... ALWAYS have a primary key!


mysql>  CREATE TABLE slave_test (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.28 sec)

Now back on my windows mysql server. Since it came with workbench I used this to set the server_id (server_id =2) and restart the server. I then opened the sql editor and added the master settings.

via_mysql_workbench>

 CHANGE MASTER TO
-> MASTER_HOST='10.132.241.18',
-> MASTER_PORT=3306,
-> MASTER_USER='slave_user',
-> MASTER_PASSWORD='<password>';

via_mysql_workbench> start slave;

via_mysql_workbench>use test;

via_mysql_workbench> SHOW CREATE TABLE slave_test ;

'slave_test', 'CREATE TABLE `slave_test` (

`i` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1'

mysql> show slave status;

...

Slave_IO_Running, Yes

Slave_SQL_Running Yes

...

So now via these blog posts.. I have a cluster with a geolocated node both on Oracle Linux 6 being replicated to another remote location MySQL server running Windows via a Virtual box instance. I can also now use Workbench to connect to the local windows server as well as the cluster if needed. This is not a production system but it does show that Cluster can be installed easily, remotely, and takes advantage of commodity systems.

Part 1 -- The Install
Part 2 -- Testing Cluster more and Replication Setup

Installation issues

This is often a straightforward case of lack of functionality. When a tool does not install what it is supposed to, it is a show stopper, and a solution must be found. In this case, it's often enough to state the environment where the installation is happening (operating system, version of the tool, version of core components such as the MySQL database used) and the command used to start the installation. The error message could be an expected failure, when the installation procedure checks for requirements and fails if they are not met. For example: "Missing Ruby interpreter". The message tells (or suggests) you what to do. Filing a bug report on an expected failure is a waste of time. You should install the missing part and try again. Even if the message is about an unexpected failure (e.g. you get a stack trace from Ruby or Java), usually the first message tells you enough to be able to find a workaround. For example, if you get an exception from Ruby complaining about a missing 'curl' command, you can file a bug report to ask for the installer to check for 'curl' in the requirements, but if you install 'curl' yourself, the installation should continue.

Simple database issues

Reporting a database bug means complaining that the DBMS is not behaving as advertising by the documentation, or as common usage dictates. If it is a missing or misbehaving functionality, the best way of showing the problem is by starting with an empty DBMS, followed by the creation of the objects needed to reproduce the issue (CREATE SCHEMA, TABLE, INDEX) and by a minimal amount of data that triggers the problem. Some information about what operating system and database version was used is probably necessary to reproduce the problem consistently.

Simple database replication issues

By simple replication we mean a vanilla master/slave topology. In this scheme, data inserted in the master will eventually end up in the slave. Bugs in this category may fail to replicate the data totally or partially, or they may cause a break in the replication flow. Reproducing them is almost as easy as with simple database bugs. If you start with an empty system and manage to reproduce the error with a short sequence of commands, it should probably reproducible by a third party. Sometimes, settings in the master and the slave are essential to reproduce the problem. In MySQL, the format of binary logs, the default database engine and SQL modes can affect replication and produce different results with the same stream of SQL commands.

Complex database replication issues

The most difficult bugs to report are the ones where the error shows up only in a given topology. While MySQL native replication offers only few options to pipe data around (single, circular, hierarchical), Tungsten replicator allows a rich set of combined pipelines that can change the outcome of a data change event, depending on the originating node and the direction it took. In these case, information o how the cluster was installed becomes essential.

Concurrency issues

This is one of the most difficult bugs to report. When an error happens only because of the contemporary action of two or more threads, there is no easy way of reporting it in a way that it can be easily reproduced. Three methods are possible:

Describe the action of the first thread, then mark the change of thread and describe the actions of the second thread, continuing in this way until you reach the error point.
If you are a developer and feel comfortable with multi thread applications, write a script that reproduces the error by running several threads (Perl, Python, and Ruby offer the best environment for this kind of tests).
If the database offers a tool to write such multi-threading tests, consider using it.

Heavy load issues

This is a more complex case of the above one. Not only you need concurrency, but a lot of it happening at the same time. Reproducing this kind or error is challenging. If you have a support agreement with the provider of the database or the tool, you may let the support engineer have a look at your running environment, to find some clues. But even in this case, the support engineers or yourself need to ultimately reproduce the case in such a way that a developer can fix the problem and test the fix. There are two methods to report this problem:

Simplification: if you can reduce the concurrency to the elements that are misbehaving, the methods for concurrency issues will apply also in this case.
Enabling a query log could lead to identifying the sequence of events that have generated the error. The log should be integrated by the DDL of the objects involved in the action (schemas, tables, triggers, views, etc).

Large data issues

If your error only shows itself with large data, there is often a logistical problem, as you can't easily provide gigabytes of data, even if there privacy and security issues weren't in the way (which usually are). There are three strategies that you can use to report such bugs:

If only the size matters, then describing the kind of data used could be enough. E.g. When a table with such fields and such indexes grows beyond X GB, then the optimizer warp drive explodes. (Don't try this at home)
Create a script to generate the data that will ultimately trigger the error. This method requires both skills and an understanding of what the error is.
You may use a publicly available database to reproduce the error (for example, the Sample database with test suite.) Just mention in the bug report where to find the database, eventually how to load it if it is not simple, and then describe the steps needed to reproduce the bug after loading it.

DOs and DONTs

DO
- Search the bug repository and the mailing lists (or forums) before submitting yours. Someone may have had the same problem before you did. (Thanks, Robert Hodges, for this important reminder.)
- Put yourself in the receiver's position, and try to reproduce the problem from a clean initial state.
- If there is a workaround, mention it: it might give a good clue to the developers.
- More information is better. Anything that can improve the identification of the bug root cause will be welcome. (But don't overdo: see below)
- If you feel that a missing feature should be useful, report it as a feature request. (Even better: suggest a patch)

DON'T
- Don't report a missing feature as a bug, unless the docs say that the feature should be there.
- Don't just send the error message without the events that generated it.
- Don't include SQL commands embedded in code.
- Don't say "my application doesn't work anymore," assuming it's the database's fault. Remember The First Rule of Programming: It's Always Your Fault
- Less is better. If there is a long way and a short way of reproducing the bug, the shorter one is better. Don't send more info just for the sake of it.
- Don't tell the developers that they are retarded. This will not increase the priority given to your bug, or your credibility.

MySQL is the 2011 Readers Choice Award for Best Database.

It may not be the most-exciting topic around, but databases make the world go round. MySQL with its dolphin mascot takes first place again this year, with more than twice as many votes as its closest competition, PostgreSQL.

However, I do take issue with the It may not be the most-exciting topic around quip. MySQL is very exciting!

CREATE FUNCTION MidPoint(ls LINESTRING) RETURNS POINT DETERMINISTIC BEGIN DECLARE len double; DECLARE workLength double DEFAULT 0; DECLARE workPoint int DEFAULT 1; DECLARE point0 POINT; DECLARE point1 POINT; DECLARE distanceOver double; DECLARE segmentLength double;


  IF (NumPoints(ls) = 1) THEN return PointN(ls, 1); END IF;
  -- find half the length of the linestring

  SET len := GLength(ls) / 2;
  -- walk the linestring until we exceed the distance

  WHILE (workLength < len) DO

    SET point0 = PointN(ls, workPoint);

    SET workPoint := workPoint + 1;

    SET point1 = PointN(ls, workPoint);

    SET segmentLength = GLength(LineString(point0, point1));

    SET workLength := workLength + segmentLength;

  END WHILE;
  -- distance to backup

  SET distanceOver = workLength - len;
  -- midpoint is distanceOver back down the last segement

  RETURN POINT(

      X(point1) - distanceOver / segmentLength * (X(point1) - X(point0)),

      Y(point1) - distanceOver / segmentLength * (Y(point1) - Y(point0))

    );

END

What happened? Well, it has been a crazy year, probably the busiest year in my whole working life.

When you are in your late twenties/early thirties, you can dedicate all your energy to something, work, hobby, charity, sport, study, you name it. You work hard, and every now and then you have time to lie down and recharge your batteries. In one or two days, if you can sleep 9/10 hours per day, all the tiredness you have accumulated goes away. I have felt that feeling, but in your late forties, there is no such thing anymore.

When you are forty and you have a family, your priorities change. You try to accommodate work, family and friends. You remove most, if not all, of the time you dedicate to yourself. You have less rest, usually not enough to recharge. The 9/10 hours you used to recharge and be up and running, ready for a new week, they become barely 6/7 hours, if you are lucky… and you feel like your head is in a constant foggy atmosphere that does not allow you to think straight. You do, but you must use more energy and it takes more time simply to get things done.

So, this is what happened to me. But I was used to that. Even in the weird days of the post-acquisition, when many people could not really do much business-wise, I felt pretty busy.

Nevertheless, I managed to enjoy this fantastic year at SkySQL. 2011 has superseded 2005 (the year I joined MySQL AB), as the best year of my entire working life. Thanks to SkySQL, thanks to all my great colleagues, to all the partners, users and customers. And thanks to my family and friends, who are supporting me so much.

What are the plans for the future? In long term, it is hard to say, but the short term plan seems really exciting, more than ever, better than 2011. I can see so many new opportunities to use MySQL and MySQL-related products and to be involved in very cool projects. MySQL is not the only wild kid in the block anymore: PostgreSQL and the NoSQL armada are rightly claiming their space and users are adopting old and new technologies. This is good news, it repositions our technology right were it should be, leaving PostgreSQL, HBase, Cassandra and others doing what they do better.

Our technology - i.e. MySQL and the MySQL Ecosystem - is evolving and improving. There is a growing demand for database systems, there is space for everybody. There is space for new projects and for integrations with the current and new infrastructures.

SkySQL will be more and more focused on new solutions and on new architectures. The reference architecture is key for these new solutions. I will blog more about in in the near future and clarify the reference architecture is and what is not. We will offer legacy systems, we will support the standard, vanilla version of MySQL, the servers that Oracle and Percona can now provide. But we will offer more, much more. There is a whole new set of highly available systems - see my talks at various meetups in the US this week - there is a new way to integrate MySQL with other databases, and a new way to create huge data warehouses systems and compete head to head with Vertica or Greenplum, not to mention the quite outdated technologies in 11g. There is a huge demand of migration from legacy systems into the cloud, with MySQL.

Here is the promise - an End-Of-Year resolution, or an anticipated New Year resolution. I will share more of this experiences and the excitement of applying the open source values and technologies where until now only expensive commercial solutions were allowed. Some of my posts will be generic and will fit in here, in my personal blog. Other posts will be more MySQL specific and will be published in MySQL4all.

The future looks bright, I hope many will enjoy the ride with me.

-ivan

I recently wrote an article on dealing with an assertion failure in log/log0recv.c, specifically !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table).

I mention it because this occurred after a system outage, and I just encountered another system outage (either HDD power outage or some other serious HDD event), with a completely different assertion failure and error message. Similar to the one above, it’s also kind of obscure, so I wanted to post this so people searching for it will find this.

For reference, the first outage assertion failure was this:

InnoDB: Assertion failure in thread 139838283589376 in file
log/log0recv.c line 1094
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) ==
dict_table_is_comp(index->table)

Here is the new assertion failure:

111201 16:45:00 InnoDB: Assertion failure in thread 4500 in
file fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >=
FIL_PAGE_DATA

As you can see, they are completely different, yet both due to similar, catastrophic events:

In fact, there are a number of relevant/useful snippets from this error log (in case you see one or more of these):

InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Warning: a page in the doublewrite buffer is not within space
InnoDB: bounds; space id 0 page number 2248, page 22 in doublewrite buf.
InnoDB: Warning: a page in the doublewrite buffer is not within space
InnoDB: bounds; space id 0 page number 1775, page 31 in doublewrite buf.
...
111201 16:45:00 InnoDB: ERROR: We were only able to scan the log up to
InnoDB: 670800896, but a checkpoint was at 670801309.
InnoDB: It is possible that the database is now corrupt!
111201 16:45:00 InnoDB: Error: page 7 log sequence number 924466223
InnoDB: is in the future! Current system log sequence number 670801309.
...
111201 16:45:00 InnoDB: Assertion failure in thread 4500 in file
fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset
>= FIL_PAGE_DATA
...
111201 16:45:00 - mysqld got exception 0xc0000005 ;
...
000000013F6A4C6F mysqld.exe!my_osmaperr()
000000013F69B9A0 mysqld.exe!my_osmaperr()
000000013F4727D6 mysqld.exe!?ha_initialize_handlerton@@
YAHPEAUst_plugin_int@@@Z()
000000013F46C172 mysqld.exe!?plugin_lock_by_name@@
YAPEAUst_plugin_int@@PEAVTHD@@PEBUst_mysql_lex_string@@H@Z()
000000013F4713E9 mysqld.exe!?plugin_init@@YAHPEAHPEAPEADH@Z()
000000013F45BDA7 mysqld.exe!handle_shutdown()
000000013F45C91A mysqld.exe!?win_main@@YAHHPEAPEAD@Z()
000000013F45CD90 mysqld.exe!?mysql_service@@YAHPEAX@Z()
000000013F45D0A3 mysqld.exe!?mysqld_main@@YAHHPEAPEAD@Z()
000000013F7FBB27 mysqld.exe!my_mb_ctype_mb()
00000000777F652D kernel32.dll!BaseThreadInitThunk()
0000000077A2C521 ntdll.dll!RtlUserThreadStart()

As you can see, the corruption was severe … so severe that InnoDB wouldn’t even start with innodb_force_recovery set to 6!

As discussed in the previous post, setting innodb_doublewrite=1, innodb_flush_log_at_trx_commit=1, sync_binlog=1, and having a battery backed cache can be the best bets against such issues.

However, I will say that in this case, innodb_flush_log_at_trx_commit=1 and innodb_doublewrite=1 were both set, and binary logging had been disabled, leaving only the battery backed cache to be desired.

Luckily, this was a test environment (and recovery from a backup was possible). Otherwise, using the InnoDB Recovery Tool would be necessary, which can be an undertaking.

Morals of the story: take regular backups (as always) and invest in a battery backed cache.


rpm -qa | grep MySQL

MySQL-Cluster-gpl-debuginfo-7.1.17-1.el6.i686
MySQL-Cluster-gpl-client-7.1.17-1.el6.i686
MySQL-Cluster-gpl-clusterj-7.1.17-1.el6.i686
MySQL-Cluster-gpl-test-7.1.17-1.el6.i686
MySQL-Cluster-gpl-server-7.1.17-1.el6.i686
MySQL-Cluster-gpl-devel-7.1.17-1.el6.i686
MySQL-Cluster-gpl-management-7.1.17-1.el6.i686
MySQL-Cluster-gpl-extra-7.1.17-1.el6.i686
MySQL-Cluster-gpl-storage-7.1.17-1.el6.i686

Just so this can be a tiny bit of a more real world example, I also installed an external node in a remote (out of the subnet/datacenter) location.


[root@remote_cluster src]# rpm -ihv MySQL-Cluster-gpl-storage-*.rpm

OK software install is straight forward and easy, but what do you do now ?

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-configuration.html) on how to configure the cluster are straight forward and very easy to follow.

Based on that documentation, I have set all the ips in all the related configuration files ...

My Config file looks like this after removal of comments. I have a local node and a remote node for this simple example.


#/var/lib/mysql-cluster/config.ini 
[ndb_mgmd]
# Management process options:
hostname=10.132.241.18                  # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files
NodeId=1

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=4                            # Number of replicas
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files
DataMemory=80M                # How much memory to allocate for data storage
IndexMemory=18M               # How much memory to allocate for index storage

[ndbd]
hostname=mysql_demo.localdomain
NodeId=3

[ndbd]
hostname=10.159.37.130
NodeId=4

[mysqld]
Nodeid=10

[mysqld]
Nodeid=30

[mysqld]
Nodeid=40

Nodes... Start your engines..... ok so really, lets get it started...

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-first-start.html) on how to start it up for the 1st time..

On Localhost


/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.56 ndb-7.1.17

On Localhost / Data Node


/usr/sbin/ndbd
2011-12-08 12:21:16 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2011-12-08 12:21:16 [ndbd] INFO     -- Angel allocated nodeid: 3

On remote node :


./ndbd
2011-12-08 12:25:39 [ndbd] INFO     -- Angel connected to '10.132.241.18:1186'
2011-12-08 12:25:40 [ndbd] INFO     -- Angel allocated nodeid: 6

On Localhost Start the sql server and check the nodes.


# /etc/init.d/mysql start

# /usr/local/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration

---------------------
[ndbd(NDB)]     4 node(s)
id=3    @127.0.0.1  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=4 (not connected, accepting connect from mysql_demo.localdomain)
id=5    @10.159.37.130  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=6    @10.159.37.130  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.132.241.18  (mysql-5.1.56 ndb-7.1.17)

[mysqld(API)]   3 node(s)
id=10 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)

OK lets log in and test it ?


# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-ndb-7.1.17-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select VERSION();
+-------------------------------+
| VERSION() |
+-------------------------------+
| 5.1.56-ndb-7.1.17-cluster-gpl |
+-------------------------------+
1 row in set (0.00 sec)

Of course if you need to stop and restart (MySQL Documentation: http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-install-shutdown-restart.html) a cluster, it is a simple process.


/usr/local/bin/ndb_mgm -e shutdown
/etc/init.d/mysql stop

Others posts on cluster installs:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-installation.html

http://alexyu.se/comment/37

http://dbperf.wordpress.com/2011/03/04/mysql-cluster-setup-and-replication-between-ndb-and-non-ndb-engines-2/

Mine:
Part 1 -- The Install

Part 2 -- Testing Cluster more and Replication Setup

In this episode we talk about the MySQL Master High Availability Agent, aka MHA.

News

There will be a "MySQL and Friends" devroom at FOSDEM 2012. The dev room is scheduled for Sunday 5th February 2012, whole day. If you are interested to give a talk, please submit it before December 26th through the submission form. You can read the original announcement by Frédéric Descamps.

Meet the MySQL Experts Podcast

Funding for BlazeMeter and Digital Reasoning. Red Hat goes unstructured. And more.

# BlazeMeter announced $1.2m in Series A funding and launched the a cloud service for load and performance testing.

# Digital Reasoning announced a second round of funding to help develop its Hadoop-based analytics offering.

# Red Hat announced the availability of Red Hat Storage Software Appliance, based on its recent acquisition of Gluster.

# Red Hat also announced the general availability of Red Hat Enterprise Linux 6.2.

# Jaspersoft released Jaspersoft 4.5, delivering drag-and-drop analytics and reporting on Apache Hadoop, NoSQL and analytic databases.

# Jaspersoft also delivered a second-generation native connector to MongoDB.

# CloudBees announced the availability of Jenkins Enterprise by CloudBees providing support and enhanced capabilities for the Jenkins Continuous Integration platform.

# Diaspora* is back in action, and outlined its plans.

# Talend announced that Bi3 Solutions has embedded Talend Integration Suite inside its Software-as-a-Service platform.

# DataStax announced new versions of Apache Cassandra, DataStax Community, and DataStax Enterprise.

# The H reported that Microsoft’s Windows Store agreement has open source exception.

# Black Duck Software announced the release of Export 6.0.

# Antelink launched SourceSquare, a free open source scanning engine.

Keith Larson and I will be presenting MySQL for Oracle DBAs or How to Speak MySQL for beginners at Collaborate 12 in April at the Mandalay Bay hotel in Las Vegas.

One of the more common questions I get asked is which Linux distribution I would use for a MySQL database server. Bearing the responsibility for someone else’s success means I should advise something that is stable, reliable, easy to manage and has plenty of resources available online. It should also allow running MySQL without too much hassle. Unless there are individual circumstances, it actually makes the decision quite easy.

There are probably only a few distributions, which can be considered: CentOS, Debian, RedHat Enterprise Linux, SuSE Linux and Ubuntu. Of course CentOS and Ubuntu derive from RedHat and Debian respectively, but their install bases are large enough to mention them separately. Running MySQL won’t be much different whether one or another distribution is used. All use common Linux kernel – the heart of Linux operating system – which in principle will behave the same way in all cases. The kernel versions may be different in different distributions as for example RedHat is very conservative in that area for the sake of compatibility with drivers and applications throughout a release lifetime, which can be even several years. Newer kernel versions may carry new features or slight performance improvements, however these days it is rarely important to MySQL users. If it is to you, then you probably did your own research and benchmarks already and this post is not for you.

For most people, managing a database server during its lifetime comes down to this rather boring process – install, configure, tune, start/stop MYSQL, upgrade MySQL, 10x start/stop MySQL, upgrade MySQL, downgrade MySQL, upgrade MySQL, install a security fix for something, 20x start/stop MySQL, upgrade MySQL, … . Restarting and changing MySQL version might just be the most critical operations you ever do once server is moved into production. Success and efficiency of these operations may directly impact service availability, which means that what you could wish for the most are quick and problem free restarts and upgrades (or downgrades).

From my experience, and I have done thousands upgrades and downgrades in my life, the least number of problems come from RPM packages available in RedHat, CentOS and SuSE. In fact, I cannot recall encountering any serious problem with that package management system. Moreover, I have not seen broken systems, where installing or updating a RPM package would be impossible without resolving tons of problems first. It can obviously mean that RPM has flaws and does not verify consistency very carefully, but it never turned out to be any problem.

On the other end there are Debian and Ubuntu. Both use tool called dpkg for package management. There isn’t a month that I log in to a system based on either distribution where there are no issues with packages consistency. Unfinished installations, unresolved conflicts are so common that it’s just beyond simple negligence. The packaging system is just not robust enough. Another problem is that one broken package may block you from installing or uninstalling anything else. Imagine that someone left system in such shape, you prepared for downtime, stopped MySQL and… error – text editor has not been properly installed, so you cannot upgrade MySQL either until the problem is fixed. In a stressful situation when downtime clock ticks – annoying at best. Resolving problems can easily lead to unexpected consequences. Here’s a different scenario:
# apt-get install binutils E: dpkg was interrupted, you must manually run 'dpkg --configure -a' to correct the problem.

While everyone should stop and check what this means instead of following the advice blindly, many still would just do as they were instructed. In this particular case the problems were with MySQL packages upgraded from 5.1 to 5.5. The former were not fully uninstalled, the latter were in half-configured state. Obviously binutils has nothing to do with MySQL, but it would not install anyway. The larger problem – chances are that fixing this with ‘dpkg –configure -a’ would cause MySQL to shut down. The MySQL package scripts for Debian force MySQL stop/start upon completion and unlike RPM, dpkg does not have any opt-out option.

More problems can come from the standard Debian init (startup) script for MySQL. By default it not only starts database, but also updates system tables (if needed), scans all tables for consistency problems, etc. I mean, that works great for a computer at home or a tiny and unimportant system, but any of these operations may have severe consequences on a large production system. This is why all of this extra functionality was stripped from Percona Server. This behavior actually appears to be a general problem with Debian – it wants to be smarter than you. This might work for desktops, but anywhere else it is plain stupid and makes you waste time on outsmarting a wise-ass system. Of course I do know some people who like such approach, but he is Belgian and they were unable to form a government for 541 days… (congrats that you finally made it this week, btw! )

The init scripts for RedHat, CentOS and SuSE are simple and do only what’s required of them – stop or start MySQL. No problems there.

It’s now clear that I never recommend Debian or Ubuntu, because I do not like some of the “mechanics” and I feel people are generally safer if they do not use any of these two. With the choice left between RedHat/CentOS and SuSE, I lean towards the former. Why? RedHat and CentOS are the only platforms getting packages from all MySQL and MySQL-fork vendors – Oracle, MariaDB and Percona. By my observations RedHat and CentOS are also much more frequently used with MySQL, so there will be more resources available online.

But if you are a skilled systems administrator or your company hires one, then you could use pretty much anything, including my favorites such as Slackware and Gentoo.

In the beginning I mentioned that individual circumstances may influence the decision. One example of such specific case, which limits your options, is for example a requirement for commercial support for the operating system. You will have to choose between RedHat or SuSE. Another such case can be related to hardware. Any newly released component may simply lack support from the Linux kernel, but this can also be the case for any high-end/enterprise class equipment. Hardware vendors may release Linux drivers on their own, but often only for very specific Linux distributions.

In case you haven’t seen it, the Facebook team held a Tech Talk on Monday focusing on MySQL and HBase at Scale.

You can view the recording here.

Facebook relies on MySQL for it users database, handling users interactions such as likes, shares, status updates, events, notifications…etc, and is running massive operations, especially considering (as Domas Mituzas from the database performance team mentioned) the time and scope of “social” data. Domas shared some pretty impressive numbers including:

800M active monthly users
500M active daily users
350M mobile users
7M apps and web sites integrated via platform
60M queries per second
4M row changes per second

The team also discussed why they believe MySQL is a better choice for them than other NoSQL solutions, and shared a lot of great information about what they’re working on to scale MySQL to the levels they need.

Thanks to the Facebook team for sharing!

Just in case you've missed my initial post last week (there were some problems on the Planet MySQL site, and it did not appear correctly)..

I'm looking for Test Scenarios to extend our Benchmark Workloads to run on MySQL Server in way to improve MySQL performance and find the most optimal solutions to your problems.. - More closed to production workloads we're testing, better MySQL will keep your production workloads in the future! easy, no?.. ;-)

Well, it's just a reminder from my initial post (and I cannot believe you have nothing to share ;-))
(hoping there will be no problems on the Planet MySQL site this time ;-))

Rgds,
-Dimitri

As you may or may not know, MariaDB has supported microseconds in version 5.3 for several months already.

And now MySQL has added microsecond support in version 5.6 (5.6.4, specifically).

For more details on MariaDB microseconds, please refer to this page:

http://kb.askmonty.org/en/microseconds-in-mariadb

And for more details on MySQL microseconds, see “Fractional Seconds Handling” on the following page:

http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html

From what I can tell, the implementation looks largely to be the same (at least from a user perspective), in that if the fractional part is given, it must be an integer number 0 to 6. And if no precision is given, the default is 0, for backward compatibility.

MySQL 5.6 is still in the “Development” phase, and MariaDB 5.3 is in the “Beta” phase, so I’d expect MariaDB 5.3 to be GA first, but that’s just a guesstimate on my part.

Slaves should 'reflect' information about replicated epochs they have applied
Applied epoch numbers should be included in the Slave Binlog events returning to the originating cluster, in a Binlog position corresponding to the commit time of the replicated epoch transaction relative to Slave local transactions.
Masters should maintain a maximum replicated epoch
A cluster should use the reflected epoch information to track which of its epochs has been applied by a Slave cluster. This will be the maximum of all epochs applied by the Slave.
Masters should track commit-time epoch per row
To allow per-row detection of conflicts
Masters should track commit-authorship per row
To differentiate recent epochs due to replication or conflicting activity.

'Reflecting' epoch information and maintaining the maximum replicated epoch

Every epoch transaction in the Binlog contains a special WRITE_ROW event on the mysql.ndb_apply_status table which carries the epoch transaction's epoch number. This is designed to give an atomically consistent way to determine a Slave cluster's position relative to a Master cluster. Normally these WRITE_ROW events are applied by the Slave but not logged in the Slave's Binlog, even when --log-slave-updates is ON. A new MySQLD option, --ndb-log-apply-status causes WRITE_ROW events applied to the mysql.ndb_apply_status table to be binlogged at a Slave, even when --log-slave-updates is OFF. These events are logged with the ServerId of the Slave MySQLD, so that they can be applied on the Master, but will not loop infinitely.

Allowing this applied epoch information to propagate through a Slave Cluster has the following effects :

Downstream Clusters become aware of their position relative to all upstream Master clusters, not just their immediate Master cluster.
They gain extra mysql.ndb_apply_status entries for all upstream Masters.
Circularly replicating clusters become aware of which of their epochs, and epoch transactions, have been applied to all clusters in the circle.
They gain extra mysql.ndb_apply_status entries for all Binlogging MySQLDs in the loop

Effect 1 is useful for replication failover with more than two replication-chained clusters where an intermediate cluster is being routed-around (A->B->C) -> (A->C). Cluster C knows the correct Binlog file and position to resume from on A, without consulting B.

Effect 2 could be used to allow clients to wait until their writes have been fully replicated and are globally visible, a kind of synchronous replication. More relevantly, effect 2 allows us to maintain a maximum replicated epoch value for detecting conflicts.

The visible result of using --ndb-log-apply-status on a Slave is that the mysql.ndb_apply_status table on the Master contains extra entries for the Binlogging MySQLDs attached to its Cluster. The maximum replicated epoch is the maximum of these epoch values.

 
     Cluster 1 Epoch transactions in flight in
           a circular configuration
          (Ignoring Cluster 2 epochs)

                             39       38       37 
                       ->---->----->----->----->--
                      /                           \ (Queued epochs 36-26)
            Cluster 1                             Cluster 2
(Queued epochs 23,24) \                           /
                       -<---<------<----<----<----
                            25       26       27

Current epoch = 40
Max replicated epoch = 22

A MySQLD acting as a conflict detecting Slave for a cluster needs to know the attached cluster's maximum replicated epoch for conflict detection. On Slave start, before the Slave starts applying replicated changes to the Ndb storage engine, it scans the mysql.ndb_apply_status table to find the highest reflected epoch value. Rows in mysql.ndb_apply_status with server ids in the CHANGE MASTER TO IGNORE_SERVER_IDS list are considered to be local servers, as well as the Slave's own server id, and the maximum replicated epoch is the maximum epoch value from these rows.


@ Slave start

   max_replicated_epoch = SELECT MAX(epoch)
                            FROM mysql.ndb_apply_status
                           WHERE server_id IN @@IGNORE_SERVER_IDS;

Once the Max_replicated_epoch has been initialised at slave start, it is updated as each reflected epoch event (WRITE_ROW event to mysql.ndb_apply_status) arrives and is processed by the Slave SQL thread. The current Max_replicated_epoch can be seen by issuing the command SHOW STATUS LIKE 'Ndb_slave_max_replicated_epoch';. Note that this is really just a cached copy of the current result of the SELECT MAX(epoch) query from above. One subtlety is that the max_replicated_epoch is only changed when the Slave commits an epoch transaction, as it is only at this point that we know for sure that any event committed on the other cluster before the replicated epoch was applied has been handled.

Per row last-modified epoch storage

Each row stored in Ndb has a built-in hidden metadata column called NDB$GCI64. This columns stores the epoch number at which the row was last modified. For normal system recovery purposes, only the top 32 bits of the 64 bit epoch, called the Global Checkpoint Index or GCI are used. NDB$EPOCH needs further bits to be stored per-row. Epoch values only use a few of the bits in the bottom 32 bits of the epoch, so by default 6 extra bits per row are used to enable a full 64 bit epoch to be stored for each row. The actual number of bits used can be controlled by a parameter to NDB$EPOCH. Where some epoch is not fully expressible in the number of bits available, the bottom 32 bits are saturated, which again errs on the side of safety, potentially causing false conflicts, but ensuring no real conflicts are missed. The ndb_select_all tool has a --gci64 option which shows each row's stored epoch value.

A conflict detecting slave detects conflicts between transactions already committed, whose rows have their commit-time epoch numbers, and incoming operations in an epoch transaction, which are considered to have been committed at the epoch given by the current Maximum Replicated Epoch. An incoming operation is considered to be in-conflict if the row it affects has a last-committed epoch that is greater than the current Maximum Replicated Epoch.

  in_conflict = (ndb_gci64 > max_replicated_epoch)

In other words, at the time the change was committed on the other Cluster, that other Cluster was only aware of our changes as-of our epoch (max_replicated_epoch). Therefore it was unaware of any changes committed in more recent epochs. If the row being changed has been locally modified since that epoch then there have been concurrent modifications and a conflict has been discovered.

Note that this mechanism is purely based on monitoring serialisation of updates to rows. No semantic understanding of row data, or the meaning of applied changes is attempted. Even if both clusters update some row to contain exactly the same value it will be considered to be a conflict, as the updates were not serialised with respect to each other.

Per row hidden Author metacolumn

One advantage of reusing the row's last-modified epoch number for conflict detection is that it is automatically set on every commit. However the downside is that when a replicated modification is found to not be in conflict, and is applied, the row's epoch is automatically set to the current value at commit time as normal. By definition, the current epoch value is always greater than the maximum replicated epoch, and so if a further replicated modification to the same row were to arrive, it would find the row's epoch to be higher than the current maximum replicated epoch, and detect a false conflict.

In theory we could consider the current maximum replicated epoch to be the row's commit time epoch, but as the per-row epoch is used for other more critical DB recovery purposes it's not safe to abuse it in this way. Instead we use the observation that if we found a previous row update from some other cluster to be not-in-conflict, then further updates from it are also not-in-conflict.

To detect this, a new hidden metadata column is introduced called NDB$AUTHOR. This column is set to zero when a row is modified by any unmodified NdbApi client, including MySQLD, but when a row is modified by the MySQLD Slave SQL thread, it is set to one. More generally, NDB$AUTHOR could be set to a non-zero identifier of which other cluster sourced an accepted change. Just setting to one limits us to having one other cluster originating potentially conflicting changes. The ndb_select_all tool has a --author option which shows each row's stored Author value.

By extending the conflict detecting function to examine the NDB$AUTHOR value, we avoid the problem of falsely detecting conflicts when applied consecutive replicated changes.

  in_conflict = (ndb$author != change_author) && (ndb_gci64 > max_replicated_epoch)

We are currently just using 1 to mean 'other author', so this simplifies to :


  in_conflict = (ndb$author != 1) && (ndb_gci64 > max_replicated_epoch)

              = (ndb$author == 0) && (ndb_gci64 > max_replicated_epoch)

This conflict detection function is encoded in an Ndb interpreted program and attached to the replicated DELETE and UPDATE NdbApi operations so that it can be quickly and atomically executed at the Ndb data nodes as a predicate prior to applying the operation.

Ndb binlog row event ordering and false conflicts

The happened-before relationship between reflected epoch events (WRITE_ROW to mysql.ndb_apply_status) and incoming row events is used to determine whether a conflict has occurred. As described in the last post, Ndb offers limited ordering guarantees on the row events within an epoch transaction. The only guarantee is that multiple changes to the same row will be recorded in the order they committed. This implies that the relative ordering of the reflected epoch WRITE_ROW event, on some row in mysql.ndb_apply_status, and other row events on other tables sharing the same epoch transaction is meaningless. The only ordering guarantees between different rows exist at epoch boundaries.

This means that if we see a reflected epoch WRITE_ROW event somewhere in replicated epoch j, then we can only safely assume that this happened before incoming row events in epoch j+1 and later. The row events appearing before and after the reflected epoch WRITE_ROW event in epoch j may have committed before or after the reflected epoch event.

The relaxed relative ordering gives us reduced precision in determining happened-before, and to be safe, we must err on the side of assuming that a conflict exists rather than that it does not. Consider a Master committing a change to row X, recorded in epoch N. This is then applied on the Slave in Slave epoch S. If the Slave then commits a local change affecting the same row X in the same epoch S, this will be returned to the Master in the same Slave epoch transaction, and the Master will be unable to determine whether it occurred before or after it's original write to X, so must assume that it occurred before and is therefore in conflict. If the Slave had committed its change in epoch S+1 or later, the happened-before relationship would be clear and the change would not be considered in conflict.

These potential false conflicts are the price paid here for the lack of fine grained event ordering in the Ndb Binlog.

I'm lost

There's been a lot of information, or at least a lot of words. Let's summarise how NDB$EPOCH and NDB$EPOCH_TRANS detect row conflicts by following

@Cluster A
Transactions modify rows, automatically setting their hidden NDB$GCI64 column to the current epoch and their NDB$AUTHOR column to 0

Binlogging MySQLDs record modified rows in epoch transactions in their Binlogs, together with MySQLD generated mysql.ndb_apply_status WRITE_ROW events
@Cluster B
Slave MySQLDs apply replicated epoch transactions along with their generated mysql.ndb_apply_status WRITE_ROW events

Other clients of Cluster B commit transactions against the same data.

Binlogging MySQLDs 'reflect' the applied-replicated epoch information by recording the mysql.ndb_apply_status WRITE_ROW events in their Binlogs as a result of --ndb-log-apply-status.

Binlogging MySQLDs also record the row changes made by local clients.
@Cluster A
Slave MySQLDs track the incoming reflected epoch mysql.ndb_apply_status WRITE_ROW events to maintain their ndb_slave_max_replicated_epoch variables

Slave MySQLDs attach NdbApi interpreted programs to UPDATE and DELETE operations as they are applied to the database, comparing the row's stored NDB$GCI64 and NDB$AUTHOR columns with constant values supplied in the program.

If there are no conflicts, the UPDATE and DELETE operations are applied, and the row's NDB$AUTHOR columns are set to one indicating a successful Slave modification

If there are conflicts then conflict handling for the conflicting rows begins.

Now does that make any sense? Assuming it does, then next we look at how detected conflicts are handled.

Once again, another wordy endurance test and we're not finished. Surely the end must be near?

We welcome everyone to Amsterdam where on Friday at 16:00 CET we will be giving a talk together with SpamExperts at the Open Source Conference 2011. The talk will be about MariaDB and how SpamExperts successfully overcame their problems with MySQL by migrating to MariaDB. The talk will focus on the MariaDB features that differentiate it from vanilla MySQL and an in-depth case study on SpamExperts and the problems they faced when deciding to migrate to MariaDB and a detailed overview of the actual migration process. For more information about the conference, check out their website: http://www.opensourceconference.nl/

If you are close to Washington DC, you should plan to attend our Percona Live event on January 11th at the DC Convention Center. We have three tracks of expert speakers on core MySQL-related topics.

This event will not be as large as some of the other events we’ve run, with a capacity of about 200. Frankly, we are targeting this to more local attendees. We know that a lot of people simply can’t travel out to Santa Clara for the main MySQL conference every year, and we want to make sure there is great technical content for them. This is part of our idea to host smaller regional events periodically to try to make sure top-quality MySQL events are more accessible to all, not just the privileged.

We have the entire hallway, with a number of large rooms, immediately off the Metro stop. This event couldn’t be easier to attend if you’re inside the Beltway. We are right outside the Metro doors. If you’re from outside the area, we suggest taking the Metro in for the day. Detailed travel instructions and hotel suggestions (if you need them) are listed on the event venue page. (I’ve personally stayed in the hotels listed there and found them to be great.)

This is a great opportunity to learn from experts, recruit, and network in Washington DC. I personally will be there because I’m close and this is something of a pet project for me. I hope to see you there, and would appreciate it if you tell your friends about the event.

  Incoming independent transactions
   affecting multiple rows

     T3         T4         T7
     T1         T2         T5

      |         |          |
      V         V          V

   --------  --------  --------
   |  1   |  |  2   |  |  3   |
   |  TC  |  |  TC  |  |  TC  |   Data nodes with multiple
   |      |--|      |--|      |   transaction coordinators
   |------|  |------|  |------|   acting on data stored in
   |      |  |      |  |      |       different nodes
   | DATA |  | DATA |  | DATA |
   --------  --------  --------

      |         |          |
      V         V          V

     t4        t4          t3
     t1        t7          t2
     t2        t1          t7
               t5

   Outgoing row change event
    streams by causing
       transaction

These row event streams are generated independently by each data node in a cluster, but to be useful they need to be correlated together. For system recovery from a crash, the data nodes need to recover to a cluster-wide consistent state. A state which contains only whole transactions, and a state which, logically at least, existed at some point in time. This correlation could be done by an analysis of the transaction ids and row dependencies of each recorded row change to determine a valid order for the merged event streams, but this would add significant overhead. Instead, the Cluster uses a distributed logical clock known as the epoch to group large sets of committed transactions together.

Each epoch contains zero or more committed transactions. Each committed transaction is in only one epoch. The epoch clock advances periodically, every 100 milliseconds by default. When it is time for a new epoch to start, a distributed protocol known as the Global Commit Protocol (GCP) results in all of the transaction coordinators in the Cluster agreeing on a point of time in the flow of committing transactions at which to change epoch. This epoch boundary, between the commit of the last transaction in epoch n, and the commit of the first transaction in epoch n+1, is a cluster-wide consistent point in time. Obtaining this consistent point in time requires cluster-wide synchronisation, between all transaction coordinators, but it need only happen periodically.

Furthermore, each node ensures that the all events for epoch n are published before any events for epoch n+1 are published. Effectively the event streams are sorted by epoch number, and the first time a new epoch is encountered signifies a precise epoch boundary.

 Incoming independent transactions

     T3         T4         T7
     T1         T2         T5

      |         |          |
      V         V          V

   --------  --------  --------
   |  1   |  |  2   |  |  3   |
   |  TC  |  |  TC  |  |  TC  |   Data nodes with multiple
   |      |--|      |--|      |   transaction coordinators
   |------|  |------|  |------|   acting on data stored in
   |      |  |      |  |      |      different nodes
   | DATA |  | DATA |  | DATA |
   --------  --------  --------

      |         |          |
      V         V          V

    t4(22)    t4(22)      t3(22)            Epoch 22
    ......    ......      ......
    t1(23)    t7(23)      t2(23)            Epoch 23
    t2(23)    t1(23)      t7(23)
              ......
              t5(24)                        Epoch 24

    Outgoing row change event
    streams by causing transaction
    with epoch numbers in ()

When these independent streams are merge-sorted by epoch number we get a unified change stream. Multiple possible orderings can result.
One Partial ordering is shown here :

      Events      Transactions
                 contained in epoch

     t4(22)
     t4(22)      {T4,T3}
     t3(22)
   
     ......

     t1(23)
     t2(23)
     t7(23)
     t1(23)      {T1, T2, T7}
     t2(23)
     t7(23)

     ......

     t5(24)      {T5}

Note that we can state from this that T4 -> T1 (Happened before), and T1 -> T5. However we cannot say whether T4 -> T3 or T3 -> T4. In epoch 23 we see that the row events resulting from T1, T2 and T7 are interleaved.

Epoch boundaries act as markers in the flow of row events generated by each node, which are then used as consistent points to recover to. Epoch boundaries also allow a single system wide unified transaction log to be generated from each node's row change stream, by merge-sorting the per-node row change streams by epoch number. Note that the order of events within an epoch is still not tightly constrained. As concurrent transactions can only interact via row locks, the order of events on a single row (Table and Primary key value) signifies transaction commit order, but there is by definition no order between transactions affecting independent row sets.

To record a Binlog of Ndb row changes, MySQLD listens to the row change streams arriving from each data node, and merge-sorts them them by epoch into a single, epoch-ordered stream. When all events for a given epoch have been received, MySQLD records a single Binlog transaction containing all row events for that epoch. This Binlog transaction is referred to as an 'Epoch transaction' as it describes all row changes that occurred in an epoch.

Epoch transactions in the Binlog

Epoch transactions in the Binlog have some interesting properties :

Efficiency : They can be considered a kind of Binlog group commit, where multiple user transactions are recorded in one Binlog (epoch) transaction. As an epoch normally contains 100 milliseconds of row changes from a cluster, this is a significant amortisation.
Consistency : Each epoch transaction contains the row operations which occurred when moving the cluster from epoch boundary consistent state A to epoch boundary consistent state B
Therefore, when applied as a transaction by a slave, the slave will atomically move from consistent state A to consistent state B
Inter-epoch ordering : Any row event recorded in epoch n+1 logically happened after every row event in epoch n
Intra-epoch disorder : Any two row events recorded in epoch n, affecting different rows, may have happened in any order.
Intra-epoch key-order : Any two row events recorded in epoch n, affecting the same row, happened in the order they are recorded.

The ordering properties show that epochs give only a partial order, enough to subdivide the row change streams into self-consistent chunks. Within an epoch, row changes may be interleaved in any way, except that multiple changes to the same row will be recorded in the order they were committed.

Each epoch transaction contains the row changes for a particular epoch, and that information is recorded in the epoch transaction itself, as an extra WRITE_ROW event on a system table called mysql.ndb_apply_status. This WRITE_ROW event contains the binlogging MySQLD's server id and the epoch number. This event is added so that it will be atomically applied by the Slave along with the rest of the row changes in the epoch transaction, giving an atomically reliable indicator of the replication 'position' of the Slave relative to the Master Cluster in terms of epoch number. As the epoch number is abstracted from the details of a particular Master MySQLD's binlog files and offsets, it can be used to failover to an alternative Master.

We can visualise a MySQL Cluster Binlog as looking something like this. Each Binlog transaction contains one 'artificially generated' WRITE_ROW event at the start, and then RBR row events for all row changes that occurred in that epoch.

    BEGIN
    WRITE_ROW mysql.ndb_apply_status server_id=4, epoch=6998
    WRITE_ROW ...
    UPDATE_ROW ...
    DELETE_ROW ...
    ...
    COMMIT # Consistent state of the database

    BEGIN
    WRITE_ROW mysql.ndb_apply_status server_id=4, epoch=6999
    ...
    COMMIT # Consistent state of the database

    BEGIN
    WRITE_ROW mysql.ndb_apply_status server_id=4, epoch=7000
    ...
    COMMIT # Consistent state of the database
    ...

A series of epoch transactions, each with a special WRITE_ROW event for recording the epoch on the Slave. You can see this structure using the mysqlbinlog tool with the --verbose option.

Rows tagged with last-commit epoch

Each row in a MySQL Cluster stores a hidden metadata column which contains the epoch at which a write to the row was last committed. This information is used internally by the Cluster during node recovery and other operations. The ndb_select_all tool can be used to see the epoch numbers for rows in a table by supplying the --gci or --gci64 options. Note that the per-row epoch is not a row version, as two updates to a row in reasonably quick succession will have the same commit epoch.

Epochs and eventual consistency

Reviewing epochs from the point of view of my previous posts on eventual consistency we see that :

Epochs provide an incrementing logical clock
Epochs are recorded in the Binlog, and therefore shipped to Slaves
Epoch boundaries imply happened-before relationships between events before and after them in the Binlog

The properties mean that epochs are almost perfect for monitoring conflict windows in an active-active circular replication setup, with only a few enhancements required.

I'll describe these enhancements in the next post.

Included in all MySQL commercial editions subscriptions, Oracle Premier Support for MySQL can help you make the most of your MySQL deployments.

Organizations choose to purchase technical support from software vendors for various reasons, many seeing it as an insurance policy. Indeed, much as they would not drive a car without insurance, IT managers consider it good practice to be able to call on the company behind the software they are using should they encounter any issue.

However, Oracle Premier Support for MySQL offers benefits far beyond "insurance", value you can leverage even if you never experience problems.

Get our new white paper “Oracle Premier Support for MySQL: Rely on The Experts, Get Unique Benefits”, to better understand the value Oracle Premier Support for MySQL represents for your organization.

If you were unable to attend the live webinar (or you want to go back and listen to it again) then it’s now available to view on-line here.

Databases are the center of today’s web and enterprise applications, storing and protecting an organization’s most valuable assets and supporting business-critical applications. Just minutes of downtime can result in significant lost revenue and dissatisfied customers. Ensuring database highly availability is therefore a top priority for any organization. Tune into this webcast to learn more.

The session discusses:

Causes, effect and impact of downtime
Methodologies to map applications to HA solution
Overview of MySQL HA solutions
Operational best practices to ensure business continuity

DrupalDownUnder 2012 will be held in Melbourne Australia 13-15 January. A great event, I’ve been to several of its predecessors. People there don’t care an awful lot for databases, but they do realise that sometimes it’s important to either learn more about it or talk to someone specialised in that field. And when discussing general infrastructure, resilience is quite relevant. Clients want a site to remain up, but keep costs low.

I will teach pre-conference training sessions on the Friday at DDU:

MySQL Query Design ($220 half-day)
MySQL Server Tuning ($220 half-day)

The material is made specific to Drupal developers and users. The query design skills, for instance, will help you with module development and designing Drupal Views. The two half-days can also be booked as a MySQL Training Pack for $395.

On Saturday afternoon in the main conference, I have a session Scaling out your Drupal and Database Infrastructure, Affordably covering the topics of resilience, ease of maintenance, and scaling.

I’m honoured to have been selected to do these sessions, I know there were plenty of submissions from excellent speakers. As with all Drupal conferences, attendees also vote on which submissions they would like to see.

After DDU I’m travelling on to Ballarat for LinuxConfAU 2012, where I’m not speaking in the main program this year, but will have sessions in the “High Availability and Storage” and “Business of Open Source” miniconfs. I’ll do another post on the former – the latter is not related to Open Query.

I’m researching algorithms for automatic fault detection in MySQL (see my previous post for context). I need real-world data samples to test the algorithm. Can you help by sending me a bit of data from your production server?

The end goal is an open-source tool that will be a standard part of a typical MySQL installation. The problem I’m trying to solve for all MySQL users is this: something went wrong, what was it? Most of the time there’s no way to answer that; you have to set up a set of tools and hope you capture enough information to diagnose the problem next time. We need a tool that just runs all the time even when you don’t think anything is going to go wrong.

You can help build this tool. I need samples from a wide variety of healthy and sick servers, both heavily and lightly loaded. I need samples that are between a few hours and a week or so long. Here is a script that will gather what I need:

$ mysqladmin ext -i1 | awk '
	/Queries/{q=$4-qp;qp=$4}
	/Threads_connected/{tc=$4}
	/Threads_running/{print q, tc, $4}'

The output should look something like this:

2147483647   136     7
  798   136     7
  767   134     9
  828   134     7
  683   134     7
  784   135     7
  614   134     7
  108   134    24
  187   134    31
  179   134    28
 1179   134     7
 1151   134     7
 1240   135     7
 1000   135     7

Please save this output to a file, and contact me at moc.anocrep@norab (reversed) if you would like to offer a dataset for us to test on. If you need any help setting up the data collection, you can use the same email. I’d also appreciate if you’d help spread the word about this via Twitter or other means. Thanks very much!

Further Reading:

Today's topic is deployment. It's called one-click deployment for a reason: Developers are lazy.
It's hard to do less than clicking on one button, so that's our goal.

With the growing need for lower time-to-market and faster response to user feedback it is inevitable to not be limited by technical factors (there are enough other obstacles already). The focus lies on reproducible results.

So, what do we need? Actually, not much. Disregarding the tools and practices that build the foundation of agile software development, you only need a central build server. But you've already got that one covered, right?

If you don't, you should get one. It's a huge help to discover errors quickly and be alerted instantly. This usually leads to a shorter time frame until a fix is done. Tests are run continuously and new parts are integrated into the whole code base.

Everything written here is based on Apache ant, so the easy choices here are Hudson and Jenkins. We're focusing on Jenkins here, but we've successfully used some variation of this continuous integration/deployment script on both.

We've also used CruiseControl/phpUnderControl and Atlassian Bamboo in the past for continuous integration, so the deployment parts should work as well.

Back to the hard facts. We're using the Template for Jenkins Jobs for PHP Projects here for our basic checks, so let's assume you got it installed and your build server is working.

The basics

The typical workflow for a developer now consists of these steps:

write some unit tests
write some lines of code to get the tests to green
commit to git/svn, let's call it "release 1.32c"
build server integrates the commit automatically and runs all configured checks
build status is green (hopefully)

There's not much deployment in this, so a few more steps:

after the build is done, a new ant task is started, called "create-package"
developer clicks on "deploy-to-staging" (yes, another ant task)
some 5 minutes later the staging environment has "release 1.32c" installed

How does this work?

There are a few preconditions in our environment that haven't been named yet. Not all of them are hard requirements, but they make some things a lot easier:

all developers have a VM image for this project, based on Ubuntu 11.04
this VM image is personalized to a certain degree (user/hostname)
it is managed by puppet, all developers can edit puppet manifests
the staging environment also consists of 4 VMs
- Ubuntu 11.04 for the 2 frontend nodes
- Ubuntu 10.04 for the 2 backend nodes
- (hindsight is 20/20, we should've used the same version on all 4, but it's not that bad)
the machine that Jenkins is running on is also based on that same development image. This helps fight issues with incompatible versions and build tools.

The frontend nodes use a setup including varnish, nginx, PHP 5.3+APC, ZF 1.11, Dojo 1.6 and Doctrine 2.1.
The backend nodes use MySQL(Master+Slave), Apache Solr, RabbitMQ, memcached and ejabberd.

Back to ant.

The task create-package is building a .deb package that is to be deployed to all the staging hosts.

It basically executes these steps:

git pull
ant phpunit (unit tests, integration tests)
ant clean (remove build artifacts)
ant compilejs (using Google Closure Compiler)
ant compilecss (compiling SASS to CSS)
dh_make (read the Debian New Maintainers' Guide for details)

And that's it.

The manually executed task is called deploy-to-staging and consists of these steps:

ant deb-sign (sign the package with the development team's key, optional)
ant db-staging-up (we're using Liquibase, so roll out the database changes)
copy the .deb to all staging nodes via scp

Here's one caveat: We've set up our staging (and production) nodes to include a directory (like /opt/repository) in /etc/apt/sources.list and puppet periodically checks if there are new packages in this directory, and installs it. Of course you could just call dpkg -i through ssh as well.

So far, so good. After waiting 5mins we've got our "release 1.32c" deployed to all 4 staging nodes and the testers can do their work.

Wasn't that easy?

We're not quite done yet though. The testers really liked release 1.32c and we want to deploy to live now!

So this means:

developer clicks on deploy-to-live (yes, you've guessed it, an ant task)
some 5mins later "release 1.32c" is live

In greater detail:

fetch the latest "known good" debian package from a staging host
ant db-production-up (Liquibase doing it's magic again)
copy the .deb to all production nodes via scp
there's no step 4

If everything went well, you just deployed a new release!

Of course it's not all perfect, there are some disadvantages and shortcomings.

The first thing we noticed is the build time. The build used to take 15 minutes because of massive disk I/O. Mostly all the analysis/metrics tools are to blame, if you have a few of those, every single one has to read and check all of your hundreds of files even if you exclude the external dependencies and libraries (which is a good idea, anyway). We put the files on a ram disk and brought the build from 15 minutes down to 4. Good enough.

The setup is quite complex (as you've seen if you're still reading). It takes a bit to get used to and there are some moving parts. But so far it's working nicely.

Jenkins is a single point of failure. If your build server goes the way of the dodo, it's very hard to get a release going. There's an easy fix, however: Have a second machine ready that's running an identical copy of your Jenkins setup. If your main build server is down you can still use that one then. As your developer VMs are using the same ant build.xml for daily work, even using that as a last resort is possible.

There are also some goodies that aren't crucial, but nice:

everyone can deploy (even the product owner)
30 minutes from bug report to live deploy is not uncommon with this setup and it's still following a fixed procedure
this setup still allows us to do hotfixes (check out the tag deployed to production, do a fix, commit to a branch and HEAD, create .deb, deploy)
Jenkins has some graphing functions, you can do commit stats or graph your Facebook fans

Having talked with developers from other companies there seem to be quite a few similar setups including chef, rake, phing or even make - so you're surely not forced to use ant or a debian-based distribution.

Good luck simplifying your own deployment!

PlanetMySQL Voting: Vote UP / Vote DOWN";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}}}}i:48;a:6:{s:4:"data";s:63:" ";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";s:5:"child";a:3:{s:0:"";a:6:{s:5:"title";a:1:{i:0;a:5:{s:4:"data";s:51:"Making the case for Database Sharding using a Proxy";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"guid";a:1:{i:0;a:5:{s:4:"data";s:32:"http://www.scalebase.com/?p=1822";s:7:"attribs";a:1:{s:0:"";a:1:{s:11:"isPermaLink";s:5:"false";}}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:4:"link";a:1:{i:0;a:5:{s:4:"data";s:77:"http://www.scalebase.com/making-the-case-for-sharding-using-a-database-proxy/";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:11:"description";a:1:{i:0;a:5:{s:4:"data";s:1548:"There are several ways to implement sharding in your application. The first and by far the most popular, is to implement it inside your application. It can be implemented as part of your own Data Access Layer, database driver, or an ORM extension. However, there are many limitations with such implementation, which drove us, at ScaleBase, to look for an alternative architecture. As the above diagram shows, ScaleBase is implemented as a standalone proxy. There are several benefits to using such an architecture. First and foremost, since the sharding logic is not embedded inside the application, third party applications can be used, be it MySQL Workbench, MySQL command line interface or any other third party product. This translates to a huge saving in the day-to-day costs of both developers and system administrators. Backup can be executed via the proxy, and so allows users to consistently backup a sharded environment – not an easy task when sharding is developed internally. Since the application server machines are usually highly utilized (as they should be, to optimize costs), running additional code on application server machines will just slow them down. Running the code on external proxies allows for a more efficient division of tasks between the servers, and allows requests to be unaffected by data crunching (for instance cross-shard queries) requests. So all in all there are many reasons to run sharding code outside the scope of the application and application server. If you’re interested – we’d love to chat.";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:7:"pubDate";a:1:{i:0;a:5:{s:4:"data";s:31:"Tue, 06 Dec 2011 15:24:31 +0000";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}s:8:"category";a:5:{i:0;a:5:{s:4:"data";s:12:"Architecture";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:1;a:5:{s:4:"data";s:4:"Blog";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:2;a:5:{s:4:"data";s:12:"Company News";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:3;a:5:{s:4:"data";s:4:"News";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}i:4;a:5:{s:4:"data";s:9:"ScaleBase";s:7:"attribs";a:0:{}s:8:"xml_base";s:0:"";s:17:"xml_base_explicit";b:0;s:8:"xml_lang";s:0:"";}}}s:40:"http://purl.org/rss/1.0/modules/content/";a:1:{s:7:"encoded";a:1:{i:0;a:5:{s:4:"data";s:1974:"

There are several ways to implement sharding in your application. The first and by far the most popular, is to implement it inside your application. It can be implemented as part of your own Data Access Layer, database driver, or an ORM extension. However, there are many limitations with such implementation, which drove us, at ScaleBase, to look for an alternative architecture.

As the above diagram shows, ScaleBase is implemented as a standalone proxy. There are several benefits to using such an architecture.

First and foremost, since the sharding logic is not embedded inside the application, third party applications can be used, be it MySQL Workbench, MySQL command line interface or any other third party product. This translates to a huge saving in the day-to-day costs of both developers and system administrators.

Backup can be executed via the proxy, and so allows users to consistently backup a sharded environment – not an easy task when sharding is developed internally.

Since the application server machines are usually highly utilized (as they should be, to optimize costs), running additional code on application server machines will just slow them down. Running the code on external proxies allows for a more efficient division of tasks between the servers, and allows requests to be unaffected by data crunching (for instance cross-shard queries) requests.

So all in all there are many reasons to run sharding code outside the scope of the application and application server. If you’re interested – we’d love to chat.

The headline above has two parts: 1) some letters 2) a question mark. The question mark is the important thing here! It resolves to:

* Can we debug stored programs in MySQL?
* Do you debug stored programs in MySQL?
* How do you debug stored programs in MySQL?

We have tried, but we gave up! In the early stages of our IssueBurner application we actually used Stored Procedures quite a lot. As the complexity increased debugging became so tedious that we recoded the application ‘the traditional way’ coding the logic in the application code instead.

In simple cases you may of course add a little debugging code that writes some data to user variables, temporary tables etc. Next remove them or comment them when you want to turn debugging off. If you want to work more systematically you can add a IN-parameter (debug: integer) to a Stored Procedure paramer-list and CALL mysp(….,0|1) what would then control if the stored program should enter or bypass debugging code when executing.

It should not be like that in the 21st century. Debugging options should be part of the server code itself and there should be an API for it. You should be able to execute line-by-line, set breakpoints, view the status of variables, handlers and cursors for every step just like you would do in a true Integrated Development Environment (IDE). Stored programs is code just like application code is.

Some years ago (I think around 3 years ago) I joined a ‘vote’ on the MySQL website where I had an option to vote for my personal priorities of future MySQL development. An API for debugging stored programs was one of the options listed and I cast all my 10 votes to it.

I would not live without Stored Functions and Events. They will also most often be pretty simple and thus rarely cause problems. Stored Procedures is another matter – they can be and will often need to be very complex to be usable for what you want to achieve (and let us forget Triggers here – they are close to being a joke in MySQL).

I have seen some solutions claiming to be able to debug MySQL Stored Procedures. What I have seen all use some kind of (very simple, really) emulation (like replacing loops with sequential statements, local variables with user variables or they will rewrite the original SP to a ‘cascaded series’ of SP’s calling each others). All what I have seen fails with examples just a little bit more complex than trivial (a few nested loops, some handlers or cursors and similar is usually enough to get weird results).

I write this because we now again had a request for a ‘stored program debugger’ in SQLyog. We would be happy to develop it (it is around 5 years ago we discussed first time I think), but without proper API support from the server we will not even attempt it. Past attempts are not encouraging.

I wonder:
* Does anybody know about any progress with stored programs debugging API/functionalities in MySQL?
* How do you debug your stored programs?