innodb_dict_size_limit.patch

   1 # name       : innodb_dict_size_limit.patch
   2 # introduced : 11 or before
   3 # maintainer : Yasufumi
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 --- a/storage/innobase/btr/btr0sea.c
   9 +++ b/storage/innobase/btr/btr0sea.c
  10 @@ -1187,6 +1187,178 @@
  11         mem_free(folds);
  12  }
  13
  14 +/************************************************************************
  15 +Drops a page hash index based on index */
  16 +UNIV_INTERN
  17 +void
  18 +btr_search_drop_page_hash_index_on_index(
  19 +/*=====================================*/
  20 +       dict_index_t*   index)          /* in: record descriptor */
  21 +{
  22 +
  23 +       hash_table_t*   table;
  24 +       buf_block_t*    block;
  25 +       ulint           n_fields;
  26 +       ulint           n_bytes;
  27 +       const page_t*           page;
  28 +       const rec_t*            rec;
  29 +       ulint           fold;
  30 +       ulint           prev_fold;
  31 +       index_id_t      index_id;
  32 +       ulint           n_cached;
  33 +       ulint           n_recs;
  34 +       ulint*          folds;
  35 +       ulint           i, j;
  36 +       mem_heap_t*     heap    = NULL;
  37 +       ulint*          offsets;
  38 +       ibool           released_search_latch;
  39 +
  40 +       rw_lock_s_lock(&btr_search_latch);
  41 +
  42 +       table = btr_search_sys->hash_index;
  43 +
  44 +       for (j = 0; j < srv_buf_pool_instances; j++) {
  45 +               buf_pool_t*     buf_pool;
  46 +
  47 +               buf_pool = buf_pool_from_array(j);
  48 +
  49 +               do {
  50 +                       buf_chunk_t*    chunks  = buf_pool->chunks;
  51 +                       buf_chunk_t*    chunk   = chunks + buf_pool->n_chunks;
  52 +
  53 +                       released_search_latch = FALSE;
  54 +
  55 +                       while (--chunk >= chunks) {
  56 +                               block   = chunk->blocks;
  57 +                               i       = chunk->size;
  58 +
  59 +retry:
  60 +                               for (; i--; block++) {
  61 +                                       if (buf_block_get_state(block)
  62 +                                           != BUF_BLOCK_FILE_PAGE
  63 +                                           || block->index != index
  64 +                                           || !block->index) {
  65 +                                               continue;
  66 +                                       }
  67 +
  68 +                                       page = block->frame;
  69 +
  70 +                                       /* from btr_search_drop_page_hash_index() */
  71 +                                       n_fields = block->curr_n_fields;
  72 +                                       n_bytes = block->curr_n_bytes;
  73 +
  74 +
  75 +                                       /* keeping latch order */
  76 +                                       rw_lock_s_unlock(&btr_search_latch);
  77 +                                       released_search_latch = TRUE;
  78 +                                       rw_lock_x_lock(&block->lock);
  79 +
  80 +
  81 +                                       ut_a(n_fields + n_bytes > 0);
  82 +
  83 +                                       n_recs = page_get_n_recs(page);
  84 +
  85 +                                       /* Calculate and cache fold values into an array for fast deletion
  86 +                                       from the hash index */
  87 +
  88 +                                       folds = mem_alloc(n_recs * sizeof(ulint));
  89 +
  90 +                                       n_cached = 0;
  91 +
  92 +                                       rec = page_get_infimum_rec(page);
  93 +                                       rec = page_rec_get_next_low(rec, page_is_comp(page));
  94 +
  95 +                                       index_id = btr_page_get_index_id(page);
  96 +
  97 +                                       ut_a(index_id == index->id);
  98 +
  99 +                                       prev_fold = 0;
 100 +
 101 +                                       offsets = NULL;
 102 +
 103 +                                       while (!page_rec_is_supremum(rec)) {
 104 +                                               offsets = rec_get_offsets(rec, index, offsets,
 105 +                                                                       n_fields + (n_bytes > 0), &heap);
 106 +                                               ut_a(rec_offs_n_fields(offsets) == n_fields + (n_bytes > 0));
 107 +                                               fold = rec_fold(rec, offsets, n_fields, n_bytes, index_id);
 108 +
 109 +                                               if (fold == prev_fold && prev_fold != 0) {
 110 +
 111 +                                                       goto next_rec;
 112 +                                               }
 113 +
 114 +                                               /* Remove all hash nodes pointing to this page from the
 115 +                                               hash chain */
 116 +
 117 +                                               folds[n_cached] = fold;
 118 +                                               n_cached++;
 119 +next_rec:
 120 +                                               rec = page_rec_get_next_low(rec, page_rec_is_comp(rec));
 121 +                                               prev_fold = fold;
 122 +                                       }
 123 +
 124 +                                       if (UNIV_LIKELY_NULL(heap)) {
 125 +                                               mem_heap_empty(heap);
 126 +                                       }
 127 +
 128 +                                       rw_lock_x_lock(&btr_search_latch);
 129 +
 130 +                                       if (UNIV_UNLIKELY(!block->index)) {
 131 +                                               goto cleanup;
 132 +                                       }
 133 +
 134 +                                       ut_a(block->index == index);
 135 +
 136 +                                       if (UNIV_UNLIKELY(block->curr_n_fields != n_fields)
 137 +                                           || UNIV_UNLIKELY(block->curr_n_bytes != n_bytes)) {
 138 +                                               rw_lock_x_unlock(&btr_search_latch);
 139 +                                               rw_lock_x_unlock(&block->lock);
 140 +
 141 +                                               mem_free(folds);
 142 +
 143 +                                               rw_lock_s_lock(&btr_search_latch);
 144 +                                               goto retry;
 145 +                                       }
 146 +
 147 +                                       for (i = 0; i < n_cached; i++) {
 148 +
 149 +                                               ha_remove_all_nodes_to_page(table, folds[i], page);
 150 +                                       }
 151 +
 152 +                                       ut_a(index->search_info->ref_count > 0);
 153 +                                       index->search_info->ref_count--;
 154 +
 155 +                                       block->index = NULL;
 156 +
 157 +cleanup:
 158 +#if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG
 159 +                                       if (UNIV_UNLIKELY(block->n_pointers)) {
 160 +                                               /* Corruption */
 161 +                                               ut_print_timestamp(stderr);
 162 +                                               fprintf(stderr,
 163 +"InnoDB: The adaptive hash index is corrupted. After dropping\n"
 164 +"InnoDB: the hash index to a page of %s, %lu hash nodes still remain.\n",
 165 +                                                       index->name, (ulong) block->n_pointers);
 166 +                                       }
 167 +#endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */
 168 +                                       rw_lock_x_unlock(&btr_search_latch);
 169 +                                       rw_lock_x_unlock(&block->lock);
 170 +
 171 +                                       mem_free(folds);
 172 +
 173 +                                       rw_lock_s_lock(&btr_search_latch);
 174 +                               }
 175 +                       }
 176 +               } while (released_search_latch);
 177 +       }
 178 +
 179 +       rw_lock_s_unlock(&btr_search_latch);
 180 +
 181 +       if (UNIV_LIKELY_NULL(heap)) {
 182 +               mem_heap_free(heap);
 183 +       }
 184 +}
 185 +
 186  /********************************************************************//**
 187  Drops a possible page hash index when a page is evicted from the buffer pool
 188  or freed in a file segment. */
 189 --- a/storage/innobase/buf/buf0buf.c
 190 +++ b/storage/innobase/buf/buf0buf.c
 191 @@ -294,14 +294,14 @@
 192  # endif /* !PFS_SKIP_BUFFER_MUTEX_RWLOCK */
 193  #endif /* UNIV_PFS_MUTEX || UNIV_PFS_RWLOCK */
 194
 195 -/** A chunk of buffers.  The buffer pool is allocated in chunks. */
 196 -struct buf_chunk_struct{
 197 -       ulint           mem_size;       /*!< allocated size of the chunk */
 198 -       ulint           size;           /*!< size of frames[] and blocks[] */
 199 -       void*           mem;            /*!< pointer to the memory area which
 200 -                                       was allocated for the frames */
 201 -       buf_block_t*    blocks;         /*!< array of buffer control blocks */
 202 -};
 203 +/** A chunk of buffers.  The buffer pool is allocated in chunks. (moved to buf0buf.h)*/
 204 +//struct buf_chunk_struct{
 205 +//     ulint           mem_size;       /*!< allocated size of the chunk */
 206 +//     ulint           size;           /*!< size of frames[] and blocks[] */
 207 +//     void*           mem;            /*!< pointer to the memory area which
 208 +//                                     was allocated for the frames */
 209 +//     buf_block_t*    blocks;         /*!< array of buffer control blocks */
 210 +//};
 211  #endif /* !UNIV_HOTBACKUP */
 212
 213  /********************************************************************//**
 214 --- a/storage/innobase/dict/dict0boot.c
 215 +++ b/storage/innobase/dict/dict0boot.c
 216 @@ -284,6 +284,7 @@
 217         system tables */
 218         /*-------------------------*/
 219         table = dict_mem_table_create("SYS_TABLES", DICT_HDR_SPACE, 8, 0);
 220 +       table->n_mysql_handles_opened = 1; /* for pin */
 221
 222         dict_mem_table_add_col(table, heap, "NAME", DATA_BINARY, 0, 0);
 223         dict_mem_table_add_col(table, heap, "ID", DATA_BINARY, 0, 0);
 224 @@ -336,6 +337,7 @@
 225
 226         /*-------------------------*/
 227         table = dict_mem_table_create("SYS_COLUMNS", DICT_HDR_SPACE, 7, 0);
 228 +       table->n_mysql_handles_opened = 1; /* for pin */
 229
 230         dict_mem_table_add_col(table, heap, "TABLE_ID", DATA_BINARY, 0, 0);
 231         dict_mem_table_add_col(table, heap, "POS", DATA_INT, 0, 4);
 232 @@ -368,6 +370,7 @@
 233
 234         /*-------------------------*/
 235         table = dict_mem_table_create("SYS_INDEXES", DICT_HDR_SPACE, 7, 0);
 236 +       table->n_mysql_handles_opened = 1; /* for pin */
 237
 238         dict_mem_table_add_col(table, heap, "TABLE_ID", DATA_BINARY, 0, 0);
 239         dict_mem_table_add_col(table, heap, "ID", DATA_BINARY, 0, 0);
 240 @@ -413,6 +416,7 @@
 241
 242         /*-------------------------*/
 243         table = dict_mem_table_create("SYS_FIELDS", DICT_HDR_SPACE, 3, 0);
 244 +       table->n_mysql_handles_opened = 1; /* for pin */
 245
 246         dict_mem_table_add_col(table, heap, "INDEX_ID", DATA_BINARY, 0, 0);
 247         dict_mem_table_add_col(table, heap, "POS", DATA_INT, 0, 4);
 248 --- a/storage/innobase/dict/dict0crea.c
 249 +++ b/storage/innobase/dict/dict0crea.c
 250 @@ -1209,6 +1209,9 @@
 251                 /* Foreign constraint system tables have already been
 252                 created, and they are ok */
 253
 254 +               table1->n_mysql_handles_opened = 1; /* for pin */
 255 +               table2->n_mysql_handles_opened = 1; /* for pin */
 256 +
 257                 mutex_exit(&(dict_sys->mutex));
 258
 259                 return(DB_SUCCESS);
 260 @@ -1290,6 +1293,11 @@
 261
 262         trx_commit_for_mysql(trx);
 263
 264 +       table1 = dict_table_get_low("SYS_FOREIGN");
 265 +       table2 = dict_table_get_low("SYS_FOREIGN_COLS");
 266 +       table1->n_mysql_handles_opened = 1; /* for pin */
 267 +       table2->n_mysql_handles_opened = 1; /* for pin */
 268 +
 269         row_mysql_unlock_data_dictionary(trx);
 270
 271         trx_free_for_mysql(trx);
 272 --- a/storage/innobase/dict/dict0dict.c
 273 +++ b/storage/innobase/dict/dict0dict.c
 274 @@ -626,6 +626,8 @@
 275
 276         table = dict_table_get_on_id_low(table_id);
 277
 278 +       dict_table_LRU_trim(table);
 279 +
 280         mutex_exit(&(dict_sys->mutex));
 281
 282         return(table);
 283 @@ -744,6 +746,8 @@
 284                 table->n_mysql_handles_opened++;
 285         }
 286
 287 +       dict_table_LRU_trim(table);
 288 +
 289         mutex_exit(&(dict_sys->mutex));
 290
 291         if (table != NULL) {
 292 @@ -1264,6 +1268,64 @@
 293         dict_mem_table_free(table);
 294  }
 295
 296 +/**************************************************************************
 297 +Frees tables from the end of table_LRU if the dictionary cache occupies
 298 +too much space. */
 299 +UNIV_INTERN
 300 +void
 301 +dict_table_LRU_trim(
 302 +/*================*/
 303 +       dict_table_t*   self)
 304 +{
 305 +       dict_table_t*   table;
 306 +       dict_table_t*   prev_table;
 307 +       dict_foreign_t* foreign;
 308 +       ulint           n_removed;
 309 +       ulint           n_have_parent;
 310 +       ulint           cached_foreign_tables;
 311 +
 312 +#ifdef UNIV_SYNC_DEBUG
 313 +       ut_ad(mutex_own(&(dict_sys->mutex)));
 314 +#endif /* UNIV_SYNC_DEBUG */
 315 +
 316 +retry:
 317 +       n_removed = n_have_parent = 0;
 318 +       table = UT_LIST_GET_LAST(dict_sys->table_LRU);
 319 +
 320 +       while ( srv_dict_size_limit && table
 321 +               && ((dict_sys->table_hash->n_cells
 322 +                    + dict_sys->table_id_hash->n_cells) * sizeof(hash_cell_t)
 323 +                   + dict_sys->size) > srv_dict_size_limit ) {
 324 +               prev_table = UT_LIST_GET_PREV(table_LRU, table);
 325 +
 326 +               if (table == self || table->n_mysql_handles_opened)
 327 +                       goto next_loop;
 328 +
 329 +               cached_foreign_tables = 0;
 330 +               foreign = UT_LIST_GET_FIRST(table->foreign_list);
 331 +               while (foreign != NULL) {
 332 +                       if (foreign->referenced_table)
 333 +                               cached_foreign_tables++;
 334 +                       foreign = UT_LIST_GET_NEXT(foreign_list, foreign);
 335 +               }
 336 +
 337 +               if (cached_foreign_tables == 0) {
 338 +                       dict_table_remove_from_cache(table);
 339 +                       n_removed++;
 340 +               } else {
 341 +                       n_have_parent++;
 342 +               }
 343 +next_loop:
 344 +               table = prev_table;
 345 +       }
 346 +
 347 +       if ( srv_dict_size_limit && n_have_parent && n_removed
 348 +               && ((dict_sys->table_hash->n_cells
 349 +                    + dict_sys->table_id_hash->n_cells) * sizeof(hash_cell_t)
 350 +                   + dict_sys->size) > srv_dict_size_limit )
 351 +               goto retry;
 352 +}
 353 +
 354  /****************************************************************//**
 355  If the given column name is reserved for InnoDB system columns, return
 356  TRUE.
 357 @@ -1768,6 +1830,11 @@
 358         ut_ad(index->magic_n == DICT_INDEX_MAGIC_N);
 359         ut_ad(mutex_own(&(dict_sys->mutex)));
 360
 361 +       /* remove all entry of the index from adaptive hash index,
 362 +       because removing from adaptive hash index needs dict_index */
 363 +       if (btr_search_enabled && srv_dict_size_limit)
 364 +               btr_search_drop_page_hash_index_on_index(index);
 365 +
 366         /* We always create search info whether or not adaptive
 367         hash index is enabled or not. */
 368         info = index->search_info;
 369 --- a/storage/innobase/handler/ha_innodb.cc
 370 +++ b/storage/innobase/handler/ha_innodb.cc
 371 @@ -677,6 +677,8 @@
 372    (char*) &export_vars.innodb_dblwr_pages_written,       SHOW_LONG},
 373    {"dblwr_writes",
 374    (char*) &export_vars.innodb_dblwr_writes,              SHOW_LONG},
 375 +  {"dict_tables",
 376 +  (char*) &export_vars.innodb_dict_tables,               SHOW_LONG},
 377    {"have_atomic_builtins",
 378    (char*) &export_vars.innodb_have_atomic_builtins,      SHOW_BOOL},
 379    {"log_waits",
 380 @@ -11813,6 +11815,11 @@
 381    NULL, NULL, 0, 0, 1, 0);
 382  #endif
 383
 384 +static MYSQL_SYSVAR_ULONG(dict_size_limit, srv_dict_size_limit,
 385 +  PLUGIN_VAR_RQCMDARG,
 386 +  "Limit the allocated memory for dictionary cache. (0: unlimited)",
 387 +  NULL, NULL, 0, 0, LONG_MAX, 0);
 388 +
 389  static struct st_mysql_sys_var* innobase_system_variables[]= {
 390    MYSQL_SYSVAR(additional_mem_pool_size),
 391    MYSQL_SYSVAR(autoextend_increment),
 392 @@ -11882,6 +11889,7 @@
 393    MYSQL_SYSVAR(flush_neighbor_pages),
 394    MYSQL_SYSVAR(read_ahead),
 395    MYSQL_SYSVAR(adaptive_flushing_method),
 396 +  MYSQL_SYSVAR(dict_size_limit),
 397    MYSQL_SYSVAR(use_sys_malloc),
 398    MYSQL_SYSVAR(use_native_aio),
 399    MYSQL_SYSVAR(change_buffering),
 400 --- a/storage/innobase/ibuf/ibuf0ibuf.c
 401 +++ b/storage/innobase/ibuf/ibuf0ibuf.c
 402 @@ -575,6 +575,7 @@
 403
 404         /* Use old-style record format for the insert buffer. */
 405         table = dict_mem_table_create(IBUF_TABLE_NAME, IBUF_SPACE_ID, 1, 0);
 406 +       table->n_mysql_handles_opened = 1; /* for pin */
 407
 408         dict_mem_table_add_col(table, heap, "DUMMY_COLUMN", DATA_BINARY, 0, 0);
 409
 410 --- a/storage/innobase/include/btr0sea.h
 411 +++ b/storage/innobase/include/btr0sea.h
 412 @@ -140,6 +140,13 @@
 413                                 s- or x-latched, or an index page
 414                                 for which we know that
 415                                 block->buf_fix_count == 0 */
 416 +/************************************************************************
 417 +Drops a page hash index based on index */
 418 +UNIV_INTERN
 419 +void
 420 +btr_search_drop_page_hash_index_on_index(
 421 +/*=====================================*/
 422 +       dict_index_t*   index);         /* in: record descriptor */
 423  /********************************************************************//**
 424  Drops a possible page hash index when a page is evicted from the buffer pool
 425  or freed in a file segment. */
 426 --- a/storage/innobase/include/buf0buf.h
 427 +++ b/storage/innobase/include/buf0buf.h
 428 @@ -1595,6 +1595,15 @@
 429  #define BUF_POOL_ZIP_FOLD_BPAGE(b) BUF_POOL_ZIP_FOLD((buf_block_t*) (b))
 430  /* @} */
 431
 432 +/** A chunk of buffers.  The buffer pool is allocated in chunks. */
 433 +struct buf_chunk_struct{
 434 +       ulint           mem_size;       /*!< allocated size of the chunk */
 435 +       ulint           size;           /*!< size of frames[] and blocks[] */
 436 +       void*           mem;            /*!< pointer to the memory area which
 437 +                                       was allocated for the frames */
 438 +       buf_block_t*    blocks;         /*!< array of buffer control blocks */
 439 +};
 440 +
 441  /** @brief The buffer pool statistics structure. */
 442  struct buf_pool_stat_struct{
 443         ulint   n_page_gets;    /*!< number of page gets performed;
 444 --- a/storage/innobase/include/dict0dict.h
 445 +++ b/storage/innobase/include/dict0dict.h
 446 @@ -1200,6 +1200,12 @@
 447  /*====================================*/
 448         dict_table_t*   table,  /*!< in: table */
 449         const char*     name);  /*!< in: name of the index to find */
 450 +
 451 +UNIV_INTERN
 452 +void
 453 +dict_table_LRU_trim(
 454 +/*================*/
 455 +       dict_table_t*   self);
 456  /* Buffers for storing detailed information about the latest foreign key
 457  and unique key errors */
 458  extern FILE*   dict_foreign_err_file;
 459 --- a/storage/innobase/include/dict0dict.ic
 460 +++ b/storage/innobase/include/dict0dict.ic
 461 @@ -825,6 +825,13 @@
 462         HASH_SEARCH(name_hash, dict_sys->table_hash, table_fold,
 463                     dict_table_t*, table, ut_ad(table->cached),
 464                     !strcmp(table->name, table_name));
 465 +
 466 +       /* make young in table_LRU */
 467 +       if (table) {
 468 +               UT_LIST_REMOVE(table_LRU, dict_sys->table_LRU, table);
 469 +               UT_LIST_ADD_FIRST(table_LRU, dict_sys->table_LRU, table);
 470 +       }
 471 +
 472         return(table);
 473  }
 474
 475 @@ -918,6 +925,12 @@
 476                 table = dict_load_table_on_id(table_id);
 477         }
 478
 479 +       /* make young in table_LRU */
 480 +       if (table) {
 481 +               UT_LIST_REMOVE(table_LRU, dict_sys->table_LRU, table);
 482 +               UT_LIST_ADD_FIRST(table_LRU, dict_sys->table_LRU, table);
 483 +       }
 484 +
 485         ut_ad(!table || table->cached);
 486
 487         /* TODO: should get the type information from MySQL */
 488 --- a/storage/innobase/include/srv0srv.h
 489 +++ b/storage/innobase/include/srv0srv.h
 490 @@ -234,6 +234,7 @@
 491  extern ulint   srv_read_ahead;
 492  extern ulint   srv_adaptive_flushing_method;
 493
 494 +extern ulint   srv_dict_size_limit;
 495  /*-------------------------------------------*/
 496
 497  extern ulint   srv_n_rows_inserted;
 498 @@ -717,6 +718,7 @@
 499         ulint innodb_data_writes;               /*!< I/O write requests */
 500         ulint innodb_data_written;              /*!< Data bytes written */
 501         ulint innodb_data_reads;                /*!< I/O read requests */
 502 +       ulint innodb_dict_tables;
 503         ulint innodb_buffer_pool_pages_total;   /*!< Buffer pool size */
 504         ulint innodb_buffer_pool_pages_data;    /*!< Data pages */
 505         ulint innodb_buffer_pool_pages_dirty;   /*!< Dirty data pages */
 506 --- a/storage/innobase/srv/srv0srv.c
 507 +++ b/storage/innobase/srv/srv0srv.c
 508 @@ -417,6 +417,8 @@
 509  UNIV_INTERN ulint      srv_enable_unsafe_group_commit = 0; /* 0:disable 1:enable */
 510  UNIV_INTERN ulint      srv_read_ahead = 3; /* 1: random  2: linear  3: Both */
 511  UNIV_INTERN ulint      srv_adaptive_flushing_method = 0; /* 0: native  1: estimate  2: keep_average */
 512 +
 513 +UNIV_INTERN ulint      srv_dict_size_limit = 0;
 514  /*-------------------------------------------*/
 515  UNIV_INTERN ulong      srv_n_spin_wait_rounds  = 30;
 516  UNIV_INTERN ulong      srv_n_free_tickets_to_enter = 500;
 517 @@ -2225,6 +2227,7 @@
 518         export_vars.innodb_data_reads = os_n_file_reads;
 519         export_vars.innodb_data_writes = os_n_file_writes;
 520         export_vars.innodb_data_written = srv_data_written;
 521 +       export_vars.innodb_dict_tables= (dict_sys ? UT_LIST_GET_LEN(dict_sys->table_LRU) : 0);
 522         export_vars.innodb_buffer_pool_read_requests = stat.n_page_gets;
 523         export_vars.innodb_buffer_pool_write_requests
 524                 = srv_buf_pool_write_requests;
 525 --- /dev/null
 526 +++ b/mysql-test/suite/sys_vars/r/innodb_dict_size_limit_basic.result
 527 @@ -0,0 +1,3 @@
 528 +SELECT @@global.innodb_dict_size_limit;
 529 +@@global.innodb_dict_size_limit
 530 +0
 531 --- /dev/null
 532 +++ b/mysql-test/suite/sys_vars/t/innodb_dict_size_limit_basic.test
 533 @@ -0,0 +1 @@
 534 +SELECT @@global.innodb_dict_size_limit;