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

    sort_buffer_size = 1M
    join_buffer_size = 1M
    thread_stack = 192K