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

Freeradius и ippool

Коллеги дайте совет.
Есть Freeradius где абонентам выделяются IP адреса используя следующие запросы.
allocate_find = "\
        SELECT framedipaddress FROM ${ippool_table} \
        WHERE pool_name = 'test' \
        AND ( (username = '%{User-Name}') OR (expiry_time < NOW() OR expiry_time IS NULL) ) \
        ORDER BY \
                (username <> '%{User-Name}'), \
                expiry_time \
        LIMIT 1 \
        FOR UPDATE"


allocate_update = "\
        UPDATE ${ippool_table} \
        SET \
                nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \
                callingstationid = '%{Calling-Station-Id}', \
                username = '%{User-Name}', expiry_time = NOW() + INTERVAL ${lease_duration} SECOND \
        WHERE framedipaddress = '%I'"

Если использовать mariadb 10.3 + InnoDB(myisam не подходит из за необходимости транзакций) то при тестирование база быстро загибается, в процессах повисают SELECT.
При использование Postgres из коробки все летает, все достаточно быстро обрабатывает. После небольшого тюнинга производительность вырасла еще в два раза...

Пробовал использовать MySQL  с функцией построчной блокировки FOR UPDATE SKIP LOCK производительность MySQL выросла, но всеравно в разы хуже чем у Postgresql

Неужели у MySQL+InnoDB все так плохо? 
Кто какие решения использует?

Share this post


Link to post
Share on other sites

MySQL при правильном использовании даёт неплохую производительность. Какие-то гуглосервисы на нем даже работают.

Но это нужно анализировать запросы, создавать индексы, настраивать БД и сервер, вообщем погружаться довольно глубоко в DBA.

Если с Postgre все хорошо, то его и используйте, он из коробки работает лучше, чем MySQL из коробки.

Share this post


Link to post
Share on other sites

Да уже как только и индексы не крутил, и различные тюнинги, всеравно просто жо...

Интересно как у других оно, наверняка уже набили шишки.

Share this post


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

https://github.com/major/MySQLTuner-perl умеет выдать кучу инфы с подсказками, что не так.

Это было проверено первым) когда у тебя 10 тыс клиентов подключается, все просто умирает)

Share this post


Link to post
Share on other sites

индекс по округлённому до минут expiry_time создать, индекс по username, возможно, индекс по framedipaddress(не понимаю как select for update тут может/должно работать, если не работает, то нужен индекс, если работает, то не нужен), остальные индексы грохнуть.

Share this post


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

индекс по округлённому до минут expiry_time создать, индекс по username, возможно, индекс по framedipaddress(не понимаю как select for update тут может/должно работать, если не работает, то нужен индекс, если работает, то не нужен), остальные индексы грохнуть.

Так и сделано, правда индекс еще и по адресу.

Share this post


Link to post
Share on other sites

выкиньте лишний индекс, analyze для таблицы/индексов делали?

Share this post


Link to post
Share on other sites

Проверьте, не проявляется ли у Вас этот баг:

https://bugs.mysql.com/bug.php?id=67745

 

т.е. лок идёт более чем на одну строчку.

 

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

правда индекс еще и по адресу.

покажите индексы.

Share this post


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

Проверьте, не проявляется ли у Вас этот баг:

https://bugs.mysql.com/bug.php?id=67745

 

т.е. лок идёт более чем на одну строчку.

 

покажите индексы.

Текущая конфигурация индексов

Create Table


CREATE TABLE `radippool` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pool_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `framedipaddress` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `nasipaddress` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `calledstationid` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `callingstationid` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `expiry_time` datetime DEFAULT NULL,
  `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `pool_key` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pool_name` (`pool_name`,`username`,`expiry_time`,`framedipaddress`),
  KEY `pool_name_2` (`pool_name`,`expiry_time`,`username`,`framedipaddress`)
) ENGINE=InnoDB AUTO_INCREMENT=70092 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

Для отсутствия блокировки таблицы, используется MySQL 8 c SKIP LOCK

 

allocate_find = "\
        SELECT framedipaddress FROM ${ippool_table} \
        WHERE pool_name = 'test' \
        AND ( (username = '%{User-Name}') OR (expiry_time < NOW() OR expiry_time IS NULL) ) \
        ORDER BY \
                (username <> '%{User-Name}'), \
                expiry_time \
        LIMIT 1 \
        FOR UPDATE SKIP LOCKED"
 

Share this post


Link to post
Share on other sites
Цитата

show indexes from radippool;

покажите.

 

2 часа назад, Davion сказал:

Для отсутствия блокировки таблицы, используется MySQL 8 c SKIP LOCK

  

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

 

2 часа назад, Davion сказал:

KEY `pool_name` (`pool_name`,`username`,`expiry_time`,`framedipaddress`),

я уже не помню синтексиса вывода mysql, но зачем у Вас индекс составной, если это, конечно, индекс.

Share this post


Link to post
Share on other sites

@Davion 

3 часа назад, Davion сказал:

allocate_find = "\
        SELECT framedipaddress FROM ${ippool_table} \
        WHERE pool_name = 'test' \
        AND ( (username = '%{User-Name}') OR (expiry_time < NOW() OR expiry_time IS NULL) ) \
        ORDER BY \
                (username <> '%{User-Name}'), \
                expiry_time \
        LIMIT 1 \
        FOR UPDATE SKIP LOCKED"

Сделайте EXPLAIN для данного запроса, сразу увидите, есть ли индексы  и работают ли он для данного запроса. 

Share this post


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

@Davion 

Сделайте EXPLAIN для данного запроса, сразу увидите, есть ли индексы  и работают ли он для данного запроса. 

+----+-------------+-----------+------------+------+-----------------------+-----------+---------+-------+-------+----------+------------------------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key       | key_len | ref   | rows  | filtered | Extra                                    |
+----+-------------+-----------+------------+------+-----------------------+-----------+---------+-------+-------+----------+------------------------------------------+
|  1 | SIMPLE      | radippool | NULL       | ref  | pool_name,pool_name_2 | pool_name | 92      | const | 28978 |    46.00 | Using where; Using index; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------+---------+-------+-------+----------+------------------------------------------+
1 row in set, 1 warning (0,00 sec)
Все ок.

 

2 часа назад, NewUse сказал:

покажите.

 

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

 

я уже не помню синтексиса вывода mysql, но зачем у Вас индекс составной, если это, конечно, индекс.

mysql> show indexes from radippool;
+-----------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name    | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| radippool |          0 | PRIMARY     |            1 | id              | A         |       57731 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name   |            1 | pool_name       | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name   |            2 | username        | A         |       57731 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name   |            3 | expiry_time     | A         |       57731 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name   |            4 | framedipaddress | A         |       57731 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name_2 |            1 | pool_name       | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name_2 |            2 | expiry_time     | A         |       12004 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name_2 |            3 | username        | A         |       55087 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| radippool |          1 | pool_name_2 |            4 | framedipaddress | A         |       56460 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
 

Ну логично что блокируется вся таблица без SKIP LOCK

Share this post


Link to post
Share on other sites

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

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

 

общий объем таблицы в строках, сколько 1млн то хоть есть?

Edited by NewUse

Share this post


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

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

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

Были не составные разницы никакой. 

В MySQL 8.0.1 был введен модификатор SKIP LOCKED, использующийся для не детерминистического чтения из таблицы с пропуском строк, заблокированых другими пользователями.

https://sqlinfo.ru/articles/info/41.html

Share this post


Link to post
Share on other sites

да, но это не совсем то, Если у Вас блокиррвалась и обновлялась только одна строка, то по идее влять серьёзно скип локед был не должен.

так что проверяйте.

Верните несоставные. Сделайте индекс с округлением, Вы не ответили по объёму таблицы. Если таблица маленькая, то причина в локах, надо тогда добиваться блокировки ровно одной строки на запрос(это к специалистам mysql обращаться надо на какой-нибудь sql ru)

Share this post


Link to post
Share on other sites

Поигрался с индексами чуть удалось разогнать, но не сильно)

 

3 минуты назад, NewUse сказал:

да, но это не совсем то, Если у Вас блокиррвалась и обновлялась только одна строка, то по идее влять серьёзно скип локед был не должен.

так что проверяйте.

Верните несоставные. Сделайте индекс с округлением, Вы не ответили по объёму таблицы. Если таблица маленькая, то причина в локах, надо тогда добиваться блокировки ровно одной строки на запрос(это к специалистам mysql обращаться надо на какой-нибудь sql ru)

60 тыс. записей 

 

Гугол не знае про округление индексов...

Share this post


Link to post
Share on other sites

 

Удалось разогнать
             Total sent        :  1936
             Total retransmits :  7
             Total succeeded   :  1936
             Total failed      :  0
             Total no reply    :  0
             Total time (s)    :  73.341
             Packets/s         :  26
             Response times:
                < 10 usec  : 0
                < 100 usec : 0
                < msec     : 0
                < 10 msec  : 0
                < 0.1s     : 0
                < s        : 32
                < 10s      : 1904
                < 100s     : 0
 

Но до Postgresql не дотягивает:

Total sent        :  1936
             Total retransmits :  0
             Total succeeded   :  1936
             Total failed      :  0
             Total no reply    :  0
             Total time (s)    :  20.880
             Packets/s         :  93
             Response times:
                < 10 usec  : 0
                < 100 usec : 0
                < msec     : 0
                < 10 msec  : 0
                < 0.1s     : 0
                < s        : 1936
                < 10s      : 0
                < 100s     : 0

 

Без SKIP LOCKED все в ступор впадает...

 

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

 

Share this post


Link to post
Share on other sites

            Total sent        :  1936
             Total retransmits :  2
             Total succeeded   :  1936
             Total failed      :  0
             Total no reply    :  0
             Total time (s)    :  5.015
             Packets/s         :  386
             Response times:
                < 10 usec  : 0
                < 100 usec : 0
                < msec     : 0
                < 10 msec  : 0
                < 0.1s     : 1810
                < s        : 124
                < 10s      : 2
                < 100s     : 0
Если expiry_time еще не истек, тогда все хорошо)

 

Без SKIP LOCKED тоже все ок... 

 

Если expiretime истек, то все ступор.

Share this post


Link to post
Share on other sites

проблема видимо в INDEX datetime

Share this post


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

Гугол не знае про округление индексов...

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

не, всё ОК, всё умеет, только обрезку до минут или часов сделайте:

https://use-the-index-luke.com/sql/where-clause/obfuscation/dates

 

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

Без SKIP LOCKED все в ступор впадает... 

сколько блокировок на один запрос select for update?

Edited by NewUse
добавил пример индекса по округлению

Share this post


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

60 тыс. записей

у Вас не должно быть запросов дольше 0.1 секунды, по крайней мере на Oracle среднее время выполнения было бы около 0.02с.

Share this post


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

UPDATE ... WHERE framedipaddress = '%I'"

Без индекса по framedipaddress  это знатные грабли

Выборка же в таком виде ничего кроме индекса по pool_name  использовать не может - составной индекс при условии "OR" бесполезен

Share this post


Link to post
Share on other sites

у него select for update: не нужен индекс по framedipaddress при одной строке в выборке.

Share this post


Link to post
Share on other sites

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

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