Archive for the ‘Mysql’ Category

MySQL data sharding using Spock Proxy

Tuesday, August 12th, 2008

Yesterday at the Silicon valley MySQL Meetup, Frank of talked about Spock Proxy. Spock Proxy is a fork of MySQL proxy which has been built to meet the data sharding needs of, the people search engine.

Here are some highlights:

  •’s web interface is built on Rails and they use ActiveRecords as their O-R layer for MySQL data access
  • Spock has around 1,000 web servers using Rails and they connect to MySQL slaves and masters using Spock Proxy
  • Spock Proxy acts like a normal MySQL engine, except that it transparently talks to other MySQL servers. At spock they use 4 master and 4 slaves each having their own Spock Proxy.
  • The Web servers each have one connection open to the Spock Proxy while the proxy may have 100s of pooled connections
  • The Proxy tokenizes a SQL statement and figures out the target shard for the query. The query must have a shard_key. The shard_key is stored in a Universal DB which stores the dictionary of the partitioned tables, shard hostname/user/password, ranges and range for auto_incremented columns
  • It currently supports only range based partitioning — while a lot of partitioning is done based on hashing, but should not be a big deal to change
  • The current alpha version is very much suited to meet Spock’s internal needs, but I’m sure people will take this up to generalize
  • Unsupported query constructs (like inner queries, group by, multi-table joins) may not throw exceptions. DDLs are also not supported


WordPress Plugin development, a Windows déjà vu!

Tuesday, May 20th, 2008

10+ years ago, in my first job I got an opportunity to code a number of Windows applications using MFC/COM/Win32 API. Windows was getting popular, client-server computing even more; all Unix apps were wanting to have a visual appeal. Came Windows to the rescue. During those days as a Windows programmer, I battled the following:

  1. Writing code in a way that Windows Operating System couldn’t mess it up. Having an application run on 95, 98 Developer Beta and several hot fixes in between
  2. Avoiding clashes with other Windows applications. This is funny, 90% of the time the issues were related to end-users installing/uninstalling other related/unrelated applications
  3. Finally, writing code so that hardware changes don’t mess the application up–Installed a new iomega Zip drive? The D:\ drive stops working. Changed Network properties to make Banyan Vines work? The whole TCP/IP stack stops responding!

Fast forward to Social Media applicaion development today. In my short tryst with WordPress Plugin development, I found that I have to take care of similar things (though in a much easier way compared to writing C++ code).

  1. When writing a WordPress Plugin, you have to make sure that it serves at least 3-4 major revisions of WordPress installations. Few people bother to upgrade (although, WordPress does a good job alerting users in the admin panel).
  2. Make sure that your Plugin plays nice with other plugins and themes. Due to increased flexibility (love them WordPress!) zealous features may sometimes may not work with themes and such; say a theme which has two sidebars on the left and none on the right. 
  3. Most of the times WordPress Plugin code (written in PHP) and underlying operating environment (PHP, MySQL) mix each other well. However, at times end-users may have different settings, database permissions and security sandboxing rules. Watch out for these as they’re hard to detect and the end-user may not have any idea about the configurations.

There are specific examples to the above WP issues which I’ll rant on in future posts.

All in all, these are happy problems and shows the maturity/popularity of the platform amongst the developer community. Neither these are hard problems–requires diligent coding and testing and a generous feedback from the ‘beta-user’ community 🙂