MySQL MyISAM disk-bound scaling issue / drive cache

Question

I have the following lookup-table:

CREATE TABLE `widgetuser` (
 `widgetuserid` char(40) NOT NULL,
 `userid` int(10) unsigned NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`widgetuserid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1;

I have a widgetuser_tmp Table with the same structure but no key and I fill the widgetuser table with this data (4mio rows):

mysql> insert into widgetuser select * from widgetuser_tmp limit 0,4000000;flush tables;
Query OK, 4000000 rows affected (33.14 sec)
Records: 4000000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.91 sec)

While it is writing, it goes directly to RAID-1 with 15MB/s, disk util <50% and we see no reads, since I filled the disk cache with the source table:

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00  3839.20    0.00   52.40     0.00    15.20   594.20    12.46  237.75   5.57  29.20
sdb               0.00  3839.00    0.00   52.60     0.00    15.20   591.94    14.50  275.59   7.19  37.80

I insert the next 1 Mio rows, it's all fine and the wMB/s goes back to 0 right after the flush:

mysql> insert into widgetuser select * from widgetuser_tmp limit 4000000,1000000;flush tables;
Query OK, 1000000 rows affected (10.18 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.87 sec)

mysql> insert into widgetuser select * from widgetuser_tmp limit 5000000,1000000;flush tables;
Query OK, 1000000 rows affected (10.21 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (1.02 sec)

mysql> insert into widgetuser select * from widgetuser_tmp limit 6000000,1000000;flush tables;
Query OK, 1000000 rows affected (10.67 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (1.17 sec)

But when I do the 7mio batch, the result still looks the same, but in the iostat -mdx sda sdb 5 suddenly we have 100% util for 30 seconds:

mysql> insert into widgetuser select * from widgetuser_tmp limit 7000000,1000000;flush tables;
Query OK, 1000000 rows affected (10.73 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (1.21 sec)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    88.60    0.00  295.60     0.00     1.52    10.53   130.60  435.93   3.38 100.00
sdb               0.00    89.20    0.00  300.80     0.00     1.57    10.68   143.99  483.97   3.32 100.00

The data-files are not touched after the flush:

-rw-rw---- 1 mysql mysql 1032000000 2009-10-30 12:10 widgetuser.MYD
-rw-rw---- 1 mysql mysql  522777600 2009-10-30 12:11 widgetuser.MYI

And also the table status seams normal:

+----------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-------------------+---------+
| Name           | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options    | Comment |
+----------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-------------------+---------+
| widgetuser     | MyISAM |      10 | Fixed      |  8000000 |            129 |  1032000000 | 36310271995674623 |    522777600 |         0 |           NULL | 2009-10-30 11:59:41 | 2009-10-30 12:10:59 | NULL       | utf8_general_ci |     NULL | delay_key_write=1 |         |
+----------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-------------------+---------+

And when I continue (since we have 100% drive utilization), it get's worse very fast:

mysql> insert into widgetuser select * from widgetuser_tmp limit 9000000,1000000;flush tables;
Query OK, 1000000 rows affected (31.93 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (2.34 sec)

mysql> insert into widgetuser select * from widgetuser_tmp limit 10000000,1000000;flush tables;
Query OK, 1000000 rows affected (2 min 39.72 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (7.82 sec)

The primary key is checked to see whether the new entry is unique or not. As soon as the key does not fit into memory (key_buffer_size=512MB = ca. 8Mio entries), it needs to fetch the missing key parts from the drive(-cache) for checking it. Therefore we should see more reads and slower insert times - we don't see the slower reads since the key is buffered in disk cache. But my question: who is writing suddenly so much and where and why and how can I fix this? Any ideas are appreciated!

Futher ideas and insights:

• since the 1MB/s random writes follow the finished statement, the unique validation is already passed
• it is a software raid-1 with ahci on, disks are 93% free and capable of about 80wMB/s
• the machine has 8GB ram, 5GB cache, 600MB taken by MySQL, 1,7GB free
• MySQL 5.1.31-1ubuntu2-log
• the delay_key_write does not change this behavior
• myisam_sort_buffer_size = 2 GB (not used here, though?)
• key_buffer_size = 512 MB
• bin_log is off
• Linux 2.6.28-15-server #52-Ubuntu SMP Wed Sep 9 11:34:09 UTC 2009 x86_64 GNU/Linux

Answer 1

It's not entirely clear from your question what behaviour you're expecting, or getting. Here are some things you might not know

• FLUSH TABLES blows away the MyISAM key cache - it doesn't just write dirty blocks, it also discards clean ones so every index block must be fetched again to be modified
• MyISAM uses a block size of 1k by default which is probably smaller than your filesystem blocks; this can create performance problems
• If you intend any kind of durability (which you presumably don't, because you're using MyISAM), then you should use hardware raid with a battery-backed cache in the controller.

My guess is that either the index no longer fits in the key buffer, or that it's having to do a lot more writes, which trigger reads because they're unbuffered writes off the block-size boundaries.

Try changing myisam_block_size to 4k or higher and rebuild the table (this is a my.cnf-only option which only takes effect on new tables after a restart).

You can examine the block size on a table with myisamchk -dv