Davion Posted June 10, 2019 · Report post Коллеги дайте совет. Есть 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
alibek Posted June 10, 2019 · Report post MySQL при правильном использовании даёт неплохую производительность. Какие-то гуглосервисы на нем даже работают. Но это нужно анализировать запросы, создавать индексы, настраивать БД и сервер, вообщем погружаться довольно глубоко в DBA. Если с Postgre все хорошо, то его и используйте, он из коробки работает лучше, чем MySQL из коробки. Share this post Link to post Share on other sites
Davion Posted June 10, 2019 · Report post Да уже как только и индексы не крутил, и различные тюнинги, всеравно просто жо... Интересно как у других оно, наверняка уже набили шишки. Share this post Link to post Share on other sites
jffulcrum Posted June 10, 2019 · Report post https://github.com/major/MySQLTuner-perl умеет выдать кучу инфы с подсказками, что не так. Share this post Link to post Share on other sites
Davion Posted June 10, 2019 · Report post 44 минуты назад, jffulcrum сказал: https://github.com/major/MySQLTuner-perl умеет выдать кучу инфы с подсказками, что не так. Это было проверено первым) когда у тебя 10 тыс клиентов подключается, все просто умирает) Share this post Link to post Share on other sites
NewUse Posted June 10, 2019 · Report post индекс по округлённому до минут expiry_time создать, индекс по username, возможно, индекс по framedipaddress(не понимаю как select for update тут может/должно работать, если не работает, то нужен индекс, если работает, то не нужен), остальные индексы грохнуть. Share this post Link to post Share on other sites
Davion Posted June 10, 2019 · Report post 8 минут назад, NewUse сказал: индекс по округлённому до минут expiry_time создать, индекс по username, возможно, индекс по framedipaddress(не понимаю как select for update тут может/должно работать, если не работает, то нужен индекс, если работает, то не нужен), остальные индексы грохнуть. Так и сделано, правда индекс еще и по адресу. Share this post Link to post Share on other sites
NewUse Posted June 10, 2019 · Report post выкиньте лишний индекс, analyze для таблицы/индексов делали? Share this post Link to post Share on other sites
NewUse Posted June 10, 2019 · Report post Проверьте, не проявляется ли у Вас этот баг: https://bugs.mysql.com/bug.php?id=67745 т.е. лок идёт более чем на одну строчку. 25 минут назад, Davion сказал: правда индекс еще и по адресу. покажите индексы. Share this post Link to post Share on other sites
Davion Posted June 11, 2019 · Report post 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
NewUse Posted June 11, 2019 · Report post Цитата 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
jffulcrum Posted June 11, 2019 · Report post @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
Davion Posted June 11, 2019 · Report post 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
NewUse Posted June 11, 2019 (edited) · Report post не логично, судя по доке должны блокироваться только выбранные строки, а skip locked снимает это ограничение, в любом случае, я почти уверен, что не отрабатывает, limit, проверьте. Не вижу индекса по округлённому expiry_time, также не совсем понимаю, но похоже у Вас индексы составные, что в Вашем случае может сыграть в минус. общий объем таблицы в строках, сколько 1млн то хоть есть? Edited June 11, 2019 by NewUse Share this post Link to post Share on other sites
Davion Posted June 11, 2019 · Report post 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
NewUse Posted June 11, 2019 · Report post да, но это не совсем то, Если у Вас блокиррвалась и обновлялась только одна строка, то по идее влять серьёзно скип локед был не должен. так что проверяйте. Верните несоставные. Сделайте индекс с округлением, Вы не ответили по объёму таблицы. Если таблица маленькая, то причина в локах, надо тогда добиваться блокировки ровно одной строки на запрос(это к специалистам mysql обращаться надо на какой-нибудь sql ru) Share this post Link to post Share on other sites
Davion Posted June 11, 2019 · Report post Поигрался с индексами чуть удалось разогнать, но не сильно) 3 минуты назад, NewUse сказал: да, но это не совсем то, Если у Вас блокиррвалась и обновлялась только одна строка, то по идее влять серьёзно скип локед был не должен. так что проверяйте. Верните несоставные. Сделайте индекс с округлением, Вы не ответили по объёму таблицы. Если таблица маленькая, то причина в локах, надо тогда добиваться блокировки ровно одной строки на запрос(это к специалистам mysql обращаться надо на какой-нибудь sql ru) 60 тыс. записей Гугол не знае про округление индексов... Share this post Link to post Share on other sites
Davion Posted June 11, 2019 · Report post Удалось разогнать 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
Davion Posted June 11, 2019 · Report post 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
Davion Posted June 11, 2019 · Report post проблема видимо в INDEX datetime Share this post Link to post Share on other sites
NewUse Posted June 11, 2019 (edited) · Report post 51 минуту назад, Davion сказал: Гугол не знае про округление индексов... похоже я это на постгресе делал, а не на мускуле, но сделайте тогда виртуальную колонку с округлен до минуты expiry_time , постройте индекс по ней, и в запросе используйте поиск по ней. не, всё ОК, всё умеет, только обрезку до минут или часов сделайте: https://use-the-index-luke.com/sql/where-clause/obfuscation/dates 39 минут назад, Davion сказал: Без SKIP LOCKED все в ступор впадает... сколько блокировок на один запрос select for update? Edited June 11, 2019 by NewUse добавил пример индекса по округлению Share this post Link to post Share on other sites
NewUse Posted June 11, 2019 · Report post 1 час назад, Davion сказал: 60 тыс. записей у Вас не должно быть запросов дольше 0.1 секунды, по крайней мере на Oracle среднее время выполнения было бы около 0.02с. Share this post Link to post Share on other sites
Tosha Posted June 11, 2019 · Report post 20 часов назад, Davion сказал: UPDATE ... WHERE framedipaddress = '%I'" Без индекса по framedipaddress это знатные грабли Выборка же в таком виде ничего кроме индекса по pool_name использовать не может - составной индекс при условии "OR" бесполезен Share this post Link to post Share on other sites
NewUse Posted June 11, 2019 · Report post у него select for update: не нужен индекс по framedipaddress при одной строке в выборке. Share this post Link to post Share on other sites
Tosha Posted June 11, 2019 · Report post select for update только блокировку ставит. Скорости исполнения апдейта он мало помогает. Имхо Share this post Link to post Share on other sites
