AAMP Optimization - Speeding up MySQL

AAMP is an Apache MySQL PHP (AMP) server for Amiga OS 4.x that was ported by Edgar Schwan. I have recently started using it to host a development version of this website. This enables me to test upgrades and changes prior to uploading them to the live website. Previously I was making changes directly to this website, and hoping that it did not interfere with visitors. One issue with AAMP is that its performance is low. While this is not a big issue for a developer test server, when I did a test Silverstripe upgrade and rebuild, the MySQL server's response time dropped as low as 0.2 queries per second. When processing thousands of queries, this is unacceptably slow.

The problem stems from the fact that MySQL's default settings are designed to work on all machines, but be optimized for none. The optimal settings depends on the server specifications, and even the purpose that it is being used for. While I am a proponent of writing software to be self adapting/optimizing, MySQL's designers have chosen to leave optimization up to the server maintainer. Given the complexity involved, this is perfectly reasonable.

To aid in optimizing the server, MySQL comes with several example configuration files (NOTE: AAMP currently does not include these files; checked 2010/05/24):

• my-huge.cnf - for systems with at least 1 GiB memory,
• my-large.cnf - for systems with 512 MiB,
• my-medium.cnf - for systems with 128 MiB,
• my-small.cnf - for systems with less than 64 MiB.

Note that the system requirements for these configuration files assumes that Apache is also running on the same server. These files can be found on the internet by performing a simple search.

To improve the performance of the server, the mf.cnf file needs to be modified to enable various caches. With AmiCygnix, this file can be found at: System:Utilities/AmiCygnix/CygnixPPC/etc. I took the my-large.cnf configuration file as reference as I have a 512 MiB system. However, I halved the size of the key buffer, because I don't want the server to take up all of the available resources. The resulting configuration file was:

#
# my.cnf
#

[mysqld]
user=mysql

basedir=/Cygnix/CygnixPPC
datadir=/Cygnix/CygnixPPC/var/mysql-data
character_sets_dir=/Cygnix/CygnixPPC/share/mysql/charsets
plugin_dir=/Cygnix/CygnixPPC/lib/mysql/plugin

language=/Cygnix/CygnixPPC/share/mysql/english

lower_case_table_names=2
#one-thread
use-symbolic-links=false

skip_innodb

key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M



[mysqldump]
add-locks=FALSE
quick
max_allowed_packet = 16M


[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

With this configuration, the average number of queries per second rose from 0.2 to over 20 per second for the same task.¹ Still not particularly fast, but good enough for testing purposes. I'm sure that I could improve its performance even more by tweaking the settings.² My recommendation for anyone using AAMP or MySQL would be to start with the example configuration that most closely matches your system (or use my configuration), and tweak it for better performance. Also, do not forget that performance is also affected by the SQL queries themselves. Optimizing SQL queries is a whole different game though, and will not be covered here.

1. IMPORTANT: The queries per second figures obtained were from a very short and unscientific test. This does not give an accurate picture of performance over time.  In particular, this figure does not take into account server load, although I tried to keep conditions as similar as possible. Also, a lot of the speed is gained via caching, and the full benefits of those caches are not seen unless enough queries have been made to fill the caches.

2. I chose not to enable "skip-locking" which is present in the example configurations because, while it improves performance, I'm not sure if Silverstripe can handle its side-effects.