Обрывы авторизации - opt82

[sever]

Возможно deadlock  в mysql возникает. Я решил полностью изменить функцию get_ip, попробуй:

Код:

DROP FUNCTION IF EXISTS `get_ip`;
DELIMITER $$
CREATE FUNCTION `get_ip` ( user_id INTEGER UNSIGNED ) RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15);
    DECLARE row_cnt 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;

    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
        LIMIT 1;

    SELECT ROW_COUNT() INTO row_cnt;
    IF( row_cnt < 1 )
    THEN
        UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
            WHERE uid = 0 AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            LIMIT 1;
    END IF;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id LIMIT 1;
    RETURN user_ip;
END$$
DELIMITER ;

7 дней работы, полёт нормальный.

[enigmaticua]

Такая же проблема была, но с авторизацией PPPoE

[NodenY45]

была ревизия 490 где-то(с тестовым get_ip), решил обновится... обновился до 538.
Возник вопрос, при обновлении биллинга затирается процедура get_ip?
Так как сразу после обновление началась та же проблема с авторизацией клиентов, пришлось откатывать все назад.

SHOW ENGINE INNODB STATUS;

Код:

| InnoDB |      | 
=====================================
2018-11-14 17:27:08 8ed317800 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 31235446 srv_active, 0 srv_shutdown, 11964087 srv_idle
srv_master_thread log flush and writes: 43196359
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 53049314
OS WAIT ARRAY INFO: signal count 1310591012
Mutex spin waits 264315522, rounds 1759251357, OS waits 17867262
RW-shared spins 266194294, rounds 1326272709, OS waits 20645119
RW-excl spins 51865584, rounds 1895994029, OS waits 5600375
Spin rounds per wait: 6.66 mutex, 4.98 RW-shared, 36.56 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 15613001245
Purge done for trx's n:o < 15613001025 undo n:o < 0 state: running but idle
History list length 2425
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 56732089, OS thread handle 0x8ed317800, query id 12341800735 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 15612948267, not started
MySQL thread id 56729141, OS thread handle 0x8ecc81000, query id 12341711029 localhost nodeny cleaning up
---TRANSACTION 15613000903, not started
MySQL thread id 56729131, OS thread handle 0x8ed314c00, query id 12341800170 localhost nodeny cleaning up
---TRANSACTION 15613001243, not started
MySQL thread id 56729052, OS thread handle 0x8ed31e000, query id 12341800733 localhost nodeny cleaning up
--------
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, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
5845739 OS file reads, 4396676861 OS file writes, 4019638482 OS fsyncs
14.56 reads/s, 20814 avg bytes/read, 4.50 writes/s, 2.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 20, seg size 22, 166181 merges
merged operations:
 insert 166162, delete mark 6414, delete 977
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 458 buffer(s)
349853.13 hash searches/s, 2487.22 non-hash searches/s
---
LOG
---
Log sequence number 2769459776761
Log flushed up to   2769459776761
Pages flushed up to 2769459776761
Last checkpoint at  2769459776389
0 pending log writes, 0 pending chkp writes
3946726582 log i/o's done, 1.25 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 290704
Buffer pool size   8192
Free buffers       1023
Database pages     6711
Old database pages 2457
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 67608153, not young 705217011
7.44 youngs/s, 153.93 non-youngs/s
Pages read 7563742, created 86773, written 1297208196
18.50 reads/s, 0.00 creates/s, 3.87 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 4.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6711, unzip_LRU len: 0
I/O sum[3406]:cur[5], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 34405920768, state: sleeping
Number of rows inserted 828888, updated 3903731509, deleted 425801, read 12390587856580
0.44 inserts/s, 0.37 updates/s, 0.00 deletes/s, 435533.15 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[NodenY45]

реально не понятная тема.
сижу у компа, снова киляю зависшие процессы.
нету никакой закономерности, когда оно остановится, может через 20минут, а может через 3.

даже процедуры и функции от сюда прописал http://forum.nodeny.com.ua/index.php?topic=3034.0
не помогли

Вроде локи

Код:

*** (2) TRANSACTION:
TRANSACTION 15613383651, ACTIVE 9 sec updating or deleting
mysql tables in use 2, locked 2
1803 lock struct(s), heap size 276008, 362896 row lock(s), undo log entries 139850
MySQL thread id 56748042, OS thread handle 0x8ed315c00, query id 12342362742 localhost root updating reference tables
UPDATE ip_pool i JOIN temp_ip_pool t ON i.id = t.ip_id SET i.id = t.id
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 242 page no 871 n bits 672 index `ip` of table `nodeny`.`ip_pool` trx id 15613383651 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 0a00fb0f; asc     ;;
 1: len 4; hex 0000ab1a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 0a00fb10; asc     ;;
 1: len 4; hex 0000307c; asc   0|;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 0a00fb10; asc     ;;
 1: len 4; hex 0000ab94; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 0a00fb11; asc     ;;
 1: len 4; hex 000030f6; asc   0 ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 0a00fb11; asc     ;;
 1: len 4; hex 0000ac0e; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 0a00fb12; asc     ;;
 1: len 4; hex 00003170; asc   1p;;
.........................................................
Record lock, heap no 661 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 0a00fc49; asc    I;;
 1: len 4; hex 000017b6; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 242 page no 871 n bits 888 index `ip` of table `nodeny`.`ip_pool` trx id 15613383651 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 417 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 0a00fc48; asc    H;;
 1: len 4; hex 0000689f; asc   h ;;

*** WE ROLL BACK TRANSACTION (1)

[NodenY45]

Вот такие вот обрывы авторизации....

[enigmaticua]

Люди добрые, ткните носом куда смотреть и что делать, внезапно погнали обрывы авторизаций PPPoE
Визуально на скрине...

[Cell]

Ребутни мускул. Если на какое-то время поможет - то в нем дело. Если фиолетово, то это скорее всего какой-то флуд в сети.

[enigmaticua]

Ребутни мускул. Если на какое-то время поможет - то в нем дело. Если фиолетово, то это скорее всего какой-то флуд в сети.

Да, это так.

Подтюнили, стало значительно лучше, но ошибки есть все равно:

Error: rlm_sql (sql): No connections available and at max connection limit

Вот параметры:

# tuning @2019-06-26
#
# on restart
skip_name_resolve
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=3G
innodb_additional_mem_pool_size=3G
innodb_log_file_size=1G
innodb_log_buffer_size=256M
#
# on fly
#innodb_flush_log_at_trx_commit=2
query_cache_limit=256M
query_cache_size=256M
join_buffer_size=256M
max_heap_table_size=1024M
tmp_table_size=1024M
thread_cache_size=200

Файл : /usr/local/etc/mysql/my.cnf

[mysqldump]
quick
max_allowed_packet = 16M

[Cell]

Все просто. В консоли мускула команда
show processlist
 
В идеальном виде должна быть картина типа

Код:

mysql> show processlist;
+--------+------------+------------------------------+--------+---------+-------+----------+------------------+
| Id     | User       | Host                         | db     | Command | Time  | State    | Info             |
+--------+------------+------------------------------+--------+---------+-------+----------+------------------+
|      7 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|      8 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|      9 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     10 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     11 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     12 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     13 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     14 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     15 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     16 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     17 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     18 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     19 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     20 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     21 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     22 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     23 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     24 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     25 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
|     26 | nodeny     | localhost                    | nodeny | Sleep   |     0 |          | NULL             |
.......

Если в столбце Time имеются какие-то нездоровые числа больше пары секунд а столбце Info какой-то запрос, то вот этот запрос и гомнит всю систему и его нужно упрощать. Искать в какой он процедуре... и что-то с этим делать. Команду можно повторять часто и наблюдать за процессом в динамике.