The Technician

Setting up a sandbox environment has normally been a trivial task. Set up a vhost, get a copy of the database, build out the app, and start doing stuff. When ‘Stuff’ is ‘Done’ push the changes onto production, and bask in your own crapulence. That is, until your data set exceeds the limits of the sandbox, and the SOA which is saving the day in production is becoming nothing but a headache in development.

The basic sandbox environment for an app includes a reasonably recent data set, similar ( underpowered can be OK, depending ) hardware, the exact same versions of php, mysql and apache. Php, mysql and apache need to be configured exactly the same way as in production. In fact, as part of this process, it might be useful to pull down those ever so important configuration files, put them in a safe place. Perhaps source control (cough). Consistent configuration is extremely important. Bugs produced by configuration problems are notoriously hard to reproduce, and result in devs combing through code looking for bugs that don’t exist.

Maintaining a few sandboxes should be a trivial endeavor. That is, until your project gets too big. A natural response to handling ever-growing problems is use a Service Oriented Architecture; that is, to shard off aspects of the app and dedicate hardware and resources to it. However, three or four shards later, multiplied by an environment for each developer, the guy who was doing sys admin work as needed just became full timer. Unfortunately, there’s no way around this, even with a clever sys admin, who can leave enough automated scripts around so that developers can *mostly* maintain their own environment.

The fact of the matter is maintaining the development environment is one of the most important things a company can do. Close attention to detail in the sandbox will make all the difference in the deployment process. Changes in code base, file permissions and configuration can all be tested and deployed the same as in production. So every build to every sandbox (everyone builds daily, right?) is a chance for the development team to catch mistakes, and learn from them, before the big push. And if that fails, we all know how to handle a crisis.

Users have high expectations of web apps in terms of performance, responsiveness and tons of features. Normally, you’re only allowed two of any list of three really cool things. In the case of Web Apps, that would be true. Most will find some compromise of between performance / responsiveness and tons of features. More features usually equals less responsiveness, depending on the feature and scale.

Enter Gearman. Gearman is a queuing system that allows work to be farmed out to other servers. Most importantly, it allows for intense tasks to be queued and performed in the background. This means that when a user performs an action that could potentially take a long time (sending notification emails, updating Full Text indexes, etc), that slow task can be queued to run in the background, and the page can be sent to the user, keeping things snappy.

Gearman is pretty simple to install on Red Hat.

download gearman from server
> wget http://launchpad.net/gearmand/trunk/0.8/+download/gearmand-0.8.tar.gz

unzip and move into the directory
> tar -xvzf gearmand-0.8.tar.gz
> cd gearmand-0.8

Red Hat didn’t have some dependencies. The next few steps will vary depending on your *nix distro.

Install the libevent developer library.
> yum install libevent-devel

Install the e2fsprogs developer library
> yum install e2fsprogs-devel

configure and install
> ./configure
> make
> make install

/** Net Gearman **/

download php extension from the pecl repo
> wget http://pecl.php.net/get/gearman-0.4.0.tgz

untar
> tar -xvf gearman-0.4.0.tgz

build the extension
> phpize
> ./configure
> make
> make test
> make install

Add the extension to the php.ini

[gearman]
extension=gearman.so

And you’re all set!

Integration will depend on if you decide to use the php extension, and how encapsulated the code base is. I highly recommend using the pecl extension, as it provides great implementations of the client and worker. and Gearman will save you.

Runaway queries on MySQL can be a real problem. If a long-running query locks up important tables, other queries trying to query the table will will placed in a queue. Each new query is a new connection to MySQL. Once you hit max_connections, your MySQL connection code will start to fail. Depending on how errors are handled at this stage of the request, this could mean total disaster for a site.

Although there is no way to fix this within the MySQL server itself, a bit of clever scripting can be run via cron to check if there is a problem. Presenting : save_mysql

/usr/bin/mysql -e ’show full processlist \G;’ 2> /dev/null |
grep -A1 -B5 -E “Time: [1-9][0-9][0-9]?” |
grep -E “\bId\:\ |\bState\:\ ” |
/usr/bin/perl -n -e “if( $. % 2 ) { chomp $_;print $_; } else { print $_; }” |
grep -E “\ State\:\ Sending\ data$|\ State\:\ Sorting\ result$” |
awk {‘print $2′} |
xargs -iTHREAD -r -n1 /usr/bin/mysqladmin kill THREAD &> /dev/null

/usr/bin/mysql -e ’show full processlist \G;’ 2> /dev/null
This line will grab a list of all the currently running queries and commands from the MySQL server. It also redirects any error output to the blackhole. It produces output like so:

*************************** 1. row ***************************
Id: 842863
User: admin
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show full processlist

grep -A1 -B5 -E “Time: [1-9][0-9][0-9]?”
The grep here will grab line directly below and the 5 above if the time is over 100 seconds. This line can be tweaked to grep for less time. My preference is between 30 seconds and a minute. So instead of
[1-9][0-9][0-9]
you’d have
[3-9][0-9] (30 seconds) OR [6-9][0-9] (60 seconds)

grep -E “\bId\:\ |\bState\:\ ”
This will filter out the other lines from the previous grep and just grab the MySQL process ID and it’s State.

/usr/bin/perl -n -e “if( $. % 2 ) { chomp $_;print $_; } else { print $_; }”
Quick Perl script to put id and state from the step above on the same line.

grep -E “\ State\:\ Sending\ data$|\ State\:\ Sorting\ result$”
This line will filter out the queries being run that are in the state ‘Sending Data’ or ‘Sorting Result’. These are both states where it’s safe to kill the query.

awk {‘print $2′}
This line grabs the query ID from the output.

xargs -iTHREAD -r -n1 /usr/bin/mysqladmin kill THREAD &> /dev/null
Lastly, this line will grab the ID from above to the mysqladmin kill command, effectively killing the query.

When making changes to a large site, it’s really helpful to have tools to measure how those changes affect performance. One of my favorite tools is cacti. This is a graph of the load average of one our database servers

Database Load Average

We done good…

As far as linux utilities go, cron is one of my favorite tools. It handles batch jobs, checks your database, cleans up files, whatever you need to do on a regular basis. One of my favorite uses for it has been to keep a watchful eye on MySQL for runaway queries.

The lowly crontab file

One of the problems with managing a website that has outgrown its database design is that as traffic piles up and tables grow, so does the length of time it takes to run queries. Even the simplest query, when faced with examining several million rows can wind up locking a table for a scary amount of time. The real solution is to rewrite your app to not suck. However, when that luxury (neccessity?) isn’t available, the next best thing is periodically run a script that will kill long running queries.

It is not the cleanest solution, or the safest, and care must be taken to avoid killing queries that do important things. It would be a really, really BAD THING if you wind up killing the query that logs that $500 transaction. That said, in most situations running this script every minute should solve all your woes. With decent error logging, it’ll also tell you exactly what is running away from you. But what happens when you need to run it more often? Say every 30 seconds. And that’s where cron falls on its face.

After exhaustively researching, I could not find a way to get cron to run a job more than once a minute. (If any one knows differently, please say so…) However, knowing that I needed to run this script more than once a minute, I needed to find an alternative. That alternative turned out to be the humble, rarely used sleep() command. Set up a second cron job, with the script doing nothing sleeping for 30 seconds, and then calling the save mysql script.

Voila, cron that runs more often than a minute!