Tag Archives: tech

Optimizing complex updates/deletes for MySQL replication

In a MySQL master-slave environment, when you do an UPDATE or DELETE with a complex WHERE clause, the complex query gets executed on every slave server, which is horrible if your slaves are already serving a high volume of read requests. But this can be avoided.

MySQL’s replication binlogs only replicate queries that modify data. So you can make the slaves do less work by running a SELECT first with the complex WHERE clause, then a UPDATE or DELETE with a simple WHERE clause. By breaking it up into 2 queries like this, only the simple query gets passed down and executed on the slave servers.

An example of where this might be useful is deleting a set of records that relate to another DELETE operation, and where there is no useful index that can be used to locate the related records. For example email aliases…. If a customer deletes user@example.com we need to also update/delete any aliases that point to user@example.com. If you store alias destinations in an unindexed comma delimited column, a LIKE statement or other complex string parsing statement must used in the WHERE clause to update/delete these related records. In this scenario, it would be much better to first run a SELECT query to find all alias records that you need to update/delete, and then run each update/delete query with a simple WHERE clause that references an indexed column.

Hadoop = Coolness

Stu posted a great description of the logging system he built to make sense of our massive volume of mail logs using Hadoop.  And I gave highscalability.com some follow-up details on how our logging architecture evolved to this point… but the real reason we switched to Hadoop:

> Why didn’t you choose to go to a multiple database
> server
architecture instead of relying on one
> database server?

knew that was an option, but we felt that moving to a partitioned MySQL
data set would only buy us time, and we’d still need a more scalable
solution at some point down the road.  And to be honest, the coolness
factor of building something with Hadoop really excited us and that
definitely weighed into our decision.

Ya, we’re geeks.

Btw, Hadoop is now a top level Apache project, no longer a sub-project of Lucene.  Congrats to Doug Cutting and crew!

2 more Blackberry tips

> Sent from my BlackBerry wireless device

To get rid of this tagline on your outgoing Blackberry emails, log into your Blackberry provider’s website (from a computer, not your phone, because they hide this setting when you log in from your phone in order to make it hard to

  Sprint: http://sprint.blackberry.com/
  Verizon: http://www.blackberry.com/go/vzw/
  or just google for "blackberry yourcarrier"

…click on Edit to edit your email account settings …then just delete your signature.

Also, something that I find handy is setting the following address as my "Auto BCC" address: myaccount+sent-blackberry@webmail.us   This stores a copy of all the mail I send from my Blackberry into a folder in my webmail account called "sent-blackberry".  You can do this with any folder, as long as the folder is all lowercase and contains no special characters such as spaces.

Blackberry Sync / Filtering after delivery

I have two new email habits to report…

Blackberry Sync

So as of last week, I no longer add myself as an invitee on every meeting I add to my webmail calendar in order to get the event to show up on my Blackberry.  Now I use our Blackberry Sync product to keep my Contacts, Calendar and Tasks in sync with the data in my webmail account.  It’s in private beta right now and still has a few kinks to work out, but I already love it.

Filtering after delivery

I got sick of having to read mail twice because my Blackberry doesn’t mark things as read in my webmail account.  This was happening because of the funky multi-mailbox forwarding system I set up to simulate folders on my Blackberry.  Blackberry would check these secondary accounts and mark mail as read in there, but it wouldn’t mark it as read in my primary account.  Over the weekend I ditched those secondary accounts and I deleted all of my filtering rules in webmail.  Now everything comes to my primary inbox and I have Blackberry checking just that account.  When I read something on my Blackberry now it marks it as read in webmail.

But I still love folders.  So to manage my 100+ folders I installed imapfilter on my desktop.  I transfered all of my webmail filter rules into this and I also configured it to not move any mail from my inbox which is flagged or unread.  Throughout the day now, after I’ve caught up with everything in my inbox, I simply press a button in my quicklaunch bar and woosh… all my mail gets filed away into folders.

I expect these two changes to save me at least 20 minutes per
day.  And it will save me even more time when I’m traveling and using my Blackberry as my
primary email client.

Coolest mom ever

Last year for Christmas I bought my mom a fast new Dell.  A year later, the computer suffers from “mysterious slowness,” random popups and a couple inches of browser toolbars.  So this year I’ve given her the gift that every mom should have:  Linux…