November 2010

MySQL events have been in GA for 2 years.  Events are not used as much as I would like -- often organizations will have cron jobs or scheduled tasks to perform ETL, maintenance and small aggregation tasks.  For example, a website that shows how many users are logged in might update a "cache table" with a count of active sessions every 5 minutes.

Events are great because they are a part of the database.  They only run when the database is running, and they are backed up with the database.  There is no need to store a password in a file or in a cron listing.

Events and replication can be a tricky matter.  Events can change data, and data changes are replicated.  In the example of an event that updates a table with a count of active sessions every 5 minutes, let's assume that the event itself would run on a primary master (write) server.  All slaves (including a secondary master) would receive the table update.

The tricky part is that CREATE EVENT statements are replicated.  There are three ways to avoid an event that changes data running on more than just the master:

0) Do not replicate the CREATE EVENT, or replicate it but then delete the event on any slaves.  I do not like this approach because if you promote one of the slaves to be a master, the event will not be in place.

1) Replicate the CREATE EVENT, knowing that the SLAVESIDE_DISABLED bit will be set even if you do not specify DISABLE ON SLAVE in the CREATE statement.  If you promote a slave to be a master, all you need to do is ALTER EVENT event_name ENABLED for each event that needs to be turned on.  This is the method I prefer, even though it still requires knowledge to turn on the events when a slave is promoted, and turn off events on the old master.

2) Replicate the CREATE EVENT, and enable the event right away, but set the event scheduler off.  This is easier if there are lots of events, because all that needs to be done when a slave is promoted to a master is:

mysql> SET GLOBAL event_scheduler=ON;

However, this means that you cannot have any events running on the slave itself - it is either all the events running, or not.

Part 2 will discuss some specific issues I have run into with events and replication.

So I'm creating a new cluster, and after setting up I needed to get my tokens.  As we're told in http://wiki.apache.org/cassandra/Operations:

Token selection:

Using a strong hash function means RandomPartitioner keys will, on average, be evenly spread across the Token space, but you can still have imbalances if your Tokens do not divide up the range evenly, so you should specify InitialToken to your first nodes as i * (2**127 / N) for i = 0 .. N-1. In Cassandra 0.7, you should specify initial_token in cassandra.yaml.  

Here's a nice simple code snippet to figure out your RandomPartitioner tokens based on the size of your cluster:

#! /usr/bin/python

#nodes = int(raw_input( "How many nodes?" ))

import sys

nodes=int(sys.argv[1])

def tokens(nodes):

    for i in range(1, nodes + 1):

        print (i * (2 ** 127 - 1) / nodes)

This should give something like this:

[root@cassandra06 conf]# ./tokenizer.py 6

28356863910078205288614550619314017621

56713727820156410577229101238628035242

85070591730234615865843651857942052863

113427455640312821154458202477256070484

141784319550391026443072753096570088105

170141183460469231731687303715884105727

Last week my presentation from PICC10 on "How to Stop Hating MySQL" was uploaded to YouTube.  PICC10 (Professional IT Community Conference) was held May 7-8 in New Brunswick, NJ.  The video can be found on youtube at http://www.youtube.com/user/TomOnTime#p/u/6/xPHJnB0COjo and the slides can be downloaded from http://technocation.org/files/doc/2010_05_stophatingmysql.pdf.