{"id":7721,"date":"2018-09-20T14:49:55","date_gmt":"2018-09-20T06:49:55","guid":{"rendered":"http:\/\/rmohan.com\/?p=7721"},"modified":"2018-09-20T14:49:55","modified_gmt":"2018-09-20T06:49:55","slug":"mysql-calculate-the-free-space-in-ibd-files","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7721","title":{"rendered":"MySQL: Calculate the free space in IBD files"},"content":{"rendered":"<p>If you use MySQL with InnoDB, chances are you&#8217;ve seen growing IBD data files. Those are the files that actually hold your data within MySQL. By default, they only grow &#8212; they don&#8217;t shrink. So how do you know if you still have free space left in your IBD files?<\/p>\n<p>There&#8217;s a query you can use to determine that:<\/p>\n<pre>SELECT round((data_length+index_length)\/1024\/1024,2)\r\nFROM information_schema.tables\r\nWHERE\r\n  table_schema='zabbix'\r\n  AND table_name='history_text';\r\n<\/pre>\n<p>The above will check a database called\u00a0<code>zabbix<\/code>\u00a0for a table called\u00a0<code>history_text<\/code>. The result will be the size that MySQL has &#8220;<em>in use<\/em>&#8221; in that file. If that returns 5.000 as a value, you have 5GB of data in there.<\/p>\n<p>In my example, it showed the data size to be 16GB. But the actual IBD file was over 50GB large.<\/p>\n<pre>$ ls -alh history_text.ibd\r\n-rw-r----- 1 mysql mysql 52G Sep 10 15:26 history_text.ibd\r\n<\/pre>\n<p>In this example I had 36GB of wasted space on the disk (52GB according to the OS, 16GB in use by MySQL). If you run MySQL with\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-multiple-tablespaces.html\">innodb_file_per_table=ON<\/a>, you can individually shrink the IBD files. One way, is to run an\u00a0<code>OPTIMIZE<\/code>\u00a0query on that table.<\/p>\n<p><strong>Note: this can be a blocking operation, depending on your MySQL version. WRITE and READ I\/O can be blocked to the table for the duration of the OPTIMIZE query.<\/strong><\/p>\n<pre>MariaDB [zabbix]&gt; OPTIMIZE TABLE history_text;\r\nStage: 1 of 1 'altering table'   93.7% of stage done\r\nStage: 1 of 1 'altering table'    100% of stage done\r\n\r\n+---------------------+----------+----------+-------------------------------------------------------------------+\r\n| Table               | Op       | Msg_type | Msg_text                                                          |\r\n+---------------------+----------+----------+-------------------------------------------------------------------+\r\n| zabbix.history_text | optimize | note     | Table does not support optimize, doing recreate + analyze instead |\r\n| zabbix.history_text | optimize | status   | OK                                                                |\r\n+---------------------+----------+----------+-------------------------------------------------------------------+\r\n2 rows in set (55 min 37.37 sec)\r\n<\/pre>\n<p>The result is quite a big file size savings:<\/p>\n<pre>$ ls -alh history_text.ibd\r\n-rw-rw---- 1 mysql mysql 11G Sep 10 16:27 history_text.ibd\r\n<\/pre>\n<p>The file that was previously 52GB in size, is now just 11GB.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you use MySQL with InnoDB, chances are you&#8217;ve seen growing IBD data files. Those are the files that actually hold your data within MySQL. By default, they only grow &#8212; they don&#8217;t shrink. So how do you know if you still have free space left in your IBD files?<\/p>\n<p>There&#8217;s a query you can [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7721"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7721"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7721\/revisions"}],"predecessor-version":[{"id":7722,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7721\/revisions\/7722"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}