Saturday, October 4, 2008

Low Memory Mysql / Apache configurations

Configurations will be good for 64Mb - 256Mb RAM

If you would like to convert your InnoDB tables to MyISAM, you can use the shell script to automatically convert InnoDB tables to MyISAM.

#!/bin/bash

MYSQLCMD=mysql

for db in `echo show databases | $MYSQLCMD | grep -v Database`; do
for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e's/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/'|grep -v 'Create Table'`
if [ $TABLE_TYPE = "InnoDB" ] ; then
mysqldump $db $table > $db.$table.sql
echo "ALTER TABLE $table ENGINE = MyISAM" | $MYSQLCMD $db
fi
done
done

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 = 128K

# 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
MinSpareServers 1
MaxSpareServers 5
ServerLimit 50
MaxClients 50
MaxRequestsPerChild 5000

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

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.

On Ubuntu mpm-prefork package to install:

apt-get install apache2-mpm-prefork

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

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

No comments: