Tuning LAMP Server Performance

Having trouble with a crashing webserver? Is MySQL or Apache eating up your RAM and trashing your drive swapping? Don’t worry, there are good tools to help you get started in tuning your LAMP server to avoid crashes.

There are two scripts that I find invaluable in getting me a first and fast opinion on the current status of the server. The trick is often to get the settings right so that they do not risk eating up your RAM for breakfast. Here are two scripts to help you get your MySQL and Apache settings right for you.

MySQLtuner
This script will test your MySQL settings and suggest performance improvements. By using statistics from MySQL of current state of performance it will suggest modifications to your settings.

Apachebuddy
Inspired by MySQLtuner.pl Apachebuddy.pl does the same thing to Apache server settings. It checks your current settings and calculates average as well as maximum RAM usage and suggest improvements.

These script does of course not replace knowledge. Use them as a first opinion but then educate yourself about the settings before changing anything.

Both scripts have nifty URLs to download from:
mysqltuner.pl
apachebuddy.pl

Virtual Ubuntu 8.04 JeOS LAMP Server

I’ve found the perfect portable development environment for me! First of all I wanted a virtual environment in VMWare since I have access to that software at several locations. Secondly since I would be using it on laptops I wanted the virtual machine to use as little memory and disc space as possible.

Requirements for this tutorial:

First I create a new virtual hardware for the Ubuntu distribution. Standard options straight through except I choose to have a virtual harddrive split into 2 Gb files and only created when needed (to save space, since this is kind of one of the main points of JeOS). Next I choose the ISO-image of the downloaded JeOS distribution as an imported CD-ROM drive for the virtual machine and boot it up.

Throughout the installation I just make the default choices (with only a few exceptions for country and keyboard layout). The installation is fast and the system is up and running, but no graphics of course! Just a waste on a server machine anyway. The first thing I want is the comfort of continueing from my main computer and therefore I install OpenSSH server.

sudo apt-get install openssh-server

After the installation I can freely connect from any other machine on my network which in my case is much more confortable than sitting peeking into a virtual machine on my serverbox.

To logon remotely I need to know the current IP-adress of the JeOS virutal system, to easily get it I write:

ifconfig | grep -w inet

This will show me the current available IP-adresses on the machine, one of them is the loopback (127.0.0.1) and if there are others (there should be only one with the current installation) those are the public IP-addresses. In my case it’s 192.168.0.16. If you are running IPv6 change “inet” to “inet6” to get the public IPv6 IP address.

After I logon (in my case using PuTTY from a Windows machine) I start the process of making it a LAMP server (Linux Apache MySQL PHP5). To make things easy Ubuntu provides a command called “tasksel” that packages an installation togheter into one command:

sudo tasksel install lamp-server

This is suprisingly easy, just wait and the only thing the installation asked me about was what root password I wanted for MySQL.

I use nano to edit text files, you might use something else, either way I need to install it since it’s not included in the standard JeOS distribution.

sudo apt-get install nano

Now we need to make MySQL accessible from remote computers (default setting is to only allow localhost). To do this we need to edit the file /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Please not that this can be considred unsafe since it exposes the MySQL interface to the whole network! This is only done since I want a development machine that I need to administrate remotely (might be from the same computer but with the server running virtually in the background). To change the bind address you have several options, but first off all find this line within my.cnf:

bind-address = localhost

Instead of localhost you can enter the IP you want it to bind to, if you comment out the line MySQL will by default bind to you’re current IP (which is recommended if you have a dynamic IP-address).

Now we need to create a database and give remote access to it. We need to start MySQL on the command line, so we run:

mysql -u root -p

MySQL will now prompt for your password (the one you supplied earlier in the installation) and then you are in. First I create a new database, the purpose of which is to develop WWW-services so I aptly name it “www”.

create database www;

Next I want to grant myself remote access to this database so I run:

grant all privileges on *.* to ‘magnus’@’%’ identified by ‘passwordgoeshere’ with grant option;

Again, this is UNSAFE for production environment, the above command grants limitless access to the database to any host that identifies itself as me with my password. Consult the MySQL documentation for better limited access and also (if you know which host(s) you will need access from) change the wildcard to a specific IP-number.

I can now access and administrate the MySQL database using whichever tools one favours, I recommend the GUI tools from MySQL.

Another handy tool is phpmyadmin, with this php-based GUI you can administrate the MySQL database through the web browser:

sudo apt-get install phpmyadmin

After the installation simply point the web browser to the /phpmyadmin folder on the webserver of the virtual machine. For example if you’re virtual machine have the IP 192.168.0.50 then the address would be http://192.168.0.50/phpmyadmin

Done! You can now run the virtual server with Apache2, MySQL and PHP5 remotely from any computer in your network and administrate it through SSH. At the end of the installation my virtual machine used 720 Mb discspace which should be compared with the several gigabyte discspace used by a standard Ubuntu installation. Also with the absense of graphical interface the memory consumption is very low and the virtual machine can run on as low as 128 Mb of RAM if needed.

I can add as a bonus that when I later installed a SAMBA server and WordPress 2.6 on the virtual machine the size grew in total to just below 800 Mb and memory set at 256 Mb of RAM. This will have to be a topic for another post though.

Updated 2008-08-02
* added instruction of how to obtain the IP-address of the JeOS virtual machine.
* fixed typo in grant statement