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!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help - optimize mysqltunner to reduce high cpu usage

Discussion in 'Workarounds and Optimization' started by urkeee, Nov 14, 2016.

Tags:
  1. urkeee

    urkeee Registered

    Joined:
    Nov 11, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Serbia
    cPanel Access Level:
    Root Administrator
    Hello,

    can you please help me to optimize mysql to reduce high cpu usage and sometimes big load?

    Thanks

    my.cnf

    Code:
    
    [mysqld]
    slow-query-log=1
    long-query-time=1
    key_buffer_size =3G
    tmp_table_size = 256M
    max_heap_table_size = 256M
    query_cache_size = 128M
    table_open_cache = 96
    thread_cache_size = 4
    innodb_file_per_table=1
    max_allowed_packet=268435456
    innodb_buffer_pool_size=134217728
    open_files_limit=10000
    default-storage-engine=MyISAM
    
    mysqltuner

    Code:
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.52-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics ---------------------------------------------                                                                                                                                                  --------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                                                                                                                                                  ISAM +PERFORMANCE_SCHEMA
    [--] Data in MyISAM tables: 16G (Tables: 795)
    [--] Data in InnoDB tables: 3M (Tables: 28)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations ----------------------------------------------                                                                                                                                                  --------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [--] There are 612 basic passwords in the list.
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [!!] CVE-2016-6662(<= 5.5.52) : "Oracle MySQL through 5.5.52
    [--] False positive CVE(s) for MySQL and MariaDB 5.5.x can be found.
    [--] Check careful each CVE for those particular versions
    [!!] 1 CVE(s) found for your MySQL release.
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 3d 15h 15m 8s (109M q [348.735 qps], 6M conn, TX: 36736G, RX: 6G)
    [--] Reads / Writes: 70% / 30%
    [--] Binary logging is disabled
    [--] Physical Memory     : 31.3G
    [--] Max MySQL memory    : 3.9G
    [--] Other process memory: 2.6G
    [--] Total buffers: 3.5G global + 2.8M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 3.9G (12.53% of installed RAM)
    [OK] Maximum possible memory usage: 3.9G (12.52% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (29K/109M)
    [!!] Highest connection usage: 100%  (152/151)
    [OK] Aborted connections: 0.01%  (924/6690922)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [!!] Query cache may be disabled by default due to mutex contention.
    [OK] Sorts requiring temporary tables: 4% (88K temp sorts / 1M sorts)
    [OK] No joins without indexes
    [!!] Temporary tables created on disk: 94% (1M on disk / 1M total)
    [OK] Thread cache hit rate: 89% (693K created / 6M connections)
    [!!] Table cache hit rate: 0% (96 open / 228K opened)
    [OK] Open file limit used: 1% (168/10K)
    [OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 19.9% (641M used / 3B cache)
    [OK] Key buffer size / total MyISAM indexes: 3.0G/4.6G
    [OK] Read Key buffer hit rate: 98.3% (764M cached / 13M reads)
    [!!] Write Key buffer hit rate: 75.4% (4M cached / 1M writes)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 0
    [OK] InnoDB File per table is activated
    [OK] InnoDB buffer pool / data size: 128.0M/4.0M
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.90625 %): 5.0M/128.0M should be equal 25%
    [OK] InnoDB buffer pool instances: 1
    [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [OK] InnoDB Read buffer efficiency: 99.97% (2837190 hits/ 2838089 total)
    [OK] InnoDB Write log efficiency: 99.77% (99305 hits/ 99538 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 233 writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- XtraDB Metrics ----------------------------------------------------------------------------
    [--] XtraDB is disabled.
    
    -------- RocksDB Metrics ---------------------------------------------------------------------------
    [--] RocksDB is disabled.
    
    -------- Spider Metrics ----------------------------------------------------------------------------
    [--] Spider is disabled.
    
    -------- Connect Metrics ---------------------------------------------------------------------------
    [--] Connect is disabled.
    
    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        1 CVE(s) found for your MySQL release. Consider upgrading your version !
        Reduce or eliminate persistent connections to reduce connection usage
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        Beware that open_files_limit (10000) variable
        should be greater than table_open_cache (96)
    Variables to adjust:
        max_connections (> 151)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_type (=0)
        table_open_cache (> 96)
        innodb_log_file_size should be equals to 1/4 of buffer pool size (=32M) if possible.
    
    If you need more info just ask :)
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
  3. urkeee

    urkeee Registered

    Joined:
    Nov 11, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Serbia
    cPanel Access Level:
    Root Administrator
    Thanks, very useful!

    Can someone help me about MySql tuning?
     
  4. Eminds

    Eminds Well-Known Member

    Joined:
    Nov 10, 2016
    Messages:
    211
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Go through the mysql tuner report .. compare it with your my.cnf file and update the parameters or you may need to add new parameters to your my.cnf as per the report. After making the changes monitor the server for 24 hours to check the exact results.
     
  5. urkeee

    urkeee Registered

    Joined:
    Nov 11, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Serbia
    cPanel Access Level:
    Root Administrator
    Can you please post how my.cnf should look like? I dont know what to add or what to change
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    You may need to consult with a qualified system administrator if you need direct and specific tuning advice. You can find a list of system administration services at:

    System Administration Services | cPanel Forums

    Thank you.
     
Loading...

Share This Page