{"id":8,"date":"2013-06-10T09:49:18","date_gmt":"2013-06-10T09:49:18","guid":{"rendered":"http:\/\/188.166.119.205\/?p=7"},"modified":"2026-11-10T05:52:05","modified_gmt":"2026-11-10T05:52:05","slug":"7","status":"publish","type":"post","link":"https:\/\/banyek.net\/?p=8&lang=en","title":{"rendered":"Free up space on mysql servers"},"content":{"rendered":"<p>First, I have to tell you, that the \u201cfragmentation\u201d is not the best word what I should use, but that was the closest to that what I wanted to say. The base of the fragmentation checker script is <a href=\"http:\/\/www.mysqlperformanceblog.com\/2008\/02\/04\/finding-out-largest-tables-on-mysql-server\/\">Peter Zaitsev&#8217;s 2008 article about Information_schema<\/a>, I used that query to get that results what I needed.<\/p>\n<p>Some background: a few weeks ago we got some free space related problems (well, they weren\u2019t a real problem, but they could lead onto one, and we had to act fast.) The InnoDB is working on a bit strange way, it is not like to free up space what is deleted before, and because of this, the datafiles will grow to the end of the world. The first thing what we could do to reclaim free space is to use<\/p>\n<pre><code>innodb_file_per_table\n<\/code><\/pre>\n<p>configuration option, what will split up the database to a lot of .ibd datafiles, instead of one big ibdata file. If you got your data in different tables, you can run \u201cOPTIMIZE TABLE\u201d from mysql, what will compact the datafiles (creates a new one, copies the table contents onto this new table, deletes the old file, and renames the new) for you. <em>It takes a lot of time to run, and you have to know, that the \u201coptimize\u201d command will also be replicated, so after you did this on master be aware that it will happen on slaves too.<\/em> So, when you take some maintenance time, you have to calculate with this. It\u2019s easy to determine what tables would be optimized, you can check the table size from the data dictionary, and you can see how big is that table on disk. If they differ, then you can reclaim the space what is the difference between the two sizes.<\/p>\n<p><!--more--><\/p>\n<p><em>So I\u2019ve checked the table sizes, and I\u2019ve seen there are about 45 gigs of data what could be freed up, so I run the optimize commands, and we got enough free space. Hooray!<\/em><\/p>\n<p>After this, I made a small Perl script (<a href=\"https:\/\/github.com\/banyek\/Utilities\/blob\/master\/checkfrag.pl\">checkfrag.pl<\/a>\u2013 check it out on GitHub) what can help you to determine the table sizes, and differences. It\u2019s easy to use, if you run it without any arguments, it will ask you for the root password of the local database and after this, it will show you a neat table with the table sizes, and the \u201cfragmentation\u201d ratio. Like this (and believe me, it looks much better on a terminal:<\/p>\n<pre><code>[root@sql-server bin]# \/usr\/local\/bin\/checkfragmentation.pl\nPlease add the mysql root password:\nTable name                                Number of rows (M) Size of data (G) Size of Index (G)    Total Size (G)  Data\/Index ratio  Size on disk (G) Fragmentation ratio\ndb1.AAAAAAA                                            93.79            80.84              0.93             81.77              0.01             82.93                1.01\ndb1.BBBBBBB                                            95.79             5.93             12.74             18.67              2.15             18.94                1.01\ndb2.CCCCCCC                                            42.40             3.93              8.14             12.06              2.07             12.24                1.02\ndb1.DDDDDDD                                            52.09             3.88              7.89             11.77              2.03             11.95                1.01\ndb1.EEEEEEE                                            45.66             3.49              7.40             10.89              2.12             11.05                1.01\ndb2.FFFFFFF                                            33.07             4.28              5.83             10.10              1.36             10.48                1.04\ndb3.GGGGGGG                                            41.27             3.77              5.41              9.18              1.44              9.32                1.02\ndb1.HHHHHHH                                             8.73             6.90              0.19              7.08              0.03              7.19                1.02\ndb1.IIIIIII                                            31.89             2.69              3.30              6.00              1.23              6.09                1.01\ndb3.JJJJJJJ                                            48.25             2.26              2.87              5.13              1.27              5.21                1.02\n<\/code><\/pre>\n<p>I\u2019ve made a few command line options for the script, you could select how many rows you wanna to display, you could select the mysql data files directory, and you can remove the header if you wanna to run it on a lot of tables, and you wanna to sort it with an old-fashioned Unix sort utility (remember -n and -k )<\/p>\n<pre><code>[root@sql-server bin]# \/usr\/local\/bin\/checkfragmentation.pl -h\nUsage: .\/checkfragmentation.pl [ -h ] [ -e ] [ -u user ] [ -p password ] [ -d mysqldatadir ] [ -n NUM ]\n -h                 this help screen\n -e                 no print header - useful to sort the results\n -u username        username with connect to mysql. Defaults 'root'\n -p password        password with connect to mysql. If not provided, you'll be asked for it.\n -d mysqldatadir    full path of mysql data files. Defaults '\/var\/lib\/mysql'\n -n NUM             number of rows to display. Defaults 10\n<\/code><\/pre>\n<p>(Why Perl? I like Perl. And Perl is not Dead.)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>First, I have to tell you, that the \u201cfragmentation\u201d is not the best word what I should use, but that was the closest to that what I wanted to say. The base of the fragmentation checker script is Peter Zaitsev&#8217;s 2008 article about Information_schema, I used that query to get that results what I needed. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[134],"tags":[144,126,234],"class_list":["post-8","post","type-post","status-publish","format-standard","hentry","category-tech-en","tag-mysql-en","tag-oldblog-en","tag-perl-en"],"_links":{"self":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/8","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8"}],"version-history":[{"count":1,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/8\/revisions"}],"predecessor-version":[{"id":450,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/8\/revisions\/450"}],"wp:attachment":[{"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}