iptables trick to limit concurrent tcp connections
This is sort of a self-documenting post, and a self-support group about ill-behaved tomcat apps.
Sometimes, you have multiple nodes accesing your MySQL server (or any kind of server, for that matter) concurrently. Eventually, software in one or more of these nodes might do nasty things (you know who you are buddy:))
MySQL provides a built in [...]
SOUNDEX(), triggers, and stored procedures
MySQL provides a SOUNDEX() function, which returns the soundex of a given string. For details, refer to the manual, but to put it simply, it allows you to compare strings based on how they sound, hence letting you do proximity searches on your database.
If you’re just querying for a word, it’s usage is pretty straightforward, [...]
Extending procedure_analyse
My previous post explored a stored procedure that extended procedure_analyse with the intent of helping DBAs optimize table structure.
Here’s an improved version. I’ve followed Arjen Lentz’s suggestion and added support for the max_elements and max_memory parameters.
I also added a new Indexed column to the output, which is an ENUM(’No’,’Yes’,’Overindexed’). Yes and No are self-explanatory, while [...]
Making use of procedure analyse()
SELECT Field0[,Field1,Field2,...] FROM TABLE PROCEDURE ANALYSE() is a nice tool to find out more about your table’s columns.
Still, it could be improved in a lot of ways, and the stored procedure below is a starting point. It makes use of procedure analyse (though with ‘SELECT * FROM’), and modifies it’s output to include the actual [...]
Updated mysql-proxy benchmarking script (for proxy 0.7)
My previous post contained a lua script for MySQL proxy that would generate benchmarking information. However, just days (or maybe hours?) after I published it, release 0.7 of mysql-proxy was published, making my script obsolete.
I’ve fixed this (it needed just a minor tweak), so here’s a tarball with the following:
trace.lua, which is the lua [...]
Using MySQL Proxy to benchmark query performance
By transparently sitting between client and server on each request, MySQL Proxy offers many possibilities for query manipulation.
Many are explored in the cookbook, and they even include a histogram recipe. Still, I wanted to learn more about the proxy while working on a script that would let me get some stats on the queries executed [...]
Using MySQL sandbox for testing
MySQL Sandbox is a great tool for quickly deploying test MySQL instances, particularly if your daily work involves diagnosing problems across multiple MySQL versions.
Once you’ve downloaded it, it needs no installation. Just have a few MySQL binary releases at hand, and begin creating sandboxes in just a few seconds:
./make_sandbox mysql-5.0.77-linux-x86_64-glibc23.tar.gz
or
./make_sandbox mysql-5.1.32-linux-x86_64-glibc23.tar.gz
Pretty simple, huh?
Suppose you have [...]
Using the ENUM data type to increase performance
While going through the DATA TYPES section of the Certification Study Guide, I was refreshed of the ENUM datatype, which I rarely use.
I usually create individual tables for enumerations, so that new values can be added with just an insert, or deprecated values can be marked as such.
However, today I got to think about the performance issues involved in all that joining, and how could an ENUM column improve a select.
MySQL Certification self study
I’m taking the MySQL Certification exams soon, and while I’d love to take advantage of official training, the closest classes near me will be in Buenos Aires (just a few hundred kilometers and an estuary away) in over a month, and I hope to be done with the Developer exams by then.
So I’ll be documenting, [...]
Coding Buddies
Coding Horror always has insightfull articles, and this one on code peer reviews is no exception.
Why is it that writers, musicians and, well, any respected and professional artist or scientist gets his/her work reviewed before it’s published, but the same isn’t true in IT?
My guess? Most programmers aren’t neither artists, nor scientists. They’re an anonymous [...]