Wednesday, 24 January 2007

MyStatsQL - Screen shot of data in graphical format

For anyone interested in what can be done with the stats gathered with MyStatsQL, here is a screen shot. Eventually I will put a package together for download. The different color lines represent different servers, in this case two.

Tuesday, 23 January 2007

MyStatsQL - Easy temporal stats gathering of MySQL server(s)

To make life a little easier at home and work I've put together a simple enough script that is run by cron to tally up statistics like: Innodb_data_reads, Innodb_data_writes, Qcache_hits, Qcache_inserts, Questions, Open_files and Open_tables over time (I sample every hour) with the ability to easily expand and add your own `show status` variables.

Next I'll create some nice graphical interface so it's more obvious how the server or cluster is running. You can grab the script over at my CV hosting. Note that actual client writes in a master <-> master environment would be (writes / number of servers) roughly, very roughly. If you have any ideas for countering this please let me know.


Monday, 22 January 2007

Handling MySQL replication failure gracefully

As any serious MySQL DBA knows, MySQL can and will now and again go out of sync, most of the time it cures itself, but sometimes the worst happens and a slave stops replicating. Yet I bet most apps keep on going with random outcomes, especially if your storing the users sessions in MySQL and have a master<->master setup.

To counter this problem I implemented a quick an easy check. If the check catches a replication failure `touch` a file to the nfs mounted tmp directory that is shared by all nodes.

Each node before executing anything (in index.php) checks for a file called 'halt' in tmp. If found exits and bingo no weirdness, corruption, or wondering why something is there one click, gone the next. It even emails you, and with a simple addition can sms.

mysql_query("show slave status;", CONNECTION);
mail('team@<some url>',
"Error show Slave Status: ".
$fp = fopen('tmp/halt', 'w+');
header('Location: index.php');

Don't forget to remove the 'halt' file after you have the replication under control.

Tuesday, 16 January 2007

Bitmaps | Bit Arrays - Part2

I was going to write an article on wikipedia about bitmaps. But came across this article on Bit Arrays.. ala Bitmaps after a little searching beyond raster graphics. Gotta love that wikipedia.

It's well worth the time learning how it all works and I've created the toggle macro for you:

#define bitmap_toggle(bitmap, i) bitmap[i/8]~=(1<<(i%8))

Monday, 8 January 2007


I've found a great free pdf book all about semephores, it's 261 pages long and called "The Little Book of Semaphores" :D

Bitmaps - Part 1

I've heard of bitmaps but never seen an implementation. I guess I've not browsed enough code. Anyway I was thinking about it whilst watching "You, Me and Dupree", and thinking about it again as I ran to catch quiz night at the woody, and some more as we tried to work out what a Raider bar(twix) was called in Europe.

Anyway I just got home and implemented some nice and simple bitmap code. It should come in handy with keeping track of paging in the DB project. Enjoy. Oh and excuse the sloppy code, it's a first attempt and all.

#define bitmap_init(size) calloc(1, sizeof(char)*((size/8)+1))
#define bitmap_set(bitmap, i) bitmap[i/8]|=(1<<(i%8))
#define bitmap_unset(bitmap, i) bitmap[i/8]&=~(1<<(i%8))
#define bitmap_isset(bitmap, i)!!(bitmap[i/8]&(1<<(i%8)))

Thursday, 4 January 2007

Manifestation type

You may or may not have heard of it, SQLite has them, but what is a manifestation type (I'll call them mtypes from now on)?

You can think of an mtype as a untyped variable with a twist. That is you can assign anything you want to it without having to first declare its type OR size. But first lets look at a typed variable example using the C programming language. In C if you want to store a string you will need to declare a fixed char array or create a char pointer to some malloc'd memory cast as char:

// fixed length array
char str[28];


// malloc'd fixed length array
char *str;
str = (char)malloc(sizeof(char)*28);

You need to know ahead of time how much space you will need to store a string. If your string was longer than 28 characters you would lose everything after character 28, safely off course by being sure not to write more than 28 bytes ;) This is very much the same principle in the database world, you must know the type and size before hand of a column.

Now lets use PHPs untyped variables to show an example of a more mtype way of doing things:

$str = "Hello World!\n"


$str .= "from Andrew Roses blog!\n";

The first line of code created the variable without being told what type it is or how big it is or is going to be and stored the string, the second line adds more to it (showing how there's no need to specify size). Now, watch this!

$str = 42;

Try doing something like that in C ;) What we have done it assigned what was once a string an integer.

Lets move to the database world now with the examples before in mind. First lets create a table:

create table test (
id int,
name varchar(32)

Now if we insert some data which sticks to the types(I'm using set in insert so things are clearer):

insert into test set id = 42, name = "Andrew Rose";*

This works, because we stuck to the types, but if we try this instead:

insert into test set id = "Andrew Rose", name = 42;*

If your DBMS does not support mtypes id will become 0 and name will be "42", but not the number 42, but the string. Most DBMSs will convert numeric to string and vica-versa. id became 0 because it has no logical numeric value.

A DBMS that supports mtypes would have ended up putting "Andrew Rose" in id and 42 most likely as a string in id, but possibly as an integer.

In fact in SQLite the table we created can be created without declaring the types at all:

create table test(

So are mtypes good or evil? That all depends on the user I believe.

*SQLite does not support the set in insert syntax.