Mysql uses High cpu rate

Hello Mysql Experts, Greetings !!! In my production system use Server 4CPU 3G, RAM 32G and Mysql version:-5.5.38 I'm experiencing some problems with my MYSQL server . My server is showing more than 100% usage of mysql even if I don't have too much traffic on web. I noticed that mysql makes high usage of cpu and VIRT memory Here is my.cnf:- I am seeting in my.cnf file like this.


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

[mysqld]
max_allowed_packet     = 512M
max_connections        = 5000
table_cache            = 12000
max_connect_errors     = 1000000
skip-name-resolve
event_scheduler        =on
key_buffer_size    =32M

innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=5
innodb_flush_method=O_DSYNC
tmp_table_size=16777216
join_buffer_size=33554432
sort_buffer_size=33554432
innodb_lock_wait_timeout=900



innodb_log_buffer_size=256M
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
max_heap_table_size=268435456
table_open_cache=12000


innodb_io_capacity=100000
innodb_adaptive_hash_index=on
wait_timeout=900




[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = secret

[mysqld1]
port       = 3306
datadir    = /mydata/mysql_3306
pid-file   = /var/run/mysqld/mysqld.pid
socket     = /var/run/mysqld/mysqld.sock
user       = mysql
log-error  = /var/log/mysql1.err

[mysqld2]
port       = 3307
datadir    = /mydata/mysql_3307
pid-file   = /var/lib/mysqld/mysqld1.pid
socket     = /var/lib/mysqld/mysqld1.sock
user       = mysql
log-error  = /var/log/mysql2.err


[mysqld3]
port       = 3308
datadir    = /mydata/mysql_3317
pid-file   = /var/lib/mysqld/mysqld2.pid
socket     = /var/lib/mysqld/mysqld2.sock
user       = mysql
log-error  = /var/log/mysql3.err

[mysqld5]
port     = 3310
datadir   = /mydata/mysql_3310
pid-file  = /var/lib/mysqld/mysqld5.pid
socket    = /var/lib/mysqld/mysqld5.sock
user      = mysql
log-error = /var/log/mysql5.err

=======================================
SHOW ENGINE INNODB STATUS\G;

look at the end of the output as follows:
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8791244800; in additional pool allocated 0
Dictionary memory allocated 2329722
Buffer pool size   524280
Free buffers        509686
Database pages     14512
old database pages:-5397
Pending reads 0
Buffer pool hit rate 1000 / 1000
----------------

Individual Buffer pool INFO:-
-----------------------------

Buffer pool size 104856
Free buffers     101910
Database pages     2929
old database pages 1086


Here is TOP -u mysql:-
-----------------------

PID     USER    PR    NI   VIRRT     RES    SHR  %cpu  %MEM       COMMAND
3418    mysql    20    0    13.3g    1.6g     s      748     33.2%      mysqld
3434    mysql    20    0    10.6g    939m   s      2         5.1          mysqld
3362    mysql    20    0    10.1g    926m   s     0          2.0          mysqld
3515    mysql    20    0    9851m    905m s     0          2.8          mysqld

PlZ help me...

Thanks in advance.

Mysql uses High cpu rate

Comments

Didn't find what you were looking for?