I propose the following (which should not be a huge extra effort)
1) add a ndb_statistics_check_time (as a mysqld and session variable)
2) create a "struct ndb_statistics_cache" (or similar)
instead of share->approx_records), so it's easy to add more stuff
3) make "analyze table A" force an update of the statistics for A
4) have pekka do his index statistics using same machinery
/Jonas
stewart@stripped wrote:
> Util thread maintains cache in table share, updated every
> ndb_cache_check_time. On info call, if count is zero - force a check.
>
> If ndb_cache_check_time is zero, leave 2sec between checks.
>
> For an exact row count, NDB requires round-trips to the data nodes.
>
> For approximate, we can cache.
>
> Old behaviour of disabling ndb_use_exact_count would return 100 as
> the row count. This led to horrible execution plans in some cases,
> but big respens time boosts in others.
>
> This patch gives us our cake *and* we can eat it too!
>
> I've observed 300% improvement in response time in some tests.
>
> Index: telco/sql/mysqld.cc
> ===================================================================
> --- telco.orig/sql/mysqld.cc 2007-10-12 12:19:43.125872633 +1000
> +++ telco/sql/mysqld.cc 2007-10-12 12:26:12.380964272 +1000
> @@ -4978,8 +4978,8 @@ enum options_mysqld
> OPT_DISCONNECT_SLAVE_EVENT_COUNT, OPT_TC_HEURISTIC_RECOVER,
> OPT_ABORT_SLAVE_EVENT_COUNT,
> OPT_LOG_BIN_TRUST_FUNCTION_CREATORS,
> - OPT_ENGINE_CONDITION_PUSHDOWN, OPT_NDB_CONNECTSTRING,
> - OPT_NDB_USE_EXACT_COUNT, OPT_NDB_USE_TRANSACTIONS,
> + OPT_ENGINE_CONDITION_PUSHDOWN, OPT_NDB_CONNECTSTRING,
> + OPT_NDB_USE_TRANSACTIONS,
> OPT_NDB_FORCE_SEND, OPT_NDB_AUTOINCREMENT_PREFETCH_SZ,
> OPT_NDB_SHM, OPT_NDB_OPTIMIZED_NODE_SELECTION, OPT_NDB_CACHE_CHECK_TIME,
> OPT_NDB_WAIT_CONNECTED,
> @@ -5577,17 +5577,6 @@ master-ssl",
> (uchar**) &opt_ndb_log_orig,
> 0, GET_BOOL, OPT_ARG, 0, 0, 0, 0, 0, 0},
> #endif
> - {"ndb-use-exact-count", OPT_NDB_USE_EXACT_COUNT,
> - "Use exact records count during query planning and for fast "
> - "select count(*), disable for faster queries.",
> - (uchar**) &global_system_variables.ndb_use_exact_count,
> - (uchar**) &global_system_variables.ndb_use_exact_count,
> - 0, GET_BOOL, OPT_ARG, 1, 0, 0, 0, 0, 0},
> - {"ndb_use_exact_count", OPT_NDB_USE_EXACT_COUNT,
> - "same as --ndb-use-exact-count.",
> - (uchar**) &global_system_variables.ndb_use_exact_count,
> - (uchar**) &global_system_variables.ndb_use_exact_count,
> - 0, GET_BOOL, OPT_ARG, 1, 0, 0, 0, 0, 0},
> {"ndb-use-transactions", OPT_NDB_USE_TRANSACTIONS,
> "Use transactions for large inserts, if enabled then large "
> "inserts will be split into several smaller transactions",
> Index: telco/sql/set_var.cc
> ===================================================================
> --- telco.orig/sql/set_var.cc 2007-10-12 12:19:43.129872829 +1000
> +++ telco/sql/set_var.cc 2007-10-12 12:26:12.388964664 +1000
> @@ -503,8 +503,6 @@ static sys_var_bool_ptr
> sys_ndb_log_updated_only(&vars, "ndb_log_updated_only",
> &opt_ndb_log_updated_only);
> #endif
> static sys_var_thd_bool
> -sys_ndb_use_exact_count(&vars, "ndb_use_exact_count",
> &SV::ndb_use_exact_count);
> -static sys_var_thd_bool
> sys_ndb_use_transactions(&vars, "ndb_use_transactions",
> &SV::ndb_use_transactions);
> static sys_var_long_ptr
> sys_ndb_cache_check_time(&vars, "ndb_cache_check_time",
> &ndb_cache_check_time);
> Index: telco/sql/sql_class.h
> ===================================================================
> --- telco.orig/sql/sql_class.h 2007-10-12 12:19:43.149873810 +1000
> +++ telco/sql/sql_class.h 2007-10-12 12:26:12.424966430 +1000
> @@ -320,7 +320,6 @@ struct system_variables
> my_bool keep_files_on_create;
> my_bool ndb_force_send;
> my_bool ndb_use_copying_alter_table;
> - my_bool ndb_use_exact_count;
> my_bool ndb_use_transactions;
> my_bool ndb_index_stat_enable;
>
> Index: telco/sql/ha_ndbcluster.cc
> ===================================================================
> --- telco.orig/sql/ha_ndbcluster.cc 2007-10-12 12:20:27.172032948 +1000
> +++ telco/sql/ha_ndbcluster.cc 2007-10-12 12:28:13.798919410 +1000
> @@ -670,8 +670,6 @@ ha_rows ha_ndbcluster::records()
>
> int ha_ndbcluster::records_update()
> {
> - if (m_ha_not_exact_count)
> - return 0;
> DBUG_ENTER("ha_ndbcluster::records_update");
> int result= 0;
>
> @@ -707,8 +705,7 @@ int ha_ndbcluster::records_update()
>
> void ha_ndbcluster::no_uncommitted_rows_execute_failure()
> {
> - if (m_ha_not_exact_count)
> - return;
> +
> DBUG_ENTER("ha_ndbcluster::no_uncommitted_rows_execute_failure");
> get_thd_ndb(current_thd)->m_error= TRUE;
> get_thd_ndb(current_thd)->m_error_code= 0;
> @@ -717,8 +714,6 @@ void ha_ndbcluster::no_uncommitted_rows_
>
> void ha_ndbcluster::no_uncommitted_rows_update(int c)
> {
> - if (m_ha_not_exact_count)
> - return;
> DBUG_ENTER("ha_ndbcluster::no_uncommitted_rows_update");
> struct Ndb_local_table_statistics *local_info= m_table_info;
> local_info->no_uncommitted_rows_count+= c;
> @@ -730,8 +725,6 @@ void ha_ndbcluster::no_uncommitted_rows_
>
> void ha_ndbcluster::no_uncommitted_rows_reset(THD *thd)
> {
> - if (m_ha_not_exact_count)
> - return;
> DBUG_ENTER("ha_ndbcluster::no_uncommitted_rows_reset");
> Thd_ndb *thd_ndb= get_thd_ndb(thd);
> thd_ndb->count++;
> @@ -4873,15 +4866,14 @@ int ha_ndbcluster::info(uint flag)
> if (flag & HA_STATUS_VARIABLE)
> {
> DBUG_PRINT("info", ("HA_STATUS_VARIABLE"));
> - if (m_table_info)
> - {
> - if (m_ha_not_exact_count)
> - stats.records= 100;
> - else
> - result= records_update();
> - }
> - else
> + DBUG_PRINT("info", ("use saved"));
> + ha_rows r;
> + pthread_mutex_lock(&m_share->mutex);
> + r= m_share->approx_records;
> + pthread_mutex_unlock(&m_share->mutex);
> + if(!r)
> {
> + DBUG_PRINT("info", ("update"));
> if ((my_errno= check_ndb_connection()))
> DBUG_RETURN(my_errno);
> Ndb *ndb= get_ndb();
> @@ -4891,13 +4883,13 @@ int ha_ndbcluster::info(uint flag)
> {
> DBUG_RETURN(my_errno= HA_ERR_OUT_OF_MEM);
> }
> - if (current_thd->variables.ndb_use_exact_count &&
> - (result= ndb_get_table_statistics
> - (this, TRUE, ndb, m_ndb_statistics_record, &stat)) == 0)
> + if ((result= ndb_get_table_statistics(this, TRUE, ndb,
> m_ndb_statistics_record, &stat)) == 0)
> {
> + pthread_mutex_lock(&m_share->mutex);
> + stats.records= m_share->approx_records= stat.row_count;
> + pthread_mutex_unlock(&m_share->mutex);
> stats.mean_rec_length= stat.row_size;
> stats.data_file_length= stat.fragment_memory;
> - stats.records= stat.row_count;
> }
> else
> {
> @@ -4905,6 +4897,11 @@ int ha_ndbcluster::info(uint flag)
> stats.records= 100;
> }
> }
> + else
> + {
> + DBUG_PRINT("info", ("Used cached value %llu",r));
> + stats.records= r;
> + }
> }
> if (flag & HA_STATUS_CONST)
> {
> @@ -5390,7 +5387,6 @@ int ha_ndbcluster::init_handler_for_stat
>
> DBUG_ENTER("ha_ndbcluster::init_handler_for_statement");
> // store thread specific data first to set the right context
> - m_ha_not_exact_count= !thd->variables.ndb_use_exact_count;
> m_autoincrement_prefetch=
> (ha_rows) thd->variables.ndb_autoincrement_prefetch_sz;
>
> @@ -7384,20 +7380,6 @@ void ha_ndbcluster::get_auto_increment(u
> Other flags are set under certain circumstaces in table_flags()
> */
> #define HA_NDBCLUSTER_TABLE_FLAGS \
> - HA_REC_NOT_IN_SEQ | \
> - HA_NULL_IN_KEY | \
> - HA_AUTO_PART_KEY | \
> - HA_NO_PREFIX_CHAR_KEYS | \
> - HA_NEED_READ_RANGE_BUFFER | \
> - HA_CAN_GEOMETRY | \
> - HA_CAN_BIT_FIELD | \
> - HA_PRIMARY_KEY_REQUIRED_FOR_POSITION | \
> - HA_PRIMARY_KEY_REQUIRED_FOR_DELETE | \
> - HA_PARTIAL_COLUMN_READ | \
> - HA_HAS_OWN_BINLOGGING | \
> - HA_BINLOG_ROW_CAPABLE | \
> - HA_HAS_RECORDS | \
> - HA_ONLINE_ALTER
>
>
> ha_ndbcluster::ha_ndbcluster(handlerton *hton, TABLE_SHARE *table_arg):
> @@ -7406,7 +7388,6 @@ ha_ndbcluster::ha_ndbcluster(handlerton
> m_active_cursor(NULL),
> m_table(NULL),
> m_table_info(NULL),
> - m_table_flags(HA_NDBCLUSTER_TABLE_FLAGS),
> m_share(0),
> m_part_info(NULL),
> m_use_partition_pruning(FALSE),
> @@ -7435,7 +7416,6 @@ ha_ndbcluster::ha_ndbcluster(handlerton
> m_ndb_hidden_key_record(0),
> m_ndb_statistics_record(0),
> m_dupkey((uint) -1),
> - m_ha_not_exact_count(FALSE),
> m_autoincrement_prefetch((ha_rows) 32),
> m_transaction_on(TRUE),
> m_cond(NULL)
> @@ -9030,9 +9010,24 @@ ha_ndbcluster::records_in_range(uint inx
> ulonglong ha_ndbcluster::table_flags(void) const
> {
> THD *thd= current_thd;
> - ulonglong f= m_table_flags;
> - if (m_ha_not_exact_count)
> - f= f & ~HA_STATS_RECORDS_IS_EXACT;
> + /*
> + Normal flags for binlogging is that ndb has HA_HAS_OWN_BINLOGGING
> + and preferes HA_BINLOG_ROW_CAPABLE
> + */
> + ulonglong f= (HA_REC_NOT_IN_SEQ |
> + HA_NULL_IN_KEY |
> + HA_AUTO_PART_KEY |
> + HA_NO_PREFIX_CHAR_KEYS |
> + HA_NEED_READ_RANGE_BUFFER |
> + HA_CAN_GEOMETRY |
> + HA_CAN_BIT_FIELD |
> + HA_PRIMARY_KEY_REQUIRED_FOR_POSITION |
> + HA_PRIMARY_KEY_REQUIRED_FOR_DELETE |
> + HA_PARTIAL_COLUMN_READ |
> + HA_HAS_OWN_BINLOGGING |
> + HA_BINLOG_ROW_CAPABLE |
> + HA_HAS_RECORDS |
> + HA_ONLINE_ALTER);
> /*
> To allow for logging of ndb tables during stmt based logging;
> flag cabablity, but also turn off flag for OWN_BINLOGGING
> @@ -9710,6 +9705,7 @@ NDB_SHARE *ndbcluster_get_share(const ch
> }
> #endif
> *root_ptr= old_root;
> + share->approx_records= 0;
> }
> else
> {
> @@ -10651,7 +10647,14 @@ ha_ndbcluster::update_table_comment(
> }
>
>
> -// Utility thread main loop
> +/**
> + * Utility thread main loop
> + *
> + * Utility thread has two jobs:
> + * - invalidate query cache (if ndb_cache_check_time is set, else no Qcache)
> + * - keep row count statistics up to date so we can give optimiser decent
> + * approx row count.
> + */
> pthread_handler_t ndb_util_thread_func(void *arg __attribute__((unused)))
> {
> THD *thd; /* needs to be first for thread_stack */
> @@ -10779,13 +10782,6 @@ pthread_handler_t ndb_util_thread_func(v
> ndbcluster_setup_binlog_table_shares(thd);
> #endif
>
> - if (ndb_cache_check_time == 0)
> - {
> - /* Wake up in 1 second to check if value has changed */
> - set_timespec(abstime, 1);
> - continue;
> - }
> -
> /* Lock mutex and fill list with pointers to all open tables */
> NDB_SHARE *share;
> pthread_mutex_lock(&ndbcluster_mutex);
> @@ -10872,6 +10868,9 @@ pthread_handler_t ndb_util_thread_func(v
> share->key,
> llstr(stat.commit_count, buff),
> llstr(stat.row_count, buff2)));
> + pthread_mutex_lock(&share->mutex);
> + share->approx_records= stat.row_count;
> + pthread_mutex_unlock(&share->mutex);
> }
> else
> {
> @@ -10894,8 +10893,14 @@ pthread_handler_t ndb_util_thread_func(v
> }
> next:
> /* Calculate new time to wake up */
> +
> int secs= 0;
> - int msecs= ndb_cache_check_time;
> + int msecs;
> +
> + if(ndb_cache_check_time)
> + msecs= ndb_cache_check_time;
> + else
> + msecs= 2000; /* keep statistics up to date */
>
> struct timeval tick_time;
> gettimeofday(&tick_time, 0);
> Index: telco/sql/ha_ndbcluster.h
> ===================================================================
> --- telco.orig/sql/ha_ndbcluster.h 2007-10-12 12:20:27.188033732 +1000
> +++ telco/sql/ha_ndbcluster.h 2007-10-12 12:28:38.132112870 +1000
> @@ -161,6 +161,7 @@ typedef struct st_ndbcluster_share {
> MY_BITMAP *subscriber_bitmap;
> NdbEventOperation *new_op;
> #endif
> + ha_rows approx_records;
> } NDB_SHARE;
>
> inline
> @@ -705,7 +706,6 @@ private:
> char m_dbname[FN_HEADLEN];
> //char m_schemaname[FN_HEADLEN];
> char m_tabname[FN_HEADLEN];
> - ulonglong m_table_flags;
> THR_LOCK_DATA m_lock;
> bool m_lock_tuple;
> NDB_SHARE *m_share;
> @@ -766,7 +766,6 @@ private:
> uint32 m_blobs_buffer_size;
> uint m_dupkey;
> // set from thread variables at external lock
> - bool m_ha_not_exact_count;
> ha_rows m_autoincrement_prefetch;
> bool m_transaction_on;
>
> Index: telco/mysql-test/suite/ndb/t/ndb_alter_table.test
> ===================================================================
> --- telco.orig/mysql-test/suite/ndb/t/ndb_alter_table.test 2007-10-12
> 12:19:43.241878323 +1000
> +++ telco/mysql-test/suite/ndb/t/ndb_alter_table.test 2007-10-12 12:26:12.580974082
> +1000
> @@ -55,12 +55,12 @@ col3 varchar (20) not null,
> col4 varchar(4) not null,
> col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
> col6 int not null, to_be_deleted int) ENGINE=ndbcluster;
> ---replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> +--replace_column 5 # 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> show table status;
> SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
> insert into t1 values
> (0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7),
> (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7),
> (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7);
> ---replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> +--replace_column 5 # 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> show table status;
> select * from t1 order by col1;
> alter table t1
> @@ -69,11 +69,11 @@ add column col7 varchar(30) not null aft
> add column col8 datetime not null, drop column to_be_deleted,
> change column col2 fourth varchar(30) not null after col3,
> modify column col6 int not null first;
> ---replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> +--replace_column 5 # 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> show table status;
> select * from t1 order by col1;
> insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
> ---replace_column 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> +--replace_column 5 # 6 # 7 # 8 # 10 # 12 # 13 # 14 # 18 #
> show table status;
> select * from t1 order by col1;
> delete from t1;
> Index: telco/mysql-test/suite/ndb/r/ndb_alter_table.result
> ===================================================================
> --- telco.orig/mysql-test/suite/ndb/r/ndb_alter_table.result 2007-10-12
> 12:19:43.389885582 +1000
> +++ telco/mysql-test/suite/ndb/r/ndb_alter_table.result 2007-10-12 12:26:12.900989777
> +1000
> @@ -34,13 +34,13 @@ col5 enum('PENDING', 'ACTIVE', 'DISABLED
> col6 int not null, to_be_deleted int) ENGINE=ndbcluster;
> show table status;
>
> Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
> -t1 ndbcluster 10 Dynamic 0 # # # 0 # 1 # # # latin1_swedish_ci NULL #
> +t1 ndbcluster 10 Dynamic # # # # 0 # 1 # # # latin1_swedish_ci NULL #
> SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
> insert into t1 values
> (0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7),
> (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7),
> (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7);
> show table status;
>
> Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
> -t1 ndbcluster 10 Dynamic 9 # # # 0 # 102 # # # latin1_swedish_ci NULL #
> +t1 ndbcluster 10 Dynamic # # # # 0 # 102 # # # latin1_swedish_ci NULL #
> select * from t1 order by col1;
> col1 col2 col3 col4 col5 col6 to_be_deleted
> 0 4 3 5 PENDING 1 7
> @@ -60,7 +60,7 @@ change column col2 fourth varchar(30) no
> modify column col6 int not null first;
> show table status;
>
> Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
> -t1 ndbcluster 10 Dynamic 9 # # # 0 # 102 # # # latin1_swedish_ci NULL #
> +t1 ndbcluster 10 Dynamic # # # # 0 # 102 # # # latin1_swedish_ci NULL #
> select * from t1 order by col1;
> col6 col1 col3 fourth col4 col4_5 col5 col7 col8
> 1 0 3 4 5 PENDING 0000-00-00 00:00:00
> @@ -75,7 +75,7 @@ col6 col1 col3 fourth col4 col4_5 col5 c
> insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
> show table status;
>
> Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
> -t1 ndbcluster 10 Dynamic 10 # # # 0 # 103 # # # latin1_swedish_ci NULL #
> +t1 ndbcluster 10 Dynamic # # # # 0 # 103 # # # latin1_swedish_ci NULL #
> select * from t1 order by col1;
> col6 col1 col3 fourth col4 col4_5 col5 col7 col8
> 1 0 3 4 5 PENDING 0000-00-00 00:00:00
> Index: telco/mysql-test/suite/ndb/r/ndb_basic.result
> ===================================================================
> --- telco.orig/mysql-test/suite/ndb/r/ndb_basic.result 2007-10-12 12:20:27.208034713
> +1000
> +++ telco/mysql-test/suite/ndb/r/ndb_basic.result 2007-10-12 12:26:12.964992917
> +1000
> @@ -31,7 +31,6 @@ ndb_optimized_node_selection #
> ndb_report_thresh_binlog_epoch_slip #
> ndb_report_thresh_binlog_mem_usage #
> ndb_use_copying_alter_table #
> -ndb_use_exact_count #
> ndb_use_transactions #
> CREATE TABLE t1 (
> pk1 INT NOT NULL PRIMARY KEY,
>
> --
> Stewart Smith
>
>
> If you don't want to be on this alias and only read these threads in the forums,
> click this link: https://intranet.mysql.com/secure/forum/unsubscribe.php?61
