Jump to content
Калькуляторы

Freeradius и ippool

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)
 

Индексы не составные

Share this post


Link to post
Share on other sites
5 минут назад, Tosha сказал:

select for update только блокировку ставит. Скорости исполнения апдейта он мало помогает.

от куда у Вас такие сведения? желательно с пруфом, ибо даташит говорит об обратном.

Share this post


Link to post
Share on other sites

коннект то один?  Если один то непонятно как бороться с копящимися блокировками? Или первый же апдейт ее снимает? Транзакция наверное... Никогда так не пробовал. У нас раздача - чисто селекты.

Share this post


Link to post
Share on other sites

откуда копящиеся блокировки?, коннект не один, это же фрирадиус, на каждый запрос своё соединение.

Коммит снимает блокировку.

 

12 минут назад, Davion сказал:

У всех адресов лиза  истекла.(все в ж....)

 

ещё раз, где число блокировок при этом запросе?

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

Share this post


Link to post
Share on other sites

этот запрос не спасти....

пишите хранимку, которая сначала попытается найти пул+имя, например, а если не нашлось - то пул + первая свободная.

 

Share this post


Link to post
Share on other sites
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')?

Share this post


Link to post
Share on other sites

Не уверен это чуть модернизированный штатный запрос радиуса, нашим билингистом, а я разгребаю )

Share this post


Link to post
Share on other sites

выкиньте нафиг order by,  и скорость выполнения должна взлететь под небеса :))

Share this post


Link to post
Share on other sites

Так как выборка идет практически всегда по полной таблице (точнее ее части под заданный "пул") ее тормозит наличие любой блокировки.

1) делать так чтобы запросы от одного клиента никогда не наступили на блокировку от другого клиента. Или хотя бы как редкое исключение.
2) минимизировать время блокировки. В идеале сразу помечать записи как те, для которых начат процесс выдачи и чтобы другие селекты такие записи обходили. Случай зависания адреса в процессе выдачи чистить сторонними способами.

Share this post


Link to post
Share on other sites

где Вы увидели полный скан?

в плане индексы есть. Ну, и для обхода блокировок есть skip locked как раз, но это костыль, его можно применять, но с умом.

 

я бы ныверное изменил в добавок к индексации с округлением, поле  expiry_time на not null , default 2000-01-01 или типа того, и в order by условие поставил просто expiry_time , но опять таки  с округлением до часов/минут, это если я правильно понял смысл адского order by, и да, в таком случае skip locked уже будет уместен.

Share this post


Link to post
Share on other sites

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

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"

 

А тому, кто правил запросы под Ваш биллинг -- руки пообрывать...

 

Share this post


Link to post
Share on other sites
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"

 

А тому, кто правил запросы под Ваш биллинг -- руки пообрывать...

 

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

Share this post


Link to post
Share on other sites

Следует понимать, что:

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

 

Так вот.

Я полностью согласен с предыдущими участниками дискуссии - ваши OR в условии и расчетное поле в ORDER BY - это жопа, чтобы понять какая - уберите LIMIT 1.

 

Разделите процесс на 2 части, сперва поищите последние лизы пользователя, потом берите из общего пула.

 

З.Ы. Ваш ORDER BY, требует от сервера сперва сделать полную выборку в память, и только потом вернуть 1 строку из полученной выборки.

Share this post


Link to post
Share on other sites
8 часов назад, Davion сказал:

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

поясните?, оно из коробки рабочее, есно лиза не будет затёрта пока не истечёт её срок аренды.

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

Share this post


Link to post
Share on other sites
19 часов назад, NewUse сказал:

где Вы увидели полный скан?

explain имеет rows = 28898.  Это весьма похоже на почти полный скан таблицы. У Вас в ней вообще сколько записей?

И скажите мне после выполнения первого запроса с блокировкой как в этом случае будут вести себя следующие?  Правильно - ждать снятия блокировки. А снятие когда будет?  Это же чудовищное время ожидания!

Share this post


Link to post
Share on other sites

он же писал: 60000, а 28898 -- это скорее всего дурацкий индекс по экспаир_тайм + ордер бай, я по экспаир_тайм не вижу вообще смысла в индексации, без соотв. округления и то при условии нот нулл....

 

Share this post


Link to post
Share on other sites

чтение пол таблицы это не сильно отличается от полного :)

Share this post


Link to post
Share on other sites
32 минуты назад, Tosha сказал:

И скажите мне после выполнения первого запроса с блокировкой как в этом случае будут вести себя следующие?  Правильно - ждать снятия блокировки. А снятие когда будет?  Это же чудовищное время ожидания! 

во-первых, нет, не правильно, skip locked указал и ждать не будет, во-вторых, если бага, приведённая ранее профикшена, то блокировка одной строки ничего не затормозит, в-третьих, обновление одной строки занимает сотые доли, так что тоже не проблема.

 

13 минут назад, Tosha сказал:

чтение пол таблицы это не сильно отличается от полного :)

а вот это дурацкая сортировка...

 

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

Edited by NewUse
дополнил

Share this post


Link to post
Share on other sites

Приведенный штатный запрос не сильно отличается в лучшую сторону - есть и оператор OR в WHERE и сортировка.


skip locked вещь хорошая и безусловно поможет! Не знал. У нас 5.* версия и на ней таких хороших костылей нет. Приходится хорошо думать чтобы запросы с блокировками пересекались друг с другом с низкой вероятностью :)

Share this post


Link to post
Share on other sites
1 час назад, Tosha сказал:

Приведенный штатный запрос не сильно отличается в лучшую сторону - есть и оператор OR в WHERE и сортировка. 

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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this