| Bug #94646 | An alteration of the system variable "max_heap_table_size" does not take effect? | ||
|---|---|---|---|
| Submitted: | 14 Mar 2019 2:47 | Modified: | 15 Mar 2019 2:12 |
| Reporter: | JianJun Shi | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Memory storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7.23 & 8.0.13 | OS: | Linux |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[14 Mar 2019 2:54]
JianJun Shi
How to repeat:
...
-> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds'
end;
$$
-> );
...
——————————————————————————
Sorry, it is redundant of "end;" "$$" in this part. If you want to reappear the scene, remember to delete them. :)
[14 Mar 2019 11:33]
MySQL Verification Team
Hi, Setting global variable does not change the session value and for max_heap_table_size it's session value that affects the limit so after you set global value for max_heap_table_size you need to close session and create new session to have that value loaded, or you need to set session.... Check before running your test select @@session.max_heap_table_size; Look at https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html As mentioned earlier, the max_heap_table_size system variable sets the limit on the maximum size of MEMORY tables. To control the maximum size for individual tables, set the session value of this variable before creating each table. (Do not change the global max_heap_table_size value unless you intend the value to be used for MEMORY tables created by all clients.) The following example creates two MEMORY tables, with a maximum size of 1MB and 2MB, respectively: mysql> SET max_heap_table_size = 1024*1024; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY; Query OK, 0 rows affected (0.01 sec) mysql> SET max_heap_table_size = 1024*1024*2; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY; Query OK, 0 rows affected (0.00 sec) E.g. mysql [localhost:8015] {msandbox} (testmysql) > set max_heap_table_size =1024*1024; Query OK, 0 rows affected (0.00 sec) mysql [localhost:8015] {msandbox} (testmysql) > create table MANUAL_056_taurus_T ( -> C_ID INT, -> C_D_ID INTEGER NOT NULL, -> C_W_ID BIGINT NOT NULL, -> C_DOUBLE DOUBLE NOT NULL, -> C_DECIMAL DECIMAL NOT NULL, -> C_FIRST VARCHAR(64) NOT NULL, -> C_MIDDLE CHAR(2), -> C_LAST VARCHAR(64) NOT NULL, -> C_STREET_1 VARCHAR(20) NOT NULL, -> C_STREET_2 VARCHAR(20), -> C_CITY VARCHAR(64) NOT NULL, -> C_STATE CHAR(2) NOT NULL, -> C_ZIP CHAR(9) NOT NULL, -> C_PHONE CHAR(16) NOT NULL, -> C_SINCE TIMESTAMP, -> C_CREDIT CHAR(2) NOT NULL, -> C_CREDIT_LIM NUMERIC(12,2), -> C_DISCOUNT NUMERIC(4,4), -> C_BALANCE NUMERIC(12,2), -> C_YTD_PAYMENT REAL NOT NULL, -> C_PAYMENT_CNT FLOAT NOT NULL, -> C_DELIVERY_CNT BOOLEAN NOT NULL, -> C_END DATE NOT NULL, -> C_VCHAR VARCHAR(1000) -> ) engine=MEMORY; Query OK, 0 rows affected (0.00 sec) mysql [localhost:8015] {msandbox} (testmysql) > call insertvalues_MEMORY(); ERROR 1114 (HY000): The table 'MANUAL_056_taurus_T' is full mysql [localhost:8015] {msandbox} (testmysql) > select count(*) from MANUAL_056_taurus_T; +----------+ | count(*) | +----------+ | 780 | +----------+ 1 row in set (0.00 sec) mysql [localhost:8015] {msandbox} (testmysql) > select TABLE_NAME, DATA_LENGTH/1024/1024, INDEX_LENGTH/1024/1024 from information_schema.tables WHERE TABLE_NAME ='MANUAL_ 056_taurus_T'; +---------------------+-----------------------+------------------------+ | TABLE_NAME | DATA_LENGTH/1024/1024 | INDEX_LENGTH/1024/1024 | +---------------------+-----------------------+------------------------+ | MANUAL_056_taurus_T | 1.00259399 | 0.00000000 | +---------------------+-----------------------+------------------------+ 1 row in set (0.00 sec) kind regards Bogdan
[15 Mar 2019 2:12]
JianJun Shi
Thank you for patient answer my question! :D
Description: While we set @@global.max_heap_table_size to 8388608, it mains, if we create an MEMORY table, we can only insert into the table with 8388608/1024/1024=8M data in theory. Actually, we can insert 16M data, which is the default value. So, an alteration of the system variable "max_heap_table_size" does not take effect? How to repeat: mysql> set @@global.max_heap_table_size=8388608; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.max_heap_table_size; +------------------------------+ | @@global.max_heap_table_size | +------------------------------+ | 8388608 | +------------------------------+ 1 row in set (0.00 sec) mysql> set @@global.innodb_file_per_table=ON; Query OK, 0 rows affected (0.00 sec) mysql> create database MANUAL_056_taurus; Query OK, 1 row affected (0.05 sec) mysql> use MANUAL_056_taurus; Database changed mysql> create table MANUAL_056_taurus_T ( -> C_ID INT, -> C_D_ID INTEGER NOT NULL, -> C_W_ID BIGINT NOT NULL, -> C_DOUBLE DOUBLE NOT NULL, -> C_DECIMAL DECIMAL NOT NULL, -> C_FIRST VARCHAR(64) NOT NULL, -> C_MIDDLE CHAR(2), -> C_LAST VARCHAR(64) NOT NULL, -> C_STREET_1 VARCHAR(20) NOT NULL, -> C_STREET_2 VARCHAR(20), -> C_CITY VARCHAR(64) NOT NULL, -> C_STATE CHAR(2) NOT NULL, -> C_ZIP CHAR(9) NOT NULL, -> C_PHONE CHAR(16) NOT NULL, -> C_SINCE TIMESTAMP, -> C_CREDIT CHAR(2) NOT NULL, -> C_CREDIT_LIM NUMERIC(12,2), -> C_DISCOUNT NUMERIC(4,4), -> C_BALANCE NUMERIC(12,2), -> C_YTD_PAYMENT REAL NOT NULL, -> C_PAYMENT_CNT FLOAT NOT NULL, -> C_DELIVERY_CNT BOOLEAN NOT NULL, -> C_END DATE NOT NULL, -> C_VCHAR VARCHAR(1000) -> ) engine=MEMORY; Query OK, 0 rows affected (0.08 sec) mysql> DELIMITER $$ mysql> CREATE PROCEDURE insertvalues_MEMORY() -> begin -> declare num_temp int; -> set num_temp=0; -> -> while num_temp<5000 do -> INSERT INTO MANUAL_056_taurus_T VALUES ( -> num_temp+1, -> num_temp+1, -> 234213542352134, -> 100.2, -> 123523.2132, -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds', -> 'q2', -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds', -> 'dfecddfecddfecddfecd', -> 'dfecddfecddfecddfecd', -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds', -> 'sw', -> 'ewfxejdsk', -> 'wedjkswikilsdcnr', -> '2019-03-11 17:00:01', -> '21', -> 12342124.23, -> 0.2, -> 2323423.56, -> 2342324.3454, -> 1200234.235, -> true, -> '2019-03-11', -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds' end; $$ -> ); -> set num_temp=num_temp+1; -> end while; -> end; -> $$ mysql> DELIMITER ; mysql> call insertvalues_MEMORY(); ERROR 1114 (HY000): The table 'MANUAL_056_taurus_T' is full mysql> select count(*) from MANUAL_056_taurus.MANUAL_056_taurus_T; +----------+ | count(*) | +----------+ | 3250 | +----------+ 1 row in set (0.00 sec) mysql> select TABLE_SCHEMA,sum(DATA_LENGTH)/1024/1024 as data_length,sum(INDEX_LENGTH)/1024/1024 as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables group by TABLE_SCHEMA having TABLE_SCHEMA='MANUAL_056_taurus'; +-------------------+-------------+--------------+-------------+ | TABLE_SCHEMA | data_length | index_length | sum | +-------------------+-------------+--------------+-------------+ | MANUAL_056_taurus | 16.02088928 | 0.00000000 | 16.02088928 | +-------------------+-------------+--------------+-------------+ 1 row in set (0.00 sec)