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