Почему запрос вставки иногда занимает так много времени?

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

У меня есть таблица MyISAM: daniel_test_insert (эта таблица начинается полностью пустой):

create table if not exists daniel_test_insert ( 
    id int unsigned auto_increment not null, 
    value_str varchar(255) not null default '', 
    value_int int unsigned default 0 not null, 
    primary key (id) 
)

Я вставляю в него данные, и иногда запрос на вставку занимает > 2 секунды для запуска. В этой таблице нет прочтений - записывается в последовательном порядке только одна потоковая программа.

Я выполнил тот же запрос 100 000 раз, чтобы узнать, почему запрос иногда занимает много времени. Пока что это случайное явление.

Этот запрос, например, потребовался 4.194 секунды (очень долгое время для вставки):

Query: INSERT INTO daniel_test_insert SET value_int=12345, value_str='afjdaldjsf aljsdfl ajsdfljadfjalsdj fajd as f' - ran for 4.194 seconds
status               | duration | cpu_user  | cpu_system | context_voluntary | context_involuntary | page_faults_minor
starting             | 0.000042 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
checking permissions | 0.000024 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
Opening tables       | 0.000024 | 0.001000  | 0.000000   | 0                 | 0                   | 0                
System lock          | 0.000022 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
Table lock           | 0.000020 | 0.000000  | 0.000000   | 0                 | 0                   | 0                
init                 | 0.000029 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
update               | 4.067331 | 12.151152 | 5.298194   | 204894            | 18806               | 477995           
end                  | 0.000094 | 0.000000  | 0.000000   | 8                 | 0                   | 0                
query end            | 0.000033 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
freeing items        | 0.000030 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
closing tables       | 0.125736 | 0.278958  | 0.072989   | 4294              | 604                 | 2301             
logging slow query   | 0.000099 | 0.000000  | 0.000000   | 1                 | 0                   | 0                
logging slow query   | 0.000102 | 0.000000  | 0.000000   | 7                 | 0                   | 0                
cleaning up          | 0.000035 | 0.000000  | 0.000000   | 7                 | 0                   | 0

(Это сокращенная версия команды SHOW PROFILE, я выбросил столбцы, которые были равны нулю.)

Теперь обновление имеет невероятное количество контекстных переключателей и мелких ошибок страницы. Открытые_таблицы увеличиваются примерно на 1 в 10 секунд в этой базе данных (не заканчиваются из пространства table_cache)

Статистика:

MySQL 5.0.89
Оборудование: 32 гигабайта RAM/8 ядер @2,66 ГГц; raid 10 SCSI harddisks (SCSI II???)
У меня были запрошены жесткие диски и контроллер рейда: сообщений об ошибках не сообщается. Процессоры работают примерно на 50%.
iostat -x 5 (сообщает об утилизации жестких дисков менее 10%) верхний отчет нагрузки в среднем около 10 за 1 минуту (нормальный для нашей машины db)
В области подкачки используется 156k (32 гигабайта RAM)

Я затрудняюсь выяснить, что вызывает это отставание в производительности. Это НЕ происходит на наших низковольтных подчиненных устройствах, только на нашем высокопроизводительном сервере. Это также происходит с таблицами памяти и innodb. У кого-нибудь есть предложения? (Это производственная система, поэтому ничего экзотического!)

Ответ 1

Я заметил одно и то же явление в своих системах. Запросы, которые обычно занимают миллисекунду, внезапно занимают 1-2 секунды. Все мои дела просты, одиночные таблицы INSERT/UPDATE/REPLACE - не на всех SELECT. Очевидна нагрузка, блокировка или нарезка резьбы.

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

Также исключен

Загрузка сервера - отсутствие корреляции с высокой нагрузкой
Двигатель - происходит с InnoDB/MyISAM/Memory
MySQL Query Cache - происходит ли он вкл/выкл
Логарифмические вращения - отсутствие корреляции в событиях

Единственное другое наблюдение, которое у меня есть на данный момент, связано с тем, что я запускаю один и тот же db на нескольких машинах. У меня тяжелое приложение для чтения, поэтому я использую среду с репликацией - большая часть нагрузки находится на ведомых устройствах. Я заметил, что, хотя на мастере минимальная нагрузка, явление происходит там больше. Хотя я не вижу проблем с блокировкой, возможно, у Innodb/Mysql возникают проблемы с (thread) concurrency? Напомним, что обновления на ведомом будут однопоточными.

MySQL Verion 5.1.48

Обновление

Я думаю, что у меня есть причина для проблемы на моем деле. На некоторых моих серверах я заметил это явление больше, чем другие. Увидев, что было между разными серверами, и что-то изменилось, я привел к системную переменную MySQL innodb innodb_flush_log_at_trx_commit.

Я нашел документ немного неудобным для чтения, но innodb_flush_log_at_trx_commit может принимать значения 1,2,0:

Для 1 буфер журнала очищается до файл журнала для каждой фиксации и журнал файл сбрасывается на диск для каждой фиксации.
Для 2 буфера журнала очищается до файл журнала для каждой фиксации и журнал файл сбрасывается на диск примерно каждые 1-2 секунды.
Для 0 буфер журнала очищается до файл журнала каждую секунду, и журнал файл сбрасывается на диск каждую секунду.

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

Сказав это, я обнаружил, что серверы с innodb_flush_log_at_trx_commit=0 хуже работают (т.е. имеют в 10-100 раз больше "длинных обновлений" ), чем серверы с innodb_flush_log_at_trx_commit=2. Более того, вещи сразу же улучшились в плохих случаях, когда я переключил их на 2 (обратите внимание, что вы можете изменить его на лету).

Итак, мой вопрос в том, на что вы настроены? Обратите внимание, что я не обвиняю этот параметр, а скорее подчеркиваю, что этот контекст связан с этой проблемой.

Ответ 2

У меня была эта проблема с использованием таблиц INNODB. (и индексы INNODB еще медленнее переписывать, чем MYISAM)

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

Если вы используете таблицы MYISAM, я настоятельно рекомендую использовать

LOAD DATA INFILE 'file-on-disk' INTO TABLE `tablename`

команда; MYISAM сенсационно быстро работает с этим (даже с первичными ключами), и файл может быть отформатирован как csv, и вы можете указать имена столбцов (или вы можете поместить NULL в качестве значения для поля автоинкремента).

Просмотрите документ MYSQL здесь.

Ответ 3

Первый совет, который я вам давал, - отключить функцию autocommit и выполнить вручную.

LOCK TABLES a WRITE;
... DO INSERTS HERE
UNLOCK TABLES;

Это повышает производительность, поскольку буфер индекса сбрасывается на диск только один раз, после завершения всех инструкций INSERT. Как правило, существует столько флагов индексного буфера, сколько есть инструкций INSERT.

Но лучше всего это можно сделать, и если это возможно в вашем приложении, вы делаете объемную вставку с одним единственным выбором.

Это выполняется с помощью привязки векторов, и это самый быстрый способ, по которому вы можете пойти.

Instead
of:
"INSERT INTO tableName values()"
DO
"INSERT INTO tableName values(),(),(),().......(n) " ,

Но рассмотрите эту опцию только в том случае, если привязка параметра к вектору возможна с помощью используемого вами драйвера mysql.

В противном случае я бы склонялся к первой возможности и блокировал таблицу для каждых 1000 вставок. Не блокируйте его для вставок 100k, потому что вы получите переполнение буфера.

Ответ 4

Можно ли создать еще одну таблицу с 400 (не нулевыми) столбцами и повторить тест? Если число медленных вставок стало выше, это может означать, что MySQL тратит время на запись ваших записей. (Я не знаю, как это работает, но он может выделять больше блоков или перемещать что-то во избежание фрагментации.... действительно не знаю)

Ответ 5

Мы затронули точно такую же проблему и сообщили здесь: http://bugs.mysql.com/bug.php?id=62381

Мы используем 5.1.52 и пока не имеем решения. Возможно, нам придется отключить QC, чтобы избежать этого перфоманса.

Ответ 6

Прочитайте это на Myisam Performance: http://adminlinux.blogspot.com/2010/05/mysql-allocating-memory-for-caches.html

Искать:

'Размер блока ключа MyISAM Размер ключевого блока важен' (минус одиночные кавычки), это может быть то, что происходит. Я думаю, что они исправили некоторые из этих проблем с 5.1

Ответ 7

Вы можете проверить статистику на дисковой подсистеме? Является ли I/O насыщенным? Это звучит так, будто внутренняя работа с БД происходит при промывке материала на диск/журнал.

Ответ 8

Чтобы проверить, плохо ли работает ваш диск, и если вы находитесь в Windows, вы можете создать пакетный cmd файл, который создает 10 000 файлов:

@echo OFF
FOR /L %%G IN (1, 1, 10000) DO TIME /T > out%%G.txt

сохранить его в временном каталоге, например test.cmd

Включить расширения команд, запускающие CMD с параметром /E: ON

CMD.exe /E:ON

Затем запустите свою партию и посмотрите, отличается ли время между первым и последним файлом в секундах или минутах.

В Unix/Linux вы можете написать оболочку similare script.

Ответ 9

Во всяком случае, есть ли SSD-диск на сервере? Некоторые накопители SSD страдают от "studder", что может вызвать ваш симптом.

В любом случае, я попытался бы выяснить, происходит ли задержка в MySQL или в дисковой подсистеме.

Какая ОС является вашим сервером и какая файловая система является данными MySQL?

Ответ 10

Мы обновили до MySQL 5.1, и во время этого события кеш запросов стал проблемой с большим количеством "бесплатных элементов"? состояния потоков. Затем мы удалили кеш запросов.

Либо обновление до MySQL 5.1, либо удаление кеша запросов разрешили эту проблему.

FYI, будущим читателям.

-daniel

Ответ 11

если вы используете множественную вставку при одном использовании цикла for, то, пожалуйста, сделайте паузу после каждого цикла, используя функцию PHP sleep ( "время в секундах" ).