1 # name : innodb_stats.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/dict/dict0boot.c
9 +++ b/storage/innobase/dict/dict0boot.c
11 /* Get the dictionary header */
12 dict_hdr = dict_hdr_get(&mtr);
14 + if (mach_read_from_8(dict_hdr + DICT_HDR_XTRADB_MARK)
15 + != DICT_HDR_XTRADB_FLAG) {
16 + /* not extended yet by XtraDB, need to be extended */
19 + root_page_no = btr_create(DICT_CLUSTERED | DICT_UNIQUE,
20 + DICT_HDR_SPACE, 0, DICT_STATS_ID,
21 + dict_ind_redundant, &mtr);
22 + if (root_page_no == FIL_NULL) {
23 + fprintf(stderr, "InnoDB: Warning: failed to create SYS_STATS btr.\n");
24 + srv_use_sys_stats_table = FALSE;
26 + mlog_write_ulint(dict_hdr + DICT_HDR_STATS, root_page_no,
28 + mlog_write_ull(dict_hdr + DICT_HDR_XTRADB_MARK,
29 + DICT_HDR_XTRADB_FLAG, &mtr);
34 + dict_hdr = dict_hdr_get(&mtr);
37 /* Because we only write new row ids to disk-based data structure
38 (dictionary header) when it is divisible by
39 DICT_HDR_ROW_ID_WRITE_MARGIN, in recovery we will not recover
41 table->id = DICT_FIELDS_ID;
42 dict_table_add_to_cache(table, heap);
43 dict_sys->sys_fields = table;
44 - mem_heap_free(heap);
45 + mem_heap_empty(heap);
47 index = dict_mem_index_create("SYS_FIELDS", "CLUST_IND",
51 ut_a(error == DB_SUCCESS);
53 + /*-------------------------*/
54 + table = dict_mem_table_create("SYS_STATS", DICT_HDR_SPACE, 4, 0);
55 + table->n_mysql_handles_opened = 1; /* for pin */
57 + dict_mem_table_add_col(table, heap, "INDEX_ID", DATA_BINARY, 0, 0);
58 + dict_mem_table_add_col(table, heap, "KEY_COLS", DATA_INT, 0, 4);
59 + dict_mem_table_add_col(table, heap, "DIFF_VALS", DATA_BINARY, 0, 0);
60 + dict_mem_table_add_col(table, heap, "NON_NULL_VALS", DATA_BINARY, 0, 0);
62 + /* The '+ 2' below comes from the fields DB_TRX_ID, DB_ROLL_PTR */
63 +#if DICT_SYS_STATS_DIFF_VALS_FIELD != 2 + 2
64 +#error "DICT_SYS_STATS_DIFF_VALS_FIELD != 2 + 2"
66 +#if DICT_SYS_STATS_NON_NULL_VALS_FIELD != 3 + 2
67 +#error "DICT_SYS_STATS_NON_NULL_VALS_FIELD != 3 + 2"
70 + table->id = DICT_STATS_ID;
71 + dict_table_add_to_cache(table, heap);
72 + dict_sys->sys_stats = table;
73 + mem_heap_empty(heap);
75 + index = dict_mem_index_create("SYS_STATS", "CLUST_IND",
77 + DICT_UNIQUE | DICT_CLUSTERED, 2);
79 + dict_mem_index_add_field(index, "INDEX_ID", 0);
80 + dict_mem_index_add_field(index, "KEY_COLS", 0);
82 + index->id = DICT_STATS_ID;
83 + error = dict_index_add_to_cache(table, index,
84 + mtr_read_ulint(dict_hdr
88 + ut_a(error == DB_SUCCESS);
90 + mem_heap_free(heap);
93 /*-------------------------*/
96 dict_load_sys_table(dict_sys->sys_columns);
97 dict_load_sys_table(dict_sys->sys_indexes);
98 dict_load_sys_table(dict_sys->sys_fields);
99 + dict_load_sys_table(dict_sys->sys_stats);
101 mutex_exit(&(dict_sys->mutex));
103 --- a/storage/innobase/dict/dict0crea.c
104 +++ b/storage/innobase/dict/dict0crea.c
108 /*****************************************************************//**
109 +Based on an index object, this function builds the entry to be inserted
110 +in the SYS_STATS system table.
111 +@return the tuple which should be inserted */
114 +dict_create_sys_stats_tuple(
115 +/*========================*/
116 + const dict_index_t* index,
120 + dict_table_t* sys_stats;
128 + sys_stats = dict_sys->sys_stats;
130 + entry = dtuple_create(heap, 4 + DATA_N_SYS_COLS);
132 + dict_table_copy_types(entry, sys_stats);
134 + /* 0: INDEX_ID -----------------------*/
135 + dfield = dtuple_get_nth_field(entry, 0/*INDEX_ID*/);
136 + ptr = mem_heap_alloc(heap, 8);
137 + mach_write_to_8(ptr, index->id);
138 + dfield_set_data(dfield, ptr, 8);
139 + /* 1: KEY_COLS -----------------------*/
140 + dfield = dtuple_get_nth_field(entry, 1/*KEY_COLS*/);
141 + ptr = mem_heap_alloc(heap, 4);
142 + mach_write_to_4(ptr, i);
143 + dfield_set_data(dfield, ptr, 4);
144 + /* 4: DIFF_VALS ----------------------*/
145 + dfield = dtuple_get_nth_field(entry, 2/*DIFF_VALS*/);
146 + ptr = mem_heap_alloc(heap, 8);
147 + mach_write_to_8(ptr, 0); /* initial value is 0 */
148 + dfield_set_data(dfield, ptr, 8);
149 + /* 5: NON_NULL_VALS ------------------*/
150 + dfield = dtuple_get_nth_field(entry, 3/*NON_NULL_VALS*/);
151 + ptr = mem_heap_alloc(heap, 8);
152 + mach_write_to_8(ptr, 0); /* initial value is 0 */
153 + dfield_set_data(dfield, ptr, 8);
158 +/*****************************************************************//**
159 Creates the tuple with which the index entry is searched for writing the index
160 tree root page number, if such a tree is created.
161 @return the tuple for search */
165 /***************************************************************//**
166 +Builds a row for storing stats to insert.
167 +@return DB_SUCCESS */
170 +dict_build_stats_def_step(
171 +/*======================*/
174 + dict_index_t* index;
177 + index = node->index;
179 + row = dict_create_sys_stats_tuple(index, node->stats_no, node->heap);
181 + ins_node_set_new_row(node->stats_def, row);
183 + return(DB_SUCCESS);
186 +/***************************************************************//**
187 Creates an index tree for the index if it is not a member of a cluster.
188 @return DB_SUCCESS or DB_OUT_OF_FILE_SPACE */
190 @@ -936,6 +1007,49 @@
191 dict_sys->sys_fields, heap);
192 node->field_def->common.parent = node;
194 + if (srv_use_sys_stats_table) {
195 + node->stats_def = ins_node_create(INS_DIRECT,
196 + dict_sys->sys_stats, heap);
197 + node->stats_def->common.parent = node;
199 + node->stats_def = NULL;
202 + node->commit_node = commit_node_create(heap);
203 + node->commit_node->common.parent = node;
208 +/*********************************************************************//**
212 +ind_insert_stats_graph_create(
213 +/*==========================*/
214 + dict_index_t* index,
219 + node = mem_heap_alloc(heap, sizeof(ind_node_t));
221 + node->common.type = QUE_NODE_INSERT_STATS;
223 + node->index = index;
225 + node->state = INDEX_BUILD_STATS_COLS;
226 + node->page_no = FIL_NULL;
227 + node->heap = mem_heap_create(256);
229 + node->ind_def = NULL;
230 + node->field_def = NULL;
232 + node->stats_def = ins_node_create(INS_DIRECT,
233 + dict_sys->sys_stats, heap);
234 + node->stats_def->common.parent = node;
235 + node->stats_no = 0;
237 node->commit_node = commit_node_create(heap);
238 node->commit_node->common.parent = node;
240 @@ -1086,6 +1200,7 @@
242 node->state = INDEX_BUILD_FIELD_DEF;
244 + node->stats_no = 0;
246 thr->run_node = node->ind_def;
248 @@ -1131,7 +1246,31 @@
252 - node->state = INDEX_CREATE_INDEX_TREE;
253 + if (srv_use_sys_stats_table
254 + && !((node->table->flags >> DICT_TF2_SHIFT) & DICT_TF2_TEMPORARY)) {
255 + node->state = INDEX_BUILD_STATS_COLS;
257 + node->state = INDEX_CREATE_INDEX_TREE;
260 + if (node->state == INDEX_BUILD_STATS_COLS) {
261 + if (node->stats_no <= dict_index_get_n_unique(node->index)) {
263 + err = dict_build_stats_def_step(node);
265 + if (err != DB_SUCCESS) {
267 + goto function_exit;
272 + thr->run_node = node->stats_def;
276 + node->state = INDEX_CREATE_INDEX_TREE;
280 if (node->state == INDEX_CREATE_INDEX_TREE) {
281 @@ -1177,6 +1316,66 @@
285 + thr->run_node = que_node_get_parent(node);
290 +/****************************************************************//**
294 +dict_insert_stats_step(
295 +/*===================*/
296 + que_thr_t* thr) /*!< in: query thread */
299 + ulint err = DB_ERROR;
304 + trx = thr_get_trx(thr);
306 + node = thr->run_node;
308 + if (thr->prev_node == que_node_get_parent(node)) {
309 + node->state = INDEX_BUILD_STATS_COLS;
312 + if (node->state == INDEX_BUILD_STATS_COLS) {
313 + if (node->stats_no <= dict_index_get_n_unique(node->index)) {
315 + err = dict_build_stats_def_step(node);
317 + if (err != DB_SUCCESS) {
319 + goto function_exit;
324 + thr->run_node = node->stats_def;
328 + node->state = INDEX_COMMIT_WORK;
332 + if (node->state == INDEX_COMMIT_WORK) {
334 + /* do not commit transaction here for now */
338 + trx->error_state = err;
340 + if (err == DB_SUCCESS) {
345 thr->run_node = que_node_get_parent(node);
348 --- a/storage/innobase/dict/dict0dict.c
349 +++ b/storage/innobase/dict/dict0dict.c
351 print an error message and return without doing
353 dict_update_statistics(table, TRUE /* only update stats
354 - if they have not been initialized */);
355 + if they have not been initialized */, FALSE);
359 @@ -4354,6 +4354,295 @@
362 /*********************************************************************//**
363 +functions to use SYS_STATS system table. */
366 +dict_reload_statistics(
367 +/*===================*/
368 + dict_table_t* table,
369 + ulint* sum_of_index_sizes)
371 + dict_index_t* index;
375 + index = dict_table_get_first_index(table);
377 + if (index == NULL) {
378 + /* Table definition is corrupt */
383 + heap = mem_heap_create(1000);
386 + size = btr_get_size(index, BTR_TOTAL_SIZE);
388 + index->stat_index_size = size;
390 + *sum_of_index_sizes += size;
392 + size = btr_get_size(index, BTR_N_LEAF_PAGES);
395 + /* The root node of the tree is a leaf */
399 + index->stat_n_leaf_pages = size;
401 +/*===========================================*/
403 + dict_table_t* sys_stats;
404 + dict_index_t* sys_index;
414 + ib_int64_t* stat_n_diff_key_vals_tmp;
415 + ib_int64_t* stat_n_non_null_key_vals_tmp;
420 + n_cols = dict_index_get_n_unique(index);
421 + stat_n_diff_key_vals_tmp = mem_heap_zalloc(heap, (n_cols + 1) * sizeof(ib_int64_t));
422 + stat_n_non_null_key_vals_tmp = mem_heap_zalloc(heap, (n_cols + 1) * sizeof(ib_int64_t));
424 + sys_stats = dict_sys->sys_stats;
425 + sys_index = UT_LIST_GET_FIRST(sys_stats->indexes);
426 + ut_a(!dict_table_is_comp(sys_stats));
428 + tuple = dtuple_create(heap, 1);
429 + dfield = dtuple_get_nth_field(tuple, 0);
431 + buf = mem_heap_alloc(heap, 8);
432 + mach_write_to_8(buf, index->id);
434 + dfield_set_data(dfield, buf, 8);
435 + dict_index_copy_types(tuple, sys_index, 1);
439 + btr_pcur_open_on_user_rec(sys_index, tuple, PAGE_CUR_GE,
440 + BTR_SEARCH_LEAF, &pcur, &mtr);
441 + for (i = 0; i <= n_cols; i++) {
442 + rec = btr_pcur_get_rec(&pcur);
444 + if (!btr_pcur_is_on_user_rec(&pcur)
445 + || mach_read_from_8(rec_get_nth_field_old(rec, 0, &len))
447 + /* not found: even 1 if not found should not be alowed */
448 + fprintf(stderr, "InnoDB: Warning: stats for %s/%s (%lu/%lu)"
449 + " not found in SYS_STATS\n",
450 + index->table_name, index->name, i, n_cols);
451 + btr_pcur_close(&pcur);
453 + mem_heap_free(heap);
457 + if (rec_get_deleted_flag(rec, 0)) {
463 + n_fields = rec_get_n_fields_old(rec);
465 + field = rec_get_nth_field_old(rec, 1, &len);
468 + key_cols = mach_read_from_4(field);
470 + ut_a(i == key_cols);
472 + field = rec_get_nth_field_old(rec, DICT_SYS_STATS_DIFF_VALS_FIELD, &len);
475 + stat_n_diff_key_vals_tmp[i] = mach_read_from_8(field);
477 + if (n_fields > DICT_SYS_STATS_NON_NULL_VALS_FIELD) {
478 + field = rec_get_nth_field_old(rec, DICT_SYS_STATS_NON_NULL_VALS_FIELD, &len);
481 + stat_n_non_null_key_vals_tmp[i] = mach_read_from_8(field);
483 + /* not enough fields: should be older */
484 + fprintf(stderr, "InnoDB: Notice: stats for %s/%s (%lu/%lu)"
485 + " in SYS_STATS seems older format. "
486 + "Please execute ANALYZE TABLE for it.\n",
487 + index->table_name, index->name, i, n_cols);
489 + stat_n_non_null_key_vals_tmp[i] = ((ib_int64_t)(-1));
492 + btr_pcur_move_to_next_user_rec(&pcur, &mtr);
495 + btr_pcur_close(&pcur);
498 + for (i = 0; i <= n_cols; i++) {
499 + index->stat_n_diff_key_vals[i] = stat_n_diff_key_vals_tmp[i];
500 + if (stat_n_non_null_key_vals_tmp[i] == ((ib_int64_t)(-1))) {
501 + /* approximate value */
502 + index->stat_n_non_null_key_vals[i] = stat_n_diff_key_vals_tmp[n_cols];
504 + index->stat_n_non_null_key_vals[i] = stat_n_non_null_key_vals_tmp[i];
508 +/*===========================================*/
510 + index = dict_table_get_next_index(index);
513 + mem_heap_free(heap);
519 +dict_store_statistics(
520 +/*==================*/
521 + dict_table_t* table)
523 + dict_index_t* index;
526 + index = dict_table_get_first_index(table);
530 + heap = mem_heap_create(1000);
533 +/*===========================================*/
535 + dict_table_t* sys_stats;
536 + dict_index_t* sys_index;
547 + ib_int64_t* stat_n_diff_key_vals_tmp;
548 + ib_int64_t* stat_n_non_null_key_vals_tmp;
553 + n_cols = dict_index_get_n_unique(index);
554 + stat_n_diff_key_vals_tmp = mem_heap_zalloc(heap, (n_cols + 1) * sizeof(ib_int64_t));
555 + stat_n_non_null_key_vals_tmp = mem_heap_zalloc(heap, (n_cols + 1) * sizeof(ib_int64_t));
557 + for (i = 0; i <= n_cols; i++) {
558 + stat_n_diff_key_vals_tmp[i] = index->stat_n_diff_key_vals[i];
559 + stat_n_non_null_key_vals_tmp[i] = index->stat_n_non_null_key_vals[i];
562 + sys_stats = dict_sys->sys_stats;
563 + sys_index = UT_LIST_GET_FIRST(sys_stats->indexes);
564 + ut_a(!dict_table_is_comp(sys_stats));
566 + tuple = dtuple_create(heap, 1);
567 + dfield = dtuple_get_nth_field(tuple, 0);
569 + buf = mem_heap_alloc(heap, 8);
570 + mach_write_to_8(buf, index->id);
572 + dfield_set_data(dfield, buf, 8);
573 + dict_index_copy_types(tuple, sys_index, 1);
577 + btr_pcur_open_on_user_rec(sys_index, tuple, PAGE_CUR_GE,
578 + BTR_MODIFY_LEAF, &pcur, &mtr);
579 + rests = n_cols + 1;
580 + for (i = 0; i <= n_cols; i++) {
581 + rec = btr_pcur_get_rec(&pcur);
583 + if (!btr_pcur_is_on_user_rec(&pcur)
584 + || mach_read_from_8(rec_get_nth_field_old(rec, 0, &len))
592 + btr_pcur_store_position(&pcur, &mtr);
594 + if (rec_get_deleted_flag(rec, 0)) {
600 + n_fields = rec_get_n_fields_old(rec);
602 + if (n_fields <= DICT_SYS_STATS_NON_NULL_VALS_FIELD) {
603 + /* not update for the older smaller format */
604 + fprintf(stderr, "InnoDB: Notice: stats for %s/%s (%lu/%lu)"
605 + " in SYS_STATS seems older format. Please ANALYZE TABLE it.\n",
606 + index->table_name, index->name, i, n_cols);
610 + field = rec_get_nth_field_old(rec, 1, &len);
613 + key_cols = mach_read_from_4(field);
615 + field = rec_get_nth_field_old(rec, DICT_SYS_STATS_DIFF_VALS_FIELD, &len);
618 + mlog_write_ull((byte*)field, stat_n_diff_key_vals_tmp[key_cols], &mtr);
620 + field = rec_get_nth_field_old(rec, DICT_SYS_STATS_NON_NULL_VALS_FIELD, &len);
623 + mlog_write_ull((byte*)field, stat_n_non_null_key_vals_tmp[key_cols], &mtr);
630 + btr_pcur_restore_position(BTR_MODIFY_LEAF, &pcur, &mtr);
632 + btr_pcur_move_to_next_user_rec(&pcur, &mtr);
634 + btr_pcur_close(&pcur);
638 + fprintf(stderr, "InnoDB: Warning: failed to store %lu stats entries"
639 + " of %s/%s to SYS_STATS system table.\n",
640 + rests, index->table_name, index->name);
643 +/*===========================================*/
645 + index = dict_table_get_next_index(index);
648 + mem_heap_free(heap);
651 +/*********************************************************************//**
652 Calculates new estimates for table and index statistics. The statistics
653 are used in query optimization. */
655 @@ -4361,10 +4650,11 @@
656 dict_update_statistics(
657 /*===================*/
658 dict_table_t* table, /*!< in/out: table */
659 - ibool only_calc_if_missing_stats)/*!< in: only
660 + ibool only_calc_if_missing_stats,/*!< in: only
661 update/recalc the stats if they have
662 not been initialized yet, otherwise
664 + ibool sync) /*!< in: TRUE if must update SYS_STATS */
667 ulint sum_of_index_sizes = 0;
668 @@ -4381,6 +4671,27 @@
672 + if (srv_use_sys_stats_table && !((table->flags >> DICT_TF2_SHIFT) & DICT_TF2_TEMPORARY) && !sync) {
673 + dict_table_stats_lock(table, RW_X_LATCH);
675 + /* reload statistics from SYS_STATS table */
676 + if (dict_reload_statistics(table, &sum_of_index_sizes)) {
679 + fprintf(stderr, "InnoDB: DEBUG: reload_statistics succeeded for %s.\n",
685 + dict_table_stats_unlock(table, RW_X_LATCH);
688 + fprintf(stderr, "InnoDB: DEBUG: update_statistics for %s.\n",
691 + sum_of_index_sizes = 0;
693 /* Find out the sizes of the indexes and how many different values
694 for the key they approximately have */
696 @@ -4445,6 +4756,11 @@
697 index = dict_table_get_next_index(index);
700 + if (srv_use_sys_stats_table && !((table->flags >> DICT_TF2_SHIFT) & DICT_TF2_TEMPORARY)) {
701 + /* store statistics to SYS_STATS table */
702 + dict_store_statistics(table);
705 index = dict_table_get_first_index(table);
707 table->stat_n_rows = index->stat_n_diff_key_vals[
708 @@ -4462,6 +4778,78 @@
709 dict_table_stats_unlock(table, RW_X_LATCH);
712 +/*********************************************************************//**
716 +dict_is_older_statistics(
717 +/*=====================*/
718 + dict_index_t* index)
721 + dict_table_t* sys_stats;
722 + dict_index_t* sys_index;
732 + heap = mem_heap_create(100);
734 + sys_stats = dict_sys->sys_stats;
735 + sys_index = UT_LIST_GET_FIRST(sys_stats->indexes);
736 + ut_a(!dict_table_is_comp(sys_stats));
738 + tuple = dtuple_create(heap, 1);
739 + dfield = dtuple_get_nth_field(tuple, 0);
741 + buf = mem_heap_alloc(heap, 8);
742 + mach_write_to_8(buf, index->id);
744 + dfield_set_data(dfield, buf, 8);
745 + dict_index_copy_types(tuple, sys_index, 1);
749 + btr_pcur_open_on_user_rec(sys_index, tuple, PAGE_CUR_GE,
750 + BTR_SEARCH_LEAF, &pcur, &mtr);
753 + rec = btr_pcur_get_rec(&pcur);
755 + if (!btr_pcur_is_on_user_rec(&pcur)
756 + || mach_read_from_8(rec_get_nth_field_old(rec, 0, &len))
759 + btr_pcur_close(&pcur);
761 + mem_heap_free(heap);
762 + /* no statistics == not older statistics */
766 + if (rec_get_deleted_flag(rec, 0)) {
767 + btr_pcur_move_to_next_user_rec(&pcur, &mtr);
771 + n_fields = rec_get_n_fields_old(rec);
773 + btr_pcur_close(&pcur);
775 + mem_heap_free(heap);
777 + if (n_fields > DICT_SYS_STATS_NON_NULL_VALS_FIELD) {
784 /**********************************************************************//**
785 Prints info of a foreign key constraint. */
787 @@ -4539,7 +4927,8 @@
789 ut_ad(mutex_own(&(dict_sys->mutex)));
791 - dict_update_statistics(table, FALSE /* update even if initialized */);
792 + if (srv_stats_auto_update)
793 + dict_update_statistics(table, FALSE /* update even if initialized */, FALSE);
795 dict_table_stats_lock(table, RW_S_LATCH);
797 --- a/storage/innobase/dict/dict0load.c
798 +++ b/storage/innobase/dict/dict0load.c
804 + "SYS_FOREIGN_COLS",
808 /* If this flag is TRUE, then we will load the cluster index's (and tables')
809 @@ -348,12 +349,13 @@
812 if ((status & DICT_TABLE_UPDATE_STATS)
813 + && srv_stats_auto_update
814 && dict_table_get_first_index(*table)) {
816 /* Update statistics if DICT_TABLE_UPDATE_STATS
818 dict_update_statistics(*table, FALSE /* update even if
820 + initialized */, FALSE);
825 //#endif /* FOREIGN_NOT_USED */
827 /********************************************************************//**
828 +This function parses a SYS_STATS record and extract necessary
829 +information from the record and return to caller.
830 +@return error message, or NULL on success */
833 +dict_process_sys_stats_rec(
834 +/*=============================*/
835 + mem_heap_t* heap __attribute__((unused)), /*!< in/out: heap memory */
836 + const rec_t* rec, /*!< in: current SYS_STATS rec */
837 + index_id_t* index_id, /*!< out: INDEX_ID */
838 + ulint* key_cols, /*!< out: KEY_COLS */
839 + ib_uint64_t* diff_vals, /*!< out: DIFF_VALS */
840 + ib_uint64_t* non_null_vals) /*!< out: NON_NULL_VALS */
846 + if (UNIV_UNLIKELY(rec_get_deleted_flag(rec, 0))) {
847 + return("delete-marked record in SYS_STATS");
850 + n_fields = rec_get_n_fields_old(rec);
852 + if (UNIV_UNLIKELY(n_fields < 5)) {
853 + return("wrong number of columns in SYS_STATS record");
856 + field = rec_get_nth_field_old(rec, 0/*INDEX_ID*/, &len);
857 + if (UNIV_UNLIKELY(len != 8)) {
859 + return("incorrect column length in SYS_STATS");
861 + *index_id = mach_read_from_8(field);
863 + field = rec_get_nth_field_old(rec, 1/*KEY_COLS*/, &len);
864 + if (UNIV_UNLIKELY(len != 4)) {
867 + *key_cols = mach_read_from_4(field);
869 + rec_get_nth_field_offs_old(rec, 2/*DB_TRX_ID*/, &len);
870 + if (UNIV_UNLIKELY(len != DATA_TRX_ID_LEN && len != UNIV_SQL_NULL)) {
873 + rec_get_nth_field_offs_old(rec, 3/*DB_ROLL_PTR*/, &len);
874 + if (UNIV_UNLIKELY(len != DATA_ROLL_PTR_LEN && len != UNIV_SQL_NULL)) {
878 + field = rec_get_nth_field_old(rec, 4/*DIFF_VALS*/, &len);
879 + if (UNIV_UNLIKELY(len != 8)) {
882 + *diff_vals = mach_read_from_8(field);
884 + if (n_fields < 6) {
885 + *non_null_vals = ((ib_uint64_t)(-1));
887 + field = rec_get_nth_field_old(rec, 5/*NON_NULL_VALS*/, &len);
888 + if (UNIV_UNLIKELY(len != 8)) {
891 + *non_null_vals = mach_read_from_8(field);
896 +/********************************************************************//**
897 Determine the flags of a table described in SYS_TABLES.
898 @return compressed page size in kilobytes; or 0 if the tablespace is
899 uncompressed, ULINT_UNDEFINED on error */
900 --- a/storage/innobase/handler/ha_innodb.cc
901 +++ b/storage/innobase/handler/ha_innodb.cc
903 static my_bool innobase_create_status_file = FALSE;
904 static my_bool innobase_stats_on_metadata = TRUE;
905 static my_bool innobase_large_prefix = FALSE;
906 +static my_bool innobase_use_sys_stats_table = FALSE;
909 static char* internal_innobase_data_file_path = NULL;
910 @@ -2468,6 +2469,8 @@
914 + srv_use_sys_stats_table = (ibool) innobase_use_sys_stats_table;
916 /* -------------- Log files ---------------------------*/
918 /* The default dir for log files is the datadir of MySQL */
919 @@ -5256,6 +5259,10 @@
921 error = row_insert_for_mysql((byte*) record, prebuilt);
923 +#ifdef EXTENDED_FOR_USERSTAT
924 + if (error == DB_SUCCESS) rows_changed++;
927 /* Handle duplicate key errors */
930 @@ -5591,6 +5598,10 @@
934 +#ifdef EXTENDED_FOR_USERSTAT
935 + if (error == DB_SUCCESS) rows_changed++;
938 innodb_srv_conc_exit_innodb(trx);
940 error = convert_error_code_to_mysql(error,
941 @@ -5644,6 +5655,10 @@
943 error = row_update_for_mysql((byte*) record, prebuilt);
945 +#ifdef EXTENDED_FOR_USERSTAT
946 + if (error == DB_SUCCESS) rows_changed++;
949 innodb_srv_conc_exit_innodb(trx);
951 error = convert_error_code_to_mysql(
952 @@ -5965,6 +5980,11 @@
956 +#ifdef EXTENDED_FOR_USERSTAT
958 + if (active_index < MAX_KEY)
959 + index_rows_read[active_index]++;
962 case DB_RECORD_NOT_FOUND:
963 error = HA_ERR_KEY_NOT_FOUND;
964 @@ -6196,6 +6216,11 @@
968 +#ifdef EXTENDED_FOR_USERSTAT
970 + if (active_index < MAX_KEY)
971 + index_rows_read[active_index]++;
974 case DB_RECORD_NOT_FOUND:
975 error = HA_ERR_END_OF_FILE;
976 @@ -8149,11 +8174,35 @@
977 /* In sql_show we call with this flag: update
978 then statistics so that they are up-to-date */
980 + if (srv_use_sys_stats_table && !((ib_table->flags >> DICT_TF2_SHIFT) & DICT_TF2_TEMPORARY)
981 + && called_from_analyze) {
982 + /* If the indexes on the table don't have enough rows in SYS_STATS system table, */
983 + /* they need to be created. */
984 + dict_index_t* index;
986 + prebuilt->trx->op_info = "confirming rows of SYS_STATS to store statistics";
988 + ut_a(prebuilt->trx->conc_state == TRX_NOT_STARTED);
990 + for (index = dict_table_get_first_index(ib_table);
992 + index = dict_table_get_next_index(index)) {
993 + if (dict_is_older_statistics(index)) {
994 + row_delete_stats_for_mysql(index, prebuilt->trx);
995 + innobase_commit_low(prebuilt->trx);
997 + row_insert_stats_for_mysql(index, prebuilt->trx);
998 + innobase_commit_low(prebuilt->trx);
1001 + ut_a(prebuilt->trx->conc_state == TRX_NOT_STARTED);
1004 prebuilt->trx->op_info = "updating table statistics";
1006 dict_update_statistics(ib_table,
1007 FALSE /* update even if stats
1008 - are initialized */);
1009 + are initialized */, called_from_analyze);
1011 prebuilt->trx->op_info = "returning various info to MySQL";
1013 @@ -8238,7 +8287,7 @@
1014 are asked by MySQL to avoid locking. Another reason to
1015 avoid the call is that it uses quite a lot of CPU.
1017 - if (flag & HA_STATUS_NO_LOCK
1018 + if (flag & HA_STATUS_NO_LOCK || !srv_stats_update_need_lock
1019 || !(flag & HA_STATUS_VARIABLE_EXTRA)) {
1020 /* We do not update delete_length if no
1021 locking is requested so the "old" value can
1022 @@ -11511,6 +11560,26 @@
1023 "The number of index pages to sample when calculating statistics (default 8)",
1024 NULL, NULL, 8, 1, ~0ULL, 0);
1026 +static MYSQL_SYSVAR_ULONG(stats_auto_update, srv_stats_auto_update,
1027 + PLUGIN_VAR_RQCMDARG,
1028 + "Enable/Disable InnoDB's auto update statistics of indexes. "
1029 + "(except for ANALYZE TABLE command) 0:disable 1:enable",
1030 + NULL, NULL, 1, 0, 1, 0);
1032 +static MYSQL_SYSVAR_ULONG(stats_update_need_lock, srv_stats_update_need_lock,
1033 + PLUGIN_VAR_RQCMDARG,
1034 + "Enable/Disable InnoDB's update statistics which needs to lock dictionary. "
1035 + "e.g. Data_free.",
1036 + NULL, NULL, 1, 0, 1, 0);
1038 +static MYSQL_SYSVAR_BOOL(use_sys_stats_table, innobase_use_sys_stats_table,
1039 + PLUGIN_VAR_NOCMDARG | PLUGIN_VAR_READONLY,
1040 + "Enable to use SYS_STATS system table to store statistics statically, "
1041 + "And avoids to calculate statistics at every first open of the tables. "
1042 + "This option may make the opportunities of update statistics less. "
1043 + "So you should use ANALYZE TABLE command intentionally.",
1044 + NULL, NULL, FALSE);
1046 static MYSQL_SYSVAR_BOOL(adaptive_hash_index, btr_search_enabled,
1047 PLUGIN_VAR_OPCMDARG,
1048 "Enable InnoDB adaptive hash index (enabled by default). "
1049 @@ -11883,6 +11952,9 @@
1050 MYSQL_SYSVAR(recovery_update_relay_log),
1051 MYSQL_SYSVAR(rollback_on_timeout),
1052 MYSQL_SYSVAR(stats_on_metadata),
1053 + MYSQL_SYSVAR(stats_auto_update),
1054 + MYSQL_SYSVAR(stats_update_need_lock),
1055 + MYSQL_SYSVAR(use_sys_stats_table),
1056 MYSQL_SYSVAR(stats_sample_pages),
1057 MYSQL_SYSVAR(adaptive_hash_index),
1058 MYSQL_SYSVAR(stats_method),
1059 @@ -11957,7 +12029,10 @@
1060 i_s_innodb_sys_columns,
1061 i_s_innodb_sys_fields,
1062 i_s_innodb_sys_foreign,
1063 -i_s_innodb_sys_foreign_cols
1064 +i_s_innodb_sys_foreign_cols,
1065 +i_s_innodb_sys_stats,
1066 +i_s_innodb_table_stats,
1067 +i_s_innodb_index_stats
1068 mysql_declare_plugin_end;
1070 /** @brief Initialize the default value of innodb_commit_concurrency.
1071 --- a/storage/innobase/handler/i_s.cc
1072 +++ b/storage/innobase/handler/i_s.cc
1074 #include "trx0trx.h" /* for TRX_QUE_STATE_STR_MAX_LEN */
1075 #include "trx0rseg.h" /* for trx_rseg_struct */
1076 #include "trx0sys.h" /* for trx_sys */
1077 +#include "dict0dict.h" /* for dict_sys */
1081 @@ -3511,6 +3512,225 @@
1082 STRUCT_FLD(flags, 0UL)
1085 +/* Fields of the dynamic table INFORMATION_SCHEMA.innodb_sys_stats */
1086 +static ST_FIELD_INFO innodb_sys_stats_fields_info[] =
1088 +#define SYS_STATS_INDEX_ID 0
1089 + {STRUCT_FLD(field_name, "INDEX_ID"),
1090 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1091 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1092 + STRUCT_FLD(value, 0),
1093 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1094 + STRUCT_FLD(old_name, ""),
1095 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1097 +#define SYS_STATS_KEY_COLS 1
1098 + {STRUCT_FLD(field_name, "KEY_COLS"),
1099 + STRUCT_FLD(field_length, MY_INT32_NUM_DECIMAL_DIGITS),
1100 + STRUCT_FLD(field_type, MYSQL_TYPE_LONG),
1101 + STRUCT_FLD(value, 0),
1102 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1103 + STRUCT_FLD(old_name, ""),
1104 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1106 +#define SYS_STATS_DIFF_VALS 2
1107 + {STRUCT_FLD(field_name, "DIFF_VALS"),
1108 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1109 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1110 + STRUCT_FLD(value, 0),
1111 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1112 + STRUCT_FLD(old_name, ""),
1113 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1115 +#define SYS_STATS_NON_NULL_VALS 3
1116 + {STRUCT_FLD(field_name, "NON_NULL_VALS"),
1117 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1118 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1119 + STRUCT_FLD(value, 0),
1120 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED | MY_I_S_MAYBE_NULL),
1121 + STRUCT_FLD(old_name, ""),
1122 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1124 + END_OF_ST_FIELD_INFO
1126 +/**********************************************************************//**
1127 +Function to fill information_schema.innodb_sys_stats
1128 +@return 0 on success */
1131 +i_s_dict_fill_sys_stats(
1132 +/*====================*/
1133 + THD* thd, /*!< in: thread */
1134 + index_id_t index_id, /*!< in: INDEX_ID */
1135 + ulint key_cols, /*!< in: KEY_COLS */
1136 + ib_uint64_t diff_vals, /*!< in: DIFF_VALS */
1137 + ib_uint64_t non_null_vals, /*!< in: NON_NULL_VALS */
1138 + TABLE* table_to_fill) /*!< in/out: fill this table */
1142 + DBUG_ENTER("i_s_dict_fill_sys_stats");
1144 + fields = table_to_fill->field;
1146 + OK(fields[SYS_STATS_INDEX_ID]->store(longlong(index_id), TRUE));
1148 + OK(fields[SYS_STATS_KEY_COLS]->store(key_cols));
1150 + OK(fields[SYS_STATS_DIFF_VALS]->store(longlong(diff_vals), TRUE));
1152 + if (non_null_vals == ((ib_uint64_t)(-1))) {
1153 + fields[SYS_STATS_NON_NULL_VALS]->set_null();
1155 + OK(fields[SYS_STATS_NON_NULL_VALS]->store(longlong(non_null_vals), TRUE));
1156 + fields[SYS_STATS_NON_NULL_VALS]->set_notnull();
1159 + OK(schema_table_store_record(thd, table_to_fill));
1163 +/*******************************************************************//**
1164 +Function to populate INFORMATION_SCHEMA.innodb_sys_stats table.
1165 +@return 0 on success */
1168 +i_s_sys_stats_fill_table(
1169 +/*=====================*/
1170 + THD* thd, /*!< in: thread */
1171 + TABLE_LIST* tables, /*!< in/out: tables to fill */
1172 + COND* cond) /*!< in: condition (not used) */
1179 + DBUG_ENTER("i_s_sys_stats_fill_table");
1181 + /* deny access to non-superusers */
1182 + if (check_global_access(thd, PROCESS_ACL)) {
1186 + heap = mem_heap_create(1000);
1187 + mutex_enter(&dict_sys->mutex);
1190 + rec = dict_startscan_system(&pcur, &mtr, SYS_STATS);
1193 + const char* err_msg;
1194 + index_id_t index_id;
1196 + ib_uint64_t diff_vals;
1197 + ib_uint64_t non_null_vals;
1199 + /* Extract necessary information from a SYS_FOREIGN_COLS row */
1200 + err_msg = dict_process_sys_stats_rec(
1201 + heap, rec, &index_id, &key_cols, &diff_vals, &non_null_vals);
1204 + mutex_exit(&dict_sys->mutex);
1207 + i_s_dict_fill_sys_stats(
1208 + thd, index_id, key_cols, diff_vals, non_null_vals,
1211 + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
1212 + ER_CANT_FIND_SYSTEM_REC,
1216 + mem_heap_empty(heap);
1218 + /* Get the next record */
1219 + mutex_enter(&dict_sys->mutex);
1221 + rec = dict_getnext_system(&pcur, &mtr);
1225 + mutex_exit(&dict_sys->mutex);
1226 + mem_heap_free(heap);
1230 +/*******************************************************************//**
1231 +Bind the dynamic table INFORMATION_SCHEMA.innodb_sys_stats
1232 +@return 0 on success */
1235 +innodb_sys_stats_init(
1236 +/*========================*/
1237 + void* p) /*!< in/out: table schema object */
1239 + ST_SCHEMA_TABLE* schema;
1241 + DBUG_ENTER("innodb_sys_stats_init");
1243 + schema = (ST_SCHEMA_TABLE*) p;
1245 + schema->fields_info = innodb_sys_stats_fields_info;
1246 + schema->fill_table = i_s_sys_stats_fill_table;
1251 +UNIV_INTERN struct st_mysql_plugin i_s_innodb_sys_stats =
1253 + /* the plugin type (a MYSQL_XXX_PLUGIN value) */
1255 + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN),
1257 + /* pointer to type-specific plugin descriptor */
1259 + STRUCT_FLD(info, &i_s_info),
1263 + STRUCT_FLD(name, "INNODB_SYS_STATS"),
1265 + /* plugin author (for SHOW PLUGINS) */
1267 + STRUCT_FLD(author, "Percona"),
1269 + /* general descriptive text (for SHOW PLUGINS) */
1271 + STRUCT_FLD(descr, "XtraDB SYS_STATS table"),
1273 + /* the plugin license (PLUGIN_LICENSE_XXX) */
1275 + STRUCT_FLD(license, PLUGIN_LICENSE_GPL),
1277 + /* the function to invoke when plugin is loaded */
1278 + /* int (*)(void*); */
1279 + STRUCT_FLD(init, innodb_sys_stats_init),
1281 + /* the function to invoke when plugin is unloaded */
1282 + /* int (*)(void*); */
1283 + STRUCT_FLD(deinit, i_s_common_deinit),
1285 + /* plugin version (for SHOW PLUGINS) */
1286 + /* unsigned int */
1287 + STRUCT_FLD(version, INNODB_VERSION_SHORT),
1289 + /* struct st_mysql_show_var* */
1290 + STRUCT_FLD(status_vars, NULL),
1292 + /* struct st_mysql_sys_var** */
1293 + STRUCT_FLD(system_vars, NULL),
1295 + /* reserved for dependency checking */
1297 + STRUCT_FLD(__reserved1, NULL),
1299 + /* flags for plugin */
1300 + /* unsigned long */
1301 + STRUCT_FLD(flags, 0UL)
1304 /***********************************************************************
1306 static ST_FIELD_INFO i_s_innodb_rseg_fields_info[] =
1307 @@ -3677,3 +3897,349 @@
1309 STRUCT_FLD(flags, 0UL),
1312 +/***********************************************************************
1314 +static ST_FIELD_INFO i_s_innodb_table_stats_info[] =
1316 + {STRUCT_FLD(field_name, "table_schema"),
1317 + STRUCT_FLD(field_length, NAME_LEN),
1318 + STRUCT_FLD(field_type, MYSQL_TYPE_STRING),
1319 + STRUCT_FLD(value, 0),
1320 + STRUCT_FLD(field_flags, 0),
1321 + STRUCT_FLD(old_name, ""),
1322 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1324 + {STRUCT_FLD(field_name, "table_name"),
1325 + STRUCT_FLD(field_length, NAME_LEN),
1326 + STRUCT_FLD(field_type, MYSQL_TYPE_STRING),
1327 + STRUCT_FLD(value, 0),
1328 + STRUCT_FLD(field_flags, 0),
1329 + STRUCT_FLD(old_name, ""),
1330 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1332 + {STRUCT_FLD(field_name, "rows"),
1333 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1334 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1335 + STRUCT_FLD(value, 0),
1336 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1337 + STRUCT_FLD(old_name, ""),
1338 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1340 + {STRUCT_FLD(field_name, "clust_size"),
1341 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1342 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1343 + STRUCT_FLD(value, 0),
1344 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1345 + STRUCT_FLD(old_name, ""),
1346 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1348 + {STRUCT_FLD(field_name, "other_size"),
1349 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1350 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1351 + STRUCT_FLD(value, 0),
1352 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1353 + STRUCT_FLD(old_name, ""),
1354 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1356 + {STRUCT_FLD(field_name, "modified"),
1357 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1358 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1359 + STRUCT_FLD(value, 0),
1360 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1361 + STRUCT_FLD(old_name, ""),
1362 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1364 + END_OF_ST_FIELD_INFO
1367 +static ST_FIELD_INFO i_s_innodb_index_stats_info[] =
1369 + {STRUCT_FLD(field_name, "table_schema"),
1370 + STRUCT_FLD(field_length, NAME_LEN),
1371 + STRUCT_FLD(field_type, MYSQL_TYPE_STRING),
1372 + STRUCT_FLD(value, 0),
1373 + STRUCT_FLD(field_flags, 0),
1374 + STRUCT_FLD(old_name, ""),
1375 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1377 + {STRUCT_FLD(field_name, "table_name"),
1378 + STRUCT_FLD(field_length, NAME_LEN),
1379 + STRUCT_FLD(field_type, MYSQL_TYPE_STRING),
1380 + STRUCT_FLD(value, 0),
1381 + STRUCT_FLD(field_flags, 0),
1382 + STRUCT_FLD(old_name, ""),
1383 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1385 + {STRUCT_FLD(field_name, "index_name"),
1386 + STRUCT_FLD(field_length, NAME_LEN),
1387 + STRUCT_FLD(field_type, MYSQL_TYPE_STRING),
1388 + STRUCT_FLD(value, 0),
1389 + STRUCT_FLD(field_flags, 0),
1390 + STRUCT_FLD(old_name, ""),
1391 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1393 + {STRUCT_FLD(field_name, "fields"),
1394 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1395 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1396 + STRUCT_FLD(value, 0),
1397 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1398 + STRUCT_FLD(old_name, ""),
1399 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1401 + {STRUCT_FLD(field_name, "rows_per_key"),
1402 + STRUCT_FLD(field_length, 256),
1403 + STRUCT_FLD(field_type, MYSQL_TYPE_STRING),
1404 + STRUCT_FLD(value, 0),
1405 + STRUCT_FLD(field_flags, 0),
1406 + STRUCT_FLD(old_name, ""),
1407 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1409 + {STRUCT_FLD(field_name, "index_total_pages"),
1410 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1411 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1412 + STRUCT_FLD(value, 0),
1413 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1414 + STRUCT_FLD(old_name, ""),
1415 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1417 + {STRUCT_FLD(field_name, "index_leaf_pages"),
1418 + STRUCT_FLD(field_length, MY_INT64_NUM_DECIMAL_DIGITS),
1419 + STRUCT_FLD(field_type, MYSQL_TYPE_LONGLONG),
1420 + STRUCT_FLD(value, 0),
1421 + STRUCT_FLD(field_flags, MY_I_S_UNSIGNED),
1422 + STRUCT_FLD(old_name, ""),
1423 + STRUCT_FLD(open_method, SKIP_OPEN_TABLE)},
1425 + END_OF_ST_FIELD_INFO
1430 +i_s_innodb_table_stats_fill(
1431 +/*========================*/
1433 + TABLE_LIST* tables,
1436 + TABLE* i_s_table = (TABLE *) tables->table;
1438 + dict_table_t* table;
1440 + DBUG_ENTER("i_s_innodb_table_stats_fill");
1442 + /* deny access to non-superusers */
1443 + if (check_global_access(thd, PROCESS_ACL)) {
1447 + mutex_enter(&(dict_sys->mutex));
1449 + table = UT_LIST_GET_FIRST(dict_sys->table_LRU);
1452 + char buf[NAME_LEN * 2 + 2];
1455 + if (table->stat_clustered_index_size == 0) {
1456 + table = UT_LIST_GET_NEXT(table_LRU, table);
1460 + buf[NAME_LEN * 2 + 1] = 0;
1461 + strncpy(buf, table->name, NAME_LEN * 2 + 1);
1462 + ptr = strchr(buf, '/');
1470 + field_store_string(i_s_table->field[0], buf);
1471 + field_store_string(i_s_table->field[1], ptr);
1472 + i_s_table->field[2]->store(table->stat_n_rows);
1473 + i_s_table->field[3]->store(table->stat_clustered_index_size);
1474 + i_s_table->field[4]->store(table->stat_sum_of_other_index_sizes);
1475 + i_s_table->field[5]->store(table->stat_modified_counter);
1477 + if (schema_table_store_record(thd, i_s_table)) {
1482 + table = UT_LIST_GET_NEXT(table_LRU, table);
1485 + mutex_exit(&(dict_sys->mutex));
1487 + DBUG_RETURN(status);
1492 +i_s_innodb_index_stats_fill(
1493 +/*========================*/
1495 + TABLE_LIST* tables,
1498 + TABLE* i_s_table = (TABLE *) tables->table;
1500 + dict_table_t* table;
1501 + dict_index_t* index;
1503 + DBUG_ENTER("i_s_innodb_index_stats_fill");
1505 + /* deny access to non-superusers */
1506 + if (check_global_access(thd, PROCESS_ACL)) {
1510 + mutex_enter(&(dict_sys->mutex));
1512 + table = UT_LIST_GET_FIRST(dict_sys->table_LRU);
1515 + if (table->stat_clustered_index_size == 0) {
1516 + table = UT_LIST_GET_NEXT(table_LRU, table);
1520 + ib_int64_t n_rows = table->stat_n_rows;
1526 + index = dict_table_get_first_index(table);
1530 + char row_per_keys[256+1];
1531 + char buf[NAME_LEN * 2 + 2];
1535 + buf[NAME_LEN * 2 + 1] = 0;
1536 + strncpy(buf, table->name, NAME_LEN * 2 + 1);
1537 + ptr = strchr(buf, '/');
1545 + field_store_string(i_s_table->field[0], buf);
1546 + field_store_string(i_s_table->field[1], ptr);
1547 + field_store_string(i_s_table->field[2], index->name);
1548 + i_s_table->field[3]->store(index->n_uniq);
1550 + row_per_keys[0] = '\0';
1552 + /* It is remained optimistic operation still for now */
1553 + //dict_index_stat_mutex_enter(index);
1554 + if (index->stat_n_diff_key_vals) {
1555 + for (i = 1; i <= index->n_uniq; i++) {
1556 + ib_int64_t rec_per_key;
1557 + if (index->stat_n_diff_key_vals[i]) {
1558 + rec_per_key = n_rows / index->stat_n_diff_key_vals[i];
1560 + rec_per_key = n_rows;
1562 + ut_snprintf(buff, 256, (i == index->n_uniq)?"%llu":"%llu, ",
1564 + strncat(row_per_keys, buff, 256 - strlen(row_per_keys));
1567 + //dict_index_stat_mutex_exit(index);
1569 + field_store_string(i_s_table->field[4], row_per_keys);
1571 + i_s_table->field[5]->store(index->stat_index_size);
1572 + i_s_table->field[6]->store(index->stat_n_leaf_pages);
1574 + if (schema_table_store_record(thd, i_s_table)) {
1579 + index = dict_table_get_next_index(index);
1582 + if (status == 1) {
1586 + table = UT_LIST_GET_NEXT(table_LRU, table);
1589 + mutex_exit(&(dict_sys->mutex));
1591 + DBUG_RETURN(status);
1596 +i_s_innodb_table_stats_init(
1597 +/*========================*/
1600 + DBUG_ENTER("i_s_innodb_table_stats_init");
1601 + ST_SCHEMA_TABLE* schema = (ST_SCHEMA_TABLE*) p;
1603 + schema->fields_info = i_s_innodb_table_stats_info;
1604 + schema->fill_table = i_s_innodb_table_stats_fill;
1611 +i_s_innodb_index_stats_init(
1612 +/*========================*/
1615 + DBUG_ENTER("i_s_innodb_index_stats_init");
1616 + ST_SCHEMA_TABLE* schema = (ST_SCHEMA_TABLE*) p;
1618 + schema->fields_info = i_s_innodb_index_stats_info;
1619 + schema->fill_table = i_s_innodb_index_stats_fill;
1624 +UNIV_INTERN struct st_mysql_plugin i_s_innodb_table_stats =
1626 + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN),
1627 + STRUCT_FLD(info, &i_s_info),
1628 + STRUCT_FLD(name, "INNODB_TABLE_STATS"),
1629 + STRUCT_FLD(author, "Percona"),
1630 + STRUCT_FLD(descr, "InnoDB table statistics in memory"),
1631 + STRUCT_FLD(license, PLUGIN_LICENSE_GPL),
1632 + STRUCT_FLD(init, i_s_innodb_table_stats_init),
1633 + STRUCT_FLD(deinit, i_s_common_deinit),
1634 + STRUCT_FLD(version, 0x0100 /* 1.0 */),
1635 + STRUCT_FLD(status_vars, NULL),
1636 + STRUCT_FLD(system_vars, NULL),
1637 + STRUCT_FLD(__reserved1, NULL),
1638 + STRUCT_FLD(flags, 0UL)
1641 +UNIV_INTERN struct st_mysql_plugin i_s_innodb_index_stats =
1643 + STRUCT_FLD(type, MYSQL_INFORMATION_SCHEMA_PLUGIN),
1644 + STRUCT_FLD(info, &i_s_info),
1645 + STRUCT_FLD(name, "INNODB_INDEX_STATS"),
1646 + STRUCT_FLD(author, "Percona"),
1647 + STRUCT_FLD(descr, "InnoDB index statistics in memory"),
1648 + STRUCT_FLD(license, PLUGIN_LICENSE_GPL),
1649 + STRUCT_FLD(init, i_s_innodb_index_stats_init),
1650 + STRUCT_FLD(deinit, i_s_common_deinit),
1651 + STRUCT_FLD(version, 0x0100 /* 1.0 */),
1652 + STRUCT_FLD(status_vars, NULL),
1653 + STRUCT_FLD(system_vars, NULL),
1654 + STRUCT_FLD(__reserved1, NULL),
1655 + STRUCT_FLD(flags, 0UL)
1657 --- a/storage/innobase/handler/i_s.h
1658 +++ b/storage/innobase/handler/i_s.h
1660 extern struct st_mysql_plugin i_s_innodb_sys_foreign;
1661 extern struct st_mysql_plugin i_s_innodb_sys_foreign_cols;
1662 extern struct st_mysql_plugin i_s_innodb_rseg;
1663 +extern struct st_mysql_plugin i_s_innodb_sys_stats;
1664 +extern struct st_mysql_plugin i_s_innodb_table_stats;
1665 +extern struct st_mysql_plugin i_s_innodb_index_stats;
1668 --- a/storage/innobase/include/dict0boot.h
1669 +++ b/storage/innobase/include/dict0boot.h
1671 #define DICT_COLUMNS_ID 2
1672 #define DICT_INDEXES_ID 3
1673 #define DICT_FIELDS_ID 4
1674 +#define DICT_STATS_ID 6
1675 /* The following is a secondary index on SYS_TABLES */
1676 #define DICT_TABLE_IDS_ID 5
1678 @@ -131,10 +132,13 @@
1679 #define DICT_HDR_INDEXES 44 /* Root of the index index tree */
1680 #define DICT_HDR_FIELDS 48 /* Root of the index field
1682 +#define DICT_HDR_STATS 52 /* Root of the stats tree */
1684 #define DICT_HDR_FSEG_HEADER 56 /* Segment header for the tablespace
1685 segment into which the dictionary
1686 header is created */
1688 +#define DICT_HDR_XTRADB_MARK 256 /* Flag to distinguish expansion of XtraDB */
1689 /*-------------------------------------------------------------*/
1691 /* The field numbers in the SYS_TABLES clustered index */
1692 @@ -146,11 +150,16 @@
1693 #define DICT_SYS_INDEXES_TYPE_FIELD 6
1694 #define DICT_SYS_INDEXES_NAME_FIELD 4
1696 +#define DICT_SYS_STATS_DIFF_VALS_FIELD 4
1697 +#define DICT_SYS_STATS_NON_NULL_VALS_FIELD 5
1699 /* When a row id which is zero modulo this number (which must be a power of
1700 two) is assigned, the field DICT_HDR_ROW_ID on the dictionary header page is
1702 #define DICT_HDR_ROW_ID_WRITE_MARGIN 256
1704 +#define DICT_HDR_XTRADB_FLAG 0x5854524144425F31ULL /* "XTRADB_1" */
1707 #include "dict0boot.ic"
1709 --- a/storage/innobase/include/dict0crea.h
1710 +++ b/storage/innobase/include/dict0crea.h
1712 dict_index_t* index, /*!< in: index to create, built as a memory data
1714 mem_heap_t* heap); /*!< in: heap where created */
1715 +/*********************************************************************//**
1719 +ind_insert_stats_graph_create(
1720 +/*==========================*/
1721 + dict_index_t* index,
1722 + mem_heap_t* heap);
1723 /***********************************************************//**
1724 Creates a table. This is a high-level function used in SQL execution graphs.
1725 @return query thread to run next or NULL */
1727 /*===================*/
1728 que_thr_t* thr); /*!< in: query thread */
1729 /***********************************************************//**
1733 +dict_insert_stats_step(
1734 +/*===================*/
1736 +/***********************************************************//**
1737 Creates an index. This is a high-level function used in SQL execution
1739 @return query thread to run next or NULL */
1741 ins_node_t* field_def; /* child node which does the inserts of
1742 the field definitions; the row to be inserted
1743 is built by the parent node */
1744 + ins_node_t* stats_def;
1745 commit_node_t* commit_node;
1746 /* child node which performs a commit after
1747 a successful index creation */
1749 dict_table_t* table; /*!< table which owns the index */
1750 dtuple_t* ind_row;/* index definition row built */
1751 ulint field_no;/* next field definition to insert */
1753 mem_heap_t* heap; /*!< memory heap used as auxiliary storage */
1757 #define INDEX_CREATE_INDEX_TREE 3
1758 #define INDEX_COMMIT_WORK 4
1759 #define INDEX_ADD_TO_CACHE 5
1760 +#define INDEX_BUILD_STATS_COLS 6
1763 #include "dict0crea.ic"
1764 --- a/storage/innobase/include/dict0dict.h
1765 +++ b/storage/innobase/include/dict0dict.h
1766 @@ -1126,10 +1126,18 @@
1767 dict_update_statistics(
1768 /*===================*/
1769 dict_table_t* table, /*!< in/out: table */
1770 - ibool only_calc_if_missing_stats);/*!< in: only
1771 + ibool only_calc_if_missing_stats, /*!< in: only
1772 update/recalc the stats if they have
1773 not been initialized yet, otherwise
1776 +/*********************************************************************//**
1780 +dict_is_older_statistics(
1781 +/*=====================*/
1782 + dict_index_t* index);
1783 /********************************************************************//**
1784 Reserves the dictionary system mutex for MySQL. */
1786 @@ -1244,6 +1252,7 @@
1787 dict_table_t* sys_columns; /*!< SYS_COLUMNS table */
1788 dict_table_t* sys_indexes; /*!< SYS_INDEXES table */
1789 dict_table_t* sys_fields; /*!< SYS_FIELDS table */
1790 + dict_table_t* sys_stats; /*!< SYS_STATS table */
1792 #endif /* !UNIV_HOTBACKUP */
1794 --- a/storage/innobase/include/dict0load.h
1795 +++ b/storage/innobase/include/dict0load.h
1802 /* This must be last item. Defines the number of system tables. */
1803 SYS_NUM_SYSTEM_TABLES
1804 @@ -327,6 +328,20 @@
1805 const char** ref_col_name, /*!< out: referenced column name
1806 in referenced table */
1807 ulint* pos); /*!< out: column position */
1808 +/********************************************************************//**
1809 +This function parses a SYS_STATS record and extract necessary
1810 +information from the record and return to caller.
1811 +@return error message, or NULL on success */
1814 +dict_process_sys_stats_rec(
1815 +/*=============================*/
1816 + mem_heap_t* heap, /*!< in/out: heap memory */
1817 + const rec_t* rec, /*!< in: current SYS_STATS rec */
1818 + index_id_t* index_id, /*!< out: INDEX_ID */
1819 + ulint* key_cols, /*!< out: KEY_COLS */
1820 + ib_uint64_t* diff_vals, /*!< out: DIFF_VALS */
1821 + ib_uint64_t* non_null_vals); /*!< out: NON_NULL_VALS */
1823 #include "dict0load.ic"
1825 --- a/storage/innobase/include/que0que.h
1826 +++ b/storage/innobase/include/que0que.h
1828 #define QUE_NODE_CALL 31
1829 #define QUE_NODE_EXIT 32
1831 +#define QUE_NODE_INSERT_STATS 34
1833 /* Query thread states */
1834 #define QUE_THR_RUNNING 1
1835 #define QUE_THR_PROCEDURE_WAIT 2
1836 --- a/storage/innobase/include/row0mysql.h
1837 +++ b/storage/innobase/include/row0mysql.h
1838 @@ -387,6 +387,22 @@
1839 then checked for not being too
1841 /*********************************************************************//**
1845 +row_insert_stats_for_mysql(
1846 +/*=======================*/
1847 + dict_index_t* index,
1849 +/*********************************************************************//**
1853 +row_delete_stats_for_mysql(
1854 +/*=======================*/
1855 + dict_index_t* index,
1857 +/*********************************************************************//**
1858 Scans a table create SQL string and adds to the data dictionary
1859 the foreign key constraints declared in the string. This function
1860 should be called after the indexes for a table have been created.
1861 --- a/storage/innobase/include/srv0srv.h
1862 +++ b/storage/innobase/include/srv0srv.h
1864 extern ibool srv_innodb_status;
1866 extern unsigned long long srv_stats_sample_pages;
1867 +extern ulint srv_stats_auto_update;
1868 +extern ulint srv_stats_update_need_lock;
1869 +extern ibool srv_use_sys_stats_table;
1871 extern ibool srv_use_doublewrite_buf;
1872 extern ibool srv_use_checksums;
1873 --- a/storage/innobase/que/que0que.c
1874 +++ b/storage/innobase/que/que0que.c
1875 @@ -621,11 +621,21 @@
1877 que_graph_free_recursive(cre_ind->ind_def);
1878 que_graph_free_recursive(cre_ind->field_def);
1879 + if (srv_use_sys_stats_table)
1880 + que_graph_free_recursive(cre_ind->stats_def);
1881 que_graph_free_recursive(cre_ind->commit_node);
1883 mem_heap_free(cre_ind->heap);
1886 + case QUE_NODE_INSERT_STATS:
1889 + que_graph_free_recursive(cre_ind->stats_def);
1890 + que_graph_free_recursive(cre_ind->commit_node);
1892 + mem_heap_free(cre_ind->heap);
1895 que_graph_free_stat_list(((proc_node_t*)node)->stat_list);
1897 @@ -1138,6 +1148,8 @@
1898 str = "CREATE TABLE";
1899 } else if (type == QUE_NODE_CREATE_INDEX) {
1900 str = "CREATE INDEX";
1901 + } else if (type == QUE_NODE_INSERT_STATS) {
1902 + str = "INSERT TO SYS_STATS";
1903 } else if (type == QUE_NODE_FOR) {
1905 } else if (type == QUE_NODE_RETURN) {
1906 @@ -1255,6 +1267,8 @@
1907 thr = dict_create_table_step(thr);
1908 } else if (type == QUE_NODE_CREATE_INDEX) {
1909 thr = dict_create_index_step(thr);
1910 + } else if (type == QUE_NODE_INSERT_STATS) {
1911 + thr = dict_insert_stats_step(thr);
1912 } else if (type == QUE_NODE_ROW_PRINTF) {
1913 thr = row_printf_step(thr);
1915 --- a/storage/innobase/row/row0ins.c
1916 +++ b/storage/innobase/row/row0ins.c
1917 @@ -2018,6 +2018,8 @@
1921 + if (!srv_use_sys_stats_table
1922 + || index != UT_LIST_GET_FIRST(dict_sys->sys_stats->indexes))
1924 page_t* page = btr_cur_get_page(&cursor);
1925 rec_t* first_rec = page_rec_get_next(
1926 --- a/storage/innobase/row/row0merge.c
1927 +++ b/storage/innobase/row/row0merge.c
1928 @@ -2019,6 +2019,8 @@
1929 "UPDATE SYS_INDEXES SET NAME=CONCAT('"
1930 TEMP_INDEX_PREFIX_STR "', NAME) WHERE ID = :indexid;\n"
1932 + /* Drop the statistics of the index. */
1933 + "DELETE FROM SYS_STATS WHERE INDEX_ID = :indexid;\n"
1934 /* Drop the field definitions of the index. */
1935 "DELETE FROM SYS_FIELDS WHERE INDEX_ID = :indexid;\n"
1936 /* Drop the index definition and the B-tree. */
1937 --- a/storage/innobase/row/row0mysql.c
1938 +++ b/storage/innobase/row/row0mysql.c
1941 table->stat_modified_counter = counter + 1;
1943 + if (!srv_stats_auto_update)
1946 /* Calculate new statistics if 1 / 16 of table has been modified
1947 since the last time a statistics batch was run, or if
1948 stat_modified_counter > 2 000 000 000 (to avoid wrap-around).
1950 || ((ib_int64_t)counter > 16 + table->stat_n_rows / 16)) {
1952 dict_update_statistics(table, FALSE /* update even if stats
1953 - are initialized */);
1954 + are initialized */, TRUE);
1958 @@ -2077,6 +2080,71 @@
1961 /*********************************************************************//**
1965 +row_insert_stats_for_mysql(
1966 +/*=======================*/
1967 + dict_index_t* index,
1975 + //ut_ad(trx->mysql_thread_id == os_thread_get_curr_id());
1977 + trx->op_info = "try to insert rows to SYS_STATS";
1979 + trx_start_if_not_started(trx);
1980 + trx->error_state = DB_SUCCESS;
1982 + heap = mem_heap_create(512);
1984 + node = ind_insert_stats_graph_create(index, heap);
1986 + thr = pars_complete_graph_for_exec(node, trx, heap);
1988 + ut_a(thr == que_fork_start_command(que_node_get_parent(thr)));
1989 + que_run_threads(thr);
1991 + err = trx->error_state;
1993 + que_graph_free((que_t*) que_node_get_parent(thr));
1995 + trx->op_info = "";
1997 + return((int) err);
2000 +/*********************************************************************//**
2004 +row_delete_stats_for_mysql(
2005 +/*=============================*/
2006 + dict_index_t* index,
2009 + pars_info_t* info = pars_info_create();
2011 + trx->op_info = "delete rows from SYS_STATS";
2013 + trx_start_if_not_started(trx);
2014 + trx->error_state = DB_SUCCESS;
2016 + pars_info_add_ull_literal(info, "indexid", index->id);
2018 + return((int) que_eval_sql(info,
2019 + "PROCEDURE DELETE_STATISTICS_PROC () IS\n"
2021 + "DELETE FROM SYS_STATS WHERE INDEX_ID = :indexid;\n"
2026 +/*********************************************************************//**
2027 Scans a table create SQL string and adds to the data dictionary
2028 the foreign key constraints declared in the string. This function
2029 should be called after the indexes for a table have been created.
2030 @@ -3001,7 +3069,7 @@
2031 dict_table_autoinc_initialize(table, 1);
2032 dict_table_autoinc_unlock(table);
2033 dict_update_statistics(table, FALSE /* update even if stats are
2035 + initialized */, TRUE);
2037 trx_commit_for_mysql(trx);
2039 @@ -3312,6 +3380,8 @@
2040 " IF (SQL % NOTFOUND) THEN\n"
2043 + " DELETE FROM SYS_STATS\n"
2044 + " WHERE INDEX_ID = index_id;\n"
2045 " DELETE FROM SYS_FIELDS\n"
2046 " WHERE INDEX_ID = index_id;\n"
2047 " DELETE FROM SYS_INDEXES\n"
2048 --- a/storage/innobase/row/row0row.c
2049 +++ b/storage/innobase/row/row0row.c
2050 @@ -364,6 +364,14 @@
2052 rec_len = rec_offs_n_fields(offsets);
2054 + if (srv_use_sys_stats_table
2055 + && index == UT_LIST_GET_FIRST(dict_sys->sys_stats->indexes)) {
2056 + if (rec_len < dict_index_get_n_fields(index)) {
2057 + /* the new record should be extended */
2058 + rec_len = dict_index_get_n_fields(index);
2062 entry = dtuple_create(heap, rec_len);
2064 dtuple_set_n_fields_cmp(entry,
2065 @@ -375,6 +383,14 @@
2066 for (i = 0; i < rec_len; i++) {
2068 dfield = dtuple_get_nth_field(entry, i);
2070 + if (srv_use_sys_stats_table
2071 + && index == UT_LIST_GET_FIRST(dict_sys->sys_stats->indexes)
2072 + && i >= rec_offs_n_fields(offsets)) {
2073 + dfield_set_null(dfield);
2077 field = rec_get_nth_field(rec, offsets, i, &len);
2079 dfield_set_data(dfield, field, len);
2080 --- a/storage/innobase/row/row0upd.c
2081 +++ b/storage/innobase/row/row0upd.c
2082 @@ -439,6 +439,12 @@
2086 + if (srv_use_sys_stats_table
2087 + && index == UT_LIST_GET_FIRST(dict_sys->sys_stats->indexes)
2088 + && upd_field->field_no >= rec_offs_n_fields(offsets)) {
2092 old_len = rec_offs_nth_size(offsets, upd_field->field_no);
2094 if (rec_offs_comp(offsets)
2095 @@ -879,6 +885,18 @@
2097 for (i = 0; i < dtuple_get_n_fields(entry); i++) {
2099 + if (srv_use_sys_stats_table
2100 + && index == UT_LIST_GET_FIRST(dict_sys->sys_stats->indexes)
2101 + && i >= rec_offs_n_fields(offsets)) {
2102 + dfield = dtuple_get_nth_field(entry, i);
2104 + upd_field = upd_get_nth_field(update, n_diff);
2105 + dfield_copy(&(upd_field->new_val), dfield);
2106 + upd_field_set_field_no(upd_field, i, index, trx);
2108 + goto skip_compare;
2111 data = rec_get_nth_field(rec, offsets, i, &len);
2113 dfield = dtuple_get_nth_field(entry, i);
2114 --- a/storage/innobase/srv/srv0srv.c
2115 +++ b/storage/innobase/srv/srv0srv.c
2117 /* When estimating number of different key values in an index, sample
2118 this many index pages */
2119 UNIV_INTERN unsigned long long srv_stats_sample_pages = 8;
2120 +UNIV_INTERN ulint srv_stats_auto_update = 1;
2121 +UNIV_INTERN ulint srv_stats_update_need_lock = 1;
2122 +UNIV_INTERN ibool srv_use_sys_stats_table = FALSE;
2124 UNIV_INTERN ibool srv_use_doublewrite_buf = TRUE;
2125 UNIV_INTERN ibool srv_use_checksums = TRUE;
2126 --- a/storage/innobase/trx/trx0rec.c
2127 +++ b/storage/innobase/trx/trx0rec.c
2128 @@ -669,14 +669,27 @@
2129 /* Save to the undo log the old values of the columns to be updated. */
2132 + ulint extended = 0;
2134 if (trx_undo_left(undo_page, ptr) < 5) {
2139 - ptr += mach_write_compressed(ptr, upd_get_n_fields(update));
2140 + if (srv_use_sys_stats_table
2141 + && index == UT_LIST_GET_FIRST(dict_sys->sys_stats->indexes)) {
2142 + for (i = 0; i < upd_get_n_fields(update); i++) {
2143 + ulint pos = upd_get_nth_field(update, i)->field_no;
2145 + if (pos >= rec_offs_n_fields(offsets)) {
2151 + ptr += mach_write_compressed(ptr, upd_get_n_fields(update) - extended);
2153 - for (i = 0; i < upd_get_n_fields(update); i++) {
2154 + for (i = 0; i < upd_get_n_fields(update) - extended; i++) {
2156 ulint pos = upd_get_nth_field(update, i)->field_no;
2159 +++ b/mysql-test/r/percona_innodb_use_sys_stats_table.result
2161 +show variables like 'innodb_use_sys_stats%';
2162 +Variable_name Value
2163 +innodb_use_sys_stats_table ON
2165 +++ b/mysql-test/t/percona_innodb_use_sys_stats_table-master.opt
2167 +--innodb_use_sys_stats_table
2169 +++ b/mysql-test/t/percona_innodb_use_sys_stats_table.test
2171 +--source include/have_innodb.inc
2172 +show variables like 'innodb_use_sys_stats%';