Merge lp:~laurynas-biveinis/percona-server/ahi-partitions-5.6-5.5 into lp:percona-server/5.5

[Laurynas Biveinis]

Fix bug 1214449 (Adaptive hash index partitions not tested in MTR) by
creating a very basic partitioned AHI testcase.

This is done as a prerequisite for AHI partitions 5.6 port. No 5.6 MP is being submitted now, the testcase 5.6 merge will be submitted together with that port.

http://jenkins.percona.com/job/percona-server-5.5-param/831/

[Alexey Kopytov (akopytov)]

Looks good, except that the test does too much unnecessary stuff:

On Fri, Aug 23 2013 06:12:30 +0000, Laurynas Biveinis wrote:

> === added file 'Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions.test'
> --- Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions.test 1970-01-01 00:00:00 +0000
> +++ Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions.test 2013-08-23 06:11:41 +0000
> @@ -0,0 +1,72 @@
> +#
> +# Basic test for InnoDB adaptive hash index partitions.
> +# TODO: add another testcase that uses DEBUG_SYNC to check the partition locking
> +#
> +--source include/have_innodb.inc
> +
> +# Check setup
> +SELECT @@GLOBAL.innodb_adaptive_hash_index;
> +SELECT @@GLOBAL.innodb_adaptive_hash_index_partitions;
> +
> +FLUSH STATUS;

FLUSH STATUS has no effect on InnoDB status vars, so that’s redundant.

> +
> +SELECT VARIABLE_VALUE INTO @ahi_searches_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
> + WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_hash_searches';
> +
> +--disable_warnings
> +DROP TABLE IF EXISTS t1, t2, t3;
> +--enable_warnings

You don’t really need that. Please forget it, and stop copy-pasting from
other tests.

> +
> +# 6 index trees across 4 AHI partitions
> +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(200), INDEX b(b)) ENGINE=InnoDB;
> +CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(200), INDEX b(b)) ENGINE=InnoDB;
> +CREATE TABLE t3 (a INT PRIMARY KEY, b INT, c CHAR(200), INDEX b(b)) ENGINE=InnoDB;
> +
> +--echo Filling tables
> +--disable_query_log
> +let $i=4000;
> +while ($i)
> +{
> + eval INSERT INTO t1 VALUES ($i, $i, REPEAT("a", 200));
> + eval INSERT INTO t2 VALUES ($i, $i, REPEAT("a", 200));
> + eval INSERT INTO t3 VALUES ($i, $i, REPEAT("a", 200));
> + dec $i;
> +}

What’s the point in creating megabytes of data (and slowing down the
test considerably) just to query a few pages max later? I tried changing
4000 to 2 and the test still passes.

> +
> +--echo Querying
> +--disable_result_log
> +let $i=1000;
> +while ($i)
> +{
> + SELECT b FROM t1 WHERE a=1;
> + SELECT a FROM t1 WHERE b=1;
> + SELECT b FROM t2 WHERE a=2;
> + SELECT a FROM t2 WHERE b=2;
> + SELECT b FROM t3 WHERE a=3;
> + SELECT a FROM t3 WHERE b=3;
> + dec $i;
> +}

Same here. 200 iterations seems to be sufficient for AHI to be involved.

> +--enable_result_log
> +--enable_query_log
> +
> +# AHI hash tables should have some cells now
> +SELECT VARIABLE_VALUE > 0 AS should_be_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
> + WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_cells';
> +

This is a pointless check. AHI hash cells are static (they are allocated
on startup), so this value is constant throughout the test.

review: Needs Fixing

[Laurynas Biveinis (laurynas-biveinis)]

> > +--disable_warnings
> > +DROP TABLE IF EXISTS t1, t2, t3;
> > +--enable_warnings
>
> You don’t really need that. Please forget it, and stop copy-pasting from
> other tests.

Heh, OK. I don't copy-paste, this is something I type by heart in every testcase :)

> What’s the point in creating megabytes of data (and slowing down the
> test considerably) just to query a few pages max later? I tried changing
> 4000 to 2 and the test still passes.

So the problems I tried to avoid here and with the excessive querying below is that 1) AHI builds reluctantly on small index trees, hence I tried to create height 2 trees; 2) AHI builds reluctantly anyway when there are few queries, as it keeps some profitability counters.

So for example if we reduced the data to just a few rows and executed the query loop twice, then for some reason only one index tree (t1 primary index) gets AHI and other indexes do not. The testcase still passes because I didn't know how to write "all index trees use AHI".

Admittedly currently I am unable to quantify the "reluctantly" above to produce a precise testcase and couldn't allocate time for research it, so went for an overkill instead.

[Alexey Kopytov (akopytov)]

On Tue, 27 Aug 2013 09:20:21 -0000, Laurynas Biveinis wrote:
>>> +--disable_warnings
>>> +DROP TABLE IF EXISTS t1, t2, t3;
>>> +--enable_warnings
>>
>> You don’t really need that. Please forget it, and stop copy-pasting from
>> other tests.
>
> Heh, OK. I don't copy-paste, this is something I type by heart in every testcase :)
>

Yet it's being enforced in reviews ;)

>> What’s the point in creating megabytes of data (and slowing down the
>> test considerably) just to query a few pages max later? I tried changing
>> 4000 to 2 and the test still passes.
>
> So the problems I tried to avoid here and with the excessive querying below is that 1) AHI builds reluctantly on small index trees, hence I tried to create height 2 trees; 2) AHI builds reluctantly anyway when there are few queries, as it keeps some profitability counters.
>
> So for example if we reduced the data to just a few rows and executed the query loop twice, then for some reason only one index tree (t1 primary index) gets AHI and other indexes do not. The testcase still passes because I didn't know how to write "all index trees use AHI".
>
> Admittedly currently I am unable to quantify the "reluctantly" above to produce a precise testcase and couldn't allocate time for research it, so went for an overkill instead.
>

I did a little more testing and it appears that SELECTs in the test case
do not contribute anything at all to adaptive hash building. I.e.
everything is built on the INSERT stage if it's long enough.

However, that's easy to fix by creating a unique index on the b column,
i.e. replace range scans in queries like "SELECT a FROM t1 WHERE b=1;"
with index lookups on 'b'. In this case, adaptive hash indexes are built
on tables with just a few rows, and about 200 SELECT queries.

Another comment I've got after reviewing the 5.6 MP: since we went for
providing basic AHI tests for 5.5, why don't provide a sys_var test as
well, as done in 5.6 MP?

[Laurynas Biveinis (laurynas-biveinis)]

> >> What’s the point in creating megabytes of data (and slowing down the
> >> test considerably) just to query a few pages max later? I tried changing
> >> 4000 to 2 and the test still passes.
> >
> > So the problems I tried to avoid here and with the excessive querying below
> is that 1) AHI builds reluctantly on small index trees, hence I tried to
> create height 2 trees; 2) AHI builds reluctantly anyway when there are few
> queries, as it keeps some profitability counters.
> >
> > So for example if we reduced the data to just a few rows and executed the
> query loop twice, then for some reason only one index tree (t1 primary index)
> gets AHI and other indexes do not. The testcase still passes because I didn't
> know how to write "all index trees use AHI".
> >
> > Admittedly currently I am unable to quantify the "reluctantly" above to
> produce a precise testcase and couldn't allocate time for research it, so went
> for an overkill instead.
> >
>
> I did a little more testing and it appears that SELECTs in the test case
> do not contribute anything at all to adaptive hash building. I.e.
> everything is built on the INSERT stage if it's long enough.
>
> However, that's easy to fix by creating a unique index on the b column,
> i.e. replace range scans in queries like "SELECT a FROM t1 WHERE b=1;"
> with index lookups on 'b'. In this case, adaptive hash indexes are built
> on tables with just a few rows, and about 200 SELECT queries.

I tried making b indexes unique, but then was not sure how to adjust queries, i.e. why SELECT a FROM t1 WHERE b=1 is not an index lookup? Does it mean to use b as covering index, i.e. SELECT b FROM t1 WHERE b=1? But this one is too easy for the query optimizer to optimize away. I tried this and it did not appear to increase AHI. Since you said you did some testing, could you please share the queries?

Thanks,

[Alexey Kopytov (akopytov)]

On Tue, Aug 27 2013 14:51:23 +0000, Laurynas Biveinis wrote:

>>
>> I did a little more testing and it appears that SELECTs in the test case
>> do not contribute anything at all to adaptive hash building. I.e.
>> everything is built on the INSERT stage if it's long enough.
>>
>> However, that's easy to fix by creating a unique index on the b column,
>> i.e. replace range scans in queries like "SELECT a FROM t1 WHERE b=1;"
>> with index lookups on 'b'. In this case, adaptive hash indexes are built
>> on tables with just a few rows, and about 200 SELECT queries.
>
> I tried making b indexes unique, but then was not sure how to adjust
> queries, i.e. why SELECT a FROM t1 WHERE b=1 is not an index lookup?

It is an index lookup if there’s a unique index on b.

> Does it mean to use b as covering index, i.e. SELECT b FROM t1 WHERE
> b=1? But this one is too easy for the query optimizer to optimize
> away. I tried this and it did not appear to increase AHI. Since you
> said you did some testing, could you please share the queries?
>

It works with any columns in SELECT queries for me (whether it’s is ‘a’,
‘b’ or ‘*’). Example:

Tables:

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
CREATE TABLE t3 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;

Insert stage:

let $i=3;
while ($i)
{
        eval INSERT INTO t1 VALUES ($i, $i, REPEAT("a", 200));
        eval INSERT INTO t2 VALUES ($i, $i, REPEAT("a", 200));
        eval INSERT INTO t3 VALUES ($i, $i, REPEAT("a", 200));
        dec $i;
}

Querying:

let $i=200;
while ($i)
{
        SELECT a FROM t1 WHERE b=1;
        SELECT b FROM t1 WHERE a=1;
        SELECT a FROM t2 WHERE b=2;
        SELECT b FROM t2 WHERE a=2;
        SELECT a FROM t3 WHERE b=3;
        SELECT b FROM t3 WHERE a=3;
        dec $i;
}

Innodb_adaptive_hash_hash_searches:
before querying: 0,
after querying: 414

is_hashed pages in the buffer pool:
before querying: 0
after querying: 6

Debug log:

btr_search_build_page_hash_index(test/t1.b)
btr_search_build_page_hash_index(test/t1.PRIMARY)
btr_search_build_page_hash_index(test/t2.b)
btr_search_build_page_hash_index(test/t2.PRIMARY)
btr_search_build_page_hash_index(test/t3.b)
btr_search_build_page_hash_index(test/t3.PRIMARY)

=== added file 'Percona-Server/mysql-test/suite/innodb/r/percona_ahi_partitions.result'
--- Percona-Server/mysql-test/suite/innodb/r/percona_ahi_partitions.result	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/innodb/r/percona_ahi_partitions.result	2013-08-28 07:02:05 +0000
@@ -0,0 +1,27 @@
+SELECT @@GLOBAL.innodb_adaptive_hash_index;
+@@GLOBAL.innodb_adaptive_hash_index
+1
+SELECT @@GLOBAL.innodb_adaptive_hash_index_partitions;
+@@GLOBAL.innodb_adaptive_hash_index_partitions
+4
+SELECT VARIABLE_VALUE INTO @ahi_searches_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_hash_searches';
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
+CREATE TABLE t3 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
+Filling tables
+Querying
+SELECT VARIABLE_VALUE INTO @ahi_searches_2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_hash_searches';
+SELECT @ahi_searches_2 - @ahi_searches_1 > 0 AS should_be_1;
+should_be_1
+1
+SELECT COUNT(*) > 0 AS should_be_1 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+WHERE PAGE_STATE LIKE "MEMORY";
+should_be_1
+1
+SELECT COUNT(*) >= 6 AS should_be_1 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+WHERE IS_HASHED LIKE "YES";
+should_be_1
+1
+DROP TABLE t1, t2, t3;

=== added file 'Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions-master.opt'
--- Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions-master.opt	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions-master.opt	2013-08-28 07:02:05 +0000
@@ -0,0 +1,1 @@
+--innodb-adaptive-hash-index-partitions=4

=== added file 'Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions.test'
--- Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions.test	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/innodb/t/percona_ahi_partitions.test	2013-08-28 07:02:05 +0000
@@ -0,0 +1,62 @@
+#
+# Basic test for InnoDB adaptive hash index partitions.
+# TODO: add another testcase that uses DEBUG_SYNC to check the partition locking
+#
+--source include/have_innodb.inc
+
+# Check setup
+SELECT @@GLOBAL.innodb_adaptive_hash_index;
+SELECT @@GLOBAL.innodb_adaptive_hash_index_partitions;
+
+SELECT VARIABLE_VALUE INTO @ahi_searches_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+       WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_hash_searches';
+
+# 6 index trees across 4 AHI partitions
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
+CREATE TABLE t3 (a INT PRIMARY KEY, b INT, c CHAR(200), UNIQUE INDEX b(b)) ENGINE=InnoDB;
+
+--echo Filling tables
+--disable_query_log
+let $i=3;
+while ($i)
+{
+        eval INSERT INTO t1 VALUES ($i, $i, REPEAT("a", 200));
+        eval INSERT INTO t2 VALUES ($i, $i, REPEAT("a", 200));
+        eval INSERT INTO t3 VALUES ($i, $i, REPEAT("a", 200));
+        dec $i;
+}
+
+--echo Querying
+--disable_result_log
+let $i=200;
+while ($i)
+{
+        SELECT a FROM t1 WHERE a=1;
+        SELECT b FROM t1 WHERE b=1;
+        SELECT a FROM t2 WHERE a=2;
+        SELECT b FROM t2 WHERE b=2;
+        SELECT a FROM t3 WHERE a=3;
+        SELECT b FROM t3 WHERE b=3;
+        dec $i;
+}
+--enable_result_log
+--enable_query_log
+
+# TODO: test Innodb_adaptive_hash_heap_buffers too.  But it requires large AHI
+# to become non-zero?
+
+# AHI should have been used for queries, but the exact lower bound is hard to determine
+SELECT VARIABLE_VALUE INTO @ahi_searches_2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+       WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_hash_searches';
+SELECT @ahi_searches_2 - @ahi_searches_1 > 0 AS should_be_1;
+
+# Buffer pool must contain AHI pages now
+SELECT COUNT(*) > 0 AS should_be_1 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+       WHERE PAGE_STATE LIKE "MEMORY";
+
+# Buffer pool must contain no less than the number of index trees hashed pages now
+SELECT COUNT(*) >= 6 AS should_be_1 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+       WHERE IS_HASHED LIKE "YES";
+
+DROP TABLE t1, t2, t3;

=== modified file 'Percona-Server/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_partitions_basic.result'
--- Percona-Server/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_partitions_basic.result	2012-04-18 23:26:11 +0000
+++ Percona-Server/mysql-test/suite/sys_vars/r/innodb_adaptive_hash_index_partitions_basic.result	2013-08-28 07:02:05 +0000
@@ -1,3 +1,9 @@
-SELECT @@global.innodb_adaptive_hash_index_partitions;
-@@global.innodb_adaptive_hash_index_partitions
+SELECT @@GLOBAL.innodb_adaptive_hash_index_partitions;
+@@GLOBAL.innodb_adaptive_hash_index_partitions
 1
+SET @@GLOBAL.innodb_adaptive_hash_index_partitions=1;
+ERROR HY000: Variable 'innodb_adaptive_hash_index_partitions' is a read only variable
+SELECT @@LOCAL.innodb_adaptive_hash_index_partitions;
+ERROR HY000: Variable 'innodb_adaptive_hash_index_partitions' is a GLOBAL variable
+SELECT @@SESSION.innodb_adaptive_hash_index_partitions;
+ERROR HY000: Variable 'innodb_adaptive_hash_index_partitions' is a GLOBAL variable

=== modified file 'Percona-Server/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_partitions_basic.test'
--- Percona-Server/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_partitions_basic.test	2012-04-18 23:26:11 +0000
+++ Percona-Server/mysql-test/suite/sys_vars/t/innodb_adaptive_hash_index_partitions_basic.test	2013-08-28 07:02:05 +0000
@@ -1,1 +1,14 @@
-SELECT @@global.innodb_adaptive_hash_index_partitions;
+# A sys_vars suite test for innodb_adaptive_hash_index_partitions.
+
+--source include/have_innodb.inc
+
+SELECT @@GLOBAL.innodb_adaptive_hash_index_partitions;
+
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SET @@GLOBAL.innodb_adaptive_hash_index_partitions=1;
+
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SELECT @@LOCAL.innodb_adaptive_hash_index_partitions;
+
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SELECT @@SESSION.innodb_adaptive_hash_index_partitions;