MySQL Forums
Forum List  »  InnoDB

Re: Shudden Slow down of MySQL Queries
Posted by: Rick James
Date: October 29, 2014 08:18AM

> data+indexs become 60GB. but our server has only 48GB RAM, 30GB buffer-pool, Hence performance will be degraded.
Usually, Is everybody who ever are using innodb tables should maintain enough RAM ?, Otherwise they have to segregate the data as well.

Un-panic.

If you have performance problems as InnoDB tables grow bigger than the buffer_pool, let's look at the first few queries in pt-query-digest. They will probably be table scans, and there will probably be a way to change them to avoid such.

Even in the worst of buffer_pool vs data, a "point query" (SELECT ... WHERE exact row...) will take only one disk hit of about 1/100 second. It's table scans that cause trouble. (There are, of course, queries that fall in between.)

> long_query_time = 0

A bit of overkill; is the slow log filling up the disk quite fast? A value of 1 usually is good enough to find the naughty queries. Or you could use a fractional value (eg, 0.2).

> avg execution time

Performance comes in two main buckets:
* How fast is a user's request (which might involve multiple queries) getting finished -- "latency".
* Is the system melting down -- "throughput".

pt-query-digest provides "avg execution time", which is useful for latency (of a single command anyway). But more importantly, it sorts the queries by total execution time -- effectively the product of the avg and the count. I find that if you focus on this "total", the other problems tend to vanish.

So, let's see the top could of queries, and provide the suggested SHOW and EXPLAIN.

> Total lock-time

What good is that? Maybe (Total lock-time) / (Total time) would be useful?

And, what can you do with lock time? Or is this mostly aimed at MyISAM, which has significant lock issues, unlike InnoDB?

> delete with limit is not a safe bin-log statement it seems, as we are using Master-Slave (Statement based) replication

True. There's an old bug report on that. In some cases warning is actually bogus and could be avoided. Here's an alternative to avoid it (if it applies to your case):

SELECT @cutoff := id FROM tbl WHERE ... ORDER BY id LIMIT 1000,1;
DELETE FROM tbl WHERE ... AND id < @cutoff ORDER BY id;
(or something like that)
That works especially well when the WHERE clause is always TRUE, such as when the id is AUTO_INCREMENT and the WHERE is looking at the oldest dates.

The SELECT primes the cache (on the Master), making the DELETE actually run faster. However, the Slave won't have such a priming effect (since the SELECT is not replicated). Hence, if you were to put this in a loop (which your case does not seem to need), a SLEEP would be very desirable.

> Rick James, I'm really happy about your suggestions.

I have dealt with literally thousands of situations. I have personal experience (fingers on keyboard, not just looking over shoulders) in many of the aspects in this thread. On this set of forums, I have done 19,679 replies to 14,494 threads from 10,782 askers. (All too many of those replies were "please provide ... so that I can help you". You have responded, so this thread is getting somewhere, making the process more fun for me.)

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
1228
October 28, 2014 02:24PM
Re: Shudden Slow down of MySQL Queries
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.