Innodb_current_row_locks shows very high but other Innodb_* looks normal

[Paul Namuag]

Running SHOW GLOBAL STATUS shows that Innodb_current_row_locks looks very high as shown having its long integer in 64 bit platforms,

Innodb_current_row_locks 18446744073707846050

However, the other Innodb_* values looks normal and are not super high in comparison with Innodb_current_row_locks. See below,

Innodb_adaptive_hash_cells 6375023
Innodb_adaptive_hash_heap_buffers 1097
Innodb_adaptive_hash_hash_searches 87913438
Innodb_adaptive_hash_non_hash_searches 54881124
Innodb_background_log_sync 1396849
Innodb_buffer_pool_pages_data 195508
Innodb_buffer_pool_bytes_data 3203203072
Innodb_buffer_pool_pages_dirty 11
Innodb_buffer_pool_bytes_dirty 180224
Innodb_buffer_pool_pages_flushed 11025338
Innodb_buffer_pool_pages_LRU_flushed 122
Innodb_buffer_pool_pages_free 1
Innodb_buffer_pool_pages_made_not_young 0
Innodb_buffer_pool_pages_made_young 650916
Innodb_buffer_pool_pages_misc 1098
Innodb_buffer_pool_pages_old 72149
Innodb_buffer_pool_pages_total 196607
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 103196
Innodb_buffer_pool_read_ahead_evicted 2789
Innodb_buffer_pool_read_requests 535237341
Innodb_buffer_pool_reads 494510
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 220298934
Innodb_checkpoint_age 2798
Innodb_checkpoint_max_age 433682474
Innodb_checkpoint_target_age 420129897
Innodb_data_fsyncs 2197879
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 10789720064
Innodb_data_reads 659042
Innodb_data_writes 19080736
Innodb_data_written 394192715776
Innodb_dblwr_pages_written 11025338
Innodb_dblwr_writes 175755
Innodb_deadlocks 0
Innodb_dict_tables 680
Innodb_have_atomic_builtins ON
Innodb_history_list_length 2418
Innodb_ibuf_discarded_delete_marks 0
Innodb_ibuf_discarded_deletes 0
Innodb_ibuf_discarded_inserts 0
Innodb_ibuf_free_list 884
Innodb_ibuf_merged_delete_marks 430516
Innodb_ibuf_merged_deletes 1815
Innodb_ibuf_merged_inserts 269744
Innodb_ibuf_merges 63621
Innodb_ibuf_segment_size 886
Innodb_ibuf_size 1
Innodb_log_waits 0
Innodb_log_write_requests 63118179
Innodb_log_writes 7658005
Innodb_lsn_current 805657508770
Innodb_lsn_flushed 805657505972
Innodb_lsn_last_checkpoint 805657505972
Innodb_master_thread_1_second_loops 1206282
Innodb_master_thread_10_second_loops 116394
Innodb_master_thread_background_loops 50618
Innodb_master_thread_main_flush_loops 50618
Innodb_master_thread_sleeps 1206278
Innodb_max_trx_id 753654949
Innodb_mem_adaptive_hash 68994032
Innodb_mem_dictionary 17905089
Innodb_mem_total 3306160128
Innodb_mutex_os_waits 50456
Innodb_mutex_spin_rounds 4877801
Innodb_mutex_spin_waits 1343884
Innodb_oldest_view_low_limit_trx_id 753654943
Innodb_os_log_fsyncs 995768
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 32850601472
Innodb_page_size 16384
Innodb_pages_created 422811
Innodb_pages_read 658418
Innodb_pages_written 11025338
Innodb_purge_trx_id 753654927
Innodb_purge_undo_no 0
Innodb_row_lock_current_waits 0
Innodb_current_row_locks 18446744073707846050
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 6112984
Innodb_rows_inserted 28545278
Innodb_rows_read 36552938
Innodb_rows_updated 1313482
Innodb_read_views_memory 152
Innodb_descriptors_memory 8000
Innodb_s_lock_os_waits 133986
Innodb_s_lock_spin_rounds 4135612
Innodb_s_lock_spin_waits 153072
Innodb_truncated_status_writes 0
Innodb_x_lock_os_waits 22473
Innodb_x_lock_spin_rounds 1013600
Innodb_x_lock_spin_waits 40365

Uptime is not so long as well having,

Uptime 1411925

i.e. 16.3 days.

Innodb_current_row_locks is defined from the manual, in reference to MariaDB XtraDB storage engine, https://mariadb.com/kb/en/xtradbinnodb-server-status-variables/#innodb_current_row_locks (or http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_status.html#Innodb_current_row_locks) as

"Number of current row locks on InnoDB tables as shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output. Renamed from InnoDB_row_lock_numbers in XtraDB 5.5.8-20.1."

Wondering what happened how come it's so very high.

Version used is actually PXC (5.5.34-log Percona XtraDB Cluster (GPL), wsrep_23.7.6.r3936), however, this particular variable is under XtraDB as seen in the manual above.

[Nilnandan Joshi (nilnandan-joshi)]

Tried to reproduce with 5.5.36 and sysbench but unable to find high value.
Can you provide more specific steps or instructions on how to repeat?

nilnandan@nilnandan-Dell-XPS:~/sandboxes/msb_5_5_36$ sysbench --test=oltp --oltp-table-size=1000000 --oltp-test-mode=complex --oltp-read-only=off --num-threads=12 --max-time=60 --max-requests=0 --mysql-db=dbtest --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox5536.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 12

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 11 times)
Done.

OLTP test statistics:
    queries performed:
        read: 798140
        write: 285050
        other: 114020
        total: 1197210
    transactions: 57010 (950.12 per sec.)
    deadlocks: 0 (0.00 per sec.)
    read/write requests: 1083190 (18052.33 per sec.)
    other operations: 114020 (1900.25 per sec.)

Test execution summary:
    total time: 60.0028s
    total number of events: 57010
    total time taken by event execution: 719.4837
    per-request statistics:
         min: 3.15ms
         avg: 12.62ms
         max: 445.79ms
         approx. 95 percentile: 27.68ms

Threads fairness:
    events (avg/stddev): 4750.8333/12.42
    execution time (avg/stddev): 59.9570/0.00

nilnandan@nilnandan-Dell-XPS:~/sandboxes/msb_5_5_36$

During test, it was 4,6 and after test it become 0.

Innodb_current_row_locks | 4
Innodb_current_row_locks | 6
Innodb_current_row_locks | 0

[Roel Van de Paar (roel11)]

See bug 1395253, innodb_row_lock_current_waits spikes to 18446744073709551615

[Launchpad Janitor (janitor)]

[Expired for Percona Server 5.7 because there has been no activity for 60 days.]

[Launchpad Janitor (janitor)]

[Expired for Percona Server 5.5 because there has been no activity for 60 days.]

[Launchpad Janitor (janitor)]

[Expired for Percona Server 5.6 because there has been no activity for 60 days.]

[Shahriyar Rzayev (rzayev-sehriyar)]

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