Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Change max_heap_table_size value in MySQL?
The max_heap_table_size is a system variable that has both read/write property.
Initially, max_heap_table_size has size 16 MB. First, check the value of max_heap_table_size, which is in bytes.
The query is as follows −
mysql> select @@max_heap_table_size;
The following is the output −
+-----------------------+ | @@max_heap_table_size | +-----------------------+ | 16777216 | +-----------------------+ 1 row in set (0.00 sec)
Now let us how the value 16777216 byte = 16 MB −
1 MB = 1024KB 1KB = 1024 Bytes 1MB = 1024*1024 bytes. To convert 16777216 byte to MB you need to divide 1024*1024. =16777216/(1024*1024) =16777216/1048576 =16 MB
Now you can change the max_heap_table_size using SET command. The syntax is as follows −
SET @@@@max_heap_table_size=yourNumberOfBytes.
Let us change max_heap_table_size value. The number of bytes is 33554432, which is equal to 32 MB.
The query is as follows −
mysql> set @@max_heap_table_size=33554432; Query OK, 0 rows affected (0.00 sec)
Now check the value of @@max_heap_table_size. The query is as follows −
mysql> select @@max_heap_table_size;
The following is the output −
+-----------------------+ | @@max_heap_table_size | +-----------------------+ | 33554432 | +-----------------------+ 1 row in set (0.00 sec)
Let us see how whether it is equal to 32MB or not. The formulae used here is discussed above −
mysql> select @@max_heap_table_size/1048576 as MB;
The following is the output −
+---------+ | MB | +---------+ | 32.0000 | +---------+ 1 row in set (0.00 sec)
Updated on: 30-Jul-2019
2K+ Views
Advertisements