CentrioHost Blog

Stories and News from IT Industry, Reviews & Tips | Technology Blog


Optimize MySQL & Apache on cPanel/WHM Server

  • Category : cPanel Tutorials
  • Posted on : Jan 27, 2021
  • Views : 925
  • By : CentrioHost

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.6+

* In step 5 “Exhaustive Options List” select

– Deflate

– Expires

– 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 to 12GB+ memory) 	 	

StartServers 	 	 	4 	 	8 	 	16 	
MinSpareServers 	 	4 	 	8 	 	16 	
MaxSpareServers 	 	8 	 	16 	 	32 	
ServerLimit 	 	 	128 	 	256 	 	512 	
MaxRequestWorkers 	 	150 	 	250 	 	500 	
MaxConnectionsPerChild 	 	1000 	 	2500 	 	5000 
Keep-Alive			On		On		On
Keep-Alive Timeout	 	1	 	1	 	 1
Max Keep-Alive Requests		30	 	30	 	30
Timeout				60		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 2 core & 4GB memory MySQL 5.6 MariaDB 10:

[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=25280

[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 & 16GB+ memory (Shared server) MySQL 5.6 MariaDB 10:

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

Subscribe Now

10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!

Archive Calendar

SatSunMonTueWedThuFri
 1234
567891011
12131415161718
19202122232425
2627282930 

Over 20000 Satisfied Customers!

  • web hosting reviewer
    Valerie Quinn
    CTO, Acteon Group

    Centriohost staff were fantastic, I had a concern with a domain and they got back to me very quickly and they helped me to resolve the issue! ~ . . . Read more

  • Joomla hosting reviewer
    Collin Bryan
    Photographer, Allister Freeman

    I'm using centrio for my portfolio since 2006. The transition was seamless, the support was immediate, and everything works perfectly. ~ . . . Read more

  • dedicated server reviewer
    Harry Collett
    Actor, A&J Artists

    Very easy to understand & use even though I am not very technologically minded. No complications whatsoever & I wouldn't hesitate to recommend it to all. ~ . . . Read more

  • vps web hosting reviewer
    Porfirio Santos
    Technician, Diageo PLC

    Centrio support team have been amazingly responsive and helpful to any of my queries, thank you so much to the Centriohost have been amazingly responsive and helpful to any of my queries 👍👍👍 ~ . . . Read more

  • wordpress hosting plans reviewer
    Catherine Auer
    Doctor, SmartClinics

    Anytime I've had a problem I can't solve, I've found Centriohost to be diligent and persistent. They simply won't let an issue go until the client is happy. ~ . . . Read more

  • reseller hosting reviewer
    Effectivo Social
    Freelancer, Fiverr

    Recommend their shared hosting for all my SME web design clients. Their cloud or VME offerings are too great to deal with. Pricing is perfect and suitable for all users (͠≖ ͜ʖ͠≖) 👌 ~ . . . Read more

Top