MySQL Forums
Forum List  »  MySQL Administrator

How reduce mysql cpu usage.
Posted by: sanoj_2020 2020
Date: May 20, 2011 05:32AM

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.

Options: ReplyQuote


Subject
Written By
Posted
How reduce mysql cpu usage.
May 20, 2011 05:32AM
September 19, 2011 10:40PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.