High cpu usage (mysql/httpd)

DhoTjai

Verified User
Joined
Apr 23, 2006
Messages
80
Location
Netherlands
Hello all,

I am having a problem, i see alot of httpd/mysqld processes.
All websites are loading slow and high cpu.

Code:
top - 00:20:05 up 7 days, 17:56,  1 user,  load average: 59.68, 50.22, 49.06
Tasks: 645 total,  35 running, 608 sleeping,   0 stopped,   2 zombie
Cpu(s): 77.9% us, 22.1% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:   2073800k total,  1691252k used,   382548k free,    16860k buffers
Swap:  4192956k total,      188k used,  4192768k free,   218180k cached

  PID USER      PR  NI %CPU    TIME+  %MEM  VIRT  RES  SHR S COMMAND
 1542 root      16   0    6   0:00.20  0.5 13564  10m 1780 S mrtg
  614 apache    16   0    1   0:01.43  1.1 44132  22m 3708 S httpd
  629 apache    16   0    1   0:01.35  1.1 44132  22m 3708 S httpd
  650 apache    16   0    1   0:01.23  1.1 44008  22m 3700 S httpd
  665 apache    15   0    1   0:01.23  1.1 44008  22m 3700 S httpd
  696 apache    15   0    1   0:01.16  1.1 44008  22m 3700 S httpd
  697 apache    15   0    1   0:01.18  1.1 44008  22m 3700 S httpd
  744 apache    16   0    1   0:01.16  1.1 44008  22m 3700 S httpd
 1209 mysql     15   0    1   0:01.09  2.9  356m  57m 2436 S mysqld
  621 apache    15   0    1   0:01.38  1.1 44132  22m 3708 S httpd
  622 apache    15   0    1   0:01.41  1.1 44132  22m 3708 S httpd
What can I do about it?
Is it because of an exploited website? How can I find/trace/locate it?

With kind regards,

DhoTjai
 

chatwizrd

Verified User
Joined
Jul 3, 2005
Messages
2,007
Try the mytop command to see what is causing it. Try tweaking /etc/my.cnf
 

DhoTjai

Verified User
Joined
Apr 23, 2006
Messages
80
Location
Netherlands
My my.cnf
Code:
[mysqld]
local-infile=0
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout
I have an Intel Core 2 Duo E6400, 2GB ram

With kind regards,

DhoTjai
 

DhoTjai

Verified User
Joined
Apr 23, 2006
Messages
80
Location
Netherlands
Nobody Check Security Tool
Code:
Done Scanning
Clean Processes: 256
Your server is all clean and safe - keep up the good work!
In my /tmp there are:
cache files of a php webpage (categories_box-english.cacheXX)
sess_files
clamav
Da backup files
wrtxxxx files (when I open them, I mainly see: <?php /* Smarty version 2.6.12, created on 2008-05-22 12:24:08
with some other php codes here and paths)
 

DhoTjai

Verified User
Joined
Apr 23, 2006
Messages
80
Location
Netherlands
mytop gives me:
Code:
MySQL on localhost (5.0.51a-community)                                                                                                                                                                               up 0+00:00:50 [01:33:04]
 Queries: 48.5k  qps:  992 Slow:     0.0         Se/In/Up/De(%):    79/00/00/00
             qps now: 2158 Slow qps: 0.0  Threads:  229 ( 228/   8) 81/00/00/00
 Key Efficiency: 92.5%  Bps in/out:   5.8/ 4.2k   Now in/out:   8.1/14.1k

      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
     713      root       localhost       test         0  Query show full processlist
     952 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'a51befb7fe9e2b73b473e55e4401d88f'
     962 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = '957ef130619dcc857553c517c0e49e9d'
     972 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'acd2ca3652cf8ce4f8e22f32f76c9912'
     971 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'ebe75562216fd4cb15853bca78f75fd5'
     969 user1       localhost user1         0  Sleep
     967 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'c39048d42ce37848358aab240dbce0ac'
     739 user1       localhost user1         1  Sleep
     944 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '0a27a6c31ee5fa7ae42e73797b18e6ce'
     945 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '581809af3683f370489f32e5717add6f'
     946 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '33a2b5df7e064e823421d58cc7ac5e73'
     948 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fb957ba19549ce0b6c1fa294a26de954'
     947 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '3da06bf07e69e6ffbb1e89e2b2209432'
     949 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '2c37779ace84c4082630239624d42df9'
     950 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'eb0419bf26b1869ddd80b39610c6784b'
     951 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'e47e13db2740ec17e364ddc4aa4b714f'
     953 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fb35dc64fa40aef8a9ed9a367f77aad7'
     954 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '4230a0e8c880c590107ede0d6b51d136'
     955 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'a9c9a9deb3aa3e58ac490d514f147db8'
     956 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '40c53c7e4c4f27b57ee28b884f457a58'
     957 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'd0a817536d87e9b943eaab47f5084048'
     958 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fc0738a2bfd3d3a66adfa0408b182bb7'
     959 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fcf6eec357162673bc10679feb462fc7'
     963 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'b025ea73433e256d8a465a1691531509'
     960 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '484130eab180b108674c098b5d239788'
     964 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '0dd823cfbacb7ad498f4059e1b3195f7'
    1120 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1121 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1132 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1133 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1134 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1135 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1136 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1137 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1138 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1139 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1140 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1141 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1142 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1143 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1144 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1145 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1146 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1147 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1148 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1149 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1150 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1151 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1152 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1153 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1154 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1155 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1156 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1157 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1158 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1159 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1160 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1161 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1162 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1163 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1164 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1165 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
And other crap, but it's the user1. I believe he's running oscommerce.
 

DhoTjai

Verified User
Joined
Apr 23, 2006
Messages
80
Location
Netherlands
I tried to tweak my.cnf, but that did not work.
Code:
[mysqld]
local-infile=0
skip-locking
skip-innodb
query_cache_limit=2M
query_cache_size=64M
query_cache_type=1
query_prealloc_size = 163840
query_alloc_block_size = 32768
max_connections=500
interactive_timeout=100
wait_timeout=30
connect_timeout=10
tmp_table_size=30M
thread_cache_size=128
key_buffer=384M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=2M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout
My server is still having an heavy load. (httpd/mysqld)
 

Dravu

Verified User
Joined
Sep 9, 2007
Messages
324
mytop gives me:
Code:
MySQL on localhost (5.0.51a-community)                                                                                                                                                                               up 0+00:00:50 [01:33:04]
 Queries: 48.5k  qps:  992 Slow:     0.0         Se/In/Up/De(%):    79/00/00/00
             qps now: 2158 Slow qps: 0.0  Threads:  229 ( 228/   8) 81/00/00/00
 Key Efficiency: 92.5%  Bps in/out:   5.8/ 4.2k   Now in/out:   8.1/14.1k

      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
     713      root       localhost       test         0  Query show full processlist
     952 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'a51befb7fe9e2b73b473e55e4401d88f'
     962 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = '957ef130619dcc857553c517c0e49e9d'
     972 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'acd2ca3652cf8ce4f8e22f32f76c9912'
     971 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'ebe75562216fd4cb15853bca78f75fd5'
     969 user1       localhost user1         0  Sleep
     967 user1       localhost user1         0  Query select count(*) as count from whos_online where session_id = 'c39048d42ce37848358aab240dbce0ac'
     739 user1       localhost user1         1  Sleep
     944 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '0a27a6c31ee5fa7ae42e73797b18e6ce'
     945 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '581809af3683f370489f32e5717add6f'
     946 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '33a2b5df7e064e823421d58cc7ac5e73'
     948 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fb957ba19549ce0b6c1fa294a26de954'
     947 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '3da06bf07e69e6ffbb1e89e2b2209432'
     949 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '2c37779ace84c4082630239624d42df9'
     950 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'eb0419bf26b1869ddd80b39610c6784b'
     951 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'e47e13db2740ec17e364ddc4aa4b714f'
     953 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fb35dc64fa40aef8a9ed9a367f77aad7'
     954 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '4230a0e8c880c590107ede0d6b51d136'
     955 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'a9c9a9deb3aa3e58ac490d514f147db8'
     956 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '40c53c7e4c4f27b57ee28b884f457a58'
     957 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'd0a817536d87e9b943eaab47f5084048'
     958 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fc0738a2bfd3d3a66adfa0408b182bb7'
     959 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'fcf6eec357162673bc10679feb462fc7'
     963 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = 'b025ea73433e256d8a465a1691531509'
     960 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '484130eab180b108674c098b5d239788'
     964 user1       localhost user1         1  Query select count(*) as count from whos_online where session_id = '0dd823cfbacb7ad498f4059e1b3195f7'
    1120 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1121 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1132 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1133 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1134 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1135 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1136 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1137 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1138 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1139 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1140 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1141 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1142 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1143 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1144 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1145 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1146 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1147 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1148 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1149 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1150 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1151 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1152 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1153 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1154 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1155 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1156 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1157 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1158 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1159 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1160 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1161 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1162 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1163 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1164 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
    1165 user1       localhost user1         1  Query delete from whos_online where time_last_click < '1211498283'
And other crap, but it's the user1. I believe he's running oscommerce.
Wow. That is some massive MySQL usage and the queries being done are horribly inefficient as well. It appears user1 is most likely the one causing the very high resource usage though. No kind of tweaking to the MySQL config is really going to save you from the high loads. You're going to have to talk to the user and get them to tweak their script(s), mainly the one calling the Users Online. Instead of updating the users online every pageview, they should probably use a cronjob to update it every minute or few minutes. Right now, the server is basically being forced to act OCD. It's doing the same thing a hundred times every second, but really only needs to do it once every minute or so before going on. :p
 
Last edited:

DhoTjai

Verified User
Joined
Apr 23, 2006
Messages
80
Location
Netherlands
Server load is around 16% now, instead of 70/80++
I've looked at his oscommerce script and it was outdated (2003).
And the mytop command is pretty useful =)

Thanks all for helping.
 
Top