Watching for Disk Based Tables being created

I didn’t know you could actually do this before addressing this problem in a benchmark using 5.0.36.

MySQL allows you to update two tables with a single UPDATE statement. I knew you could reference two tables in an UPDATE statement but not update both. However when working with a client after benchmarking I observed a large number of Created_tmp_disk_tables via SHOW GLOBAL STATUS and found that this query was the offending query only for certain circumstances. Understanding took a little longer.

schema.sql

DROP TABLE IF EXISTS a;
CREATE TABLE a(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL)
ENGINE=INNODB;
DROP TABLE IF EXISTS b;
CREATE TABLE b(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL,
t   TEXT NOT NULL)
ENGINE=INNODB;

data.sql

INSERT INTO a VALUES(1,1,'a');
INSERT INTO a VALUES(2,2,'b');
INSERT INTO a VALUES(3,3,'c');
INSERT INTO a VALUES(4,4,'d');
INSERT INTO b VALUES(1,1,'a',REPEAT('The quick brown fox jumped over the lazy dog',100));
INSERT INTO b VALUES(2,2,'b',REPEAT('The quick brown fox jumped over the lazy dog',200));
INSERT INTO b VALUES(3,3,'c',REPEAT('The quick brown fox jumped over the lazy dog',300));
INSERT INTO b VALUES(4,4,'d',REPEAT('The quick brown fox jumped over the lazy dog',400));

test.sql

SHOW SESSION STATUS LIKE '%tmp%tables';
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20 WHERE a.id = b.id and a.id=1;
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20,b.t=REPEAT('b',5000) WHERE a.id = b.id and a.id=3;
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE b SET t = REPEAT('a',5000) WHERE b.id=2;
SHOW SESSION STATUS LIKE '%tmp%tables';

Output

The following output shows the following.

  1. Impact of a SHOW SESSION STATUS increments Created_tmp_tables
  2. A two table update increments Created_tmp_tables
  3. A two table update including a text field increments Created_tmp_tables and Created_tmp_disk_tables
  4. A single table update with the text field increments Created_tmp_tables

So the culprit was the combination of a two table update and a TEXT field.

mysql> source test.sql
--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 4     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20 WHERE a.id = b.id and a.id=1
--------------

Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 6     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20,b.t=REPEAT('b',5000) WHERE a.id = b.id and a.id=3
--------------

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 8     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
UPDATE b SET t = REPEAT('a',5000) WHERE b.id=2
--------------

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 9     |
+-------------------------+-------+
2 rows in set (0.00 sec)
Tagged with: Databases MySQL

Related Posts

More CPUs or Newer CPUs

More CPUs or Newer CPUs

In a CPU-bound database workload, regardless of price, would you scale-up or scale-new? What if price was the driving factor, would you scale-up or scale-new? I am using as a baseline the first available AWS Graviton2 processor for RDS (r6g).

Read more
An Interesting Artifact with AWS RDS Aurora Storage

An Interesting Artifact with AWS RDS Aurora Storage

As part of using public datasets with my own Benchmarking Suite I wanted upsize a dataset for larger volume testing. I have always used the INFORMATION_SCHEMA.TABLES data_length and index_length columns as a sufficiently accurate measurement for actual disk space used.

Read more
How long does it take the ReadySet cache to warm up?

How long does it take the ReadySet cache to warm up?

During my setup of benchmarking I run a quick test-sysbench script to ensure my configuration is right before running an hour+ duration test. When pointing to a Readyset cache where I have cached the 5 queries used in the sysbench test, but I have not run any execution of the SQL, throughput went up 10x in 5 seconds.

Read more