13

Is possible to obtain used memory in a running query?

Something like:

mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql before run query
mysql> SELECT id FROM table WHERE id>2; #run query
mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql after run query

and return something like (return 17kb):

+----------+
|  memory  |
+----------+
|  20100   |
+----------+

+------+
|  id  |
+------+
|  3   |
+------+

+----------+
|  memory  |
+----------+
|  2026767 |
+----------+

or

mysql> USAGE SELECT id FROM table WHERE id>2;

and return something like (return 17kb):

+---------+
|  USAGE  |
+---------+
|  16767  |
+---------+

Is this possible?

I need it to do various tests and see which engines queries and performs better (personal experience).

1
  • 1
    @tombom I disagree. The question is quite straightforward: "is there a way to directly ask MySQL about memory usage by a specific query?" Jan 11, 2014 at 15:42

2 Answers 2

7

In MySQL 5.7, assuming you're using mostly INNODB tables, you can see how much memory is currently being used by running this query:

SHOW ENGINE INNODB STATUS

You can see the maximum amount of memory your DB will ever use by running this query:

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( 
    @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
2
  • MySQL doesn't really adhere to this. My server says max memory with your query of 36.4 GB. I've watched it eat 120 GB just for the MySQL process daily. To be clear though I'm also on MySQL 8.
    – Tony Maro
    Aug 17, 2022 at 19:40
  • @TonyMaro are you using INNODB? MyISAM tables are totally different. Aug 23, 2022 at 21:00
3

The answer appears to be "not yet."

It looks like this is being added to MySQL Server 5.7.

http://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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