MySQL Bugs: #45353: Support for compression in partitions

Bug #45353	Support for compression in partitions
Submitted:	5 Jun 2009 14:23	Modified:	3 Aug 2009 9:17
Reporter:	Alan Griffiths	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I would like to request that compression, through myisampack, be supported for partitioned MyISAM tables such that I can pack some partitions and still write to others. It should also be possible to drop compressed partitions without dropping the whole table.

Thanks,

Alan

How to repeat:
Create a partitioned table, shutdown server, run myisampack/myisamchk on one partition. Restart server, attempt to insert into un-compressed partition, query fails with "ERROR 1030 (HY000): Got error 174 from storage engine".

Issuing drop partition also gives the same error.

Suggested fix:
Add full support for compressed MyISAM partitions.

I don't think this feature is in the roadmap, or that it is easily feasible.
In the meantime, can you use partitioned  ARCHIVE instead?

See this presentation (slide 84 and 85)
http://www.slideshare.net/datacharmer/boost-performance-with-mysql-51-partitions

In some instances I could get away with archive engine, but often a full table scan is too expensive for me. For example I have a partition with ~18 million rows, on MyISAM an average query returns in <1 second, but takes 9 seconds on ARCHIVE. Another solution would be to add greater indexing support to ARCHIVE.

Many thanks for writing a reasonable feature request.

The problem found is the difference in table_flags:
Packed myisam have HA_REC_NOT_IN_SEQ, HA_HAS_CHECKSUM
ordinary myisam have HA_CAN_GEOMETRY, HA_DUPLICATE_POS, HA_CAN_INSERT_DELAYED, HA_CAN_FULLTEXT, HA_CAN_SQL_HANDLER

and partitioning disables HA_CAN_GEOMETRY, HA_CAN_FULLTEXT, HA_DUPLICATE_POS, HA_CAN_SQL_HANDLER, HA_CAN_INSERT_DELAYED, HA_PRIMARY_KEY_REQUIRED_FOR_POSITION

and sets HA_FILE_BASED, HA_REC_NOT_IN_SEQ

Which leaves HA_HAS_CHECKSUM, which needs to be handled, so that it is only supported if ALL partitions support it, or possibly to take advantage of it on the partitions that support it.

(hint: since this bug is classified as 'feature request' test if checksums on partitioned myisam table really work, if not, report a bug with Category 'Partitioning' and we will probably disable that flag in the partitioning engine. And by disable that flag it seems to work for select, but it will make the whole table, all partitions, read only, since it will fail getting a write lock on that partition. So it will also need pruning before locking as in bug#37252.)

There is another bug open somewhere about being able to myisampack and unpack tables from within mysql rather than from outside.

Any compression support in partitions should be also possible _both_ on the shell and in SQL (ALTER TABLE ... command I guess).

Also would be good if individual partitions can be compressed/uncompressed individually as if storing periodic data it's likely that old data won't be changed and so space can be saved by compressing an "old" partition.