Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL Optimisation

Discussion in 'Workarounds and Optimization' started by charliekapper, Oct 4, 2013.

  1. charliekapper

    charliekapper Member

    Aug 18, 2013
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Hi there,

    I would like some advice on optimising MySQL for a 1gb virtuozzo vps.

    The vps is currently hosted at hostdime which I believe they set a proc limit to around 180 (not sure if this plays a part in the problem).

    The problem I face is the server is running low on memory around 80-120mb causing the whm panel to show "fatal error occured" messages.

    I see mysql is using a lot of memory in the top command and in whm.

    # top
    1060 mysql     20   0 3425m 148m 7004 S  2.7 14.5 240:51.66 mysqld
    # free -m
                 total       used       free     shared    buffers     cached
    Mem:          1024        927         96          0          0        927
    -/+ buffers/cache:          0       1024
    Swap:            0          0          0
    # /usr/local/cpanel/3rdparty/mysqltuner/
    Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at /usr/local/cpanel/3rdparty/mysqltuner/ line 148.
     >>  MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
     >>  Bug reports, feature requests, and downloads at
     >>  Run with '--help' for additional options and output filtering
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 104M (Tables: 258)
    [--] Data in InnoDB tables: 67M (Tables: 69)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 22
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7d 0h 13m 53s (49M q [81.502 qps], 4M conn, TX: 32B, RX: 4B)
    [--] Reads / Writes: 89% / 11%
    [--] Total buffers: 168.0M global + 2.8M per thread (500 max threads)
    [!!] Maximum possible memory usage: 1.5G (150% of installed RAM)
    [OK] Slow queries: 0% (0/49M)
    [OK] Highest usage of available connections: 8% (42/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/143.4M
    [OK] Key buffer hit rate: 100.0% (39M cached / 10K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 81K sorts)
    [!!] Temporary tables created on disk: 28% (3M on disk / 11M total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 77% (400 open / 515 opened)
    [OK] Open file limit used: 23% (596/2K)
    [OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
    [OK] InnoDB data size / buffer pool: 67.4M/128.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (>= 8M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
    I have no idea if the server is using innodb or myisam, although I hear myisam can cut ram down a lot.

    I have no real idea of configuring mysql so that may be why the my.cnf file is short and unconfigured.

    I've been told the problem could possibly be due to php.

    I use this web server just for a game hosting panel called Multicraft and have various dedicated servers which connect to the mysql to access the daemon.

    Let me know if you need any further info.

    Thanks in advance.
  2. Aaron.Edwards

    Aaron.Edwards Active Member

    Sep 21, 2013
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Hi Charlie,

    How many hits does your server gets usually at the peak time of the server ? How many domains hosted in the server ?

    Determine your maximum connections value by reducing it.
  3. charliekapper

    charliekapper Member

    Aug 18, 2013
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Thanks for the reply.

    Around 40 hits afaik. How could I check this?

    6 domains are hosted on the server, 5 are sitting with no traffic and 1 (the main one) has the panel on with the databases.

    If I reduce the amount, how do I tell if the connections get rejected because it's too low?
    Also if I set the wrong settings (low values) will mysql not work and fail to write changes?
  4. thinkbot

    thinkbot Well-Known Member

    Oct 30, 2012
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    change my.cnf to


    wait_timeout = 60
    connect_timeout = 2

    query_cache_type = 1
    query_cache_size = 20M
    query_cache_limit = 1M

    thread_cache_size = 20
    table_open_cache = 512
    key_buffer_size = 150M

    innodb_buffer_pool_size = 100M

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice