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 @@ -1185,6 +1185,179 @@
  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->is_hashed) {
  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->is_hashed)) {
 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->is_hashed = FALSE;
 156 +                                       block->index = NULL;
 157 +
 158 +cleanup:
 159 +#if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG
 160 +                                       if (UNIV_UNLIKELY(block->n_pointers)) {
 161 +                                               /* Corruption */
 162 +                                               ut_print_timestamp(stderr);
 163 +                                               fprintf(stderr,
 164 +"InnoDB: The adaptive hash index is corrupted. After dropping\n"
 165 +"InnoDB: the hash index to a page of %s, %lu hash nodes still remain.\n",
 166 +                                                       index->name, (ulong) block->n_pointers);
 167 +                                       }
 168 +#endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */
 169 +                                       rw_lock_x_unlock(&btr_search_latch);
 170 +                                       rw_lock_x_unlock(&block->lock);
 171 +
 172 +                                       mem_free(folds);
 173 +
 174 +                                       rw_lock_s_lock(&btr_search_latch);
 175 +                               }
 176 +                       }
 177 +               } while (released_search_latch);
 178 +       }
 179 +
 180 +       rw_lock_s_unlock(&btr_search_latch);
 181 +
 182 +       if (UNIV_LIKELY_NULL(heap)) {
 183 +               mem_heap_free(heap);
 184 +       }
 185 +}
 186 +
 187  /********************************************************************//**
 188  Drops a page hash index when a page is freed from a fseg to the file system.
 189  Drops possible hash index if the page happens to be in the buffer pool. */
 190 --- a/storage/innobase/buf/buf0buf.c
 191 +++ b/storage/innobase/buf/buf0buf.c
 192 @@ -294,14 +294,14 @@
 193  # endif /* !PFS_SKIP_BUFFER_MUTEX_RWLOCK */
 194  #endif /* UNIV_PFS_MUTEX || UNIV_PFS_RWLOCK */
 195
 196 -/** A chunk of buffers.  The buffer pool is allocated in chunks. */
 197 -struct buf_chunk_struct{
 198 -       ulint           mem_size;       /*!< allocated size of the chunk */
 199 -       ulint           size;           /*!< size of frames[] and blocks[] */
 200 -       void*           mem;            /*!< pointer to the memory area which
 201 -                                       was allocated for the frames */
 202 -       buf_block_t*    blocks;         /*!< array of buffer control blocks */
 203 -};
 204 +/** A chunk of buffers.  The buffer pool is allocated in chunks. (moved to buf0buf.h)*/
 205 +//struct buf_chunk_struct{
 206 +//     ulint           mem_size;       /*!< allocated size of the chunk */
 207 +//     ulint           size;           /*!< size of frames[] and blocks[] */
 208 +//     void*           mem;            /*!< pointer to the memory area which
 209 +//                                     was allocated for the frames */
 210 +//     buf_block_t*    blocks;         /*!< array of buffer control blocks */
 211 +//};
 212  #endif /* !UNIV_HOTBACKUP */
 213
 214  /********************************************************************//**
 215 --- a/storage/innobase/dict/dict0boot.c
 216 +++ b/storage/innobase/dict/dict0boot.c
 217 @@ -284,6 +284,7 @@
 218         system tables */
 219         /*-------------------------*/
 220         table = dict_mem_table_create("SYS_TABLES", DICT_HDR_SPACE, 8, 0);
 221 +       table->n_mysql_handles_opened = 1; /* for pin */
 222
 223         dict_mem_table_add_col(table, heap, "NAME", DATA_BINARY, 0, 0);
 224         dict_mem_table_add_col(table, heap, "ID", DATA_BINARY, 0, 0);
 225 @@ -336,6 +337,7 @@
 226
 227         /*-------------------------*/
 228         table = dict_mem_table_create("SYS_COLUMNS", DICT_HDR_SPACE, 7, 0);
 229 +       table->n_mysql_handles_opened = 1; /* for pin */
 230
 231         dict_mem_table_add_col(table, heap, "TABLE_ID", DATA_BINARY, 0, 0);
 232         dict_mem_table_add_col(table, heap, "POS", DATA_INT, 0, 4);
 233 @@ -368,6 +370,7 @@
 234
 235         /*-------------------------*/
 236         table = dict_mem_table_create("SYS_INDEXES", DICT_HDR_SPACE, 7, 0);
 237 +       table->n_mysql_handles_opened = 1; /* for pin */
 238
 239         dict_mem_table_add_col(table, heap, "TABLE_ID", DATA_BINARY, 0, 0);
 240         dict_mem_table_add_col(table, heap, "ID", DATA_BINARY, 0, 0);
 241 @@ -413,6 +416,7 @@
 242
 243         /*-------------------------*/
 244         table = dict_mem_table_create("SYS_FIELDS", DICT_HDR_SPACE, 3, 0);
 245 +       table->n_mysql_handles_opened = 1; /* for pin */
 246
 247         dict_mem_table_add_col(table, heap, "INDEX_ID", DATA_BINARY, 0, 0);
 248         dict_mem_table_add_col(table, heap, "POS", DATA_INT, 0, 4);
 249 --- a/storage/innobase/dict/dict0crea.c
 250 +++ b/storage/innobase/dict/dict0crea.c
 251 @@ -1209,6 +1209,9 @@
 252                 /* Foreign constraint system tables have already been
 253                 created, and they are ok */
 254
 255 +               table1->n_mysql_handles_opened = 1; /* for pin */
 256 +               table2->n_mysql_handles_opened = 1; /* for pin */
 257 +
 258                 mutex_exit(&(dict_sys->mutex));
 259
 260                 return(DB_SUCCESS);
 261 @@ -1290,6 +1293,11 @@
 262
 263         trx_commit_for_mysql(trx);
 264
 265 +       table1 = dict_table_get_low("SYS_FOREIGN");
 266 +       table2 = dict_table_get_low("SYS_FOREIGN_COLS");
 267 +       table1->n_mysql_handles_opened = 1; /* for pin */
 268 +       table2->n_mysql_handles_opened = 1; /* for pin */
 269 +
 270         row_mysql_unlock_data_dictionary(trx);
 271
 272         trx_free_for_mysql(trx);
 273 --- a/storage/innobase/dict/dict0dict.c
 274 +++ b/storage/innobase/dict/dict0dict.c
 275 @@ -626,6 +626,8 @@
 276
 277         table = dict_table_get_on_id_low(table_id);
 278
 279 +       dict_table_LRU_trim(table);
 280 +
 281         mutex_exit(&(dict_sys->mutex));
 282
 283         return(table);
 284 @@ -744,6 +746,8 @@
 285                 table->n_mysql_handles_opened++;
 286         }
 287
 288 +       dict_table_LRU_trim(table);
 289 +
 290         mutex_exit(&(dict_sys->mutex));
 291
 292         if (table != NULL) {
 293 @@ -1259,6 +1263,64 @@
 294         dict_mem_table_free(table);
 295  }
 296
 297 +/**************************************************************************
 298 +Frees tables from the end of table_LRU if the dictionary cache occupies
 299 +too much space. */
 300 +UNIV_INTERN
 301 +void
 302 +dict_table_LRU_trim(
 303 +/*================*/
 304 +       dict_table_t*   self)
 305 +{
 306 +       dict_table_t*   table;
 307 +       dict_table_t*   prev_table;
 308 +       dict_foreign_t* foreign;
 309 +       ulint           n_removed;
 310 +       ulint           n_have_parent;
 311 +       ulint           cached_foreign_tables;
 312 +
 313 +#ifdef UNIV_SYNC_DEBUG
 314 +       ut_ad(mutex_own(&(dict_sys->mutex)));
 315 +#endif /* UNIV_SYNC_DEBUG */
 316 +
 317 +retry:
 318 +       n_removed = n_have_parent = 0;
 319 +       table = UT_LIST_GET_LAST(dict_sys->table_LRU);
 320 +
 321 +       while ( srv_dict_size_limit && table
 322 +               && ((dict_sys->table_hash->n_cells
 323 +                    + dict_sys->table_id_hash->n_cells) * sizeof(hash_cell_t)
 324 +                   + dict_sys->size) > srv_dict_size_limit ) {
 325 +               prev_table = UT_LIST_GET_PREV(table_LRU, table);
 326 +
 327 +               if (table == self || table->n_mysql_handles_opened)
 328 +                       goto next_loop;
 329 +
 330 +               cached_foreign_tables = 0;
 331 +               foreign = UT_LIST_GET_FIRST(table->foreign_list);
 332 +               while (foreign != NULL) {
 333 +                       if (foreign->referenced_table)
 334 +                               cached_foreign_tables++;
 335 +                       foreign = UT_LIST_GET_NEXT(foreign_list, foreign);
 336 +               }
 337 +
 338 +               if (cached_foreign_tables == 0) {
 339 +                       dict_table_remove_from_cache(table);
 340 +                       n_removed++;
 341 +               } else {
 342 +                       n_have_parent++;
 343 +               }
 344 +next_loop:
 345 +               table = prev_table;
 346 +       }
 347 +
 348 +       if ( srv_dict_size_limit && n_have_parent && n_removed
 349 +               && ((dict_sys->table_hash->n_cells
 350 +                    + dict_sys->table_id_hash->n_cells) * sizeof(hash_cell_t)
 351 +                   + dict_sys->size) > srv_dict_size_limit )
 352 +               goto retry;
 353 +}
 354 +
 355  /****************************************************************//**
 356  If the given column name is reserved for InnoDB system columns, return
 357  TRUE.
 358 @@ -1762,6 +1824,11 @@
 359         ut_ad(index->magic_n == DICT_INDEX_MAGIC_N);
 360         ut_ad(mutex_own(&(dict_sys->mutex)));
 361
 362 +       /* remove all entry of the index from adaptive hash index,
 363 +       because removing from adaptive hash index needs dict_index */
 364 +       if (btr_search_enabled && srv_dict_size_limit)
 365 +               btr_search_drop_page_hash_index_on_index(index);
 366 +
 367         /* We always create search info whether or not adaptive
 368         hash index is enabled or not. */
 369         info = index->search_info;
 370 --- a/storage/innobase/handler/ha_innodb.cc
 371 +++ b/storage/innobase/handler/ha_innodb.cc
 372 @@ -675,6 +675,8 @@
 373    (char*) &export_vars.innodb_dblwr_pages_written,       SHOW_LONG},
 374    {"dblwr_writes",
 375    (char*) &export_vars.innodb_dblwr_writes,              SHOW_LONG},
 376 +  {"dict_tables",
 377 +  (char*) &export_vars.innodb_dict_tables,               SHOW_LONG},
 378    {"have_atomic_builtins",
 379    (char*) &export_vars.innodb_have_atomic_builtins,      SHOW_BOOL},
 380    {"log_waits",
 381 @@ -11658,6 +11660,11 @@
 382    "Choose method of innodb_adaptive_flushing. (native, [estimate], keep_average)",
 383    NULL, innodb_adaptive_flushing_method_update, 1, &adaptive_flushing_method_typelib);
 384
 385 +static MYSQL_SYSVAR_ULONG(dict_size_limit, srv_dict_size_limit,
 386 +  PLUGIN_VAR_RQCMDARG,
 387 +  "Limit the allocated memory for dictionary cache. (0: unlimited)",
 388 +  NULL, NULL, 0, 0, LONG_MAX, 0);
 389 +
 390  static struct st_mysql_sys_var* innobase_system_variables[]= {
 391    MYSQL_SYSVAR(additional_mem_pool_size),
 392    MYSQL_SYSVAR(autoextend_increment),
 393 @@ -11726,6 +11733,7 @@
 394    MYSQL_SYSVAR(flush_neighbor_pages),
 395    MYSQL_SYSVAR(read_ahead),
 396    MYSQL_SYSVAR(adaptive_flushing_method),
 397 +  MYSQL_SYSVAR(dict_size_limit),
 398    MYSQL_SYSVAR(use_sys_malloc),
 399    MYSQL_SYSVAR(use_native_aio),
 400    MYSQL_SYSVAR(change_buffering),
 401 --- a/storage/innobase/ibuf/ibuf0ibuf.c
 402 +++ b/storage/innobase/ibuf/ibuf0ibuf.c
 403 @@ -566,6 +566,7 @@
 404
 405         /* Use old-style record format for the insert buffer. */
 406         table = dict_mem_table_create(IBUF_TABLE_NAME, IBUF_SPACE_ID, 1, 0);
 407 +       table->n_mysql_handles_opened = 1; /* for pin */
 408
 409         dict_mem_table_add_col(table, heap, "DUMMY_COLUMN", DATA_BINARY, 0, 0);
 410
 411 --- a/storage/innobase/include/btr0sea.h
 412 +++ b/storage/innobase/include/btr0sea.h
 413 @@ -140,6 +140,13 @@
 414                                 s- or x-latched, or an index page
 415                                 for which we know that
 416                                 block->buf_fix_count == 0 */
 417 +/************************************************************************
 418 +Drops a page hash index based on index */
 419 +UNIV_INTERN
 420 +void
 421 +btr_search_drop_page_hash_index_on_index(
 422 +/*=====================================*/
 423 +       dict_index_t*   index);         /* in: record descriptor */
 424  /********************************************************************//**
 425  Drops a page hash index when a page is freed from a fseg to the file system.
 426  Drops possible hash index if the page happens to be in the buffer pool. */
 427 --- a/storage/innobase/include/buf0buf.h
 428 +++ b/storage/innobase/include/buf0buf.h
 429 @@ -1594,6 +1594,15 @@
 430  #define BUF_POOL_ZIP_FOLD_BPAGE(b) BUF_POOL_ZIP_FOLD((buf_block_t*) (b))
 431  /* @} */
 432
 433 +/** A chunk of buffers.  The buffer pool is allocated in chunks. */
 434 +struct buf_chunk_struct{
 435 +       ulint           mem_size;       /*!< allocated size of the chunk */
 436 +       ulint           size;           /*!< size of frames[] and blocks[] */
 437 +       void*           mem;            /*!< pointer to the memory area which
 438 +                                       was allocated for the frames */
 439 +       buf_block_t*    blocks;         /*!< array of buffer control blocks */
 440 +};
 441 +
 442  /** @brief The buffer pool statistics structure. */
 443  struct buf_pool_stat_struct{
 444         ulint   n_page_gets;    /*!< number of page gets performed;
 445 --- a/storage/innobase/include/dict0dict.h
 446 +++ b/storage/innobase/include/dict0dict.h
 447 @@ -1183,6 +1183,12 @@
 448  /*====================================*/
 449         dict_table_t*   table,  /*!< in: table */
 450         const char*     name);  /*!< in: name of the index to find */
 451 +
 452 +UNIV_INTERN
 453 +void
 454 +dict_table_LRU_trim(
 455 +/*================*/
 456 +       dict_table_t*   self);
 457  /* Buffers for storing detailed information about the latest foreign key
 458  and unique key errors */
 459  extern FILE*   dict_foreign_err_file;
 460 --- a/storage/innobase/include/dict0dict.ic
 461 +++ b/storage/innobase/include/dict0dict.ic
 462 @@ -824,6 +824,13 @@
 463         HASH_SEARCH(name_hash, dict_sys->table_hash, table_fold,
 464                     dict_table_t*, table, ut_ad(table->cached),
 465                     !strcmp(table->name, table_name));
 466 +
 467 +       /* make young in table_LRU */
 468 +       if (table) {
 469 +               UT_LIST_REMOVE(table_LRU, dict_sys->table_LRU, table);
 470 +               UT_LIST_ADD_FIRST(table_LRU, dict_sys->table_LRU, table);
 471 +       }
 472 +
 473         return(table);
 474  }
 475
 476 @@ -905,6 +912,12 @@
 477                 table = dict_load_table_on_id(table_id);
 478         }
 479
 480 +       /* make young in table_LRU */
 481 +       if (table) {
 482 +               UT_LIST_REMOVE(table_LRU, dict_sys->table_LRU, table);
 483 +               UT_LIST_ADD_FIRST(table_LRU, dict_sys->table_LRU, table);
 484 +       }
 485 +
 486         ut_ad(!table || table->cached);
 487
 488         /* TODO: should get the type information from MySQL */
 489 --- a/storage/innobase/include/srv0srv.h
 490 +++ b/storage/innobase/include/srv0srv.h
 491 @@ -229,6 +229,7 @@
 492  extern ulint   srv_read_ahead;
 493  extern ulint   srv_adaptive_flushing_method;
 494
 495 +extern ulint   srv_dict_size_limit;
 496  /*-------------------------------------------*/
 497
 498  extern ulint   srv_n_rows_inserted;
 499 @@ -709,6 +710,7 @@
 500         ulint innodb_data_writes;               /*!< I/O write requests */
 501         ulint innodb_data_written;              /*!< Data bytes written */
 502         ulint innodb_data_reads;                /*!< I/O read requests */
 503 +       ulint innodb_dict_tables;
 504         ulint innodb_buffer_pool_pages_total;   /*!< Buffer pool size */
 505         ulint innodb_buffer_pool_pages_data;    /*!< Data pages */
 506         ulint innodb_buffer_pool_pages_dirty;   /*!< Dirty data pages */
 507 --- a/storage/innobase/srv/srv0srv.c
 508 +++ b/storage/innobase/srv/srv0srv.c
 509 @@ -415,6 +415,8 @@
 510  UNIV_INTERN ulint      srv_enable_unsafe_group_commit = 0; /* 0:disable 1:enable */
 511  UNIV_INTERN ulint      srv_read_ahead = 3; /* 1: random  2: linear  3: Both */
 512  UNIV_INTERN ulint      srv_adaptive_flushing_method = 0; /* 0: native  1: estimate  2: keep_average */
 513 +
 514 +UNIV_INTERN ulint      srv_dict_size_limit = 0;
 515  /*-------------------------------------------*/
 516  UNIV_INTERN ulong      srv_n_spin_wait_rounds  = 30;
 517  UNIV_INTERN ulong      srv_n_free_tickets_to_enter = 500;
 518 @@ -2220,6 +2222,7 @@
 519         export_vars.innodb_data_reads = os_n_file_reads;
 520         export_vars.innodb_data_writes = os_n_file_writes;
 521         export_vars.innodb_data_written = srv_data_written;
 522 +       export_vars.innodb_dict_tables= (dict_sys ? UT_LIST_GET_LEN(dict_sys->table_LRU) : 0);
 523         export_vars.innodb_buffer_pool_read_requests = stat.n_page_gets;
 524         export_vars.innodb_buffer_pool_write_requests
 525                 = srv_buf_pool_write_requests;