Sorry, I can't address your specific question, but I have several other tips,
some of which may save a lot of space...
USING HASH -- ignored; presumably BTree is used instead.
What Version of MySQL? 5.5(?) can drop an InnoDB (only?) index live.
BTrees sometimes grow after any sort of copy/reorg. Inserting rows (of data or
index) in sorted order will produce a more compact BTree. Random insertion
will (in theory) lead to about 40% overhead.
Tricking MyISAM into "Rebuild by sorting" instead of "rebuild by keybuffer"
should take the more compact approach, and probable be faster.
OPTIMIZE (on MyISAM) will squeeze out any wasted space caused by DELETEs or
diff-length UPDATEs.
pt-online-schema-change uses a TRIGGER to do the work 'live'.
columnF is exactly 8 bytes in MyISAM. That is <10% of the total?
These are so close to the same that I question they need for all of them:
> UNIQUE KEY `unique2`
> (`columnR`,`columnB`,`columnA`,`columnN`,`columnL`,`columnM`)
> UNIQUE KEY `unique3` (`columnR`,`columnB`,`columnA`,`columnO`,`columnI`)
> UNIQUE KEY `unique4` (`columnR`,`columnB`,`columnA`,`columnJ`)
Do you need the UNIQUEness constraints? Or would it suffice to simply say
INDEX(B, A)
Hmmm... I would consider replacing these 8 indexes
u3 RBAOI
u4 RBAJ
u2 RBANLM
k4 RBANIOH
k7 BANOH
k9 RBAINOH
k5 RBAJH
u1 RBNAKOM
With 1:
INDEX(columnB, columnA, columnN)
Even if that doesn't work out, I suspect you could consolidate some. After 3-4
fields, an index becomes not very useful, and takes a lot of disk space.
(These look different enough to ignore)
k1 QN
k2 REPB
k3 BFNAR
k6 RNAHIBO
k8 BGNAR
> PARTITION BY RANGE (columnR)
For performance, it is usually better to put columnR at the _end_ of the
indexes. Better yet would be to leave columnR off the indexes all together --
partition pruning does most of what you need it for.
Since columnR smells like year+week, and each partition has a distinct value of
it, I think you definitely should not include R in any of the non-unique
indexes -- this would save ~3 bytes per row per index. Also, definitely move R
to the _end_ of the UNIQUE indexes -- this would improve performance,
especially when you query over a range of columnR.
> `columnQ` datetime DEFAULT NULL,
DATETIME takes 8 bytes and TIMESTAMP takes only 4 (until 5.6); consider
changing to TIMESTAMP.
Most operations on a PARTITIONed table open _all_ partitions, even if they only
use _one_. For that reason, I recommend no more than, say, 50 partitions in a
table.
> -----Original Message-----
> From: Michael Finch [mailto:mfi...@brightedge.com]
> Sent: Tuesday, May 14, 2013 12:56 PM
> To: mysql@lists.mysql.com
> Subject: Dropping column/index from MyISAM table increases disk space
> without calling "optimize"
>
> Hey,
>
> We have a table with a column and index that we don't need anymore, so
> we are trying to find the best way to get rid of them
>
> Path 1) Create a new table with the updated schema (excluding that
> column and index), and insert from the old table into the new table
> Path 2) Alter the original table to drop the column and index
>
> The problem with either of these is that when we are done, the table
> size has grown significantly (~15%).
>
> size of orig table: 2026.5G
> size of new table after inserting all of the data or running the alter:
> 2473.5M ~~flush the table~~ size of orig table: 2026.5G size of new
> table after inserting all of the data or running the alter: 2292.2M
> ~~optimize the table~~ size of new table after inserting all of the
> data or running the alter: 1912.7M
>
> The only way to get the size of the new table below the size of the
> original table is by optimizing it (this holds true for innodb as
> well). Our problem is that since this table is so big, it takes long
> enough just to alter it, let alone optimize it afterwards.
>
> Can anyone explain the best method for us to get rid of the
> column/index and reclaim the maximum amount of disk space? If we really
> need to alter + optimize that's okay, but I'd like to understand why
> that is. Any help is greatly appreciated.
>
> Thanks!!!
> Michael
>
>
>
>
> Here's the schema of the original table. We want to drop columnF and
> key3
>
>
> CREATE TABLE `tableT` (
> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
> `columnA` int(11) unsigned NOT NULL,
> `columnB` int(11) unsigned NOT NULL,
> `columnC` varchar(200) DEFAULT NULL,
> `columnD` varchar(200) DEFAULT NULL,
> `columnE` varchar(200) DEFAULT NULL,
> `columnF` bigint(20) unsigned NOT NULL DEFAULT '0',
> `columnG` bigint(20) unsigned NOT NULL DEFAULT '0',
> `columnH` tinyint(4) DEFAULT NULL,
> `columnI` smallint(3) DEFAULT NULL,
> `columnJ` smallint(3) DEFAULT NULL,
> `columnK` smallint(3) DEFAULT NULL,
> `columnL` smallint(3) DEFAULT NULL,
> `columnM` smallint(3) DEFAULT '0',
> `columnN` tinyint(4) DEFAULT NULL,
> `columnO` tinyint(4) DEFAULT '0',
> `columnP` tinyint(4) DEFAULT NULL,
> `columnQ` datetime DEFAULT NULL,
> `columnR` mediumint(8) unsigned NOT NULL,
> `columnS` mediumint(8) unsigned NOT NULL,
> `columnT` smallint(5) unsigned NOT NULL,
> PRIMARY KEY (`id`,`columnR`),
> UNIQUE KEY `unique1`
> (`columnR`,`columnB`,`columnN`,`columnA`,`columnK`,`columnO`,`columnM`)
> USING HASH,
> UNIQUE KEY `unique2`
> (`columnR`,`columnB`,`columnA`,`columnN`,`columnL`,`columnM`) USING
> HASH,
> UNIQUE KEY `unique3`
> (`columnR`,`columnB`,`columnA`,`columnO`,`columnI`) USING HASH,
> UNIQUE KEY `unique4` (`columnR`,`columnB`,`columnA`,`columnJ`) USING
> HASH,
> KEY `key1` (`columnQ`,`columnN`) USING BTREE,
> KEY `key2` (`columnR`,`columnE`,`columnP`,`columnB`) USING HASH,
> KEY `key3` (`columnB`,`columnF`,`columnN`,`columnA`,`columnR`) USING
> HASH,
> KEY `key4`
> (`columnR`,`columnB`,`columnA`,`columnN`,`columnI`,`columnO`,`columnH`)
> USING HASH,
> KEY `key5` (`columnR`,`columnB`,`columnA`,`columnJ`,`columnH`) USING
> HASH,
> KEY `key6`
> (`columnR`,`columnN`,`columnA`,`columnH`,`columnI`,`columnB`,`columnO`)
> USING HASH,
> KEY `key7` (`columnB`,`columnA`,`columnN`,`columnO`,`columnH`) USING
> HASH,
> KEY `key8` (`columnB`,`columnG`,`columnN`,`columnA`,`columnR`) USING
> HASH,
> KEY `key9`
> (`columnR`,`columnB`,`columnA`,`columnI`,`columnN`,`columnO`,`columnH`)
> USING HASH
> ) ENGINE=MyISAM AUTO_INCREMENT=529343873 DEFAULT CHARSET=latin1
> /*!50100 PARTITION BY RANGE (columnR)
> (PARTITION p0 VALUES LESS THAN (201001) ENGINE = MyISAM, PARTITION p1
> VALUES LESS THAN (201002) ENGINE = MyISAM, PARTITION p2 VALUES LESS
> THAN (201003) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (201004)
> ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (201005) ENGINE =
> MyISAM, PARTITION p5 VALUES LESS THAN (201006) ENGINE = MyISAM,
> PARTITION p6 VALUES LESS THAN (201007) ENGINE = MyISAM, PARTITION p7
> VALUES LESS THAN (201008) ENGINE = MyISAM, PARTITION p8 VALUES LESS
> THAN (201009) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (201010)
> ENGINE = MyISAM, PARTITION p10 VALUES LESS THAN (201011) ENGINE =
> MyISAM, PARTITION p11 VALUES LESS THAN (201012) ENGINE = MyISAM,
> PARTITION p12 VALUES LESS THAN (201013) ENGINE = MyISAM, PARTITION p13
> VALUES LESS THAN (201014) ENGINE = MyISAM, PARTITION p14 VALUES LESS
> THAN (201015) ENGINE = MyISAM, PARTITION p15 VALUES LESS THAN (201016)
> ENGINE = MyISAM, PARTITION p16 VALUES LESS THAN (201017) ENGINE =
> MyISAM, PARTITION p17 VALUES LESS THAN (201018) ENGINE = MyISAM,
> PARTITION p18 VALUES LESS THAN (201019) ENGINE = MyISAM, PARTITION p19
> VALUES LESS THAN (201020) ENGINE = MyISAM, PARTITION p20 VALUES LESS
> THAN (201021) ENGINE = MyISAM, PARTITION p21 VALUES LESS THAN (201022)
> ENGINE = MyISAM, PARTITION p22 VALUES LESS THAN (201023) ENGINE =
> MyISAM, PARTITION p23 VALUES LESS THAN (201024) ENGINE = MyISAM,
> PARTITION p24 VALUES LESS THAN (201025) ENGINE = MyISAM, PARTITION p25
> VALUES LESS THAN (201026) ENGINE = MyISAM, PARTITION p26 VALUES LESS
> THAN (201027) ENGINE = MyISAM, PARTITION p27 VALUES LESS THAN (201028)
> ENGINE = MyISAM, PARTITION p28 VALUES LESS THAN (201029) ENGINE =
> MyISAM, PARTITION p29 VALUES LESS THAN (201030) ENGINE = MyISAM,
> PARTITION p30 VALUES LESS THAN (201031) ENGINE = MyISAM, PARTITION p31
> VALUES LESS THAN (201032) ENGINE = MyISAM, PARTITION p32 VALUES LESS
> THAN (201033) ENGINE = MyISAM, PARTITION p33 VALUES LESS THAN (201034)
> ENGINE = MyISAM, PARTITION p34 VALUES LESS THAN (201035) ENGINE =
> MyISAM, PARTITION p35 VALUES LESS THAN (201036) ENGINE = MyISAM,
> PARTITION p36 VALUES LESS THAN (201037) ENGINE = MyISAM, PARTITION p37
> VALUES LESS THAN (201038) ENGINE = MyISAM, PARTITION p38 VALUES LESS
> THAN (201039) ENGINE = MyISAM, PARTITION p39 VALUES LESS THAN (201040)
> ENGINE = MyISAM, PARTITION p40 VALUES LESS THAN (201041) ENGINE =
> MyISAM, PARTITION p41 VALUES LESS THAN (201042) ENGINE = MyISAM,
> PARTITION p42 VALUES LESS THAN (201043) ENGINE = MyISAM, PARTITION p43
> VALUES LESS THAN (201044) ENGINE = MyISAM, PARTITION p44 VALUES LESS
> THAN (201045) ENGINE = MyISAM, PARTITION p45 VALUES LESS THAN (201046)
> ENGINE = MyISAM, PARTITION p46 VALUES LESS THAN (201047) ENGINE =
> MyISAM, PARTITION p47 VALUES LESS THAN (201048) ENGINE = MyISAM,
> PARTITION p48 VALUES LESS THAN (201049) ENGINE = MyISAM, PARTITION p49
> VALUES LESS THAN (201050) ENGINE = MyISAM, PARTITION p50 VALUES LESS
> THAN (201051) ENGINE = MyISAM, PARTITION p51 VALUES LESS THAN (201052)
> ENGINE = MyISAM, PARTITION p52 VALUES LESS THAN (201053) ENGINE =
> MyISAM, PARTITION p53 VALUES LESS THAN (201101) ENGINE = MyISAM,
> PARTITION p54 VALUES LESS THAN (201102) ENGINE = MyISAM, PARTITION p55
> VALUES LESS THAN (201103) ENGINE = MyISAM, PARTITION p56 VALUES LESS
> THAN (201104) ENGINE = MyISAM, PARTITION p57 VALUES LESS THAN (201105)
> ENGINE = MyISAM, PARTITION p58 VALUES LESS THAN (201106) ENGINE =
> MyISAM, PARTITION p59 VALUES LESS THAN (201107) ENGINE = MyISAM,
> PARTITION p60 VALUES LESS THAN (201108) ENGINE = MyISAM, PARTITION p61
> VALUES LESS THAN (201109) ENGINE = MyISAM, PARTITION p62 VALUES LESS
> THAN (201110) ENGINE = MyISAM, PARTITION p63 VALUES LESS THAN (201111)
> ENGINE = MyISAM, PARTITION p64 VALUES LESS THAN (201112) ENGINE =
> MyISAM, PARTITION p65 VALUES LESS THAN (201113) ENGINE = MyISAM,
> PARTITION p66 VALUES LESS THAN (201114) ENGINE = MyISAM, PARTITION p67
> VALUES LESS THAN (201115) ENGINE = MyISAM, PARTITION p68 VALUES LESS
> THAN (201116) ENGINE = MyISAM, PARTITION p69 VALUES LESS THAN (201117)
> ENGINE = MyISAM, PARTITION p70 VALUES LESS THAN (201118) ENGINE =
> MyISAM, PARTITION p71 VALUES LESS THAN (201119) ENGINE = MyISAM,
> PARTITION p72 VALUES LESS THAN (201120) ENGINE = MyISAM, PARTITION p73
> VALUES LESS THAN (201121) ENGINE = MyISAM, PARTITION p74 VALUES LESS
> THAN (201122) ENGINE = MyISAM, PARTITION p75 VALUES LESS THAN (201123)
> ENGINE = MyISAM, PARTITION p76 VALUES LESS THAN (201124) ENGINE =
> MyISAM, PARTITION p77 VALUES LESS THAN (201125) ENGINE = MyISAM,
> PARTITION p78 VALUES LESS THAN (201126) ENGINE = MyISAM, PARTITION p79
> VALUES LESS THAN (201127) ENGINE = MyISAM, PARTITION p80 VALUES LESS
> THAN (201128) ENGINE = MyISAM, PARTITION p81 VALUES LESS THAN (201129)
> ENGINE = MyISAM, PARTITION p82 VALUES LESS THAN (201130) ENGINE =
> MyISAM, PARTITION p83 VALUES LESS THAN (201131) ENGINE = MyISAM,
> PARTITION p84 VALUES LESS THAN (201132) ENGINE = MyISAM, PARTITION p85
> VALUES LESS THAN (201133) ENGINE = MyISAM, PARTITION p86 VALUES LESS
> THAN (201134) ENGINE = MyISAM, PARTITION p87 VALUES LESS THAN (201135)
> ENGINE = MyISAM, PARTITION p88 VALUES LESS THAN (201136) ENGINE =
> MyISAM, PARTITION p89 VALUES LESS THAN (201137) ENGINE = MyISAM,
> PARTITION p90 VALUES LESS THAN (201138) ENGINE = MyISAM, PARTITION p91
> VALUES LESS THAN (201139) ENGINE = MyISAM, PARTITION p92 VALUES LESS
> THAN (201140) ENGINE = MyISAM, PARTITION p93 VALUES LESS THAN (201141)
> ENGINE = MyISAM, PARTITION p94 VALUES LESS THAN (201142) ENGINE =
> MyISAM, PARTITION p95 VALUES LESS THAN (201143) ENGINE = MyISAM,
> PARTITION p96 VALUES LESS THAN (201144) ENGINE = MyISAM, PARTITION p97
> VALUES LESS THAN (201145) ENGINE = MyISAM, PARTITION p98 VALUES LESS
> THAN (201146) ENGINE = MyISAM, PARTITION p99 VALUES LESS THAN (201147)
> ENGINE = MyISAM, PARTITION p100 VALUES LESS THAN (201148) ENGINE =
> MyISAM, PARTITION p101 VALUES LESS THAN (201149) ENGINE = MyISAM,
> PARTITION p102 VALUES LESS THAN (201150) ENGINE = MyISAM, PARTITION
> p103 VALUES LESS THAN (201151) ENGINE = MyISAM, PARTITION p104 VALUES
> LESS THAN (201152) ENGINE = MyISAM, PARTITION p105 VALUES LESS THAN
> (201201) ENGINE = MyISAM, PARTITION p106 VALUES LESS THAN (201202)
> ENGINE = MyISAM, PARTITION p107 VALUES LESS THAN (201203) ENGINE =
> MyISAM, PARTITION p108 VALUES LESS THAN (201204) ENGINE = MyISAM,
> PARTITION p109 VALUES LESS THAN (201205) ENGINE = MyISAM, PARTITION
> p110 VALUES LESS THAN (201206) ENGINE = MyISAM, PARTITION p111 VALUES
> LESS THAN (201207) ENGINE = MyISAM, PARTITION p112 VALUES LESS THAN
> (201208) ENGINE = MyISAM, PARTITION p113 VALUES LESS THAN (201209)
> ENGINE = MyISAM, PARTITION p114 VALUES LESS THAN (201210) ENGINE =
> MyISAM, PARTITION p115 VALUES LESS THAN (201211) ENGINE = MyISAM,
> PARTITION p116 VALUES LESS THAN (201212) ENGINE = MyISAM, PARTITION
> p117 VALUES LESS THAN (201213) ENGINE = MyISAM, PARTITION p118 VALUES
> LESS THAN (201214) ENGINE = MyISAM, PARTITION p119 VALUES LESS THAN
> (201215) ENGINE = MyISAM, PARTITION p120 VALUES LESS THAN (201216)
> ENGINE = MyISAM, PARTITION p121 VALUES LESS THAN (201217) ENGINE =
> MyISAM, PARTITION p122 VALUES LESS THAN (201218) ENGINE = MyISAM,
> PARTITION p123 VALUES LESS THAN (201219) ENGINE = MyISAM, PARTITION
> p124 VALUES LESS THAN (201220) ENGINE = MyISAM, PARTITION p125 VALUES
> LESS THAN (201221) ENGINE = MyISAM, PARTITION p126 VALUES LESS THAN
> (201222) ENGINE = MyISAM, PARTITION p127 VALUES LESS THAN (201223)
> ENGINE = MyISAM, PARTITION p128 VALUES LESS THAN (201224) ENGINE =
> MyISAM, PARTITION p129 VALUES LESS THAN (201225) ENGINE = MyISAM,
> PARTITION p130 VALUES LESS THAN (201226) ENGINE = MyISAM, PARTITION
> p131 VALUES LESS THAN (201227) ENGINE = MyISAM, PARTITION p132 VALUES
> LESS THAN (201228) ENGINE = MyISAM, PARTITION p133 VALUES LESS THAN
> (201229) ENGINE = MyISAM, PARTITION p134 VALUES LESS THAN (201230)
> ENGINE = MyISAM, PARTITION p135 VALUES LESS THAN (201231) ENGINE =
> MyISAM, PARTITION p136 VALUES LESS THAN (201232) ENGINE = MyISAM,
> PARTITION p137 VALUES LESS THAN (201233) ENGINE = MyISAM, PARTITION
> p138 VALUES LESS THAN (201234) ENGINE = MyISAM, PARTITION p139 VALUES
> LESS THAN (201235) ENGINE = MyISAM, PARTITION p140 VALUES LESS THAN
> (201236) ENGINE = MyISAM, PARTITION p141 VALUES LESS THAN (201237)
> ENGINE = MyISAM, PARTITION p142 VALUES LESS THAN (201238) ENGINE =
> MyISAM, PARTITION p143 VALUES LESS THAN (201239) ENGINE = MyISAM,
> PARTITION p144 VALUES LESS THAN (201240) ENGINE = MyISAM, PARTITION
> p145 VALUES LESS THAN (201241) ENGINE = MyISAM, PARTITION p146 VALUES
> LESS THAN (201242) ENGINE = MyISAM, PARTITION p147 VALUES LESS THAN
> (201243) ENGINE = MyISAM, PARTITION p148 VALUES LESS THAN (201244)
> ENGINE = MyISAM, PARTITION p149 VALUES LESS THAN (201245) ENGINE =
> MyISAM, PARTITION p150 VALUES LESS THAN (201246) ENGINE = MyISAM,
> PARTITION p151 VALUES LESS THAN (201247) ENGINE = MyISAM, PARTITION
> p152 VALUES LESS THAN (201248) ENGINE = MyISAM, PARTITION p153 VALUES
> LESS THAN (201249) ENGINE = MyISAM, PARTITION p154 VALUES LESS THAN
> (201250) ENGINE = MyISAM, PARTITION p155 VALUES LESS THAN (201251)
> ENGINE = MyISAM, PARTITION p156 VALUES LESS THAN (201252) ENGINE =
> MyISAM, PARTITION p157 VALUES LESS THAN (201301) ENGINE = MyISAM,
> PARTITION p158 VALUES LESS THAN (201302) ENGINE = MyISAM, PARTITION
> p159 VALUES LESS THAN (201303) ENGINE = MyISAM, PARTITION p160 VALUES
> LESS THAN (201304) ENGINE = MyISAM, PARTITION p161 VALUES LESS THAN
> (201305) ENGINE = MyISAM, PARTITION p162 VALUES LESS THAN (201306)
> ENGINE = MyISAM, PARTITION p163 VALUES LESS THAN (201307) ENGINE =
> MyISAM, PARTITION p164 VALUES LESS THAN (201308) ENGINE = MyISAM,
> PARTITION p165 VALUES LESS THAN (201309) ENGINE = MyISAM, PARTITION
> p166 VALUES LESS THAN (201310) ENGINE = MyISAM, PARTITION p167 VALUES
> LESS THAN (201311) ENGINE = MyISAM, PARTITION p168 VALUES LESS THAN
> (201312) ENGINE = MyISAM, PARTITION p169 VALUES LESS THAN (201313)
> ENGINE = MyISAM, PARTITION p170 VALUES LESS THAN (201314) ENGINE =
> MyISAM, PARTITION p171 VALUES LESS THAN (201315) ENGINE = MyISAM,
> PARTITION p172 VALUES LESS THAN (201316) ENGINE = MyISAM, PARTITION
> p173 VALUES LESS THAN (201317) ENGINE = MyISAM, PARTITION p174 VALUES
> LESS THAN (201318) ENGINE = MyISAM, PARTITION p175 VALUES LESS THAN
> (201319) ENGINE = MyISAM, PARTITION p176 VALUES LESS THAN (201320)
> ENGINE = MyISAM, PARTITION p177 VALUES LESS THAN (201321) ENGINE =
> MyISAM, PARTITION p178 VALUES LESS THAN (201322) ENGINE = MyISAM,
> PARTITION p179 VALUES LESS THAN (201323) ENGINE = MyISAM, PARTITION
> p180 VALUES LESS THAN (201324) ENGINE = MyISAM, PARTITION p181 VALUES
> LESS THAN (201325) ENGINE = MyISAM, PARTITION p182 VALUES LESS THAN
> (201326) ENGINE = MyISAM, PARTITION p183 VALUES LESS THAN (201327)
> ENGINE = MyISAM, PARTITION p184 VALUES LESS THAN (201328) ENGINE =
> MyISAM, PARTITION p185 VALUES LESS THAN (201329) ENGINE = MyISAM,
> PARTITION p186 VALUES LESS THAN (201330) ENGINE = MyISAM, PARTITION
> p187 VALUES LESS THAN (201331) ENGINE = MyISAM, PARTITION p188 VALUES
> LESS THAN (201332) ENGINE = MyISAM, PARTITION p189 VALUES LESS THAN
> (201333) ENGINE = MyISAM, PARTITION p190 VALUES LESS THAN (201334)
> ENGINE = MyISAM, PARTITION p191 VALUES LESS THAN (201335) ENGINE =
> MyISAM, PARTITION p192 VALUES LESS THAN (201336) ENGINE = MyISAM,
> PARTITION p193 VALUES LESS THAN (201337) ENGINE = MyISAM, PARTITION
> p194 VALUES LESS THAN (201338) ENGINE = MyISAM, PARTITION p195 VALUES
> LESS THAN (201339) ENGINE = MyISAM, PARTITION p196 VALUES LESS THAN
> (201340) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE
> ENGINE = MyISAM) */
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
