MySQL Bugs: #39637: Support key caches for partitioned tables.

Bug #39637	Support key caches for partitioned tables.
Submitted:	24 Sep 2008 21:27	Modified:	15 Nov 2009 13:45
Reporter:	Dimitriy A	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	index buffer, index cache, key buffer, key cache, partition, partitioned table

Description:
Please add support for key caches for partitioned tables. Using partitioned tables would give us a big performance boost, but only if the key caches would work with partitioned tables as well.

How to repeat:
N/A

Suggested fix:
N/A

Thank you for the bug report.

Here is a test that shows that partitioned myisam does not support key cache (useing the latest 5.1 main tree):
(run with "./mtr --record --mysqld=--hot_cache.key_buffer_size=8M b39637"
CREATE TABLE t1 (
a INT,
PRIMARY KEY (a))
ENGINE = MyISAM
PARTITION BY HASH (a)
PARTITIONS 2;
CACHE INDEX t1 IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	note	The storage engine for the table doesn't support assign_to_keycache
ALTER TABLE t1 REMOVE PARTITIONING;
CACHE INDEX t1 IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK

Mikael Ronström just added a new work-log for extending the use of key caches per partition in WL#4571.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56405

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

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56939

2673 Mattias Jonsson	2008-10-23
      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.
      (Changed the syntax and added more tests since last commit)
      
      New syntax:
      CACHE INDEX keycache_list|keycache_parts IN key_cache_name
      
      keycache_list:
      tbl_name [INDEX|KEY (index_name[, index_name] ...)][, tbl_name ...]
      
      keycache_parts:
      tbl_name PARTITION (ALL|partition[, partition] ...)
      [INDEX|KEY (index_name[, index_name] ...)]
      
      
      LOAD INDEX INTO CACHE tbl_index_list|parts_index_list
      
      tbl_index_list:
      tbl_name [INDEX|KEY (index_name[, index_name] ...)]
      [INGORE LEAVES][, tbl_name ...]
      
      parts_index_list:
      tbl_name PARTITION (ALL|partition[, partition] ...)
      [INDEX|KEY (index_name[,index_name] ...)
      [IGNORE LEAVES]

Ok to push from me, provided that the triage team adds the target version and required tags, and architecs approve the syntax in WL#4571 (Assign Key Cache per partition). Also the target version in WL#4571 should be fixed before pushing.

There is a confusion here on key cache and query cache.
The patch here makes it possible to have separate
key caches per partition. It doesn't affect query
cache handling at all.

This patch does not attempt to resolve any issues with
the query cache.

This is already pushed into 5.5

pushed as WL#4571.

(a) The feature change this fix imparts appears for the first time in 5.5.0, I consider this in the "Doesn't appear in any release category";

(b) Docs work associated with the change is already covered under WL#4571 (which is my task to document);

Therefore, I am closing this bug without adding a changelog entry.