Когда медленный запрос MySQL по данному соединению влияет на другие соединения?

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

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

Если одно соединение блокирует некоторые элементы, я понимаю, что это задержит запросы, запускающие другие соединения, которые нуждаются в этих элементах. Например:

SELECT FOR UPDATE

заблокирует то, что вы выбираете.

Что происходит, когда вы делаете что-то простое:

SELECT COUNT(*) FROM myTable

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

Что делать, если вы выбрали большой объем данных с помощью SELECT и JOIN, например:

SELECT * FROM myTable1 JOIN myTable2 ON myTable1.id = myTable2.id;

имеет блокировку соединения для других запросов?

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

Спасибо

Ответ 1

Существуют разные углы:

Блокировка строк: это не должно происходить, если вы настраиваете свою архитектуру, поэтому вам следует забыть об этом.
Реальные проблемы с производительностью и узкое место. В нашем случае, побочные эффекты.

Об этом втором пункте проблема в основном разделена на 3 области:

Диск читает
Использование памяти (буфер)
Использование ЦП.

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

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

О использовании процессора: в основном процессор будет занят, когда он

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

Итак, теперь, когда нужно знать, когда есть побочный эффект? Профилирование вашего оборудования... Как профайл?

Абсолютное профилирование: используйте SHOW INNODB STATUS или SHOW PROFILE, чтобы получить полезную информацию о главных сторожевых таймерах mysql, процессорах и памяти.
относительное профилирование: используйте свой любимый профилировщик ОС. Например, под окнами xp вы можете использовать отличный perfmon.exe и наблюдать за PRIVATE BYTES и VIRTUAL BYTES процесса mysql. Я говорю "относительный", потому что, если запрос занимает много времени на вашем компьютере, это может быть не в системе НАСА...

Надеюсь, это поможет, привет.

Ответ 2

Чтение запросов зависит только от уровней изоляции других запросов. Они сами не блокируют таблицу когда-либо.

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

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

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

Ответ 3

Это очень общий вопрос, поэтому дать точный ответ сложно.

Вы можете думать о базе данных как о пуле общих ресурсов; особенно потому, что базовое оборудование, на котором работает ваша база данных, имеет физические ограничения. Чаще всего причина, по которой вы видите что-то вроде запроса выбора, вызывающего влияние производительности на другие запросы, потому что все они конкурируют за использование тех основных физических ресурсов, как Disk IO или RAM-доступ или процессорное время, и их недостаточно.

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

Например, в ваших примерах выбора могут быть следующие переменные: нужны ли данные, которые требуется запросу в ОЗУ? Может ли он эффективно искать строки по индексу? Если это нужно сделать IO, сколько других запросов запрашивает чтение данных с диска? Вы используете вторичный индекс и должны делать несколько чтений? Является ли база данных делать чтение вперед для буферизации других страниц? Является ли запрос вызовом последовательного или случайного io? Имеются ли какие-либо обновления, блокирующие данные? Сколько читаемых IO может поддерживать физическое оборудование?

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

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

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

Итак, в вашем случае я бы сказал, что бесконечно более полезно сосредоточиться на просто оптимизации ваших запросов по отдельности. Чем быстрее они выполняются, тем меньше ресурсов они, вероятно, используют, и чем меньше изменений они будут влиять на других. Затем вы научитесь анализировать систему. Просто взгляните на одно, что медленно, и спросите: "Почему это медленно?" Затем исправьте его. Это процесс оптимизации.

Однако в первом случае вы написали с помощью SELECT... FOR UPDATE явные блокировки могут и будут иметь большие проблемы с производительностью. Будьте осторожны с ними.