Tuning Apache2 & MySQL

I’ve spent a lot of time on tweaking our Apache2 and MySQL-Cluster setup to gain more performance without assigning more resources to it. I ended up with a very good configuration for several thousand visitors per day.

Hardware Configuration
– Make sure you have enough processing power on the webservers. Mysql-cluster uses relatively low CPU, apache uses a lot
– Make sure that the storage you are using is fast enough (no IDE drives, please!)
– Make sure your swap file is big enough. I recommend using 3-4 times the amount of ram as SWAP drive.
– Make sure you have enough bandwidth between de database nodes, they tend to exchange a lot of data.

Tweaking the web servers (Apache2)
While tweaking web servers i would recommend using Firebug (the Firefox extension) to see where the delay is coming from. Most developers already have this to debug code (like JavaScript). To tweak your website for apache please read this documentation!

I would recommend using some kind of PHP Accelerator or Caching mechanism. I’m a big fan of php5-xcache but this is up to you, for me this already compresses about 50% of the data traffic and significantly improves the page-loads.

Check how many clients you have on a regular basis and set the MaxClients option in Apache to reflect this. Make sure you do not set this too high. This formula is usually the maximum that you can use:

MaxClients = TotalMemory * 80% / Max Memory Per Process

The maximum memory per Apache process is usually around the 8-9 MB but this depends on your situation, make sure to check this!

Now, check all the Apache Modules that you have enabled. All of the modules take resources from the server and i guarantee you don’t need all of the default enabled modules. Most of them actually. When using Linux it is also recommended to set the MaxRequestsPerChild to a maximum of 25.

Some short other hints:
– If you have a lot of hits make sure to put the ‘KeepAlive’ option off in the apache configuration.
– Leave HostNameLookups off
– Set DirectoryIndex correctly so as to avoid content-negotiation.
– For the Options directive, avoid Options Multiviews as this performs a directory scan.

Setting up the XCache module
So, what is XCache;
This was taken from the XCache website: “XCache is a fast, stable PHP opcode cacher that has been tested and is now running on production servers under high load.”

To give you an idea of the difference that XCache can make i have done a test with the tool called ‘ab’ and will post here the requests per second before installing xCache and after the installation:

Req/s before: 52
Req/s after: 142.3

This means i doubled the requests per second simply by enabling xcache in default mode! Amazing..

The installation of XCache is fairly simple, just install it on your OS (debian: apt-get install php5-cache) and leave everything default at first.

MySQL Server performance
Important: You must make backups of your database before attempting to change any MySQL server configuration. After any change to the my.cnf, restart mysqld.

First start by analyzing the query’s that might cause a problem for you. You can do this by enabling the ‘low-slow-queries’ option:
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

Restart MySQL and start tailing the logfile to see what query’s take more then a second to load (more then a second could mean a problem!). Start debugging these query’s to improve performance and then start looking at the indexes of your tables (if you have any?). You can use the ‘EXPLAIN EXTENDED‘ method in MySQL for more information about the query’s/tables and hopefully the problem.

Last thing for now, check the ‘Table_locks_waited’ in MySQL. You can do this commandline with the exact command: show status like ‘Table_locks_waited’; If you see this number growing it means something is going wrong and a good reason to investigate.


Geef een reactie

%d bloggers liken dit: