Rackspace Load Balancing with SSL

Recently, Feedmagnet had its first customer ask us for a load balanced solution that would answer on both :80 and :443 (http and https respectively).  I have set up no less than 10 Rackspace Cloud Load Balancers over the last year, but never been asked to do this, so never really looked into it.

If you are looking for a tutorial for setting one up the first time, look at their three videos.  Great for a guy like me who comes from a database focus or a newbie.  

Of course, the customer did not warn us their link and hash tag would be promoted on the Today Show, so this was all a bit of a hurry.  When there was no obvious way to do it in the Control Panel, I had to search Google.  There was surprisingly little out there, so hopefully you found this easily enough:

Set Up

The "complication" here is simply that Cloud Load Balancers are able to share virtual IPs (VIPs).  The short of it is that a load balancer for each port must be created.

With that fact, it simply becomes a matter of setting up the first load balancer on port 80 for standard web traffic and making a note of the VIP.  

Then creating a second load balancer for port 443 and setting the VIP to that of the first.

The first load balancer for port 80 will appear in the list below the "Virtual IP Type" drop down.

Continue with the setup as normal after that.

Pricing

Problem solved, customer happy.  Now it is time to face the boss and explain costs!    Exact pricing for Rackspace Cloud Load Balancers can be found here.  Below is clarification on some of the language at a monthly granularity.

A single load balancer will cost a minimum of:

Instance	$10.95	This is the cost for just having the thing up
Connections	$10.95 per 100 concurrent	Rounded up, so one connection is 100. This is measured every 5 minutes and billed on the hour.
Bandwidth	$0.18 / GB *OUT*	Rackspace only charges for out
Total Minimum:	$22.00 / month	Rounded

So, the total cost of ownership (TCO) is $22.00 in the minimum per load balancer.

This means that that the setup described above is really going to cost a minimum of $44.00 per month in load balancing fees.

The above solution does not involve the far more expensive Cloud Load Balancer with SSL option.  In technical terms this more expensive setup would be called "SSL termination at the Load Balancer."  In practical terms a SSL certificate must be installed on the Load Balancer if this option is to be used.  Simply choosing port 443 for a load balancer is not the same thing and falls under the pricing described in the table above.

Assigning the Right App to a Shortcut (SymLink)

Assigning the Right App to a Shortcut (SymLink)

I have a number of files in the /tools directory of a git repo.  These things have no bearing on the app this repo is for, rather they are management scripts and handy commands I like to have on my desktop.  One particular script cutlery.sh would only open in Xcode.  No matter how many times I opened it with "other" and chose MacVim to open it, it would revert to Xcode by default.  Sort of wrecked the convenience of the thing.


To change the way a link opens:

1. Delete the existing link.
2. Right click the source file (the file you want to point the link to) by right clicking and choosing "other". Choose the application you want to open with and be sure to set the check box below the list to "Always open with this"
3. Click OK.
4. Now create the link:  ln -sv ~/projects/tools/cutlery.sh CUTLERY 

Slow Writes Using MySql + InnoDB

I recently started a new gig at FeedMagnet as the Chief Systems Architect.  My first task was to determine why the system was running so slow.   So setting my DBA hat firmly upon my brow I started to look around.  

Problem

I learned that inserts which should have been instantaneous were taking up to a minute then dying due to a lock time out.  

Surely this cannot be write (pun intended)!  Innodb uses row level locking, so all of our read traffic (about 98%) cannot block writes.  No, poor reader if you have landed here in frustration, there is a caveat.  A proviso.  An exception.  

InnoDB grabs a table level lock when an insert is done on a table with an auto incrementing key if binary logging is enabled.  Heikki Tuuri himself encourages the use of surrogate keys when using InnoDB due to the way data is stored, so this all the more counter-intuitive.

Diagnosis

First I noted there were writes in the slow query log with this simple Linux command:

> cat /var/log/mysql/aragorn_slow_query.log | grep 'INSERT\|UPDATE\|DELETE'

Next I used Innotop to show locking information.  Before you can work with locks effectively you must pray to the gods of obscurity and create this table:

CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;

Then choose mode "L".  It was apparent very quickly that locks were timing out.

Solution

The solution (for MySQL 5.1 anyway) is to switch the binary log format to ROW.  This precludes the need for the table level lock.  

References

To make my high school English teachers proud, the below links are the details behind this write-up.  I strongly encourage you to read them as these are true experts:

• Farhan Mashraqi - DBA stud behind Fotolog.  
• MySQL Bug Report - Describes the problem and solution in useful detail
• Baron Schwartz - MySQL Guru and author of amazing tools (Maatkit, Innotop)

Caveats

At the time of this writing I have two concerns with this solution:

1. ROW based logging simply does not have the history of STATEMENT based logging, especially with respect to replication.
2. ROW based logging is known to take up significantly more disk space than STATEMENT based logging.  This is particularly true if bulk write operations are frequent.  Beware your log volume size!