The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySql using CPU 100%

Discussion in 'Workarounds and Optimization' started by palpalani, Sep 4, 2012.

  1. palpalani

    palpalani Member

    Joined:
    Sep 4, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Chennai, Tamil Nadu, India
    cPanel Access Level:
    Website Owner
    I'm a newbie to dedicated server.

    My dedicated server have 8GB RAM, Duel Processor and i have running 1 wordpress website 2 sub domains (ZenPhoto). Mysql database size is around 120MB.

    while checking cPanel, it shows always morethan 100% CPU used by MySql and Memory is only around 8%.

    Here is my CPU usage report, available on WHM.

    Code:
    Pid	Owner	Priority	CPU % Memory %	Command
    11518 (Trace) (Kill)	mysql	0	  109	  8.5	/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.southdreamz.com.err --open-files-limit=8192 --pid-file=/var/lib/mysql/server.southdreamz.com.pid
    4392 (Trace) (Kill)	nobody	18	  4.5	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    1800 (Trace) (Kill)	varnish	0	  3.0	  3.0	/usr/local/varnish/sbin/varnishd -P /usr/local/varnish/var/run/varnish.pid -P /usr/local/varnish/var/run/varnish.pid -a :8082 -T localhost:6082 -f /usr/local/varnish/etc/varnish/default.vcl -p thread_pools 3 -p thread_pool_min 80 -p thread_pool_max 3000 -p thread_pool_add_delay 2 -p sess_workspace 65536 -u varnish -g varnish -s file,/usr/local/varnish/var/varnish/varnish_storage.bin,2GB
    4387 (Trace) (Kill)	root	0	  2.8	  0.5	whostmgr2 - top ./top
    4376 (Trace) (Kill)	nobody	18	  2.6	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    4265 (Trace) (Kill)	nobody	18	  2.5	  0.6	/usr/local/apache/bin/httpd -k start -DSSL
    4369 (Trace) (Kill)	nobody	18	  2.5	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    4390 (Trace) (Kill)	nobody	18	  2.5	0.2	/usr/local/apache/bin/httpd -k start -DSSL
    4370 (Trace) (Kill)	nobody	18	  2.4	  0.6	/usr/local/apache/bin/httpd -k start -DSSL
    4380 (Trace) (Kill)	nobody	18	  2.4	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    4363 (Trace) (Kill)	nobody	18	  2.3	  0.6	/usr/local/apache/bin/httpd -k start -DSSL
    4379 (Trace) (Kill)	nobody	18	  2.3	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    4359 (Trace) (Kill)	nobody	18	  2.2	  0.6	/usr/local/apache/bin/httpd -k start -DSSL
    4358 (Trace) (Kill)	nobody	18	  1.9	  0.5	/usr/local/apache/bin/httpd -k start -DSSL
    4357 (Trace) (Kill)	nobody	18	  1.9	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    4391 (Trace) (Kill)	nobody	18	  1.8	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    4362 (Trace) (Kill)	nobody	18	  1.7	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    4373 (Trace) (Kill)	nobody	18	  1.6	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    1771 (Trace) (Kill)	nobody	0	  1.2	0.0	nginx: worker process
    4374 (Trace) (Kill)	nobody	18	  1.2	0.3	/usr/local/apache/bin/httpd -k start -DSSL
    1770 (Trace) (Kill)	nobody	0	  1.2	0.0	nginx: worker process
    345 (Trace) (Kill)	root	-5	  1.0	0.0	[md2_raid1]
    4388 (Trace) (Kill)	nobody	18	  0.8	0.2	/usr/local/apache/bin/httpd -k start -DSSL
    348 (Trace) (Kill)	root	-5	  0.8	0.0	[md1_raid1]
    4389 (Trace) (Kill)	nobody	18	  0.6	0.2	/usr/local/apache/bin/httpd -k start -DSSL
    4393 (Trace) (Kill)	nobody	18	0.3	0.2	/usr/local/apache/bin/httpd -k start -DSSL
    352 (Trace) (Kill)	root	-5	0.2	0.0	[kjournald]
    4385 (Trace) (Kill)	root	0	0.2	0.1	whostmgrd - serving 122.174. --llu=1346331614 --listen=3,4,5,6,7,8
    128 (Trace) (Kill)	root	0	0.1	0.0	[pdflush]
    911 (Trace) (Kill)	root	-5	0.1	0.0	[kjournald]
    1882 (Trace) (Kill)	root	0	0.0	0.0	mdadm --monitor --scan -f --pid-file=/var/run/mdadm/mdadm.pid
    1866 (Trace) (Kill)	root	0	0.0	0.0	irqbalance
    1857 (Trace) (Kill)	root	0	0.0	0.0	klogd -x
    1854 (Trace) (Kill)	root	0	0.0	0.0	syslogd -m 0
    1798 (Trace) (Kill)	root	0	0.0	0.0	/usr/local/varnish/sbin/varnishd -P /usr/local/varnish/var/run/varnish.pid -P /usr/local/varnish/var/run/varnish.pid -a :8082 -T localhost:6082 -f /usr/local/varnish/etc/varnish/default.vcl -p thread_pools 3 -p thread_pool_min 80 -p thread_pool_max 3000 -p thread_pool_add_delay 2 -p sess_workspace 65536 -u varnish -g varnish -s file,/usr/local/varnish/var/varnish/varnish_storage.bin,2GB
    1769 (Trace) (Kill)	root	0	0.0	0.0	nginx: master process /usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf
    1227 (Trace) (Kill)	root	-10	0.0	0.0	iscsid
    1226 (Trace) (Kill)	root	0	0.0	0.0	iscsid
    1200 (Trace) (Kill)	root	-5	0.0	0.0	[rdma_cm]
    1194 (Trace) (Kill)	root	-5	0.0	0.0	[ib_cm/3]
    1193 (Trace) (Kill)	root	-5	0.0	0.0	[ib_cm/2]
    1192 (Trace) (Kill)	root	-5	0.0	0.0	[ib_cm/1]
    1191 (Trace) (Kill)	root	-5	0.0	0.0	[ib_cm/0]
    1185 (Trace) (Kill)	root	-5	0.0	0.0	[iw_cm_wq]
    1179 (Trace) (Kill)	root	-5	0.0	0.0	[local_sa]
    1178 (Trace) (Kill)	root	-5	0.0	0.0	[ib_inform]
    1177 (Trace) (Kill)	root	-5	0.0	0.0	[ib_mcast]
    1159 (Trace) (Kill)	root	-5	0.0	0.0	[ib_addr]
    1145 (Trace) (Kill)	root	-20	0.0	0.0	[bnx2i_thread/3]
    1144 (Trace) (Kill)	root	-20	0.0	0.0	[bnx2i_thread/2]
    1139 (Trace) (Kill)	root	-20	0.0	0.0	[bnx2i_thread/1]
    1138 (Trace) (Kill)	root	-20	0.0	0.0	[bnx2i_thread/0]
    1127 (Trace) (Kill)	root	-5	0.0	0.0	[cnic_wq]
    1065 (Trace) (Kill)	root	-5	0.0	0.0	[iscsi_eh]
    913 (Trace) (Kill)	root	-5	0.0	0.0	[kjournald]
    886 (Trace) (Kill)	root	-5	0.0	0.0	[kmpath_handlerd]
    885 (Trace) (Kill)	root	-5	0.0	0.0	[kmpathd/3]
    884 (Trace) (Kill)	root	-5	0.0	0.0	[kmpathd/2]
    883 (Trace) (Kill)	root	-5	0.0	0.0	[kmpathd/1]
    882 (Trace) (Kill)	root	-5	0.0	0.0	[kmpathd/0]
    402 (Trace) (Kill)	root	-4	0.0	0.0	/sbin/udevd -d
    374 (Trace) (Kill)	root	-5	0.0	0.0	[kauditd]
    351 (Trace) (Kill)	root	-5	0.0	0.0	[md0_raid1]
    322 (Trace) (Kill)	root	-5	0.0	0.0	[kstriped]
    270 (Trace) (Kill)	root	-5	0.0	0.0	[kpsmoused]
    257 (Trace) (Kill)	root	-5	0.0	0.0	[xenfb thread]
    134 (Trace) (Kill)	root	-5	0.0	0.0	[aio/3]
    133 (Trace) (Kill)	root	-5	0.0	0.0	[aio/2]
    132 (Trace) (Kill)	root	-5	0.0	0.0	[aio/1]
    131 (Trace) (Kill)	root	-5	0.0	0.0	[aio/0]
    1891 (Trace) (Kill)	dbus	0	0.0	0.0	dbus-daemon --system
    129 (Trace) (Kill)	root	0	0.0	0.0	[pdflush]
    127 (Trace) (Kill)	root	0	0.0	0.0	[khungtaskd]
    48 (Trace) (Kill)	root	-5	0.0	0.0	[kseriod]
    46 (Trace) (Kill)	root	-5	0.0	0.0	[khubd]
    42 (Trace) (Kill)	root	-5	0.0	0.0	[cqueue/3]
    41 (Trace) (Kill)	root	-5	0.0	0.0	[cqueue/2]
    40 (Trace) (Kill)	root	-5	0.0	0.0	[cqueue/1]
    39 (Trace) (Kill)	root	-5	0.0	0.0	[cqueue/0]
    38 (Trace) (Kill)	root	-5	0.0	0.0	[kblockd/3]
    37 (Trace) (Kill)	root	-5	0.0	0.0	[kblockd/2]
    36 (Trace) (Kill)	root	-5	0.0	0.0	[kblockd/1]
    35 (Trace) (Kill)	root	-5	0.0	0.0	[kblockd/0]
    30 (Trace) (Kill)	root	-5	0.0	0.0	[events/3]
    29 (Trace) (Kill)	root	-	0.0	0.0	[watchdog/3]
    28 (Trace) (Kill)	root	19	0.0	0.0	[ksoftirqd/3]
    27 (Trace) (Kill)	root	-	0.0	0.0	[migration/3]
    26 (Trace) (Kill)	root	-5	0.0	0.0	[events/2]
    25 (Trace) (Kill)	root	-	0.0	0.0	[watchdog/2]
    24 (Trace) (Kill)	root	19	0.0	0.0	[ksoftirqd/2]
    23 (Trace) (Kill)	root	-	0.0	0.0	[migration/2]
    22 (Trace) (Kill)	root	-5	0.0	0.0	[events/1]
    21 (Trace) (Kill)	root	-	0.0	0.0	[watchdog/1]
    20 (Trace) (Kill)	root	19	0.0	0.0	[ksoftirqd/1]
    19 (Trace) (Kill)	root	-	0.0	0.0	[migration/1]
    10 (Trace) (Kill)	root	-5	0.0	0.0	[xenbus]
    9 (Trace) (Kill)	root	-5	0.0	0.0	[xenwatch]
    7 (Trace) (Kill)	root	-5	0.0	0.0	[kthread]
    6 (Trace) (Kill)	root	-5	0.0	0.0	[khelper]
    5 (Trace) (Kill)	root	-5	0.0	0.0	[events/0]
    4 (Trace) (Kill)	root	-	0.0	0.0	[watchdog/0]
    3 (Trace) (Kill)	root	19	0.0	0.0	[ksoftirqd/0]
    2 (Trace) (Kill)	root	-	0.0	0.0	[migration/0]
    1 (Trace) (Kill)	root	0	0.0	0.0	init [3]
    4870 (Trace) (Kill)	root	18	0.0	0.1	/usr/local/apache/bin/httpd -k start -DSSL
    4108 (Trace) (Kill)	root	18	0.0	0.1	/usr/bin/perl /usr/local/cpanel/bin/leechprotect
    4394 (Trace) (Kill)	nobody	18	0.0	0.2	/usr/local/apache/bin/httpd -k start -DSSL
    305 (Trace) (Kill)	root	0	0.0	0.2	cpsrvd (SSL) - waiting for c --llu=1346331614 --listen=3,4,5,6,7,8
    1220 (Trace) (Kill)	root	-10	0.0	0.4	iscsiuio
    13355 (Trace) (Kill)	root	0	0.0	  0.6	spamd child
    13345 (Trace) (Kill)	root	0	0.0	  0.6	/usr/local/bin/spamd -d --allowed-ips=127.0.0.1 --pidfile=/var/run/spamd.pid --max-children=3 --max-spare=1
    1904 (Trace) (Kill)	68	0	0.0	0.0	hald
    1905 (Trace) (Kill)	root	0	0.0	0.0	hald-runner
    1929 (Trace) (Kill)	named	0	0.0	0.0	/usr/sbin/named -u named
    1961 (Trace) (Kill)	ntp	0	0.0	0.0	ntpd -u ntp:ntp -p /var/run/ntpd.pid -g
    2228 (Trace) (Kill)	root	0	0.0	0.0	/usr/sbin/dovecot
    2229 (Trace) (Kill)	root	0	0.0	0.0	dovecot-auth
    2234 (Trace) (Kill)	dovecot	0	0.0	0.0	pop3-login
    2235 (Trace) (Kill)	dovecot	0	0.0	0.0	pop3-login
    2236 (Trace) (Kill)	dovecot	0	0.0	0.0	imap-login
    2237 (Trace) (Kill)	dovecot	0	0.0	0.0	imap-login
    2260 (Trace) (Kill)	root	0	0.0	0.0	pure-ftpd (SERVER)
    2262 (Trace) (Kill)	root	0	0.0	0.0	/usr/sbin/pure-authd -s /var/run/ftpd.sock -r /usr/sbin/pureauth
    2270 (Trace) (Kill)	root	0	0.0	0.0	crond
    2286 (Trace) (Kill)	root	0	0.0	0.0	/usr/sbin/atd
    2543 (Trace) (Kill)	root	0	0.0	0.0	/sbin/agetty xvc0 9600 vt100-nav
    2544 (Trace) (Kill)	root	0	0.0	0.0	/sbin/mingetty tty1
    2545 (Trace) (Kill)	root	0	0.0	0.0	/sbin/mingetty tty2
    2546 (Trace) (Kill)	root	0	0.0	0.0	/sbin/mingetty tty3
    2547 (Trace) (Kill)	root	0	0.0	0.0	/sbin/mingetty tty4
    2548 (Trace) (Kill)	root	0	0.0	0.0	/sbin/mingetty tty5
    2549 (Trace) (Kill)	root	0	0.0	0.0	/sbin/mingetty tty6
    4395 (Trace) (Kill)	root	0	0.0	0.0	/bin/ps -ewwo pid,user,nice,pmem,pcpu,command
    7142 (Trace) (Kill)	root	0	0.0	0.0	/usr/sbin/sshd
    11397 (Trace) (Kill)	root	0	0.0	0.0	/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/server.xxxx.com.pid
    13319 (Trace) (Kill)	mailnull	0	0.0	0.0	/usr/sbin/exim -bd -q60m
    14013 (Trace) (Kill)	root	0	0.0	0.0	queueprocd - wait to process a task
    14024 (Trace) (Kill)	root	0	0.0	0.0	tailwatchd
    14046 (Trace) (Kill)	root	0	0.0	0.0	cPhulkd - processor
    14058 (Trace) (Kill)	root	18	0.0	0.0	cpanellogd - sleeping for logs
    130 (Trace) (Kill)	root	-5	0.0	0.0	[kswapd0]
    
    Any one help me to reduce the load.
     
  2. backblaze

    backblaze Member

    Joined:
    May 17, 2010
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    where is your database config?
     
  3. pwhjenny

    pwhjenny Well-Known Member

    Joined:
    Aug 31, 2012
    Messages:
    135
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Have you optimized mysql? You can use following command to find what databases running during load spike..

    mysql -e 'show databases'
     
  4. palpalani

    palpalani Member

    Joined:
    Sep 4, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Chennai, Tamil Nadu, India
    cPanel Access Level:
    Website Owner
    Hi @backblaze: here is my my.cnf

    Code:
    [mysqld]
    innodb_file_per_table=1
    max_connections = 300
    key_buffer = 250M
    myisam_sort_buffer_size = 256M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 4000
    thread_cache_size = 286
    interactive_timeout = 20
    wait_timeout = 20
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 8M
    query_cache_size = 512M
    query_cache_type = 1
    tmp_table_size = 1024M
    max_heap_table_size = 1024M
    skip-innodb
    # for slow queries, comment when not used
    #long_query_time=6
    #log-slow-queries=/var/log/mysql-slow.log
    #log-queries-not-using-indexes
    [mysqld_safe]
    open_files_limit = 8192
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M
    [mysqlhotcopy]
    interactive-timeout
    @pwhjenny: Given command `mysql -e 'show databases'` is giving the following output.

    Code:
    Database
    information_schema
    cphulkd
    cs8may12_tamcinlive
    eximstats
    horde
    mydbs_db1
    mydbs_db2
    mydbs_db3
    mydbs_db4
    mydbs_db4
    leechprotect
    logaholicDB_southdreamz
    munin_innodb
    mysql
    roundcube

    and i ran MySQLTuner, it gives the below output.
    Code:
     >>  MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.63-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 193M (Tables: 109)
    [!!] Total fragmented tables: 10
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 17h 59m 31s (204M q [860.721 qps], 959K conn, TX: 834B, RX: 17B)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 1.7G global + 4.5M per thread (300 max threads)
    [OK] Maximum possible memory usage: 3.1G (41% of installed RAM)
    [OK] Slow queries: 0% (318/204M)
    [OK] Highest usage of available connections: 41% (125/300)
    [OK] Key buffer size / total MyISAM indexes: 250.0M/80.5M
    [OK] Key buffer hit rate: 100.0% (1B cached / 184K reads)
    [OK] Query cache efficiency: 83.5% (166M cached / 199M selects)
    [!!] Query cache prunes per day: 31017
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 214K sorts)
    [!!] Temporary tables created on disk: 46% (85K on disk / 181K total)
    [OK] Thread cache hit rate: 99% (125 created / 959K connections)
    [OK] Table cache hit rate: 57% (559 open / 964 opened)
    [OK] Open file limit used: 8% (691/8K)
    [OK] Table locks acquired immediately: 98% (33M immediate / 33M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Increasing the query_cache size over 128M may reduce performance
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 512M) [see warning above]
     
  5. palpalani

    palpalani Member

    Joined:
    Sep 4, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Chennai, Tamil Nadu, India
    cPanel Access Level:
    Website Owner
    Any updates on this issue? Any other information needed?
     
  6. fongst

    fongst Registered

    Joined:
    Oct 29, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    i dont know what should i do
     
  7. palpalani

    palpalani Member

    Joined:
    Sep 4, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Chennai, Tamil Nadu, India
    cPanel Access Level:
    Website Owner
    Iam not get any response for this issue.

    Is that changing server will resolve this error?
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    wget http://hackmysql.com/scripts/mysqlreport
    chmod +x mysqlreport
    ./mysqlreport

    please post results of this tool
    please post the screenshot of munin page (if you have installed in cpanel)

    then

    make changes to my.cnf
    long_query_time=0.3
    log-slow-queries=/var/log/mysql-slow.log

    and restart mysql
    [edit: before restarting mysql, remove old file /var/log/mysql-slow.log]
    it will collect slow query log, which you will post here to see which queries are slow
     
    #8 thinkbot, Oct 30, 2012
    Last edited: Oct 30, 2012
  9. palpalani

    palpalani Member

    Joined:
    Sep 4, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Chennai, Tamil Nadu, India
    cPanel Access Level:
    Website Owner
    Here is output for mysqlreport:

    Code:
    root@server [~]# ./mysqlreport
    Use of uninitialized value in subtraction (-) at ./mysqlreport line 648.
    Use of uninitialized value in subtraction (-) at ./mysqlreport line 648.
    Use of uninitialized value in multiplication (*) at ./mysqlreport line 648.
    Use of uninitialized value in multiplication (*) at ./mysqlreport line 652.
    Use of uninitialized value in multiplication (*) at ./mysqlreport line 652.
    MySQL 5.1.65-cll         uptime 30 18:49:47     Fri Nov  2 16:57:09 2012
    
    __ Key _________________________________________________________________
    Buffer used   102.97M of 250.00M  %Used:  41.19
      Current     123.25M            %Usage:  49.30
    Write hit      37.07%
    Read hit      100.00%
    
    __ Questions ___________________________________________________________
    Total           2.62G   986.4/s
      QC Hits       2.20G   828.1/s  %Total:  83.95
      DMS         369.74M   139.0/s           14.09
      Com_         46.32M    17.4/s            1.77
      COM_QUIT     10.48M     3.9/s            0.40
      -Unknown      5.41M     2.0/s            0.21
    Slow 10 s       4.92k     0.0/s            0.00  %DMS:   0.00  Log: OFF
    DMS           369.74M   139.0/s           14.09
      SELECT      363.89M   136.8/s           13.87         98.42
      UPDATE        5.02M     1.9/s            0.19          1.36
      DELETE      607.38k     0.2/s            0.02          0.16
      REPLACE     147.73k     0.1/s            0.01          0.04
      INSERT       76.93k     0.0/s            0.00          0.02
    Com_           46.32M    17.4/s            1.77
      set_option   30.61M    11.5/s            1.17
      change_db    10.29M     3.9/s            0.39
      stmt_close    1.79M     0.7/s            0.07
    
    __ SELECT and Sort _____________________________________________________
    Scan           36.80M    13.8/s %SELECT:  10.11
    Range          32.72M    12.3/s            8.99
    Full join           0       0/s            0.00
    Range check         0       0/s            0.00
    Full rng join      14     0.0/s            0.00
    Sort scan     631.16k     0.2/s
    Sort range    687.95k     0.3/s
    Sort mrg pass  17.29k     0.0/s
    
    __ Query Cache _________________________________________________________
    Memory usage  364.32M of 512.00M  %Used:  71.16
    Block Fragmnt  19.79%
    Hits            2.20G   828.1/s
    Inserts       349.08M   131.2/s
    Insrt:Prune  336.48:1   130.9/s
    Hit:Insert     6.31:1
    
    __ Table Locks _________________________________________________________
    Waited          7.70M     2.9/s  %Total:   2.08
    Immediate     362.94M   136.5/s
    
    __ Tables ______________________________________________________________
    Open              837 of 4000    %Cache:  20.93
    Opened          2.07k     0.0/s
    
    __ Connections _________________________________________________________
    Max used          113 of  300      %Max:  37.67
    Total          10.48M     3.9/s
    
    __ Created Temp ________________________________________________________
    Disk table    292.22k     0.1/s
    Table         429.56k     0.2/s    Size:   1.0G
    File           34.58k     0.0/s
    
    __ Threads _____________________________________________________________
    Running             4 of    5
    Cached            109 of  286      %Hit:    100
    Created           113     0.0/s
    Slow                1     0.0/s
    
    __ Aborted _____________________________________________________________
    Clients        10.73k     0.0/s
    Connects        6.98k     0.0/s
    
    __ Bytes _______________________________________________________________
    Sent           10.33T    3.9M/s
    Received      220.31G   82.8k/s
    root@server [~]#
    I didn't installed munin tool.

    I will update slow query logs soon.
     
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    and btw. 100% in top, means using full of 1 core, not full CPU
    if top shows 200% on 2-core CPU, then this is 100% of CPU usage

    (it's a bit more complicated, whet it comes to describe load, but this is the simplest way)

    based on your current usage, you got over 4920 slow queries (time > 10s)
    and tons of temporary tables

    most of the queries are in query cache which is good, but you should make query cache smaller,
    like
    query_cache_limit = 2M
    query_cache_size = 150M
    query_cache_type = 1

    big query_cache_size can make inserting queries to cache slower

    the optimization in your case will mostly consist of optimizing the top slowest queries (the ones that the most time) and the ones using temporary tables

    so collect some queries with settings as provided:
    long_query_time=0.3

    then analyze with mk-query-digest
     
Loading...

Share This Page