14.5.8 Using Raw Disk Partitions for the Shared Tablespace

You can use raw disk partitions as data files in the InnoDB system tablespace. This technique enables nonbuffered I/O on Windows and on some Linux and Unix systems without file system overhead. Perform tests with and without raw partitions to verify whether this change actually improves performance on your system.

When you use a raw disk partition, ensure that the user ID that runs the MySQL server has read and write privileges for that partition. For example, if you run the server as the mysql user, the partition must be readable and writeable by mysql. If you run the server with the --memlock option, the server must be run as root, so the partition must be readable and writeable by root.

The procedures described below involve option file modification. For additional information, see Section 4.2.6, “Using Option Files”.

Allocating a Raw Disk Partition on Linux and Unix Systems

  1. When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option. The partition must be at least as large as the size that you specify. Note that 1MB in InnoDB is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
    
  2. Restart the server. InnoDB notices the newraw keyword and initializes the new partition. However, do not create or change any InnoDB tables yet. Otherwise, when you next restart the server, InnoDB reinitializes the partition and your changes are lost. (As a safety measure InnoDB prevents users from modifying data when any partition with newraw is specified.)

  3. After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
    
  4. Restart the server. InnoDB now permits changes to be made.

Allocating a Raw Disk Partition on Windows

On Windows systems, the same steps and accompanying guidelines described for Linux and Unix systems apply except that the innodb_data_file_path setting differs slightly on Windows.

  1. When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Gnewraw
    

    The //./ corresponds to the Windows syntax of \\.\ for accessing physical drives. In the example above, D: is the drive letter of the partition.

  2. Restart the server. InnoDB notices the newraw keyword and initializes the new partition.

  3. After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Graw
    
  4. Restart the server. InnoDB now permits changes to be made.