User Tools

Site Tools


howtos:mysql_optimize

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

howtos:mysql_optimize [d/m/Y H:i] (current)
Line 1: Line 1:
 +MySQL Data Fragmentation - What, When and How
 +March 11, 2011
  
 +MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient.
 +
 +Fragmentation - an example
 +MySQL has quite a few different storage engines to store data in tables. Whenever MySQL deletes rows from your table, the space left behind is then empty. Over time with a lot of DELETEs, this space can grow larger than the used space in your table. When MySQL goes to scan that data, it scans to the high water mark of the table, that is the highest point at which data has been added. If new inserts occur, MySQL will try to use that space, but nevertheless gaps will persist.
 +
 +This extra fragmented space can make reads against the table less efficient than they might otherwise be. Let's look at an example.
 +
 +We'll create a database (sometimes called a schema) and a test table:
 +
 +<code>
 +(root@localhost) [test]> create database frag_test;
 +Query OK, 1 row affected (0.03 sec)
 +
 +(root@localhost) [test]> use frag_test;
 +Database changed
 +
 +(root@localhost) [frag_test]> create table frag_test (c1 varchar(64));
 +Query OK, 0 rows affected (0.05 sec)
 +
 +</code>
 +
 +Next let's add some rows to the table:
 +
 +<code>
 +(root@localhost) [frag_test]> insert into frag_test values ('this is row 1');
 +Query OK, 1 row affected (0.01 sec)
 +
 +(root@localhost) [frag_test]> insert into frag_test values ('this is row 2');
 +Query OK, 1 row affected (0.00 sec)
 +
 +(root@localhost) [frag_test]> insert into frag_test values ('this is row 3');
 +Query OK, 1 row affected (0.00 sec)
 +</code>
 +
 +Now we'll check for fragmentation:
 +
 +<code>
 +(root@localhost) [frag_test]> show table status from frag_test\G;
 +*************************** 1. row ***************************
 +           Name: frag_test
 +         Engine: MyISAM
 +        Version: 10
 +     Row_format: Dynamic
 +           Rows: 3
 + Avg_row_length: 20
 +    Data_length: 60
 +Max_data_length: 281474976710655
 +   Index_length: 1024
 +      Data_free: 0
 + Auto_increment: NULL
 +    Create_time: 2011-02-23 14:55:27
 +    Update_time: 2011-02-23 15:06:55
 +     Check_time: NULL
 +      Collation: latin1_swedish_ci
 +       Checksum: NULL
 + Create_options: 
 +        Comment: 
 +1 row in set (0.00 sec)
 +</code>
 +Now let's delete a row and check again:
 +
 +<code>
 +(root@localhost) [frag_test]> delete from frag_test where c1 = 'this is row 2';
 +Query OK, 1 row affected (0.00 sec)
 +
 +(root@localhost) [frag_test]> show table status from frag_test\G;
 +*************************** 1. row ***************************
 +           Name: frag_test
 +         Engine: MyISAM
 +        Version: 10
 +     Row_format: Dynamic
 +           Rows: 2
 + Avg_row_length: 20
 +    Data_length: 60
 +Max_data_length: 281474976710655
 +   Index_length: 1024
 +      Data_free: 20
 + Auto_increment: NULL
 +    Create_time: 2011-02-23 14:55:27
 +    Update_time: 2011-02-23 15:07:49
 +     Check_time: NULL
 +      Collation: latin1_swedish_ci
 +       Checksum: NULL
 + Create_options: 
 +        Comment: 
 +1 row in set (0.00 sec)
 +</code>
 +
 +Notice the "data_free" column shows the space left by the second row that we deleted. Imagine you had 20,000 rows. They would take 400k bytes of space. Now if you deleted 19,999 rows, there are 20bytes of useful space in the table, but MySQL will still read 400k and data_free will show 39980.
 +
 +Eliminating fragmentation
 +Luckily MySQL comes with a simple way to fix this once you've identified it. It's called optimize table. Take a look:
 +
 +<code>
 +(root@localhost) [frag_test]> optimize table frag_test;
 ++---------------------+----------+----------+----------+
 +| Table               | Op       | Msg_type | Msg_text |
 ++---------------------+----------+----------+----------+
 +| frag_test.frag_test | optimize | status   | OK       
 ++---------------------+----------+----------+----------+
 +1 row in set (0.00 sec)
 +
 +(root@localhost) [frag_test]> show table status from frag_test\G;
 +*************************** 1. row ***************************
 +           Name: frag_test
 +         Engine: MyISAM
 +        Version: 10
 +     Row_format: Dynamic
 +           Rows: 2
 + Avg_row_length: 20
 +    Data_length: 40
 +Max_data_length: 281474976710655
 +   Index_length: 1024
 +      Data_free: 0
 + Auto_increment: NULL
 +    Create_time: 2011-02-23 14:55:27
 +    Update_time: 2011-02-23 15:11:05
 +     Check_time: 2011-02-23 15:11:05
 +      Collation: latin1_swedish_ci
 +       Checksum: NULL
 + Create_options: 
 +        Comment: 
 +1 row in set (0.00 sec)
 +</code>
 +
 +Performance considerations
 +OPTIMIZE TABLE will lock the entire table while doing its work. For small tables this will work fine because the whole table can be read and written quickly. For very large tables, this can take a very long time and interrupt or reduce available to your application. What to do?
 +
 +Luckily MySQL has a great feature called Master-Master replication. In this configuration, your backend database is actually two separate databases, one active and one passive. Both of the databases are identical in every way. To perform the operations online - including the OPTIMIZE TABLE operation, simply run them on your PASSIVE database. This will not interrupt your application one iota. Once the operation has completed, switch roles so that your application is pointing to your secondary database and make it active. Then make or original database the passive one.
 +
 +Now that the roles are switched and the application is happily pointing at db2, perform the same OPTIMIZE TABLE operation on db1. It's now the passive database, so it won't interrupt the primary either.
 +
 +Other commands
 +Show all the fragmented tables in your database:
 +
 +<code>
 +(root@localhost) [(none)]> select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;
 ++--------------+-----------------------------+-----------+--------+
 +| table_schema | table_name                  | data_free | engine |
 ++--------------+-----------------------------+-----------+--------+
 +| aitc         | wp_comments                    346536 | MyISAM | 
 +| aitc         | wp_options                  |     64308 | MyISAM | 
 +| aitc         | wp_postmeta                       124 | MyISAM | 
 +| cactidb      | poller_item                       160 | MyISAM | 
 +| cactidb      | poller_output                     384 | MyISAM | 
 +| drupal       | sessions                    |     30976 | MyISAM | 
 +| drupal       | users                              92 | MyISAM | 
 +| drupal       | variable                    |        20 | MyISAM | 
 +| gg           | wp_comments                       232 | MyISAM | 
 +| gg           | wp_options                  |       696 | MyISAM | 
 +| gg           | wp_postmeta                       560 | MyISAM | 
 +| ihi          | wp_comments                       536 | MyISAM | 
 +| ihi          | wp_options                  |       444 | MyISAM | 
 +| ihi          | wp_postmeta                       288 | MyISAM | 
 +| ihi          | wp_redirection_items        |      1292 | MyISAM | 
 +| ihi          | wp_redirection_logs            140352 | MyISAM | 
 +| nds          | wp_comments                      4704 | MyISAM | 
 +| nds          | wp_options                  |    150580 | MyISAM | 
 +| nds          | wp_postmeta                        76 | MyISAM | 
 +| oos          | wp_comments                    317124 | MyISAM | 
 +| oos          | wp_options                  |     88196 | MyISAM | 
 +| oos          | wp_postmeta                        76 | MyISAM | 
 +| phplist      | phplist_listuser            |       252 | MyISAM | 
 +| phplist      | phplist_sendprocess                52 | MyISAM | 
 +| phplist      | phplist_user_user               32248 | MyISAM | 
 +| phplist      | phplist_user_user_attribute |       120 | MyISAM | 
 +| phplist      | phplist_user_user_history         288 | MyISAM | 
 +| phplist      | phplist_usermessage              1428 | MyISAM | 
 +| pn_nds       | nuke_session_info               12916 | MyISAM | 
 +| psa          | exp_event                       10024 | MyISAM | 
 +| test         | active_sessions                 30144 | MyISAM | 
 ++--------------+-----------------------------+-----------+--------+
 +31 rows in set (0.26 sec)
 +</code>
 +
 +You can also cause a table to be defragmented if you change the storage engine. That's because MySQL has to read the entire table, and write it back to disk using the new storage engine, and in the process the rows and data gets compressed down more efficiently.
 +
 +Here's what that looks like:
 +
 +<code>
 +(root@localhost) [frag_test]> alter table frag_test engine = innodb;
 +Query OK, 2 rows affected (0.17 sec)
 +Records: 2  Duplicates: 0  Warnings: 0
 +
 +(root@localhost) [frag_test]> show table status from frag_test
 +    -> \G;
 +*************************** 1. row ***************************
 +           Name: frag_test
 +         Engine: InnoDB
 +        Version: 10
 +     Row_format: Compact
 +           Rows: 2
 + Avg_row_length: 8192
 +    Data_length: 16384
 +Max_data_length: 0
 +   Index_length: 0
 +      Data_free: 0
 + Auto_increment: NULL
 +    Create_time: 2011-02-23 15:41:12
 +    Update_time: NULL
 +     Check_time: NULL
 +      Collation: latin1_swedish_ci
 +       Checksum: NULL
 + Create_options: 
 +        Comment: InnoDB free: 7168 kB
 +1 row in set (0.00 sec)
 +</code>
 +
 +Conclusion
 +Fragmentation is something to keep an eye on but not necessarily to obsess over. If you see a few tables with a very large data_free value, it can be worthwhile to OPTIMIZE those, as read performance on the table will improve afterward.
 +
 +
 +
 +----
 +Source: www.databasejournal.com/features/mysql/article.php/3927871
howtos/mysql_optimize.txt · Last modified: d/m/Y H:i (external edit)