Make information from SHOW ENGINE INNODB STATUS available in SHOW STATUS
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
High
|
Unassigned |
Bug Description
We want to have next information available in SHOW STATUS command
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9664, signal count 11182
Mutex spin waits 20599, rounds 223821, OS waits 4479
RW-shared spins 5155, OS waits 1678; RW-excl spins 5632, OS waits 2592
Spin rounds per wait: 10.87 mutex, 15.01 RW-shared, 27.19 RW-excl
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
-------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------
Ibuf: size 1, free list len 6089, seg size 6091,
44497 inserts, 44497 merged recs, 8734 merges
Hash table size 276707, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
innodb_ibuf_size
innodb_
innodb_
innodb_ibuf_inserts
innodb_ibuf_merges
innodb_
innodb_
innodb_
innodb_
innodb_
---
LOG
---
Log sequence number 28219393219
Log flushed up to 28219393219
Last checkpoint at 28212583337
Max checkpoint age 7782360
Checkpoint age target 7539162
Modified age 6809882
Checkpoint age 6809882
0 pending log writes, 0 pending chkp writes
8570 log i/o's done, 2000.00 log i/o's/second
innodb_lsn_current
innodb_lsn_flushed
innodb_
innodb_
innodb_
innodb_
innodb_
-------
BUFFER POOL AND MEMORY
-------
Total memory allocated 137625600; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 3774352 (2213656 + 1560696)
Page hash 139144
Dictionary cache 629811 (554864 + 74947)
File system 83536 (82672 + 864)
Lock system 380792 (332872 + 47920)
Recovery system 0 (0 + 0)
Threads 84040 (82696 + 1344)
Dictionary memory allocated 74947
Buffer pool size 8192
Buffer pool size, bytes 134217728
Free buffers 0
Database pages 8095
Old database pages 2968
Modified db pages 5914
Pending reads 0
Pending writes: LRU 0, flush list 129, single page 0
Pages made young 372084, not young 0
2546000.00 youngs/s, 0.00 non-youngs/s
Pages read 103356, created 154787, written 979572
469000.00 reads/s, 78000.00 creates/s, 138000.00 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 34 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 15000.00/s
innodb_mem_total
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
------------
TRANSACTIONS
------------
Trx id counter F561FD
Purge done for trx's n:o < F561EB undo n:o < 0
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 993, OS thread id 140213152634640
MySQL thread id 15933, query id 32109 localhost root
show innodb status
---TRANSACTION F561FC, ACTIVE 29 sec, process no 993, OS thread id 140213152769808 updating or deleting
mysql tables in use 1, locked 1
innodb_trx_counter (Make it Integer)
innodb_
Also: Is there any value as number of locks or lock structure size (global) which Innodb maintains - this would allow to easily track cases
when there are some transactions which are doing it
Also is there any way to access the age of oldest transaction quickly ? (without scanning potentially large list) - this would also be helpful.
NOTE: We should ensure the values requested can be read quickly (like reading some values or doing basic month, we should not add anything to show status which is expensive to compute as this is often called very frequently)
Changed in percona-server: | |
importance: | Undecided → High |
milestone: | none → 5.5-20beta |
Changed in percona-server: | |
assignee: | nobody → Yasufumi Kinoshita (yasufumi-kinoshita) |
Changed in percona-server: | |
status: | New → Triaged |
Changed in percona-server: | |
status: | Triaged → In Progress |
Changed in percona-server: | |
status: | In Progress → Fix Committed |
Changed in percona-server: | |
status: | Fix Committed → Fix Released |
1.
I think we should not use any additional mutex for "show status".
Some of the values needs mutex to calculate.
2.
The suggestion seems to skip useful values and including most of not-so-useful values.
Why do you choose the values? Is it worth to make slower the "show status" command?
I cannot accept such baseless suggestions.
You should understand expense of the getting values.
e.g.
Pending reads 0
Pending writes: LRU 0, flush list 129, single page 0
These are the values we should look at first to judge whether the problem is IO bound or not.