2

What are the factors that would affect the RAM usage by a query?

How do I assess the rough RAM usage of a query given the complete SQL?

This is regarding the MySQL RDBMS.

1

MySQL uses RAM in many ways...

  • For caching data and indexes. In some sense, it does not matter how much RAM, since it is cache. This may be the biggest use, but it is a "shared" use.

  • A complex SELECT may need a temporary table (eg, for GROUP BY and/or ORDER BY and/or subquery). This may be done in RAM, or it may not even attempt to do it in RAM, but instead do it on disk. There is one setting that puts a limit on the size of each in-memory temp table.

  • The parsing of the statement takes a little bit of RAM.

  • The binlog is buffered through RAM.

  • "Group commit" is buffered in RAM.

  • Galera does its "will this commit with a deadlock" logic in RAM.

  • InnoDB uses bits to keep track of what is locked.

Shall I go on? Your question is not easy to answer. Furthermore, I would argue that it is mostly irrelevant. Would you care to explain why you asked?

  • Rick, Thank you for your answer. One of our instances started using RAM all at sudden after running successfully for 100 days. During that time, we have observed a complex query on the query prompt. I just wanted to know whether it can b a reason or not. Your reply is nice enough for me. It would be my strong reference going forward while dealing with this like issues. – Uday Jul 11 '15 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.