{"id":52,"date":"2013-09-30T20:24:47","date_gmt":"2013-09-30T20:24:47","guid":{"rendered":"http:\/\/aadant.com\/blog\/?p=52"},"modified":"2014-03-29T12:37:23","modified_gmt":"2014-03-29T12:37:23","slug":"poor-mans-online-optimize-in-5-6","status":"publish","type":"post","link":"http:\/\/aadant.com\/blog\/2013\/09\/30\/poor-mans-online-optimize-in-5-6\/","title":{"rendered":"Poor man&#8217;s Online Optimize in 5.6"},"content":{"rendered":"<p><strong>Table space fragmentation<\/strong> has generally 2 origins :<\/p>\n<ul>\n<li><strong>File System fragmentation<\/strong> : the data file is spread physically on many non contiguous locations on the disk.<\/li>\n<li><strong>Internal Fragmentation<\/strong> : the data and index pages have &#8220;holes&#8221; : this happens when\u00a0 rows are deleted or updated, especially at random.<\/li>\n<\/ul>\n<p>As a result, performance is affected by table space fragmentation. Data typically takes more space on disk and in memory. The disk is more busy than it should.<\/p>\n<p>File System fragmentation can be detected using the filefrag command on Linux (and similar on different OS). When using MyISAM, MYI files are usually very fragmented on the FS, much more than the MYD files.<\/p>\n<pre class=\"lang:sh mark:10,12 decode:true\" title=\"MyISAM fragmentation\"> ls -al frag\r\ntotal 883304\r\ndrwx------  2 aadant common      4096 Sep 30 18:41 .\r\ndrwxr-xr-x 17 aadant common      4096 Sep 30 18:59 ..\r\n-rw-rw----  1 aadant common        65 Sep 30 18:40 db.opt\r\n-rw-rw----  1 aadant common      8608 Sep 30 18:41 t.frm\r\n-rw-rw----  1 aadant common 551944192 Sep 30 19:41 t.MYD\r\n-rw-rw----  1 aadant common 426150912 Sep 30 19:41 t.MYI\r\nfilefrag frag\/t.MYD\r\nfrag\/t.MYD: 23 extents found\r\nfilefrag frag\/t.MYI\r\nfrag\/t.MYI: 4949 extents found<\/pre>\n<p>To measure internal InnoDB fragmentation, there is no general formula. It is possible to compute the theoretical size and compare it to the actual size on disk. It only works on fixed length data types. I have this trick : it is mentioned in <a href=\"http:\/\/aadant.com\/blog\/2013\/09\/25\/mysql-connect-slides\/\">50 Tips for Boosting MySQL Performance [CON2655]<\/a>. The idea is to compare the fragmented table and a much smaller table having the same table definition and sample data.<br \/>\nIt works well when the average row length can be estimated from the sample data even for variable length rows.<\/p>\n<pre class=\"lang:mysql decode:true\">create table t_defrag like t; \r\ninsert into t_defrag select * from t limit 20000;<\/pre>\n<p>Here 20000 rows 15-20 Mb on disk gives a good idea. Inserting the sample data the same empty table actually defragments the sample, since it contains non empty rows.<\/p>\n<p>The table is likely internally fragmented<\/p>\n<p><strong>if Avg_row_length(t) &gt; Avg_row_length(t_defrag)<\/strong><\/p>\n<p>where Avg_row_length comes from <strong>show table status<\/strong>.<\/p>\n<p>Example : some random data is created, uniformly distributed random row length : 1M rows, 640Mb. Internal fragmentation is created by deleting the even rows (one every two rows).<\/p>\n<pre class=\"lang:mysql mark:22,25-26 decode:true\" title=\"Fragmentation test case\">drop database test_case2;\r\ncreate database test_case2;\r\nuse test_case2;\r\nset global query_cache_size = 0;\r\nset global innodb_flush_log_at_trx_commit = 2;\r\ndrop table if exists t1;\r\ncreate table t1(\r\nid int primary key auto_increment,\r\nk  varchar(50),\r\nvalue varchar(1000), unique key(k)) engine=InnoDB;\r\nset @t1 =(select now());\r\nset @i := 1;\r\ninsert into t1(k,value) values(@i:=@i+1,repeat('a',rand()*1000)); \r\ninsert into t1(k,value) values(@i:=@i+1,repeat('a',rand()*1000)); \r\nreplace into t1(k,value) select @i:=@i+1,repeat('a',rand()*1000) from t1 t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6, t1 t7, t1 t8, t1 t9, t1 t10, t1 t11, t1 t12, t1 t13, t1 t14, t1 t15, t1 t16, t1 t17,\r\nt1 t18, t1 t19, t1 t20;\r\nset @t2 =(select now());\r\nselect @@version,timediff(@t2,@t1) duration;\r\n\r\nanalyze table t1\\G\r\nshow table status like 't1'\\G\r\ndelete from t1 where mod(id,2) = 0; \r\nshow table status like 't1'\\G\r\ndrop table if exists t1_defrag;\r\ncreate table t1_defrag like t1; \r\ninsert into t1_defrag select * from t1 limit 20000;\r\nanalyze table t1\\G\r\nanalyze table t1_defrag\\G\r\nshow table status like 't1'\\G\r\nshow table status like 't1_defrag'\\G<\/pre>\n<pre class=\"lang:default mark:8,30 decode:true\" title=\"Show table status\">mysql&gt; show table status like 't1'\\G\r\n*************************** 1. row ***************************\r\n           Name: t1\r\n         Engine: InnoDB\r\n        Version: 10\r\n     Row_format: Compact\r\n           Rows: 476676\r\n Avg_row_length: 1293\r\n    Data_length: 616562688\r\nMax_data_length: 0\r\n   Index_length: 37257216\r\n      Data_free: 5242880\r\n Auto_increment: 1114098\r\n    Create_time: 2013-09-30 15:19:01\r\n    Update_time: NULL\r\n     Check_time: NULL\r\n      Collation: latin1_swedish_ci\r\n       Checksum: NULL\r\n Create_options:\r\n        Comment:\r\n1 row in set (0.01 sec)\r\n\r\nmysql&gt; show table status like 't1_defrag'\\G\r\n*************************** 1. row ***************************\r\n           Name: t1_defrag\r\n         Engine: InnoDB\r\n        Version: 10\r\n     Row_format: Compact\r\n           Rows: 19773\r\n Avg_row_length: 610\r\n    Data_length: 12075008\r\nMax_data_length: 0\r\n   Index_length: 1589248\r\n      Data_free: 4194304\r\n Auto_increment: 40000\r\n    Create_time: 2013-09-30 15:23:38\r\n    Update_time: NULL\r\n     Check_time: NULL\r\n      Collation: latin1_swedish_ci\r\n       Checksum: NULL\r\n Create_options:\r\n        Comment:\r\n1 row in set (0.01 sec)<\/pre>\n<pre class=\"lang:default mark:5 decode:true\" title=\"1M rows, 671Mb on disk\">ls -al data\/test_case2\/t1*\r\n-rw-rw---- 1 aadant common      8612 Sep 30 20:39 data\/test_case2\/t1_defrag.frm\r\n-rw-rw---- 1 aadant common  22020096 Sep 30 20:39 data\/test_case2\/t1_defrag.ibd\r\n-rw-rw---- 1 aadant common      8612 Sep 30 20:40 data\/test_case2\/t1.frm\r\n-rw-rw---- 1 aadant common 671088640 Sep 30 20:44 data\/test_case2\/t1.ibd<\/pre>\n<p>As you can see, the table t1 is fragmented since its average row length is 2 times larger than t1_defrag.<\/p>\n<p>Fortunately, there is a command that fixes both FS and internal fragmentation on most storage engines :<\/p>\n<pre title=\"Optimize\">optimize table t1;<\/pre>\n<p>For InnoDB, optimize does rebuild the table and analyze it. So this command also works :<\/p>\n<pre title=\"Alter table\">alter table t1 engine=InnoDB;<\/pre>\n<pre class=\"lang:mysql mark:12 decode:true\" title=\"Defragmentation\">mysql&gt; alter table t1 engine=InnoDB;\r\nQuery OK, 524289 rows affected (5 min 58.99 sec)\r\nRecords: 524289  Duplicates: 0  Warnings: 0\r\n\r\nmysql&gt; show table status like 't1'\\G\r\n*************************** 1. row ***************************\r\n           Name: t1\r\n         Engine: InnoDB\r\n        Version: 10\r\n     Row_format: Compact\r\n           Rows: 500516\r\n Avg_row_length: 611\r\n    Data_length: 305971200\r\nMax_data_length: 0\r\n   Index_length: 18399232\r\n      Data_free: 5242880\r\n Auto_increment: 1048578\r\n    Create_time: 2013-09-30 19:53:47\r\n    Update_time: NULL\r\n     Check_time: NULL\r\n      Collation: latin1_swedish_ci\r\n       Checksum: NULL\r\n Create_options:\r\n        Comment:\r\n1 row in set (0.26 sec)<\/pre>\n<p>After the table rebuild, the average row length is back to normal and the table size is minimal on disk, here 2 times smaller, 324Mb.<\/p>\n<pre class=\"lang:sh mark:3 decode:true\"> ls -al data\/test_case2\/t1*\r\n-rw-rw---- 1 aadant common      8612 Sep 30 20:35 data\/test_case2\/t1.frm\r\n-rw-rw---- 1 aadant common 339738624 Sep 30 20:37 data\/test_case2\/t1.ibd<\/pre>\n<p>Note that the optimize and alter table engine=InnoDB commands take an exclusive lock on the table.<\/p>\n<p><strong>Update<\/strong> : from MySQL 5.6.17, optimize and alter table engine=InnoDB are online operations. See this blog and the MySQL manual for more information.<\/p>\n<p><a href=\"http:\/\/mysqlserverteam.com\/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables\/\">http:\/\/mysqlserverteam.com\/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables\/<\/a><\/p>\n<p>It means that if you use these commands before MySQL 5.6.17, your application : reads, writes, other DDL will be blocked during the table rebuild. So most of the time these commands were run in maintenance windows. Or using special tricks such as <a href=\"http:\/\/www.percona.com\/doc\/percona-toolkit\/2.1\/pt-online-schema-change.html\">pt-online-schema-change<\/a> or <a title=\"Online Schema Change at Facebook\" href=\"https:\/\/www.facebook.com\/note.php?note_id=430801045932\">Online Schema Change at Facebook<\/a>.<\/p>\n<p>In MySQL 5.6, this is no longer required in most cases thanks to <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-online-ddl.html\">InnoDB online DDL<\/a>. Online DDL is a major 5.6 feature. Even though optimize table is not an online operation, in practice, you can use this online DDL :<\/p>\n<pre class=\"lang:default decode:true\">alter table t1 row_format = &lt;row format&gt;;<\/pre>\n<p>where &lt;row_format&gt; is given by show table status. Usually :<\/p>\n<pre>alter table t1 row_format = Compact;<\/pre>\n<p>Make sure :<\/p>\n<ul>\n<li>you have enough disk space, otherwise you may run into this : Bug\u00a0#<a href=\"http:\/\/bugs.mysql.com\/bug.php?id=68895\">68895<\/a> Various assertions and crashes when running out of space<\/li>\n<li>\n<div><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-parameters.html#sysvar_innodb_online_alter_log_max_size\">innodb_online_alter_log_max_size<\/a> is large enough<\/div>\n<\/li>\n<\/ul>\n<p>Check also : <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-parameters.html#sysvar_innodb_sort_buffer_size\">innodb_sort_buffer_size<\/a> that can speed up online DDL.<\/p>\n<pre class=\"lang:default mark:3 decode:true\" title=\"Show table status after poor man's optimize\">\u00a0alter table t1 row_format=Compact;\r\nQuery OK, 0 rows affected (5 min 26.53 sec)\r\nRecords: 0\u00a0 Duplicates: 0\u00a0 Warnings: 0\r\n\r\nmysql&gt; show table status like 't1'\\G\r\n*************************** 1. row ***************************\r\n           Name: t1\r\n         Engine: InnoDB\r\n        Version: 10\r\n     Row_format: Compact\r\n           Rows: 502312\r\n Avg_row_length: 613\r\n    Data_length: 308068352\r\nMax_data_length: 0\r\n   Index_length: 9977856\r\n      Data_free: 0\r\n Auto_increment: 1114098\r\n    Create_time: 2013-09-30 20:53:41\r\n    Update_time: NULL\r\n     Check_time: NULL\r\n      Collation: latin1_swedish_ci\r\n       Checksum: NULL\r\n Create_options: row_format=COMPACT\r\n        Comment:\r\n1 row in set (0.02 sec)<\/pre>\n<p>The disk usage is even slightly smaller, 320Mb, than the classical rebuild and it is also slightly faster.<\/p>\n<pre class=\"lang:sh mark:3 decode:true\" title=\"Disk usage after poor man's optimize\"> ls -al data\/test_case2\/t1*\r\n-rw-rw---- 1 aadant common      8612 Sep 30 20:51 data\/test_case2\/t1.frm\r\n-rw-rw---- 1 aadant common 318767104 Sep 30 20:53 data\/test_case2\/t1.ibd<\/pre>\n<p>With <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-parameters.html#sysvar_innodb_sort_buffer_size\">innodb_sort_buffer_size<\/a>= 32M, the alter is faster :<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"With innodb_sort_buffer_size = 32M\"> alter table t1 row_format=Compact;\r\nQuery OK, 0 rows affected (2 min 51.96 sec)\r\nRecords: 0  Duplicates: 0  Warnings: 0<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Table space fragmentation has generally 2 origins : File System fragmentation : the data file is spread physically on many non contiguous locations on the disk. Internal Fragmentation : the data and index pages have &#8220;holes&#8221; : this happens when\u00a0 &hellip; <a href=\"http:\/\/aadant.com\/blog\/2013\/09\/30\/poor-mans-online-optimize-in-5-6\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[7,6],"tags":[],"class_list":["post-52","post","type-post","status-publish","format-standard","hentry","category-online-ddl","category-tip"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts\/52","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/comments?post=52"}],"version-history":[{"count":38,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts\/52\/revisions"}],"predecessor-version":[{"id":166,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts\/52\/revisions\/166"}],"wp:attachment":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/media?parent=52"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/categories?post=52"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/tags?post=52"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}