My sql problem ? No reason to MySQL eat 100% CPU

[konrath]

Hello

the mysql is eating the processors

( CPU ) >>> 102.4 1.5 1:41.15 mysqld

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23330 mysql 16 0 737m 88m 4460 S 102.4 1.5 1:41.15 mysqld
5995 tomcat 27 12 197m 54m 6292 S 0.7 0.9 0:10.92 jsvc
11 root 5 -10 0 0 0 S 0.3 0.0 0:06.54 events/1
11882 named 20 0 79784 25m 2324 S 0.3 0.4 1:32.59 named
18908 nobody 19 4 151m 78m 3488 S 0.3 1.3 0:00.16 httpd
1 root 16 0 1888 632 536 S 0.0 0.0 0:16.90 init


---------------------------

mytop show nothing.


Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
21 root localhost test 0 Query show full processlist

---------------------------

Pid Owner Priority Cpu % Mem % Command
23330 mysql 0 71.4 1.6 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.vipreseller7.net.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

---------------------------


No reason to MySQL eat 100% CPU

This happens at 3 o'clock in the morning. No site overloading the server.

Any suggestions?



Thank you
Konrath

[konrath]

Hello

I found the problem. The innodb is eating the processor.

How to fix this problem ? I want keep innodb enable.

Thank you
Konrath

[GaryT]

Sounds like values are set to high on some parameters.

Install mysql tuner via SSH by:

wget mysqltuner.pl

Once done run the mysql tuner by:

perl mysqltuner.pl

My advice is run it once, Change settings, Then restart mysql - Then leave it for a around 4 days then run it again, And eventually you will get the most stable and best performance from your mysql.

[konrath]

Hello GaryT

The problem continues. I will execute your recomendation.

When the innodb are activated, the mysql eat 100% of CPU.

Thank you
Konrath

[hostnex]

Hello GaryT

The problem continues. I will execute your recomendation.

When the innodb are activated, the mysql eat 100% of the processor.

Thank you
Konrath

We are having the same kind of issue where MySQL service stop responding and take 100 % CPU usage during cpanel nightly backups. Any Solutions ?

[konrath]

We are having the same kind of issue where MySQL service stop responding and take 100 % CPU usage during cpanel nightly backups. Any Solutions ?

Hello Hostnex

I have this problem with or without backup running. The problem is with inoodb.

When inoodb is running the MYSQL eat 100% of CPU.

The inoodb file has 5GB. ( 5194645504 Sep 7 15:34 ibdata1 )

See

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7898 mysql 21 6 1100m 574m 5076 S 103.8 9.4 185:45.47 mysqld



Thank you
Konrath

[B12Org]

In my experience this has been caused by queries against innodb tables that are kind or large or changing often that do not have indexes and thus require a full table scan for every operation.

[konrath]

In my experience this has been caused by queries against innodb tables that are kind or large or changing often that do not have indexes and thus require a full table scan for every operation.

Hello B12ORG

How to enable indexes ?

Thank you
Konrath

[B12Org]

Please see mysql documentation on indexes - it will explain better than I can.
MySQL :: MySQL 5.0 Reference Manual :: 7.5.3 How MySQL Uses Indexes

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

[hostnex]

We moved to the new server and now everything working fine