CODEWITHSUNDEEP
mysqltemp-tables
You Old Fool
You Old Fool

Reputation: 22960

How can I get the size of a TEMPORARY MySQL MEMORY table?

While inserting rows into a temporary table with ENGINE=MEMORY I've come up against the error:

The table 'tmp' is full

I know I can increase the max_heap_table_size variable to overcome this but the trouble is I have no idea how much memory the current data is using.

I've tried SHOW TABLE STATUS; but it doesn't seem to include temporary tables. I've also looked at information_schema.tables but again, it doesn't show any information about temporary tables.

How can I get the current usage statistics of a temporary table?

Upvotes: 2

Views: 2595

Answers (1)

Rick James
Rick James

Reputation: 142433

For the sake of this debugging, make it a permanent table long enough to get your answer. Even bake it in:

CREATE /* not TEMPORARY */ TABLE tmp...;
INSERT INTO tmp...;
use `tmp`
SHOW TABLE STATUS LIKE 'tmp';
DROP TABLE tmp;

If this tmp table needs to exist for multiple connections simultaneously, then you have a potentially worse problem -- swapping or running out of memory. I bring that up rather than discussion how you could make the name tmp unique to the connection.

Note that you need to decrease innodb_buffer_pool_size (and perhaps other cache limits) to leave enough RAM for you tmp table. You are "robbing peter to pay paul"; it may lead to worse overall performance.

Please describe further what you are doing. It may be that MEMORY is not the best solution.

Upvotes: 2

Related Questions