It’s been over a month without any activity, but the SQL server build is moving forward again. In the previous five installments I was installing, configuring and getting comfortable with the the basic Ubuntu install.
The past articles can be found in the Ubuntu Server Project section on my Linux page.
Today’s task is to install MySQL, which is very straight-forward. I’ll make some guesses at a low-memory configuration but I’ll wait until the server is completely up before drilling deep into the optimizing.
I’ll be needing the original Ubuntu server CD for the installation. So I make sure that the CD drive for the VM is configured to open the ISO file with the Ubuntu server CD image. After I start the VM I connect with a remote SSH terminal session and mount the CD with the command
Since the server has been untouched for over a month I start off by making sure the installed software is already up to date by executing the following commands:
sudo aptitude update
sudo aptitude safe-upgrade
Once this is done I’m ready to go. To install MySQL I use the following aptitude command:
sudo aptitude install mysql-server mysql-client
MySQL-server and MySQL-client are meta packages. They’ll install the latest server and client software that’s in the repository, which is currently MySQL 5.0.45. After running the command aptitude tells me the list of packages that will be installed.
Then I’m prompted to insert the CD, which is already mounted, so I hit <return> to move along.
I’m then prompted to enter a root password for MySQL. Even though this is a private server off the Internet I go ahead and enter one. There’s no confirmation prompt so I type carefully.
The installation runs and finishes a couple of minutes later without any problems. Now it’s time to configure MySQL for my small 256MB server.
Configuring MySQL for a 256MB Server
I’m not using the word optimize here for a specific reason. I won’t start optimizing until I have a WordPress site built and can better test performance. I’ll be configuring MySQL to have a small memory footprint now and I’ll optimize later.
First I check the current SQL memory usage by running
top then pressing <shift>-<m> to sort by memory usage. This shows MySQL using about 6% of memory.
I open up the MySQL configuration file in the nano editor:
sudo nano /etc/mysql/my.cnf
The first thing I want to do is disable InnoDB. InnoDB is a storage engine but WordPress uses MyISAM by default so I don’t need it. Since it uses a great deal of memory I’ll turn it off by uncommenting (remove the #) the line
I then search for and change the following values:
keybuffer from 16M to 16K
This is probably too small a value. But I’ll start low and raise it once all the other server software is installed and I start evaluating performance. From what I’ve read this setting is critical to performance and I’ll probably want to increase it.
max_allowed_packet from 16M to 1M
I’ll also look at increasing this once everything is installed. Memory is only allocated when needed but I shouldn’t be sending to much data in and out of MySQL through WordPress
thread_stack from 128K to 64K
This server is not going to have a lot of concurrent connections so we’ll start low and see how things look.
thread_cache_size from 8 to 4
This is the number of threads that are cached (after a user disconnects). New threads are only created when there’s none in the cache. Again, I lowered it because this will be a lightly used server.
I then added the following two new parameters just after thread_cache_size.
sort_buffer = 64K
I’m again starting with the smallest value that I’ve seen recommended for low memory servers and I’ll work my way up.
net_buffer_length = 2K
This is the starting size for the connection and result buffers. Both shrink to this size after each SQL statement.
After making all the changes I save the file and restart MySQL with the command:
sudo /etc/init.d/mysql restart
Then I run top and check the memory again. Now MySQL is only using 1.9% of memory.
So I’ve dropped memory usage by 2/3. Performance tuning will wait for a later session. Apache and PHP 5 are next on the to do list.
Optimizing Apache & MySQL for Low Memory (Part 2) at Emergent Properties
MySQL Memory Usage at MySQL.com
InnoDB article at Wikipedia