Проблемы с производительностью процедур в MySQL

Обсуждение возникших проблем, предложений и ошибок UniDAC компонентов
Закрыто
JayDi
Сообщения: 61
Зарегистрирован: Пт 28 ноя 2008 18:29
Откуда: Россия, г.Сызрань
Контактная информация:

Проблемы с производительностью процедур в MySQL

Сообщение JayDi » Вс 16 окт 2011 09:29

Первоначально данное обсуждение шло через форму обратной связи с сайта. Думаю, ему самое место в этом новом разделе.

JayDi, 09.10.2011:
При работе с процедурами и функциями сервера MySQL имеются существенные проблемы с производительностью. А именно -- с выборкой информации о них (из таблицы mysql.proc).

Из-за дополнительного условия LOWER(db) = xxx не используются стандартный индекс, и серверу приходится обходить всю таблицу целиком. Это оказывает заметное влияние и нагрузку на сервер при большой количестве используемых процедур и функций.

План запроса для вашей версии:

Изображение

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

Изображение

Dmitry, Devart Team (Ticket ID: 33169), 14.10.2011:
Мы не можем убрать условие LOWER(db), так как MySQL сервера на различных операционных системах по разному реагируют на чувствительность к регистру имени базы данных. В Windows OC имена не чувствительны к регистру, в то время как в Linux ОС - чувствительны.
Также пользователь может выполнять хранимую процедуру из другой базы данных, и в таком случае получать оригинальное имя для каждой базы только уменьшит производительность.

JayDi, 14.10.2011:
Есть множество путей решения данной проблемы. Например, запрашивать текст процедуры в 2 подхода. При первом подходе ищем процедуру с оригинальным именем базы и с использованием индекса. А если не находим -- делаем вторую попытку с измененным именем базы.

Еще раз обращаю внимание на катастрофическую скорость работы данного кода -- производительность падает в десятки и сотни раз!

Вот результаты профилировщика производительности, который показывает сколько времени потратилось на выполнение той или иной операции (версия MySQL 5.0.67, в базах 1500+ процедур):

1. Подготовка занимает в 28 раз больше времени, чем сам запрос (получение множества значений):

Изображение

2. Подготовка занимает в 115 раз больше времени, чем сам запрос (получение одного значения):

Изображение


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

AndreyZ
Devart Team
Сообщения: 328
Зарегистрирован: Чт 08 сен 2011 13:18

Сообщение AndreyZ » Вт 25 окт 2011 13:54

Спасибо за информацию. Мы устранили данную проблему с производительностью. Данное исправление будет включено в следующий билд UniDAC.

JayDi
Сообщения: 61
Зарегистрирован: Пт 28 ноя 2008 18:29
Откуда: Россия, г.Сызрань
Контактная информация:

Re: Проблемы с производительностью процедур в MySQL

Сообщение JayDi » Чт 09 окт 2014 11:18

Грусть-печаль. В новых версиях UniDAC (возможно, начиная с пятой версии) "системные" запросы для получения информации о процедурах изменили с таблицы mysql.proc на INFORMATION_SCHEMA.ROUTINES -- которая вообще не является таблицей и не имеет никаких индексов.

В результате производительность запросов с вызовом процедур опять катастрофически упала (особенно заметно, если на сервере куча баз и процедур). Падение производительности -- в десятки раз (см. первый пост со скриншотами).

Посмотрел код в MyClassesUni.TMySQLCommand.GetSPParams, там стоит интересное условие вида:

Код: Выделить всё

  if ((FConnection.FServerPrimaryVer = 5) and (FConnection.FServerMinorVer = 0) and
    (FConnection.FServerReleaseVer < 4)) or FConnection.FEmbedded
  then
    _GetSPParams(IsFunc, ParamList, ReturnParam)
  else
    with GetParamBlock(GetCreateSQL) do begin
      ParamList := Params;
      ReturnParam := Returns;
    end;
Вот и получается, что _GetSPParams -- нормальный запрос к mysql.proc не вызывается. Зато запрашивается GetCreateSQL к проблемной INFORMATION_SCHEMA.

P.S. Я понимаю, что к INFORMATION_SCHEMA доступ есть по умолчанию у всех, а к mysql.proc надо отдельно настраивать. Но может все же можно добавить хотя бы дополнительную опцию типа "fast_routine_prepare" или т.п. аналог -- который бы включал старое поведение для сервера выше 5.0.4 версии.

ViktorV
Devart Team
Сообщения: 212
Зарегистрирован: Чт 31 июл 2014 09:52

Re: Проблемы с производительностью процедур в MySQL

Сообщение ViktorV » Пт 10 окт 2014 09:32

Спасибо за информацию.
Мы будем исследовать возможность использования таблицы mysql.proc вместо INFORMATION_SCHEMA для получения информации о процедурах.

Dimon
Сообщения: 6
Зарегистрирован: Ср 17 дек 2014 08:13

Re: Проблемы с производительностью процедур в MySQL

Сообщение Dimon » Вт 06 янв 2015 14:43

Мы исследовали производительность доступа к таблицам mysql.proc и INFORMATION_SCHEMA.ROUTINES и не обнаружили значительной разницы во времени доступа даже для серверов с большим количеством баз и процедур. К тому же за более чем 7 лет использования данной функциональности вы первый пользователь, у кого воспроизводится описанная вами проблема.
Пожалуйста, укажите, какое конкретно время занимает доступ на вашем сервере к таблице mysql.proc и к INFORMATION_SCHEMA.ROUTINES.

JayDi
Сообщения: 61
Зарегистрирован: Пт 28 ноя 2008 18:29
Откуда: Россия, г.Сызрань
Контактная информация:

Re: Проблемы с производительностью процедур в MySQL

Сообщение JayDi » Вт 06 янв 2015 16:09

Dimon писал(а):Мы исследовали производительность доступа к таблицам mysql.proc и INFORMATION_SCHEMA.ROUTINES и не обнаружили значительной разницы во времени доступа даже для серверов с большим количеством баз и процедур. К тому же за более чем 7 лет использования данной функциональности вы первый пользователь, у кого воспроизводится описанная вами проблема.
Пожалуйста, укажите, какое конкретно время занимает доступ на вашем сервере к таблице mysql.proc и к INFORMATION_SCHEMA.ROUTINES.
То, что никто не жалуется -- не значит, что проблем нет. Дело в том, что использование процедур в MySQL -- очень редкое явление. А использование профайлеров - еще реже. С этой проблемой столкнулся 3 года назад и создал соответствующую тему. Проблема была решена в одном из обновлений. Но потом опять появилась.

Выше есть скриншоты AQTime с реальными замерами (см. строчки с цифрами слева: 10 -- 10 раз выполнился код, 539 -- на все 10 выполнений потрачено 539 мс времени).

Там же выше есть планы запроса для mysql.proc и для INFORMATION_SCHEMA.ROUTINES, где видно, что первый вариант использует индекс, а второй -- считывает все данные.

С тех пор ничего кардинально не изменилось.

Вот сегодняшний пример с тестового сервера (виртуальная линукс-машина с установленной MySQL (5.0.67), 1666 процедур во всех базах):

Код: Выделить всё

//время выполнения: 0,001 сек
SELECT type, returns, param_list FROM mysql.proc WHERE (name = 'm_orders_return_stages') AND (db = 'print_v29')

Код: Выделить всё

//время выполнения: 0,046 сек
SELECT ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE (ROUTINE_NAME = 'm_orders_return_stages') AND  (ROUTINE_SCHEMA = 'print_v29')
Как видно, код выполняется в 46 раз медленнее.

На рабочем сервере (5.6.21-log) с 878 процедурами во всех базах результат аналогичный.

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

JayDi
Сообщения: 61
Зарегистрирован: Пт 28 ноя 2008 18:29
Откуда: Россия, г.Сызрань
Контактная информация:

Re: Проблемы с производительностью процедур в MySQL

Сообщение JayDi » Вт 06 янв 2015 16:23

Забавно. Только сейчас обратил внимание. В новом коде обращение к INFORMATION_SCHEMA.ROUTINES используется только для того, чтобы узнать -- процедура это или функция. А далее вызывается код "SHOW CREATE PROCEDURE" или "SHOW CREATE FUNCTION".

Но в действительно, SHOW CREATE -- это чуть ли не аналог для обращения к всё той же mysql.proc :)

simplex
Сообщения: 1
Зарегистрирован: Пт 20 янв 2017 09:46

Re: Проблемы с производительностью процедур в MySQL

Сообщение simplex » Пт 20 янв 2017 10:02

Подтверждаю проблему, если в базе 1000 процедур, и на форме лежит 20 MyStoredProc - жуткие тормоза ...

ViktorV
Devart Team
Сообщения: 212
Зарегистрирован: Чт 31 июл 2014 09:52

Re: Проблемы с производительностью процедур в MySQL

Сообщение ViktorV » Вт 24 янв 2017 10:26

Если Вы хотите, чтобы мы добавили данный функционал, пожалуйста, напишите об этом на нашем User Voice форуме: https://devart.uservoice.com/forums/104 ... 939-unidac. Если Ваше предложение наберет достаточно голосов, мы рассмотрим возможность его реализации.

ViktorV
Devart Team
Сообщения: 212
Зарегистрирован: Чт 31 июл 2014 09:52

Re: Проблемы с производительностью процедур в MySQL

Сообщение ViktorV » Вт 21 май 2019 08:09

Мы улучшили производительность при получении информации о хранимой процедуры (функции). Данное исправление будет включено в следующий билд UniDAC.

Закрыто