Tuesday, May 25, 2010

Low memory MySQL / Apache configurations

SkyHi @ Tuesday, May 25, 2010

Our VPSLink1-3 Plans include limited resources that may make running Apache/MySQL difficult under certain circumstances. Below you will find some sample configurations to help ensure you're not allocating resources unnecessarily.

If you would like to convert your InnoDB tables to MyISAM, you can use the script located at Convert_InnoDB_to_MyISAM

MySQL 4

Place the below configuration into /etc/my.cnf and restart your mysql server to begin using the new configuration.



[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

# For low memory, Berkeley DB should not be used so keep skip-bdb uncommented unless required
skip-bdb

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

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50


[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



Apache

Make your sure httpd.conf (/etc/httpd/conf/httpd.conf) is not configured to start too many servers, or have to many spare server sitting around. Reference the example below



StartServers 1<br />MinSpareServers 1<br />MaxSpareServers 5<br />ServerLimit 50<br />MaxClients 50<br />MaxRequestsPerChild 5000<br />


Also, make sure to adjust KeepAliveTimeout (to say, 2 or 3).

The default configuration file for apache also frequently loads every module it can. This is an especially big deal with the prefork mpm, as each apache instance will eat up geometrically more memory when unneeded modules are enabled. Comment out any modules that aren't needed to save yourself some more memory.

Apache 2.2.X Note: The configuration files are likely under /usr/local/apache2/conf. Additionally, uncomment the following line in httpd.conf


# Include conf/extra/httpd-mpm.conf<br />

Then you can edit this file with the above tip. One last tip is to comment out the features you don't currently use (e.g. webdav).

Also, make sure Apache is setup to use the right multi-Processing Module for your setup. mpm_prefork served me nicely on my vps-link-4 account, while mpm_worker hogged too much RAM.

On Ubuntu with a vps-link-3 account, this is the mpm-prefork package to install:


apt-get install apache2-mpm-prefork<br />

This is essential to stay under the memory limit. This reduced my apache footprint from around 225MB (one thread) to about 12MB per thread.

External Resources

Optimize Apache Link 1

Optimize Apache Link 2


REFERENCES
http://wiki.vpslink.com/Low_memory_MySQL_/_Apache_configurations