Re: How long replication takes?
Ok, here are the outputs of
SHOW INNODB STATUS on the client:
*************************** 1. row ***************************
Status:
=====================================
041230 10:12:36 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1985, signal count 1967
Mutex spin waits 7367, rounds 17601, OS waits 74
RW-shared spins 3520, OS waits 1664; RW-excl spins 231, OS waits 211
------------
TRANSACTIONS
------------
Trx id counter 0 43551259
Purge done for trx's n:o < 0 43551207 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 15625, OS thread id 5050383
MySQL thread id 1225, query id 41037 localhost root
show innodb status
---TRANSACTION 0 43551241, not started, process no 15107, OS thread id 4235286
MySQL thread id 1026, query id 40990 consumer 10.0.7.151 root
---TRANSACTION 0 43551240, not started, process no 15106, OS thread id 4231189
MySQL thread id 1025, query id 40989 consumer 10.0.7.151 root
---TRANSACTION 0 43551174, not started, process no 15105, OS thread id 4227089
MySQL thread id 1024, query id 40856 consumer 10.0.7.151 root
---TRANSACTION 0 43549461, not started, process no 15104, OS thread id 4222992
MySQL thread id 1023, query id 36973 consumer 10.0.7.151 root
---TRANSACTION 0 43551239, not started, process no 15096, OS thread id 4202510
MySQL thread id 1018, query id 40988 consumer 10.0.7.151 root
---TRANSACTION 0 43551258, not started, process no 15095, OS thread id 4198413
MySQL thread id 1017, query id 41036 consumer 10.0.7.151 root
---TRANSACTION 0 43551257, not started, process no 15094, OS thread id 4194316
MySQL thread id 1016, query id 41035 consumer 10.0.7.151 root
---TRANSACTION 0 43549442, not started, process no 15093, OS thread id 4190219
MySQL thread id 1015, query id 36906 consumer 10.0.7.151 root
---TRANSACTION 0 43549440, not started, process no 15092, OS thread id 4186122
MySQL thread id 1014, query id 36905 consumer 10.0.7.151 root
---TRANSACTION 0 43551255, not started, process no 12708, OS thread id 106516
MySQL thread id 18, query id 41034 sascha 10.0.7.101 root
---TRANSACTION 0 43551254, not started, process no 12688, OS thread id 81939
MySQL thread id 12, query id 41031 Has read all relay log; waiting for the I/O slave
--------
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
19217 OS file reads, 12714 OS file writes, 10312 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.35 writes/s, 0.97 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
6 inserts, 6 merged recs, 6 merges
Hash table size 34679, used cells 757, node heap has 2 buffer(s)
49.19 hash searches/s, 0.42 non-hash searches/s
---
LOG
---
Log sequence number 1 2431821940
Log flushed up to 1 2431821940
Last checkpoint at 1 2431820419
0 pending log writes, 0 pending chkp writes
9454 log i/o's done, 0.71 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17677144; in additional pool allocated 1048576
Buffer pool size 512
Free buffers 0
Database pages 510
Modified db pages 6
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 30188, created 697, written 3955
0.00 reads/s, 0.03 creates/s, 0.65 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 12673, id 28680, state: sleeping
Number of rows inserted 8699, updated 699, deleted 117, read 7752792
0.52 inserts/s, 0.10 updates/s, 0.00 deletes/s, 1292.44 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
SHOW PROCESSLIST on the client:
*************************** 1. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 2946
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 12
User: system user
Host:
db: playng
Command: Connect
Time: 0
State: Reading event from the relay log
Info: COMMIT
*************************** 3. row ***************************
Id: 18
User: root
Host: sascha:35872
db: playng
Command: Sleep
Time: 14
State:
Info: NULL
*************************** 4. row ***************************
Id: 1014
User: root
Host: consumer:4993
db: playng
Command: Sleep
Time: 448
State:
Info: NULL
*************************** 5. row ***************************
Id: 1015
User: root
Host: consumer:4994
db: playng
Command: Sleep
Time: 448
State:
Info: NULL
*************************** 6. row ***************************
Id: 1016
User: root
Host: consumer:4995
db: playng
Command: Sleep
Time: 2
State:
Info: NULL
*************************** 7. row ***************************
Id: 1017
User: root
Host: consumer:4996
db: playng
Command: Sleep
Time: 2
State:
Info: NULL
*************************** 8. row ***************************
Id: 1018
User: root
Host: consumer:4997
db: playng
Command: Sleep
Time: 6
State:
Info: NULL
*************************** 9. row ***************************
Id: 1023
User: root
Host: consumer:4999
db: playng
Command: Sleep
Time: 440
State:
Info: NULL
*************************** 10. row ***************************
Id: 1024
User: root
Host: consumer:5000
db: playng
Command: Sleep
Time: 20
State:
Info: NULL
*************************** 11. row ***************************
Id: 1025
User: root
Host: consumer:1027
db: playng
Command: Sleep
Time: 6
State:
Info: NULL
*************************** 12. row ***************************
Id: 1026
User: root
Host: consumer:1028
db: playng
Command: Sleep
Time: 6
State:
Info: NULL
*************************** 13. row ***************************
Id: 1203
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
I wasn't able to fetch the outputs from the server at the specified time of the hang.
All outputs will follow, if this hang will occur next time.
Seems to be a deadlock or something like that?!
Perhaps the SQL Thread is lagging?
I'll keep on examining.
Best regards,
Martin