перегон данных между таблицами

[soulhunter]

перегон данных между таблицами

Здравствуйте.

Ситуация такая:
БД - MySQL
Таблица links - 60 000 000 записей, MyISAM
Таблица temp_8403 - 15 000 000 записей, MyISAM

Делается вот такой запрос, перегоняются данные, часть инсертится часть апдейтится при встрече совпадающего по уникальному ключу:

INSERT INTO links (server_id, hash, path, size, extension, last_online, found)
SELECT 26, hash, path, size, extension, '2009-07-05 23:00:00', found
FROM temp_8403
ON DUPLICATE KEY UPDATE found = VALUES(found), last_online = '2009-07-05 23:00:00', path = VALUES(path)

Ранее (когда записей в таблице links было меньше) запрос нормально выполнялся в пределах 7 минут, с при достижении данных цифр в таблице links - запрос перестал выполняться вообще, то есть он тупо виснет навсегда и все.

Естественно, перед запросом делаю:

LOCK TABLES links_dc WRITE, temp_8403 READ
ALTER TABLE links_dc DISABLE KEYS

Настройки мускула:

#
# MyISAM specific options
#
key_buffer_size = 7G
read_buffer_size = 8M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_recover

Подскажите, в чем может быть причина тупого повисания данного запроса? на жестком диске места естественно придостаточно, оперативки 16гб

Хотя нет, поправочка. Все же запрос не повисает совсем, а просто выполняется очень долго. Ранее, как уже писал, он выполнялся порядка 8-10 минут, теперь от 45 минут в лучшем случае и выше до 2часа+. Это произошло после увеличения объема данных до текущих цифр, в чем могло появиться узкое место, что запрос стал резко настолько дольше идти?

 

[DiMA]

уничтожаем индексы
используем mysqldump + LOAD DATA FILE

 

[Alexandre]

Все же запрос не повисает совсем, а просто выполняется очень долго. Ранее, как уже писал, он выполнялся порядка 8-10 минут, теперь от 45 минут в лучшем случае и выше до 2часа+.

SHOW PROCESSLIST;

предыдущий пост наиболее оптимально,

можно посоветовать группировать инсерты по 100-1000 данных в один оператор: INSERT () ... VALUES (....),(....)... (...); [много-много раз]

 

[findnext]

не помню точно, но мне кажется что в + ко всему будет быстрее если отключить SQL_LOG_BIN

 

[soulhunter]

DiMA

Это уже альтернативный вариант решения задачи, я бы сначала хотел попробовать разобраться где же узкое место именно в этом запросе, а если решение не найдем или окажется что просто мускул фиговенько начинает работать, например, с большим блоком индексов (для поиска существования значения по уникальному ключу), то тогда конечно буду использовать альтернативные варианты.

Alexandre

Зачем SHOW PROCESSLIST, что вы хотите там увидеть, время выполнения? )

findnext

Да, согласно документации мускула, это дает увеличение производительности на 10%. Вообще бинлог мне может быть нужен в других местах, поэтому отключать его совсем не буду, но можно отключить его только для этого запроса, однако для этого нужны права рута, а этого очень не хотелось бы давать этому скрипту. Но посмотрим стоит ли овчинка выделки, смотря какой реально будет прирост производительности.

 

[Gas]

soulhunter
попробуй заменить ON DUPLICATE KEY UPDATE на REPLACE (у тебя же старые данные никак не используются), теоритически должно быть быстрее за счёт того, что при update считывается строка со старыми данными, при replace этого не происходит и возможна экономия кучи дисковых операций если много данных нет в кеше OC.
Ещё интересны show create table links и show table status like 'links'.

И я ещё не совсем понял, при росте количества записей время запроса увеличивается не линейно или линейно, но оно уже не устраивает? Если первое, то можно попробовать разбить один большой инсерт на несколько с limit'ами чтоб добиться линейного времени выполнения.

Зачем SHOW PROCESSLIST, что вы хотите там увидеть, время выполнения? )

не время выполнения, а выполняется ли ещё запрос или нет

 

[Alexandre]

Зачем SHOW PROCESSLIST, что вы хотите там увидеть, время выполнения?

лично мне от него ничего не нужно, это я подсказываю как определить висит сервер или что-то на нем выполняется исходя из нижесказанного:

с при достижении данных цифр в таблице links - запрос перестал выполняться вообще, то есть он тупо виснет навсегда и все.

-~{}~ 07.07.09 00:20:

а если решение не найдем или окажется что просто мускул фиговенько начинает работать, например, с большим блоком индексов (для поиска существования значения по уникальному ключу), то тогда конечно буду использовать альтернативные варианты.

ключи надо отключать или напрочь убивать, а потом вновь ставить. Вставка - это самая тяжелая операция и при выполнении каждого инсерта происходит переиндексация, чем инсертов больше, тем больше времени уходит на переиндексацию.

 

[Gas]

soulhunter
Нашёл доп.инфу тут - http://sql.ru/forum/actualthread.aspx?tid=677466, там очень шарящие ребята подключились.
Ещё свои 5 копеек - после alter table disable keys делать load index `links_dc` и поменять индекс sync(server_id, hash) на sync(hash, server_id), для поиска по server_id всё равно есть индекс `process_outdated`, а при смене порядка полей повысится селективность, что на таком количестве записей может заметно сказаться.

 

[soulhunter]

Всем спасибо, проблема решилась. Действительно начались проблемы с нехваткой key_buffer.
Помогли две вещи:
1. Перед запросом выполняю LOAD INDEX INTO CACHE links_dc
2. Заменил INSERT ... ON DUPLICATE KEY UPDATE на REPLACE, который, как показала практика, работает в данном случае на 40% быстрее.

Особенно спасибо Gas!

 

[Gorynych]

+ я бы добавил в INESERT понижающий приоритет, но это на любителя

 

[Активист]

Gorynych
?

 

[Gas]

soulhunter
Спасибо что отписал результаты, а то теория теорией, но в жизни всё бывает иначе.

 

[Gorynych]

Активист INSERT LOW_PRIORITY ... и только