#At file:///Users/mattiasj/clones/bzrroot/b39637-51-bugteam/
2673 Mattias Jonsson 2008-10-16
Bug#39637: Support key cache in partitioned myisam tables
(builds on patch for bug-39434)
Added the necessary handler calls to the partitioning handler
and new syntax for managing key caches on partition level.
New syntax:
CACHE PARTITIONED INDEX tbl_name
[INDEX|KEY (index_name[, index_name] ...)]
PARTITIONS ALL|partition_list IN key_cache_name
LOAD PARTITIONED INDEX INTO CACHE tbl_name
[INDEX|KEY (index_name[, index_name] ...)] [IGNORE LEAVES]
PARTITIONS ALL|partition_list
added:
mysql-test/r/partition_key_cache.result
mysql-test/t/partition_key_cache.test
modified:
sql/ha_partition.cc
sql/ha_partition.h
sql/lex.h
sql/sql_partition.cc
sql/sql_table.cc
sql/sql_yacc.yy
per-file messages:
mysql-test/r/partition_key_cache.result
Bug#39637: Support key cache in partitioned myisam tables
New test result file
mysql-test/t/partition_key_cache.test
Bug#39637: Support key cache in partitioned myisam tables
New test case file
sql/ha_partition.cc
Bug#39637: Support key cache in partitioned myisam tables
Added support for preload_keys and assign_to_keycache
sql/ha_partition.h
Bug#39637: Support key cache in partitioned myisam tables
Added support for preload_keys and assign_to_keycache
sql/lex.h
Bug#39637: Support key cache in partitioned myisam tables
Added support for preload_keys and assign_to_keycache by
adding a new keyword 'PARTITIONED'
sql/sql_partition.cc
Bug#39637: Support key cache in partitioned myisam tables
Just updated the comment, since it it now also is used
when assign_to_keycache and preload_keys
sql/sql_table.cc
Bug#39637: Support key cache in partitioned myisam tables
Just updated the comment, since it it now also is used
when assign_to_keycache and preload_keys
sql/sql_yacc.yy
Bug#39637: Support key cache in partitioned myisam tables
Added syntax for key cache on partitions.
restructured the check if partitioning is included.
removed a duplicate lex->alter_info.reset()
=== added file 'mysql-test/r/partition_key_cache.result'
--- a/mysql-test/r/partition_key_cache.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/partition_key_cache.result 2008-10-16 18:14:19 +0000
@@ -0,0 +1,249 @@
+DROP TABLE IF EXISTS t1, t2, v, x;
+SET GLOBAL hot_cache.key_buffer_size = 1024*1024;
+SET GLOBAL warm_cache.key_buffer_size = 1024*1024;
+SET @@global.cold_cache.key_buffer_size = 1024*1024;
+SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d;
+a b c d
+1048576 1024 300 100
+SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d;
+a b c d
+1048576 1024 300 100
+SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d;
+a b c d
+1048576 1024 300 100
+SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d;
+a b c d
+1048576 1024 300 100
+CREATE TABLE t1 (
+a INT,
+b VARCHAR(257),
+c INT NOT NULL,
+PRIMARY KEY (a),
+KEY `inx_b` (b),
+KEY `inx_c`(c))
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH (a)
+(PARTITION p0 VALUES LESS THAN (10)
+(SUBPARTITION sp0,
+SUBPARTITION sp1),
+PARTITION p1 VALUES LESS THAN MAXVALUE
+(SUBPARTITION sp2,
+SUBPARTITION sp3));
+CREATE TABLE t2 (
+a INT,
+b VARCHAR(257),
+c INT NOT NULL,
+PRIMARY KEY (a),
+KEY `inx_b` (b),
+KEY `inx_c`(c));
+SET @a:=1167602400;
+CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4;
+CREATE VIEW x AS SELECT 1 FROM v,v a,v b;
+INSERT t1 SELECT @a, CONCAT('X_', @a, ' MySQL'), 1167612400 - (@a:=@a+1) FROM x, x a;
+DROP VIEW x;
+DROP VIEW v;
+INSERT t2 SELECT a, b, c FROM t1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+4096
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+4096
+FLUSH TABLES;
+# Restrict partitioned commands to partitioned tables only
+CACHE PARTITIONED INDEX t2 KEY (`inx_b`) PARTITIONS p0 IN hot_cache;
+ERROR HY000: Partition management on a not partitioned table is not possible
+CACHE PARTITIONED INDEX t2 INDEX (`PRIMARY`) PARTITIONS p0,p1 IN hot_cache;
+ERROR HY000: Partition management on a not partitioned table is not possible
+CACHE PARTITIONED INDEX t2 INDEX (`PRIMARY`,`inx_b`) PARTITIONS p1 IN hot_cache;
+ERROR HY000: Partition management on a not partitioned table is not possible
+CACHE PARTITIONED INDEX t2 KEY (`inx_b`,`PRIMARY`) PARTITIONS ALL IN hot_cache;
+ERROR HY000: Partition management on a not partitioned table is not possible
+# Basic key cache testing
+CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+CACHE PARTITIONED INDEX t1 KEY (`inx_b`) PARTITIONS p0 IN cold_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE PARTITIONED INDEX t1 INDEX (`PRIMARY`) PARTITIONS p0,p1 IN warm_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE PARTITIONED INDEX t1 INDEX (`PRIMARY`,`inx_b`) PARTITIONS p1 IN hot_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE PARTITIONED INDEX t1 KEY (`inx_b`,`PRIMARY`) PARTITIONS ALL IN default;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE PARTITIONED INDEX t1 PARTITIONS ALL IN hot_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE INDEX t1 INDEX (`inx_b`) IN default;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+CACHE INDEX t1 IN hot_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+# Test of non existent key cache:
+CACHE INDEX t1 IN non_existent_key_cache;
+ERROR HY000: Unknown key cache 'non_existent_key_cache'
+# Basic testing of LOAD INDEX
+LOAD INDEX INTO CACHE t2;
+Table Op Msg_type Msg_text
+test.t2 preload_keys status OK
+# PRIMARY and secondary keys have different block sizes
+LOAD INDEX INTO CACHE t2 ignore leaves;
+Table Op Msg_type Msg_text
+test.t2 preload_keys error Indexes use different block sizes
+test.t2 preload_keys status Operation failed
+# Must have INDEX or KEY before the index list
+LOAD INDEX INTO CACHE t2 (`PRIMARY`);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`PRIMARY`)' at line 1
+# Test of IGNORE LEAVES
+LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`);
+Table Op Msg_type Msg_text
+test.t2 preload_keys status OK
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t2 preload_keys error Indexes use different block sizes
+test.t2 preload_keys status Operation failed
+CACHE INDEX t2 IN warm_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+CACHE INDEX t1 IN cold_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache status OK
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t2 preload_keys error Indexes use different block sizes
+test.t2 preload_keys status Operation failed
+CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t2 preload_keys error Indexes use different block sizes
+test.t2 preload_keys status Operation failed
+CACHE INDEX t2 IN warm_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t2 preload_keys error Indexes use different block sizes
+test.t2 preload_keys status Operation failed
+CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`);
+Table Op Msg_type Msg_text
+test.t2 preload_keys status OK
+CACHE INDEX t2 IN default;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache status OK
+LOAD INDEX INTO CACHE t2 IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t2 preload_keys error Indexes use different block sizes
+test.t2 preload_keys status Operation failed
+LOAD PARTITIONED INDEX INTO CACHE t2 INDEX (`PRIMARY`) PARTITIONS p1;
+ERROR HY000: Partition management on a not partitioned table is not possible
+LOAD INDEX INTO CACHE t1, t2;
+Table Op Msg_type Msg_text
+test.t1 preload_keys status OK
+test.t2 preload_keys status OK
+LOAD INDEX INTO CACHE t1 ignore leaves;
+Table Op Msg_type Msg_text
+test.t1 preload_keys error Indexes use different block sizes
+test.t1 preload_keys error Subpartition sp2 returned error
+test.t1 preload_keys status Operation failed
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`);
+Table Op Msg_type Msg_text
+test.t1 preload_keys status OK
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t1 preload_keys error Indexes use different block sizes
+test.t1 preload_keys error Subpartition sp2 returned error
+test.t1 preload_keys status Operation failed
+LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t1 preload_keys error Indexes use different block sizes
+test.t1 preload_keys error Subpartition sp2 returned error
+test.t1 preload_keys status Operation failed
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES;
+Table Op Msg_type Msg_text
+test.t1 preload_keys error Indexes use different block sizes
+test.t1 preload_keys error Subpartition sp2 returned error
+test.t1 preload_keys status Operation failed
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`);
+Table Op Msg_type Msg_text
+test.t1 preload_keys status OK
+LOAD PARTITIONED INDEX INTO CACHE t1 INDEX (`PRIMARY`) PARTITIONS p1;
+Table Op Msg_type Msg_text
+test.t1 preload_keys status OK
+LOAD PARTITIONED INDEX INTO CACHE t1 KEY (`PRIMARY`) IGNORE LEAVES PARTITIONS p1,p0;
+Table Op Msg_type Msg_text
+test.t1 preload_keys error Indexes use different block sizes
+test.t1 preload_keys error Subpartition sp2 returned error
+test.t1 preload_keys status Operation failed
+LOAD PARTITIONED INDEX INTO CACHE t1 PARTITIONS ALL;
+Table Op Msg_type Msg_text
+test.t1 preload_keys status OK
+LOAD PARTITIONED INDEX INTO CACHE t1 IGNORE LEAVES PARTITIONS p1,p0;
+Table Op Msg_type Msg_text
+test.t1 preload_keys error Indexes use different block sizes
+test.t1 preload_keys error Subpartition sp2 returned error
+test.t1 preload_keys status Operation failed
+DROP INDEX `inx_b` on t1;
+DROP INDEX `inx_b` on t2;
+CACHE PARTITIONED INDEX t2 KEY (`inx_b`) PARTITIONS p0 IN hot_cache;
+ERROR HY000: Partition management on a not partitioned table is not possible
+CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
+Table Op Msg_type Msg_text
+test.t2 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't2'
+test.t2 assign_to_keycache status Operation failed
+CACHE PARTITIONED INDEX t1 KEY (`inx_b`) PARTITIONS p0 IN hot_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache error Subpartition sp0 returned error
+test.t1 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't1'
+test.t1 assign_to_keycache status Operation failed
+CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache;
+Table Op Msg_type Msg_text
+test.t1 assign_to_keycache error Subpartition sp0 returned error
+test.t1 assign_to_keycache Error Key 'inx_b' doesn't exist in table 't1'
+test.t1 assign_to_keycache status Operation failed
+DROP TABLE t1,t2;
+SET GLOBAL hot_cache.key_buffer_size = 0;
+SET GLOBAL warm_cache.key_buffer_size = 0;
+SET @@global.cold_cache.key_buffer_size = 0;
+SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d;
+a b c d
+1048576 1024 300 100
+SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d;
+a b c d
+0 1024 300 100
+SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d;
+a b c d
+0 1024 300 100
+SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d;
+a b c d
+0 1024 300 100
=== added file 'mysql-test/t/partition_key_cache.test'
--- a/mysql-test/t/partition_key_cache.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/partition_key_cache.test 2008-10-16 18:14:19 +0000
@@ -0,0 +1,126 @@
+# Test of key cache with partitions
+--source include/have_partition.inc
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, v, x;
+--enable_warnings
+SET GLOBAL hot_cache.key_buffer_size = 1024*1024;
+SET GLOBAL warm_cache.key_buffer_size = 1024*1024;
+SET @@global.cold_cache.key_buffer_size = 1024*1024;
+SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d;
+SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d;
+SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d;
+SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d;
+CREATE TABLE t1 (
+ a INT,
+ b VARCHAR(257),
+ c INT NOT NULL,
+ PRIMARY KEY (a),
+ KEY `inx_b` (b),
+ KEY `inx_c`(c))
+PARTITION BY RANGE (a)
+SUBPARTITION BY HASH (a)
+(PARTITION p0 VALUES LESS THAN (10)
+ (SUBPARTITION sp0,
+ SUBPARTITION sp1),
+ PARTITION p1 VALUES LESS THAN MAXVALUE
+ (SUBPARTITION sp2,
+ SUBPARTITION sp3));
+CREATE TABLE t2 (
+ a INT,
+ b VARCHAR(257),
+ c INT NOT NULL,
+ PRIMARY KEY (a),
+ KEY `inx_b` (b),
+ KEY `inx_c`(c));
+SET @a:=1167602400;
+# Genereate 4096 rows. Idea from:
+# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html
+CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4;
+CREATE VIEW x AS SELECT 1 FROM v,v a,v b;
+INSERT t1 SELECT @a, CONCAT('X_', @a, ' MySQL'), 1167612400 - (@a:=@a+1) FROM x, x a;
+DROP VIEW x;
+DROP VIEW v;
+INSERT t2 SELECT a, b, c FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+FLUSH TABLES;
+
+--echo # Restrict partitioned commands to partitioned tables only
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE PARTITIONED INDEX t2 KEY (`inx_b`) PARTITIONS p0 IN hot_cache;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE PARTITIONED INDEX t2 INDEX (`PRIMARY`) PARTITIONS p0,p1 IN hot_cache;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE PARTITIONED INDEX t2 INDEX (`PRIMARY`,`inx_b`) PARTITIONS p1 IN hot_cache;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE PARTITIONED INDEX t2 KEY (`inx_b`,`PRIMARY`) PARTITIONS ALL IN hot_cache;
+--echo # Basic key cache testing
+CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
+CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache;
+CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache;
+CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default;
+CACHE PARTITIONED INDEX t1 KEY (`inx_b`) PARTITIONS p0 IN cold_cache;
+CACHE PARTITIONED INDEX t1 INDEX (`PRIMARY`) PARTITIONS p0,p1 IN warm_cache;
+CACHE PARTITIONED INDEX t1 INDEX (`PRIMARY`,`inx_b`) PARTITIONS p1 IN hot_cache;
+CACHE PARTITIONED INDEX t1 KEY (`inx_b`,`PRIMARY`) PARTITIONS ALL IN default;
+CACHE PARTITIONED INDEX t1 PARTITIONS ALL IN hot_cache;
+CACHE INDEX t1 INDEX (`inx_b`) IN default;
+CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache;
+CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache;
+CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache;
+CACHE INDEX t1 IN hot_cache;
+--echo # Test of non existent key cache:
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 IN non_existent_key_cache;
+--echo # Basic testing of LOAD INDEX
+LOAD INDEX INTO CACHE t2;
+--echo # PRIMARY and secondary keys have different block sizes
+LOAD INDEX INTO CACHE t2 ignore leaves;
+--echo # Must have INDEX or KEY before the index list
+--error ER_PARSE_ERROR
+LOAD INDEX INTO CACHE t2 (`PRIMARY`);
+
+--echo # Test of IGNORE LEAVES
+LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`);
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES;
+CACHE INDEX t2 IN warm_cache;
+CACHE INDEX t1 IN cold_cache;
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES;
+CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache;
+LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES;
+CACHE INDEX t2 IN warm_cache;
+CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache;
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES;
+CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default;
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`);
+CACHE INDEX t2 IN default;
+LOAD INDEX INTO CACHE t2 IGNORE LEAVES;
+
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+LOAD PARTITIONED INDEX INTO CACHE t2 INDEX (`PRIMARY`) PARTITIONS p1;
+LOAD INDEX INTO CACHE t1, t2;
+LOAD INDEX INTO CACHE t1 ignore leaves;
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`);
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`);
+LOAD PARTITIONED INDEX INTO CACHE t1 INDEX (`PRIMARY`) PARTITIONS p1;
+LOAD PARTITIONED INDEX INTO CACHE t1 KEY (`PRIMARY`) IGNORE LEAVES PARTITIONS p1,p0;
+LOAD PARTITIONED INDEX INTO CACHE t1 PARTITIONS ALL;
+LOAD PARTITIONED INDEX INTO CACHE t1 IGNORE LEAVES PARTITIONS p1,p0;
+DROP INDEX `inx_b` on t1;
+DROP INDEX `inx_b` on t2;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE PARTITIONED INDEX t2 KEY (`inx_b`) PARTITIONS p0 IN hot_cache;
+CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
+CACHE PARTITIONED INDEX t1 KEY (`inx_b`) PARTITIONS p0 IN hot_cache;
+CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache;
+DROP TABLE t1,t2;
+SET GLOBAL hot_cache.key_buffer_size = 0;
+SET GLOBAL warm_cache.key_buffer_size = 0;
+SET @@global.cold_cache.key_buffer_size = 0;
+SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d;
+SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d;
+SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d;
+SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d;
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2008-10-10 18:12:38 +0000
+++ b/sql/ha_partition.cc 2008-10-16 18:14:19 +0000
@@ -855,9 +855,12 @@ int ha_partition::rename_partitions(cons
#define ANALYZE_PARTS 2
#define CHECK_PARTS 3
#define REPAIR_PARTS 4
+#define ASSIGN_KEYCACHE_PARTS 5
+#define PRELOAD_KEYS_PARTS 6
static const char *opt_op_name[]= {NULL,
- "optimize", "analyze", "check", "repair" };
+ "optimize", "analyze", "check", "repair",
+ "assign_to_keycache", "preload_keys"};
/*
Optimize table
@@ -942,7 +945,44 @@ int ha_partition::repair(THD *thd, HA_CH
DBUG_RETURN(handle_opt_partitions(thd, check_opt, REPAIR_PARTS));
}
+/**
+ Assign to keycache
+
+ @param thd Thread object
+ @param check_opt Check/analyze/repair/optimize options
+
+ @return
+ @retval >0 Error
+ @retval 0 Success
+*/
+
+int ha_partition::assign_to_keycache(THD *thd, HA_CHECK_OPT *check_opt)
+{
+ DBUG_ENTER("ha_partition::assign_to_keycache");
+
+ DBUG_RETURN(handle_opt_partitions(thd, check_opt, ASSIGN_KEYCACHE_PARTS));
+}
+
+/**
+ Preload to keycache
+
+ @param thd Thread object
+ @param check_opt Check/analyze/repair/optimize options
+
+ @return
+ @retval >0 Error
+ @retval 0 Success
+*/
+
+int ha_partition::preload_keys(THD *thd, HA_CHECK_OPT *check_opt)
+{
+ DBUG_ENTER("ha_partition::preload_keys");
+
+ DBUG_RETURN(handle_opt_partitions(thd, check_opt, PRELOAD_KEYS_PARTS));
+}
+
+
/*
Handle optimize/analyze/check/repair of one partition
@@ -973,6 +1013,10 @@ static int handle_opt_part(THD *thd, HA_
error= file->ha_check(thd, check_opt);
else if (flag == REPAIR_PARTS)
error= file->ha_repair(thd, check_opt);
+ else if (flag == ASSIGN_KEYCACHE_PARTS)
+ error= file->assign_to_keycache(thd, check_opt);
+ else if (flag == PRELOAD_KEYS_PARTS)
+ error= file->preload_keys(thd, check_opt);
else
{
DBUG_ASSERT(FALSE);
=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h 2008-10-10 18:12:38 +0000
+++ b/sql/ha_partition.h 2008-10-16 18:14:19 +0000
@@ -998,12 +998,13 @@ public:
virtual int backup(TD* thd, HA_CHECK_OPT *check_opt);
virtual int restore(THD* thd, HA_CHECK_OPT *check_opt);
- virtual int assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt);
- virtual int preload_keys(THD *thd, HA_CHECK_OPT *check_opt);
virtual int dump(THD* thd, int fd = -1);
virtual int net_read_dump(NET* net);
virtual uint checksum() const;
*/
+ /* Enabled keycache for performance reasons, WL#4571 */
+ virtual int assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt);
+ virtual int preload_keys(THD* thd, HA_CHECK_OPT* check_opt);
/*
-------------------------------------------------------------------------
=== modified file 'sql/lex.h'
--- a/sql/lex.h 2008-02-12 09:43:38 +0000
+++ b/sql/lex.h 2008-10-16 18:14:19 +0000
@@ -390,6 +390,7 @@ static SYMBOL symbols[] = {
{ "PAGE_CHECKSUM", SYM(PAGE_CHECKSUM_SYM)},
{ "PARTIAL", SYM(PARTIAL)},
{ "PARTITION", SYM(PARTITION_SYM)},
+ { "PARTITIONED", SYM(PARTITIONED_SYM)},
{ "PARTITIONING", SYM(PARTITIONING_SYM)},
{ "PARTITIONS", SYM(PARTITIONS_SYM)},
{ "PASSWORD", SYM(PASSWORD)},
=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc 2008-10-10 18:12:38 +0000
+++ b/sql/sql_partition.cc 2008-10-16 18:14:19 +0000
@@ -4131,7 +4131,7 @@ uint set_part_state(Alter_info *alter_in
/*
Mark the partition.
I.e mark the partition as a partition to be "changed" by
- analyzing/optimizing/rebuilding/checking/repairing
+ analyzing/optimizing/rebuilding/checking/repairing/...
*/
no_parts_found++;
part_elem->part_state= part_state;
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2008-10-16 15:14:23 +0000
+++ b/sql/sql_table.cc 2008-10-16 18:14:19 +0000
@@ -4203,6 +4203,7 @@ static bool mysql_admin_table(THD* thd,
/*
Set up which partitions that should be processed
if ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION ..
+ CACHE PARTITIONED INDEX/LOAD PARTITIONED INDEX
*/
Alter_info *alter_info= &lex->alter_info;
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2008-10-10 18:12:38 +0000
+++ b/sql/sql_yacc.yy 2008-10-16 18:14:19 +0000
@@ -889,6 +889,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
%token PARTIAL /* SQL-2003-N */
%token PARTITIONING_SYM
%token PARTITIONS_SYM
+%token PARTITIONED_SYM
%token PARTITION_SYM /* SQL-2003-R */
%token PASSWORD
%token PHASE_SYM
@@ -3743,17 +3744,9 @@ opt_partitioning:
;
partitioning:
- PARTITION_SYM
+ PARTITION_SYM have_partitioning
{
-#ifdef WITH_PARTITION_STORAGE_ENGINE
LEX *lex= Lex;
- LEX_STRING partition_name={C_STRING_WITH_LEN("partition")};
- if (!plugin_is_ready(&partition_name, MYSQL_STORAGE_ENGINE_PLUGIN))
- {
- my_error(ER_FEATURE_DISABLED, MYF(0),
- "partitioning", "--with-partition");
- MYSQL_YYABORT;
- }
lex->part_info= new partition_info();
if (!lex->part_info)
{
@@ -3764,14 +3757,27 @@ partitioning:
{
lex->alter_info.flags|= ALTER_PARTITION;
}
+ }
+ partition
+ ;
+
+have_partitioning:
+ /* empty */
+ {
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ LEX_STRING partition_name={C_STRING_WITH_LEN("partition")};
+ if (!plugin_is_ready(&partition_name, MYSQL_STORAGE_ENGINE_PLUGIN))
+ {
+ my_error(ER_FEATURE_DISABLED, MYF(0),
+ "partitioning", "--with-partition");
+ MYSQL_YYABORT;
+ }
#else
my_error(ER_FEATURE_DISABLED, MYF(0),
"partitioning", "--with-partition");
MYSQL_YYABORT;
#endif
-
}
- partition
;
partition_entry:
@@ -5423,7 +5429,6 @@ alter:
if (!lex->select_lex.add_table_to_list(thd, $4, NULL,
TL_OPTION_UPDATING))
MYSQL_YYABORT;
- lex->alter_info.reset();
lex->col_list.empty();
lex->select_lex.init_order();
lex->select_lex.db=
@@ -6287,6 +6292,22 @@ keycache:
LEX *lex=Lex;
lex->sql_command= SQLCOM_ASSIGN_TO_KEYCACHE;
lex->ident= $5;
+ lex->alter_info.reset();
+ }
+ | CACHE_SYM PARTITIONED_SYM have_partitioning INDEX_SYM
+ {
+ Lex->alter_info.reset();
+ }
+ table_ident cache_keys_spec
+ PARTITIONS_SYM all_or_alt_part_name_list IN_SYM key_cache_name
+ {
+ LEX *lex= Lex;
+ if (!Select->add_table_to_list(YYTHD, $6, NULL, 0, TL_READ,
+ Select->pop_index_hints()))
+ MYSQL_YYABORT;
+ lex->sql_command= SQLCOM_ASSIGN_TO_KEYCACHE;
+ lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
+ lex->ident= $11;
}
;
@@ -6314,9 +6335,24 @@ preload:
{
LEX *lex=Lex;
lex->sql_command=SQLCOM_PRELOAD_KEYS;
+ lex->alter_info.reset();
}
preload_list
{}
+ | LOAD PARTITIONED_SYM have_partitioning INDEX_SYM INTO CACHE_SYM
+ {
+ Lex->alter_info.reset();
+ }
+ table_ident cache_keys_spec opt_ignore_leaves PARTITIONS_SYM
+ all_or_alt_part_name_list
+ {
+ LEX *lex= Lex;
+ if (!Select->add_table_to_list(YYTHD, $8, NULL, $10, TL_READ,
+ Select->pop_index_hints()))
+ MYSQL_YYABORT;
+ lex->sql_command= SQLCOM_PRELOAD_KEYS;
+ lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
+ }
;
preload_list:
