hello everyone,
Today, I tried to reset the values of max_heap_table_size and tmp_table_size for a bigger size but ran into some wierd thing.
my env: Centos 6.5_64, Mysql 5.6.26_64
Here is my step:
mysql> show variables like '%heap%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 83886080 | <---Original size
mysql> set global max_heap_table_size=88886080;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect max_heap_table_size value: '88886080' |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%max_heap_table_size%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 88885248 | <---new value
+---------------------+----------+
1 row in set (0.01 sec)
===>so, what's the rule to truncate the value?
My findings:
1.
mysql> select * from GLOBAL_VARIABLES where VARIABLE_NAME like '%page%';
+--------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------------------+----------------+
.
.
| INNODB_PAGE_SIZE | 16384 |
Thought it should be times of innodb_page_size but 88885248/16384=5425.125 ??? Why?
Here is the doc for the parameters:
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html
Permitted Values (32-bit platforms) Type integer
Default 16777216
Min Value 16384
Max Value 4294967295 <----2^32=4294967296 -1?? Why 1 byte reduced?
Permitted Values (64-bit platforms) Type integer
Default 16777216
Min Value 16384
Max Value 1844674407370954752 <---- 2^64=18446744073709551616? Not sure this value. Not on the same level
1844674407370954752
18446744073709551616
For tmp_table_size,
mysql> show global variables like '%table_size%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 88885248 |
| tmp_table_size | 88886080 |
+---------------------+----------+
2 rows in set (0.01 sec)
mysql> set global tmp_table_size=90000000;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%table_size%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 88885248 |
| tmp_table_size | 90000000 |
+---------------------+----------+
2 rows in set (0.00 sec)
--->hm, rule changed for tmp_table_size??
Anyone know this please help.
Thanks in advance!
Ivan