Monday, July 7, 2014

MySQL status variables: Difference between Created_tmp_tables and Created_tmp_disk_tables

MySQL has two very important types of variables: -

  1. Global variables
  2. Status variables

For optimum performance, global variables should be tuned as per the application usage and available resources. It is recommended to tune (increase or decrease the value) one parameter (global variable) a time and then use the system with peak load and check the value of respective status variables. These status variables indicate positive and negative affect of tuned global variable.
Usually, status variables are counter variable since start of MySQL service.

There are two status variable - created_tmp_tables and created_tmp_disk tables.
These variables are related with  tmp_table_size and  max_heap_table_size
Since max_heap_table_size is specially for memory storage engine. If your database hasn't any table which storage engine type is not memory, we can leave it.
Created_tmp_tables is increased when server creates an internal temporary tables either in memory or in disk. Temporary tables created in memory if size is small and placed on disk when size increased. This size is determined by tmp_table_size and max_heap_table_size global variables.
Created_tmp_disk_tables is increased when server creates an internal temporary tables only on disk.
This is the reason we always find value of created_tmp_tables is always higher than created_tmp_disk_tables.

Created_tmp_tables = Created_tmp_disk_tables + Temporary tables open in memory

Please feel free to input your comments...

1 comment:

Thanks for your valuable comments.

Important queries based formulas to watch performance of a MySQL based application

(1) Session Vs global status variables value select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (selec...