Tag Archives: MySQL

About downtime on my blog: lack of RAM and swap! Also, what is “swappiness”

Over the last few days I had a bit of downtime. I have no monitoring set up for the services, and I didn’t have problems on the previous server. But having migrated to DigitalOcean, it turns out that its default configuration does not provide a swap partition, and that 512mb is too little for Apache2 and MySQL (at least with configurations I had on the previous server).

Even after a bit of reconfiguration to tell both Apache2 and MySQL to back off a bit, every few hours Linux would choose MySQL as the “less important” process to kill, due to lack of memory. And MySQL would not automatically restart (because it’s not set up to do so).

So I’ve done some deleting of old hosted files (freeing up a bit of disk space) and have set up a 1gb swapfile (note that the instructions are intended for 256mb servers, hence creating a 512mb swapfile).

In the process, I learned about sys.vm.swappiness, which is a kernel setting that apparently controls when the kernel will decide to swap things to the swap partition or swap file. By default set to 60, as the article instructs I’ve set it to 0 (which makes sense, as I want swap to be used as rarely as possible). IBM also suggests 0 in KVM-based virtual machines which is also what DigitalOcean uses.

This article from 2007 suggests you may even want to increase it in some cases (when you have a lot of inactive jobs waiting to be run), and force kernel to more aggressively find unused memory pages that it can swap to the disk. This AskUbuntu answer has a good explanation on what swappiness means: a setting of 60 means that at 40% usage kernel will attempt to find inactive pages to swap, and a setting of 10 means that at 90% usage kernel will attempt to find inactive pages to swap. Wikipedia also has an article, which sadly does little to explain the actual mechanisms.

A better solution would be to force MySQL and Apache2 to use appropriate amounts of RAM (and abide by some absolute limits, such as 196mb+196mb). But that’s something I’ll play with on another day… there are more important things to work on right now. 😉

After MySQL upgrade on OS X, all tables are missing

When upgrading MySQL on OS X (anno domini 2010),  I learned the hard way that the “data” folder containing your databases and tables does not get moved. Look into /usr/local; you will probably have the old mysql-* folder there containing just the “data” folder. /usr/local/mysql is a symlink to the current MySQL installation, so move its data folder elsewhere, and put the old one in there.

PHP on Mac no longer able to connect to MySQL

It’s been a while since I did web development on my Mac, and MySQL ceased to work. Could be related to that one time that I accidentally removed a bunch of dot-files from my home directory, could be related to MySQL upgrade. In any case, it no longer worked.
What I figured out was that the /var/mysql/mysql.sock UNIX domain socket was no longer generated. The /var/mysql folder was missing. When just creating it and assigning it to user _mysql (chown _mysql /var/mysql) did not help, I knew something was wrong with configuration.

MySQL tries to read configuration from /etc/my.cnf. To get its UNIX domain socket where PHP expects it, this needs to be contained in this configuration file:

[client]
port = 3306
socket = /var/mysql/mysql.sock


[mysqld]
port = 3306
basedir = /usr/local/mysql/
socket = /var/mysql/mysql.sock
; datadir = /servers/raiddrive/databases/

Please read the manual for details, and this is not administration advice; I’m not sure if this opens security holes, but I only use MySQL for development purposes. (Because you should know better than letting programmers near mission critical production servers.)