I write DB scripts in PHP

Lots of people like PERL, Python, Java or C to write database backfills, data repair, etc. I like writing in all these languages but what I don't like doing is writing the same code over and over again, so I write my DB code in the same language as the environment that I'm using. This means I write backend DB scripts in PHP, I get to reuse common DB paths, classes and functions and improve things when I see them.

But, PHP if not written correctly will use up 2GB of memory easily from loosing reference to arrays or setting globals and forgetting about them; stuff like that. This is especially visible in long running applications.

PEAR is not immune to these memory leaks. So, to get around reference problems in PEAR I do

$skiptrace =& PEAR::getStaticProperty('PEAR_Error', 'skiptrace');

$skiptrace = false;

This prevents this error
PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 76 bytes) in /usr/share/pear/PEAR.php on line 872

from long running processes.

Thought I pass it along.

MySQL 2008 UC Presentation Scaling a HUGE volumn of concurrent writes

The details can be found

http://en.oreilly.com/mysql2008/public/schedule/detail/588


I've since moved on from Flickr to a new Job, but Flickr is still allowing me to give this talk. Flickr is so cool!

The talk encompasses capacity planning and scaling for a heavy concurrent write and read environment, and when it makes sense to split resources out to a single application.

mySQL PHP PEAR::DB

So, I just fixed a behavior in PEAR::DB. In isManip, a regular expression is called on every query that goes through the PEAR::DB layer. That sucks btw, but the purpose of function is to tell the classes that inherit from DB that the query passed is a query that affects data.

The code is as follows

 function isManip($query)
    {
        $manips = 'INSERT|UPDATE|DELETE|REPLACE|'
                . 'CREATE|DROP|'
                . 'LOAD DATA|SELECT .* INTO .* FROM|COPY|'
                . 'ALTER|GRANT|REVOKE|'
                . 'LOCK|UNLOCK';
        if (preg_match('/^\s*"?(' . $manips . ')\s+/i', $query)) {
            return true;
        }
        return false;
    }

Then in mysql.php there is this

function affectedRows()
    {
        if ($this->_last_query_manip) {
            return @mysql_affected_rows($this->connection);
        } else {
            return 0;
        }
     }

Which is not ideal IMHO. I changed mysql.php to just return the results of mysql_affected_rows which is better anyway IMHO.

What DB wrapper do you use? What do you think about PDO?

Personally I like to write my own, bare bone wrappers around mysql_* functions for php but to make things compatible I'm using PEAR::DB.

MySQL multi datacenter HOT / HOT BCP

In a previous post I explained about BCP. I have just finished my latest and nearly final test, and all worked as expected. For about an hour certain front end servers where hitting a database shard in 1 datacenter while the rest of the front ends hit the same shard in another datacenter.

What makes this incredible is that now data from mySQL can be close to geo-graphic locations of the end user, without having to make changes to the front end application. So fail over is silent from a database perspective if an entire datacenter is down. Actions outside of the application are also replicating seamlessly and in order. Latency is high but the goal is not to have a WWW in one datacenter talk to a shard in another datacenter. So, latency is in effect not an issue especially if one uses Akami-DNS to geo-graphically loadbalance your user base.

This is a great simple solution that scales, and it only took 1 week to implement from the ground up.

Other solutions that I have seen have a Proxy layer infront of the actual database that write data to one datacenter and synchronously writes data to another datacenter. So, your entire transaction time is the SUM of the transaction, for each server plus the SUM of the latency to talk to another datacenter. Also if that "Proxy Layer" goes down, or the stunnel goes down the application goes down. This is not ideal for me.

The solution which I designed and was implemented+tweaked by our master java engineer: removes these layers and makes the data transfer independent of the application. So, if that layer dies the application does not die-it just gets restarted and catches up to the latest events.

I love when things work.

The oldest component is finally finished

I have finalized the design for Federating Connections and part of the design has been implemented so far with amazing results. Overnight the dependency on replication has been reduced. Feed updates no longer are lagging and query load doubled without the need for new hardware.

Social connections (not mySQL connections) at Flickr in particular are directly responsible for permission levels. This allows members to see into a another members photostream. This global requirement means that every logged in page viewed on Flickr requires a database read if the page is not the members own page. So if the cluster is down, all access to photostreams default to the most restrictive state, i.e. public photos only.

As a result, the service needs to be extremely responsive on reads, since possibly every page view on Flickr could produce a realtime query on the contact cluster.

Next, the data has to be redundant and always available. This is very hard to do, when you have no spare servers, and only two servers to do this entire procedure-yet we did it. We recorded all photo permission change events, created the new method, backfilled and applied all change events to the new schema layout.

Finally the new design allows for more features and more requests to the system with the ability to spread data across N severs.

Two servers are all that is needed now, with the next phase spreading the data across more servers - with NO memcache or cache layer at all.