mySQL UC 2007 Presentation File

Doing Billions of Queries per Day

Read this doc on Scribd: DVPmysqlucFederation at Flickr: Doing Billions of Queries Per Day

Unique strings in a text field

So, I want to reduce data usage of a text field, by storing unique strings separated by a delimiter. So to do so I came up with this:

Given a table

CREATE TABLE `hmm` (
  `a` int(10) unsigned NOT NULL default '0',
  `b` text NOT NULL,
  `c` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO hmm VALUES (1, 'hmm8',1) ON DUPLICATE KEY UPDATE b = IF(FIND_IN_SET('hmm8', b)>0, b, CONCAT_WS(',',b, 'hmm8')), c = c+1;

SELECT * FROM hmm;
+---+--------------------------+---+
| a | b                        | c |
+---+--------------------------+---+
| 1 | hmm4,hmm5,hmm6,hmm7,hmm8 | 7 |
+---+--------------------------+---+

Let's break apart the INSERT statement, if column 'a' produces a duplicate ON DUPLICATE KEY UPDATE will issue an IF statement

FIND_IN_SET will return the position of the found string. The logic of IF say if FIND_IN_SET is is greater then 0 i.e. the string was found, update column b with b itself (do nothing) else concat the column with the column contents and the new string separated by a comma. Additionally in all cases increment column c by 1.

Why do it this way? Well I removed the need to do a SELECT and provided a solution to store distinct text values instead of the raw value, thus saving space and resources on network transfers etc.

A little quick statement that might be useful for others.

Auto-Generated-Consistent-ServerIds

I manage /etc/my.cnf from a template id. Since I use servers in pairs, all master-master setups, I need to ensure that the server id is unique and consistent for the server mysql is running on. Managing the id's is a pain in the ass, especially when servers change roles. For instance rotating a server in from another cluster, or recovering a server from backup.

So, I create a my.cnf.tmpl file and that is deployed to all database instances.

The /etc/init.d/mysql script executes a perl script to generate my.cnf from my.cnf.tmpl right before a start executes.

!/usr/bin/perl -w 
#
#

use strict;


my $HOSTNAME = `hostname`;
chomp($HOSTNAME);

my ($name,$alias,$addrtype,$length,@addrs) = gethostbyname($HOSTNAME);

my ($a,$b,$c,$d) = unpack('C4',$addrs[0]);

my $cnfContents ='';
while(<>) {
    $cnfContents .= $_;
}
$cnfContents =~ s/\%SERVERID\%/$b$c$d/g;
print $cnfContents;

This script gets the IP address of the host, reads stdin and prints out the my.cnf contents, after replacing %SERVERID% with an the last 3 octets of the IP address to ensure that the number fits in the variable size.


in /etc/init.d/mysql this is added

start')
    if test -s $confdir/my.cnf.tmpl
    then
        if test -x $bindir/generateServerID.pl
        then
            cat $confdir/my.cnf.tmpl | $bindir/generateServerID.pl > $confdir/my.cnf
        fi
    fi

So, I'm ensured that my, my.cnf is generated and set to a server of which is the concat of the servers IP address.