James Montebello

James Montebello, a friend, a mentor, a fantastic engineer, and the person who introduce me to mySQL in 1999 passed away August 24, 2006.

I write this today in dedication to a great man. I also write this in appreciation for all the time we had together, and for guiding me on what are the right things to do.

He was among the 1st to believe in my designs

James you will be deeply missed.

I recommend putting this in your mysql init scripts


'stop')

echo -n 'Are you sure? (y|N)';
read SANITYCHECK;
if [ $SANITYCHECK != 'y' ]; then
echo "EXITING INPUT NOT y";
exit 1;
fi



Why? Well imagine you have 20 servers running mysql for the site. You spent 100K to get these servers redundant, automatically failover if a few go down-which gets the company a High Availability score of 99.999% in the database backend and then someone runs

dsh -N ALL_MY_DB_SERVERS /etc/init.d/mysql restart

when the intention was to restart another process like say the monitoring process.



DOH!!

Unorthodox approach to database design

There are whole books on the subject about building a great design that is scalable and portable among developers and or administrators.

Then there are whole books on the subject of capacity and scalability for the database layer.

Then there are novels from developers that in many cases really don't know the tricks of the DBMS they are working with, and create elaborate abstraction layers that automatically generate SQL for the DB in question from objects and such.

But, with all these people who tell you how to do it, actually can they prove that it works under a constant high workload for many people all at the same time.

I can boast this. Flickr does over 4 BILLION+ queries per day, 2 BILLION of which are SELECTS. Most of our data is REAL TIME queries from the database layer. We don't do any fancy tricks to dedicate resources to API calls to certain servers; they hit the SAME servers that the Flickr Users do.

You may be thinking to yourself yea right say you can do 20K + transactions per seconds that must be a crap load of expensive hardware all running, where all the data is served out of memory. Nope we run our stuff on RHEL-4.0 with mySQL version 4.1.20-flickr (my little special tweaks for x86_64) and the data is only 3% HOT (meaning out of the 1 TB of user data less then 3% is in memory).

Still hard to swallow? Let me add a little more info to blow your mind and wipe away all the things that you may have read that can't be done.

All of our database connections are real time. Our load balancer for the database is written in 13 lines of PHP code.

How can this be, how does Flickr scale? How did the Flickr Engineering Team do this (6 people)?


If you’re interested let me know post a comment, and I'll write up the design that I proposed July'ish 2005 to Flickr which we use today. It's able to scale linearly based on a function of users not on content.

Quick php script to figure out bad utf8 characters.

When I wrote this post about invalid utf8 characters I needed a way to convert the mysql message into a real identifier for me to take a look at. Below is a quick and dirty script to figure out the bad rows.

I wrote the script below in literally 2 mins. It's really basic and basically just CROSS JOINs two versions of the table one in utf8 the other in latin1 and reports back which string column is not correct. I haven't cleaned the script up, it's ugly and for my purposes will only exist for a short period of time.

test.$TABLE = is the original table latin1 for instance
- use mysqlimport this forces data to not get converted to latin1 from utf8 by setting the character set to binary (i.e. do no convert)

$DB.$TABLE = is the new utf8 table.

#!/usr/bin/php -q


        $dbh = mysql_connect('localhost','root','',1);
        $DB  = 'your db';
        $TABLE = 'your table';

        if (!$dbh) {
                die ("Failed to connect\n");
        }


        if (!mysql_select_db($DB,$dbh)) {
                die ("Failed to changed dbs: " . mysql_error());
        }

        $query = "SELECT * FROM $TABLE LIMIT 1";
        $result = mysql_query($query,$dbh);

        $fields = mysql_num_fields($result);

        print "Number of fields : $fields\n";

        $names = array();
        
        for($i = 0; $i < $fields; $i++) {
                $type  = mysql_field_type($result, $i);
                $name  = mysql_field_name($result, $i);
         
                if ($type == 'string') {
                        $names[] = $name;
                }
        }    
        mysql_free_result($result);
        
        foreach($names as $name) {
    
                $query  = "SELECT a1.id FROM $DB.$TABLE a1, test.$TABLE a2 WHERE a1.id=a2.id AND BINARY(a1.$name) != BINARY(a2.name)";

                $result = mysql_query($query,$dbh);
                while($row = mysql_fetch_assoc($result)) {
                        print "Invalid UTF8 Chars for column $name: ROWID: $row[id]\n";
                }

                mysql_free_result($result);

    
        }
?>

Indexes, the optimizer, and doing efficent selects

Basics

Btree-Indexes must be less then 1024 bytes, so if you have a utf8 column that is 255 characters mySQL assumes 728 bytes, if you combine that with other varchar(255) utf8 columns you'll get an error.


Compound, composite indexes:

This term is used allot. It basically means 1 index across multiple columns.

For instance you have a table with

A tinyint
B tinyint
C tinyint
D tinyint

and a index on A,B,C taking 3 bytes.

following the principles of left-most-prefix key lookups (assume AND between columns)

A,B,C is an index lookup using 3 bytes
A,B order by C is a index Lookup taking 3 bytes 1 is used for a efficent sort.
A,B is an index lookup using 2 bytes
A is an index lookup using 1 byte

A, C is an index lookup using 1 byte. Notice that A,C does not use 2 bytes of the index making the lookup scan the result set of A, since A,C doesn't follow left-most in the compound index C comes after B ;))


B,C is not a index lookup causing a table scan.

A order by D is a index lookup using 1 byte with a filesort sorting the result set after the 2nd pass of finding the results.


Above we assumed that all the index lookups where based off of an 'AND' Clause. OR sucks in mySQL

A OR B uses a 1 byte index lookup scaning all of B making the query very slow.

How do we get around this?

USE UNION

SELECT * FROM ABCD_TABLE WHERE A=1 UNION SELECT * FROM ABCD_TABLE WHERE B=1

assuming that another index exists on B in the leftmost prefix of the compound index.

This is very fast.


More to come

Note about utf8_bin

utf8_bin collation on UNIQUE columns needs to be used with caution. In latin1

f == F so a duplicate error would be thrown if a user entered in 'F' when 'f' already existed.

With utf8_bin f != F so, make sure to normalize your data before sticking it into the dblayer i.e. put all email addresses, tags, etc in your db as lowercase :)

Thanks for reminding me Peter Z!!

MySQL 4.1 and insert or Load data warnings

When using LOAD DATA especially when importing from one charset container to utf8. Make sure to issue SHOW WARNINGS after the statement. Why? Well a cryptic message might occur and lead you to the true cause of the problem.


For instance if you get a message:

"Data truncated for column 'column' at row 157"

Why was row 157 truncated? The data use to exist before correctly in the table structure in the old format, what's different?


Well, in the scenario of upgrading to true utf8 column character sets, MySQL will remove all invalid utf8 chars and produce this message. You might be lead to believe oh crap maybe I need to increase the column size, NO that's not it. In MySQL 4.1 varchar, text, chars size are no longer just a function of bytes. Previous to 4.1

varchar(255) means that it would take up to 255 bytes, i.e. 1 latin1 character 1 byte. In 4.1+ this means that the column can hold up to 255 characters with a max byte size of 765 bytes for 3 byte utf8 characters.

So, why such a cryptic message, why not say "This row contains an invalid character. Data truncated for column at row 157".


Because you haven't bought a MySQL support license and requested this addition :)

Calculating utf8 sizes for varchars

The utf8 spec says that a utf8 character can take up to 4 bytes, mySQL currently only supports up to 3 bytes. So, in essence if your application allowed 255 characters to be inserted into a field, when in utf8 land ie a utf8 column these 255 characters can take up to 765 bytes.

Here is a breakdown from
dev.mysql.com

• Basic Latin letters, digits, and punctuation signs use one byte.


• Most European and Middle East script letters fit into a two-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others.


• Korean, Chinese, and Japanese ideographs use three-byte sequences.

What does mySQL 4.1 do with utf8 and collation

I ran into a problem. I converted my latin1 table into utf8 with utf8_unicode_ci collation as described in my previous post. The table in question has a UNIQUE index on the utf8_unicode_ci collation column. When reimporting the data I get a duplicate entry on accent e with e itself. Why?

e == utf8 0x65
accented e == UTF-8 0xC3 0xA9, U+00E9

but the rules defined
http://www.unicode.org/reports/tr10/

say to ignore accents for unicode collations. To get around this I know to define my tables as

utf8 with collation of utf8_bin


This bug at mysql.com Here was the indicator that accent e is not the problem.