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 diff -ruN a/storage/innobase/btr/btr0sea.c b/storage/innobase/btr/btr0sea.c
   9 --- a/storage/innobase/btr/btr0sea.c    2010-11-03 07:01:13.000000000 +0900
  10 +++ b/storage/innobase/btr/btr0sea.c    2010-12-03 15:45:47.503988924 +0900
  11 @@ -1185,6 +1185,132 @@
  12         mem_free(folds);
  13  }
  14
  15 +/************************************************************************
  16 +Drops a page hash index based on index */
  17 +UNIV_INTERN
  18 +void
  19 +btr_search_drop_page_hash_index_on_index(
  20 +/*=====================================*/
  21 +       dict_index_t*   index)          /* in: record descriptor */
  22 +{
  23 +       buf_page_t*     bpage;
  24 +       hash_table_t*   table;
  25 +       buf_block_t*    block;
  26 +       ulint           n_fields;
  27 +       ulint           n_bytes;
  28 +       const page_t*           page;
  29 +       const rec_t*            rec;
  30 +       ulint           fold;
  31 +       ulint           prev_fold;
  32 +       index_id_t      index_id;
  33 +       ulint           n_cached;
  34 +       ulint           n_recs;
  35 +       ulint*          folds;
  36 +       ulint           i, j;
  37 +       mem_heap_t*     heap    = NULL;
  38 +       ulint*          offsets;
  39 +
  40 +       rw_lock_x_lock(&btr_search_latch);
  41 +       buf_pool_mutex_enter_all();
  42 +
  43 +       table = btr_search_sys->hash_index;
  44 +
  45 +       for (j = 0; j < srv_buf_pool_instances; j++) {
  46 +               buf_pool_t*     buf_pool;
  47 +
  48 +               buf_pool = buf_pool_from_array(j);
  49 +
  50 +               bpage = UT_LIST_GET_LAST(buf_pool->LRU);
  51 +
  52 +               while (bpage != NULL) {
  53 +                       block = (buf_block_t*) bpage;
  54 +                       if (block->index == index && block->is_hashed) {
  55 +                               page = block->frame;
  56 +
  57 +                               /* from btr_search_drop_page_hash_index() */
  58 +                               n_fields = block->curr_n_fields;
  59 +                               n_bytes = block->curr_n_bytes;
  60 +
  61 +                               ut_a(n_fields + n_bytes > 0);
  62 +
  63 +                               n_recs = page_get_n_recs(page);
  64 +
  65 +                               /* Calculate and cache fold values into an array for fast deletion
  66 +                               from the hash index */
  67 +
  68 +                               folds = mem_alloc(n_recs * sizeof(ulint));
  69 +
  70 +                               n_cached = 0;
  71 +
  72 +                               rec = page_get_infimum_rec(page);
  73 +                               rec = page_rec_get_next_low(rec, page_is_comp(page));
  74 +
  75 +                               index_id = btr_page_get_index_id(page);
  76 +
  77 +                               ut_a(index_id == index->id);
  78 +
  79 +                               prev_fold = 0;
  80 +
  81 +                               offsets = NULL;
  82 +
  83 +                               while (!page_rec_is_supremum(rec)) {
  84 +                                       offsets = rec_get_offsets(rec, index, offsets,
  85 +                                                               n_fields + (n_bytes > 0), &heap);
  86 +                                       ut_a(rec_offs_n_fields(offsets) == n_fields + (n_bytes > 0));
  87 +                                       fold = rec_fold(rec, offsets, n_fields, n_bytes, index_id);
  88 +
  89 +                                       if (fold == prev_fold && prev_fold != 0) {
  90 +
  91 +                                               goto next_rec;
  92 +                                       }
  93 +
  94 +                                       /* Remove all hash nodes pointing to this page from the
  95 +                                       hash chain */
  96 +
  97 +                                       folds[n_cached] = fold;
  98 +                                       n_cached++;
  99 +next_rec:
 100 +                                       rec = page_rec_get_next_low(rec, page_rec_is_comp(rec));
 101 +                                       prev_fold = fold;
 102 +                               }
 103 +
 104 +                               for (i = 0; i < n_cached; i++) {
 105 +
 106 +                                       ha_remove_all_nodes_to_page(table, folds[i], page);
 107 +                               }
 108 +
 109 +                               ut_a(index->search_info->ref_count > 0);
 110 +                               index->search_info->ref_count--;
 111 +
 112 +                               block->is_hashed = FALSE;
 113 +                               block->index = NULL;
 114 +
 115 +#if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG
 116 +                               if (UNIV_UNLIKELY(block->n_pointers)) {
 117 +                                       /* Corruption */
 118 +                                       ut_print_timestamp(stderr);
 119 +                                       fprintf(stderr,
 120 +"  InnoDB: Corruption of adaptive hash index. After dropping\n"
 121 +"InnoDB: the hash index to a page of %s, still %lu hash nodes remain.\n",
 122 +                                               index->name, (ulong) block->n_pointers);
 123 +                               }
 124 +#endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */
 125 +
 126 +                               mem_free(folds);
 127 +                       }
 128 +
 129 +                       bpage = UT_LIST_GET_PREV(LRU, bpage);
 130 +               }
 131 +       }
 132 +
 133 +       buf_pool_mutex_exit_all();
 134 +       rw_lock_x_unlock(&btr_search_latch);
 135 +
 136 +       if (UNIV_LIKELY_NULL(heap)) {
 137 +               mem_heap_free(heap);
 138 +       }
 139 +}
 140 +
 141  /********************************************************************//**
 142  Drops a page hash index when a page is freed from a fseg to the file system.
 143  Drops possible hash index if the page happens to be in the buffer pool. */
 144 diff -ruN a/storage/innobase/dict/dict0boot.c b/storage/innobase/dict/dict0boot.c
 145 --- a/storage/innobase/dict/dict0boot.c 2010-11-03 07:01:13.000000000 +0900
 146 +++ b/storage/innobase/dict/dict0boot.c 2010-12-03 15:45:47.503988924 +0900
 147 @@ -284,6 +284,7 @@
 148         system tables */
 149         /*-------------------------*/
 150         table = dict_mem_table_create("SYS_TABLES", DICT_HDR_SPACE, 8, 0);
 151 +       table->n_mysql_handles_opened = 1; /* for pin */
 152
 153         dict_mem_table_add_col(table, heap, "NAME", DATA_BINARY, 0, 0);
 154         dict_mem_table_add_col(table, heap, "ID", DATA_BINARY, 0, 0);
 155 @@ -336,6 +337,7 @@
 156
 157         /*-------------------------*/
 158         table = dict_mem_table_create("SYS_COLUMNS", DICT_HDR_SPACE, 7, 0);
 159 +       table->n_mysql_handles_opened = 1; /* for pin */
 160
 161         dict_mem_table_add_col(table, heap, "TABLE_ID", DATA_BINARY, 0, 0);
 162         dict_mem_table_add_col(table, heap, "POS", DATA_INT, 0, 4);
 163 @@ -368,6 +370,7 @@
 164
 165         /*-------------------------*/
 166         table = dict_mem_table_create("SYS_INDEXES", DICT_HDR_SPACE, 7, 0);
 167 +       table->n_mysql_handles_opened = 1; /* for pin */
 168
 169         dict_mem_table_add_col(table, heap, "TABLE_ID", DATA_BINARY, 0, 0);
 170         dict_mem_table_add_col(table, heap, "ID", DATA_BINARY, 0, 0);
 171 @@ -413,6 +416,7 @@
 172
 173         /*-------------------------*/
 174         table = dict_mem_table_create("SYS_FIELDS", DICT_HDR_SPACE, 3, 0);
 175 +       table->n_mysql_handles_opened = 1; /* for pin */
 176
 177         dict_mem_table_add_col(table, heap, "INDEX_ID", DATA_BINARY, 0, 0);
 178         dict_mem_table_add_col(table, heap, "POS", DATA_INT, 0, 4);
 179 diff -ruN a/storage/innobase/dict/dict0crea.c b/storage/innobase/dict/dict0crea.c
 180 --- a/storage/innobase/dict/dict0crea.c 2010-11-03 07:01:13.000000000 +0900
 181 +++ b/storage/innobase/dict/dict0crea.c 2010-12-03 15:45:47.521955810 +0900
 182 @@ -1210,6 +1210,9 @@
 183                 /* Foreign constraint system tables have already been
 184                 created, and they are ok */
 185
 186 +               table1->n_mysql_handles_opened = 1; /* for pin */
 187 +               table2->n_mysql_handles_opened = 1; /* for pin */
 188 +
 189                 mutex_exit(&(dict_sys->mutex));
 190
 191                 return(DB_SUCCESS);
 192 @@ -1291,6 +1294,11 @@
 193
 194         trx_commit_for_mysql(trx);
 195
 196 +       table1 = dict_table_get_low("SYS_FOREIGN");
 197 +       table2 = dict_table_get_low("SYS_FOREIGN_COLS");
 198 +       table1->n_mysql_handles_opened = 1; /* for pin */
 199 +       table2->n_mysql_handles_opened = 1; /* for pin */
 200 +
 201         row_mysql_unlock_data_dictionary(trx);
 202
 203         trx_free_for_mysql(trx);
 204 diff -ruN a/storage/innobase/dict/dict0dict.c b/storage/innobase/dict/dict0dict.c
 205 --- a/storage/innobase/dict/dict0dict.c 2010-11-03 07:01:13.000000000 +0900
 206 +++ b/storage/innobase/dict/dict0dict.c 2010-12-03 15:45:47.525953769 +0900
 207 @@ -625,6 +625,8 @@
 208
 209         table = dict_table_get_on_id_low(table_id);
 210
 211 +       dict_table_LRU_trim(table);
 212 +
 213         mutex_exit(&(dict_sys->mutex));
 214
 215         return(table);
 216 @@ -743,6 +745,8 @@
 217                 table->n_mysql_handles_opened++;
 218         }
 219
 220 +       dict_table_LRU_trim(table);
 221 +
 222         mutex_exit(&(dict_sys->mutex));
 223
 224         if (table != NULL) {
 225 @@ -1256,6 +1260,64 @@
 226         dict_mem_table_free(table);
 227  }
 228
 229 +/**************************************************************************
 230 +Frees tables from the end of table_LRU if the dictionary cache occupies
 231 +too much space. */
 232 +UNIV_INTERN
 233 +void
 234 +dict_table_LRU_trim(
 235 +/*================*/
 236 +       dict_table_t*   self)
 237 +{
 238 +       dict_table_t*   table;
 239 +       dict_table_t*   prev_table;
 240 +       dict_foreign_t* foreign;
 241 +       ulint           n_removed;
 242 +       ulint           n_have_parent;
 243 +       ulint           cached_foreign_tables;
 244 +
 245 +#ifdef UNIV_SYNC_DEBUG
 246 +       ut_ad(mutex_own(&(dict_sys->mutex)));
 247 +#endif /* UNIV_SYNC_DEBUG */
 248 +
 249 +retry:
 250 +       n_removed = n_have_parent = 0;
 251 +       table = UT_LIST_GET_LAST(dict_sys->table_LRU);
 252 +
 253 +       while ( srv_dict_size_limit && table
 254 +               && ((dict_sys->table_hash->n_cells
 255 +                    + dict_sys->table_id_hash->n_cells) * sizeof(hash_cell_t)
 256 +                   + dict_sys->size) > srv_dict_size_limit ) {
 257 +               prev_table = UT_LIST_GET_PREV(table_LRU, table);
 258 +
 259 +               if (table == self || table->n_mysql_handles_opened)
 260 +                       goto next_loop;
 261 +
 262 +               cached_foreign_tables = 0;
 263 +               foreign = UT_LIST_GET_FIRST(table->foreign_list);
 264 +               while (foreign != NULL) {
 265 +                       if (foreign->referenced_table)
 266 +                               cached_foreign_tables++;
 267 +                       foreign = UT_LIST_GET_NEXT(foreign_list, foreign);
 268 +               }
 269 +
 270 +               if (cached_foreign_tables == 0) {
 271 +                       dict_table_remove_from_cache(table);
 272 +                       n_removed++;
 273 +               } else {
 274 +                       n_have_parent++;
 275 +               }
 276 +next_loop:
 277 +               table = prev_table;
 278 +       }
 279 +
 280 +       if ( srv_dict_size_limit && n_have_parent && n_removed
 281 +               && ((dict_sys->table_hash->n_cells
 282 +                    + dict_sys->table_id_hash->n_cells) * sizeof(hash_cell_t)
 283 +                   + dict_sys->size) > srv_dict_size_limit )
 284 +               goto retry;
 285 +}
 286 +
 287  /****************************************************************//**
 288  If the given column name is reserved for InnoDB system columns, return
 289  TRUE.
 290 @@ -1719,6 +1781,11 @@
 291         ut_ad(index->magic_n == DICT_INDEX_MAGIC_N);
 292         ut_ad(mutex_own(&(dict_sys->mutex)));
 293
 294 +       /* remove all entry of the index from adaptive hash index,
 295 +       because removing from adaptive hash index needs dict_index */
 296 +       if (btr_search_enabled && srv_dict_size_limit)
 297 +               btr_search_drop_page_hash_index_on_index(index);
 298 +
 299         /* We always create search info whether or not adaptive
 300         hash index is enabled or not. */
 301         info = index->search_info;
 302 diff -ruN a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
 303 --- a/storage/innobase/handler/ha_innodb.cc     2010-12-03 15:43:57.294986852 +0900
 304 +++ b/storage/innobase/handler/ha_innodb.cc     2010-12-03 15:45:47.534959966 +0900
 305 @@ -655,6 +655,8 @@
 306    (char*) &export_vars.innodb_dblwr_pages_written,       SHOW_LONG},
 307    {"dblwr_writes",
 308    (char*) &export_vars.innodb_dblwr_writes,              SHOW_LONG},
 309 +  {"dict_tables",
 310 +  (char*) &export_vars.innodb_dict_tables,               SHOW_LONG},
 311    {"have_atomic_builtins",
 312    (char*) &export_vars.innodb_have_atomic_builtins,      SHOW_BOOL},
 313    {"log_waits",
 314 @@ -11543,6 +11545,11 @@
 315    "Number of extra user rollback segments which are used in a round-robin fashion.",
 316    NULL, NULL, 127, 0, 127, 0);
 317
 318 +static MYSQL_SYSVAR_ULONG(dict_size_limit, srv_dict_size_limit,
 319 +  PLUGIN_VAR_RQCMDARG,
 320 +  "Limit the allocated memory for dictionary cache. (0: unlimited)",
 321 +  NULL, NULL, 0, 0, LONG_MAX, 0);
 322 +
 323  static struct st_mysql_sys_var* innobase_system_variables[]= {
 324    MYSQL_SYSVAR(additional_mem_pool_size),
 325    MYSQL_SYSVAR(autoextend_increment),
 326 @@ -11611,6 +11618,7 @@
 327    MYSQL_SYSVAR(adaptive_flushing_method),
 328    MYSQL_SYSVAR(enable_unsafe_group_commit),
 329    MYSQL_SYSVAR(extra_rsegments),
 330 +  MYSQL_SYSVAR(dict_size_limit),
 331    MYSQL_SYSVAR(use_sys_malloc),
 332    MYSQL_SYSVAR(use_native_aio),
 333    MYSQL_SYSVAR(change_buffering),
 334 diff -ruN a/storage/innobase/ibuf/ibuf0ibuf.c b/storage/innobase/ibuf/ibuf0ibuf.c
 335 --- a/storage/innobase/ibuf/ibuf0ibuf.c 2010-12-03 15:18:48.889024455 +0900
 336 +++ b/storage/innobase/ibuf/ibuf0ibuf.c 2010-12-03 15:45:47.553025057 +0900
 337 @@ -578,6 +578,7 @@
 338
 339         /* Use old-style record format for the insert buffer. */
 340         table = dict_mem_table_create(IBUF_TABLE_NAME, IBUF_SPACE_ID, 1, 0);
 341 +       table->n_mysql_handles_opened = 1; /* for pin */
 342
 343         dict_mem_table_add_col(table, heap, "DUMMY_COLUMN", DATA_BINARY, 0, 0);
 344
 345 diff -ruN a/storage/innobase/include/btr0sea.h b/storage/innobase/include/btr0sea.h
 346 --- a/storage/innobase/include/btr0sea.h        2010-11-03 07:01:13.000000000 +0900
 347 +++ b/storage/innobase/include/btr0sea.h        2010-12-03 15:45:47.555024229 +0900
 348 @@ -140,6 +140,13 @@
 349                                 s- or x-latched, or an index page
 350                                 for which we know that
 351                                 block->buf_fix_count == 0 */
 352 +/************************************************************************
 353 +Drops a page hash index based on index */
 354 +UNIV_INTERN
 355 +void
 356 +btr_search_drop_page_hash_index_on_index(
 357 +/*=====================================*/
 358 +       dict_index_t*   index);         /* in: record descriptor */
 359  /********************************************************************//**
 360  Drops a page hash index when a page is freed from a fseg to the file system.
 361  Drops possible hash index if the page happens to be in the buffer pool. */
 362 diff -ruN a/storage/innobase/include/dict0dict.h b/storage/innobase/include/dict0dict.h
 363 --- a/storage/innobase/include/dict0dict.h      2010-11-03 07:01:13.000000000 +0900
 364 +++ b/storage/innobase/include/dict0dict.h      2010-12-03 15:45:47.558024515 +0900
 365 @@ -1158,6 +1158,12 @@
 366  /*====================================*/
 367         dict_table_t*   table,  /*!< in: table */
 368         const char*     name);  /*!< in: name of the index to find */
 369 +
 370 +UNIV_INTERN
 371 +void
 372 +dict_table_LRU_trim(
 373 +/*================*/
 374 +       dict_table_t*   self);
 375  /* Buffers for storing detailed information about the latest foreign key
 376  and unique key errors */
 377  extern FILE*   dict_foreign_err_file;
 378 diff -ruN a/storage/innobase/include/dict0dict.ic b/storage/innobase/include/dict0dict.ic
 379 --- a/storage/innobase/include/dict0dict.ic     2010-11-03 07:01:13.000000000 +0900
 380 +++ b/storage/innobase/include/dict0dict.ic     2010-12-03 15:45:47.560024398 +0900
 381 @@ -824,6 +824,13 @@
 382         HASH_SEARCH(name_hash, dict_sys->table_hash, table_fold,
 383                     dict_table_t*, table, ut_ad(table->cached),
 384                     !strcmp(table->name, table_name));
 385 +
 386 +       /* make young in table_LRU */
 387 +       if (table) {
 388 +               UT_LIST_REMOVE(table_LRU, dict_sys->table_LRU, table);
 389 +               UT_LIST_ADD_FIRST(table_LRU, dict_sys->table_LRU, table);
 390 +       }
 391 +
 392         return(table);
 393  }
 394
 395 @@ -877,6 +884,12 @@
 396                 table = dict_load_table_on_id(table_id);
 397         }
 398
 399 +       /* make young in table_LRU */
 400 +       if (table) {
 401 +               UT_LIST_REMOVE(table_LRU, dict_sys->table_LRU, table);
 402 +               UT_LIST_ADD_FIRST(table_LRU, dict_sys->table_LRU, table);
 403 +       }
 404 +
 405         ut_ad(!table || table->cached);
 406
 407         /* TODO: should get the type information from MySQL */
 408 diff -ruN a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
 409 --- a/storage/innobase/include/srv0srv.h        2010-12-03 15:43:57.297067100 +0900
 410 +++ b/storage/innobase/include/srv0srv.h        2010-12-03 15:45:47.562024404 +0900
 411 @@ -228,7 +228,7 @@
 412  extern ulint   srv_adaptive_flushing_method;
 413
 414  extern ulint   srv_extra_rsegments;
 415 -
 416 +extern ulint   srv_dict_size_limit;
 417  /*-------------------------------------------*/
 418
 419  extern ulint   srv_n_rows_inserted;
 420 @@ -700,6 +700,7 @@
 421         ulint innodb_data_writes;               /*!< I/O write requests */
 422         ulint innodb_data_written;              /*!< Data bytes written */
 423         ulint innodb_data_reads;                /*!< I/O read requests */
 424 +       ulint innodb_dict_tables;
 425         ulint innodb_buffer_pool_pages_total;   /*!< Buffer pool size */
 426         ulint innodb_buffer_pool_pages_data;    /*!< Data pages */
 427         ulint innodb_buffer_pool_pages_dirty;   /*!< Dirty data pages */
 428 diff -ruN a/storage/innobase/srv/srv0srv.c b/storage/innobase/srv/srv0srv.c
 429 --- a/storage/innobase/srv/srv0srv.c    2010-12-03 15:43:57.301024390 +0900
 430 +++ b/storage/innobase/srv/srv0srv.c    2010-12-03 15:45:47.565023830 +0900
 431 @@ -414,6 +414,7 @@
 432  UNIV_INTERN ulint      srv_adaptive_flushing_method = 0; /* 0: native  1: estimate  2: keep_average */
 433
 434  UNIV_INTERN ulint      srv_extra_rsegments = 127; /* extra rseg for users */
 435 +UNIV_INTERN ulint      srv_dict_size_limit = 0;
 436  /*-------------------------------------------*/
 437  UNIV_INTERN ulong      srv_n_spin_wait_rounds  = 30;
 438  UNIV_INTERN ulong      srv_n_free_tickets_to_enter = 500;
 439 @@ -2192,6 +2193,7 @@
 440         export_vars.innodb_data_reads = os_n_file_reads;
 441         export_vars.innodb_data_writes = os_n_file_writes;
 442         export_vars.innodb_data_written = srv_data_written;
 443 +       export_vars.innodb_dict_tables= (dict_sys ? UT_LIST_GET_LEN(dict_sys->table_LRU) : 0);
 444         export_vars.innodb_buffer_pool_read_requests = stat.n_page_gets;
 445         export_vars.innodb_buffer_pool_write_requests
 446                 = srv_buf_pool_write_requests;