Tuesday, September 29, 2009

Using the MySQL EXPLAIN statement

Recently I had been having trouble with queries on a certain table in a system that I've been maintaining. I had gone through just about every excuse for why queries on the table could be performing so slowly : the machine was slow (DB running on a VM), the webserver was slow (also running on a VM), I wasn't using the native libraries (webserver was Tomcat), I had other processes running in the background (I didn't). Then I ran across a tip on a forum suggesting usage of the MySQL EXPLAIN statement. I had known all about it for the longest time, but it never occurred to me to actually use it (I think I'm that good at writing queries, turns out : I'm wrong). After using the EXPLAIN statement, I found out that the query processor was using a suboptimal query plan which utilized an index I had added with the intent of improving performance (the index had a fairly high arity, so choosing to use it was sketchy at best in the first place). Most DBMSs should have a similar functionality built in. I think that'll be the first place I go in future.

Friday, September 18, 2009

Ubuntu 9.04 servers slow to login

Recently our production system's servers have been getting progressively slower, and it has finally gotten to the point where it's merited my full attention to remedy the situation. In my research on the problem, one of the things I came across was how incredibly slow the logins were (among other problems). After watching top during numerous logins, I saw that console-kit-daemon was going horribly slow and shooting the sshd CPU usage through the roof. After some time googling for issues related to console-kit-daemon, I found that many people were getting errors in their /var/log/daemon.log file regarding console-kit-daemon being unable to initialize policykit. After installing policykit, my logins to my production servers are now lightning fast