Description:
I've created "nz" table and populated it with 10 rows, and now running set of queries to
"multiply" it content.
As you see it scales well (time doubles for double amount of rows) until some point in time,
when it blows up 20 times instead of 2 times.
As the status shows it exactly corresponds to temporary table being created on disk:
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 15 |
Temporary table on disk should cause slow down but it should not be that large,
as we already insert rows in MyISAM table on disk. Temporary MyISAM table with same
structure should make things about 2 times slow compared to heap table not 10 times.
strace reveals the following:
pwrite(21, "\3751 "..., 201, 6100953) = 201
pwrite(21, "\3751 "..., 201, 6101154) = 201
pwrite(21, "\3751 "..., 201, 6101355) = 201
pwrite(21, "\3751 "..., 201, 6101556) = 201
pwrite(21, "\3751 "..., 201, 6101757) = 201
pwrite(21, "\3751 "..., 201, 6101958) = 201
pwrite(21, "\3751 "..., 201, 6102159) = 201
pwrite(21, "\3751 "..., 201, 6102360) = 201
pwrite(21, "\3751 "..., 201, 6102561) = 201
pwrite(21, "\3751 "..., 201, 6102762) = 201
pwrite(21, "\3751 "..., 201, 6102963) = 201
pwrite(21, "\3751 "..., 201, 6103164) = 201
pwrite(21, "\3751 "..., 201, 6103365) = 201
pwrite(21, "\3751 "..., 201, 6103566) = 201
pwrite(21, "\3751 "..., 201, 6103767) = 201
pwrite(21, "\3751 "..., 201, 6103968) = 201
pwrite(21, "\3751 "..., 201, 6104169) = 201
pwrite(21, "\3751 "..., 201, 6104370) = 201
pwrite(21, "\3751 "..., 201, 6104571) = 201
pwrite(21, "\3751 "..., 201, 6104772) = 201
pwrite(21, "\3751 "..., 201, 6104973) = 201
pwrite(21, "\3751 "..., 201, 6105174) = 201
pwrite(21, "\3751 "..., 201, 6105375) = 201
pwrite(21, "\3751 "..., 201, 6105576) = 201
pwrite(21, "\3751 "..., 201, 6105777) = 201
So the problem seems to be temporary table is not getting any write buffering when
it should perhaps as normal table has it.
mysql> show create table nz;
+-------+----------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------+
| nz | CREATE TABLE `nz` (
`x` char(200) default NULL
) TYPE=MyISAM |
+-------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into nz select * from nz;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 40 rows affected (0.01 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 80 rows affected (0.01 sec)
Records: 80 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 160 rows affected (0.01 sec)
Records: 160 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 320 rows affected (0.02 sec)
Records: 320 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 640 rows affected (0.02 sec)
Records: 640 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 1280 rows affected (0.05 sec)
Records: 1280 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 2560 rows affected (0.04 sec)
Records: 2560 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 5120 rows affected (0.06 sec)
Records: 5120 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 10240 rows affected (0.10 sec)
Records: 10240 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 20480 rows affected (0.19 sec)
Records: 20480 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 40960 rows affected (0.38 sec)
Records: 40960 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 81920 rows affected (0.66 sec)
Records: 81920 Duplicates: 0 Warnings: 0
mysql> insert into nz select * from nz;
Query OK, 163840 rows affected (11.37 sec)
Records: 163840 Duplicates: 0 Warnings: 0
How to repeat:
See above.
Suggested fix:
Enable buffering for temporary MyISAM table.
