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
Triage: D5 (Feature request)

[24 Sep 2008 21:27] Dimitriy A
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
[24 Sep 2008 21:54] Miguel Solorzano
Thank you for the bug report.
[30 Sep 2008 12:42] Mattias Jonsson
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.
[16 Oct 2008 18:14] Bugs System
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
[23 Oct 2008 19:51] Bugs System
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]
[27 Oct 2008 17:59] Ingo Strüwing
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.
[26 Oct 2009 10:26] Mikael Ronström
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.
[12 Nov 2009 14:23] Mattias Jonsson
This is already pushed into 5.5
[12 Nov 2009 14:24] Mattias Jonsson
pushed as WL#4571.
[15 Nov 2009 13:45] Jon Stephens
(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.