In article <[EMAIL PROTECTED]>,
"Horváth Ágoston János" <[EMAIL PROTECTED]> <[email protected]>
writes:
> Most jutott eszembe meg egy dolog: nem lehet, hogy valami lockol par
> rekordot a tablabol? Ha futtatsz egy 'show innodb status'-t,
> megmutatja az utolso par lock-utkozest, meg sok egyeb hasznos infot.
Mond ez neked valamit?
mysql> show innodb status\G
*************************** 1. row ***************************
Status:
=====================================
070621 17:10:44 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 327833, signal count 321794
Mutex spin waits 0, rounds 7131409, OS waits 92901
RW-shared spins 223373, OS waits 102505; RW-excl spins 146433, OS waits 91321
------------------------
LATEST FOREIGN KEY ERROR
------------------------
070621 16:52:02 Transaction:
TRANSACTION 0 6195590, ACTIVE 0 sec, process no 16739, OS thread id 2967640976
inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 812, query id 2586746 localhost nfsen update
REPLACE INTO topSources (srcip, id, time, flows, bytes, minport, maxport)
VALUES ('193.225.12.83', 3, 1182427775, 5, 555, 5810,
34577)
Foreign key constraint fails for table `nfsenalarm/topSources`:
,
CONSTRAINT `topSources_ibfk_2` FOREIGN KEY (`id`) REFERENCES `ddos` (`id`) ON
DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `id` tuple:
DATA TUPLE: 3 fields;
0: len 4; hex 00000003; asc ;; 1: len 13; hex 3139332e3232352e31322e3833;
asc 193.225.12.83;; 2: len 4; hex 467a6a7f; asc Fzj ;;
But in parent table `nfsenalarm/ddos`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 4; hex 000007d0; asc ;; 1: len 6; hex 00000046914e; asc F N;; 2:
len 7; hex 0000000138100c; asc 8 ;; 3: len 15; hex
3139332e3232352e3234392e313134; asc 193.225.249.114;; 4: len 4; hex 00000426;
asc &;; 5: len 4; hex 00000003; asc ;; 6: len 4; hex 00000140; asc
@;; 7: len 1; hex 80; asc ;; 8: len 4; hex 00000001; asc ;;
------------
TRANSACTIONS
------------
Trx id counter 0 6198771
Purge done for trx's n:o < 0 6198739 undo n:o < 0 0
History list length 36
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 16739, OS thread id 2968181648
MySQL thread id 866, query id 2587497 localhost nfsen
---TRANSACTION 0 6198737, not started, process no 16739, OS thread id 2964695952
MySQL thread id 864, query id 2589473 localhost nfsen
---TRANSACTION 0 6198648, not started, process no 16739, OS thread id 2964560784
MySQL thread id 859, query id 2589400 localhost nfsen
---TRANSACTION 0 6198586, not started, process no 16739, OS thread id 2965101456
MySQL thread id 856, query id 2589350 localhost nfsen
---TRANSACTION 0 6198769, not started, process no 16739, OS thread id 2963274640
MySQL thread id 852, query id 2589499 localhost nfsen
---TRANSACTION 0 6198442, not started, process no 16739, OS thread id 2964966288
MySQL thread id 848, query id 2589227 localhost nfsen
---TRANSACTION 0 6198499, not started, process no 16739, OS thread id 2965236624
MySQL thread id 844, query id 2589275 localhost nfsen
---TRANSACTION 0 6198533, not started, process no 16739, OS thread id 3019041680
MySQL thread id 841, query id 2589307 localhost nfsen
---TRANSACTION 0 6198702, not started, process no 16739, OS thread id 2967776144
MySQL thread id 838, query id 2589448 localhost nfsen
---TRANSACTION 0 6198675, not started, process no 16739, OS thread id 3019312016
MySQL thread id 835, query id 2589425 localhost nfsen
---TRANSACTION 0 6198613, not started, process no 16739, OS thread id 2963004304
MySQL thread id 832, query id 2589375 localhost nfsen
---TRANSACTION 0 6198560, not started, process no 16739, OS thread id 2967911312
MySQL thread id 829, query id 2589325 localhost nfsen
---TRANSACTION 0 0, not started, process no 16739, OS thread id 2968046480
MySQL thread id 828, query id 2579591 localhost nfsen
---TRANSACTION 0 6198299, not started, process no 16739, OS thread id 2967370640
MySQL thread id 826, query id 2589104 localhost nfsen
---TRANSACTION 0 6198387, not started, process no 16739, OS thread id 2966694800
MySQL thread id 824, query id 2589179 localhost nfsen
---TRANSACTION 0 6198330, not started, process no 16739, OS thread id 3019176848
MySQL thread id 821, query id 2589129 localhost nfsen
---TRANSACTION 0 0, not started, process no 16739, OS thread id 3019447184
MySQL thread id 820, query id 2586051 localhost nfsen
---TRANSACTION 0 6198126, not started, process no 16739, OS thread id 2966965136
MySQL thread id 818, query id 2588939 localhost nfsen
---TRANSACTION 0 0, not started, process no 16739, OS thread id 2962869136
MySQL thread id 817, query id 2583831 localhost nfsen
---TRANSACTION 0 0, not started, process no 16739, OS thread id 2968316816
MySQL thread id 814, query id 2589043 localhost nfsen
---TRANSACTION 0 6198358, not started, process no 16739, OS thread id 2967640976
MySQL thread id 812, query id 2589154 localhost nfsen
---TRANSACTION 0 0, not started, process no 16739, OS thread id 2967100304
MySQL thread id 811, query id 2574548 localhost nfsen
---TRANSACTION 0 6198465, not started, process no 16739, OS thread id 2965371792
MySQL thread id 809, query id 2589250 localhost nfsen
---TRANSACTION 0 6143626, not started, process no 16739, OS thread id 2966829968
MySQL thread id 808, query id 2586852 localhost nfsen
---TRANSACTION 0 0, not started, process no 16739, OS thread id 2967505808
MySQL thread id 804, query id 2588262 localhost nfsen
---TRANSACTION 0 0, not started, process no 16739, OS thread id 2964831120
MySQL thread id 800, query id 2570968 localhost nfsen
---TRANSACTION 0 6143727, not started, process no 16739, OS thread id 2967235472
MySQL thread id 798, query id 2589500 localhost root
show innodb status
--------
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 (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3346773 OS file reads, 7885487 OS file writes, 4803916 OS fsyncs
0.38 reads/s, 16384 avg bytes/read, 3.38 writes/s, 3.38 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 99, seg size 101,
1365126 inserts, 1365126 merged recs, 256503 merges
Hash table size 34679, used cells 9958, node heap has 13 buffer(s)
1.14 hash searches/s, 3.14 non-hash searches/s
---
LOG
---
Log sequence number 0 3443279649
Log flushed up to 0 3443279649
Last checkpoint at 0 3443271614
0 pending log writes, 0 pending chkp writes
4707732 log i/o's done, 3.38 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17723580; in additional pool allocated 1048576
Buffer pool size 512
Free buffers 0
Database pages 499
Modified db pages 20
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 3388071, created 31945, written 3393526
0.38 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 991 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 16739, id 2991557520, state: sleeping
Number of rows inserted 1786764, updated 526640, deleted 3113640, read 230397978
1.00 inserts/s, 0.57 updates/s, 0.00 deletes/s, 139.13 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.00 sec)
mysql>
> Esetleg a 'show full processlist' is segithet, hatha valami mas is
> hasznalja az adatbazist.
Ez erdekes. 27 ilyen van:
*************************** 23. row ***************************
Id: 852
User: nfsen
Host: localhost
db: nfsenalarm
Command: Sleep
Time: 95
State:
Info: NULL
> Esetleg ha latsz valamit a syslogban (debian defaultbol oda logol), az
> is erdekes lehet, oda szoktak kerulni a fontosabb infok/warningok.
Jun 21 14:05:35 XXXXXX nfsen[27437]: MySQL error: Lock wait timeout exceeded;
try restarting transaction
Jun 21 14:06:28 XXXXXX nfsen[27437]: MySQL error: Lock wait timeout exceeded;
try restarting transaction
Jun 21 14:06:32 XXXXXX nfsen[26270]: MySQL error: Lock wait timeout exceeded;
try restarting transaction
Jun 21 14:07:20 XXXXXX nfsen[27437]: MySQL error: Lock wait timeout exceeded;
try restarting transaction
...
Jun 21 14:38:03 XXXXXX nfsen[28196]: MySQL error: Lock wait timeout exceeded;
try restarting transaction
Eppen, mikor torolni probaltam.
Fogadjunk, hogy nem veletlen! :-)
Na ezzel elleszek egy darabig.
Kosz a tippeket!
Gabor
_________________________________________________
linux lista - [email protected]
http://mlf2.linux.rulez.org/mailman/listinfo/linux
