tag:blogger.com,1999:blog-31421954.post3160215463006945464..comments2023-10-30T08:23:12.960-07:00Comments on mySQL DBA, Architecture, Dev, Scale, HA, Code : innodb_file_per_table, shrinking table spaces and the data dictionaryDathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-31421954.post-56566283428190731392010-02-04T14:14:44.621-08:002010-02-04T14:14:44.621-08:00Little scary as you deleting the files directly; y...Little scary as you deleting the files directly; you instead instead drop them (or atleast truncate and then drop).. if you just remove, tablespace still has entries for them.<br /><br />Thats always a problem until InnoDB supports a easy way to purge and reclaim the space without re-creation of the table. Its easy to introduce a command, that can purge this without affecting the online activity on the table.<br /><br />Normally, I create a new table; and copy over the data and flip at the end... (provided you have a easy way to identify whats new/old records)venuhttp://venublog.com/noreply@blogger.comtag:blogger.com,1999:blog-31421954.post-44070939768118884132010-02-04T13:48:34.532-08:002010-02-04T13:48:34.532-08:00I haven't administered a production MySQL box ...I haven't administered a production MySQL box in a few years, but this reminds me of Oracle's <i>ALTER TABLE MOVE TABLESPACE X</i> where X is the tablespace in which it already resides.<br /><br />From <a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html" rel="nofollow">MySQL 5.0 Reference Manual ... Defragmenting a Table</a>:<br /><br />It can speed up index scans if you periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table:<br /><br /><i>ALTER TABLE tbl_name ENGINE=INNODB;</i>Markhttps://www.blogger.com/profile/14668153232723718277noreply@blogger.com