How reduce mysql cpu usage.

[sanoj_2020 2020]

Hello,

One of our server facing server load issue due to mysql cpu usage. Here by I am pasting my.cnf value. Please have look in to this and let me know how to resolve this issue.


=======================
[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock




[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0



[mysqld]


server_id = 69269121



user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

#datadir = /var/lib/mysql

datadir = /var/lib/mysql

log-error = mysql.err

tmpdir = /tmp

skip-external-locking

bind-address = 127.0.0.1

bind-address = 192.168.20.123

key_buffer_size = 32M

max_allowed_packet = 80M

thread_stack = 256K


thread_cache_size = 24

myisam-recover = BACKUP

max_connections = 128

table_open_cache = 512

net_write_timeout = 2000

net_read_timeout = 2000

wait_timeout = 7200

connect_timeout = 100

tmp_table_size = 64M

max_heap_table_size = 64M

net_buffer_length = 128K

read_rnd_buffer_size = 512K

query_cache_limit = 1M

query_cache_size = 32M

myisam_sort_buffer_size = 64M

read_buffer_size = 1M

sort_buffer_size = 8M

max_prepared_stmt_count = 12288

delay_key_write = OFF

default_storage_engine = InnoDB



#thread_concurrency = 10

#

# * Query Cache Configuration

#

#query_cache_limit = 1M

#query_cache_size = 16M

#

# * Logging and Replication

#

# Both location gets rotated by the cronjob.

# Be aware that this log type is a performance killer.

#log = /var/log/mysql/mysql.log

#

# Error logging goes to syslog. This is a Debian improvement :)

#

# Here you can see queries with especially long duration

slow_query_log = 0

slow_query_log_file = mysql-slow.log

long_query_time = 2

#log-queries-not-using-indexes

#

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.


log_bin = mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

#binlog_do_db = include_database_name

#binlog_ignore_db = include_database_name

#

# * InnoDB



innodb_buffer_pool_size = 50M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 5M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 50

innodb_file_per_table



#skip-innodb

#

# * Federated

#

skip-federated

#

# * Security Features

#

# Read the manual, too, if you want chroot!

# chroot = /var/lib/mysql/

#

# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".

#

# ssl-ca=/etc/mysql/cacert.pem

# ssl-cert=/etc/mysql/server-cert.pem

# ssl-key=/etc/mysql/server-key.pem







[mysqldump]

quick

quote-names

max_allowed_packet = 16M



[mysql]


#no-auto-rehash # faster start of mysql but no tab completition



[isamchk]

key_buffer = 16M


# * NDB Cluster

#

# See /usr/share/doc/mysql-server-*/README.Debian for more information.

#

# [MYSQL_CLUSTER]

# ndb-connectstring=127.0.0.1



# * IMPORTANT: Additional settings that can override those from this file!

# The files must end with '.cnf', otherwise they'll be ignored.

#

!includedir /etc/mysql/conf.d/




===================

Regards,
Sanoj.