mysqld sevice eating 99.9% CPU

tanveer · 08-12-2007, 05:08 AM

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.

Robhogg · 08-12-2007, 01:49 PM

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...

tanveer · 08-13-2007, 10:16 PM

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.

tanveer · 08-28-2007, 05:45 AM

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....

tanveer · 08-28-2007, 05:57 AM

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

jack_ramone · 08-31-2007, 05:08 AM

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

tanveer · 09-04-2007, 08:17 AM

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.