The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

    Joined:
    Aug 1, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    pakistan
    cPanel Access Level:
    Root Administrator
    Hello

    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:


    Code:
    [mysqld]
    local-infile=0
    default-storage-engine=InnoDB
    max_connections = 300
    
    # MyISAM #
    
    key-buffer-size                = 32M
    myisam-recover                 = FORCE,BACKUP
    sort_buffer_size = 1M
    join_buffer_size = 2M
    
    # CACHES AND LIMITS #
    
    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 CONFIGURATION #
    
    innodb_buffer_pool_size = 9G
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 7M
    innodb_thread_concurrency=8
    innodb_flush_method=O_DIRECT
    innodb_buffer_pool_instances = 9
    innodb_file_per_table=1
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    

    And Mysqtuner.pl reports are :

    Code:
    root@s10 [~]# ./mysqltuner.pl
    >>  MySQLTuner 1.6.7 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  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 -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
    [--] 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 Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    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.
     
Loading...

Share This Page