Добрый вечер! Столкнулся с дедлоком на ippool но ситуация немного другая, до этого работал dhcp модуль на freebsd, клиентов онлайн гдето 2500 в пике но решиши переходить на accel-ipoe. Подключили отдельным насом accel-ipoe на debian все было нормально пока нагрузка была до 1 Гига и гдето 800 клиентов. Но на днях решили перейти полностью на accel-ipoe, Debian справляется с нагрузкой в 4Гига на ура, но вот с базой всплыл дедлок который пока не могу решить.

Код:

*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2021-11-23 22:17:47 0x103b318400 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7959461 srv_active, 0 srv_shutdown, 6558 srv_idle
srv_master_thread log flush and writes: 7966019
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2462433
OS WAIT ARRAY INFO: signal count 76174368
RW-shared spins 0, rounds 28611375, OS waits 518541
RW-excl spins 0, rounds 1136312466, OS waits 271132
RW-sx spins 152589985, rounds 794750727, OS waits 998241
Spin rounds per wait: 28611375.00 RW-shared, 1136312466.00 RW-excl, 5.21 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-23 22:17:28 0x1037682200
*** (1) TRANSACTION:
TRANSACTION 5235292920, ACTIVE 157 sec starting index read
mysql tables in use 2, locked 1
LOCK WAIT 98 lock struct(s), heap size 24784, 636 row lock(s), undo log entries 380
MySQL thread id 20778661, OS thread handle 69649049344, query id 5511644405 localhost nodeny updating
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 154 n bits 392 index PRIMARY of table `nodeny`.`ip_pool` trx id 5235292920 lock_mode X locks rec but not gap waiting
Record lock, heap no 204 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c37; asc    7;;
 1: len 6; hex 0001380c4cfb; asc   8 L ;;
 2: len 7; hex 3a000002ac1674; asc :     t;;
 3: len 4; hex 0a00069f; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a07; asc a Z ;;
 7: len 8; hex 000000000000019f; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

*** (2) TRANSACTION:
TRANSACTION 5235291913, ACTIVE 169 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 1
100 lock struct(s), heap size 24784, 1077 row lock(s), undo log entries 652
MySQL thread id 20778679, OS thread handle 69649048064, query id 5511678319 localhost nodeny updating
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id
*** (2) TRANSACTION:
TRANSACTION 5235291913, ACTIVE 169 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 1
100 lock struct(s), heap size 24784, 1077 row lock(s), undo log entries 652
MySQL thread id 20778679, OS thread handle 69649048064, query id 5511678319 localhost nodeny updating
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 84 page no 154 n bits 392 index PRIMARY of table `nodeny`.`ip_pool` trx id 5235291913 lock_mode X locks rec but not gap
Record lock, heap no 203 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c36; asc    6;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c000001b20ed0; asc ,      ;;
 3: len 4; hex 0a00061d; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a67; asc a Zg;;
 7: len 8; hex 0000000000000ba8; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 204 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c37; asc    7;;
 1: len 6; hex 0001380c4cfb; asc   8 L ;;
 2: len 7; hex 3a000002ac1674; asc :     t;;
 3: len 4; hex 0a00069f; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a07; asc a Z ;;
 7: len 8; hex 000000000000019f; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 215 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c42; asc    B;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c000001af279c; asc ,    ' ;;
 3: len 4; hex 0a0006f9; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a05; asc a Z ;;
 7: len 8; hex 0000000000000bca; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 252 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c67; asc    g;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c000001911ed3; asc ,      ;;
 3: len 4; hex 0a000674; asc    t;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a65; asc a Ze;;
 7: len 8; hex 0000000000000712; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;
Record lock, heap no 287 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c8a; asc     ;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c0000018b2d37; asc ,    -7;;
 3: len 4; hex 0a00069a; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a52; asc a ZR;;
 7: len 8; hex 0000000000000a3c; asc        <;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 291 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c8e; asc     ;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c0000018b356c; asc ,    5l;;
 3: len 4; hex 0a0006af; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a54; asc a ZT;;
 7: len 8; hex 0000000000000e36; asc        6;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 12 n bits 264 index PRIMARY of table `nodeny`.`ip_pool` trx id 5235291913 lock_mode X locks rec but not gap waiting
Record lock, heap no 110 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 0000068d; asc     ;;
 1: len 6; hex 0001380c3af8; asc   8 : ;;
 2: len 7; hex 4e000002ba0865; asc N     e;;
 3: len 4; hex 0a010176; asc    v;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a04; asc a Z ;;
 7: len 8; hex 0000000000000dba; asc         ;;
 8: len 9; hex 2c766c616e3430312c; asc ,vlan401,;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 5235303911
Purge done for trx's n:o < 5235298554 undo n:o < 0 state: running but idle
History list length 2301
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281509535383984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535380368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535357768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535374944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535385792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535370424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535359576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535365000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5235303423, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778694, OS thread handle 69649041664, query id 5511690085 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235303423 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

------------------
---TRANSACTION 5235303212, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778608, OS thread handle 70605841152, query id 5511686919 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235303212 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

------------------
---TRANSACTION 5235302935, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778688, OS thread handle 69712572416, query id 5511682638 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235302935 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

------------------
---TRANSACTION 5235302753, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778676, OS thread handle 69712576256, query id 5511680596 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235302753 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

Если я правильно понимаю то изза этой функции у меня лочится

Код:

CREATE DEFINER=`root`@`localhost` FUNCTION `get_ip_by_tag`( user_id INTEGER UNSIGNED, tag VARCHAR(64) ) RETURNS varchar(15) CHARSET utf8
    NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15) DEFAULT 0;
    DECLARE row_cnt INTEGER;
    DECLARE ip_id INTEGER;
    DECLARE tries INTEGER DEFAULT 30;
    DECLARE id_min INTEGER;
    DECLARE id_max INTEGER;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';

    SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND tags LIKE CONCAT('%,', tag, ',%')
        LIMIT 1;

    IF( ip_id IS NOT NULL) THEN
        UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id;
        SELECT ROW_COUNT() INTO row_cnt;
        IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
    END IF;

    SELECT MAX(id), MIN(id) INTO id_max, id_min
        FROM ip_pool
        WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND tags LIKE CONCAT('%,', tag, ',%');

    sel_ip: WHILE tries > 0 DO
        SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
            FROM ip_pool
            WHERE uid = 0
                AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
                AND id <= id_max
                LIMIT 1;
        IF( user_ip IS NOT NULL) THEN
            UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
                WHERE id = ip_id AND uid = 0;
            SELECT ROW_COUNT() INTO row_cnt;
            IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
            SET tries = tries - 5;
        END IF;
        SET tries = tries - 1;
    END WHILE;

END

База стоит на машине с Freebsd 12.1 Release, Mysql 5.7
Если кто знает как можно поправить буду очень благодарен.
Заранее спасибо!

Здравствуйте!
Надежней конечно по bgp принимать но только если у Вас нету своей AS то скорее всего они будут давать вам серую сеть для пиринга и серую автономку,тоесть нужно будет поднимать ibgp  а там есть некоторые нюансы.
Проще принимать по второму влану, но только тогда нужно придумать скриптик который будет это мониторить и при падении одной сесии переходить на другую.
BDCOM в качестве BGP не слышал чтобы ктото использовал, но если принимать дефолт то вполне я думаю должен работать но только если вы в будущем не планируете все же регистрировать автономку. Если планируете то для bgp лучше что то более предназначеное для bgp как вам советуют ребята.
По части Feebsd для BGP, возможно но стабильность не очень, Вам прийдется постоянно что то подкручивать.

Добрый день! Сам с Accel игрался немало. Отправлять нужно так как Стас реализовал для mpd:

Код:

accounting {
        sql
        exec
        if("%{sql:SELECT IF((SELECT 1 FROM v_ips i JOIN users u ON i.uid=u.id WHERE u.name='%{User-Name}' AND i.ip='%{Framed-IP-Address}'),1,0)}" < 1){
            update reply {
                mpd-drop-user = 1
            }
        }
    }

Это я вытащил с документации http_://nodeny.com.ua/wiki/index.php/Pppoe_(mpd5_%2B_freeradius_3)
По такому принципу я думаю было бы логично, но как это реализовать для Accel пока не придумал.

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

Добрый вечер! Столкнулся с Дедлоком у одного из обслуживаемых серверов.

Код:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-11-02 20:37:00 923e24000
*** (1) TRANSACTION:
TRANSACTION 11319377117, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 42 row lock(s)
MySQL thread id 27269, OS thread handle 0x91f1de800, query id 301542117 localhost nodeny updating
UPDATE ip_pool SET uid=0 WHERE type='dynamic' AND uid>0 AND `release`<UNIX_TIMESTAMP()
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 9 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id 11319377117 lock_mode X waiting
Record lock, heap no 43 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000327; asc    ';;
 1: len 6; hex 0000e08bedb8; asc       ;;
 2: len 7; hex 19000002ac0eb4; asc        ;;
 3: len 4; hex 0a0a002b; asc    +;;
 4: len 1; hex 01; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 566573df; asc Ves ;;
 7: len 8; hex 0000000000000000; asc         ;;
 8: len 0; hex ; asc ;;

*** (2) TRANSACTION:
TRANSACTION 11319377116, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 1
10 lock struct(s), heap size 2936, 1934 row lock(s)
MySQL thread id 27585, OS thread handle 0x923e24000, query id 301542120 localhost nodeny init
UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300 
        WHERE (uid = 0 OR uid = user_id)
            AND type = 'dynamic'
            AND realip = IF(real_ip>0,1,0)
        ORDER BY uid DESC, ip ASC LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1 page no 9 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id 11319377116 lock_mode X locks rec but not gap
Record lock, heap no 43 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000327; asc    ';;
 1: len 6; hex 0000e08bedb8; asc       ;;
 2: len 7; hex 19000002ac0eb4; asc        ;;
 3: len 4; hex 0a0a002b; asc    +;;
 4: len 1; hex 01; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 566573df; asc Ves ;;
 7: len 8; hex 0000000000000000; asc         ;;
 8: len 0; hex ; asc ;;

Что с ним делать пока незнаю, подскажите пожалуйста что возможно сделать.
Так как система на этом сервере freebsd 10.2 а mysql 5.6 и Nodeny Plus еще одна из первых версий, тоесть довольно старая то у меня вариант только обновить все до более нового и перенастроить по-новому.
Подскажите ребята может ли помочь это? Заранее спасибо.

Спасибо большое Станислав что посказали и разьяснили. Я думаю теперь уже мы спокойно перенесем биллинг в продакшин и будем работать уже на Nodeny+.

Доброе утро. Проверили под нагрузкой, начали с утра сначала все было нормально, ошибок в radius log не было. После запуска noserver на первом брасе начали появляться локи в базе, хотя в radius log ошибок небыло.
После запуска noserver на втором брасе пару раз появились ошибки в radius.log
Error: (156054) Ignoring duplicate packet from client bras port 59195 - ID: 140 due to unfinished request in component accounting module sql
И локов стало больше частота появления примерно раз в 10 секунд.
Посли запуска noserver на всех брасах ошибок в radius.log не наблюдаю хотя локи есть но они другие:

Код:

| InnoDB |      | 
=====================================
2020-07-25 06:26:20 0xcf12b4900 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2917 srv_active, 0 srv_shutdown, 185 srv_idle
srv_master_thread log flush and writes: 3102
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 62112
--Thread 55580940288 has waited at trx0undo.ic line 179 for 0  seconds the semaphore:
X-lock on RW-latch at 0x97084ffd8 created in file buf0buf.cc line 1468
a writer (thread id 56074836480) has reserved it in mode  SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file trx0undo.ic line 198
Last time write locked in file /usr/ports/databases/mysql57-server/work/mysql-5.7.29/storage/innobase/buf/buf0flu.cc line 1206
--Thread 56075652352 has waited at trx0purge.cc line 162 for 0  seconds the semaphore:
Mutex at 0xd0e5f7d60, Mutex REDO_RSEG created trx0rseg.cc:219, lock var 1

OS WAIT ARRAY INFO: signal count 56178
RW-shared spins 0, rounds 24740, OS waits 3013
RW-excl spins 0, rounds 653054, OS waits 5511
RW-sx spins 7412, rounds 157855, OS waits 1990
Spin rounds per wait: 24740.00 RW-shared, 653054.00 RW-excl, 21.30 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 17566701
Purge done for trx's n:o < 17566687 undo n:o < 0 state: running
History list length 63
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281510627798560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627819352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627803080, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627800368, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627799464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627821160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627813928, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627796752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627787712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627820256, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627794040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627790424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627789520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627810312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627818448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627817544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627795848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627809408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627813024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627785904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 17566700, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 344, OS thread handle 55581702400, query id 3549477 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566699, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 350, OS thread handle 55580946688, query id 3549470 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566698, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 354, OS thread handle 55581354496, query id 3549463 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566697, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 349, OS thread handle 55581708800, query id 3549462 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566696, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 325, OS thread handle 55580942848, query id 3549449 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566695, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 316, OS thread handle 55580944128, query id 3549442 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566694, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 324, OS thread handle 55581703680, query id 3549435 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566693, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 258, OS thread handle 55580936448, query id 3549433 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566692, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 341, OS thread handle 55580947968, query id 3549431 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566691, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 347, OS thread handle 55580945408, query id 3549414 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566690, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 328, OS thread handle 55580937728, query id 3549412 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566689, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 340, OS thread handle 55581710080, query id 3549404 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566688, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 333, OS thread handle 55585752064, query id 3549393 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566683, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 315, OS thread handle 55581353216, query id 3549386 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 17566682, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 22, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 1
27343 OS file reads, 33590 OS file writes, 8048 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 14.64 writes/s, 2.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5501, seg size 5503, 80 merges
merged operations:
 insert 80, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4515457, node heap has 424 buffer(s)
Hash table size 4515457, node heap has 1 buffer(s)
Hash table size 4515457, node heap has 19 buffer(s)
Hash table size 4515457, node heap has 1739 buffer(s)
Hash table size 4515457, node heap has 6 buffer(s)
Hash table size 4515457, node heap has 24 buffer(s)
Hash table size 4515457, node heap has 50 buffer(s)
Hash table size 4515457, node heap has 19 buffer(s)
46370.97 hash searches/s, 1476.50 non-hash searches/s
---
LOG
---
Log sequence number 40918580532
Log flushed up to   40918571686
Pages flushed up to 40910301356
Last checkpoint at  40909448935
1 pending log flushes, 0 pending chkp writes
4027 log i/o's done, 1.18 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 18690342912
Dictionary memory allocated 269725
Buffer pool size   1114049
Free buffers       1083517
Database pages     28250
Old database pages 10337
Modified db pages  789
Pending reads      0
Pending writes: LRU 0, flush list 34, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 27291, created 959, written 27899
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 28250, unzip_LRU len: 0
I/O sum[0]:cur[1376], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   139259
Free buffers       135225
Database pages     3747
Old database pages 1365
Modified db pages  71
Pending reads      0
Pending writes: LRU 0, flush list 3, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3643, created 104, written 3890
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3747, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   139264
Free buffers       135456
Database pages     3523
Old database pages 1309
Modified db pages  28
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3458, created 65, written 2360
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3523, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   139247
Free buffers       135334
Database pages     3628
Old database pages 1319
Modified db pages  144
Pending reads      0
Pending writes: LRU 0, flush list 3, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3405, created 223, written 3662
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3628, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   139264
Free buffers       135572
Database pages     3407
Old database pages 1264
Modified db pages  83
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3370, created 37, written 4604
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3407, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   139248
Free buffers       135494
Database pages     3469
Old database pages 1267
Modified db pages  135
Pending reads      0
Pending writes: LRU 0, flush list 6, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3350, created 119, written 3420
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3469, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   139261
Free buffers       135304
Database pages     3671
Old database pages 1335
Modified db pages  135
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3484, created 187, written 3693
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3671, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   139256
Free buffers       135543
Database pages     3428
Old database pages 1247
Modified db pages  120
Pending reads      0
Pending writes: LRU 0, flush list 22, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3300, created 128, written 3227
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3428, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   139250
Free buffers       135589
Database pages     3377
Old database pages 1231
Modified db pages  73
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3281, created 96, written 3043
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3377, unzip_LRU len: 0
I/O sum[0]:cur[172], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1626, Main thread ID=56075648512, state: sleeping
Number of rows inserted 23479162, updated 312491, deleted 557, read 120725277
25449.23 inserts/s, 116.99 updates/s, 0.27 deletes/s, 111452.41 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Спасибо большое Станислав. Потестируем, надеюсь єто решит нашу проблему.
Как будут результаты отпишусь.

Доброе утро! Тест под нагрузкой. Вижу локи есть.

Код:

mysql> SHOW ENGINE INNODB STATUS;
| InnoDB |      | 
=====================================
2020-07-22 06:59:44 0xd1658d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2990 srv_active, 0 srv_shutdown, 175 srv_idle
srv_master_thread log flush and writes: 3165
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1993509
OS WAIT ARRAY INFO: signal count 1190120
RW-shared spins 0, rounds 408390, OS waits 148383
RW-excl spins 0, rounds 14955663, OS waits 118485
RW-sx spins 57739, rounds 1304243, OS waits 21902
Spin rounds per wait: 408390.00 RW-shared, 14955663.00 RW-excl, 22.59 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 15463958
Purge done for trx's n:o < 15463956 undo n:o < 0 state: running but idle
History list length 31
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281510627797656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627792232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627801272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627821160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627815736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627814832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627813024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627811216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627800368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627794944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627794040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627793136, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627791328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627790424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627809408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627805792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627789520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627813928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627810312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627804888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627798560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627796752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627795848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627786808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627819352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627817544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627807600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627818448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627802176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627803984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627816640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627785904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15463954, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 491, OS thread handle 56266637568, query id 3347250 localhost nodeny update
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 698 page no 217 n bits 200 index PRIMARY of table `bill`.`auth_now` trx id 15463954 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 15463953, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 483, OS thread handle 56209500928, query id 3347243 localhost nodeny update
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 698 page no 217 n bits 200 index PRIMARY of table `bill`.`auth_now` trx id 15463953 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 15463952, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 433, OS thread handle 56266636288, query id 3347236 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
---TRANSACTION 15463948, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 492, OS thread handle 56290334976, query id 3347225 localhost nodeny update
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 698 page no 217 n bits 200 index PRIMARY of table `bill`.`auth_now` trx id 15463948 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 15463936, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 500, OS thread handle 56290333696, query id 3347191 localhost nodeny query end
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = auth_properties,
        last = UNIX_TIMESTAMP()
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
18166 OS file reads, 52058 OS file writes, 16908 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 30.83 writes/s, 8.45 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5501, seg size 5503, 45 merges
merged operations:
 insert 249, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4515457, node heap has 427 buffer(s)
Hash table size 4515457, node heap has 1 buffer(s)
Hash table size 4515457, node heap has 28 buffer(s)
Hash table size 4515457, node heap has 1731 buffer(s)
Hash table size 4515457, node heap has 17 buffer(s)
Hash table size 4515457, node heap has 23 buffer(s)
Hash table size 4515457, node heap has 50 buffer(s)
Hash table size 4515457, node heap has 20 buffer(s)
50469.95 hash searches/s, 3528.31 non-hash searches/s
---
LOG
---
Log sequence number 40001362513
Log flushed up to   40001349486
Pages flushed up to 39994056380
Last checkpoint at  39994054438
0 pending log flushes, 0 pending chkp writes
5060 log i/o's done, 1.98 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 18690342912
Dictionary memory allocated 271757
Buffer pool size   1114049
Free buffers       1092671
Database pages     19081
Old database pages 6975
Modified db pages  842
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18125, created 956, written 41747
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 19081, unzip_LRU len: 0
I/O sum[0]:cur[88], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   139259
Free buffers       136415
Database pages     2557
Old database pages 940
Modified db pages  113
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2493, created 64, written 5323
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2557, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   139264
Free buffers       136658
Database pages     2319
Old database pages 853
Modified db pages  45
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2257, created 62, written 1595
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2319, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   139247
Free buffers       136298
Database pages     2664
Old database pages 963
Modified db pages  204
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2430, created 234, written 8250
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2664, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   139264
Free buffers       136661
Database pages     2315
Old database pages 841
Modified db pages  99
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2190, created 125, written 7122
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2315, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   139248
Free buffers       136700
Database pages     2260
Old database pages 838
Modified db pages  81
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2177, created 83, written 4755
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2260, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   139261
Free buffers       136492
Database pages     2483
Old database pages 910
Modified db pages  125
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2340, created 143, written 5526
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2483, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   139256
Free buffers       136703
Database pages     2265
Old database pages 816
Modified db pages  90
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2099, created 166, written 5341
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2265, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   139250
Free buffers       136744
Database pages     2218
Old database pages 814
Modified db pages  85
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2139, created 79, written 3835
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2218, unzip_LRU len: 0
I/O sum[0]:cur[11], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=1632, Main thread ID=56075681280, state: sleeping
Number of rows inserted 16993929, updated 261585, deleted 9309, read 94575409
27388.18 inserts/s, 103.17 updates/s, 0.17 deletes/s, 122145.06 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Добрый день Станислав!

Код:

mysql> SHOW ENGINE INNODB STATUS;
| InnoDB |      | 
=====================================
2020-07-21 13:22:45 0xd193d8300 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 522 srv_active, 0 srv_shutdown, 5590 srv_idle
srv_master_thread log flush and writes: 6112
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 66902
OS WAIT ARRAY INFO: signal count 55705
RW-shared spins 0, rounds 1913, OS waits 543
RW-excl spins 0, rounds 34504, OS waits 717
RW-sx spins 101, rounds 2448, OS waits 32
Spin rounds per wait: 1913.00 RW-shared, 34504.00 RW-excl, 24.24 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 14220109
Purge done for trx's n:o < 14220099 undo n:o < 0 state: running but idle
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281510627802176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627801272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627800368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627799464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627798560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627797656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627796752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627795848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627794944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627794040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627793136, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627792232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627791328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627790424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627787712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627788616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627789520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627786808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281510627785904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
8374 OS file reads, 12340 OS file writes, 6610 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5501, seg size 5503, 17 merges
merged operations:
 insert 1513, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4515341, node heap has 422 buffer(s)
Hash table size 4515341, node heap has 1 buffer(s)
Hash table size 4515341, node heap has 19 buffer(s)
Hash table size 4515341, node heap has 3 buffer(s)
Hash table size 4515341, node heap has 16 buffer(s)
Hash table size 4515341, node heap has 22 buffer(s)
Hash table size 4515341, node heap has 50 buffer(s)
Hash table size 4515341, node heap has 19 buffer(s)
835.54 hash searches/s, 11.44 non-hash searches/s
---
LOG
---
Log sequence number 39656901458
Log flushed up to   39656901458
Pages flushed up to 39656901458
Last checkpoint at  39656901449
0 pending log flushes, 0 pending chkp writes
1396 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 18690342912
Dictionary memory allocated 271757
Buffer pool size   1114029
Free buffers       1104616
Database pages     8861
Old database pages 3275
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 31, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8334, created 527, written 8558
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8861, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   139254
Free buffers       137972
Database pages     1211
Old database pages 467
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1173, created 38, written 1258
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1211, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   139261
Free buffers       138177
Database pages     1015
Old database pages 373
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 978, created 37, written 344
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1015, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   139248
Free buffers       137984
Database pages     1192
Old database pages 423
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1067, created 125, written 2257
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1192, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   139261
Free buffers       138158
Database pages     1035
Old database pages 377
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 969, created 66, written 1482
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1035, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   139248
Free buffers       138117
Database pages     1060
Old database pages 401
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1013, created 47, written 1014
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1060, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   139252
Free buffers       137985
Database pages     1198
Old database pages 447
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1124, created 74, written 735
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1198, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   139248
Free buffers       138120
Database pages     1061
Old database pages 394
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1012, created 49, written 674
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1061, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   139257
Free buffers       138103
Database pages     1089
Old database pages 393
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 998, created 91, written 794
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1089, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=12763, Main thread ID=56075682560, state: sleeping
Number of rows inserted 67230, updated 38, deleted 33511, read 22567239
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 3296.46 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Но это без нагрузки и еще немного подтюнили, мне Михаил подсказал что можно попробовать.
Завтра утром попробуем и тогда напишу получилось или нет.

Доброго времени суток! Решили обратиться на форум так как у самих уже не выходит. История такая мы переходили с Nodeny 50.33 на Nodeny Plus с переходом нам помогли сам процесс проходит нормально и вроди все работало нормально на стенде. Но когда пришло время выкатить в продакшин всплыли проблемы с тюнингом. База данных mysql 5.7 стоит на Freebsd 12.1 машина с 4 ядрами 24 Гига оперативки freeradius 3, авторизация pppoe (mpd5) на отдельных брасах.
При выкатывании машины в продакшин начали появляться дисконекты по логу с радиус сервера определили что упирается в лимиты и подтюнили радиус вот конфик с тюнингом:

Код:

sql {
    driver = "rlm_sql_mysql"
    mysql {
        warnings = auto
    }
    server = "localhost"
    port = 3306
    login = "XXXXXX"
    password = "XXXXXXXXX"
    radius_db = "nod"

    authorize_check_query = "call radcheck('%{User-Name}', 1)"
    authorize_reply_query = "call radreply('%{User-Name}', '%{User-Password}')"
    accounting {
        reference = "%{tolower:type.%{Acct-Status-Type}.query}"
        type {
            start {
                query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
            }
            interim-update {
                query = "${..start.query}"
            }
            stop {
                query = "call radstop('%{User-Name}', '%{Framed-IP-Address}')"
            }
        }
    }
    post-auth {
        query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',\
                'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
    }
 pool {
        start = ${thread[pool].start_servers}
        min = ${thread[pool].min_spare_servers}
        max = ${thread[pool].max_servers}
        spare = ${thread[pool].max_spare_servers}
        uses = 0
        retry_delay = 30
        lifetime = 0
        idle_timeout = 60
        }

При небольшом нагрузочном тестировании все было нормально после этого фикса но когда ночью попробовали опять в продакшин то в радиус лог начали сыпать ошибки:
 Error: (1399273) Ignoring duplicate packet from client bras port 64555 - ID: 15 due to unfinished request in component <core> module <queue>
WARNING: (0) sql: WARNING: Cannot do check groups when group_membership_query is not set

Немного погуглив решили поставить немного больше таймаут на mpd:

Код:

radius:
        set radius server XX.XX.XX.XX hardpass 1812 1813
        set radius retries 3
        [color=orange][b]set radius timeout 40[/b][/color]
        set radius me XX.XX.XX.XX
        set auth acct-update 45
        set auth enable radius-auth
        set auth enable radius-acct
        set radius enable message-authentic

Это нам вначале помогло и в логе кроме ошибки
WARNING: (0) sql: WARNING: Cannot do check groups when group_membership_query is not set
ничего не было но недолго.
Буквльно через час когда уже было утро и авторизованных клиентов стало добавляться (в среднем на пике 7000 авторизованых) опять начали сыпать ошибки в радиус лог:

Код:

Error: Unresponsive child for request 1394975, in component accounting module sql
WARNING: (1394876) WARNING: Module rlm_sql became unblocked
Error: 23 requests have been waiting in the processing queue for 38 seconds.  Check that all databases are running properly!

и очень редко ошибка
Error: (1399273) Ignoring duplicate packet from client bras port 64555 - ID: 15 due to unfinished request in component <core> module <queue>

Судя по сообщениям похоже на то что нужен тюнинг mysql или настроек радиуса связанных с mysql.
Насколько сам смог а что ребята подсказали mysql подтюнили но эфекта пока нету, вот мой my.cnf

Код:

[mysqld]
innodb_buffer_pool_size     = 16512M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

log_bin_trust_function_creators = 1
#key_buffer = 1200M
thread_stack = 512K
thread_cache_size = 32
#table_cache = 60000
net_buffer_length = 32
query_cache_limit = 2M
query_cache_size = 1024M
myisam_sort_buffer_size = 256M
tmp_table_size = 256M
max_heap_table_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
sort_buffer_size = 2M
join_buffer_size = 4M
max_allowed_packet = 1024M
max_sort_length = 512
max_connections = 7000
#max_connect_errors = 50
#max_user_connections = 25


#thread_concurrency = 12
skip-character-set-client-handshake
skip-name-resolve
log-bin=mysql-bin
max_binlog_size= 500M
expire_logs_days = 3

server-id  = 1
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

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

P.S. Да кстати с этой нагрузкой на этой же машине Nodeny 50.33 практически без никакого тюнинга работает без проблем.

Спасибо большое за ответ. Но из этого вытекает еще один вопрос. У нас есть на 2 офисах свои точки продажи. Получается чтобы закрыть дневную кассу за день нужно передавать карточки на склад а на следующий день те что не продали забирать со склада? Если я правильно Вас понял.
Заранее Спасибо!

Всем Добрый день! Мы в процессе обновления с Nodeny 50.33 до Nodeny + последней ревизии. По ходу перехода у нас возник вопрос. В старой версии Nodeny 50.33 в работе карточек пополнения была функция которой мы часто пользуемся это - "продажа карточек пополнения".С помощью нее после передачи на точку продажи при покупке карты пополнения можно было контролировать сколько денег должно быть у администратора на точке и сколько карточек он продал а сколько остались не проданые. А в Nodeny Plus мы к сожалению не нашли такой функции или ее аналога. Подскажите пожалуйста есть ли замена этой функции, или может она реализована в отдельном модуле?
Заранее Спасибо!

Доброе время суток. Может кто сталкивался и может подсказать в чем дело может быть.
Выполняли обновление сервера так как на старом уже мощности не хватало для обслуживания клиентов заодно и софт обновить.
Конфигурация такая:
1)Старый сервер Freebsd 9.2-Release, DHCP Server 4.2.5-P1, Nodeny Plus rev 178, Mysql 5.5.33, Perl v5.14.4
2) Новый сервер Freebsd 11.2-Release, Dhcp Server 4.4.1, Nodeny Plus rev 571, Mysql 5.5.62, Perl v5.28.2
Используем модуль DHCP без Radius выдаем айпи из сети 10.0.0.0/16 каждая подсеть /24 в своем vlan, и все натим в пул с 15 айпи.
На новый сервер база для теста была развернута с последнего бекапа и тестировалась на офисе на нескольких офисных машинах и все работало нормально. Но вот когда попытались запустить новый сервер в продакшин то столкнулись с неожиданной проблемой, некоторые беспроводные клиенты нехотели получать айпи причем что странно в основном это были клиентские антены Rocket, с проводными и Mikrotik клиентами такого не наблюдалось. Если на новом сервере убрать с dhcp.conf опции чтобы запускать команды запуска скриптов модуля и dhcp просто выдает айпи с нужных сетей в ipfw отрыть всем доступ, то и у беспроводных клиентов с Rocket появлялся айпи и начинал работать интернет.
Мы поначалу решили что дело в самом dhcp сервере что в новом dhcp сервере что то не то и сделали схему что база данных,Nodeny ядро,noserver запустили на новом сервере а только dhcp сервер оставили на старой системе. Но это ничего не дало и опять клиенты с Rocket не получали айпи адресов. В чем дело пока понять не могу. Пока сделали такую схему База данных, ядро, dhcp сервер оставлен на старой системе и старом сервере а noserver запущен на новом сервере и весь трафик запущен через новый сервер и все хорошо работает.
Кто сталкивался подскажите пожалуйста в чем может быть дело. Заранее спасибо!

ну или убрать все китайские фильтры. И включить без бесперебойника.