We have been facing some severe problem(high CPU utilization) in our production server for the past few days. We have managed little bit by tunning MySQL my.cnf file, but it still maintains at 60-70% of CPU utilization. It seems that the "Running process" in our server says that MySQL uses 87.8% of CPU usage.
I have pasted some of that list below,
Display : PID | User | Memory | CPU | Search | Run..
CPU load averages: 2.24 (1 mins) , 2.96 (5 mins) , 2.86 (15 mins)
CPU type: Intel(R) Xeon(R) CPU E5645 @ 2.40GHz , 2 cores
**ID Owner CPU ↑ Command**
3119 mysql 87.8 % /usr/sbin/mysqld
5150 tomcat6 7.8 % /usr/lib/jvm/java-6-openjdk/bin/java - Djava.util.logging.config.file=/var/lib/to ...
6742 root 10.0 % /usr/share/webmin/proc/index_cpu.cgi
6754 root 0.0 % ps --cols 2048 -eo user:80,ruser:80,group:80,rgroup:80,pid,ppid,pgid,pcpu,vsz,ni ...
6753 root 0.0 % sh -c ps --cols 2048 -eo user:80,ruser:80,group:80,rgroup:80,pid,ppid,pgid,pcpu, ...
4971 www-data 0.0 % /usr/sbin/apache2 -k start
4950 www-data 0.0 % /usr/sbin/apache2 -k start
4949 www-data 0.0 % /usr/sbin/apache2 -k start
4947 root 0.0 % /usr/sbin/apache2 -k start
920 root 0.0 % /sbin/getty -8 38400 tty1
763 proftpd 0.0 % proftpd: (accepting connections)
733 root 0.0 % /usr/bin/perl /usr/share/webmin/miniserv.pl /etc/webmin/miniserv.conf
Here is our MySQL "my.cnf" file
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp/mysql
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 512M
max_allowed_packet = 128M
thread_stack = 192K
thread_cache_size = 8
sort_buffer_size = 512M
myisam-recover = BACKUP
max_connections = 500
table_cache = 80
query_cache_limit = 512M
query_cache_size = 512M
query-cache-type = 2
log_error = /var/log/mysql/error.log
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
expire_logs_days = 10
max_binlog_size = 100M
read_buffer_size=64M
read_rnd_buffer_size=64M
myisam_sort_buffer_size=64M
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=700M
innodb_additional_mem_pool_size=20M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
Here is our Tomcat6 connection pooling settings in tomcat server.xml
<Resource name="jdbc/dbname" auth="Container" type="javax.sql.DataSource"
initialSize ="5" maxActive ="25" minIdle ="5" maxIdle ="10"
username="xxxxx" password="xxxx" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dbname" validationQuery="select 1"/>
FYI..
Currently, Our production app crossed 550 users and for each users a web service has been called frequently for every 30sec.
Our server configurations :
***Operating system*** - Ubuntu Linux 11.10
***Kernel and CPU*** - Linux 3.0.0-14-virtual on x86_64
***Processor information*** - Intel(R) Xeon(R) CPU E5645 @ 2.40GHz, 2 cores
***CPU usage*** - 14% user, 1% kernel, 0% IO, 85% idle
***Real memory*** - 7.29 GB total, 4.84 GB used
Any help is appreciated.