MySQL Forums
Forum List  »  InnoDB

Re: Shudden Slow down of MySQL Queries
Posted by: Rick James
Date: October 28, 2014 02:24PM

> We are deleting old data based on datum column, " begin transaction: select id from availability where datum < 'yesterday', loop begins: delete from availability where id='?'; loop end: commit;

Terribly inefficient.
DELETE FROM availability WHERE datum < NOW() - INTERVAL 24 HOUR LIMIT 1000;
Run it every hour, or more frequently if you think there are more than 1000 per hour. See also:
http://mysql.rjweb.org/doc.php/deletebig

Do ANALYZE TABLE whenever there is a problem. It will _usually_ solve the problem. You need 5.6.xx (5.6.8 or later) to get a more permanent solution -- "persistent statistics". But upgrade to 5.5 for now; move to 5.6 after you have shaken down 5.5.

> What are the monitoring tools (or) any do you prefer, for being proactive.
Slowlog, with long_query_time = 1.

Options: ReplyQuote


Subject
Views
Written By
Posted
1883
October 24, 2014 04:50AM
1306
October 26, 2014 11:53AM
1186
October 27, 2014 11:43AM
Re: Shudden Slow down of MySQL Queries
1227
October 28, 2014 02:24PM
969
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.