Flapping Head

I recently had to troubleshoot a busy mysql database server for a client. Here’s some basic tips.

SHOW PROCESSLIST is your friend

Inside mysql you can run show processlist to find out what the server is doing. From the list you can see how many connections there are, which database, whether they are causing a lock, waiting for a lock, and even the query being run. Very helpful.

You can get the same info from the mysqladmin program. A simple way to monitor what the server is doing is to have cron email you every hour (or whatever interval) with the process list:

crontab -e

MAILTO="you@example.com"
31 * * * * mysqladmin -u root -psecret --verbose processlist

Sleeping connections need closing

If you have a lot of connections in a sleep state, it means they’re not doing anything but are still held open. The server can run out of connections if they’re all busy sleeping.

Assuming the database is being accessed by a website (e.g. php code), you could look for ways to close the database connection sooner.

If you’re using Smarty, instead of:

// open mysql connection
// do stuff
$smarty->display('template.tpl');
// close mysql connection

you might try:

// open mysql connection
// do stuff
$output = $smarty->fetch('template.tpl');
// close mysql connection
print $output;

The difference is, you’re closing the mysql connection before returning data to the client. If you’ve got slow clients (and assuming no reverse-proxy or load balancer to spoon-feed) it could release the database connection much sooner.

EXPLAIN those queries

If you have slow queries, you can find out why using explain. See how many rows they need to read (perhaps there’s no index the query can use) and if they need to be optimised.

In mysql you can repair a table using:

repair table mytable;

How do you repair all the tables in a database? There’s no command for that, so you’ll need to repair each one individually.

That’s quite tedious, so here’s a bit of help in MySQL 5.

select concat('repair table ', table_name, ';') from information_schema.tables where table_schema='mydatabase';

Change mydatabase to the name of your database.

This gives you a load of repair table statements. They’re formatted in a table with vertical bars, so copy the whole lot, paste in to a text editor or word processor, do a find and replace: find “|” replace with nothing. Don’t worry about the spaces.

Copy and paste that lot in to your mysql command prompt. Job done.

I was moving and upgrading OpenX for a client (v2.8.5). I changed some things in the config file, then went in to the admin interface (install page).

I got a screen full of baffling error messages starting:

MESSAGE: Undefined variable: imgPath
TYPE: Notice
FILE: /home/nice/public_html/www/admin/install.php
LINE: 75
DEBUG INFO:

70 $oMenu->add(new OA_Admin_Menu_Section('install', '', ''));
71
72 if ($oController->hasLayout()) {
73 //layout
74 $oPageHeader = $oController->getModelProperty('pageHeader');
75 phpAds_PageHeader('install', $oPageHeader, $imgPath, false, true, false);

76 }
77 if ($view) {
78 $view->display();
79 }
80 echo $actionContent;

MESSAGE: Only variable references should be returned by reference
TYPE: Notice
FILE: /home/nice/public_html/lib/OA/Admin/Menu/Section.php
LINE: 354
DEBUG INFO:

349 {
350 if ($this->type == $type) {
351 return $this;
352 }
353 else {
354 return $this->parentSection != null ? $this->parentSection->getParentOrSelf($type) : null;

355 }
356 }
357
358
359 /**

MESSAGE: Only variable references should be returned by reference
TYPE: Notice
FILE: /home/nice/public_html/lib/OA/Admin/Menu/Section.php
LINE: 354
DEBUG INFO:

349 {
350 if ($this->type == $type) {
351 return $this;
352 }
353 else {
354 return $this->parentSection != null ? $this->parentSection->getParentOrSelf($type) : null;

355 }
356 }
357
358
359 /**

MESSAGE: Undefined index: PREF
TYPE: Notice

Undefined variables. Something’s wrong with reading the config file.

I had to edit the code to find out what the problem was. In the init-parse.php file, look for $conf = @parse_ini_file. Remove that @ sign – it’s suppressing the error message. Try again and you should see an error saying can’t parse ini file on line X.

In my case, it’s because I put a dollar sign in the database password. Seems that’s not valid in the ini file. I changed the password and it’s working.

Pretty obscure, no?

I just opened gvim on my Ubuntu (Hardy Heron) box and found there was no menu bar (File, Edit, etc).

After messing with some guioptions and getting nowhere I ran gvim as root (using sudo) and the menu bar was there. The answer came from a forum post by “Marko”:

Delete the file ~/.gnome2/Vim

It will be recreated when you run gvim again. With luck, the menu will be displayed again.

Andrew and I spent a few days this week putting together a new Django project.

It’s called Layoff Talent and it’s a place for people in the tech industry who have been laid off and are looking for a new job. They can add a simple profile and then hopefully be picked up by employers looking for new talent.

It’s similar in some ways to Django People or the Djangogigs developer listings, but specifically for people who have been laid off and not restricted to Django developers.

There’s nothing ground breaking from a development point of view, but it’s another example of how Django makes it easy to put out a full-featured site in a short time. Of course, we’ll be adding more features as the site gets popular.

If you know someone who has been laid off, please tell them about layofftalent.com.

Getting selected text in a Firefox extension

I’m developing a Firefox extension for a client which does something with the currently selected text in the browser window.

The standard way to get the selection is with window.content.getSelection().

Selection is empty in Gmail

Some users reported that selected text in Gmail messages wasn’t being found by the extension. I suspect the issue is with content added using JavaScript, but I haven’t investigated.

An alternative way to getSelection

The standard Search Google for “whatever” contextual menu item does work in Gmail, so obviously it gets the selection another way.

I found a function getBrowserSelection() in the browser.js file in Firefox’s chrome. It is used by Firefox for the contextual menu search.

This is how it gets the selection:

var focusedWindow = document.commandDispatcher.focusedWindow;
var selection = focusedWindow.getSelection();

I don’t know what the difference is, but I am now using this code in my Firefox extension and it is working well.