MySQL Forums
Forum List  »  Replication

Re: How long replication takes?
Posted by: Martin Schmidt
Date: December 30, 2004 11:45AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
9240
December 06, 2004 03:49AM
3645
December 06, 2004 03:51AM
3718
December 07, 2004 03:47PM
3452
December 18, 2004 12:30PM
3538
December 19, 2004 03:55AM
Re: How long replication takes?
3743
December 30, 2004 11:45AM
3315
January 01, 2005 04:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.