From 1c0a89afcc1581187e8ee84abbd445da2bfa45d9 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 11 Apr 2012 17:14:06 -0700 Subject: The pilot implementation of mwl#250: Use the statistics from persistent statistical tables instead of the statistics provided by engine. --- sql/sql_base.h | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/sql_base.h') diff --git a/sql/sql_base.h b/sql/sql_base.h index 5b88d53d231..203f8c18e14 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -315,6 +315,7 @@ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived); int read_statistics_for_table(THD *thd, TABLE *table); int collect_statistics_for_table(THD *thd, TABLE *table); int update_statistics_for_table(THD *thd, TABLE *table); +void set_statistics_for_table(THD *thd, TABLE *table); extern "C" int simple_raw_key_cmp(void* arg, const void* key1, const void* key2); -- cgit v1.2.1 From 47fae7f08fd0437cd555bbd6b2533a4117340c8d Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 10 Jul 2012 16:34:39 -0700 Subject: Added procedures to delete records by keys from statistical tables. Now when a table is dropped the statistics on the table is removed from the statistical tables. If the table is altered in such a way that a column is dropped or the type of the column is changed then statistics on the column is removed from the table column_stat. It also triggers removal of the statistics on the indexes who use this column as its component. Added procedures that changes the names of the tables or columns in the statistical tables for. These procedures are used when tables/columns are renamed. Also partly re-factored the code that introduced the persistent statistical tables. Added test cases into statistics.test to cover the new code. --- sql/sql_base.h | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'sql/sql_base.h') diff --git a/sql/sql_base.h b/sql/sql_base.h index 44d107376b0..6c20022f7ee 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -315,6 +315,13 @@ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived); int read_statistics_for_table(THD *thd, TABLE *table); int collect_statistics_for_table(THD *thd, TABLE *table); int update_statistics_for_table(THD *thd, TABLE *table); +int delete_statistics_for_table(THD *thd, LEX_STRING *db, LEX_STRING *tab); +int delete_statistics_for_column(THD *thd, TABLE *tab, Field *col); +int delete_statistics_for_index(THD *thd, TABLE *tab, KEY *key_info); +int rename_table_in_stat_tables(THD *thd, LEX_STRING *db, LEX_STRING *tab, + LEX_STRING *new_db, LEX_STRING *new_tab); +int rename_column_in_stat_tables(THD *thd, TABLE *tab, Field *col, + const char *new_name); void set_statistics_for_table(THD *thd, TABLE *table); extern "C" int simple_raw_key_cmp(void* arg, const void* key1, -- cgit v1.2.1 From 8c499274da21af6226785d51dd24968bf2b1befe Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 26 Jul 2012 17:50:08 -0700 Subject: Performed re-factoring and re-structuring of the code for mwl#248: - Moved the definitions of the classes to store data from persistent statistical tables into statistics.h, leaving in other internal data structures only references to the corresponding objects. - Defined class Column_statistics_collected derived from the class Column_statistics. This is a helper class to collect statistics on columns. - Moved references to read statistics to TABLE SHARE, leaving the the reference to the collected statistics in TABLE. - Added a new clone method for the class Field allowing to clone fields attached to table shares. It was was used to create fields for min/max values in the memory of the table share. A lso: - Added procedures to allocate memory for statistical data in the table share memory and in table memory. Also: - Added a test case demonstrating how ANALYZE could work in parallel to collect statistics on different indexes of the same table. - Added a test two demonstrate how two connections working simultaneously could allocate memory for statistical data in the table share memory. --- sql/sql_base.h | 3 +++ 1 file changed, 3 insertions(+) (limited to 'sql/sql_base.h') diff --git a/sql/sql_base.h b/sql/sql_base.h index 6c20022f7ee..a8d4951981e 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -314,6 +314,9 @@ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived); int read_statistics_for_table(THD *thd, TABLE *table); int collect_statistics_for_table(THD *thd, TABLE *table); +int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *share, + bool is_safe); +int alloc_statistics_for_table(THD *thd, TABLE *table); int update_statistics_for_table(THD *thd, TABLE *table); int delete_statistics_for_table(THD *thd, LEX_STRING *db, LEX_STRING *tab); int delete_statistics_for_column(THD *thd, TABLE *tab, Field *col); -- cgit v1.2.1 From b3f09e8aa04aa3d2d2b86588eddd46badb7e916f Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 8 Sep 2012 12:04:31 -0700 Subject: Fixed bug mdev-504. Opening system statistical tables and reading statistical data from them for a regular table should be done after opening and locking this regular table. No test case is provided with this patch. --- sql/sql_base.h | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_base.h') diff --git a/sql/sql_base.h b/sql/sql_base.h index a8d4951981e..698c4a012e7 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -312,7 +312,7 @@ int dynamic_column_error_message(enum_dyncol_func_result rc); /* open_and_lock_tables with optional derived handling */ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived); -int read_statistics_for_table(THD *thd, TABLE *table); +int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables); int collect_statistics_for_table(THD *thd, TABLE *table); int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *share, bool is_safe); -- cgit v1.2.1 From 2447bc4c818434d2e6411dccf58887b45d47af58 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 8 Dec 2012 15:38:15 -0800 Subject: Addressed the following issue from the review of the patch for engine-independent statistics. When the primary key was dropped or changed statistics on secondary indexes for the prefixes that included components of the primary key was not removed from the table mysql.index_stats. Also fixed: in the some cases when a column was changed statistics on the indexes that included this column was not removed from the table mysql.index_stats. Also disabled the test mdev-504 for --ps-protocol. --- sql/sql_base.h | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'sql/sql_base.h') diff --git a/sql/sql_base.h b/sql/sql_base.h index 71b4a9289ce..d45652927de 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -321,7 +321,8 @@ int alloc_statistics_for_table(THD *thd, TABLE *table); int update_statistics_for_table(THD *thd, TABLE *table); int delete_statistics_for_table(THD *thd, LEX_STRING *db, LEX_STRING *tab); int delete_statistics_for_column(THD *thd, TABLE *tab, Field *col); -int delete_statistics_for_index(THD *thd, TABLE *tab, KEY *key_info); +int delete_statistics_for_index(THD *thd, TABLE *tab, KEY *key_info, + bool ext_prefixes_only); int rename_table_in_stat_tables(THD *thd, LEX_STRING *db, LEX_STRING *tab, LEX_STRING *new_db, LEX_STRING *new_tab); int rename_column_in_stat_tables(THD *thd, TABLE *tab, Field *col, -- cgit v1.2.1 From 65820439bdafeead66496b489c076012c334c710 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 12 Dec 2012 23:16:54 -0800 Subject: Fixed bug mdev-3891. If a query referenced some system statistical tables, but not all of them, then executing an ANALYZE command simultaneously with this query could lead to a deadlock. The fix prohibited reading statistics from system statistical tables for such queries. Removed the function unlock_tables_n_open_system_tables_for_write() as not used anymore. Performed some minor refactoring of the code in sql_statistics.cc. --- sql/sql_base.h | 3 --- 1 file changed, 3 deletions(-) (limited to 'sql/sql_base.h') diff --git a/sql/sql_base.h b/sql/sql_base.h index d45652927de..c4cd7f467a0 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -275,9 +275,6 @@ bool is_equal(const LEX_STRING *a, const LEX_STRING *b); /* Functions to work with system tables. */ bool open_system_tables_for_read(THD *thd, TABLE_LIST *table_list, Open_tables_backup *backup); -bool unlock_tables_n_open_system_tables_for_write(THD *thd, - TABLE_LIST *table_list, - Open_tables_backup *backup); void close_system_tables(THD *thd, Open_tables_backup *backup); void close_mysql_tables(THD *thd); TABLE *open_system_table_for_update(THD *thd, TABLE_LIST *one_table); -- cgit v1.2.1 From a06224bd1594ea1da650f748a8956922eafd2363 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 13 Dec 2012 23:05:12 -0800 Subject: Addressed all remaining issues from the review of the patch that introduced engine independent persistent statistics. In particular: - added an enumeration type for possible values of the system variable use_stat_tables - renamed KEY::real_rec_per_key to KEY::actual_rec_per_key - optimized the collection of statistical data for any primary key defined only on one column. --- sql/sql_base.h | 16 ---------------- 1 file changed, 16 deletions(-) (limited to 'sql/sql_base.h') diff --git a/sql/sql_base.h b/sql/sql_base.h index c4cd7f467a0..aa2ba9e5680 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -310,22 +310,6 @@ int dynamic_column_error_message(enum_dyncol_func_result rc); /* open_and_lock_tables with optional derived handling */ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived); -int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables); -int collect_statistics_for_table(THD *thd, TABLE *table); -int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *share, - bool is_safe); -int alloc_statistics_for_table(THD *thd, TABLE *table); -int update_statistics_for_table(THD *thd, TABLE *table); -int delete_statistics_for_table(THD *thd, LEX_STRING *db, LEX_STRING *tab); -int delete_statistics_for_column(THD *thd, TABLE *tab, Field *col); -int delete_statistics_for_index(THD *thd, TABLE *tab, KEY *key_info, - bool ext_prefixes_only); -int rename_table_in_stat_tables(THD *thd, LEX_STRING *db, LEX_STRING *tab, - LEX_STRING *new_db, LEX_STRING *new_tab); -int rename_column_in_stat_tables(THD *thd, TABLE *tab, Field *col, - const char *new_name); -void set_statistics_for_table(THD *thd, TABLE *table); - extern "C" int simple_raw_key_cmp(void* arg, const void* key1, const void* key2); extern "C" int count_distinct_walk(void *elem, element_count count, void *arg); -- cgit v1.2.1