From 047963922c0c89c76f82cd14eb05ec56c19a91e9 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Fri, 23 Sep 2016 14:18:29 +0300 Subject: MDEV-9736: Window functions: multiple cursors to read filesort result Add support for having multiple IO_CACHEs with type=READ_CACHE to share the file they are reading from. Each IO_CACHE keeps its own in-memory buffer. When doing a read or seek operation on the file, it notifies other IO_CACHEs that the file position has been changed. Make Rowid_seq_cursor use cloned IO_CACHE when reading filesort result. --- mysql-test/r/win_big.result | 93 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/win_big.test | 104 ++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 197 insertions(+) create mode 100644 mysql-test/r/win_big.result create mode 100644 mysql-test/t/win_big.test (limited to 'mysql-test') diff --git a/mysql-test/r/win_big.result b/mysql-test/r/win_big.result new file mode 100644 index 00000000000..7ea044e702c --- /dev/null +++ b/mysql-test/r/win_big.result @@ -0,0 +1,93 @@ +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t10 (a int, b int, c int); +insert into t10 +select +A.a + 1000*B.a, +A.a + 1000*B.a, +A.a + 1000*B.a +from t1 A, t0 B +order by A.a+1000*B.a; +################################################################# +## Try a basic example +flush status; +create table t21 as +select +sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B +from +t10; +show status like 'Sort_merge_passes'; +Variable_name Value +Sort_merge_passes 0 +set sort_buffer_size=1024; +flush status; +create table t22 as +select +sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B +from +t10; +show status like 'Sort_merge_passes'; +Variable_name Value +Sort_merge_passes 35 +include/diff_tables.inc [t21, t22] +drop table t21, t22; +################################################################# +# Try many cursors +set sort_buffer_size=default; +flush status; +create table t21 as +select +sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1, +sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2, +sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3 +from +t10; +show status like 'Sort_merge_passes'; +Variable_name Value +Sort_merge_passes 0 +set sort_buffer_size=1024; +flush status; +create table t22 as +select +sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1, +sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2, +sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3 +from +t10; +show status like 'Sort_merge_passes'; +Variable_name Value +Sort_merge_passes 35 +include/diff_tables.inc [t21, t22] +drop table t21, t22; +################################################################# +# Try having cursors pointing at different IO_CACHE pages +# in the IO_CACHE +set sort_buffer_size=default; +flush status; +create table t21 as +select +a, +sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1 +from +t10; +show status like 'Sort_merge_passes'; +Variable_name Value +Sort_merge_passes 0 +set sort_buffer_size=1024; +flush status; +create table t22 as +select +a, +sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1 +from +t10; +show status like 'Sort_merge_passes'; +Variable_name Value +Sort_merge_passes 35 +include/diff_tables.inc [t21, t22] +drop table t21, t22; +################################################################# +drop table t10; +drop table t0,t1; diff --git a/mysql-test/t/win_big.test b/mysql-test/t/win_big.test new file mode 100644 index 00000000000..a0398126eb3 --- /dev/null +++ b/mysql-test/t/win_big.test @@ -0,0 +1,104 @@ +# +# Tests for window functions over big datasets. +# "Big" here is "big enough so that filesort result doesn't fit in a +# memory buffer". +# +# + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + +create table t10 (a int, b int, c int); +insert into t10 +select + A.a + 1000*B.a, + A.a + 1000*B.a, + A.a + 1000*B.a +from t1 A, t0 B +order by A.a+1000*B.a; + +--echo ################################################################# +--echo ## Try a basic example +flush status; +create table t21 as +select + sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B +from + t10; +show status like 'Sort_merge_passes'; + +set sort_buffer_size=1024; +flush status; +create table t22 as +select + sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B +from + t10; +show status like 'Sort_merge_passes'; + +let $diff_tables= t21, t22; +source include/diff_tables.inc; +drop table t21, t22; + +--echo ################################################################# +--echo # Try many cursors +set sort_buffer_size=default; +flush status; +create table t21 as +select + sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1, + sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2, + sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3 +from + t10; +show status like 'Sort_merge_passes'; + +set sort_buffer_size=1024; +flush status; +create table t22 as +select + sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1, + sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2, + sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3 +from + t10; +show status like 'Sort_merge_passes'; + +let $diff_tables= t21, t22; +source include/diff_tables.inc; +drop table t21, t22; + +--echo ################################################################# +--echo # Try having cursors pointing at different IO_CACHE pages +--echo # in the IO_CACHE +set sort_buffer_size=default; +flush status; +create table t21 as +select + a, + sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1 +from + t10; +show status like 'Sort_merge_passes'; + +set sort_buffer_size=1024; +flush status; +create table t22 as +select + a, + sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1 +from + t10; +show status like 'Sort_merge_passes'; + +let $diff_tables= t21, t22; +source include/diff_tables.inc; +drop table t21, t22; +--echo ################################################################# + +drop table t10; +drop table t0,t1; + -- cgit v1.2.1