Mysql Optimisation

[newbie14]

Dear All,
I have a server with 16 cpus and 32g ram. The problem below when I run my top it show mysql %cpu is more than 100%. Below is my top results. The problem here I would like to why is mysqld is using up 137.2% of the cpu and my overall cpus is only 5.2%. So I would like to know why when I have so much of resourse but mysqld is running on maximum. Another thing I notice my mysqld is keep restarting? Any help please?

top - 10:46:14 up 21 min, 3 users, load average: 1.16, 1.29, 0.97
Tasks: 279 total, 1 running, 278 sleeping, 0 stopped, 0 zombie
Cpu(s): 5.2%us, 1.2%sy, 0.0%ni, 92.7%id, 0.3%wa, 0.1%hi, 0.5%si, 0.0%st
Mem: 33009800k total, 2027584k used, 30982216k free, 96196k buffers
Swap: 35061752k total, 0k used, 35061752k free, 199944k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5913 mysql 20 0 666m 49m 3844 S 137.2 0.2 0:18.74 mysqld
3679 gdm 20 0 337m 29m 8176 S 9.8 0.1 0:01.24 gnome-settings-
3625 root 20 0 113m 13m 4764 S 7.8 0.0 0:03.63 Xorg
5966 root 20 0 14876 1172 776 R 2.0 0.0 0:00.01 top

[smantscheff]

I don't know about the load distribution and the lack of it, but have a look at the slow query log of mysql to see if you can optimize applications on that level. Also check if all indexes are intact.

[newbie14]

Dear Smantscheff,
Yes I am currently working on the query optimisation based on the slow query log. But on the other hand if you notice the memory usage is always very low. Yes the load sometimes do go up very high and goes down then. But I dont understand why mysql fails when I have huge sum of resources and mysql is just using very minimal of it.Is it that indeed mysql have its limitations? What else can it be? I have checked there is no cron job though.

[smantscheff]

Have a look at your mysql configuration file my.cnf. There are numerous parameters which might limit the ressource usage of mysqld.

[newbie14]

Dear Smantscheff,
Below is my .cnf file. Can you give me your guidance what else to be edited?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
skip-bdb
max_connections = 1000
key_buffer = 4096M
myisam_sort_buffer_size = 64M
join_buffer_size = 16M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 32
wait_timeout = 200
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 4096M
query_cache_size = 256M
query_cache_type = 1
server-id=1283835628
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=fms,sms
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-queries-not-using-indexes
log_warnings = 2

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 10000

[mysqldump]
quick
max_allowed_packet = 16M

[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

[mysql.server]
#user=mysql

[smantscheff]

Since your system has lots of memory, try to increase all buffer and cache sizes by the factor 2 and see if it makes any difference for the server load.

[newbie14]

Dear Smantscheff,
Is there any exact guidelines how this buffer and cache should be adjusted too for my future knowledge. Can this buffer and cache be the cause of retstart in my server? Actually mysql does not have limitation in itself when comes to extensibility right? How this buffer and cache work for both myisam and innodb ?s

[smantscheff]

Sorry, not much experience with it. Look here: http://dev.mysql.com/doc/refman/5.5/en/optimizing-the-server.html