Название: Потестите. Поможет
Отправлено: Efendy от 17 Апреля 2014, 09:16:15
Долгое время постоянно сообщали, что есть некоторые проблемы при работе с динамическим ip. Скорее всего причиной этому были deadlock. Я уже пытался лечить их. Но это все была полумера т.к. я пытался уменьшить вероятность deadlock-ов, а не причину. Кажется, я нашел в чем проблема: в mysql оптимизатор не идеальный и при получении ip он лочил все пустые записи хотя был выставлен limit 1. Для меня было очевидно, что должна лочиться одна запись - а для него нет, поскольку было еще пару условий. Короче. Я сейчас потестил одну ночь и ни одного дедлока не было. Поэтому прош потестить в ваших сетях. Если все будет ок - заменю sql в стандартной поставке 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);
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 id = (SELECT id FROM (
(SELECT id, uid FROM ip_pool
WHERE uid = 0
AND type = 'dynamic'
AND realip = IF(real_ip>0,1,0)
LIMIT 1)
UNION
(SELECT id, uid FROM ip_pool
WHERE uid = user_id
AND type = 'dynamic'
AND realip = IF(real_ip>0,1,0)
LIMIT 1)
) AS tbl ORDER BY uid DESC LIMIT 1);
SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
WHERE uid = user_id LIMIT 1;
RETURN user_ip;
END$$
DELIMITER ;
Название: Re: Потестите. Поможет
Отправлено: Efendy от 17 Апреля 2014, 09:18:42
Если вдруг при выполнении кода выше будет ошибка - верните старую процедуру: 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);
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,%';
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;
SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
WHERE uid = user_id LIMIT 1;
RETURN user_ip;
END$$
DELIMITER ;
Название: Re: Потестите. Поможет
Отправлено: Efendy от 18 Апреля 2014, 17:47:14
Лана, хрен с вами. Меняю в поставке
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 19 Апреля 2014, 09:00:51
я попробую через пару дней.... и так народ злой, лишний раз не хотим дергать.... вертолеты, БМД.... стреляют.....надоело :-[
Название: Re: Потестите. Поможет
Отправлено: SeMant от 28 Апреля 2014, 08:49:51
У меня проблема не исчезла.
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 28 Апреля 2014, 09:00:00
логи в студию
Название: Re: Потестите. Поможет
Отправлено: SeMant от 11 Июля 2014, 12:41:42
логи в студию
какие именно логи демонстрировать? 140708 8:55:03 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 9 user: 'nodeny1'
140708 8:55:03 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 8 user: 'nodeny'
140708 8:55:03 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 1 user: 'nodeny3'
140708 8:55:03 InnoDB: Starting shutdown...
140708 8:55:04 InnoDB: Shutdown completed; log sequence number 423584779572
140708 8:55:04 [Note] /usr/local/libexec/mysqld: Shutdown complete
140708 08:55:04 mysqld_safe mysqld from pid file /var/db/mysql/hulk.kit.dn.ua.pid ended
140708 08:55:04 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140708 8:55:04 InnoDB: The InnoDB memory heap is disabled
140708 8:55:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140708 8:55:04 InnoDB: Compressed tables use zlib 1.2.7
140708 8:55:04 InnoDB: Initializing buffer pool, size = 128.0M
140708 8:55:04 InnoDB: Completed initialization of buffer pool
140708 8:55:04 InnoDB: highest supported file format is Barracuda.
140708 8:55:04 InnoDB: Waiting for the background threads to start
140708 8:55:05 InnoDB: 5.5.38 started; log sequence number 423584779572
140708 8:55:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140708 8:55:05 [Note] - '0.0.0.0' resolves to '0.0.0.0';
140708 8:55:05 [Note] Server socket created on IP: '0.0.0.0'.
140708 8:55:05 [Warning] 'user' entry 'root@hulk' ignored in --skip-name-resolve mode.
140708 8:55:05 [Warning] 'user' entry '@hulk' ignored in --skip-name-resolve mode.
140708 8:55:05 [Warning] 'proxies_priv' entry '@ root@hulk' ignored in --skip-name-resolve mode.
140708 8:55:05 [Note] Event Scheduler: Loaded 0 events
140708 8:55:05 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.38-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
140708 9:07:20 [Note] Found 498274 of 498520 rows when repairing './nodeny/X2014_5_29'
140708 9:08:56 [Note] Found 83815 of 83850 rows when repairing './nodeny/X2014_7_6'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:53 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:20:02 [ERROR] Invalid (old?) table or database name 'n+'
140708 22:25:52 [Note] Found 51841929 of 51842952 rows when repairing './nodeny/Z2014_7_6'
140709 8:46:51 [Note] /usr/local/libexec/mysqld: Normal shutdown
140709 8:46:51 [Note] Event Scheduler: Purging the queue. 0 events
140709 8:46:54 InnoDB: Starting shutdown...
140709 8:46:54 InnoDB: Waiting for 201 pages to be flushed
140709 8:46:54 InnoDB: Shutdown completed; log sequence number 425133786275
140709 8:46:54 [Note] /usr/local/libexec/mysqld: Shutdown complete
140709 08:46:54 mysqld_safe mysqld from pid file /var/db/mysql/hulk.kit.dn.ua.pid ended
140709 08:49:35 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140709 8:49:35 InnoDB: The InnoDB memory heap is disabled
140709 8:49:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140709 8:49:35 InnoDB: Compressed tables use zlib 1.2.7
140709 8:49:35 InnoDB: Initializing buffer pool, size = 128.0M
140709 8:49:35 InnoDB: Completed initialization of buffer pool
140709 8:49:35 InnoDB: highest supported file format is Barracuda.
140709 8:49:36 InnoDB: Waiting for the background threads to start
140709 8:49:37 InnoDB: 5.5.38 started; log sequence number 425133786275
140709 8:49:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140709 8:49:37 [Note] - '0.0.0.0' resolves to '0.0.0.0';
140709 8:49:37 [Note] Server socket created on IP: '0.0.0.0'.
140709 8:49:37 [Warning] 'user' entry 'root@hulk' ignored in --skip-name-resolve mode.
140709 8:49:37 [Warning] 'user' entry '@hulk' ignored in --skip-name-resolve mode.
140709 8:49:37 [Warning] 'proxies_priv' entry '@ root@hulk' ignored in --skip-name-resolve mode.
140709 8:49:37 [Note] Event Scheduler: Loaded 0 events
140709 8:49:37 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.38-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
140711 7:37:58 [Note] /usr/local/libexec/mysqld: Normal shutdown
140711 7:37:58 [Note] Event Scheduler: Purging the queue. 0 events
140711 7:38:00 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 165911 user: 'nodeny3'
140711 7:38:00 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 2 user: 'nodeny'
140711 7:38:00 InnoDB: Starting shutdown...
140711 7:38:01 InnoDB: Shutdown completed; log sequence number 428353403663
140711 7:38:01 [Note] /usr/local/libexec/mysqld: Shutdown complete
140711 07:38:01 mysqld_safe mysqld from pid file /var/db/mysql/hulk.kit.dn.ua.pid ended
140711 07:38:01 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140711 7:38:01 InnoDB: The InnoDB memory heap is disabled
140711 7:38:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140711 7:38:01 InnoDB: Compressed tables use zlib 1.2.7
140711 7:38:01 InnoDB: Initializing buffer pool, size = 128.0M
140711 7:38:01 InnoDB: Completed initialization of buffer pool
140711 7:38:01 InnoDB: highest supported file format is Barracuda.
140711 7:38:02 InnoDB: Waiting for the background threads to start
140711 7:38:03 InnoDB: 5.5.38 started; log sequence number 428353403663
140711 7:38:03 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140711 7:38:03 [Note] - '0.0.0.0' resolves to '0.0.0.0';
140711 7:38:03 [Note] Server socket created on IP: '0.0.0.0'.
140711 7:38:03 [Warning] 'user' entry 'root@hulk' ignored in --skip-name-resolve mode.
140711 7:38:03 [Warning] 'user' entry '@hulk' ignored in --skip-name-resolve mode.
140711 7:38:03 [Warning] 'proxies_priv' entry '@ root@hulk' ignored in --skip-name-resolve mode.
140711 7:38:03 [Note] Event Scheduler: Loaded 0 events
140711 7:38:03 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.38-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
Да и для полной картины. У меня один сервер всё в одном и 3 наса, все обслуживают всех. И только на одном насе Я замечаю такую проблему, причём каждый день. На остальных за всё время, после изминения get_ip не обнаружил.
Название: Re: Потестите. Поможет
Отправлено: SeMant от 23 Июля 2014, 06:32:05
Ещё на одном Насе появилась такая проблема, эти 2 помощней и на них больше абонов подключается. Подскажите на что обратить внимание.
Название: Re: Потестите. Поможет
Отправлено: Efendy от 23 Июля 2014, 08:40:22
SHOW ENGINE INNODB STATUS;
Название: Re: Потестите. Поможет
Отправлено: SeMant от 23 Июля 2014, 09:49:36
SHOW ENGINE INNODB STATUS; mysql> use nodeny;
Database changed
mysql> SHOW ENGINE INNODB STATUS;
| Type | Name | Status |
+--------+------+-----------------------------------------------------------------------------------------------------------
| InnoDB | |
=====================================
140723 10:35:50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1043192 1_second, 1043186 sleeps, 104318 10_second, 3 background, 3 flush
srv_master_thread log flush and writes: 1045585
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 448944, signal count 1607915
Mutex spin waits 28700909, rounds 57749126, OS waits 163263
RW-shared spins 836390, rounds 8616167, OS waits 151548
RW-excl spins 315985, rounds 7104529, OS waits 99450
Spin rounds per wait: 2.01 mutex, 10.30 RW-shared, 22.48 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
140723 10:35:43
*** (1) TRANSACTION:
TRANSACTION BE51C0B5, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
LOCK WAIT 6 lock struct(s), heap size 3112, 5 row lock(s)
MySQL thread id 702577, OS thread handle 0x82937f000, query id 391035141 localhost nodeny Sending data
UPDATE ip_pool SET uid = NAME_CONST('user_id',1324), `release` = UNIX_TIMESTAMP() + 300
WHERE id = (SELECT id FROM (
(SELECT id, uid FROM ip_pool
WHERE uid = 0
AND type = 'dynamic'
AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
LIMIT 1)
UNION
(SELECT id, uid FROM ip_pool
WHERE uid = NAME_CONST('user_id',1324)
AND type = 'dynamic'
AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
LIMIT 1)
) AS tbl ORDER BY uid DESC LIMIT 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 368 n bits 736 index `type` of table `nodeny`.`ip_pool` trx id BE51C0B5 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001000; asc ;;
*** (2) TRANSACTION:
TRANSACTION BE51C0B3, ACTIVE 0 sec fetching rows
mysql tables in use 5, locked 5
11 lock struct(s), heap size 3112, 243 row lock(s)
MySQL thread id 702578, OS thread handle 0x80280ac00, query id 391035131 localhost nodeny Sending data
UPDATE ip_pool SET uid = NAME_CONST('user_id',2452), `release` = UNIX_TIMESTAMP() + 300
WHERE id = (SELECT id FROM (
(SELECT id, uid FROM ip_pool
WHERE uid = 0
AND type = 'dynamic'
AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
LIMIT 1)
UNION
(SELECT id, uid FROM ip_pool
WHERE uid = NAME_CONST('user_id',2452)
AND type = 'dynamic'
AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
LIMIT 1)
) AS tbl ORDER BY uid DESC LIMIT 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 368 n bits 736 index `type` of table `nodeny`.`ip_pool` trx id BE51C0B3 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001000; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001001; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001002; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001003; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001004; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001005; asc ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001006; asc ;;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001007; asc ;;
Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001008; asc ;;
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001009; asc ;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000100a; asc ;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000100b; asc ;;
Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000100c; asc ;;
Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000100d; asc ;;
Record lock, heap no 16 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000100e; asc ;;
Record lock, heap no 17 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000100f; asc ;;
Record lock, heap no 18 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001010; asc ;;
Record lock, heap no 19 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001011; asc ;;
Record lock, heap no 20 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001012; asc ;;
Record lock, heap no 21 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001013; asc ;;
Record lock, heap no 22 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001014; asc ;;
Record lock, heap no 23 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001015; asc ;;
Record lock, heap no 24 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001016; asc ;;
Record lock, heap no 25 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001017; asc ;;
Record lock, heap no 26 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001018; asc ;;
Record lock, heap no 27 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001019; asc ;;
Record lock, heap no 28 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000101a; asc ;;
Record lock, heap no 29 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000101b; asc ;;
Record lock, heap no 30 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000101c; asc ;;
Record lock, heap no 31 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000101d; asc ;;
Record lock, heap no 32 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000101e; asc ;;
Record lock, heap no 33 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000101f; asc ;;
Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001020; asc ;;
Record lock, heap no 35 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001021; asc !;;
Record lock, heap no 36 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001022; asc ";;
Record lock, heap no 37 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001023; asc #;;
Record lock, heap no 38 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001024; asc $;;
Record lock, heap no 39 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001025; asc %;;
Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001026; asc &;;
Record lock, heap no 41 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001027; asc ';;
Record lock, heap no 42 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001028; asc (;;
Record lock, heap no 43 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001029; asc );;
Record lock, heap no 44 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000102a; asc *;;
Record lock, heap no 45 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000102b; asc +;;
Record lock, heap no 46 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000102c; asc ,;;
Record lock, heap no 47 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000102d; asc -;;
Record lock, heap no 48 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000102e; asc .;;
Record lock, heap no 49 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000102f; asc /;;
Record lock, heap no 50 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001030; asc 0;;
Record lock, heap no 51 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001031; asc 1;;
Record lock, heap no 52 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001032; asc 2;;
Record lock, heap no 53 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001033; asc 3;;
Record lock, heap no 54 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001034; asc 4;;
Record lock, heap no 55 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001035; asc 5;;
Record lock, heap no 56 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001036; asc 6;;
Record lock, heap no 57 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001037; asc 7;;
Record lock, heap no 58 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001038; asc 8;;
Record lock, heap no 59 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001039; asc 9;;
Название: Re: Потестите. Поможет
Отправлено: SeMant от 23 Июля 2014, 09:50:51
Record lock, heap no 60 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000103a; asc :;;
Record lock, heap no 61 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000103b; asc ;;;
Record lock, heap no 62 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000103c; asc <;;
Record lock, heap no 63 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000103d; asc =;;
Record lock, heap no 64 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000103e; asc >;;
Record lock, heap no 65 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000103f; asc ?;;
Record lock, heap no 66 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001040; asc @;;
Record lock, heap no 67 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001041; asc A;;
Record lock, heap no 68 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001042; asc B;;
Record lock, heap no 69 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001043; asc C;;
Record lock, heap no 70 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001044; asc D;;
Record lock, heap no 71 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001045; asc E;;
Record lock, heap no 72 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001046; asc F;;
Record lock, heap no 73 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001047; asc G;;
Record lock, heap no 74 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001048; asc H;;
Record lock, heap no 75 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001049; asc I;;
Record lock, heap no 76 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000104a; asc J;;
Record lock, heap no 77 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000104b; asc K;;
Record lock, heap no 78 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000104c; asc L;;
Record lock, heap no 79 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000104d; asc M;;
Record lock, heap no 80 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000104e; asc N;;
Record lock, heap no 81 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000104f; asc O;;
Record lock, heap no 82 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001050; asc P;;
Record lock, heap no 83 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001051; asc Q;;
Record lock, heap no 84 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001052; asc R;;
Record lock, heap no 85 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001053; asc S;;
Record lock, heap no 86 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001054; asc T;;
Record lock, heap no 87 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001055; asc U;;
Record lock, heap no 88 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001056; asc V;;
Record lock, heap no 89 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001057; asc W;;
Record lock, heap no 90 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001058; asc X;;
Record lock, heap no 91 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001059; asc Y;;
Record lock, heap no 92 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000105a; asc Z;;
Record lock, heap no 93 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000105b; asc [;;
Record lock, heap no 94 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000105c; asc \;;
Record lock, heap no 95 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000105d; asc ];;
Record lock, heap no 96 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000105e; asc ^;;
Record lock, heap no 97 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000105f; asc _;;
Record lock, heap no 98 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001060; asc `;;
Record lock, heap no 99 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001061; asc a;;
Record lock, heap no 100 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001062; asc b;;
Record lock, heap no 101 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001063; asc c;;
Record lock, heap no 102 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001064; asc d;;
Record lock, heap no 103 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001065; asc e;;
Record lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001066; asc f;;
Record lock, heap no 105 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001067; asc g;;
Record lock, heap no 106 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001068; asc h;;
Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 00001069; asc i;;
Record lock, heap no 108 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 02; asc ;;
1: len 4; hex 0000106a; asc j;;
*** (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 BE51C0B3 lock_mode X locks rec but not gap waiting
Record lock, heap no 259 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 4; hex 0000106a; asc j;;
1: len 6; hex 0000be518b02; asc Q ;;
2: len 7; hex 730000274d041a; asc s 'M ;;
3: len 4; hex 0a00006c; asc l;;
4: len 1; hex 02; asc ;;
5: len 1; hex 00; asc ;;
6: len 4; hex 53cf66a1; asc S f ;;
7: len 8; hex 000000000000052c; asc ,;;
8: len 0; hex ; asc ;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter BE51C445
Purge done for trx's n:o < BE51BF31 undo n:o < 0
History list length 4314
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 918164, OS thread handle 0x835d3c800, query id 391037277 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION BE51C3AB, not started
MySQL thread id 912668, OS thread handle 0x82937d000, query id 391036920 localhost nodeny
---TRANSACTION BE51C43D, not started
MySQL thread id 702577, OS thread handle 0x82937f000, query id 391037243 localhost nodeny
---TRANSACTION BE51C444, not started
MySQL thread id 702576, OS thread handle 0x82937ec00, query id 391037261 localhost nodeny
---TRANSACTION BE51C432, not started
MySQL thread id 702578, OS thread handle 0x80280ac00, query id 391037219 localhost nodeny
---TRANSACTION BE51C42A, not started
MySQL thread id 702579, OS thread handle 0x82937cc00, query id 391037201 localhost nodeny
---TRANSACTION BE51C422, not started
MySQL thread id 702580, OS thread handle 0x82937e000, query id 391037183 localhost nodeny
---TRANSACTION BE51C35C, not started
MySQL thread id 485680, OS thread handle 0x82937fc00, query id 391036731 95.47.104.8 nodeny2
---TRANSACTION BE51C2BB, not started
MySQL thread id 279344, OS thread handle 0x82937f800, query id 391036361 95.47.104.10 nodeny3
---TRANSACTION BE51BC82, not started
MySQL thread id 104, OS thread handle 0x82937f400, query id 391032584 localhost nodeny
---TRANSACTION BE51C2E5, not started
MySQL thread id 7, OS thread handle 0x82937c000, query id 391036469 95.47.104.6 nodeny1
---TRANSACTION BE51C2C4, not started
MySQL thread id 6, OS thread handle 0x82937c400, query id 391036405 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
22717 OS file reads, 54210379 OS file writes, 50580484 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 47.00 writes/s, 45.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 3743 merges
merged operations:
insert 3746, delete mark 2, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 804 buffer(s)
14941.59 hash searches/s, 1056.25 non-hash searches/s
---
LOG
---
Log sequence number 449072340699
Log flushed up to 449072340699
Last checkpoint at 449067495863
0 pending log writes, 0 pending chkp writes
48730805 log i/o's done, 43.33 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 215905
Buffer pool size 8192
Free buffers 1
Database pages 7387
Old database pages 2706
Modified db pages 502
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 55324, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 23080, created 702, written 10127561
0.00 reads/s, 0.00 creates/s, 3.67 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: 7387, unzip_LRU len: 0
I/O sum[207]: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 110177, updated 48582490, deleted 55002, read 20587271547
0.25 inserts/s, 42.91 updates/s, 0.08 deletes/s, 19471.71 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Название: Re: Потестите. Поможет
Отправлено: Efendy от 23 Июля 2014, 22:26:29
Сколько NAS, которые работают с Radius?
Название: Re: Потестите. Поможет
Отправлено: SeMant от 24 Июля 2014, 06:01:15
Всего 4, на одном из них радиус установлен.
Название: Re: Потестите. Поможет
Отправлено: Efendy от 24 Июля 2014, 09:27:24
Всего 4, на одном из них радиус установлен.
Надо попробовать сделать 4 разных процедуры для этих насов. Сегодня постараюсь нарисовать. Никак не получается одолеть этот долбанный mysql - все таки отстает он от postgres-а
Название: Re: Потестите. Поможет
Отправлено: Efendy от 24 Июля 2014, 10:38:18
В общем, сначала читаешь http://forum.nodeny.com.ua/index.php?topic=2374.0 - описание как сделать чтоб каждый NAS выдавал ip только из своего пула. Только саму функцию сделать такой: DROP FUNCTION IF EXISTS `get_ip_by_tag`;
DELIMITER $$
CREATE FUNCTION `get_ip_by_tag` ( user_id INTEGER UNSIGNED, tag VARCHAR(64) )
RETURNS VARCHAR(15) NO SQL
BEGIN
DECLARE user_ip VARCHAR(15);
DECLARE real_ip VARCHAR(15);
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 id = (SELECT id FROM (
(SELECT id, uid FROM ip_pool
WHERE uid = 0
AND type = 'dynamic'
AND realip = IF(real_ip>0,1,0)
AND tags LIKE CONCAT('%,', tag, ',%')
LIMIT 1)
UNION
(SELECT id, uid FROM ip_pool
WHERE uid = user_id
AND type = 'dynamic'
AND realip = IF(real_ip>0,1,0)
LIMIT 1)
) AS tbl ORDER BY uid DESC LIMIT 1);
SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
WHERE uid = user_id LIMIT 1;
RETURN user_ip;
END$$
DELIMITER ;
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 28 Июля 2014, 10:26:44
снова запускали пару дней назад с измененными процедурами с начала топика - результат тот же( таблицы лочатся(. че делать? пол года не могу запустить биллинг в работу...
Название: Re: Потестите. Поможет
Отправлено: Efendy от 28 Июля 2014, 13:02:42
снова запускали пару дней назад с измененными процедурами с начала топика - результат тот же( таблицы лочатся(. че делать? пол года не могу запустить биллинг в работу...
NAS один?
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 28 Июля 2014, 14:03:01
да.
Название: Re: Потестите. Поможет
Отправлено: Efendy от 28 Июля 2014, 15:17:52
Покажи лог - непонятно с каким запросом лочится
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 29 Июля 2014, 16:44:22
мускуль тюнил по тому факу, что ты указывал, и радиус тоже.
Название: Re: Потестите. Поможет
Отправлено: Efendy от 29 Июля 2014, 22:57:22
я имел ввиду SHOW ENGINE INNODB STATUS; - в предыдущих сообщениях писал
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 30 Июля 2014, 08:51:21
сервак перезагружался. это имеет значение? 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>
Название: Re: Потестите. Поможет
Отправлено: Efendy от 30 Июля 2014, 13:46:01
В логе никаких LOCK-ов нет. Жди пока появятся. Если будет раз в день - имхо можно забить
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 31 Июля 2014, 10:08:49
забить не получается, т.к. при переключении на брасе на радиус нодени, щимится очень много учеток. сесии pppoe все не поднимаются..... и даже на 500 поднятых учеток пппое в самом биллинге не отображается 500 активных клиентов. цифра там все время плавает...то 150, то 160, то 120
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 31 Июля 2014, 10:09:27
ладно, попробую ночью заснять это на видео с рабочего стола
Название: Re: Потестите. Поможет
Отправлено: Efendy от 31 Июля 2014, 11:12:02
а ip в пуле во сколько раз больше количества клиентов?
Название: Re: Потестите. Поможет
Отправлено: Efendy от 31 Июля 2014, 13:55:44
В общем, ладно - даю функцию, которая по идее не должна приводить к локам 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 ;
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 31 Июля 2014, 15:43:49
IP pool 10.10.1.1-10.10.12.254 т.е. 2800 айпишников. активных учеток 700-1000
Название: Re: Потестите. Поможет
Отправлено: Efendy от 02 Августа 2014, 11:51:56
Мне нужно чтобы в логе было написано, что такой-то sql привел к deadlock
Название: Re: Потестите. Поможет
Отправлено: Sidius от 05 Августа 2014, 22:57:19
А никто меня еще год назад не хотел слушать что есть проблема с динамическими ИП. Говорили что я криворукая редиска :(
Название: Re: Потестите. Поможет
Отправлено: Efendy от 06 Августа 2014, 13:57:54
elite говорит, что лечится ключом -t при запуске radiusd
Название: Re: Потестите. Поможет
Отправлено: 0xbad0c0d3 от 06 Августа 2014, 21:34:13
elite говорит, что лечится ключом -t при запуске radiusd
elite дело говорит ))))
Название: Re: Потестите. Поможет
Отправлено: elite от 06 Августа 2014, 22:10:09
elite говорит, что лечится ключом -t при запуске radiusd
elite дело говорит )))) 0xbad0c0d3 рекомендовал :)
Название: Re: Потестите. Поможет
Отправлено: YuSHa от 23 Августа 2014, 13:08:56
параметр -t не помог, а вот установка на другую (реальную) тачку помогла. может систему на виртуалке уже загадил за год издевательств или дело непостредственно в виртуалке.
попробую ещё сделать новую виртуалку с минимум софта.
Название: Re: Потестите. Поможет
Отправлено: elite от 24 Августа 2014, 17:26:38
параметр -t не помог, а вот установка на другую (реальную) тачку помогла. может систему на виртуалке уже загадил за год издевательств или дело непостредственно в виртуалке.
попробую ещё сделать новую виртуалку с минимум софта.
может старая версия freeradius?
|