From: Jonas Oreland Date: September 19 2011 9:30am Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (jonas.oreland:3559 to 3560) List-Archive: http://lists.mysql.com/commits/141001 Message-Id: <20110919093010.7C354D67B55@perch.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3560 Jonas Oreland 2011-09-19 [merge] ndb - merge 70 into 70-spj-scan-scan added: mysql-test/suite/ndb/r/ndb_index_stat_partitions.result mysql-test/suite/ndb/t/ndb_index_stat_partitions.test modified: sql/ha_ndb_index_stat.cc storage/ndb/src/ndbapi/NdbIndexStatImpl.cpp storage/ndb/src/ndbapi/NdbIndexStatImpl.hpp 3559 jonas oreland 2011-09-16 [merge] ndb - merge 70 to 70-spj modified: storage/ndb/src/kernel/blocks/dbspj/DbspjMain.cpp === added file 'mysql-test/suite/ndb/r/ndb_index_stat_partitions.result' --- a/mysql-test/suite/ndb/r/ndb_index_stat_partitions.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/r/ndb_index_stat_partitions.result 2011-09-19 08:16:01 +0000 @@ -0,0 +1,132 @@ +set @is_enable_default = @@global.ndb_index_stat_enable; +set @is_enable = 1; +set @is_enable = NULL; +# is_enable_on=1 is_enable_off=0 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +set @@global.ndb_index_stat_enable = 1; +set @@local.ndb_index_stat_enable = 1; +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +create table record_in_range(no int primary key auto_increment, +query varchar(256), part_count int, +explain_rows int, count_rows int) engine = myisam; +CREATE TABLE t1( +K INT NOT NULL AUTO_INCREMENT, +I INT, +J INT, +L INT, +PRIMARY KEY(K), +KEY(I,J), +KEY(L) +) ENGINE=ndbcluster +partition by key (K) partitions 1; +INSERT INTO t1(I,J,L) VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0); +INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1; +select i, count(*) from t1 group by 1 order by 1; +i count(*) +0 256 +1 256 +2 256 +3 256 +4 256 +5 256 +6 256 +7 256 +8 256 +9 256 +select l, count(*) from t1 group by 1 order by 1; +l count(*) +0 256 +1 256 +2 256 +3 256 +4 256 +5 256 +6 256 +7 256 +8 256 +9 256 +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +Table Op Msg_type Msg_text +test.t1 analyze status OK +select distinct +substring(rr.query from INSTR(rr.query, 'WHERE')) as 'WHERE', +count_rows, +p1.val as p1_val, +if(p8.err_pct is not null and abs(p8.err_pct)<45,'-',p8.val) p8_val, +case when p1.err_pct is null then '-' when abs(p1.err_pct)<45 then '<45' else p1.err_pct end p1_err_pct, +case when p8.err_pct is null then '-' when abs(p8.err_pct)<45 then '<45' else p8.err_pct end p8_err_pct +from record_in_range rr, +(select query, explain_rows as val, if(count_rows>0,floor(100*(explain_rows - count_rows) / count_rows),NULL) err_pct +from record_in_range p where part_count = 1) p1, +(select query, explain_rows as val, if(count_rows>0,floor(100*(explain_rows - count_rows) / count_rows),NULL) err_pct +from record_in_range p where part_count = 8) p8 +where rr.query = p1.query +and rr.query = p8.query +order by rr.no; +WHERE count_rows p1_val p8_val p1_err_pct p8_err_pct +WHERE i = 10 0 2 2 - - +WHERE i = 9 256 256 - <45 <45 +WHERE i = 8 256 256 - <45 <45 +WHERE i = 7 256 256 - <45 <45 +WHERE i = 6 256 256 - <45 <45 +WHERE i = 5 256 256 - <45 <45 +WHERE i = 4 256 256 - <45 <45 +WHERE i = 3 256 256 - <45 <45 +WHERE i = 2 256 256 - <45 <45 +WHERE i = 1 256 256 - <45 <45 +WHERE l = 10 0 2 2 - - +WHERE l = 9 256 256 - <45 <45 +WHERE l = 8 256 256 - <45 <45 +WHERE l = 7 256 256 - <45 <45 +WHERE l = 6 256 256 - <45 <45 +WHERE l = 5 256 256 - <45 <45 +WHERE l = 4 256 256 - <45 <45 +WHERE l = 3 256 256 - <45 <45 +WHERE l = 2 256 256 - <45 <45 +WHERE l = 1 256 256 - <45 <45 +DROP TABLE t1, record_in_range; +End of 5.1 tests +set @is_enable = @is_enable_default; +set @is_enable = NULL; +# is_enable_on=0 is_enable_off=1 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +set @@local.ndb_index_stat_enable = 0; +set @@global.ndb_index_stat_enable = 0; +drop table mysql.ndb_index_stat_sample; +drop table mysql.ndb_index_stat_head; +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF === added file 'mysql-test/suite/ndb/t/ndb_index_stat_partitions.test' --- a/mysql-test/suite/ndb/t/ndb_index_stat_partitions.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_index_stat_partitions.test 2011-09-19 08:16:01 +0000 @@ -0,0 +1,114 @@ +-- source include/have_ndb.inc + +set @is_enable_default = @@global.ndb_index_stat_enable; + +set @is_enable = 1; +source ndb_index_stat_enable.inc; + +create table record_in_range(no int primary key auto_increment, +query varchar(256), part_count int, +explain_rows int, count_rows int) engine = myisam; + +CREATE TABLE t1( + K INT NOT NULL AUTO_INCREMENT, + I INT, + J INT, + L INT, + PRIMARY KEY(K), + KEY(I,J), + KEY(L) +) ENGINE=ndbcluster + partition by key (K) partitions 1; + +INSERT INTO t1(I,J,L) VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0); + +INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1; +INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1; + +select i, count(*) from t1 group by 1 order by 1; +select l, count(*) from t1 group by 1 order by 1; + +ANALYZE TABLE t1; + +--disable_query_log +let $i = 10; +let $p = 1; +while ($i) +{ + --let $q=SELECT count(*) as Count FROM t1 WHERE i = $i + --let $explain_type= query_get_value(EXPLAIN $q, type, 1) + --let $explain_rows= query_get_value(EXPLAIN $q, rows, 1) + --let $count_rows= query_get_value($q, Count, 1) + --eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows); + dec $i; +} + +let $l = 10; +while ($l) +{ + --let $q=SELECT count(*) as Count FROM t1 WHERE l = $l + --let $explain_type= query_get_value(EXPLAIN $q, type, 1) + --let $explain_rows= query_get_value(EXPLAIN $q, rows, 1) + --let $count_rows= query_get_value($q, Count, 1) + --eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows); + dec $l; +} + +alter table t1 partition by key() partitions 8; +analyze table t1; + +let $p = 8; +let $i = 10; +while ($i) +{ + --let $q=SELECT count(*) as Count FROM t1 WHERE i = $i + --let $explain_type= query_get_value(EXPLAIN $q, type, 1) + --let $explain_rows= query_get_value(EXPLAIN $q, rows, 1) + --let $count_rows= query_get_value($q, Count, 1) + --eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows); + dec $i; +} + +let $l = 10; +while ($l) +{ + --let $q=SELECT count(*) as Count FROM t1 WHERE l = $l + --let $explain_type= query_get_value(EXPLAIN $q, type, 1) + --let $explain_rows= query_get_value(EXPLAIN $q, rows, 1) + --let $count_rows= query_get_value($q, Count, 1) + --eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows); + dec $l; +} + +--enable_query_log + +select distinct + substring(rr.query from INSTR(rr.query, 'WHERE')) as 'WHERE', + count_rows, + p1.val as p1_val, + if(p8.err_pct is not null and abs(p8.err_pct)<45,'-',p8.val) p8_val, + case when p1.err_pct is null then '-' when abs(p1.err_pct)<45 then '<45' else p1.err_pct end p1_err_pct, + case when p8.err_pct is null then '-' when abs(p8.err_pct)<45 then '<45' else p8.err_pct end p8_err_pct +from record_in_range rr, + (select query, explain_rows as val, if(count_rows>0,floor(100*(explain_rows - count_rows) / count_rows),NULL) err_pct + from record_in_range p where part_count = 1) p1, + (select query, explain_rows as val, if(count_rows>0,floor(100*(explain_rows - count_rows) / count_rows),NULL) err_pct + from record_in_range p where part_count = 8) p8 +where rr.query = p1.query + and rr.query = p8.query +order by rr.no; + +DROP TABLE t1, record_in_range; + +--echo End of 5.1 tests + +set @is_enable = @is_enable_default; +source ndb_index_stat_enable.inc; === modified file 'sql/ha_ndb_index_stat.cc' --- a/sql/ha_ndb_index_stat.cc 2011-09-02 06:43:38 +0000 +++ b/sql/ha_ndb_index_stat.cc 2011-09-19 09:26:42 +0000 @@ -24,6 +24,9 @@ #include #include +// Do we have waiter... +static bool ndb_index_stat_waiter= false; + // copied from ha_ndbcluster_binlog.h extern handlerton *ndbcluster_hton; @@ -1845,7 +1848,7 @@ ndb_index_stat_thread_func(void *arg __a for (;;) { pthread_mutex_lock(&LOCK_ndb_index_stat_thread); - if (!ndbcluster_terminating) { + if (!ndbcluster_terminating && ndb_index_stat_waiter == false) { int ret= pthread_cond_timedwait(&COND_ndb_index_stat_thread, &LOCK_ndb_index_stat_thread, &abstime); @@ -1855,6 +1858,7 @@ ndb_index_stat_thread_func(void *arg __a } if (ndbcluster_terminating) /* Shutting down server */ goto ndb_index_stat_thread_end; + ndb_index_stat_waiter= false; pthread_mutex_unlock(&LOCK_ndb_index_stat_thread); /* const bool enable_ok_new= THDVAR(NULL, index_stat_enable); */ @@ -2002,6 +2006,7 @@ ndb_index_stat_wait(Ndb_index_stat *st, DBUG_PRINT("index_stat", ("st %s wait count:%u", st->id, ++count)); pthread_mutex_lock(&LOCK_ndb_index_stat_thread); + ndb_index_stat_waiter= true; pthread_cond_signal(&COND_ndb_index_stat_thread); pthread_mutex_unlock(&LOCK_ndb_index_stat_thread); set_timespec(abstime, 1); === modified file 'storage/ndb/src/ndbapi/NdbIndexStatImpl.cpp' --- a/storage/ndb/src/ndbapi/NdbIndexStatImpl.cpp 2011-09-02 06:43:38 +0000 +++ b/storage/ndb/src/ndbapi/NdbIndexStatImpl.cpp 2011-09-19 08:13:58 +0000 @@ -1339,12 +1339,26 @@ NdbIndexStatImpl::Cache::swap_entry(uint } inline double -NdbIndexStatImpl::Cache::get_rir(uint pos) const +NdbIndexStatImpl::Cache::get_rir1(uint pos) const { const Uint8* ptr = get_valueptr(pos); Uint32 n; memcpy(&n, &ptr[0], 4); - double x = (double)m_fragCount * (double)n; + double x = (double)n; + return x; +} + +inline double +NdbIndexStatImpl::Cache::get_rir1(uint pos1, uint pos2) const +{ + assert(pos2 > pos1); + return get_rir1(pos2) - get_rir1(pos1); +} + +inline double +NdbIndexStatImpl::Cache::get_rir(uint pos) const +{ + double x = (double)m_fragCount * get_rir1(pos); return x; } @@ -1356,21 +1370,52 @@ NdbIndexStatImpl::Cache::get_rir(uint po } inline double -NdbIndexStatImpl::Cache::get_unq(uint pos, uint k) const +NdbIndexStatImpl::Cache::get_unq1(uint pos, uint k) const { assert(k < m_keyAttrs); const Uint8* ptr = get_valueptr(pos); Uint32 n; memcpy(&n, &ptr[4 + k * 4], 4); - double x = (double)m_fragCount * (double)n; + double x = (double)n; return x; } inline double -NdbIndexStatImpl::Cache::get_unq(uint pos1, uint pos2, uint k) const +NdbIndexStatImpl::Cache::get_unq1(uint pos1, uint pos2, uint k) const { assert(pos2 > pos1); - return get_unq(pos2, k) - get_unq(pos1, k); + return get_unq1(pos2, k) - get_unq1(pos1, k); +} + +static inline double +get_unqfactor(uint p, double r, double u) +{ + double ONE = (double)1.0; + double d = (double)p; + double f = ONE + (d - ONE) * ::pow(u / r, d - ONE); + return f; +} + +inline double +NdbIndexStatImpl::Cache::get_unq(uint pos, uint k) const +{ + uint p = m_fragCount; + double r = get_rir1(pos); + double u = get_unq1(pos, k); + double f = get_unqfactor(p, r, u); + double x = f * u; + return x; +} + +inline double +NdbIndexStatImpl::Cache::get_unq(uint pos1, uint pos2, uint k) const +{ + uint p = m_fragCount; + double r = get_rir1(pos1, pos2); + double u = get_unq1(pos1, pos2, k); + double f = get_unqfactor(p, r, u); + double x = f * u; + return x; } inline double === modified file 'storage/ndb/src/ndbapi/NdbIndexStatImpl.hpp' --- a/storage/ndb/src/ndbapi/NdbIndexStatImpl.hpp 2011-08-11 17:11:30 +0000 +++ b/storage/ndb/src/ndbapi/NdbIndexStatImpl.hpp 2011-09-19 08:13:58 +0000 @@ -197,8 +197,12 @@ public: // for sort void swap_entry(uint pos1, uint pos2); // get stats values primitives + double get_rir1(uint pos) const; + double get_rir1(uint pos1, uint pos2) const; double get_rir(uint pos) const; double get_rir(uint pos1, uint pos2) const; + double get_unq1(uint pos, uint k) const; + double get_unq1(uint pos1, uint pos2, uint k) const; double get_unq(uint pos, uint k) const; double get_unq(uint pos1, uint pos2, uint k) const; double get_rpk(uint pos, uint k) const; No bundle (reason: useless for push emails).