I run pt-variable-advisor
and got a note about different settings for max_heap_table_size
and tmp_table_size
.
With a web search I found only old articles (around 2007).
pt-variable-advisor h=localhost,u=root,p=Quule0juqu7aifohvo2Ahratit --socket /var/vcap/sys/run/mysql/mysqld.sock
(...)
# NOTE tmp_table_size: The effective minimum size of in-memory implicit temporary tables used internally during query execution is min(tmp_table_size, max_heap_table_size), so max_heap_table_size should be at least as large as tmp_table_size.
(...)
Our configuration
max_heap_table_size = 16777216
tmp_table_size = 33554432
We didn't modify the defaults from cf-mysql-release. I saw that MariaDB KB recommends other default values.
cf_mysql.mysql.tmp_table_size:
description: 'The maximum size (in bytes) of internal in-memory temporary tables'
default: 33554432
cf_mysql.mysql.max_heap_table_size:
description: 'The maximum size (in rows) to which user-created MEMORY tables are permitted to grow'
default: 16777216
I found also Optimize MySQL tmp_table_size and checked our values and the configuration:
MariaDB [(none)]> show global status like 'created_tmp_disk_tables';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Created_tmp_disk_tables | 12727901 |
+-------------------------+----------+
1 row in set (0.01 sec)
MariaDB [(none)]> show global status like 'created_tmp_tables';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| Created_tmp_tables | 115714303 |
+--------------------+-----------+
1 row in set (0.01 sec)
MariaDB [(none)]> select (12727901*100)/(115714303 + 12727901) as "Created disk tmp tables ratio" from dual;
+-------------------------------+
| Created disk tmp tables ratio |
+-------------------------------+
| 9.9094 |
+-------------------------------+
1 row in set (0.00 sec)
Is something wrong with our (default) configuration? We don't know our workload. We run about 500 small databases for small web apps with different usage pattern.