Archive for the ‘Geeky’ Category
In the year 2000, I was working on migrating a web application from MySQL to Oracle. One of the issues we had was a number of queries which took advantage of slackness in MySQLs handling of the GROUP BY clause. Oracle took exception to our poor SQL and forced the developers to rewrite queries to be a higher standard. To illustrate the issue, have a look at the following example.
mysql> create table `test1` (`a` int(11), `b` int(11) ); Query OK, 0 rows affected (0.14 sec) mysql> insert into test1 values (1,2), (1,3); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 2 | | 1 | 3 | +------+------+ 2 rows in set (0.00 sec) mysql> select * from test1 group by a; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+ 1 row in set (0.00 sec)
The issue at hand, is the value for the b column. You’ll note that we’ve grouped by a, so we correctly have a single row result. If you’re not sure what the issue is, let’s repopulate the table in a different order and retry.
mysql> truncate test1; Query OK, 0 rows affected (0.10 sec) mysql> insert into test1 values (1,3), (1,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 3 | | 1 | 2 | +------+------+ 2 rows in set (0.00 sec) mysql> select * from test1 group by a; +------+------+ | a | b | +------+------+ | 1 | 3 | +------+------+ 1 row in set (0.00 sec)
So, the value for b was 2 in the first example, and is now 3, with the same data in the table and the same SQL. The problem is that there is no aggregation or GROUP BY clause for b. There’s only one possible value for a, but b is not aggregated – there are two possible values: 2 or 3. MySQL just picks one of the two possible values. Let’s see what PostgreSQL does.
database=# create temporary table test1 (a integer, b integer); CREATE TABLE database=# insert into test1 values (1,2), (1,3); INSERT 0 2 database=# select * from test1; a | b ---+--- 1 | 2 1 | 3 (2 rows) database=# select * from test1 group by a; ERROR: column "test1.b" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select * from test1 group by a;
PostgreSQL decides you don’t want undefined / random data in your result set and lets you know. If you’re using GROUP BY queries in MySQL, and rely on the result being, you know, correct, it may be time for a quick audit of your SQL.
A quick search of Stack Overflow questions shows many solutions which include this flaw. The solutions work fine on MySQL, but fail on PostgreSQL.
I’m surprised that 11 years after first learning of MySQL’s handling of the GROUP BY clause, it has the same behaviour enabled by default. MySQL’s behaviour can be modified to behave in a stricter manner. Worth doing if you want to ensure you receive results without including randomly selected data.
New post on Daemons over on the Booko blog.
Just posted on the Booko Blogo an article about implementing Users and Passwords.
Over on the Booko Blog.
Back when I was a professional sysadmin (now I just do it for fun) I came up with a few simple tests to perform on misbehaving hosts. These tests are very obvious and easy to check, but they’re worth remembering because too often we’re tempted to look for complex solutions to problems that, initially, look complex. It’s humbling just how often what looks like a complex software issue, really isn’t complex at all.
So when things go wrong, before reverting the last change, before breaking out gdb and strace and before tweaking your software on your production host, spend 5 minutes and run through these quick, simple tests – there’s a high likelihood that you’ll solve your problem quickly. (I’m sure there’s other tests you can do – these are the ones burnt into my mind)
#1 – Disk space
Don’t laugh – running out of disk space can cause you pain in so many ways it’ll spin your head. Check all partitions, including /tmp, /var/tmp and /var. Running out of tmp means applications won’t be able to write temporary files which, depending on the app, may make it behave very strangely. /var is used for many things including logging in /var/log – not being able to log will make some software cry like a baby – i.e. it may crash and you’ll have no idea why – it certainly won’t be in the log file. Databases like MySQL don’t like having no room to write in /var/lib/mysql – Don’t be surprised if you get some db corruption. With MySQL, you may be able to start the database and even connect to it with the mysql client, leading you to look elsewhere – but checking disk space will take you seconds.
dkam@vihko:~$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 9.4G 7.1G 1.8G 80% / udev 10M 116K 9.9M 2% /dev shm 128M 0 128M 0% /dev/shm
Don’t forget to check iNodes too – running out of inodes can cause the same issues as diskspace but is less obvious – checking for it is just as easy though:
dkam@vihko:~$ df -ih Filesystem Inodes IUsed IFree IUse% Mounted on /dev/sda1 1.2M 445K 772K 37% / udev 32K 1.1K 31K 4% /dev shm 32K 1 32K 1% /dev/shm
#2 - DNS resolution
DNS resolution problems can cause your system and application to hang or timeout in very strange ways.
Some applications will log the name of inbound network connections, performing reverse lookups. If no NS is available, these connections may start to take a long time to connect, as the software waits for the resolver to timeout. If only the first listed NS has failed, this timeout may be variable in length, but probably around 15 seconds. If you see weird lags or delays, check your name servers. This can happen when you’re trying to ssh into the host – if you’re getting delays connecting via ssh, check DNS. If your software makes connections to external databases for example, and is configured to address them by name, you’ll see these timeouts.
This one can be tricky because some software will cache the name resolution and some local resolvers may cache – meaning you’ll see delays or timeouts sometimes, but not consistently.
Name lookups should be under a second, preferably in the low 100′s of milliseconds.
dkam@vihko:~$ time host www.apple.com www.apple.com is an alias for www.apple.com.akadns.net. www.apple.com.akadns.net has address 188.8.131.52 real 0m0.132s user 0m0.000s sys 0m0.000s dkam@vihko:~$ time host www.apple.com www.apple.com is an alias for www.apple.com.akadns.net. www.apple.com.akadns.net has address 184.108.40.206 real 0m0.011s user 0m0.000s sys 0m0.000s
You can see that in the second run, the name server had cached the value and returned much faster.
It also pays to check each nameserver listed in /etc/reslov.conf:
dig www.google.com @220.127.116.11
Naturally replace 18.104.22.168 with your name server’s IP.
Check “man resolv.conf” for more information.
#3 – Ulimits
The most common ulimit’s that I’ve come across is max number of open files, but you may see others including max user processes. This one is generally obvious if the software is running as a regular user – when you try to connect as that user you will see error messages about being unable to allocate resources. Editing a file or trying to read a man page will error out if you’re at the maximum number of open files. Network connections fall into this category also – so you may not be able to open network connections either.
The more likely scenario is that the software is running as a different user – one that people don’t log in as. Try logging in as, or su -’ing to the user – if you can’t or you can but the user can’t open files, check the ulimits. In Bash, try “ulimit -a” to view your limits. Different OSs limit these values in different ways – check your OS doco for details.
#4 - /dev/random
This is a little esoteric and is pretty unlikely, but /dev/random is used for lots of reasons – the most common use that you may have problems with is login with software that uses stuff like CRAM-MD5. Random data is used as part of the authentication process and when there’s not enough random data, logging in will be slow or may timeout completely. Most software should probably fall back to using /dev/urandom. You can time how long it takes to read 1kb of random data like this:
dkam@vihko:~$ dd if=/dev/urandom of=/dev/null bs=1K count=10 10+0 records in 10+0 records out 10240 bytes (10 kB) copied, 0.002011 s, 5.1 MB/s
#5 - permissions
Generally this will only bite you when you’ve made changes or updated software – check that config files are readable, data directories are read/writable and that executables are executable.
If you’re ever looking for a fun flying toy, I can confidently recommend the Syma S026. Flies around for about 5 minutes before needing a charge. With two sets of dual blades, you can rotate it left and right and fly forwards and backwards. Up and down too naturally. It’s tough enough that multiple crashes didn’t hurt it at all and it’s small enough that it doesn’t actually damage anything if you run into stuff. Well, avoid flowers and stuff precariously balanced. Candles also are best put away. You understand.
Update: Try eBay for even better prices! ( oh wait – $19.95 shipping!?!)
I’ve tried quite a few ways to deploy Ruby on Rails apps for Booko:
- Apache with mod_proxy and mongrel cluster
- Apache with mod_proxy and thin
- Nginx with thin
I only gave nginx a small run – I needed PHP for the blog (I’ve since moved Booko to it’s own host, so this wouldn’t be a problem) and it seemed great – but so far, I think REE + mod_rails is the best. It’s the most convenient to install/setup, it seems to be very fast, and it is quite nice to manage. You can restart your Rails application with a touch tmp/restart.txt – you know it’s restarted because the file disappears - that’s pretty nice. Naturally, you can restart Apache too.
So, this is a post on how to install Ruby Enterprise Edition and Phusion Passenger (aka: mod_rails) on Ubuntu 8.04 LTS in a VPS, specifically, Slicehost. It’s not hard, but this should make it even faster. You can read all about Ruby Enterprise Edition, but basically it’s a version of Ruby tuned to be fast and lean. It’s made by the guys who’ve created Phusion Passenger, or mod_rails and they work well together. I’ll be assuming that you want to use this version of Ruby for everything (ie – command line work as well as for mod_rails). IE, when you call irb or ruby or rake from the command line, you want to use REE.
I found an amazing bit of free software on the iTunes App store called Shazam. The idea of the software is that you send them a sample of some music, and they’ll figure out the Artist, Album, Trackname and so on. On the iPhone, what you do is fireup the Shazam App, click “Tag Now” button which starts the program recording music. It records between 8 – 15 seconds ( depending on how you configure it ) then sends that sample ( or maybe just a fingerprint of the sample ) off to the Shazam service on the internetz. A few seconds later, the program displays the artist, album, track and coverart. It it has any, it’ll also send links to listen to/buy the song on iTunes and links to YouTube to watch the filmclip.
In the few times I’ve used it, the hit rate has been very high. It has missed some tracks ( mostly Finnish tracks ) but overall it’s been quite amazing. I gave it a road test today standing in a café wondering what the music playing was. Fired up Shazam, and less than 30 seconds later, I’ve got all the track details, artist and album art. This track had no links to iTunes or YouTube unfortunately. Really quite a remarkable service.
Ian introduced me to this concept. I have to say it looks like fun. These guys are flying their planes via a wireless camera and a set of video googles. They also have gyros on their heads, such that when they move their head right, the camera pans right.
Apologies in advance for the terrible music.
This is the first video I saw — so cool.