![]() |
Задавайте вопросы, мы ответим
Вы не зашли.
Добрый день. Есть хранимая функция, она максимально оптимизирована для производительности, больше не выжать. Поэтому так и записана
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `SORT_STR`(`string1` VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE sort VARCHAR(255);
DECLARE str1 VARCHAR(255);
DECLARE word VARCHAR(255);
CREATE TEMPORARY TABLE IF NOT EXISTS sort_t
(w VARCHAR(255));
SET str1 = LOWER(string1);
sub_str_loop: LOOP
SET word = SUBSTRING_INDEX(str1,' ', 1);
SET str1 = trim(replace(str1, word, ''));
INSERT INTO sort_t set w = word;
IF str1 = '' THEN
LEAVE sub_str_loop;
END IF;
END LOOP sub_str_loop;
select group_concat(`w` ORDER BY `w` asc SEPARATOR ' ') as words into sort from sort_t;
delete from sort_t;
RETURN sort;
END
Делает она лишь 1, берет на входе строку -> разбивает ее на слова -> добавляет в временную таблицу -> сортирует -> отдает отсортированную строку.
Есть таблица InnoDB - количество записей 350 тысяч.
Есть колонка - Данные в ней примерно такие [Блок питания Chieftec A-135 APS-750C]
Update всей таблицы статичным значением, например нулл
UPDATE таблица SET колонка = null
386398 row(s) affected
Rows matched: 386398 Changed: 386398 Warnings: 0
17.659 sec
И тут начинается магия. Непонятное увеличение времени выполнения.
Небольшой бенчмарк (количество обновляемых строк - время) Запросы идут друг за другом со смешением по ИД записи что бы их не было до этого в кеше
1 - 0.031
10 - 0.063
100 - 0.187
1000 - 3.775
10000 - 246.310
Получается я быстрее сделаю 10 запросов в хранимой процедуре по 100 строк чем 1 на 1000 строк.
Что нужно Mysql для таких запросов ? Памяти хватает, сервер мощный. Где он буксует ?
Видно что в хранимой функции, но как ее оптимизировать ? почему меньшее кол-во строк выполняется в сумме быстрее чем большее ?
Отредактированно Technics (05.08.2014 14:57:05)
Неактивен
Какую именно команду выполняли для бенчмарка? Приведите пример.
Неактивен
rgbeast написал:
Какую именно команду выполняли для бенчмарка? Приведите пример.
Обычный запрос со смещением. Такие же данные можно получить ничего не смещая
Неактивен
Получается, что один запрос на 1000 записей медленнее, чем 10 запросов по 100?
Тип таблиц InnoDB? Может быть дополнительный оверхед связан с транзакционностью.
1) попробуйте создавать временную таблицу:
CREATE TEMPORARY TABLE IF NOT EXISTS sort_t (w VARCHAR(255)) ENGINE=MEMORY;
2) попробуйте выполнить профилирование запросов
Неактивен
rgbeast написал:
Получается, что один запрос на 1000 записей медленнее, чем 10 запросов по 100?
Тип таблиц InnoDB? Может быть дополнительный оверхед связан с транзакционностью.
1) попробуйте создавать временную таблицу:
CREATE TEMPORARY TABLE IF NOT EXISTS sort_t (w VARCHAR(255)) ENGINE=MEMORY;
2) попробуйте выполнить профилирование запросов
Вначале на пункт 2.
Вот что показывает профайлинг, сравнительная характеристика 2 запросов
Запрос
Отредактированно Technics (06.08.2014 18:25:54)
Неактивен
Неактивен
rgbeast написал:
DELETE FROM sort_t;
замените наTRUNCATE sort_t;
тогда она не будет занимать времени.
В таблицу попробуйте добавить индекс и сделать ее в памяти:CREATE TEMPORARY TABLE IF NOT EXISTS sort_t (w VARCHAR(255), KEY(w)) ENGINE=MEMORY;
Из примера пока не видно проблем с масштабируемостью.
В функциях нельзя использовать такую конструкцию
Отредактированно Technics (06.08.2014 18:35:50)
Неактивен
Вот более интересная статистика по увеличившему время запросу
Первая картинка это при 10 тысячах, вторая при 10
Отредактированно Technics (06.08.2014 18:51:30)
Неактивен
Да, действительно странная картина. А время выполнения каждый раз одинаковое или есть динамика от начала к концу выполнения?
Неактивен
rgbeast написал:
Да, действительно странная картина. А время выполнения каждый раз одинаковое или есть динамика от начала к концу выполнения?
Не могу посмотреть динамику, выводится всего 100 запросов по профайлингу.
Посмотрел переменные локальные, все таблицы действительно создаются в памяти
Неактивен
Для чистоты эксперимента сделайте >=1000 (вместо >1000), чтобы все записи вошли в первый набор.
Очень странное поведение. Какая версия MySQL? Если повторяется на новых версиях и можно сделать набор, на котором воспроизводится, то можно попробовать написать на bugs.mysql.com.
Неактивен
В общем решил вопрос. Все было связано с версиями.
Типом таблиц по умолчанию в конфиге был InnoDB.То есть при создании временной таблицы
Неактивен
Осталось только понять волшебство почему Innodb временная таблица при указании ключа превращается в MyISAM
A TEMPORARY table can only be of type MEMORY, MyISAM, MERGE, or InnoDB.
Temporary tables are not supported for MySQL Cluster.
Значит мемори это еще один тип таблицы, а не указание того что она находится в память.
Отредактированно Technics (06.08.2014 21:18:57)
Неактивен
У InnoDB сложное хранилище данных. Скорее всего поведение связано с тем, что все операции выполняются в одной транзакции. То есть, если что-то пойдет не так, InnoDB должен быть готов отменить все операции. Вероятно, из-за этого очещенные временные данные не исчезают, а помечаются как удаленные.
Не обязательно делать по-умолчанию MyISAM. Вы можете сменить тип на MyISAM или MEMORY только для одной таблицы.
Неактивен