Fine tuning apache and mysql for performance and security

I’ve gone through a process of migration of a server, with help with provided migration scripts, and some hard-coded scripts to help synchronizing latest data, and permission fixing. All done well, and working fine now. However, all installation of services were default, and did not optimized for our needs.

There are few aspect of tuning need to be done, relative to your resource, in terms of available memory, harddisk I/O, and some other considerations. Surely, you want the best performance, with high level of security. At some point, there are some aspect that you should be compromised to get the best in other aspect.

Apache

There a few configuration that would need a change, for example, HostnameLookups. By default it was turned On. While turned on, it will add a latency to the request to resolve the IP before the request were completed. You can disable it by replacing it with

HostnameLookups off

DirectoryIndex negotiation is an option for you to determine what file to be the default file to be loaded in any document root. Avoid using wildcards, and enter the options specifically in httpd.conf file, ordered by the priority
For example:

DirectoryIndex index.php index.htm index.html index.shtml index.phtml Default.htm Default.html

Apache 2.0 equipped with Multi-Processor Module (MPM) which will handle apache connections, handling requests, and forking child process for the requests. There are a few options to chose from, and the most common are worker and prefork. Each of them have their own advantages and disadvantages. Make sure you read through the documentation to understand each options, and how they would help you apache process/request handling.

In my case, I’m using prefork.
StartServers 15
<IfModule prefork.c>
MinSpareServers 10
MaxSpareServers 25
</IfModule>
MaxClients 255
MaxRequestsPerChild 10000

If you are using cPanel, you can edit this in WHM Control Panel -> Service Configuration -> Apache Configuration -> Global Configuration.


A few more to add
ExtendedStatus Off
ServerSignature Off
ServerTokens ProductOnly

http://httpd.apache.org/docs/2.0/mpm.html
http://httpd.apache.org/docs/2.0/misc/perf-tuning.html
http://www.webreference.com/programming/Apache-Performance-Tuning/
http://www.devside.net/articles/apache-performance-tuning

System

Not to forget, some tuning might be needed for memory limit, tcp tuning, and also filesystem. You might want to have a look at this site:
http://www.performancewiki.com/linux-tuning.html

There are a few disk benchmarking tool that you can use :
bonnie
hdparm
iostat
collectl
seeker : http://www.linuxinsight.com/how_fast_is_your_disk.html

This article do provide a good base for you to start with harddisk IO benchmarking, using a few tools from above. For other tools, sure you can find your way to some manuals. 🙂

PHP

On the PHP side, there are also few options for optimization, that could make your page load faster. One of the most effective technique is caching. eAccelerator did well for PHP caching, where it will cache PHP code execution in a compiled state. By this way, it will reduce the overhead of compiling PHP code per request significantly.

This tutorial below provide a simple yet useful instruction on how to install eaccelerator on your server. Do change the options accordingly. You can refer to the Setting options from the official site.
Beside that, try to have a look on Xcache, a PHP opcode cacher, that seems to be one of the popular PHP cacher.

There are a few options to compressed the content delivered to the user. One way is by using mod_deflate apache module. There are a few tutorial to implement mod_deflate, such as by HowtoForge, LinuxJournal, CyberCiti.biz and Devarticles.com.

There are also options for you to turn on compression in php.ini runtime config.
output_buffering = On
output_handler = ob_gzhandler

mysql

Now we’re going into mysql optimization. I need to grab my dinner, so, I’ll put this in short. 😛

Do caching on mysql, enable this by editing your my.cnf file. There are many options of caching that you can do on mysql, and below are sample of my settings that works well for me.

[mysqld]
query_cache_type = 1
query_cache_limit = 8M
query_cache_size = 32M
thread_cache_size=20
thread_concurrency=4
key_buffer_size = 16M
query_cache_limit=16M
join_buffer_size=16M
tmp_table_size = 256M
max_heap_table_size = 256M
table_cache = 4096
open_files_limit = 1000
log-queries-not-using-indexes
#custom entry
#log = /var/log/mysql/mysql_query.log

I put additional line for query log, to monitor if there will be anytime i need a log of queries performed on the server.

The business of tuning is specific to hardware, and your application as well. You need to determine what best for your environment. Try some setting, know what are the configuration are for, and tune them one by one to achieve the best result.

There are a few tools that could help you to identify what might be needed to be tuned.

tuning-primer.sh

Mysqltuner.pl
Mysql tuner is easy to use, you can just run these 2 lines of command to see the result of the report.
# wget mysqltuner.pl -O mysqltuner.pl
# perl mysqltuner.pl
mysqltuner

mtop
mtop is like top which you would use to monitor resource usage live. mtop look the same, utilizing ncurses libraries to generate the output.

You can monitor your cache and thread usage by issuing these in mysql console
show status like ‘qcache%’;
show status like ‘%thread%’

Do monitor your slow queries as well, pin down whose taking down your mysql server.
mysqladmin -u root -p status
mysqladmin -u root -p version

Last one, do defrag your fragmented mysql tables. Phil Dufault wrote a bash script that will do the work for your, for all your mysql databases that you have.
http://www.dufault.info/blog/a-script-to-optimize-fragmented-tables-in-mysql/

And once in a while you might want to repair all tables in all database. Here 2 scripts that will do that for you.
http://www.webdevlabs.net/2010/08/bash-script-to-repair-all-mysql-tables.html

Leave a Reply

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