MySQL Forums
Forum List  »  InnoDB

Re: Shudden Slow down of MySQL Queries
Posted by: Rick James
Date: October 27, 2014 11:43AM

> RAM : 48GB, buffer_pool: 32GB, log_buffer: 2GB, data-size ( innodb tables ): 12GB.

OK, That looks fine (12GB << 32GB << 48GB) -- you should not be doing much I/O. And defragmenting (OPTIMIZE TABLE) would have essentially no benefit.

> indexes: room_id_date (room_id,date_of_avail), room_id (room_id), hotel_id (hotel_id).

No PRIMARY KEY?? Probably (room_id,date_of_avail) should be the PK?? Or is (id) the PK, but you failed to list it?? (I would really prefer to see SHOW CREATE TABLE; it answers these and other questions!)

An InnoDB table works better with an explicit PK.

> deleting daily : procedure : start transaction, get the ids of past data, delete one by one row, commit.

Not enough specifics. Perhaps this will give you some ideas:
http://mysql.rjweb.org/doc.php/deletebig

See also INSERT ... ON DUPLICATE KEY UPDATE. That may be better than what sounds like INSERT, then DELETE.

> indexes: room_id_date (room_id,date_of_avail), room_id (room_id), hotel_id (hotel_id).

Any query that might want to use (room_id) can almost as efficiently use (room_id, date_of_avail), so DROP the shorter index. Since every index must be updated on every INSERT and DELETE, this will speed things up.

> we are planning to update the MySQL verion ( 5.5.40), Please suggest.

Good idea. It is not likely to help with the current problem, but it is good to upgrade.

I was hoping to see something in the InnoDB STATUS that matched something in the PROCESSLIST. Does the issue occur during the daily delete? If so, let's focus on it.

Options: ReplyQuote


Subject
Views
Written By
Posted
1883
October 24, 2014 04:50AM
1306
October 26, 2014 11:53AM
Re: Shudden Slow down of MySQL Queries
1186
October 27, 2014 11:43AM
1228
October 28, 2014 02:24PM
970
October 29, 2014 08:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.