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 cpu usage optimize

Discussion in 'Workarounds and Optimization' started by seng fatt, Dec 11, 2016.

  1. seng fatt

    seng fatt Registered

    Joined:
    Dec 11, 2016
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    iraq
    cPanel Access Level:
    Root Administrator
    hi
    i have some serious issue with mysql
    this is my.cnf file
    Code:
    [mysql]
    #port                           = 3306
    #socket                         = /var/lib/mysql/mysql.sock
    [mysqld]
    #user                           = mysql
    innodb_lock_wait_timeout=2000
    #local-infile=0
    default-storage-engine=InnoDB
    #socket                         = /var/lib/mysql/mysql.sock
    key-buffer-size=32M
    myisam-recover="FORCE,BACKUP"
    thread_concurrency=16
    max-allowed-packet=16M
    #max-connect-errors=1000000
    max-connect-errors=100
    datadir="/var/lib/mysql/"
    expire-logs-days=14
    sync-binlog=1
    tmp-table-size=50M
    max-heap-table-size=50M
    query-cache-type=0
    query-cache-size=128M
    max-connections=2000
    thread-cache-size=150
    open-files-limit=65535
    table-definition-cache=4096
    table-open-cache=1000
    innodb-flush-method=O_DIRECT
    innodb-log-files-in-group=2
    innodb-flush-log-at-trx-commit=1
    innodb_buffer_pool_instances=1
    innodb-file-per-table=1
    log-queries-not-using-indexes=1
    innodb_file_per_table=1
    max_allowed_packet=1073741824
    open_files_limit=50000
    #innodb_buffer_pool_size=3000M
    key_buffer_size         = 350M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    max_connections = 50     #default is 150
    wait_timeout = 30        #default is 28800
    innodb_buffer_pool_size = 2000M
    innodb_log_file_size = 8M
    innodb_thread_concurrency = 8
    innodb_file_per_table
    query_cache_size        = 16M
    log-queries-not-using-indexes
    
    this mysqltunner.pl results:
    Code:
     >>  MySQLTuner 1.7.0 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [!!] Currently running unsupported MySQL version 10.0.28-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Log file Recommendations ----------------------------------------------                                                                                        --------------------
    [--] Log file: /var/lib/mysql/server.example.com.err(63K)
    [OK] Log file /var/lib/mysql/server.example.com.err exists
    [OK] Log file /var/lib/mysql/server.example.com.err is readable.
    [OK] Log file /var/lib/mysql/server.example.com.err is not empty
    [OK] Log file /var/lib/mysql/server.example.com.err is smaller than 32 Mb
    [!!] /var/lib/mysql/server.example.com.err contains 72 warning(s).
    [!!] /var/lib/mysql/server.example.com.err contains 31 error(s).
    [--] 22 start(s) detected in /var/lib/mysql/server.example.com.err
    [--] 1) 161211 23:48:20 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 2) 161211 23:45:24 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 3) 161211 23:44:46 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 4) 161211 23:34:56 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 5) 161211 23:25:28 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 6) 161211 23:24:16 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 7) 161211 23:07:13 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 8) 161211 22:57:43 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 9) 161211 22:56:10 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 10) 161211 22:55:47 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 25 shutdown(s) detected in /var/lib/mysql/server.example.com.err
    [--] 1) 161211 23:48:19 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 2) 161211 23:45:23 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 3) 161211 23:44:44 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 4) 161211 23:34:55 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 5) 161211 23:25:27 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 6) 161211 23:06:35 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 7) 161211 23:06:34 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 8) 161211 22:57:23 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 9) 161211 22:57:02 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 10) 161211 22:56:58 [Note] /usr/sbin/mysqld: Shutdown complete
    
    -------- Storage Engine Statistics ---------------------------------------------                                                                                        --------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyIS                                                                                        AM +MyISAM +PERFORMANCE_SCHEMA
    [--] Data in MyISAM tables: 879M (Tables: 19)
    [--] Data in InnoDB tables: 186M (Tables: 68)
    [OK] Total fragmented tables: 0
    
    -------- 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.
    [--] There are 612 basic passwords in the list.
    
    -------- CVE Security Recommendations ------------------------------------------                                                                                        --------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    
    -------- Performance Metrics ---------------------------------------------------                                                                                        --------------------
    [--] Up for: 29m 11s (214K q [122.612 qps], 3K conn, TX: 140G, RX: 17M)
    [--] Reads / Writes: 97% / 3%
    [--] Binary logging is disabled
    [--] Physical Memory     : 9.6G
    [--] Max MySQL memory    : 2.6G
    [--] Other process memory: 1.4G
    [--] Total buffers: 2.5G global + 2.7M per thread (50 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 2.5G (26.26% of installed RAM)
    [OK] Maximum possible memory usage: 2.6G (27.35% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [!!] Slow queries: 11% (24K/214K)
    [OK] Highest usage of available connections: 20% (10/50)
    [OK] Aborted connections: 0.06%  (2/3470)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 16K sorts)
    [OK] No joins without indexes
    [!!] Temporary tables created on disk: 60% (992 on disk / 1K total)
    [OK] Thread cache hit rate: 99% (10 created / 3K connections)
    [OK] Table cache hit rate: 152% (184 open / 121 opened)
    [OK] Open file limit used: 0% (105/50K)
    [OK] Table locks acquired immediately: 99% (212K immediate / 213K locks)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 4 thread(s).
    [--] Using default value is good enough for your version (10.0.28-MariaDB)
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 31.5% (115M used / 367M cache)
    [OK] Key buffer size / total MyISAM indexes: 350.0M/224.2M
    [OK] Read Key buffer hit rate: 99.1% (5M cached / 44K reads)
    [!!] Write Key buffer hit rate: 7.7% (22K cached / 20K writes)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 8
    [OK] InnoDB File per table is activated
    [OK] InnoDB buffer pool / data size: 2.0G/186.9M
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.8 %): 8.0M * 2/2.0G 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% (24245691 hits/ 24253713 total)
    [!!] InnoDB Write Log efficiency: 52.48% (3107 hits/ 5920 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 2813 writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 99.8% (425K cached / 913 reads)
    
    -------- 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:
        Control warning line(s) into /var/lib/mysql/server.example.com.err file
        Control error line(s) into /var/lib/mysql/server.example.com.err file
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        MySQL started within last 24 hours - recommendations may be inaccurate
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Variables to adjust:
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
        innodb_log_file_size should be equals to 1/4 of buffer pool size (=500M) if possible.
    
    and this is my process list
    Code:
    root@server [~]# mysqladmin processlist
    +------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+
    | Id   | User            | Host      | db              | Command | Time | State | Info             | Progress |
    +------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+
    | 4332 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 0    |       |                  | 0.000    |
    | 4333 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 2    |       |                  | 0.000    |
    | 4334 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 1    |       |                  | 0.000    |
    | 4335 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 1    |       |                  | 0.000    |
    | 4336 | root            | localhost |                 | Query   | 0    | init  | show processlist | 0.000    |
    +------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+
    
    i dont have any heavy query according to my programer but mysql using cpu for nothing!
    please help me
    thank you

    any suggestion?
     
    #1 seng fatt, Dec 11, 2016
    Last edited by a moderator: Dec 12, 2016
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    15,618
    Likes Received:
    296
    Trophy Points:
    433
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    You can't get proper results from a system that's not been up at least 24 hours:
    Once you do that, the section titled, General recommendations: in the output, might be a nice place to start. ;)
     
  3. seng fatt

    seng fatt Registered

    Joined:
    Dec 11, 2016
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    iraq
    cPanel Access Level:
    Root Administrator
    this is new log
    Code:
    root@server [~]# perl mysqltuner.pl
    >>  MySQLTuner 1.7.0 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [!!] Currently running unsupported MySQL version 10.0.28-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Log file Recommendations ----------------------------------------------                                                                                        --------------------
    [--] Log file: /var/lib/mysql/server.example.com.err(63K)
    [OK] Log file /var/lib/mysql/server.example.com.err exists
    [OK] Log file /var/lib/mysql/server.example.com.err is readable.
    [OK] Log file /var/lib/mysql/server.example.com.err is not empty
    [OK] Log file /var/lib/mysql/server.example.com.err is smaller than 32 Mb
    [!!] /var/lib/mysql/server.example.com.err contains 72 warning(s).
    [!!] /var/lib/mysql/server.example.com.err contains 31 error(s).
    [--] 22 start(s) detected in /var/lib/mysql/server.eaglescity.com.err
    [--] 1) 161211 23:48:20 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 2) 161211 23:45:24 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 3) 161211 23:44:46 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 4) 161211 23:34:56 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 5) 161211 23:25:28 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 6) 161211 23:24:16 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 7) 161211 23:07:13 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 8) 161211 22:57:43 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 9) 161211 22:56:10 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 10) 161211 22:55:47 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 25 shutdown(s) detected in /var/lib/mysql/server.example.com.err
    [--] 1) 161211 23:48:19 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 2) 161211 23:45:23 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 3) 161211 23:44:44 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 4) 161211 23:34:55 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 5) 161211 23:25:27 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 6) 161211 23:06:35 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 7) 161211 23:06:34 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 8) 161211 22:57:23 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 9) 161211 22:57:02 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 10) 161211 22:56:58 [Note] /usr/sbin/mysqld: Shutdown complete
    
    -------- Storage Engine Statistics ---------------------------------------------                                                                                        --------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyIS                                                                                        AM +MyISAM +PERFORMANCE_SCHEMA
    [--] Data in MyISAM tables: 901M (Tables: 19)
    [--] Data in InnoDB tables: 202M (Tables: 68)
    [OK] Total fragmented tables: 0
    
    -------- 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.
    [--] There are 612 basic passwords in the list.
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 1d 0h 23m 17s (9M q [106.656 qps], 128K conn, TX: 7651G, RX: 673M)
    [--] Reads / Writes: 97% / 3%
    [--] Binary logging is disabled
    [--] Physical Memory     : 9.6G
    [--] Max MySQL memory    : 2.6G
    [--] Other process memory: 1.7G
    [--] Total buffers: 2.5G global + 2.7M per thread (50 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 2.5G (26.31% of installed RAM)
    [OK] Maximum possible memory usage: 2.6G (27.35% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [!!] Slow queries: 12% (1M/9M)
    [OK] Highest usage of available connections: 24% (12/50)
    [OK] Aborted connections: 0.01%  (9/128490)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
    [OK] Sorts requiring temporary tables: 0% (16 temp sorts / 677K sorts)
    [OK] No joins without indexes
    [!!] Temporary tables created on disk: 63% (16K on disk / 26K total)
    [OK] Thread cache hit rate: 99% (78 created / 128K connections)
    [OK] Table cache hit rate: 135% (203 open / 150 opened)
    [OK] Open file limit used: 0% (114/50K)
    [OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 4 thread(s).
    [--] Using default value is good enough for your version (10.0.28-MariaDB)
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 58.3% (213M used / 367M cache)
    [OK] Key buffer size / total MyISAM indexes: 350.0M/236.1M
    [OK] Read Key buffer hit rate: 99.9% (195M cached / 128K reads)
    [!!] Write Key buffer hit rate: 12.2% (732K cached / 643K writes)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 8
    [OK] InnoDB File per table is activated
    [OK] InnoDB buffer pool / data size: 2.0G/202.5M
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.8 %): 8.0M * 2/2.0G 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: 100.00% (797716161 hits/ 797726689 total)
    [!!] InnoDB Write Log efficiency: 53.07% (120023 hits/ 226147 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 106124 writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 99.8% (5M cached / 13K reads)
    
    -------- 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:
        Control warning line(s) into /var/lib/mysql/server.eaglescity.com.err file
        Control error line(s) into /var/lib/mysql/server.eaglescity.com.err file
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Variables to adjust:
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
        innodb_log_file_size should be equals to 1/4 of buffer pool size (=500M) if possible.
    
     
    #3 seng fatt, Dec 12, 2016
    Last edited by a moderator: Dec 12, 2016
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,022
    Likes Received:
    1,276
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    You can review the "Variables to adjust" section and make adjustments in your /etc/my.cnf to meet those suggestions. Also, you may want to use a command such as "mysqladmin processlist;" when the server resource usage is high to see if you can determine which databases are the culprit.

    Thank you.
     
Loading...

Share This Page