Nagios and Websphere

I saw something interesting pop up on twitter:

http://code.google.com/p/nagios-was/

that made me think of my websphere monitoring work:

http://bit.ly/I6YUw

Admittedly, mine isn't actually a Nagios plugin but I could pretty easily convert it to one. The logic is all there. Maybe I'll download a trial version of WAS6 (mine was written again V5) and see if I can make it an actual plugin.

More Adobe AIR stuff

So I recently wiped my Dell XPS M1710 laptop. I had purchased another 2GB of memory (bringing it to 4GB) and a 500GB 2.5" sata drive to replace the shitty 100GB that came with it.

I decided to go 64-bit Jaunty to really take full advantage of the memory. I haven't run a 64-bit desktop Linux since I ran Gentoo. I remember a whole host of problems then with chroot and such but figured things were better now.

Indeed they are except for a few closed source programs. One of those happens to be Adobe AIR.

Adobe provides some nice tips on their website for 64-bit linux installs but most of those were deprecated for Jaunty. TweetDeck would fire up but not let me click anything similar to the problem I had before with gnome-keyring vs. kwallet.

A little research later lead me to this post:

http://joefleming.net/2009/06/14/ubuntu-64-bit-and-adobe-air/

Essentially, getlibs makes all the problems I had before a non-starter. I was able to grab the 32-bit version of gnome-keyring and have it be a part of the package system (so no stray files) and start TweetDeck.

I'm still getting some cocked-up error about libcanberra but that appears to be a known issue:

https://bugs.launchpad.net/ubuntu/+source/ia32-libs/+bug/369498

Updates to mhs

So I did some updates to MHS (the mysql health daemon I wrote for work). I was implementing our scripting framework from bash in perl to provide the same wrapper functionality we have in our shell scripts.

It's not as transparent but it logs script stop and start. This one is pretty useless outside of our shop because it's now implementing our own perl modules:

HSWI::Base
HSWI::Log
HSWI::DB

I also stopped using Proc::Daemon because of some logging issues I was seeing. No matter which order I did Proc::Daemon::Init(), logging would stop. I even changed Log::Log4perl to use init_and_watch but nothing worked. I ended up having to not only change to App::Daemon just to get the tty detach (we start mhs from an init script) but also stop using File::Dispatch::LogRotate because it doesn't honor setting ownership of log files.

As soon as I have time to sanitize it, I'll post a new copy on dev.lusis.org.

Tinis for Tatas

I wanted to remind everyone who might possibly read this and lives in Atlanta that Crepe Revolution is hosting Tinis for Tatas tomorrow night:

http://creperevolution.wordpress.com/2009/05/03/tinis-for-tatas/

This is a pretty big deal as one of our dinner club friends is a survivor as well as one of my guild mates in World of Warcraft. If you're in the Atlanta area, please try and come.

MyISAM vs. InnoDB via Twitter

So I saw the following tweet come across TweetDeck (using the search functionality as a custom feed no less):

ok #mysql / #database geeks on twitter (twittabase tweeks?) .. which is better: myisam or innodb, and where and why.

from @narendranag

140 characters is nowhere near enough space to answer that question. I'm going to put my thoughts here from the perspective of someone who's had to deal with large databases (500+ GB) in both MySQL and PostgreSQL doing both OLTP and OLAP work. Here's probably the best summation:

MyISAM
- Fast

InnoDB
- Reliable

That's not to say that MyISAM can't be reliable or that InnoDB can't be fast but that's the best way to look at it. But you can't balance which table type you choose based on those two criteria. There are reasons you might not need full ACID compliance that would make you still want to use InnoDB over MyISAM. Hell, MEMORY is faster than MyISAM but you don't want to use it for all your tables. Often times, the right answer is somewhere in the middle.

I tend to default to using InnoDB across the board. It's always defined as my default table type in my.cnf. I do this because often times the developers are coding around the assumption that the database supports things like foreign keys, transactions and the like. Admittedly, this is often hidden behind the ORM they use such as Hibernate but it's still important.

But what about speed? Why is InnoDB "slower" than MyISAM. It's basically because it's doing "more" than MyISAM. It's managing key constraints, logging transactions and all the other things that make it ACID compliant. Much of that "slowness", however, can be mitigated by getting away from the default InnoDB configurations and profiling the system over time to size bufferpools and the like:

• Don't use a single tablespace file for InnoDB (innodb_file_per_table). The global tablespace will still need to be used but it's much slower than if you were to use a tablespace per table. This also gets you the benefit of being able to recover disk space after dropping an InnoDB table. If InnoDB is using the global tablespace file, the ONLY way to recover that space from dropping an InnoDB table or whole schema of InnoDB tables is to fully backup ALL schemas, remove the ibdata/log files, restart the database and then reload from backup. Not pretty.
• Use the smallest possible primary key where you can. At one company, we were using GUIDs for the primary key. InnoDB prefaces every index with a subset of the first bytes of the Primary Key for that record. I can't remember the exact number off-hand but it was only a subset. Considering the first X bytes it was using could potentially be the same across multiple records, it took more work than if we had used ints. Additionally, not only were our primary key indexes unneccesarilly large, every subsequent index was as well. This wasn't as much a big deal on columns with smaller datatypes but indexes on columns of datatypes like varchar and lob were pretty ugly
• Consider using a larger log file size. This has a trade-off in recovery time though. Your call there.
• Get fancy with disks. If you have multiple volumes of different raid types, you can not only tell InnoDB to put the global tablespace and log files on a different path but you can also "move" the database to a different volume as well. This involves creating the database, shutting mysql down, moving the database directory from the mysql data directory to a new location and then symlinking it. Until MySQL or InnoDB gets me the ability to define where I want a given tablespace, this is the next best thing.

One area where InnoDB is faster than MyISAM natively is in concurrent CRUD operations. That's because InnoDB uses row-level locking. I'm not as clear on the specifics of the locking as I am with say DB2 (DB2 will actually lock the row before and after the one you're modifying) but it's better on multiple concurrent operations than table level locking.

So when would you want to use MyISAM then? One area we really found that using MyISAM made sense was on non-relational tables within a schema that normally had InnoDB tables. In one case, we had a table that had two columns - an id and a blob. That table was MyISAM. Conceivably anywhere you are denormalizing the data quite a bit, it can make sense to use a MyISAM table especially if it's a frequently rebuilt table (like a summary table updated from a cron job). Of course we've also used MEMORY tables for that same purpose. Just be careful how you intermix these in the code (the aforementioned Java transactions for instance).

So here's my recommendation:
OLTP tables - InnoDB with a few caveats
OLAP tables - MyISAM with a few caveats

Adobe AIR apps and Linux - Tweetdeck particularly

So I ran into an interesting issue yesterday. I decided to give TweetDeck a shot. I wanted to get hashtagged search results as a feed. TweetDeck can do it. Thwirl cant.

So I make sure I have the latest TweetDeck and fire it up.

Er...what's this black window I see? I can't click on anything. The main canvas is grey and the only thing I get is tooltips on mouseover of the buttons. Nothing works.

So I do some research and find out that this appears to be a known problem. No one has been able to down exactly what's going on. Some people were mentioning that starting kwallet solved the problems while other's said that it was gnome-keyring. My brain started churning.

I started to think back to when I installed AIR on my desktop and when I installed TweetDeck. It was right after I left MediaOcean. I was setting up my desktop at home to be more like the setup I had at work so I could stay in the same mindset while looking for a job. That's when it clicked.

I installed the AIR runtime while I was running KDE. One thing AIR boasts under Linux is integration with either kwallet or gnome-keyring. I wonder if maybe it "locks" that choice in place during install. Well I run through a few quick tests which involve installing and uninstalling AIR , removing some dotfiles and directories where settings are stored. Nothing seems to work.

Here's what finally worked:

• Drop to a shell.
• Remove the package that provides kwalletd


jvincent@jvxps:~$ dpkg-query -S /usr/bin/kwalletd
kdebase-runtime-bin-kde4: /usr/bin/kwalletd
jvincent@jvxps:~$ sudo apt-get remove kdebase-runtime-bin-kde4
[sudo] password for jvincent:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
libclucene0ldbl apache2-utils libxine1-x librdf0 kdebase-runtime-data-common php5 libxine1-misc-plugins kdelibs5 libxcb-xv0 kde-icons-oxygen libxine1-bin libexiv2-5 librasqal1 apache2-mpm-prefork libsoprano4 redland-utils
apache2.2-common kdelibs5-data cvs libxcb-shape0 phonon-backend-gstreamer libstreamanalyzer0 libphonon4 libsvnqt5 kdelibs-bin libstreams0 exiv2 php5-mysql libapache2-mod-php5 libxcb-shm0 soprano-daemon kdebase-runtime-data
php5-common libxine1-console libxine1
Use 'apt-get autoremove' to remove them.
The following packages will be REMOVED:
cervisia cvsservice kdebase-runtime kdebase-runtime-bin-kde4 kdesvn kdesvn-kio-plugins khelpcenter4 kompare
0 upgraded, 0 newly installed, 8 to remove and 30 not upgraded.
After this operation, 17.8MB disk space will be freed.
Do you want to continue [Y/n]?


• One thing to watch carefully is the list of packages that will be removed. I did this on my desktop and it wanted to remove Amarok. I figured I could reinstall Amarok if I needed to after the test.
• Once that was done, I uninstalled AIR and all the apps I had installed (in this case, TweetDeck and Thwirl).
• Backup and remove the .appdata directory from your home directory. I'm not sure if this step is absolutely required but I did it anyway.

Make note of any other packages you want to reinstall.
After that, I reinstalled the AIR runtime and TweetDeck. TweetDeck was working!

So what's the dealie yo? Well it appears there are two issues:

• Which ever keychain you are using when you install AIR (gnome-keyring for gnome and kwallet for kde) becomes the default keyring for the life of the AIR installation. I realized later that when I switched back to Gnome, Thwirl was always asking for my password even when I told it to save it. Now I know why.
• It appears that Kwallet is the DEFAULT keychain used if you have it installed. That's why I had to fully uninstall KDE just to install AIR. I run under Gnome again. I don't use any KDE apps other than Amarok. Kwallet may not always be running.

I have yet to reinstall Amarok again so I don't know what will happen once kwallet is available but it appears to me that Adobe needs to fix this behaviour. Maybe give the user an option to choose which vault will be used at install time or possibly someone can create an application that can switch the vault from kde to gnome or vice versa.

New Nagios Exchange online

Got a tweet from@nagioscommunity yesterday:

New blog post: Launch of the new Nagios Exchange ! http://bit.ly/1aS44U

Went ahead and moved the stuff that was hosted at monitoringexchange (nee nagiosexchange) over to the new spot.

Fun with Foundry - Load balancing MySQL

I've worked with quite a few load balancers over the years from Coyote Point and Cisco to Citrix. One I've never worked with is Foundry.

So I have a task I'm working on - Balance our read-only MySQL slaves behind the FoundryServerIron 4G. Fun stuff and it gets me in front of the units which I've been itching to do.

As with any load balancer, simple balancing is pretty easy. Define multiple real servers that listen on a given port. Define a virtual server that consists of those real servers. Profit.

However this is what I'll call "dumb" load balancing. There's no actual intelligence to it. Most of those checks are simple Layer 4 checks. Is the real server listening on the port I defined? Yep.

Most load balancers provide something a bit higher up the OSI model for testing if a "real" server is alive and able to service connections. The most popular example is an HTTP check. The load balancer requests a page from the web server. This is much more intelligent because we know if our application stack is working all the way. You could define a custom controller to handle a given url and do something as simple as serve up static content with a string that the load balancer matches against or get complex and have it do some database queries to go whole hog.

But these are predefined health checks in the load balancers. What about something like a MySQL slave database? We have a small issue with replication lag. We've coded around most cases where it might be an issue but there are still cases where we need to deal with it transparently. Additionally, we want to enforce some business logic in it. We've gone beyond what most load balancers can do. Short of moving to installing MySQL proxy or some other external solution, we're pretty limited.

So here's what I ended up doing but first a few "facts":

1. Foundry has support for custom health checks. These are somewhat limited but you can do things like the aforementioned HTTP GET/POST checks or you can do something akin to Expect scripting.
2. We have two read-only MySQL slaves and one Master server.
3. The code has two "connection pools" (it's php so not really but I'm using that term anyway). The first is a DSN for writes going to the master as well as immediate reads based on that write. The second is for general SELECT statements and other read operations.
4. We do have a memcached layer in there but it's irrelevant to this discussion.
5. One of the slaves goes offline each night for backups.
6. Sending all traffic to the Master is NOT an option except in EXTREME situations.
7. We will have replication lag spikes on the order of 20 seconds every so often due to batch operations running against the database (building summary tables and the like). These queries are as optimized as they're going to get but 5.0 statement based replication is the reason things lag.
8. Upgrading to 5.1 and row-based replication is NOT an option at current.

So there you have the parameters I have to work with. The first step was finding out how flexible the Foundry was at custom health checks. The way Foundry works is you bind ports to real servers. Optionally, a port definition can take the name of a health check as the determining factor. Look at this example:


server real server01 10.0.101.108
port dns
port dns keepalive
port dns addr_query "server01.mydomain.com"


That sets up a real server providing DNS as a service. DNS is a known service so it has its own set of rules. We tell it that we want to do a lookup on server01.mydomain.com to determine if DNS is working. Here's an example for a web server:


server real searchserver01 10.0.101.102
source-nat
port 8080
port 8080 keepalive
port 8080 url "HEAD /solr/solrselect/admin/ping"


We're connecting to a tomcat instance and pulling up a static page inside the container to determine if it's working properly.

Now take a look at this example:


server real dbs02 10.0.101.113
source-nat
port 3306
port 3306 healthck dbs02mhs
port 3306 keepalive


This is saying that for port 3306 on the server, I want to use a health check called dbs02mhs. This is a custom health check that I've defined for this purpose. So what's in dbs02mhs?


healthck dbs02mhs tcp
dest-ip 10.0.101.113
port 10001
port 10001 content-check mhs
l7-check


We're connecting to port 10001 on IP 10.0.101.113 and performing a content check called mhs. Additionally, we're saying that this is a layer 7 check only. Here's the contents of the mhs content check:


http match-list mhs
up simple 0
down simple 1


Ignore the http part for a minute. It's a bit misleading. We're not actually checking via an http call. What this match list says is if I get a 0 as my response, the server is up. If I get a 1, the server is down. By binding it to health check above and subsequently to the real server, we're saying this:

"Connect to port 10001 on IP 10.0.103.113. If you get a 0 back, everything is good. If you get a 1 back, things are not good. In fact, to determine if port 3306 is available on this IP, I want you to do the check this way"

Interesting, no? Probably not. So what's listening on port 10001 on the database server? MHS.

MHS is a little perl daemon I wrote that encapsulates the more fine-grained logic we need in determining if the slave database should be handling queries. I'm going to post the code for mhs. I warn you now, this is a hack. It needs to be cleaned up and have some code style enforced. I'm actually working on a set of internal perl modules to move much of this stuff out. We already have a bash version of what I'm calling our "scripting framework".

---> MHS

As I said, this was more of a P.o.C. Now that I know it works, I can clean it up. Basically, MHS does three things currently:

• Check if the database server is alive. We do this by doing a "SELECT 0" from the database.
• Check if there is a backup in progress. If so, this slave will be lagged and so we don't want to use it for reads.
• Check if replication is lagging more than 60 seconds. If it is, let's not use it for now.

As I said, this is pretty specific to us. Every MySQL instance we have has a database called sysops. This is a non-replicated local table. There's also a sysops database on a central backend MySQL instance that we use for storing scripting execution details and job logging but on every other database server, there's currently a single table - job_locks. Our shell scripting framework (and soon the perl framework) has a method/function for locking the database so to speak. In this case, our backup job in addition to writing to our master sysops database information about execution time and what not, also writes a lock to the server that is being backed up. The job_locks table currently has one row:


+--------+--------+
| locked | name |
+--------+--------+
| 0 | backup |
+--------+--------+
1 row in set (0.00 sec)


The reason for storing this table directly on the server is that our sysop database instance is not redundant and doesn't need to be. It's for storing one-off databases. If it goes offline, we can't have our checks failing. By putting a table on the actual server being backed up, we can self-contain the check. The daemon runs on the same box as mysql and the tables it checks are on that server.

One thing I'm running into with the Foundry is needing to setup what they call a boolean check. You can use operators such as AND and OR as well as group multiple checks together.

My ruleset needs to basically say this:

If 3306 Layer 4 is true and mhs Layer 7 is true, server is good.
If 3306 Layer 4 is true and mhs Layer 4 is false, server is good.

The reasoning is that we don't want to fail out a slave if the perl daemon crashes. We want to make the default assumption that the slave is good and current unless explicitly told so by the fact that it either isn't listening on port 3306 or the mhs service says it's bad.

I don't THINK I can do that but if there are any Foundry experts who read this post, please let me know.