Mikrotik API

[YuSHa]

Код:

mysql> SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
140212 11:23:54 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 184143 1_second, 184142 sleeps, 18341 10_second, 732 background, 731 flush
srv_master_thread log flush and writes: 184206
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1867, signal count 1957
Mutex spin waits 22749, rounds 56076, OS waits 415
RW-shared spins 1453, rounds 41278, OS waits 1329
RW-excl spins 46, rounds 2207, OS waits 58
Spin rounds per wait: 2.46 mutex, 28.41 RW-shared, 47.98 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
140210  8:07:48
*** (1) TRANSACTION:
TRANSACTION 7F3902, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 20 row lock(s)
MySQL thread id 13, OS thread handle 0x8272fec00, query id 4266 localhost nodeny Searching rows for update
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 0 page no 365 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id 7F3902 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013f6; asc     ;;
 1: len 6; hex 0000007f38a0; asc     8 ;;
 2: len 7; hex 190000022b1cc6; asc     +  ;;
 3: len 4; hex ac100003; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86dde; asc R m ;;
 7: len 8; hex 0000000000000028; asc        (;;
 8: len 0; hex ; asc ;;

*** (2) TRANSACTION:
TRANSACTION 7F38F8, ACTIVE 0 sec fetching rows
mysql tables in use 4, locked 3
5 lock struct(s), heap size 3112, 29 row lock(s)
MySQL thread id 10, OS thread handle 0x8272fe000, query id 4254 localhost nodeny init
UPDATE ip_pool SET uid =  NAME_CONST('user_id',1647), `release` = UNIX_TIMESTAMP() + 300
        WHERE (uid = 0 OR uid =  NAME_CONST('user_id',1647))
            AND type = 'dynamic'
            AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
        ORDER BY uid DESC, id ASC LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 365 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id 7F38F8 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013f4; asc     ;;
 1: len 6; hex 0000007f2746; asc     'F;;
 2: len 7; hex 07000003b11737; asc       7;;
 3: len 4; hex ac100001; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86cdb; asc R l ;;
 7: len 8; hex 0000000000000311; asc         ;;
 8: len 0; hex ; asc ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013f5; asc     ;;
 1: len 6; hex 0000007f3834; asc     84;;
 2: len 7; hex 4d0000045c10b4; asc M   \  ;;
 3: len 4; hex ac100002; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86ddb; asc R m ;;
 7: len 8; hex 00000000000001b0; asc         ;;
 8: len 0; hex ; asc ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013f6; asc     ;;
 1: len 6; hex 0000007f38a0; asc     8 ;;
 2: len 7; hex 190000022b1cc6; asc     +  ;;
 3: len 4; hex ac100003; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86dde; asc R m ;;
 7: len 8; hex 0000000000000028; asc        (;;
 8: len 0; hex ; asc ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013f7; asc     ;;
 1: len 6; hex 0000007f2ff7; asc     / ;;
 2: len 7; hex 61000004640ad4; asc a   d  ;;
 3: len 4; hex ac100004; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86d17; asc R m ;;
 7: len 8; hex 000000000000077b; asc        {;;
 8: len 0; hex ; asc ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013f8; asc     ;;
 1: len 6; hex 0000007f33cb; asc     3 ;;
 2: len 7; hex 11000001602401; asc     `$ ;;
 3: len 4; hex ac100005; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86dbd; asc R m ;;
 7: len 8; hex 000000000000026e; asc        n;;
 8: len 0; hex ; asc ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013f9; asc     ;;
 1: len 6; hex 0000007f32d5; asc     2 ;;
 2: len 7; hex 7e0000038121ac; asc ~    ! ;;
 3: len 4; hex ac100006; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86dbc; asc R m ;;
 7: len 8; hex 000000000000044e; asc        N;;
 8: len 0; hex ; asc ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013fa; asc     ;;
 1: len 6; hex 0000007f3052; asc     0R;;
 2: len 7; hex 200000038d1d19; asc        ;;
 3: len 4; hex ac100007; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86d1c; asc R m ;;
 7: len 8; hex 000000000000032e; asc        .;;
 8: len 0; hex ; asc ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013fb; asc     ;;
 1: len 6; hex 0000007f2642; asc     &B;;
 2: len 7; hex 59000002002293; asc Y    " ;;
 3: len 4; hex ac100008; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86cd5; asc R l ;;
 7: len 8; hex 0000000000000777; asc        w;;
 8: len 0; hex ; asc ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013fc; asc     ;;
 1: len 6; hex 0000007f2352; asc     #R;;
 2: len 7; hex 550000039b1054; asc U     T;;
 3: len 4; hex ac100009; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86cbc; asc R l ;;
 7: len 8; hex 0000000000000669; asc        i;;
 8: len 0; hex ; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 365 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id 7F38F8 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000013fd; asc     ;;
 1: len 6; hex 0000007f2246; asc     "F;;
 2: len 7; hex 1f00000399168b; asc        ;;
 3: len 4; hex ac10000a; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 52f86cb7; asc R l ;;
 7: len 8; hex 000000000000000d; asc         ;;
 8: len 0; hex ; asc ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 8173F6
Purge done for trx's n:o < 816F1C undo n:o < 0
History list length 1899
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3301, OS thread handle 0x827300000, query id 538144 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 8173F5, not started
MySQL thread id 13, OS thread handle 0x8272fec00, query id 538137 localhost nodeny
---TRANSACTION 7F5401, not started
MySQL thread id 4, OS thread handle 0x8272fc800, query id 538128 localhost nodeny
---TRANSACTION 816869, not started
MySQL thread id 1, OS thread handle 0x8272fbc00, query id 538143 localhost nodeny
---TRANSACTION 816F28, not started
MySQL thread id 2, OS thread handle 0x8272fc000, query id 538134 localhost nodeny
---TRANSACTION 816F22, not started
MySQL thread id 5, OS thread handle 0x8272fcc00, query id 538110 localhost nodeny
---TRANSACTION 81686A, not started
MySQL thread id 3, OS thread handle 0x8272fc400, query id 538140 localhost nodeny
--------
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
774 OS file reads, 20281 OS file writes, 9272 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 0, seg size 2, 5 merges
merged operations:
 insert 305, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 36 buffer(s)
8.20 hash searches/s, 6.00 non-hash searches/s
---
LOG
---
Log sequence number 188680511
Log flushed up to   188680511
Last checkpoint at  188680511
0 pending log writes, 0 pending chkp writes
5717 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 202542
Buffer pool size   8192
Free buffers       7295
Database pages     861
Old database pages 317
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 764, created 97, written 16630
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: 861, 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
1 read views open inside InnoDB
Main thread id 34401729536, state: sleeping
Number of rows inserted 2082, updated 3135, deleted 1380, read 83562130
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 365.79 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

ERROR:
No query specified

mysql>

[Efendy]

Понятно. Видел уже эту проблему - мускул попадает в дедлок, когда по логике не должен. Пока временно переведи всех на статические ip, а я подумаю об изменении процедуры выдачи ip  чтобы в дедлок не попадало

[YuSHa]

есть подвижки? а то очень не удобно(((

[Efendy]

давно пофикшено

[YuSHa]

спс

[YuSHa]

дабавил всем статику, запустилось всего 700 онлайн из 900 на микротике, в биллинге при этом было авторизировано не более 200 учеток онлайна(хотя маков по регистрации pppoe насыпало 254 шт)...причем то прыгало то чуть больше то чуть меньше....такое чувство что пачками оваливались.

потом удалил статику у всех. поднялось 100 учеток и все(

Код:

[root@bill ~]# ps ax | grep pl
1359 ??  Ss     0:37.80 /usr/local/bin/flow-capture -R /var/db/flows/netflow_8888.pl -p /var/run/flow-capture/flow-capture.pid -w /var/db/flows -n1 -N0 0.0.
1406 ??  Ss     0:00.57 /usr/local/bin/perl /usr/local/lib/webmin/miniserv.pl /usr/local/etc/webmin/miniserv.conf
4354 ??  I      0:21.12 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --plugin-dir=/usr/
1360 v0- S      0:12.14 /usr/bin/perl /usr/local/nodeny/nokernel.pl -d (perl5.14.4)
1361 v0- S      1:35.20 /usr/bin/perl /usr/local/nodeny/noserver.pl -d (perl5.14.4)
1362 v0- S      0:35.77 /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=collectors -d (perl5.14.4)
1363 v0- S      0:59.04 /usr/bin/perl /usr/local/nodeny/nokernel.pl -m=cap -d (perl5.14.4)
1364 v0- S      2:38.25 /usr/bin/perl /usr/local/nodeny/noserver.pl -g=mikrotik_82.cfg.pm -d (perl5.14.4)
1365 v0- S      2:38.60 /usr/bin/perl /usr/local/nodeny/noserver.pl -g=mikrotik_84.cfg.pm -d (perl5.14.4)
1366 v0- S      2:36.85 /usr/bin/perl /usr/local/nodeny/noserver.pl -g=mikrotik_250.cfg.pm -d (perl5.14.4)
4591  5  RL+    0:00.00 grep pl
[root@bill ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.28 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use nodeny;
Database changed
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
140329  7:29:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 662 1_second, 661 sleeps, 66 10_second, 2 background, 2 flush
srv_master_thread log flush and writes: 669
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 319, signal count 360
Mutex spin waits 7099, rounds 32155, OS waits 153
RW-shared spins 193, rounds 4524, OS waits 101
RW-excl spins 11, rounds 538, OS waits 10
Spin rounds per wait: 4.53 mutex, 23.44 RW-shared, 48.91 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
140329  7:25:53
*** (1) TRANSACTION:
TRANSACTION ADE886, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 66 row lock(s)
MySQL thread id 3, OS thread handle 0x8277ef800, query id 11390 localhost nodeny Searching rows for update
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 0 page no 465 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id ADE886 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000028ee; asc   ( ;;
 1: len 6; hex 000000ade777; asc      w;;
 2: len 7; hex 1b0000020a08c5; asc        ;;
 3: len 4; hex 0a640002; asc  d  ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 53365a5e; asc S6Z^;;
 7: len 8; hex 0000000000000168; asc        h;;
 8: len 0; hex ; asc ;;

*** (2) TRANSACTION:
TRANSACTION ADE881, ACTIVE 0 sec fetching rows
mysql tables in use 4, locked 3
5 lock struct(s), heap size 3112, 14 row lock(s)
MySQL thread id 11, OS thread handle 0x82bd86000, query id 11386 localhost nodeny init
UPDATE ip_pool SET uid =  NAME_CONST('user_id',1950), `release` = UNIX_TIMESTAMP() + 300
        WHERE (uid = 0 OR uid =  NAME_CONST('user_id',1950))
            AND type = 'dynamic'
            AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
        ORDER BY uid DESC, id ASC LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 465 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id ADE881 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000028ee; asc   ( ;;
 1: len 6; hex 000000ade777; asc      w;;
 2: len 7; hex 1b0000020a08c5; asc        ;;
 3: len 4; hex 0a640002; asc  d  ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 53365a5e; asc S6Z^;;
 7: len 8; hex 0000000000000168; asc        h;;
 8: len 0; hex ; asc ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000028ef; asc   ( ;;
 1: len 6; hex 000000ade59c; asc       ;;
 2: len 7; hex 47000004532173; asc G   S!s;;
 3: len 4; hex 0a640003; asc  d  ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 53365a14; asc S6Z ;;
 7: len 8; hex 00000000000007be; asc         ;;
 8: len 0; hex ; asc ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000028f0; asc   ( ;;
 1: len 6; hex 000000ade78b; asc       ;;
 2: len 7; hex 27000003fa1978; asc '     x;;
 3: len 4; hex 0a640004; asc  d  ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 53365a5e; asc S6Z^;;
 7: len 8; hex 00000000000005e3; asc         ;;
 8: len 0; hex ; asc ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000028f1; asc   ( ;;
 1: len 6; hex 000000ade776; asc      v;;
 2: len 7; hex 1a000003890ff9; asc        ;;
 3: len 4; hex 0a640005; asc  d  ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 53365a5e; asc S6Z^;;
 7: len 8; hex 0000000000000392; asc         ;;
 8: len 0; hex ; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 465 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id ADE881 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 000028f2; asc   ( ;;
 1: len 6; hex 000000ade7ec; asc       ;;
 2: len 7; hex 6d0000040f302a; asc m    0*;;
 3: len 4; hex 0a640006; asc  d  ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 53365a76; asc S6Zv;;
 7: len 8; hex 0000000000000117; asc         ;;
 8: len 0; hex ; asc ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter ADEB9D
Purge done for trx's n:o < ADEB92 undo n:o < 0
History list length 2407
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 17, OS thread handle 0x82bd87800, query id 13345 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION ADEA90, not started
MySQL thread id 11, OS thread handle 0x82bd86000, query id 12844 localhost nodeny
---TRANSACTION ADEA86, not started
MySQL thread id 14, OS thread handle 0x82bd86c00, query id 12804 localhost nodeny
---TRANSACTION ADEA81, not started
MySQL thread id 13, OS thread handle 0x82bd86800, query id 12787 localhost nodeny
---TRANSACTION ADEA79, not started
MySQL thread id 12, OS thread handle 0x82bd86400, query id 12757 localhost nodeny
---TRANSACTION ADEA8B, not started
MySQL thread id 10, OS thread handle 0x82bd85c00, query id 12820 localhost nodeny
---TRANSACTION ADEB6B, not started
MySQL thread id 9, OS thread handle 0x82bd85800, query id 13291 localhost nodeny
---TRANSACTION ADEB93, not started
MySQL thread id 4, OS thread handle 0x8277efc00, query id 13323 localhost nodeny
---TRANSACTION ADE731, not started
MySQL thread id 5, OS thread handle 0x8277f0000, query id 13334 localhost nodeny
---TRANSACTION ADEB9C, not started
MySQL thread id 3, OS thread handle 0x8277ef800, query id 13344 localhost nodeny
---TRANSACTION ADEB97, not started
MySQL thread id 2, OS thread handle 0x8277ef400, query id 13337 localhost nodeny
---TRANSACTION ADEB83, not started
MySQL thread id 1, OS thread handle 0x8277ef000, query id 13341 localhost nodeny
--------
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
633 OS file reads, 4908 OS file writes, 1921 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 5.50 writes/s, 3.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 22 buffer(s)
115.44 hash searches/s, 73.96 non-hash searches/s
---
LOG
---
Log sequence number 221196525
Log flushed up to   221196525
Last checkpoint at  221194130
0 pending log writes, 0 pending chkp writes
1670 log i/o's done, 1.50 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 174239
Buffer pool size   8192
Free buffers       7517
Database pages     653
Old database pages 256
Modified db pages  5
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 622, created 31, written 4209
0.00 reads/s, 0.00 creates/s, 3.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: 653, 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
1 read views open inside InnoDB
Main thread id 34401730560, state: sleeping
Number of rows inserted 726, updated 1082, deleted 376, read 5947415
0.00 inserts/s, 7.00 updates/s, 0.00 deletes/s, 987.51 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

mysql>

кусок лога радиуса со статическими IP

Код:

Sat Mar 29 06:24:44 2014 : Info: Signalled to terminate
Sat Mar 29 06:24:44 2014 : Info: Exiting normally.
Sat Mar 29 06:24:45 2014 : Info: rlm_sql (sql): Closing sqlsocket 4
Sat Mar 29 06:24:45 2014 : Info: rlm_sql (sql): Closing sqlsocket 3
Sat Mar 29 06:24:45 2014 : Info: rlm_sql (sql): Closing sqlsocket 2
Sat Mar 29 06:24:45 2014 : Info: rlm_sql (sql): Closing sqlsocket 1
Sat Mar 29 06:24:45 2014 : Info: rlm_sql (sql): Closing sqlsocket 0
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Attempting to connect to nodeny@localhost:3306/nodeny
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Attempting to connect rlm_sql_mysql #0
Sat Mar 29 06:26:07 2014 : Info: rlm_sql_mysql: Starting connect to MySQL server for #0
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Connected new DB handle, #0
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Attempting to connect rlm_sql_mysql #1
Sat Mar 29 06:26:07 2014 : Info: rlm_sql_mysql: Starting connect to MySQL server for #1
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Connected new DB handle, #1
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Attempting to connect rlm_sql_mysql #2
Sat Mar 29 06:26:07 2014 : Info: rlm_sql_mysql: Starting connect to MySQL server for #2
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Connected new DB handle, #2
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Attempting to connect rlm_sql_mysql #3
Sat Mar 29 06:26:07 2014 : Info: rlm_sql_mysql: Starting connect to MySQL server for #3
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Connected new DB handle, #3
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Attempting to connect rlm_sql_mysql #4
Sat Mar 29 06:26:07 2014 : Info: rlm_sql_mysql: Starting connect to MySQL server for #4
Sat Mar 29 06:26:07 2014 : Info: rlm_sql (sql): Connected new DB handle, #4
Sat Mar 29 06:26:07 2014 : Info: Loaded virtual server <default>
Sat Mar 29 06:26:07 2014 : Info: Loaded virtual server inner-tunnel
Sat Mar 29 06:26:07 2014 : Info: Ready to process requests.
Sat Mar 29 06:48:35 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:48:36 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:48:50 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:48:51 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:49:11 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:49:32 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:49:33 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:49:44 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 06:49:55 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0

конец лога с динамикой

Код:

Sat Mar 29 07:21:49 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:21:56 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:22:11 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:22:23 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:22:33 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:22:45 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:22:46 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:22:51 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:22:58 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:23:01 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:23:22 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:23:25 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:23:34 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:23:51 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:24:00 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:24:01 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:24:28 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:24:46 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:25:06 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:25:28 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:25:31 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:25:53 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:25:53 2014 : Error: [sql] Couldn't update SQL accounting ALIVE record - Deadlock found when trying to get lock; try restarting transaction
Sat Mar 29 07:25:53 2014 : Error: rlm_sql_mysql: Cannot store result
Sat Mar 29 07:25:53 2014 : Error: rlm_sql_mysql: MySQL error 'Deadlock found when trying to get lock; try restarting transaction'
Sat Mar 29 07:25:53 2014 : Error: [sql] Couldn't update SQL accounting ALIVE record - Deadlock found when trying to get lock; try restarting transaction
Sat Mar 29 07:25:53 2014 : Error: rlm_sql_mysql: Cannot store result
Sat Mar 29 07:25:53 2014 : Error: rlm_sql_mysql: MySQL error 'Deadlock found when trying to get lock; try restarting transaction'
Sat Mar 29 07:26:14 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:26:17 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:26:29 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Sat Mar 29 07:26:39 2014 : Error: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0

[Efendy]

http://g.zeos.in/?q=Error%3A%20rlm_sql%20(sql)%3A%20There%20are%20no%20DB%20handles%20to%20use!

http://forum.nodeny.com.ua/index.php?topic=1890.0

[YuSHa]

те же яйца только в профиль(
в my.cnf

Код:

max_connections = 1200

в radiusd.conf

Код:

max_servers = 1024

поменял только тут с 5 до 50 sql.conf

Код:

num_sql_socks = 50

[YuSHa]

проблема дедлоков не решена(
лог mysql

Код:

140410 19:24:11 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.28'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
140411  5:44:57 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:44:57 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:44:57 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:44:57 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:48:16 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:48:16 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:48:16 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:48:16 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:48:16 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:48:16 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:50:32 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:50:32 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:38 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:38 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:51:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:54:31 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:23 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:23 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:23 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:23 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:55:24 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:17 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:17 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:53 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:54 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:56:54 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction
140411  5:57:55 [ERROR] /usr/local/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
140411  5:57:55 [ERROR] /usr/local/libexec/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction

[YuSHa]

лог радиуса

[Efendy]

Прикрепи вывод SHOW ENGINE INNODB STATUS;

[YuSHa]

у нас свет выбивало, так что сервак ребутался....
вывод после ребута

[Efendy]

В общем, я затупил - дедлоки я убрал давно, но не коммитил, т.к. хотел чтоб потестилось на моей сети. А после тестирования забыл. Сейчас закоммитил. После обновления надо перезапустить ядро N+

[YuSHa]

  фух..... будем пробовать снова

[Stronglan]

А возможно ли реализовать аналог модуля заглушки на микротике?