MySQL Bugs: #25374: Innodb low performace

Bug #25374	Innodb low performace
Submitted:	2 Jan 2007 19:52	Modified:	7 Feb 2007 18:11
Reporter:	Pavel Francírek	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.0.27	OS:
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	innodb, lock, performance

Description:
After crash at end end of the year (already reported bug with vanishing free space in InnoDB table space) we restarted databaze (it always helps to free space again) and added new 2GB table space file. But InnoDB totally lost performace. We did over 7000q/s at 50%v CPU load and now it is 100% load with about 4000 q/s. Queries are mostly simple select/update on primary key. We used old 4.0.24 so we tried upgrade to 5.0.26 (dump data, create new table space only 2x 2GB as it was before crash). SHOW INNODB STATUS sais that lots of queries are in "statistics" state. And something like: 
OPERATIONS
--------------
8 queries inside InnoDB, 210 queries in queue
33 read views open inside InnoDB
Main thread process no. 21555, id 32777, state: sleeping
Number of rows inserted 447452, updated 3772226, deleted 445124, read 1208036623
72.18 inserts/s, 656.58 updates/s, 0.00 deletes/s, 175871.63 reads/s 

How to repeat:
sorry, don't know ..

and with 5.0.26 and original config is performance even worse than with 4.0.24

Sorry, the version is 5.0.27-standard-log

Changing transaction levels didn't help.

Pavel,

please post your my.cnf and the full output of SHOW INNODB STATUS\G and vmstat 5 during the slow performance.

Regards,

Heikki

Oops, I see that you already posted what I need! Thank you.
--Heikki

Pavel,

the workload looks like CPU-bound. Only < 20 file I/O operations per second.

You have lots of queries doing sorting in ORDER BY ... DESC. Can you create an index that could speed up those queries?

No threads seem to be waiting for InnoDB semaphores. 71 threads are waiting in the InnoDB queue. This does not look like bugs #15815 and #22868, but with good luck, you could get better performance with MySQL-5.0.30. The source code of it is available at ftp://ftp.mysql.com/pub/mysql/src. Please test with that.

Regards,

Heikki

The 'statistics' status of a MySQL query means that the optimizer is collecting and calculating statistics for query optimization. It is a very common state for a query.

ad index. The data for this index are updated thousand times per second but range selects are only few (maybe tens per second). So we decided to not create index and trade scan overhead for saving of index update overhead. It worked quite good since this problem. Now it really looks like this select scans block row level access.

ad source. We can try but we always preferred binary packages because we never got the same performance with binary compiled by ourselves. But true is that we did so few years ago :)

Thank you. I understand of state 'statistics' now. But it seems to me that too many processes spent too much time in this state especially if queries are using primary key. But it may be caused by amount of this processes.

Pavel,

I did not quite understand, have you already tested an index to help the SELECT query?

Regarding source releases: MySQL AB no longer gives binaries for download of the latest versions of MySQL-5.0.xx Enterprise. You have to build 5.0.30 from source, or find some Linux distro site that contains a binary. Kaj Arnö blogged about this:

http://www.planetmysql.org/kaj/?p=82

Regards,

Heikki

OK. I understand.

We did two steps:
1) Downloaded and compiled 5.0.30-log version
2) Separated frequently updated data to different table so in innodb engine are only queries to one row identified by primary key.

Even then nothing changed. Queries are still slow. One think we don't understand is why the same server, same hardware and same quieries were done at rate 7000q/s before December 31 and are done at rate 4500q/s now.

another status

Attachment: status_5.0.30-1 (application/octet-stream, text), 62.59 KiB.

Pavel,

maybe the table sizes have changed, or query optimization has changed? That could explain why it now uses more CPU.

The InnoDB statuses that you attached do show quite a few semaphore waits. That is a sign of 'thread thrashing'. You should test setting innodb_thread_concurrency to 4, or even to 1, to throttle concurrency.

Regards,

Heikki

Pavel,

that may suggest contention on the MySQL LOCK_open mutex. Please test restricting innodb_thread_concurrency.

--Heikki

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