Optimize MySQL & Apache on cPanel/WHM server

Optimize MySQL & Apache on cPanel/WHM server

On this optimization process we will go over the Apache core configuration and modules that are part of Apache core. We think that with the correct settings of Apache and MySQL you can get excellent results and the correct level of resource use without installing third-party proxy and cache modules. So let’s start,

 

Apache & PHP

In the first stage we run the Easy Apache and selected the following:

  • Apache Version 2.4+

  • PHP Version 5.4+

  • In step 5 “Exhaustive Options List” select

– Deflate

– Expires

– MPM Prefork

– MPM Worker

After Easy Apache finished go to your WHM » Service Configuration » Apache Configuration » “Global Configuration” and set the values by the level of resources available on your server.

Apache Directive       (From 2GB memory or less and up to 12GB memory)         
StartServers            4       8       16  
MinSpareServers         4       8       16  
MaxSpareServers         8       16      32  
ServerLimit             64      128         256     
MaxRequestWorkers       50      120         250     
MaxConnectionsPerChild      1000        2500        5000 
Keep-Alive          On      On      On
Keep-Alive Timeout      5       5        5
Max Keep-Alive Requests     50      120     120
Timeout             30      60      60

 

Now go to WHM » Service Configuration » Apache Configuration » Include Editor » “Pre VirtualHost Include” and allow users minimal cache and data compression to allow the server to work less for the same things by pasting the code below into the text field.

# Cache Control Settings for one hour cache
<FilesMatch ".(ico|pdf|flv|jpg|jpeg|png|gif|js|css|swf)$">
Header set Cache-Control "max-age=3600, public"
</FilesMatch>
<FilesMatch ".(xml|txt)$">
Header set Cache-Control "max-age=3600, public, must-revalidate"
</FilesMatch>
<FilesMatch ".(html|htm)$">
Header set Cache-Control "max-age=3600, must-revalidate"
</FilesMatch>
# Mod Deflate performs data compression
<IfModule mod_deflate.c>
<FilesMatch ".(js|css|html|php|xml|jpg|png|gif)$">
SetOutputFilter DEFLATE
BrowserMatch ^Mozilla/4 gzip-only-text/html
BrowserMatch ^Mozilla/4.0[678] no-gzip
BrowserMatch bMSIE no-gzip
</FilesMatch>
</IfModule>

 

Go to WHM » Service Configuration » “PHP Configuration Editor” and set the parameters according to your needs:

– memory_limit

– max_execution_time

– max_input_time

 

MySQL

For MySQL you need to update the configuration file that usually in /etc/my.cnf

Best config base on 1 core & 2GB memory MySQL 5.5:

[mysqld]
local-infile = 0
max_connections = 250
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
max_heap_table_size = 16M
table_cache = 5000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 15
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 16M
open_files_limit=2528
[mysqld_safe]
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout

 

Best config base on 8 core & 12GB memory (Shared server) MySQL 5.5:

[mysqld]
local-infile=0
max_connections = 600
max_user_connections=1000
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
read_buffer_size = 1M
table_open_cache = 5000
thread_cache_size = 384
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 128M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 5M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65535
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
max_write_lock_count = 8
slow_query_log
log-error
external-locking=FALSE
open_files_limit=50000
[mysqld_safe]
[mysqldump]
quick
max_allowed_packet = 16M
[isamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
#### Per connection configuration ####
sort_buffer_size = 1M
join_buffer_size = 1M
thread_stack = 192K

 

Repair & optimize databases then restart MySQL:

mysqlcheck --check --auto-repair --all-databases
mysqlcheck --optimize --all-databases
/etc/init.d/mysql restart

 

Security & Limit Resources

 

Install CSF (ConfigServer Security & Firewall) at: http://configserver.com/free/csf/install.txt

1) Go to WHM » Plugins » ConfigServer Security & Firewall » “Check Server Security” And pass on what appears as required to repair:

2) Go to WHM » Plugins » ConfigServer Security & Firewall » “Firewall Configuration” and set the parameters according to your needs:

PT_USERMEM=180

PT_USERTIME=180

PT_USERKILL=1

PT_USERKILL_ALERT=1 (Optional)

 

Now enjoy your new fast and more effective server.

  • Import and Export MySQL Databases

    Import and Export MySQL Databases How do I export and import a mySQL db using phpMyAdmin? Open phpMyAdmin and select […]

  • WordPress Tutorials

    What is WordPress? WordPress is an open source blog publishing application powered by PHP and MySQL which can also be […]

  • How-to-Add-HTTPS-Support-to Turn on HTTPS redirect for your blog

    If you want visitors to access your blog only over HTTPS, you can turn on HTTPS redirect. There are three main […]

  • Tips for Adsense Publisher

    Dear AdSense publisher, With the holiday season fast approaching, users are spending more time online and advertisers are more likely […]

Leave a Reply

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

*
*

*