Release 6249: Larger tmp_table_size and max_heap if tmp_on_data option enabled

  • Home / Changelog / Release 6249: Larger tmp_table_size and max_heap if tmp_on_data option enabled
  •  in Changelog
  • , by Rick

In this release we will increase the tmp_table_size and max_heap_table_size setting from the default 64MB value that is configured on Hypernode to 256MB if the previously existing configurable option mysql_tmp_on_data_enabled is enabled. The mysql_tmp_on_data_enabled setting that can be configured using the hypernode-api or using the hypernode-systemctl command-line tool enables users to make MySQL store temporary tables on the /data disk instead of on the root disk.

This can be convenient when the storage required for the temporary tables exceed that what is available on the partition that the /tmp directory is on. MySQL temp tables will be written to disk if they can no longer fit in memory. For performance, it is better to keep everything in RAM. To increase performance for shops that need large temporary tables, and for that reason have the mysql_tmp_on_data_enabled option toggled, we have now configured MySQL to allow keeping larger tmp_tables in memory.

Note that a larger tmp_table_size could already be configured from the shell of a running MySQL (without the need of a restart), but by enabling it using the hypernode-api setting you can ensure that it will persist between restarts. To configure the option manually during runtime you can run these MySQL commands:

SET GLOBAL tmp_table_size = 1024 * 1024 * 256; 
SET GLOBAL max_heap_table_size = 1024 * 1024 * 256;

To check what these settings are currently configured as you can run the following to show the active values in KB:


SELECT @@tmp_table_size;
SELECT @@max_heap_table_size;

To enable the mysql_tmp_on_data_enabled flag that now includes this tmp_table_size tweak run this on your Hypernode:

app@j6ysi3-vdloo-magweb-cmbl:~$ hypernode-systemctl settings mysql_tmp_on_data_enabled --value True
Operation was successful and is being processed. Please allow a few minutes for the settings to be applied. Run 'livelog' to see the progress.

Other changes:
As part of the up/down scaling process we will now longer check if the data of the shop will fit on the new instance if a volume swap can be performed instead of an rsync to transfer the data (the same disk will be used so it will fit by definition). This will slightly speed up migrations and it will decrease the performance impact the migration process will have just before the system goes live with the new node.