Mysql increase key length

We have recently been converting most of our databases from latin to UTF-8 and have encountered issues with smaller Mysql default keylength. Mysql by default allows a max key length of 1024 size. While it was quite a bit of work to change the current data to confirm to the key length, the easier way was to recompile the Mysql source with a custom key length. Here I will detail the steps to do that:

wget http://www.percona.com/mysql/5.0.67-b7/source/mysql-5.0.67-percona-b7-src.tar.gz
tar zxf mysql-5.0.67-percona-b7-src.tar.gz

Now you'll want to make the edits to the myisam.h file to use our longer key length instead of the default 1000 byte keylength. Below is a compare of the file before and after change:

--- /tmp/myisam.h       2008-05-28 13:36:22.000000000 -0700
+++ include/myisam.h    2007-03-15 14:15:41.000000000 -0700
@@ -48,12 +49,12 @@
 #define MI_MAX_KEY                  MAX_INDEXES         /* Max allowed keys */
 #endif
 
-#define MI_MAX_POSSIBLE_KEY_BUFF    (1024+6+6)      /* For myisam_chk */
+#define MI_MAX_POSSIBLE_KEY_BUFF    (4096+6+6)      /* For myisam_chk */
 /*
   The following defines can be increased if necessary.
   But beware the dependency of MI_MAX_POSSIBLE_KEY_BUFF and MI_MAX_KEY_LENGTH.
 */
-#define MI_MAX_KEY_LENGTH           1000            /* Max length in bytes */
+#define MI_MAX_KEY_LENGTH           4000            /* Max length in bytes */
 #define MI_MAX_KEY_SEG              16              /* Max segments for key */
 
 #define MI_MAX_KEY_BUFF  (MI_MAX_KEY_LENGTH+MI_MAX_KEY_SEG*6+8+8)

Next you'll need to run configure / make install

./configure --prefix={choose a name here like /tmp/mysql_mod}
sudo make install

mysql timeout issue

The sys admins had recently changed the timeout on the firewalls to a fairly low value to deal with the excessive connections issue. This was causing an issue with the mysql servers as the nightly etl and scripts were failing with stale connection issue. Googled around to find a way to set the connection timeout in mysql settings but couldn't find any lead. One of our sys admins suggested we change the tcp timeout on the mysql boxes and that seems to have resolved the issue. here's the command:

sudo /sbin/sysctl net.ipv4.tcp_keepalive_time=90