When (and how) to move an InnoDB table outside the shared tablespace - Peter Zaitsev

http://ift.tt/1whh876

In my last post, “A closer look at the MySQL ibdata1 disk space issue and big tables,” I looked at the growing ibdata1 problem under the perspective of having big tables residing inside the so-called shared tablespace. In the particular case that motivated that post, we had a customer running out of disk space in his server who was looking for a way to make the ibdata1 file shrink. As you may know, that file (or, as explained there, the set of ibdata files composing the shared tablespace) stores all InnoDB tables created when innodb_file_per_table is disabled, but also other InnoDB structures, such as undo logs and data dictionary.

For example, when you run a transaction involving InnoDB tables, MySQL will first write all the changes it triggers in an undo log, for the case you later decide to “roll them back”. Long standing, uncommited transactions are one of the causes for a growing ibdata file. Of course, if you have innodb_file_per_table disabled then all your InnoDB tables live inside it. That was what happened in that case.

So, how do you move a table outside the shared tablespace and change the storage engine it relies on? As importantly, how does that affects disk space use? I’ve explored some of the options presented in the previous post and now share my findings with you below.

The experiment

I created a very simple InnoDB table inside the shared tablespace of a fresh installed Percona Server 5.5.37-rel35.1 with support for TokuDB and configured it with a 1GB buffer pool. I’ve used a 50G partition to host the ‘datadir’ and another one for ‘tmpdir’:

  Filesystem                 Size Used Avail Use% Mounted on  /dev/mapper/vg0-lvFernando1 50G 110M  47G   1%  /media/lvFernando1  # datadir  /dev/mapper/vg0-lvFernando2 50G  52M  47G   1%  /media/lvFernando2  # tmpdir  

Here’s the table structure:

  CREATE TABLE `joinit` (    `i` int(11) NOT NULL AUTO_INCREMENT,    `s` varchar(64) DEFAULT NULL,    `t` time NOT NULL,    `g` int(11) NOT NULL,    PRIMARY KEY (`i`)  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;  

I populated it with 134 million rows using the following routine:

  INSERT INTO joinit VALUES (NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )));  INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;  # repeat until you reach your target number of rows  

which resulted in the table below:

  mysql> show table status from test like 'joinit'G  *************************** 1. row ***************************  Name: joinit  Engine: InnoDB  Version: 10  Row_format: Compact  Rows: 134217909  Avg_row_length: 72  Data_length: 9783214080  Max_data_length: 0  Index_length: 0  Data_free: 1013972992  Auto_increment: 134872552  Create_time: 2014-07-30 20:42:42  Update_time: NULL  Check_time: NULL  Collation: latin1_swedish_ci  Checksum: NULL  Create_options:  Comment:  1 row in set (0.00 sec)  

The resulting ibdata1 file was showing to have 11G, which accounted in practice for 100% of the datadir partition use then. What follows next is a few experiences I did by converting that table to use a different storage engine, moving it outside the shared tablespace, compressing it, and dumping and restoring the database back to see the effects in disk space use. I haven’t timed how long running each command took and focused mostly on the generated files size. As a bonus, I’ve also looked at how to extend the shared table space by adding an extra ibdata file.

#1) Converting to MyISAM

Technical characteristics and features apart, MyISAM tables are know to occupy less disk space than InnoDB’s ones. How much less depends on the actual table structure. Here I made the conversion in the most simplest way:

  mysql> ALTER TABLE test.joinit ENGINE=MYISAM;  

which created the following files (the .frm file already existed):

  $ ls -lh /media/lvFernando1/data/test/  total 8.3G  -rw-rw---- 1 fernando.laudares admin 8.5K Jul 31 16:21 joinit.frm  -rw-rw---- 1 fernando.laudares admin 7.0G Jul 31 16:27 joinit.MYD  -rw-rw---- 1 fernando.laudares admin 1.3G Jul 31 16:27 joinit.MYI  

The resulting MyISAM files amounted for an additional 8.3G of disk space use:

  /dev/mapper/vg0-lvFernando1 50G 19G 29G 40% /media/lvFernando1  

I was expecting smaller files but, of course, the result depends largely on the data types of the columns composing the table. The problem (or the consequence) is that we end up with close to the double of the initial disk space being used:

As it happens with th

Truncated by Planet PHP, read more at the original (another 13357 bytes)

from Planet MariaDB http://ift.tt/1lvLCrP