diff options
| -rw-r--r-- | BitKeeper/etc/logging_ok | 6 | ||||
| -rwxr-xr-x | sql-bench/test-insert.sh | 115 | ||||
| -rw-r--r-- | sql/filesort.cc | 7 | ||||
| -rw-r--r-- | sql/ha_berkeley.cc | 24 | ||||
| -rw-r--r-- | sql/ha_berkeley.h | 3 | ||||
| -rw-r--r-- | sql/handler.h | 1 | ||||
| -rw-r--r-- | sql/sql_class.h | 2 |
7 files changed, 127 insertions, 31 deletions
diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok index 7de3b456185..a6b0f220269 100644 --- a/BitKeeper/etc/logging_ok +++ b/BitKeeper/etc/logging_ok @@ -1,5 +1 @@ -monty@donna.mysql.com -mwagner@evoq.home.mwagner.org -sasha@mysql.sashanet.com -serg@serg.mysql.com -tim@threads.polyesthetic.msg +monty@tik.mysql.com diff --git a/sql-bench/test-insert.sh b/sql-bench/test-insert.sh index e6690b043f1..36a2b7761d5 100755 --- a/sql-bench/test-insert.sh +++ b/sql-bench/test-insert.sh @@ -270,7 +270,7 @@ for ($i=1 ; $i <= $small_loop_count ; $i++) { if (!$error++) { - print "Warning: Got $found_rows rows when selecting a hole table of " . ($total_rows) . " rows\nContact the database or DBD author!\n"; + print "Warning: Got $found_rows rows when selecting a whole table of " . ($total_rows) . " rows\nContact the database or DBD author!\n"; } } $count+=$found_rows; @@ -280,44 +280,125 @@ $end_time=new Benchmark; print "Time for select_big ($small_loop_count:$count): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; +# +# Do a lot of different ORDER BY queries +# + $loop_time=new Benchmark; -$estimated=0; -$rows=0; -$count=0; -for ($i=1 ; $i <= $small_loop_count/2 ; $i++) +$estimated=$rows=0; +for ($i=1 ; $i <= $small_loop_count ; $i++) { $rows+=fetch_all_rows($dbh,"select id from bench1 order by id",1); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i, + $small_loop_count)); +} +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for order_by_big_key ($small_loop_count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +$loop_time=new Benchmark; +$estimated=$rows=0; +for ($i=1 ; $i <= $small_loop_count ; $i++) +{ $rows+=fetch_all_rows($dbh,"select id from bench1 order by id desc",1); - $count+=2; $end_time=new Benchmark; - last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count, + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i, $small_loop_count)); } if ($estimated) { print "Estimated time"; } else { print "Time"; } -print " for order_by_key ($count:$rows): " . +print " for order_by_big_key_desc ($small_loop_count:$rows): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; $loop_time=new Benchmark; -$estimated=0; -$rows=0; -$count=0; -for ($i=1 ; $i <= $small_loop_count/2 ; $i++) +$estimated=$rows=0; +for ($i=1 ; $i <= $small_loop_count ; $i++) { - $rows+=fetch_all_rows($dbh,"select id2 from bench1 order by id2",1); - $rows+=fetch_all_rows($dbh,"select id2 from bench1 order by id2 desc",1); - $count+=2; + $rows+=fetch_all_rows($dbh,"select id3 from bench1 order by id3",1); $end_time=new Benchmark; - last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count, + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i, + $small_loop_count)); +} +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for order_by_big_key2 ($small_loop_count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +$loop_time=new Benchmark; +$estimated=$rows=0; +for ($i=1 ; $i <= $small_loop_count ; $i++) +{ + $rows+=fetch_all_rows($dbh,"select id2 from bench1 order by id3",1); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i, + $small_loop_count)); +} +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for order_by_big_key_diff ($small_loop_count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + +$loop_time=new Benchmark; +$estimated=$rows=0; +for ($i=1 ; $i <= $small_loop_count ; $i++) +{ + $rows+=fetch_all_rows($dbh,"select id from bench1 order by id2,id3",1); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i, + $small_loop_count)); +} +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for order_by_big ($small_loop_count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +$loop_time=new Benchmark; +$estimated=$rows=0; +for ($i=1 ; $i <= $small_loop_count ; $i++) +{ + $start=$opt_loop_count/$small_loop_count*$i; + $end=$start+$i; + $rows+=fetch_all_rows($dbh,"select dummy1 from bench1 where id>=$start and id <= $end order by id",1); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i, + $small_loop_count)); +} +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for order_by_key ($small_loop_count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +$loop_time=new Benchmark; +$estimated=$rows=0; +for ($i=1 ; $i <= $small_loop_count ; $i++) +{ + $start=$opt_loop_count/$small_loop_count*$i; + $end=$start+$small_loop_count; + $rows+=fetch_all_rows($dbh,"select id2 from bench1 where id3>=$start and id3 <= $end order by id3",1); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i, $small_loop_count)); } if ($estimated) { print "Estimated time"; } else { print "Time"; } -print " for order_by ($count:$rows): " . +print " for order_by_key2_diff ($small_loop_count:$rows): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; # diff --git a/sql/filesort.cc b/sql/filesort.cc index 6d6bbe00182..0cee107cbb8 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -148,7 +148,7 @@ ha_rows filesort(TABLE **table, SORT_FIELD *sortorder, uint s_length, else { table[0]->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);/* Get record-count */ - records=table[0]->file->records+EXTRA_RECORDS; + records=table[0]->file->estimate_number_of_rows(); selected_records_file= 0; } if (param.sort_length == param.ref_length && records > param.max_rows) @@ -168,8 +168,8 @@ ha_rows filesort(TABLE **table, SORT_FIELD *sortorder, uint s_length, memavl=sortbuff_size; while (memavl >= MIN_SORT_MEMORY) { - if ((records+1)*(param.sort_length+sizeof(char*))+sizeof(BUFFPEK)*10 < - (ulong) memavl) + if ((ulonglong) (records+1)*(param.sort_length+sizeof(char*))+sizeof(BUFFPEK)*10 < + (ulonglong) memavl) param.keys=(uint) records+1; else { @@ -382,6 +382,7 @@ static ha_rows find_all_keys(SORTPARAM *param, SQL_SELECT *select, } if (*killed) { + DBUG_PRINT("info",("Sort killed by user")); (void) file->extra(HA_EXTRA_NO_CACHE); file->rnd_end(); DBUG_RETURN(HA_POS_ERROR); /* purecov: inspected */ diff --git a/sql/ha_berkeley.cc b/sql/ha_berkeley.cc index 1239c7db7d3..13306397fe1 100644 --- a/sql/ha_berkeley.cc +++ b/sql/ha_berkeley.cc @@ -25,8 +25,9 @@ We will need an updated Berkeley DB version for this. - Killing threads that has got a 'deadlock' - SHOW TABLE STATUS should give more information about the table. - - Get a more accurate count of the number of rows. - We could store the found number of rows when the table is scanned. + - Get a more accurate count of the number of rows (estimate_number_of_rows()). + We could store the found number of rows when the table is scanned and + then increment the counter for each attempted write. - We will need a manager thread that calls flush_logs, removes old logs and makes checkpoints at given intervals. - When not using UPDATE IGNORE, don't make a sub transaction but abort @@ -42,7 +43,6 @@ - LOCK TABLES - CHAR keys - BLOBS - - delete from t1; */ @@ -1297,7 +1297,7 @@ void ha_berkeley::info(uint flag) DBUG_ENTER("info"); if (flag & HA_STATUS_VARIABLE) { - records = HA_BERKELEY_ROWS_IN_TABLE; // Just to get optimisations right + records = estimate_number_of_rows(); // Just to get optimisations right deleted = 0; } else if (flag & HA_STATUS_ERRKEY) @@ -1607,4 +1607,20 @@ void ha_berkeley::update_auto_primary_key() pthread_mutex_unlock(&share->mutex); } +/* + Return an estimated of the number of rows in the table. + Used when sorting to allocate buffers and by the optimizer. +*/ + +ha_rows ha_berkeley::estimate_number_of_rows() +{ + ulonglong max_ident; + if (!hidden_primary_key) + return INT_MAX32; + pthread_mutex_lock(&share->mutex); + max_ident=share->auto_ident+EXTRA_RECORDS; + pthread_mutex_unlock(&share->mutex); + return (ha_rows) min(max_ident,(ulonglong) INT_MAX32); +} + #endif /* HAVE_BERKELEY_DB */ diff --git a/sql/ha_berkeley.h b/sql/ha_berkeley.h index 84061ae09be..1d1de613ce0 100644 --- a/sql/ha_berkeley.h +++ b/sql/ha_berkeley.h @@ -91,7 +91,8 @@ class ha_berkeley: public handler uint max_keys() const { return MAX_KEY-1; } uint max_key_parts() const { return MAX_REF_PARTS; } uint max_key_length() const { return MAX_KEY_LENGTH; } - uint extra_rec_buf_length() { return BDB_HIDDEN_PRIMARY_KEY_LENGTH; } + uint extra_rec_buf_length() { return BDB_HIDDEN_PRIMARY_KEY_LENGTH; } + ha_rows estimate_number_of_rows(); bool fast_key_read() { return 1;} bool has_transactions() { return 1;} diff --git a/sql/handler.h b/sql/handler.h index b7872ead98a..93ce7fef03d 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -203,6 +203,7 @@ public: virtual bool fast_key_read() { return 0;} virtual bool has_transactions(){ return 0;} virtual uint extra_rec_buf_length() { return 0; } + virtual ha_rows estimate_number_of_rows() { return records+EXTRA_RECORDS; } virtual int index_init(uint idx) { active_index=idx; return 0;} virtual int index_end() {return 0; } diff --git a/sql/sql_class.h b/sql/sql_class.h index 4ba3ee87144..095dad09486 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -57,7 +57,7 @@ typedef struct st_master_info pthread_mutex_t lock; bool inited; - st_master_info():inited(0),pending(0) + st_master_info() :pending(0), inited(0) { host[0] = 0; user[0] = 0; password[0] = 0;} inline void inc_pending(ulonglong val) |