GA

2020/06/26

最近のMySQLにテンポラリーなファイル/ディレクトリを指定するオプションが多い気がする件

TL;DR


mysql80 9> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

mysql80 9> SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+---------------------------------+-----------------------+
| variable_name                   | variable_value        |
+---------------------------------+-----------------------+
| avoid_temporal_upgrade          | OFF                   |
| default_tmp_storage_engine      | InnoDB                |
| innodb_temp_data_file_path      | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir     | ./#innodb_temp/       |
| innodb_tmpdir                   |                       |
| internal_tmp_mem_storage_engine | TempTable             |
| show_old_temporals              | OFF                   |
| slave_load_tmpdir               | /tmp                  |
| temptable_max_ram               | 1073741824            |
| temptable_use_mmap              | ON                    |
| tmp_table_size                  | 16777216              |
| tmpdir                          | /tmp                  |
+---------------------------------+-----------------------+
12 rows in set (0.00 sec)
mysql57 6> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)

mysql57 6> SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+----------------------------------+-----------------------+
| variable_name                    | variable_value        |
+----------------------------------+-----------------------+
| avoid_temporal_upgrade           | OFF                   |
| default_tmp_storage_engine       | InnoDB                |
| innodb_temp_data_file_path       | ibtmp1:12M:autoextend |
| innodb_tmpdir                    |                       |
| internal_tmp_disk_storage_engine | InnoDB                |
| max_tmp_tables                   | 32                    |
| show_old_temporals               | OFF                   |
| slave_load_tmpdir                | /tmp                  |
| tmp_table_size                   | 16777216              |
| tmpdir                           | /tmp                  |
+----------------------------------+-----------------------+
10 rows in set (0.00 sec)
  • 5.6は performance_schema ではなくて information_schema
mysql56> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.6.48-log |
+------------+
1 row in set (0.00 sec)

mysql56> SELECT variable_name, variable_value FROM information_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+----------------------------+----------------+
| variable_name              | variable_value |
+----------------------------+----------------+
| INNODB_TMPDIR              |                |
| SHOW_OLD_TEMPORALS         | OFF            |
| TMPDIR                     | /tmp           |
| MAX_TMP_TABLES             | 32             |
| DEFAULT_TMP_STORAGE_ENGINE | InnoDB         |
| AVOID_TEMPORAL_UPGRADE     | OFF            |
| TMP_TABLE_SIZE             | 16777216       |
| SLAVE_LOAD_TMPDIR          | /tmp           |
+----------------------------+----------------+
8 rows in set (0.00 sec)

0 件のコメント :

コメントを投稿