LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
LinkBack Search this Thread
Old 08-12-2007, 05:08 AM   #1
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 488

Rep: Reputation: 37
mysqld sevice eating 99.9% CPU


Hi all,
I am facing with a problem in RHEL4 in mysqld service. Its constantly using 99.9% of CPU. I have posted this problem in mysql forums but do anyone here faced with this before.
Below is the link of the mysql posting of mine because I don't want to made this page huge by pasting those whole outputs again here.
http://forums.mysql.com/read.php?10,...002#msg-167002

I really appreciate your help on this. Thanks in advance.

Many have this same problem in mysql forums also but no one ever answered that question. How come this is not answered.

Waiting for your suggestions and help.
 
Old 08-12-2007, 01:49 PM   #2
Robhogg
Member
 
Registered: Sep 2004
Location: Old York, North Yorks.
Distribution: Debian 7 (mainly)
Posts: 653

Rep: Reputation: 85
Slightly out-of-context, but:

What size are your tables? How are they being used?

I'm thinking of an issue that has recently been resolved where I work. One of our systems (a booking system) uses an MS SQLServer database. It was developing the habit of grinding to a halt at approx 4.30pm every day (busy time) and getting earlier, and the server's processor usage was topped out at 100%.

It turned out that there was a procedure that was supposed to run each night, to dump all records older than 7 days from the reservations table into an "old resrvations" table. This had stopped running (it was being prevented by a backup routine that had been set to run at the same time - D'Oh!). The reservations table was being queried each time a booking was made, or a booked session was started, and when the table got to over a million or so rows it just couldn't cope during busy periods.

OK, a bit anecdotal, but you never know...

Last edited by Robhogg; 08-12-2007 at 01:51 PM.
 
Old 08-13-2007, 10:16 PM   #3
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 488

Original Poster
Rep: Reputation: 37
Thanks for your reply.

As vendor did the development section of web server, mysql, apache so its hard for me to tell on which section they actually did the problem. They did some repair table, analyze table in mysql and changed some global values which did a little.
Its not showing mysql service taking cpu usage 99.9% constantly now but it still reaches there and holds for a certain amount of time.
Dont have any clue on this.
 
Old 08-28-2007, 05:45 AM   #4
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 488

Original Poster
Rep: Reputation: 37
Hi all,

I can't believe that none had ever fell into this problem. Even anyone in mysql forums is not answering this problem. I am using mysql 4.1.12 version and this is my my.cnf file. Is this any version related problem?
One more thing I have another mysql server running with huge hits and in that server I noticed that several mysql instances are running but in this server only one is running.

my.cnf :

Code:
[root@localhost ~]# vi /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 720
connect_timeout = 10
tmp_table_size = 164M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 40M
query_cache_size = 120M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
skip-name-resolve

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -1
open_files_limit = 8192

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
Running this simple query takes immediately 99.9% CPU and if someone else runs in between then it becomes a hell.
Code:
SELECT * FROM division where request_date between '2007-08-21 00:00:00' and '2007-08-21 23:59:59' AND id in (SELECT id FROM division_assign WHERE action_to='Division' AND action_status='0') order by id desc;
Any idea, please....
 
Old 08-28-2007, 05:57 AM   #5
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 488

Original Poster
Rep: Reputation: 37
Output of ps -afx
Code:
32375 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid

32410 ?        S<l    0:29  \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock
 
Old 08-31-2007, 05:08 AM   #6
jack_ramone
LQ Newbie
 
Registered: Aug 2007
Posts: 1

Rep: Reputation: 0
Hi,
I have exactly the same problem (RHE3 + mysqld Ver 4.1.12-max-log for pc-linux-gnu on i686)
I don't know what setup should be changed but I found some workarround. (I know it's not perfect workarround)

I create temporary table which is smaller for just about one column. (this coulumn is not used in my query).

With smaller table my qery works without problems (about 2s).
So, it seems that problem is with the size of table.

best regards
jack
 
Old 09-04-2007, 08:17 AM   #7
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 488

Original Poster
Rep: Reputation: 37
Thank you all for your replies.

I recently implemented that same database in a simple desktop PC with 512 RAM and did some indexing in the most used tables. To my surprise I found that after doing that the mysqld service is not even showing in the 'top' command, that is, so faster processing. Now taking care of indexing issue on the tables. Hope this will solve the problem.
Also changed the mysql server version from 4.1.2 to 5.0.27.

Thank you.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
kacpid eating cpu 99% cpu time! tjclancy Linux - Software 13 09-19-2011 03:08 PM
XFS is eating my CPU xKintaro Debian 0 09-27-2005 09:01 AM
ksoftirqd eating >60% of my CPU subaruwrx Fedora 0 09-22-2004 01:10 AM
Need help with logrotate process eating 99.9% CPU glock19 Linux - General 5 12-30-2002 08:02 PM
kapm-idled eating up cpu Syncrm Linux - General 2 10-11-2002 08:47 PM


All times are GMT -5. The time now is 03:03 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration