1 # name : innodb_show_status.patch
2 # introduced : 11 or before
3 # maintainer : Yasufumi
6 # Any small change to this file in the main branch
7 # should be done or reviewed by the maintainer!
8 --- a/storage/innobase/buf/buf0buf.c
9 +++ b/storage/innobase/buf/buf0buf.c
13 total_info->pool_size += pool_info->pool_size;
14 + total_info->pool_size_bytes += pool_info->pool_size_bytes;
15 total_info->lru_len += pool_info->lru_len;
16 total_info->old_lru_len += pool_info->old_lru_len;
17 total_info->free_list_len += pool_info->free_list_len;
20 pool_info->pool_size = buf_pool->curr_size;
22 + pool_info->pool_size_bytes = buf_pool->curr_pool_size;
24 pool_info->lru_len = UT_LIST_GET_LEN(buf_pool->LRU);
26 pool_info->old_lru_len = buf_pool->LRU_old_len;
27 @@ -4544,14 +4547,16 @@
31 - "Buffer pool size %lu\n"
32 - "Free buffers %lu\n"
33 - "Database pages %lu\n"
34 - "Old database pages %lu\n"
35 - "Modified db pages %lu\n"
36 + "Buffer pool size %lu\n"
37 + "Buffer pool size, bytes %lu\n"
38 + "Free buffers %lu\n"
39 + "Database pages %lu\n"
40 + "Old database pages %lu\n"
41 + "Modified db pages %lu\n"
43 "Pending writes: LRU %lu, flush list %lu, single page %lu\n",
45 + pool_info->pool_size_bytes,
46 pool_info->free_list_len,
48 pool_info->old_lru_len,
49 --- a/storage/innobase/buf/buf0flu.c
50 +++ b/storage/innobase/buf/buf0flu.c
52 static buf_flush_stat_t buf_flush_stat_sum;
54 /** Number of pages flushed through non flush_list flushes. */
55 -static ulint buf_lru_flush_page_count = 0;
56 +// static ulint buf_lru_flush_page_count = 0;
60 --- a/storage/innobase/fil/fil0fil.c
61 +++ b/storage/innobase/fil/fil0fil.c
62 @@ -4889,3 +4889,30 @@
67 +/*************************************************************************
68 +Return local hash table informations. */
71 +fil_system_hash_cells(void)
72 +/*=======================*/
75 + return (fil_system->spaces->n_cells
76 + + fil_system->name_hash->n_cells);
83 +fil_system_hash_nodes(void)
84 +/*=======================*/
87 + return (UT_LIST_GET_LEN(fil_system->space_list)
88 + * (sizeof(fil_space_t) + MEM_BLOCK_HEADER_SIZE));
93 --- a/storage/innobase/handler/ha_innodb.cc
94 +++ b/storage/innobase/handler/ha_innodb.cc
96 (char*) &export_vars.innodb_buffer_pool_pages_dirty, SHOW_LONG},
97 {"buffer_pool_pages_flushed",
98 (char*) &export_vars.innodb_buffer_pool_pages_flushed, SHOW_LONG},
99 + {"buffer_pool_pages_LRU_flushed",
100 + (char*) &export_vars.innodb_buffer_pool_pages_LRU_flushed, SHOW_LONG},
101 {"buffer_pool_pages_free",
102 (char*) &export_vars.innodb_buffer_pool_pages_free, SHOW_LONG},
104 @@ -11097,6 +11099,16 @@
105 "Force InnoDB to not use next-key locking, to use only row-level locking.",
108 +static MYSQL_SYSVAR_ULONG(show_verbose_locks, srv_show_verbose_locks,
109 + PLUGIN_VAR_OPCMDARG,
110 + "Whether to show records locked in SHOW INNODB STATUS.",
111 + NULL, NULL, 0, 0, 1, 0);
113 +static MYSQL_SYSVAR_ULONG(show_locks_held, srv_show_locks_held,
114 + PLUGIN_VAR_RQCMDARG,
115 + "Number of locks held to print for each InnoDB transaction in SHOW INNODB STATUS.",
116 + NULL, NULL, 10, 0, 1000, 0);
118 #ifdef UNIV_LOG_ARCHIVE
119 static MYSQL_SYSVAR_STR(log_arch_dir, innobase_log_arch_dir,
120 PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
121 @@ -11284,7 +11296,7 @@
123 static MYSQL_SYSVAR_STR(version, innodb_version_str,
124 PLUGIN_VAR_NOCMDOPT | PLUGIN_VAR_READONLY,
125 - "InnoDB version", NULL, NULL, INNODB_VERSION_STR);
126 + "Percona-InnoDB-plugin version", NULL, NULL, INNODB_VERSION_STR);
128 static MYSQL_SYSVAR_BOOL(use_sys_malloc, srv_use_sys_malloc,
129 PLUGIN_VAR_NOCMDARG | PLUGIN_VAR_READONLY,
130 @@ -11378,6 +11390,8 @@
131 MYSQL_SYSVAR(thread_concurrency),
132 MYSQL_SYSVAR(thread_sleep_delay),
133 MYSQL_SYSVAR(autoinc_lock_mode),
134 + MYSQL_SYSVAR(show_verbose_locks),
135 + MYSQL_SYSVAR(show_locks_held),
136 MYSQL_SYSVAR(version),
137 MYSQL_SYSVAR(use_sys_malloc),
138 MYSQL_SYSVAR(use_native_aio),
139 --- a/storage/innobase/include/buf0buf.h
140 +++ b/storage/innobase/include/buf0buf.h
142 /* General buffer pool info */
143 ulint pool_unique_id; /*!< Buffer Pool ID */
144 ulint pool_size; /*!< Buffer Pool size in pages */
145 + ulint pool_size_bytes;
146 ulint lru_len; /*!< Length of buf_pool->LRU */
147 ulint old_lru_len; /*!< buf_pool->LRU_old_len */
148 ulint free_list_len; /*!< Length of buf_pool->free list */
149 --- a/storage/innobase/include/fil0fil.h
150 +++ b/storage/innobase/include/fil0fil.h
152 /*============================*/
153 ulint id); /*!< in: space id */
155 +/*************************************************************************
156 +Return local hash table informations. */
159 +fil_system_hash_cells(void);
160 +/*========================*/
163 +fil_system_hash_nodes(void);
164 +/*========================*/
166 typedef struct fil_space_struct fil_space_t;
169 --- a/storage/innobase/include/read0read.h
170 +++ b/storage/innobase/include/read0read.h
176 const read_view_t* view); /*!< in: read view */
177 /*********************************************************************//**
178 Create a consistent cursor view for mysql to be used in cursors. In this
179 --- a/storage/innobase/include/srv0srv.h
180 +++ b/storage/innobase/include/srv0srv.h
182 extern char srv_adaptive_flushing;
185 +extern ulint srv_show_locks_held;
186 +extern ulint srv_show_verbose_locks;
188 /* The sort order table of the MySQL latin1_swedish_ci character set
190 extern const byte* srv_latin1_ordering;
192 buffer pool to disk */
193 extern ulint srv_buf_pool_flushed;
195 +extern ulint buf_lru_flush_page_count;
197 /** Number of buffer pool reads that led to the
198 reading of a disk page */
199 extern ulint srv_buf_pool_reads;
201 ulint innodb_buffer_pool_reads; /*!< srv_buf_pool_reads */
202 ulint innodb_buffer_pool_wait_free; /*!< srv_buf_pool_wait_free */
203 ulint innodb_buffer_pool_pages_flushed; /*!< srv_buf_pool_flushed */
204 + ulint innodb_buffer_pool_pages_LRU_flushed; /*!< buf_lru_flush_page_count */
205 ulint innodb_buffer_pool_write_requests;/*!< srv_buf_pool_write_requests */
206 ulint innodb_buffer_pool_read_ahead; /*!< srv_read_ahead */
207 ulint innodb_buffer_pool_read_ahead_evicted;/*!< srv_read_ahead evicted*/
208 --- a/storage/innobase/lock/lock0lock.c
209 +++ b/storage/innobase/lock/lock0lock.c
210 @@ -4377,6 +4377,7 @@
214 + if ( srv_show_verbose_locks ) {
215 block = buf_page_try_get(space, page_no, &mtr);
217 for (i = 0; i < lock_rec_get_n_bits(lock); ++i) {
218 @@ -4403,6 +4404,7 @@
225 if (UNIV_LIKELY_NULL(heap)) {
226 @@ -4586,7 +4588,7 @@
230 - if (!srv_print_innodb_lock_monitor) {
231 + if (!srv_print_innodb_lock_monitor && !srv_show_locks_held) {
235 @@ -4658,8 +4660,8 @@
239 - if (nth_lock >= 10) {
240 - fputs("10 LOCKS PRINTED FOR THIS TRX:"
241 + if (nth_lock >= srv_show_locks_held) {
242 + fputs("TOO MANY LOCKS PRINTED FOR THIS TRX:"
243 " SUPPRESSING FURTHER PRINTS\n",
246 --- a/storage/innobase/read/read0read.c
247 +++ b/storage/innobase/read/read0read.c
248 @@ -357,34 +357,35 @@
253 const read_view_t* view) /*!< in: read view */
258 if (view->type == VIEW_HIGH_GRANULARITY) {
261 "High-granularity read view undo_n:o %llu\n",
262 (ullint) view->undo_no);
264 - fprintf(stderr, "Normal read view\n");
265 + fprintf(file, "Normal read view\n");
268 - fprintf(stderr, "Read view low limit trx n:o " TRX_ID_FMT "\n",
269 + fprintf(file, "Read view low limit trx n:o " TRX_ID_FMT "\n",
270 (ullint) view->low_limit_no);
272 - fprintf(stderr, "Read view up limit trx id " TRX_ID_FMT "\n",
273 + fprintf(file, "Read view up limit trx id " TRX_ID_FMT "\n",
274 (ullint) view->up_limit_id);
276 - fprintf(stderr, "Read view low limit trx id " TRX_ID_FMT "\n",
277 + fprintf(file, "Read view low limit trx id " TRX_ID_FMT "\n",
278 (ullint) view->low_limit_id);
280 - fprintf(stderr, "Read view individually stored trx ids:\n");
281 + fprintf(file, "Read view individually stored trx ids:\n");
283 n_ids = view->n_trx_ids;
285 for (i = 0; i < n_ids; i++) {
286 - fprintf(stderr, "Read view trx id " TRX_ID_FMT "\n",
287 + fprintf(file, "Read view trx id " TRX_ID_FMT "\n",
288 (ullint) read_view_get_nth_trx_id(view, i));
291 --- a/storage/innobase/srv/srv0srv.c
292 +++ b/storage/innobase/srv/srv0srv.c
294 #include "ha_prototypes.h"
296 #include "os0sync.h" /* for HAVE_ATOMIC_BUILTINS */
297 +#include "read0read.h"
298 #include "mysql/plugin.h"
299 #include "mysql/service_thd_wait.h"
303 UNIV_INTERN char srv_adaptive_flushing = TRUE;
305 +UNIV_INTERN ulint srv_show_locks_held = 10;
306 +UNIV_INTERN ulint srv_show_verbose_locks = 0;
308 /** Maximum number of times allowed to conditionally acquire
309 mutex before switching to blocking wait on the mutex */
310 #define MAX_MUTEX_NOWAIT 20
312 /* variable to count the number of pages that were written from buffer
314 UNIV_INTERN ulint srv_buf_pool_flushed = 0;
315 +UNIV_INTERN ulint buf_lru_flush_page_count = 0;
317 /** Number of buffer pool reads that led to the
318 reading of a disk page */
319 @@ -1823,6 +1828,13 @@
323 + ulint btr_search_sys_subtotal;
324 + ulint lock_sys_subtotal;
325 + ulint recv_sys_subtotal;
330 mutex_enter(&srv_innodb_monitor_mutex);
332 current_time = time(NULL);
333 @@ -1871,31 +1883,6 @@
335 mutex_exit(&dict_foreign_err_mutex);
337 - /* Only if lock_print_info_summary proceeds correctly,
338 - before we call the lock_print_info_all_transactions
339 - to print all the lock information. */
340 - ret = lock_print_info_summary(file, nowait);
344 - long t = ftell(file);
346 - *trx_start = ULINT_UNDEFINED;
348 - *trx_start = (ulint) t;
351 - lock_print_info_all_transactions(file);
353 - long t = ftell(file);
355 - *trx_end = ULINT_UNDEFINED;
357 - *trx_end = (ulint) t;
365 @@ -1926,10 +1913,78 @@
366 "BUFFER POOL AND MEMORY\n"
367 "----------------------\n", file);
369 - "Total memory allocated " ULINTPF
370 - "; in additional pool allocated " ULINTPF "\n",
371 - ut_total_allocated_memory,
372 - mem_pool_get_reserved(mem_comm_pool));
373 + "Total memory allocated " ULINTPF
374 + "; in additional pool allocated " ULINTPF "\n",
375 + ut_total_allocated_memory,
376 + mem_pool_get_reserved(mem_comm_pool));
377 + /* Calcurate reserved memories */
378 + if (btr_search_sys && btr_search_sys->hash_index->heap) {
379 + btr_search_sys_subtotal = mem_heap_get_size(btr_search_sys->hash_index->heap);
381 + btr_search_sys_subtotal = 0;
382 + for (i=0; i < btr_search_sys->hash_index->n_mutexes; i++) {
383 + btr_search_sys_subtotal += mem_heap_get_size(btr_search_sys->hash_index->heaps[i]);
387 + lock_sys_subtotal = 0;
389 + mutex_enter(&kernel_mutex);
390 + trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list);
392 + lock_sys_subtotal += ((trx->lock_heap) ? mem_heap_get_size(trx->lock_heap) : 0);
393 + trx = UT_LIST_GET_NEXT(mysql_trx_list, trx);
395 + mutex_exit(&kernel_mutex);
398 + recv_sys_subtotal = ((recv_sys && recv_sys->addr_hash)
399 + ? mem_heap_get_size(recv_sys->heap) : 0);
402 + "Internal hash tables (constant factor + variable factor)\n"
403 + " Adaptive hash index %lu \t(%lu + %lu)\n"
404 + " Page hash %lu (buffer pool 0 only)\n"
405 + " Dictionary cache %lu \t(%lu + %lu)\n"
406 + " File system %lu \t(%lu + %lu)\n"
407 + " Lock system %lu \t(%lu + %lu)\n"
408 + " Recovery system %lu \t(%lu + %lu)\n",
410 + (ulong) (btr_search_sys
411 + ? (btr_search_sys->hash_index->n_cells * sizeof(hash_cell_t)) : 0)
412 + + btr_search_sys_subtotal,
413 + (ulong) (btr_search_sys
414 + ? (btr_search_sys->hash_index->n_cells * sizeof(hash_cell_t)) : 0),
415 + (ulong) btr_search_sys_subtotal,
417 + (ulong) (buf_pool_from_array(0)->page_hash->n_cells * sizeof(hash_cell_t)),
419 + (ulong) (dict_sys ? ((dict_sys->table_hash->n_cells
420 + + dict_sys->table_id_hash->n_cells
421 + ) * sizeof(hash_cell_t)
422 + + dict_sys->size) : 0),
423 + (ulong) (dict_sys ? ((dict_sys->table_hash->n_cells
424 + + dict_sys->table_id_hash->n_cells
425 + ) * sizeof(hash_cell_t)) : 0),
426 + (ulong) (dict_sys ? (dict_sys->size) : 0),
428 + (ulong) (fil_system_hash_cells() * sizeof(hash_cell_t)
429 + + fil_system_hash_nodes()),
430 + (ulong) (fil_system_hash_cells() * sizeof(hash_cell_t)),
431 + (ulong) fil_system_hash_nodes(),
433 + (ulong) ((lock_sys ? (lock_sys->rec_hash->n_cells * sizeof(hash_cell_t)) : 0)
434 + + lock_sys_subtotal),
435 + (ulong) (lock_sys ? (lock_sys->rec_hash->n_cells * sizeof(hash_cell_t)) : 0),
436 + (ulong) lock_sys_subtotal,
438 + (ulong) (((recv_sys && recv_sys->addr_hash)
439 + ? (recv_sys->addr_hash->n_cells * sizeof(hash_cell_t)) : 0)
440 + + recv_sys_subtotal),
441 + (ulong) ((recv_sys && recv_sys->addr_hash)
442 + ? (recv_sys->addr_hash->n_cells * sizeof(hash_cell_t)) : 0),
443 + (ulong) recv_sys_subtotal);
445 fprintf(file, "Dictionary memory allocated " ULINTPF "\n",
448 @@ -1945,6 +2000,16 @@
449 fprintf(file, "%lu read views open inside InnoDB\n",
450 UT_LIST_GET_LEN(trx_sys->view_list));
452 + if (UT_LIST_GET_LEN(trx_sys->view_list)) {
453 + read_view_t* view = UT_LIST_GET_LAST(trx_sys->view_list);
456 + fprintf(file, "---OLDEST VIEW---\n");
457 + read_view_print(file, view);
458 + fprintf(file, "-----------------\n");
462 n_reserved = fil_space_get_n_reserved_extents(0);
463 if (n_reserved > 0) {
465 @@ -1988,6 +2053,31 @@
466 srv_n_rows_deleted_old = srv_n_rows_deleted;
467 srv_n_rows_read_old = srv_n_rows_read;
469 + /* Only if lock_print_info_summary proceeds correctly,
470 + before we call the lock_print_info_all_transactions
471 + to print all the lock information. */
472 + ret = lock_print_info_summary(file, nowait);
476 + long t = ftell(file);
478 + *trx_start = ULINT_UNDEFINED;
480 + *trx_start = (ulint) t;
483 + lock_print_info_all_transactions(file);
485 + long t = ftell(file);
487 + *trx_end = ULINT_UNDEFINED;
489 + *trx_end = (ulint) t;
494 fputs("----------------------------\n"
495 "END OF INNODB MONITOR OUTPUT\n"
496 "============================\n", file);
497 @@ -2031,6 +2121,7 @@
498 = srv_buf_pool_write_requests;
499 export_vars.innodb_buffer_pool_wait_free = srv_buf_pool_wait_free;
500 export_vars.innodb_buffer_pool_pages_flushed = srv_buf_pool_flushed;
501 + export_vars.innodb_buffer_pool_pages_LRU_flushed = buf_lru_flush_page_count;
502 export_vars.innodb_buffer_pool_reads = srv_buf_pool_reads;
503 export_vars.innodb_buffer_pool_read_ahead
504 = stat.n_ra_pages_read;
505 --- a/storage/innobase/sync/sync0arr.c
506 +++ b/storage/innobase/sync/sync0arr.c
510 "--Thread %lu has waited at %s line %lu"
511 - " for %.2f seconds the semaphore:\n",
512 + " for %#.5g seconds the semaphore:\n",
513 (ulong) os_thread_pf(cell->thread),
514 innobase_basename(cell->file), (ulong) cell->line,
515 difftime(time(NULL), cell->reservation_time));
516 --- a/storage/innobase/trx/trx0purge.c
517 +++ b/storage/innobase/trx/trx0purge.c
518 @@ -1212,7 +1212,7 @@
519 /*=====================*/
521 fprintf(stderr, "InnoDB: Purge system view:\n");
522 - read_view_print(purge_sys->view);
523 + read_view_print(stderr, purge_sys->view);
525 fprintf(stderr, "InnoDB: Purge trx n:o " TRX_ID_FMT
526 ", undo n:o " TRX_ID_FMT "\n",