Davion Опубликовано 11 июня, 2019 · Жалоба 13 минут назад, Tosha сказал: Без индекса по framedipaddress это знатные грабли Выборка же в таком виде ничего кроме индекса по pool_name использовать не может - составной индекс при условии "OR" бесполезен Да уже куча опытов было)) сейчас с индексом на айдрес, пул и время и узернейм. Выхлопа не много, если лиза истекал все пропало. У всех адресов лиза не истекла. EXPLAIN SELECT framedipaddress FROM radippool WHERE pool_name = 'test' AND ( (username = 'test') OR (expiry_time < NOW() OR expiry_time IS NULL) ) ORDER BY (username <> 'test'), expiry_time LIMIT 1 FOR UPDATE; +----+-------------+-----------+------------+-------------+-------------------------------+----------------------+---------+------+------+----------+---------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------------+-------------------------------+----------------------+---------+------+------+----------+---------------------------------------------------------------------+ | 1 | SIMPLE | radippool | NULL | index_merge | poolname,username,expiry_time | username,expiry_time | 194,6 | NULL | 2 | 50.00 | Using sort_union(username,expiry_time); Using where; Using filesort | +----+-------------+-----------+------------+-------------+-------------------------------+----------------------+---------+------+------+----------+---------------------------------------------------------------------+ 1 row in set, 1 warning (0,00 sec) У всех адресов лиза истекла.(все в ж....) mysql> EXPLAIN SELECT framedipaddress FROM radippool WHERE pool_name = test' AND ( (username = 'test) OR (expiry_time < NOW() OR expiry_time IS NULL) ) ORDER BY (username <> 'test'), expiry_time LIMIT 1 FOR UPDATE; +----+-------------+-----------+------------+------+-------------------------------+----------+---------+-------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-------------------------------+----------+---------+-------+-------+----------+-----------------------------+ | 1 | SIMPLE | radippool | NULL | ref | poolname,username,expiry_time | poolname | 92 | const | 28898 | 46.00 | Using where; Using filesort | +----+-------------+-----------+------------+------+-------------------------------+----------+---------+-------+-------+----------+-----------------------------+ 1 row in set, 1 warning (0,00 sec) Индексы не составные Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 11 июня, 2019 · Жалоба 5 минут назад, Tosha сказал: select for update только блокировку ставит. Скорости исполнения апдейта он мало помогает. от куда у Вас такие сведения? желательно с пруфом, ибо даташит говорит об обратном. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Tosha Опубликовано 11 июня, 2019 · Жалоба коннект то один? Если один то непонятно как бороться с копящимися блокировками? Или первый же апдейт ее снимает? Транзакция наверное... Никогда так не пробовал. У нас раздача - чисто селекты. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 11 июня, 2019 · Жалоба откуда копящиеся блокировки?, коннект не один, это же фрирадиус, на каждый запрос своё соединение. Коммит снимает блокировку. 12 минут назад, Davion сказал: У всех адресов лиза истекла.(все в ж....) ещё раз, где число блокировок при этом запросе? я выше кидал ссыль на статью для индексирования округлённого времени, и в условиях тоже надо использовать округление. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Tosha Опубликовано 11 июня, 2019 · Жалоба этот запрос не спасти.... пишите хранимку, которая сначала попытается найти пул+имя, например, а если не нашлось - то пул + первая свободная. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 11 июня, 2019 · Жалоба 24 минуты назад, Davion сказал: EXPLAIN SELECT framedipaddress FROM radippool WHERE pool_name = test' AND ( (username = 'test) OR (expiry_time < NOW() OR expiry_time IS NULL) ) ORDER BY (username <> 'test'), expiry_time LIMIT 1 FOR UPDATE; Да, ещё один дурацкий вопрос, Вы уверены что вам нужен order by? что за форма ORDER BY (username <> 'test')? Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Davion Опубликовано 11 июня, 2019 · Жалоба Не уверен это чуть модернизированный штатный запрос радиуса, нашим билингистом, а я разгребаю ) Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 11 июня, 2019 · Жалоба выкиньте нафиг order by, и скорость выполнения должна взлететь под небеса :)) Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Tosha Опубликовано 11 июня, 2019 · Жалоба Так как выборка идет практически всегда по полной таблице (точнее ее части под заданный "пул") ее тормозит наличие любой блокировки. 1) делать так чтобы запросы от одного клиента никогда не наступили на блокировку от другого клиента. Или хотя бы как редкое исключение. 2) минимизировать время блокировки. В идеале сразу помечать записи как те, для которых начат процесс выдачи и чтобы другие селекты такие записи обходили. Случай зависания адреса в процессе выдачи чистить сторонними способами. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 11 июня, 2019 · Жалоба где Вы увидели полный скан? в плане индексы есть. Ну, и для обхода блокировок есть skip locked как раз, но это костыль, его можно применять, но с умом. я бы ныверное изменил в добавок к индексации с округлением, поле expiry_time на not null , default 2000-01-01 или типа того, и в order by условие поставил просто expiry_time , но опять таки с округлением до часов/минут, это если я правильно понял смысл адского order by, и да, в таком случае skip locked уже будет уместен. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 11 июня, 2019 · Жалоба В общем, залез я в код фрирадиуса, нефиг мудрить, перепишите запросы(исправил в соответствии с Вашей таблицей): allocate_find = "\ SELECT framedipaddress \ FROM ${ippool_table} \ WHERE pool_name = 'test' \ AND (username = '%{User-Name} or username = '') \ ORDER BY \ (username <> '%{User-Name}), \ expiry_time \ LIMIT 1 \ FOR UPDATE" Индексы: KEY radippool_poolname_expire (pool_name, expiry_time), KEY callingstationid (username), KEY framedipaddress (framedipaddress), KEY radippool_nasip_poolkey_ipaddress (nasipaddress, pool_key, framedipaddress) Не считаю это оптимальным, но в любом случае будет лучше, чем сейчас. Если нужно, чтоб ip был каждый раз для одного пользователя новый, фрирадиус предлагает следующий запрос: allocate_find = "\ SELECT framedipaddress \ FROM ${ippool_table} \ WHERE pool_name = 'test' \ AND expiry_time < NOW() \ ORDER BY RAND() \ LIMIT 1 \ FOR UPDATE" А тому, кто правил запросы под Ваш биллинг -- руки пообрывать... Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Davion Опубликовано 11 июня, 2019 · Жалоба 1 час назад, NewUse сказал: В общем, залез я в код фрирадиуса, нефиг мудрить, перепишите запросы(исправил в соответствии с Вашей таблицей): allocate_find = "\ SELECT framedipaddress \ FROM ${ippool_table} \ WHERE pool_name = 'test' \ AND (username = '%{User-Name} or username = '') \ ORDER BY \ (username <> '%{User-Name}), \ expiry_time \ LIMIT 1 \ FOR UPDATE" Индексы: KEY radippool_poolname_expire (pool_name, expiry_time), KEY callingstationid (username), KEY framedipaddress (framedipaddress), KEY radippool_nasip_poolkey_ipaddress (nasipaddress, pool_key, framedipaddress) Не считаю это оптимальным, но в любом случае будет лучше, чем сейчас. Если нужно, чтоб ip был каждый раз для одного пользователя новый, фрирадиус предлагает следующий запрос: allocate_find = "\ SELECT framedipaddress \ FROM ${ippool_table} \ WHERE pool_name = 'test' \ AND expiry_time < NOW() \ ORDER BY RAND() \ LIMIT 1 \ FOR UPDATE" А тому, кто правил запросы под Ваш биллинг -- руки пообрывать... Требуется обязательно Лизы, чтобы айпи адреса высвобождать... И сохранять айпи за абонентом в течение срока действия Лизы... Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
sdy_moscow Опубликовано 11 июня, 2019 · Жалоба Следует понимать, что: два быстрых запроса лучше одного общего медленного. Боретесь с межсерверным взаимодействием - засуньте их в хранимую процедуру для повышения эффективности. Так вот. Я полностью согласен с предыдущими участниками дискуссии - ваши OR в условии и расчетное поле в ORDER BY - это жопа, чтобы понять какая - уберите LIMIT 1. Разделите процесс на 2 части, сперва поищите последние лизы пользователя, потом берите из общего пула. З.Ы. Ваш ORDER BY, требует от сервера сперва сделать полную выборку в память, и только потом вернуть 1 строку из полученной выборки. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 12 июня, 2019 · Жалоба 8 часов назад, Davion сказал: Требуется обязательно Лизы, чтобы айпи адреса высвобождать... И сохранять айпи за абонентом в течение срока действия Лизы... поясните?, оно из коробки рабочее, есно лиза не будет затёрта пока не истечёт её срок аренды. айпи адреса будут высвобождаться по истечению срока и по мере заканчивания свободных в пуле. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Tosha Опубликовано 12 июня, 2019 · Жалоба 19 часов назад, NewUse сказал: где Вы увидели полный скан? explain имеет rows = 28898. Это весьма похоже на почти полный скан таблицы. У Вас в ней вообще сколько записей? И скажите мне после выполнения первого запроса с блокировкой как в этом случае будут вести себя следующие? Правильно - ждать снятия блокировки. А снятие когда будет? Это же чудовищное время ожидания! Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 12 июня, 2019 · Жалоба он же писал: 60000, а 28898 -- это скорее всего дурацкий индекс по экспаир_тайм + ордер бай, я по экспаир_тайм не вижу вообще смысла в индексации, без соотв. округления и то при условии нот нулл.... Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Tosha Опубликовано 12 июня, 2019 · Жалоба чтение пол таблицы это не сильно отличается от полного :) Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 12 июня, 2019 (изменено) · Жалоба 32 минуты назад, Tosha сказал: И скажите мне после выполнения первого запроса с блокировкой как в этом случае будут вести себя следующие? Правильно - ждать снятия блокировки. А снятие когда будет? Это же чудовищное время ожидания! во-первых, нет, не правильно, skip locked указал и ждать не будет, во-вторых, если бага, приведённая ранее профикшена, то блокировка одной строки ничего не затормозит, в-третьих, обновление одной строки занимает сотые доли, так что тоже не проблема. 13 минут назад, Tosha сказал: чтение пол таблицы это не сильно отличается от полного :) а вот это дурацкая сортировка... в общем, я уже расписал, как можно сделать по человечески, но для начала я бы откатился на штатный запрос, а потом уже оптимизировал, если не хватит... Изменено 12 июня, 2019 пользователем NewUse дополнил Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
Tosha Опубликовано 14 июня, 2019 · Жалоба Приведенный штатный запрос не сильно отличается в лучшую сторону - есть и оператор OR в WHERE и сортировка. skip locked вещь хорошая и безусловно поможет! Не знал. У нас 5.* версия и на ней таких хороших костылей нет. Приходится хорошо думать чтобы запросы с блокировками пересекались друг с другом с низкой вероятностью :) Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...
NewUse Опубликовано 14 июня, 2019 · Жалоба 1 час назад, Tosha сказал: Приведенный штатный запрос не сильно отличается в лучшую сторону - есть и оператор OR в WHERE и сортировка. поможет, там or по индексированному полю с повторяемым значением. Вставить ник Цитата Ответить с цитированием Поделиться сообщением Ссылка на сообщение Поделиться на других сайтах More sharing options...