1

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.

mavroprovato
  • 8,023
  • 5
  • 37
  • 52
Shakthi
  • 175
  • 2
  • 13
  • Unless you really know what you're doing, the `sort_buffer_size`, `query_cache_limit`, `read_buffer_size`, `read_rnd_buffer_size`, and `myisam_sort_buffer_size` values are set much higher than they usually need to be. – G-Nugget Feb 22 '13 at 16:09

0 Answers0