Thanks to PickledOnion and SliceHost for posting this on their tutorials site, here's a quick example to improve your MySQL speed.
Tuning your Install
Remote onto your box via SSH, and...
sudo nano /etc/mysql/my.cnf
Go down till you see a section for the [mysqld] settings, then add...
default-storage-engine = MyISAM
This will set MySQL to use the MyISAM storage engine (lighter than InnoDB) for all newly created databases.
Now look for anything called...
#skip-innodb
delete the # to make skip loading the InnoDB engine, if you're not using it, changing it to...
skip-innodb
NOTE: Ubuntu Gutsy MySQL 5 uses InnoDB by default (i've found) , so if you haven't set the type and your on Ubuntu Gutsy with a database already on there; then chances are it's under InnoDB. so skip this one for now (see bottom).
Now navigate to the Fine Tuning section and change the values to:
# * Fine Tuning
#
key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
thread_cache_size = 4
Add these also,
sort_buffer = 64K
net_buffer_length = 2K
Save the file and restart MySQL with...
sudo /etc/init.d/mysql restart
Job done, you can run:
top
To see your memory usage (exit with 'q')
Convert Tables to MyISAM
Log into your MySQL server via...
mysql -u root
Now type,
show databases;
Choose your database,
use my_db;
Now see what tables are around,
show tables;
Now you can run a command to convert your tables over to the MyISAM format,
ALTER TABLE my_table ENGINE = MyISAM;
More here...
Restart MySQL
Easily done via,
sudo /etc/init.d/mysql restart
And If All Fails
Now for me those settings weren't completely ideal for Typo5's performance so I switched them back to the default settings, with my other apps they were fine (think they're good for memory-starved situations).
The default settings are...
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
#sort_buffer = 64K
#net_buffer_length = 2K
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
