[Davion]

Коллеги дайте совет.
Есть 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 все так плохо? 
Кто какие решения использует?

[alibek]

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

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

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

[Davion]

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

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

[jffulcrum]

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

[Davion]

44 минуты назад, jffulcrum сказал:

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

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

[NewUse]

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

[Davion]

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

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

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

[NewUse]

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

[NewUse]

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

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

 

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

 

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

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

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

[Davion]

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"

[NewUse]

Цитата

show indexes from radippool;

покажите.

 

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

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

  

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

 

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

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

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

[jffulcrum]

@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 для данного запроса, сразу увидите, есть ли индексы  и работают ли он для данного запроса. 

[Davion]

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

[NewUse]

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

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

 

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

Изменено 11 июня, 2019 пользователем NewUse

[Davion]

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

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

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

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

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

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

[NewUse]

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

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

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

[Davion]

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

 

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

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

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

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

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

 

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

[Davion]

 

Удалось разогнать
             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 все в ступор впадает...

 

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

 

[Davion]

            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 истек, то все ступор.

[Davion]

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

[NewUse]

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

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

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

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

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

 

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

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

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

Изменено 11 июня, 2019 пользователем NewUse
добавил пример индекса по округлению

[NewUse]

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

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

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

[Tosha]

20 часов назад, Davion сказал:

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

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

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

[NewUse]

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

[Tosha]

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