У меня есть следующая таблица поиска:
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;
У меня есть таблица widgetuser_tmp с той же структурой, но без ключа, и я заполняю таблицу widgetuser этими данными (4 миллиона строк):
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)
Пока пишет, идет сразу на RAID-1 со скоростью 15 МБ/с, disk util ‹50% и мы не видим чтений, так как я заполнил кеш диска исходной таблицей:
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
Я вставляю следующие 1 млн строк, все в порядке, и wMB/s возвращается к 0 сразу после сброса:
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)
Но когда я делаю пакет 7mio, результат все равно выглядит так же, но в iostat -mdx sda sdb 5
внезапно у нас есть 100% util в течение 30 секунд:
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
Файлы данных после сброса не трогаются:
-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
А также статус таблицы выглядит нормально:
+----------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-------------------+---------+
| 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 | |
+----------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-------------------+---------+
И когда я продолжаю (поскольку у нас 100% загрузка диска), становится все хуже очень быстро:
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)
Первичный ключ проверяется, чтобы увидеть, является ли новая запись уникальной или нет. Как только ключ не помещается в память (key_buffer_size=512MB = около 8Mio записей), ему необходимо извлечь недостающие части ключа с диска (-cache) для проверки. Поэтому мы должны увидеть больше чтений и более медленное время вставки — мы не видим более медленных чтений, поскольку ключ буферизуется в дисковом кеше. Но мой вопрос: кто вдруг так много пишет и где и почему и как мне это исправить? Любые идеи приветствуются!
Дополнительные идеи и идеи:
- поскольку за готовым оператором следуют случайные записи со скоростью 1 МБ/с, уникальная проверка уже пройдена.
- это программный рейд-1 с включенным ahci, диски свободны на 93% и имеют скорость около 80wMB/s
- машина имеет 8 ГБ оперативной памяти, 5 ГБ кэш-памяти, 600 МБ занято MySQL, 1,7 ГБ свободно
- MySQL 5.1.31-1ubuntu2-журнал
- delay_key_write не меняет это поведение
- myisam_sort_buffer_size = 2 ГБ (здесь не используется?)
- key_buffer_size = 512 МБ
- bin_log выключен
- Linux 2.6.28-15-server #52-Ubuntu SMP Ср, 9 сентября, 11:34:09 UTC 2009 x86_64 GNU/Linux