MySQL Bugs: #35910: MySQL Consumes 99% of CPU

Bug #35910	MySQL Consumes 99% of CPU
Submitted:	8 Apr 2008 18:52	Modified:	16 May 2008 10:13
Reporter:	Emiliano Sandler	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.23	OS:	Windows (Server 2003)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	99 cpu innodb stored procedures

Description:
I have a database with INNODB and Stored Procedures that is used by a gameserver.

The gameserver uses ODBC Connector 3.51 and has an average of 1000 simultaneous users at the same time (With 1 ODBC connection)

The problem happens when the server software is started, like if it's closed, and I re-open it, some people try to connect at the same time and the mysql server stucks using 99% of the CPU, for more tan 30 minutes.

Then it happens some times a in the day, and CPU usage never gets lower than 50%

I am using a Quad core processor with 2GB of ram.

How to repeat:
Restarting the server software

Thank you for the report.

Version 5.1.22 is a bit old. Please upgrade to version 5.1.23 and if problem still exists provide your configuration file and output of show status like '%connect%'; taken in time when server takes 99% CPU.

The problem is that I can't use MySQL 5.1.23 because gives me errors wile creating some stored procedures.

I am adding one of the procedures that give me error in hidden from public mode.

Just remove "*/" from string with "END */"

The my.ini file that i am actually using

Attachment: my.ini (application/octet-stream, text), 9.04 KiB.

Ok,
Now with 5.1.23 the performance is better.

But the server is still reaching 99% of CPU for some seconds, then it goes down to 50~70%, then comes back to 90% an repeates.

The show stats like '%connect%'; results:

mysql> show status like '%connect%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     |
| Connections              | 1751  |
| Max_used_connections     | 30    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 19    |
+--------------------------+-------+
7 rows in set (0.01 sec)

Then, I have sent the my.ini as a file because it's large.

There probably is no bug in InnoDB. Your stored procedures run entirely in the buffer pool and read 0.5 million rows per second:

Pages read 12139, created 1279, written 1251892
0.00 reads/s, 0.00 creates/s, 8.46 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
4 queries inside InnoDB, 0 queries in queue
5 read views open inside InnoDB
Main thread id 53412, state: flushing buffer pool pages
Number of rows inserted 280158, updated 1373683, deleted 40229, read 80631616
1.96 inserts/s, 9.61 updates/s, 0.28 deletes/s, 509858.87 reads/s

What makes you think there is a bug in MySQL/InnoDB?

Regards,

Heikki

I think there is a bug or bad configuration because mysqld stucks at 99% of CPU frequently and for more than 10 minutes.

Emiliano,

I don't think that this is a bug.

You are using mixed insert/delete/update/select statement.

Did you ever think about indexing? You know, indexes will slow down inserts but will be fast on selects.

Try to use disable/enable keys here (ALTER TABLE tab DISABLE KEYS;).

Also please read our documentation what is needful for your database to configure in your my.cnf.

You can see all avaiable variables with: "SHOW VARIABLES;"

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".