Some database engines have a fundamental concept of a row id. The row id is everything you need to know to locate a row. Common uses include secondary indexes (key is what’s indexed, value is rowid which you then use to lookup the row).

One design is the InnoDB method of having secondary indexes have the value in the index be the primary key of the row. Another is to store the rowid instead. Usually (or often… or sometimes…) rowid is much smaller than the pkey of the row. This is how innodb can answer some queries just out of the index. If it used rowid, it may involve more IO to answer the query. All this is irrelevant if you never want just the primary key from a secondary index.

Some engines are designed from the start to have rowid, others it’s added later (e.g. NDB).

Anyway… all beside the point. Did you know you can do this in mysql or drizzle:

drizzle> create table t1 (a int primary key);
Query …

I found out the hashing algorithm used in PHP-Memcache is different from that of Python-Memcache. The keys went to different servers as the hash created by python and php were different.

I posted a question on the memcache groups and was lucky to find this wonderful reply.

import memcache
import binascii
m = memcache.Client(['192.168.28.7:11211', '192.168.28.8:11211
', '192.168.28.9:11211'])

def php_hash(key):
    return (binascii.crc32(key) >> 16) & 0x7fff

for i in range(30):
       key = 'key' + str(i)
       a = m.get((php_hash(key), key))
       print i, a

This is the only thing that has to be done on Python's end, change the way the hash is calculated. The coding on PHP end remains same. All you guys using PHP for web based front-end with MySQL and Python for back-end scripts shall find this helpful.

Thanks Brian Rue.

Reference: …

John Eikenberry has taken over work on ZMySQLDA for some time now and has released ZMySQLDA 3.1. I don't have any ongoing Zope deployment except some Zenoss, and use MySQLdb directly, so I'm not a good test candidate. Superficially, there don't seem to be any outstanding issues.

Since Python 2.6 and 3.0 were released, there has been a lot of demand for prepackaged MySQLdb for those versions. MySQLdb-1.2.2 seems to throw some warnings on Python 2.6, due to a new Python set type (and the old Set module being deprecated), and some old-style exception usage. These problems should be fixed in the SVN version (MySQLdb-1.2 branch). MySQLdb was originally developed for Python 1.5 so some old crufty stuff is still hiding out in there. There are also some build fixes for Mac and Windows.

MySQL-1.2.3 is not too far off. There are probably a couple more fixes that need to go into the 1.2 branch. This will almost certainly be the …

A quick note…

As mentioned several times here, hardware can not be treated as a black box.  Every mysql professional who is charged with performance tuning has to understand where often overlooked bottlenecks can occur. This can occur anywhere in the system : disk, cpu, memory, networking.   Everyone who reads my blog knows that I have beaten the disk horse until its bloody corpse, although I still believe too many people ignore disk performance… Everyone looks at CPU, in fact every monitoring tool known to man seems to include cpu stats.  But what about network performance?  The performance of the network is even more taken for granted then disk is.  I mean to most people they don’t give a second thought to what’s happening between servers, after all isn’t that the “network teams”  job.   Unfortunately I run into network problems more often then I would like.  What could these  …

One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER TABLE, for example).

The benefit is that you have another server with all the same data, up and running, ready to serve queries. In theory, it's a truly hot standby (stay with me -- that's not really guaranteed). You don't get this with shared storage or DRBD, although those provide stronger guarantees against data loss if mysqld crashes. And you can use the standby (passive) master for serving some SELECT queries, taking backups, etc as …

Point-in-time recovery or PITR is one of my favorite PostgreSQL features. Most database servers have a transaction log for recovery. (MySQL is one of the few exceptions; more on that shortly.) However, PostgreSQL PITR allows and even encourages users to manipulate the logs to perform some very useful tasks:

* Creating a bit-for-bit backup of a server without halting
* Restoring a server to a specific point in time
* Creating a warm standby server

The third task is especially interesting because it's so common. One of the most pronounced trends in computing is the decreasing cost of computing power through Moore's law improvements and virtualization. Standby solutions nowadays look like a good investment compared to the cost having a server down or worse …

John Goulah wrote a nice quick tutorial to MySQL Sandbox.
Thanks, John!