#1  
Old 14th February 2011, 04:57
newbie14 newbie14 is offline
Member
 
Join Date: Jul 2010
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mysql Optimisation

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
Reply With Quote
Sponsored Links
  #2  
Old 14th February 2011, 13:40
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 37,044
Thanks: 841
Thanked 5,661 Times in 4,468 Posts
Default

You can use e.g. mysqltuner to optimize the mysql configuration:

http://www.faqforge.com/linux/optimi...th-mysqltuner/
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 14th February 2011, 19:59
newbie14 newbie14 is offline
Member
 
Join Date: Jul 2010
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear Till,
Thank for the link. Must I install perl first to run the script is it? Besides that 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.
Reply With Quote
  #4  
Old 14th February 2011, 20:25
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 37,044
Thanks: 841
Thanked 5,661 Times in 4,468 Posts
 
Default

Quote:
Must I install perl first to run the script is it?
yes.

Quote:
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.
Mysql has several variables that define the max resources used by the system. Run mysltuner, the script will give you some good new default values based on the resource usage of your database and tell you where the bottlenecks are.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CPU load locks up box. Apache or MYSQL related. crypted General 61 29th October 2010 23:16
Squirrelmail login failure Cracklefish Installation/Configuration 9 30th June 2010 21:38
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 14:58
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 10:11
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 17:04


All times are GMT +2. The time now is 20:04.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.