Потестите. Поможет

[YuSHa]

снова запускали пару дней назад с измененными процедурами с начала топика - результат тот же( таблицы лочатся(. че делать? пол года не могу запустить биллинг в работу...

[Efendy]

снова запускали пару дней назад с измененными процедурами с начала топика - результат тот же( таблицы лочатся(. че делать? пол года не могу запустить биллинг в работу...

NAS один?

[YuSHa]

да.

[Efendy]

Покажи лог - непонятно с каким запросом лочится

[YuSHa]

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

[Efendy]

я имел ввиду

Код:

SHOW ENGINE INNODB STATUS;

- в предыдущих сообщениях писал

[YuSHa]

сервак перезагружался. это имеет значение?

Код:

mysql> use nodeny;
Database changed
mysql> SHOW ENGINE INNODB STATUS;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |
=====================================
140730  9:48:21 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1627214 1_second, 1627213 sleeps, 162218 10_second, 5035 background, 5029 flush
srv_master_thread log flush and writes: 1627420
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7640, signal count 7632
Mutex spin waits 13120, rounds 64526, OS waits 1653
RW-shared spins 5924, rounds 177678, OS waits 5921
RW-excl spins 11, rounds 1034, OS waits 33
Spin rounds per wait: 4.92 mutex, 29.99 RW-shared, 94.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 1264FA8
Purge done for trx's n:o < 125FA3D undo n:o < 0
History list length 2144
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 4917, OS thread handle 0x8273ca400, query id 5108555 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1264FA7, not started
MySQL thread id 4, OS thread handle 0x8273bc800, query id 5108554 localhost nodeny
---TRANSACTION 1259742, not started
MySQL thread id 7, OS thread handle 0x8273bd400, query id 5108451 localhost nodeny
---TRANSACTION 1264767, not started
MySQL thread id 2, OS thread handle 0x8273bc000, query id 5108549 localhost nodeny
---TRANSACTION 125F9FE, not started
MySQL thread id 1, OS thread handle 0x8273bbc00, query id 5108551 localhost nodeny
---TRANSACTION 1259726, not started
MySQL thread id 3, OS thread handle 0x8273bc400, query id 5108545 localhost nodeny
---TRANSACTION 1259732, not started
MySQL thread id 5, OS thread handle 0x8273bcc00, query id 5108528 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
877 OS file reads, 46333 OS file writes, 27749 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, 2 merges
merged operations:
 insert 2, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 49 buffer(s)
8.70 hash searches/s, 6.15 non-hash searches/s
---
LOG
---
Log sequence number 285353753
Log flushed up to   285353753
Last checkpoint at  285353753
0 pending log writes, 0 pending chkp writes
14413 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 194758
Buffer pool size   8192
Free buffers       7196
Database pages     947
Old database pages 347
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 6, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 866, created 81, written 28367
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: 947, 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 4097, updated 2802, deleted 2341, read 678276645
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 386.69 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

mysql>

[Efendy]

В логе никаких LOCK-ов нет. Жди пока появятся. Если будет раз в день - имхо можно забить

[YuSHa]

забить не получается, т.к. при переключении на брасе на радиус нодени, щимится очень много учеток. сесии pppoe все не поднимаются..... и даже на 500 поднятых учеток пппое в самом биллинге не отображается 500 активных клиентов. цифра там все время плавает...то 150, то 160, то 120

[YuSHa]

ладно, попробую ночью заснять это на видео с рабочего стола

[Efendy]

а ip в пуле во сколько раз больше количества клиентов?

[Efendy]

В общем, ладно - даю функцию, которая по идее не должна приводить к локам

Код:

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_count 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 `release` = UNIX_TIMESTAMP() + 300
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0);
    
    SELECT ROW_COUNT() INTO row_count;

    IF( row_count  < 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 ;

[YuSHa]

IP pool 10.10.1.1-10.10.12.254 т.е. 2800 айпишников. активных учеток 700-1000

[Efendy]

Мне нужно чтобы в логе было написано, что такой-то sql привел к deadlock

[Sidius]

А никто меня еще год назад не хотел слушать что есть проблема с динамическими ИП. Говорили что я криворукая редиска