0

For testing, I almost rebuild the newly designed MySQL database every day recently, I also have a Php application based on that. For my understanding, some of system variables value has been accumulated in every rebuild, such as:

mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 14062 |
| Created_tmp_files       | 437   |
| Created_tmp_tables      | 20854 |
+-------------------------+-------+
3 rows in set (0.00 sec)

Created_tmp_disk_tables and Created_tmp_tables are growing constantly based on every rebuild. Surely there are some other variables doing same thing. I wonder how can we clean them safely in every rebuild, so we won't be cheated by these values. We will see the real value.

Please feel free to let me know if the question is not clear. Thanks.

after testing @dwjv's suggestion, doing 'flush status', got:

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)

mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 14062 |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 20856 |
+-------------------------+-------+
3 rows in set (0.00 sec)

The variable 'Created_tmp_files' was cleaned, but other two didn't change. 'Flush status' will only reset the session status, some, but not all the global status variables.

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Then I followd &Yak's suggestion 'service mysql restart', got:

mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 14062 |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 20857 |
+-------------------------+-------+
3 rows in set (0.00 sec)

Still same, no change.

user1342336
  • 967
  • 2
  • 16
  • 28

2 Answers2

2

All you need to do is restart the server.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Thanks @Yak, I did what you suggested, however nothing was changed. – user1342336 May 22 '13 at 14:34
  • This is strange. Probably a stupid question but there I go... are you sure you restarted the right server? That is to say, if you issue a `service mysql stop`, does `mysql` really fail to connect? – RandomSeed May 22 '13 at 15:04
  • I am sure I restarted the right server. I just have mysql 5.5 installed in my Ubuntu. – user1342336 May 22 '13 at 15:08
2
FLUSH STATUS;

This will flush many of the global variables.

dwjv
  • 1,227
  • 9
  • 15