Reducing the RAM useage of MySQL

One thing I noticed when setting up the MySQL database was the amount of RAM that it was using on my server. It seems that when you install MySQL it is fully featured and set up for much bigger useage than your average webserver requires. My initial install was using over 100Mb. What we need to do reduce it is to create a configuration file that MySQL looks for when it starts up. In our initial webserver setup we don’t have one, so the MySQL database server starts with defaults.
To create this file from the command line you need to open up a command line text editor. There are a few about, nano, pico, vim etc. My preferred one is vim. Basically to create a file in vim you just need to start vim and state the file path. As MySQL looks for it’s config file called my.cnf in the /etc folder we simply type;

1
vi /etc/my.cnf

Now pressing i to start insert mode on the vi text editor we can type, or copy depending on what system you are using, the following text into our my.cnf document.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[cc lang="mysql"][mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K

# For low memory, InnoDB should not be used so keep skip-innodb uncommented unless required
skip-innodb

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout"[/cc]

Save the file from vi, by pressing Esc, then typing :wq this will write the file to /etc/my.cnf and quit the text editor.
Now we need to restart MySQL by using;

1
/etc/init.d/mysqld restart

Tada! Now your MySQL installation is consuming almost half the amount of RAM as before, whilst still offering way more features than you are ever likely going to need for a wordpress web host.
This could probably be trimmed down even more if needed, though this will do for starters.

^