mysql high loads in top

[SupermanInNY]

Hi All,

I have a server that is being under DoS attacks for some time.
We managed to decrease the attacks substantially and now httpd-status shows a normal activity.

However, I'm experiencing high CPU activity that I'm not clear as to what is the cause of it, and let me explain.

When I run top, I see mysql poping at the top of the list followed by httpd.

I am running the utility called mytop that shows me the current live queries as they are being run.

The active database is only 2.1MB in size and it has queries that are not lasting very long.
There is no problem with server's memory (2GB) as we do not use any SWAP space.

my.cnf shows the following:

# less /etc/my.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2
set-variable = max_connections=600
local-infile=0
max_allowed_packet = 32M

# set-variable = long_query_time=2
# log-long-format
# log-slow-queries = /var/lib/mysql/slow_queries.log


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

---------------------------------

This is a Core2Duo E6600 single processor server with 2GB RAM.
When I run the "log-slow-queries", I get no entries at all.
Yet, the CPU activity is extremly hight (almost constantly on 3.50 to 4.55) and even at low activity times it is still as high as 1.20 or so.

Any pointers on how-to identify the problem?

Thanks for any help on this.

-Alon.

 

[tillo]

There are a few ways to know what's going on, I may be able to help if hired.
I generally use:

• phpMyAdmin
• innotop
• maatkit
• mysqlreport
• mysqlsla
• mysqltuner
• tuning-primer

You may want to search for those on the Web and learn how to use them.

 

[webquarry]

If you shut off mysql, does the load disappear?

if so, it sounds like a poorly constructed query is killing you.

look in my.cnf to see what the threashold for slow query logging is. Set it for something low and let mysql run for a while.

In general, you will find this site to be very helpful:

http://hackmysql.com/