Mysql high Cpu Usage

[chintan Shah]

Hello Experts,

I have a web app on my production server which is running on LAMP. now mysql consuming too much of cpu and my website is running extreamly slow.
when i checked the load-average of cpu it shows me below result.

Load average  36.2,  34.4,  32.6

I have 4 cores of cpu with 2.40GHz


And I have already done the performance tuning of mysql.
for reference please have a look on my.cnf file of my server.

#### start configuration of my.cnf  #########
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

max_allowed_packet = 8M
key_buffer_size = 256M

max_connections=200
query_cache_size= 32M
thread_cache_size = 8
table_cache = 256
sort_buffer_size = 8M
read_buffer_size = 8M


innodb_buffer_pool_size = 256M
long_query_time=5
slow-query-log=/var/lib/mysql/log-slow-queries.log

interactive_timeout=60
wait_timeout=60
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqldump]
max_allowed_packet = 64M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

########### End my.cnf file ##########


And for your information We are using both Myisam/Innodb storage engine type with our tables.

And As per my knowledge there is no way to find which mysql query is taking too much of cpu so now it is big trouble for me to find the root cause of this higher cpu usage issue.



I will appreciate if anyone from the group can help me in this issue.

Looking forward to your valuable reply.


Let me know if you need any other detail from my end.

Thanks.
Regards,
C. Shah

[Bhatt Niraj]

--
--
Please read http://www.catb.org/~esr/faqs/smart-questions.html before posting.
You received this message because you are subscribed to the "Vibrant GNU/Linux User Group".
To stop receiving emails from this group, mail to VGLUG+un...@googlegroups.com
To post to this group, send email to VG...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/VGLUG
 
 
 

Can you please let us know total RAM available on your server and how much memory consume by one mysql process?

For now you can do below changes and restart your mysql server

interactive_timeout=20
wait_timeout=30

--
Thanks & Regards,

Niraj Bhatt
------------------------------------------------------------
Give me space to stand, I will move the earth

[Alok Thaker]

Hi,

If you are using a vm machine please try adding more CPU and adding more memory to it. Please do some optimization of mysql and also identifying slow queries along with just make sure that your machine is not affected by DOS-DDOS Attack.

Thanks & Regards,

Alok 

[ElectroMech]

Hi,

On Thu, Oct 25, 2012 at 9:05 PM, chintan Shah <rhchin...@gmail.com> wrote:

Hello Experts,

I have a web app on my production server which is running on LAMP. now mysql consuming too much of cpu and my website is running extreamly slow.
when i checked the load-average of cpu it shows me below result.

Load average  36.2,  34.4,  32.6

Can you check with top

Cpu(s):  1.6%us,  2.1%sy,  0.0%ni, 94.6%id,  1.6%wa,  0.0%hi,  0.0%si,  0.0%st

what is consuming more cpu usage user, system or wa?
 
You can also use utilities like mytop, mtop, innotop etc. to monitor mysql.

In between you can also try to setup mysql slow query log. for your mysql query.

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined. The default value of long_query_time is 10.

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Thanks.
Regards,
C. Shah

Thanks and Regards.

--
--
Nilesh Vaghela
(RHCSA RHCE)
ElectroMech Corporation
Redhat Channel Partner and Training Partner
302, New York Plaza, Opp Judges Bunglow, Bodakdev, Ahmedabad
22, 1st Floor, Vardhaman Complex, Subhanpura , Baroda.
www.electromech.info

[niraj vara]

Hi

    Please send the output of

  netstat -ntlp

mysqladmin -u root -p proc

slow query log  of mysql

ps aux | grep httpd | wc -l 








--
Niraj M. Vara
Linux Administrator
Mavenvista Pvt. Ltd.
Mobile : 8000977677

"DON'T TELL ME THE SKY IS THE LIMIT, THERE ARE FOOTPRINTS ON THE MOON"

[chintan Shah]

Thank you all for your reply.

@Niraj Bhatt:- Currently I have 8GB of ram in my server and  please have a look below to see how much memory mysql process is consuming.

####
## ps aufx |grep mysql
\root     28323  0.0  0.0 103444   832 pts/3    S+   19:31   0:00  |                   \_ grep mysql
root      1195  0.0  0.0 108332  1596 ?        S    Oct24   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     1533 94.5  2.9 2858924 451876 ?      Sl   Oct24 2635:13  \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
########


And yeah we can reduce the number of  seconds a mysql thread waits in idle state before mysql preempts and kills it but i think if 30 sec is too low because if we set this value then mysql create many threads to handle the connection and it will consume the more resources of server.
Let me know if you think i am wrong.

@Alok:- I have mysql slow query log enabled on the server and the long_query_time is 3 sec and there are only few queries which take more then 3 sec to execute and have already asked our developer to optimize those query for the better performance.

And for DOS/DDOS attacks I am usijng iptables rules to protect the server from DOS/DDOS attack but if you know any other tool which can provide better security from DDOS attache then please let me know.

And i have already done the performace tuning and optimization of mysql and also shared the my.cnf file with you but if you know any other steps for the performance tuning and optimization  then please let me know.


@Nilesh Sir:- I am already using mtop and slow query logs to monitor the performance of mysql queries. but there is no way to find query base cpu utilization means how much cpu each query is consuming, and as per your instruction I checked the output of top command and it is showing the below mentioned result.

Cpu(s): 79.8%us, 19.4%sy,  0.0%ni,  0.7%id,  0.0%wa,  0.0%hi,  0.1%si,  0.1%st

Can you please explain the brief idea about this output.


@Niraj Vara:- sorry i cannot share the output of mysqladmin -u root -p proc(Mysql Processlist ) and slow query logs  but yeah the output of

ps aux | grep httpd | wc -l   is very random means sometime we get 192 apache connection and sometime it is 180,170 or 175. and the MaxClient value of prefork mpm is 200.


Let me know if you need any further detail from my end.

Thanks.
 

On Fri, Oct 26, 2012 at 11:25 AM, Alok Thaker <alok...@gmail.com> wrote:

[chintan Shah]

Any Updates on this..?

Thanks.
C. Shah

[chintan Shah]

Please provide answer of my above questions.

Thanks.

[Nirmal Pathak]

On 30 October 2012 11:05, chintan Shah <rhchin...@gmail.com> wrote:

Please provide answer of my above questions.

Thanks.

Please do not Top Post on the mailing list, it makes thread reading difficult. Please read: http://en.wikipedia.org/wiki/Posting_style#Top-posting 

Members reply when they are free so you should not expect urgent answer or solution.

On Mon, Oct 29, 2012 at 1:46 PM, chintan Shah <rhchin...@gmail.com> wrote:

Any Updates on this..?

Thanks.
C. Shah

On Sat, Oct 27, 2012 at 2:22 AM, chintan Shah <rhchin...@gmail.com> wrote:

Thank you all for your reply.

@Niraj Bhatt:- Currently I have 8GB of ram in my server and  please have a look below to see how much memory mysql process is consuming.

####
## ps aufx |grep mysql
\root     28323  0.0  0.0 103444   832 pts/3    S+   19:31   0:00  |                   \_ grep mysql
root      1195  0.0  0.0 108332  1596 ?        S    Oct24   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     1533 94.5  2.9 2858924 451876 ?      Sl   Oct24 2635:13  \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
########


And yeah we can reduce the number of  seconds a mysql thread waits in idle state before mysql preempts and kills it but i think if 30 sec is too low because if we set this value then mysql create many threads to handle the connection and it will consume the more resources of server.
Let me know if you think i am wrong.

@Alok:- I have mysql slow query log enabled on the server and the long_query_time is 3 sec and there are only few queries which take more then 3 sec to execute and have already asked our developer to optimize those query for the better performance.

And for DOS/DDOS attacks I am usijng iptables rules to protect the server from DOS/DDOS attack but if you know any other tool which can provide better security from DDOS attache then please let me know.

And i have already done the performace tuning and optimization of mysql and also shared the my.cnf file with you but if you know any other steps for the performance tuning and optimization  then please let me know.


@Nilesh Sir:- I am already using mtop and slow query logs to monitor the performance of mysql queries. but there is no way to find query base cpu utilization means how much cpu each query is consuming, and as per your instruction I checked the output of top command and it is showing the below mentioned result.

Cpu(s): 79.8%us, 19.4%sy,  0.0%ni,  0.7%id,  0.0%wa,  0.0%hi,  0.1%si,  0.1%st

Can you please explain the brief idea about this output.

I can see major part of CPU is utilized by User Space Application & this could be MySQL. From previous output of 'ps' command I can see that mysql process is using around 94% of CPU.

mysql     1533 94.5  2.9 2858924 451876 ?      Sl   Oct24 2635:13  \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

 

@Niraj Vara:- sorry i cannot share the output of mysqladmin -u root -p proc(Mysql Processlist ) and slow query logs  but yeah the output of

ps aux | grep httpd | wc -l   is very random means sometime we get 192 apache connection and sometime it is 180,170 or 175. and the MaxClient value of prefork mpm is 200.


Let me know if you need any further detail from my end.

Thanks.

 

Have FuN!
--
Nirmal D Pathak.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Let me help my fellow beings; that is all I seek.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Alok Thaker]

Hi Chintan,

Can you please confirm are you running this machine on virtualization or cloud environment if yes please try to add more cpu to it ,it would surely help in mysql functionality and if not please make sure that there are not number of connections exceeding to mysql from the various clients and indexing is done on tables too. This should surely help.  Along with that do send us your my.cnf file too which might also help in checking out various paramenters. Do let me know if you have any dual master replication for mysql or not and also what is the storage engine you are using for your database is it myISAM or innodb.

Thanks & Regards,

Alok 

[nehal dattani]

Hi Chintan,

Some parts of this email are not for you, so please do not take it personaly

> I have a web app on my production server which is running on LAMP. now
> mysql consuming too much of cpu and my website is running extreamly slow.
> when i checked the load-average of cpu it shows me below result.
>
> Load average 36.2, 34.4, 32.6
>

1) CPU cost of mysql query:

Can you please copy-paste following URL in your browser and see if it helps ?

https://www.google.co.in/search?q=mysql+query+cpu+cost&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:unofficial&client=firefox-a

Natuarrly you have not tried googling which is the fastest and prooven
way of getting information.

I remember some community evengelist (immature one probably ?) saying

"If every user is suggesting to search on Google than whats the use of
this group ? Its their wish i want to search on websites or not ? Is
that Compulsory to search on Google first and then put questions ? If
that so, then please make a guideline that "Please Paste your Link
what you've search on internet and then followed by content"

I am just wondering what their opinion is, for this particular issue.

> Let me know if you need any other detail from my end.

2) If you are not comfirtable sharing data over public mailing list,
probably this problem will catch less attraction. You can sanitize the
data like host-name, ip address, even table name or database name etc
before posting, but performance related parameters are absolutely
required in order to understand the issue. However, if you still do
not want to provide some information, I'd suggest you to ensure that
tables ( especially those on which long running queries are observed)
are properly indexed. Poorly indexed tables are many times cause of
high cpu load.

3) Try using mysqltuner.pl and post the output here, if you want
further assitance.

https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl



--
The production of too many useful things results in too many useless people.

With best regards,
Nehal Dattani