Buy Percona ServicesBuy Now!

MySQL Utilizes high CPU all the time

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Utilizes high CPU all the time

    Hi Team,
    I am running a problem with one of my VM dedicated for mysqld service. All the time the CPU utilization on the system is high and even consuming the swap memory too.I have described the specifications below(let me know if you need any more details). Could anyone of you please analyze and let me know what i am missing here/any changes recommended in configuration.

    RAM 251G
    Swap 8G
    20 cores
    Red Hat Enterprise Linux Server release 7.2
    5.7.17-enterprise-commercial-advanced-log

    my.cnf
    =====
    [mysqld]
    port=9000
    pid-file=/var/run/mysqld/mysqld.pid
    socket=/var/lib/mysql/mysql.sock

    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    server-id=1
    datadir=/datasas/mysql_new/datadir
    tmpdir=/datasas/mysql_new/mysqltemp
    binlog_format = statement
    event_scheduler=on

    # LOGS DATA
    log_output = FILE
    log_warnings=0
    log_bin = ON
    log-bin=/datassd/mysql/bin_log/DPDP_DB_BINlog
    expire_logs_days = 1
    binlog_format = STATEMENT
    local_infile = 0
    binlog_error_action='IGNORE_ERROR'
    # ERROR LOGS
    log-error=/datasas/mysql_new/error_log/mysql_error.log

    #SLOW QUERY LOGS
    log_slow_admin_statements=1
    log_queries_not_using_indexes=0
    log_throttle_queries_not_using_indexes=3
    slow_query_log = 1
    slow_query_log_file = /datasas/mysql_new/slow_query_log/slow_querry.log
    long_query_time = 5
    slow_launch_time=2

    # MyISAM #
    key-buffer-size = 5G

    # SAFETY #
    max-allowed-packet = 16M
    max-connect-errors = 1000000
    sql-mode = STRICT_ALL_TABLES

    # CACHES AND LIMITS #
    tmp-table-size = 32M
    max-heap-table-size = 32M
    query-cache-type = 0
    query-cache-size = 0
    max-connections = 1800
    thread-cache-size = 256
    open-files-limit = 65535
    table-definition-cache = 10000
    table-open-cache = 10000
    join_buffer_size = 4M
    sort_buffer_size = 512K
    read_buffer_size = 512K
    # INNODB #
    innodb-flush-method = O_DIRECT
    innodb-log-files-in-group = 2
    innodb-log-file-size = 512M
    innodb-flush-log-at-trx-commit = 2
    innodb-file-per-table = 1
    innodb-buffer-pool-size = 160G
    innodb_buffer_pool_chunk_size = 4G
    innodb_buffer_pool_instances = 40
    innodb_thread_concurrency = 30
    ====================================

    traffic - not more than 1000 connections at peak time.
    Do not find any huge queries running on the host.
    Any suggestions are appreciated most.

    Thanks
    Farhana Mohammed

Working...
X