Ytukyg

MySQL showing very high CPU usage. like over 200% sometime.
I had slow query log on. and slow query time set to 1s.
there is not any slow query.
I am using code igniter PHP.

my my.cnf file :

[mysqld]

innodb_io_capacity=2000

innodb_read_io_threads=32

innodb_write_io_threads=32

innodb_log_buffer_size=250M

innodb_thread_concurrency=0

innodb_buffer_pool_size=1000M

user        = mysql

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

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

port        = 3306

basedir     = /usr

datadir     = /var/lib/mysql

tmpdir      = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

##

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

#bind-address       = 127.0.0.1

#

# * Fine Tuning

#

innodb_buffer_pool_size = 12G

key_buffer_size     = 1G

max_allowed_packet  = 256M

thread_stack        = 192K

thread_cache_size       = 100

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover-options  = BACKUP

max_connections        = 300





#innodb_buffer_pool_instances=8

#innodb_read_io_threads=8

#innodb_write_io_threads=8



#open_files_limit = 1024

#table_open_cache = 400

server_id           = 2

bind-address        = 0.0.0.0

log_bin             = /var/log/mysql/mysql-bin.log

log_bin_index       = /var/log/mysql/mysql-bin.log.index

relay_log           = /var/log/mysql/mysql-relay-bin

relay_log_index     = /var/log/mysql/mysql-relay-bin.index

expire_logs_days    = 10

max_binlog_size     = 100M

log_slave_updates   = 1

auto-increment-increment = 2

auto-increment-offset = 2

replicate-ignore-db=phpmyadmin

replicate-ignore-db=mysql

skip-name-resolve

#table_cache            = 64

#thread_concurrency     = 10

#

# * Query Cache Configuration

#

query_cache_limit=0

query_cache_size=0



sort_buffer_size = 2M

join_buffer_size = 128K

read_buffer_size = 128K

read_rnd_buffer_size=256K

what can I do to reduce CPU usage?
Is there any way to find which queryes are causing high cpu usage o

there is not any slow query

There aren't any slow queries, or there aren't any slow queries showing in the log? I think your first prt of call would be to check if the query logging is working as you expect. This can be done easily using discretionary locks without adding additional load on the system.

Assuming that there really are no queries taking over 1 second, then the load arises fro lots of queries taking less than 1 second. You need to capture them all to identify and prioritize your tuning efforts. But you might start by enabling the query cache (which you have disabled for some reason).