Showing posts with label Tablespace. Show all posts
Showing posts with label Tablespace. Show all posts

Monday, May 12, 2008

Variable's Day Out #10: innodb_file_per_table

Properties:

Applicable To InnoDB
Server Startup Option --innodb-file-per-table
Scope Global
Dynamic General InnoDB Engine: No
InnoDB plug-in: Yes
Possible Values Enable|Disable Flag
Default Value Disabled
Category Maintenance

Description:

This variable if enabled, makes InnoDB to create a separate .ibd file for storing indexes and data. Setting this variable makes it easy to manage disks with huge tables. Having this option enabled, makes it a lot easier to move bigger (or smaller) .ibd files to separate physical disks and/or have backups of certain tables without affecting others.

As I have mentioned in my earlier post, that InnoDB does not return back the space once claimed for a tablespace, this variable comes handy in managing such situations. Though I have not seen any performance gain while using this option, people do cite such metrics. Again, I would say as always that it may be case dependent.

It should be noted that this option only effects tables that are created after this option is enabled (and server is restarted). Tables that are created with this option disabled are created in the shared tablespace and remain there even if this option is enabled in between. Also, even if all the tables are created with this option enabled, InnoDB still needs the shared tablespace to store its internal data dictionary and undo logs.

Speical case: InnoDB plug-in

With the introduction of InnoDB plug-in, this variable becomes dynamic in nature and holds much more importance than just disk maintenance. You should have this variable enabled to be able to use the new file format. But even in InnoDB plug-in the restriction on tablespaces not returning disk space is intact.

Read More:

Thursday, July 12, 2007

InnoDB Tablespace Size

Unlike MyISAM where data for individual tables is stored in their respective files, InnoDB stores data in a tablespace. By default, there is one single tablespace and data of all the databases is stored in one file. This file has data dictionary, tables, as well as indexes in it. There is a global parameter innodb_data_file_path that defines this tablespace file. It has a syntax like ibdata1:256M:autoextend, this means at the beginning a file of size 256 MB will be created and then whenever the data size exceeds this, the file will be auto-extended. The innodb_autoextend_increment variable defines in MB's that by how much each increment should be.

Let's see how well can we play around:

  1. Inserts: Suppose you have too many inserts and InnoDB is extending the file too frequently. It makes sense to increase the value of innodb_autoextend_increment. Say we increase it to 16MB, then obviously the number of attempts to autoextend tablespace comes down by a factor of 2, hence performance. But beware before you take it too easy and increase the value too much. There is a big trap, we will come to it shortly.
  2. Deletes: Here is the trap. You have a 10 GB tablespace (after too many autoextends), delete some 5 GB data (data + indexes) and think now the tablespace is 5 GB. Wrong, InnoDB doesn't have the notion of giving back space to the file system. Though, it will make sure to use the freed up space for further inserts. So, this method directly cannot be used to free disk space. So, in case you have data which you can get rid of, get rid of quickly before the next autoextend is done. One thing that can be done to reclaim space is to use OPTIMIZE TABLE frequently enough on tables that have high volume of inserts and deletes. But again remember, MySQL locks a table during the time OPTIMIZE TABLE is running. Another Gotcha, right? OPTIMIZE TABLE does several other things for which it makes sense to run it, though not that frequently. I will be posting a blog soon on it. 
  3. Separate Files per Table: InnoDB provides this option where data (data + indexes) for each table can be stored in a separate file through a global variable innodb_file_per_table. Though still a shared tablespace will be created for storing the likes of data dictionary et al. But still this approach makes sense as having data in small chunks (separate files) will improve the scope of managing them well and may increase performance in general.
  4. Fixed Tablespace size: One way to work around with the tablespace file size problems is to fix the tablespace size (remove autoextend) to an extrapolated value. So, when you hit the limit, you know it is time to cleanup. This is not that viable with all the applications, as extrapolation is not always possible. And also it increases the complexity of the application, which then needs to take care of all such error conditions and not lose any data.

So, where does this end? You need to figure out what your data is, how critical it is, what all you want to do with it, what all you want your data to do. Then take some of the following steps.

  1. Move to MyISAM: For all the tables (or even databases), for which you feel data is not that critical to have transactions et al, move them to MyISAM. So, for the problem we can't solve completely, we destroy the problem.
  2. Separate Tablespace: Its a lot easier to maintain 10 small problems than a single big one.
  3. Delete data/OPTIMIZE TABLE: Figure out how soon you can get rid of data. You actually don't need to delete data as it is. Transfer it to a MyISAM table, compress the file and archive it somewhere else and then delete it from the main table. Likewise there are many ways to do it. Run OPTIMIZE TABLE frequently enough so that it doesn't bother your reads and writes too much and also it doesn't take too much time to run.

Hope this blog was helpful to you. Keep posting your comments.

Monday, July 9, 2007

Table Status

Checking out the state of tables of your MySQL setup in production is important for fine tuning server parameters. MySQL's SHOW TABLE STATUS command comes very handy in determining table states from a running environment. Here, I explain the output of the command.

mysql> SHOW TABLE STATUS LIKE 'mytable' \G
****************** 1. row ******************
           Name: mytable
         Engine: InnoDB
        Version: 9
     Row_format: Dynamic
           Rows: 2465222
 Avg_row_length: 370
    Data_length: 913326080
Max_data_length: NULL
   Index_length: 146817024
      Data_free: 0
 Auto_increment: 18357110
    Create_time: 2007-05-11 22:27:56
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 869376 kB
1 row in set (0.34 sec)

Leaving behind the obvious parameters, we shall see only the more relevant ones and ones that look like more interesting.

Row_format: This describes the length behavior of the rows in this table. Can have one value out of Dynamic, Fixed or Compressed. Dynamic rows have variable length due to fields having type like BLOB, VARCHAR etc. Fixed rows are made up of fields that do not vary in length. Compressed rows exist for compressed tables as those in Compressed MyISAM.

Rows: This describes the number of rows in a table. This number is always accurate for non-transactional engines like MyISAM, but an approximate value for transactional tables like InnoDB. Ever wondered why select count(*) is faster on MyISAM than InnoDB.

Avg_row_length: The name says it all. Anyhow, it's in bytes.

Data_length: This is the total data (in bytes) in the table. For InnoDB tables, which tend to use a single tablespace, this can be a deterministic factor for splitting tables in their individual tablespaces.

Max_data_lenth: This is generally not applicable for all the engines. This describes the maximum data that a table can hold. MyISAM tables generally have this restriction.

Index_length: This parameter should be of utmost interest. This describes the total space (in bytes) taken up by the index of the table. The aggregate value of this field over all the tables under the same engine (or the engines that use same buffer for index data) can actually be a deterministic value for such buffers. For InnoDB tables, this can also play a role in splitting tables in their individual tablespaces.

As far as this moment is concerned, this much of explanation is enough. Users should take care of not going by the instinct of a single snap shot for this data. Rather this data should be collected periodically to actually see the change and decisions be made accordingly.

Hope this blog was helpful to you. Keep posting your comments.