MyISAM packed tables is uncompressed when index is added.
Hi, The question: Is uncompressing a myisam table when adding an index the expected behavior? Here is the story: I have a very big table so I thought to compress it. I used myisampack which worked great. The table.MYD shrank in size. myisamchk -ddv shows the table as compressed. I noticed the documentation that urges to run: myisamchk -rq after myisampack and I thought: Maybe I shouldn't create any indices on the uncompressed table since myisamchk will redo the work. But if I try to add an index to the compressed table suddenly it gets expanded to its original size and myisamchk -ddv does report the table a uncompressed. So the only solution is to: 1. add the indices in the uncompressed table 2. compress the table 3. run myisamchk -rq [--sort-index --analyze --parallel-recover --tmpdir=/somewhere/with/a/lot/of/space] 4. mysqladmin flush-tables; But this duplicates the index creations step. So is this the expected behavior? * If yes: why? Expanding the table behind the user's back is surprising... * If no: Should I report it as a bug? Here is how to replicate: #DROP TABLE IF EXISTS txxx; CREATE TABLE txxx (id INT, value TEXT); INSERT INTO txxx SELECT 1, REPEAT('A', 65536); *# Let's go to shell *ls -ltr txxx.* -rw-rw---- 1 mysql mysql 466 Jun 30 19:34 txxx.frm -rw-rw---- 1 mysql mysql 1024 Jun 30 19:40 txxx.MYI -rw-rw---- 1 mysql mysql *65548* Jun 30 19:40 txxx.MYD <-- 64K cool *# cool! let's compress *myisampack -f txxx Compressing txxx.MYD: (1 records) - Calculating statistics - Compressing file 87.42% ls -ltr txxx.* -rw-rw---- 1 mysql mysql 466 Jun 30 19:34 txxx.frm -rw-rw---- 1 mysql mysql *8255* Jun 30 19:40 txxx.MYD <-- 8K cool -rw-rw---- 1 mysql mysql 1024 Jun 30 19:41 txxx.MYI # great it is compressed *# because documentation says so: *myisamchk -qr txxx - check record delete-chain - recovering (with keycache) MyISAM-table 'txxx' Data records: 1 ls -ltr txxx.* -rw-rw---- 1 mysql mysql 466 Jun 30 19:34 txxx.frm -rw-rw---- 1 mysql mysql *8255* Jun 30 19:40 txxx.MYD <-- Still compressed -rw-rw---- 1 mysql mysql 1024 Jun 30 19:43 txxx.MYI myisamchk -ddv txxx MyISAM file: txxx Record format: *Compressed* <-------------------------------------!!!! OK Character set: utf8mb3_general_ci (33) File-version: 1 Creation time: 2024-06-30 19:40:36 Recover time: 2024-06-30 19:43:52 Status: checked Checksum: 2613455662 Data records: 1 Deleted blocks: 0 Datafile parts: 1 Deleted data: 0 Datafile pointer (bytes): 6 Keyfile pointer (bytes): 3 Datafile length: 8248 Keyfile length: 1024 Max datafile length: 281474976710654 Max keyfile length: 17179868159 Recordlength: 15 table description: Key Start Len Index Type Rec/key Root Blocksize ro *# Now Let's add the index* ALTER TABLE txxx ADD INDEX(id); *# Shell again *ls -ltr txxx.* -rw-rw---- 1 mysql mysql 958 Jun 30 19:47 txxx.frm -rw-rw---- 1 mysql mysql *65548* Jun 30 19:47 txxx.MYD <--- *Oups - now it is uncompressed* -rw-rw---- 1 mysql mysql 2048 Jun 30 19:47 txxx.MYI myisamchk -ddv txxx MyISAM file: txxx Record format: *Packed* <----------------------------- *Why*? Character set: utf8mb3_general_ci (33) File-version: 1 Creation time: 2024-06-30 19:47:30 Status: changed Data records: 1 Deleted blocks: 0 Datafile parts: 1 Deleted data: 0 Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6 Datafile length: 65548 Keyfile length: 2048 Max datafile length: 281474976710654 Max keyfile length: 288230376151710719 Recordlength: 16 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 multip. long NULL 0 1024 1024
Hi, Vassilis, On Jun 30, Vassilis Virvilis via discuss wrote:
Hi,
The question: Is uncompressing a myisam table when adding an index the expected behavior?
Yes. MyISAM does not support adding indexes inplace, so ALTER creates a new empty MyISAM table with indexes and copies the data over. The resulting table is uncompressed.
* If yes: why? Expanding the table behind the user's back is surprising...
Technically MyISAM could be able to add indexes inplace, it's just a feature that was never implemented. You can try this (disclaimer: I didn't test it). Create an empty table from your compressed one with CREATE TABLE LIKE. Add an index there. Compress it. Then copy your big MYD file over the the small MYD file and run myisamchk as you wanted. Better create a backup of your big table first, of course, or practice this on something small. Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hi Sergei, On 7/1/24 9:06 PM, Sergei Golubchik wrote:
Yes. MyISAM does not support adding indexes inplace, so ALTER creates a new empty MyISAM table with indexes and copies the data over. The resulting table is uncompressed.
Thanks for the confirmation. I think that this behavior should be documented though...
Technically MyISAM could be able to add indexes inplace, it's just a feature that was never implemented.
A very interesting fun project indeed :-)
You can try this (disclaimer: I didn't test it). Create an empty table from your compressed one with CREATE TABLE LIKE. Add an index there. Compress it. Then copy your big MYD file over the the small MYD file and run myisamchk as you wanted. Better create a backup of your big table first, of course, or practice this on something small.
Sneaky!!!! It has the potential to shave 3.5-4h from my build time but it requires the double space. Hm.... can't decide right now if I want to try it. Thanks. Vassilis
participants (2)
-
Sergei Golubchik -
Vassilis Virvilis