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 Eating a lot of memory and CPU

Discussion in 'Workarounds and Optimization' started by Zeeshan Mudassir, Mar 31, 2016.

  1. Zeeshan Mudassir

    Zeeshan Mudassir Registered

    Aug 1, 2015
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator

    I am having issues with MYSQL and CPU+RAM usage

    my server is

    Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz
    4 cores/8 threads
    32 GB ram
    2x2TB raid0

    my mysql configurations are:

    max_connections = 300
    # MyISAM #
    key-buffer-size                = 32M
    myisam-recover                 = FORCE,BACKUP
    sort_buffer_size = 1M
    join_buffer_size = 2M
    query_cache_limit = 10M
    query_cache_size = 1024M
    query_cache_type = 1
    tmp-table-size                 = 512M
    max-heap-table-size            = 512M
    thread-cache-size              = 50
    open-files-limit               = 65535
    table-definition-cache         = 8192
    table-open-cache               = 10240
    innodb_buffer_pool_size = 9G
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 7M
    innodb_buffer_pool_instances = 9
    max_allowed_packet = 16M

    And reports are :

    root@s10 [~]# ./
    >>  MySQLTuner 1.6.7 - Major Hayden <>
    >>  Bug reports, feature requests, and downloads at
    >>  Modified by George Liu (eva2000) at
    >>  Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.29
    [OK] Operating on 64-bit architecture
    -------- System Linux Recommendations  ---------------------------------------
    Look for related Linux system recommandations
    [--] There is 43 listening port(s) on this server.
    [!!] There is too many listening ports: 43 > 10
    [!!] There is Apache like server running on 80 or 443 port.
    [OK] No Application server runing on 8080 or 8443 port.
    -------- Storage Engine Statistics -------------------------------------------
    [--] Data in MyISAM tables: 11M (Tables: 34)
    [--] Data in InnoDB tables: 8G (Tables: 7229)
    [--] Data in CSV tables: 0B (Tables: 4)
    [--] Data in MEMORY tables: 0B (Tables: 12)
    [!!] Total fragmented tables: 547
    -------- Security Recommendations  -------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] User 'munin@localhost' has user name as password.
    [!!] User 'dataisla_checkin@%' hasn't specific host restriction.
    [!!] User 'dataislamabad@%' hasn't specific host restriction.
    [!!] There is no basic password file list!
    -------- CVE Security Recommendations  ---------------------------------------
    [--] Skipped due to --cvefile option undefined
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 0h 21m 25s (66M q [380.327 qps], 1M conn, TX: 491G, RX: 9G)
    [--] Reads / Writes: 94% / 6%
    [--] Binary logging is disabled
    [--] Total buffers: 10.5G global + 2.6M per thread (300 max threads)
    [OK] Maximum reached memory usage: 10.7G (34.25% of installed RAM)
    [OK] Maximum possible memory usage: 11.3G (36.31% of installed RAM)
    [OK] Slow queries: 0% (43/66M)
    [OK] Highest usage of available connections: 16% (49/300)
    [OK] Aborted connections: 0.00%  (11/1560518)
    [!!] Query cache should be disabled by default due to mutex contention.
    [OK] Sorts requiring temporary tables: 0% (281 temp sorts / 3M sorts)
    [!!] Joins performed without indexes: 9648
    [!!] Temporary tables created on disk: 52% (1M on disk / 2M total)
    [OK] Thread cache hit rate: 99% (49 created / 1M connections)
    [OK] Table cache hit rate: 98% (8K open / 8K opened)
    [OK] Open file limit used: 0% (131/65K)
    [OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
    -------- ThreadPool Metrics --------------------------------------------------
    [--] ThreadPool stat is disabled.
    -------- Performance schema --------------------------------------------------
    [--] Performance schema is enabled.
    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 24.3% (8M used / 33M cache)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/3.5M
    [OK] Read Key buffer hit rate: 99.9% (2M cached / 1K reads)
    [OK] Write Key buffer hit rate: 97.8% (195K cached / 4K writes)
    -------- AriaDB Metrics ------------------------------------------------------
    [--] AriaDB is disabled.
    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 9.0G/8.4G
    [OK] InnoDB buffer pool instances: 9
    [!!] InnoDB Used buffer: 38.74% (228513 used/ 589815 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (9858875877 hits/ 9859028204 total)
    [!!] InnoDB Write Log efficiency: 82.70% (4580780 hits/ 5538873 total)
    [!!] InnoDB log waits: 0.00% (2 waits / 958093 writes)
    -------- TokuDB Metrics ------------------------------------------------------
    [--] TokuDB is disabled.
    -------- Galera Metrics ------------------------------------------------------
    [--] Galera is disabled.
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Consider dedicating a server for your database installation with less services running on !
        Consider dedicating a server for Web server in production !
        Run OPTIMIZE TABLE to defragment tables for better performance
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Restrict Host for user@% to user@SpecificDNSorIp
        Adjust your join queries to always utilize indexes. Please note this
        calculation is made by adding Select_full_join + Select_range_check
        status values and triggered when the total >250
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_type (=0)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        innodb_log_buffer_size (>= 6M)

    what should i do now to minimize server + RAM usage. because i am using FASTCGI with custom configurations. otherwise on SUPHP, the server load goes from 4% to 60% instantly.
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Apr 11, 2011
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Hello :)

    I suggest using a command such as "mysqladmin processlist" when CPU usage is high to see if any specific databases or tables are responsible for the increase in resource usage.

    Thank you.
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...

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