optimizing apache mysql php with low memory server

Don’t run X, shutdown all unnecessary services, and compile Apache, MySQL, and PHP with only essential functionality.

 

Apache

The biggest problem with Apache is the amount of ram is uses. I’ll discuss the following techniques for speeding up Apache and lowering the ram used.

  • Handle Fewer Simultaneous Requests
  • Loading Fewer Modules
  • Log less

Tune Apache to only have a small number of spare children running.

Prefork is where the real magic happens. This is where we can tell apache to only generate so many processes. The defaults here are high it’s eating your server memory. Make your sure httpd.conf is not configured to start too many servers, or have to many spare server best weight loss sitting around. Reference the example below:

    StartServers          1
    MinSpareServers       1
    MaxSpareServers       3
    MaxClients           10
    MaxRequestsPerChild 3000



    StartServers          1
    MinSpareThreads       5
    MaxSpareThreads      15 
    ThreadLimit          25
    ThreadsPerChild       5
    MaxClients           25
    MaxRequestsPerChild 200

Also, make sure to adjust KeepAliveTimeout to 10 or 15. In my opinion, 15 seconds is longer than a short page view requires, and shorter than a long page view requires.

Only load the modules you require

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. To check which apache modules are enabled/installed, use command below:

# apache2ctl -M

Default Apache modules (may be difference with yours):
Apache Loaded Modules

Comment out any modules that aren’t needed to save yourself some more memory. Or you can change it by use commands below:
To enable a module:

# a2enmod module_name

To disable a module:

# a2dismod module_name

You must restart the server after enable/disable the modules:

# service apache2 restart

Log Less

If you’re trying to maximize performance, you can definitely log less. In my server, I set it to error level. Also, if you don’t care about looking at certain statistics, you can choose to not log certain things, like the User-Agent or the http-referer.

# ErrorLog: The location of the error log file.
# If you do not specify an ErrorLog directive within a
# container, error messages relating to that virtual host will be
# logged here.  If you *do* define an error logfile for a
# container, that host’s errors will be logged there and not here.
#
ErrorLog ${APACHE_LOG_DIR}/error.log

#
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
#
LogLevel error

I like seeing those things, but it’s up to you.

Optimize MySQL server

Tweaking MySQL to use small amounts of memory is fairly straightforward. I’m going to try to show you the why instead of the what, so you can hopefully tweak things for your specific server. We’ll look at the following MySQL types of mysql settings:

  • Things We Can Disable
  • Turning MySQL Server Parameters
  • Third Party Configuration Wizard for MySQL

To optimize mySQL we need to edit the mySQL configuration file, it’s /etc/mysql/my.conf

Things We Can Disable

MySQL allows a few different storage engines for its tables. The two most common are InnoDB and MyISAM. The main difference between the two is:

  • MyISAM offers table-level locking, meaning that when data is being written into a table, the whole table is locked, and if there are other writes that must be performed at the same time on the same table, they will have to wait until the first one has finished writing data.
  • InnoDB, on the other hand, offers row-level locking, meaning that when data is being written to a row, only that particular row is locked; the rest of the table is available for writing.

The problems of table-level locking are only noticeable on very busy servers. For the typical website scenario, usually MyISAM offers better performance at a lower server cost.

If you decide to use only MyISAM tables, you must add the following configuration lines to your my.cnf file:

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM

If you only have MyISAM tables, you can disable the InnoDB engine, which will save you RAM, by adding the following line to your my.cnf file:

skip-innodb

If you use InnoDB from the past, here is a simple 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

 

Turning MySQL Server Parameters

There are several parameters that can be adjusted on a MySQL server to make it faster.

Key buffer size

This is probably the single most important thing you can tweak to influence MySQL memory usage and performance. MySQL tries to put everything that’s indexed into the key buffer so this is a huge performance speedup. The SQL query will be served directly from RAM. I can’t say what size you should make your key buffer, because only you know how much ram you have free.

The Query Cache

If you do the same query two times in a row, and the result fits in the query cache, mysql doesn’t have to do the query again. If you’re going for performance, this can be a huge benefit, but it can also eat up memory. So, you need setting it’s not too high and low as your website needed.
There are three variables that influence how the query cache works.

  • query_cache_size
  • query_cache_limit
  • query_cache_type

Maximum Number of Connections

It’s option parameter. If you’re already limiting the number of apache processes, then you’ll be fine. If you’re not, and you need to handle thousands of users simultaneously, you need to increase this number.

The Table Cache

Every time you access a table, MySQL loads a reference to a table as one entry in the table cache. This is done for every concurrent access of a table, it’s really important for performance, marginally so for memory usage. You can keep upping the table cache, but you’ll eventually hit a limit on the number of files your operating system can have open, so keep that in mind. If table cache is set too low, mysql will throw up on you, and you don’t want that.
Here is current my.conf that I’ve optimized on my VPS.

[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, 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

Optimize PHP & Caching

PHP is not very memory intensive, so I don’t think you should worry too much about memory usage, unless your app needs it, in which case the memory footprint of PHP won’t be too significant. But I’ve researched then found some tweaks of PHP configuration that decrease memory usage of your webserver.

; Limit the memory to 40M should be fine for barebones WordPress
memory_limit = 48M
realpath_cache_ttl=300
realpath_cache_size=1M
This entry was posted in Apache, MySQL, Optimize, PHP. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.