Make information from SHOW ENGINE INNODB STATUS available in SHOW STATUS

Bug #698797 reported by Vadim Tkachenko
6
This bug affects 1 person
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_mutex_spin_waits
innodb_mutex_spin_rounds
innodb_mutex_spin_os_waits
innodb_rwlock_shared_spins
innodb_rwlock_exclusive_spins
innodb_rwlock_shared_os_waits
innodb_rwlock_exclusive_os_waits

-------------------------------------
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_ibuf_free_list
innodb_ibuf_segment_size
innodb_ibuf_inserts
innodb_ibuf_merges
innodb_ibuf_merged_recs
innodb_adaptive_hash_table_size
innodb_adaptive_hash_node_heap_buffers
innodb_adaptive_hash_hash_searches
innodb_adaptive_hash_non_hash_searches

---
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_lsn_checkpoint
innodb_checkpoint_max_age
innodb_checkpoint_age_target
innodb_checkpoint_modified_age
innodb_checkpoint_age

----------------------
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_mem_adaptive_hash
innodb_mem_dictionary
innodb_buffer_pool_pages_old
innodb_pages_made_young
innodb_pages_made_not_young
innodb_pages_read_ahead
innodb_pages_evicted_without_access

------------
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_history_length

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
Revision history for this message
Yasufumi Kinoshita (yasufumi-kinoshita) wrote :

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.

Revision history for this message
Vadim Tkachenko (vadim-tk) wrote :

Yasufumi,

1. It is OK to show values without mutexes.

2. SHOW STATUS is needed to have simple way to monitor current status.
SHOW INNODB STATUS is very hard to parse and understand.

We can add
"
Pending reads 0
Pending writes: LRU 0, flush list 129, single page 0
"

it is good to have it.

Revision history for this message
Peter Zaitsev (pz-percona) wrote : Re: [Bug 698797] Re: Make information from SHOW ENGINE INNODB STATUS available in SHOW STATUS
Download full text (5.3 KiB)

Vadim,

Please come up with names for these. I might have skipped it

On Wed, Jan 12, 2011 at 9:43 AM, Vadim Tkachenko <email address hidden> wrote:

> Yasufumi,
>
> 1. It is OK to show values without mutexes.
>
> 2. SHOW STATUS is needed to have simple way to monitor current status.
> SHOW INNODB STATUS is very hard to parse and understand.
>
>
> We can add
> "
> Pending reads 0
> Pending writes: LRU 0, flush list 129, single page 0
> "
>
> it is good to have it.
>
> --
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
> https://bugs.launchpad.net/bugs/698797
>
> Title:
> Make information from SHOW ENGINE INNODB STATUS available in SHOW
> STATUS
>
> Status in Percona Server with XtraDB:
> Triaged
>
> 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_mutex_spin_waits
> innodb_mutex_spin_rounds
> innodb_mutex_spin_os_waits
> innodb_rwlock_shared_spins
> innodb_rwlock_exclusive_spins
> innodb_rwlock_shared_os_waits
> innodb_rwlock_exclusive_os_waits
>
>
> -------------------------------------
> 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_ibuf_free_list
> innodb_ibuf_segment_size
> innodb_ibuf_inserts
> innodb_ibuf_merges
> innodb_ibuf_merged_recs
> innodb_adaptive_hash_table_size
> innodb_adaptive_hash_node_heap_buffers
> innodb_adaptive_hash_hash_searches
> innodb_adaptive_hash_non_hash_searches
>
>
> ---
> 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_lsn_checkpoint
> innodb_checkpoint_max_age
> innodb_checkpoint_age_target
> innodb_checkpoint_modified_age
> innodb_checkpoint_age
>
> ----------------------
> 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 ...

Read more...

Changed in percona-server:
status: Triaged → In Progress
Revision history for this message
Yasufumi Kinoshita (yasufumi-kinoshita) wrote :

I will add the values as possible as I can.

But only "differential values between SHOW INNODB STATUS" values or values calculated by the such differential values cannot be added.

Revision history for this message
Yasufumi Kinoshita (yasufumi-kinoshita) wrote :

> 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

It is hash_table.
    Lock system 380792 (332872 + 47920)
But I think it is not useful for your purpose. Because it is just about allocation of memory.
print entries in the hash table?

> Also is there any way to access the age of oldest transaction quickly ? (without scanning potentially large list) - this would also be helpful.

age? what the unit?
oldest view can be caught instead of "oldest?" transaction.
"trx_id of the oldest view" is ok?
helpful for what? I don't judge without your purpose.

Revision history for this message
Yasufumi Kinoshita (yasufumi-kinoshita) wrote :
Download full text (4.4 KiB)

reordered what should be added...

The following based on XtraDB-5.5.8.
Are they enough?

"Innodb_" is omitted.

BACKGROUND THREAD############
ulint
master_thread_1_second_loops srv_main_1_second_loops
master_thread_sleeps srv_main_sleeps
master_thread_10_second_loops srv_main_10_second_loops
master_thread_background_loops srv_main_background_loops
master_thread_main_flush_loops srv_main_flush_loops
background_log_sync srv_log_writes_and_flush

SEMAPHORES###################
ib_int64_t
mutex_spin_waits mutex_spin_wait_count
mutex_spin_rounds mutex_spin_round_count
mutex_os_waits mutex_os_wait_count
s_lock_spin_waits rw_s_spin_wait_count
s_lock_spin_rounds rw_s_spin_round_count
s_lock_os_waits rw_s_os_wait_count
x_lock_spin_waits rw_x_spin_wait_count
x_lock_spin_rounds rw_x_spin_round_count
x_lock_os_waits rw_x_os_wait_count

INSERT BUFFER AND ADAPTIVE HASH INDEX##########
ulint
ibuf_size ibuf->size
ibuf_free_list ibuf->free_list_len
ibuf_segment_size ibuf->seg_size
ibuf_merges ibuf->n_merges
ibuf_merged_inserts ibuf->n_merged_ops[IBUF_OP_INSERT]
ibuf_merged_delete_marks ibuf->n_merged_ops[IBUF_OP_DELETE_MARK]
ibuf_merged_deletes ibuf->n_merged_ops[IBUF_OP_DELETE]
ibuf_discarded_inserts ibuf->n_discarded_ops[IBUF_OP_INSERT]
ibuf_discarded_delete_marks ibuf->n_discarded_ops[IBUF_OP_DELETE_MARK]
ibuf_discarded_deletes ibuf->n_discarded_ops[IBUF_OP_DELETE]

        [summed (i = 0; i < btr_search_index_num; i++)]
        hash_table_t* table = btr_search_get_hash_index((index_id_t)i);
adaptive_hash_cells += hash_get_n_cells(table)
adaptive_hash_heap_buffers += (UT_LIST_GET_LEN(table->heap->base) - 1)

adaptive_hash_hash_searches btr_cur_n_sea
adaptive_hash_non_hash_searches btr_cur_n_non_sea

LOG############################
ib_uint64_t
lsn_current log_sys->lsn
lsn_flushed log_sys->flushed_to_disk_lsn
lsn_last_checkpoint log_sys->last_checkpoint_lsn

ulint
checkpoint_max_age log_sys->max_checkpoint_age
checkpoint_target_age log_max_checkpoint_age_async()

###checkpoint_modified_age "dangerous without mutex": impossible not to affect performance much

checkpoint_age (log_sys->lsn - log_sys->last_checkpoint_lsn)

BUFFER POOL AND MEMORY###############
innodb_mem_total ut_total_allocated_memory
innodb_mem_adaptive_hash
        if (btr_search_sys && btr_search_sys->hash_index[0]->heap) {
                btr_search_sys_subtotal = mem_heap_get_size(btr_search_sys->hash_index[0]->heap);
        } else {
                btr_search_sys_subtotal = 0;
                for (i=0; i < btr_search_sys->hash_index[0]->n_mutexes; i++) {
                        btr_search_sys_subtotal += mem_heap_get_size(btr_search_sys->hash_index[0]->heaps[i]);
                }
        }
        btr_search_sys_subtotal *= btr_search_index_num;
        += (btr_search_sys
            ? (btr_search_sys->hash_index[0]->n_cells * btr_search_index_num * ...

Read more...

Changed in percona-server:
status: In Progress → Fix Committed
Revision history for this message
Fred Linhoss (fred-linhoss) wrote :

Yasufumi, please confirm that this is the correct list of names implemented, or correct as necessary:

#####BACKGROUND THREAD#####
innodb_srv_main_1_second_loops
innodb_srv_main_sleeps
innodb_srv_main_10_second_loops
innodb_srv_main_background_loops
innodb_srv_main_flush_loops
innodb_srv_log_writes_and_flush

#####SEMAPHORES#####
innodb_mutex_spin_wait_count
innodb_mutex_spin_round_count
innodb_mutex_os_wait_count
innodb_rw_s_spin_wait_count
innodb_rw_s_spin_round_count
innodb_rw_s_os_wait_count
innodb_rw_x_spin_wait_count
innodb_rw_x_spin_round_count
innodb_rw_x_os_wait_count

#####INSERT BUFFER AND ADAPTIVE HASH INDEX#####
innodb_ibuf_size
innodb_ibuf_free_list_len
innodb_ibuf_seg_size
innodb_ibuf_n_merges
innodb_ibuf_n_merged_ops
innodb_ibuf_n_merged_ops
innodb_ibuf_n_merged_ops
innodb_ibuf_n_discarded_ops
innodb_ibuf_n_discarded_ops
innodb_ibuf_n_discarded_ops
innodb_hash_get_n_cells
innodb_adaptive_hash_heap_buffers - RENAMED?
innodb_btr_cur_n_sea
innodb_btr_cur_n_non_sea

#####LOG#####
innodb_log_sys_lsn
innodb_log_sys_flushed_to_disk_lsn
innodb_log_sys_last_checkpoint_lsn
innodb_log_sys_max_checkpoint_age
innodb_log_max_checkpoint_age_async
innodb_log_sys_last_checkpoint_lsn

#####BUFFER POOL AND MEMORY#####
innodb_innodb_ut_total_allocated_memory
innodb_innodb_mem_adaptive_hash
innodb_innodb_mem_dictionary
innodb_buffer_pool_pages_old - RENAMED?
innodb_buffer_pool_pages_made_young - RENAMED?
innodb_buffer_pool_pages_made_not_young - RENAMED?
innodb_buffer_pool_pages_read_ahead - RENAMED?
innodb_buffer_pool_pages_evicted_without_access - RENAMED?

#####TRANSACTIONS#####
innodb_trx_sys_max_trx_id
innodb_purge_sys_purge_trx_no
innodb_purge_sys_purge_undo_no
innodb_trx_sys_rseg_history_len
innodb_lock_sys_rec_num - RENAMED?
innodb_oldest_view_low_limit_trx_id - RENAMED?

Changed in percona-server:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-455

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.