MySQL :: MySQL 5.5 Reference Manual :: 14.10.3 Using Raw Disk Partitions for the System Tablespace

MySQL 5.5 Reference Manual / ... / Using Raw Disk Partitions for the System Tablespace

14.10.3 Using Raw Disk Partitions for the System 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

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
```
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.)
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
```
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.

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.
Restart the server. InnoDB notices the newraw keyword and initializes the new partition.
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
```
Restart the server. InnoDB now permits changes to be made.

PREV HOME UP NEXT

User Comments

Posted by M Z on August 26, 2004

The following does not work on Windows
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw
I think the problem is in "::".

Posted by Andrew Button on July 19, 2006

This is how I set up MySQL to use a raw device on RedHat Linux ES4. I could not access the disk partiton directly so I had to create raw devices that link to the disk partitions.

Using parted I created the raw partition (it did not let me add it in the install as there is no mount point).

Assign this partition to a raw device using raw. I.e.

raw /dev/raw/raw1 /dev/cciss/c0d0p5

Then make accessible the raw device to mysql (as mentioned in the docs):

chown mysql dev/raw/raw1

Then use the raw device (as shown in the docs). i.e.

innodb_data_home_dir =
innodb_data_file_path = /dev/raw/raw1:20000Mnewraw;

Posted by Ralf Schwedler on August 16, 2007

Chowning the raw device like in

chown mysql /dev/raw/raw1

will fail after the next reboot, if the /dev-tree is dynamically created by, e.g. udev, a MAKEDEV-script or similar. After reboot, the device will regain its former permission, typically excluding access by mysql.

A permanent solution is (in the case of a udev-generated /dev) to create the file /etc/udev/rules.d/41-local-permissions-rules containing the single line:

KERNEL=="sda5" GROUP="mysql"

to make /dev/sda5 accessible to group mysql.

Posted by Seva E on January 16, 2008

innodb_file_per_table must be set to 0 (default). If it's set (to 1), InnoDB table data files (.ibd) will be created in the datadir instead of in the raw device's shared tablespace even if innodb_data_home_dir is blank.

Not sure if this is a bug or a feature, but this is not what I expected. Tested with mysql-5.0.22-2.1 (RHEL 5).

Posted by Joaquin Alcañiz on April 15, 2008

It works fine on Windows. Follow next steps:
1. Select a primary clear partition and format it on FAT32 (instead NTFS). Assign a drive letter to it, and compute its size in Mb or Gb (asume M: and 30Gb in this example)
2. Edit "my.ini" file and add on it:
innodb_data_home_dir=
innodb_data_file_path=//./M::30Gnewraw (yes, use "::")
3. Start mysqld service and WAIT until disk ends working. (see at LED on front your PC).
4. Stop mysqld service.
5. Edit "my.ini" file and change ("newraw" to "raw"):
innodb_data_file_path=//./M::30Graw
6. Start mysqld service again, you can create and load your databases.

Posted by Nikolay Pelov on April 23, 2011

You might want to use size of raw device in bytes or at least kilobytes, because when creating partitions the size does not match exactly if specified in GB:
innodb_data_file_path = /dev/raw/raw1:199108608newraw;
innodb_data_file_path = /dev/raw/raw1:194442Knewraw;
and NOT like this
innodb_data_file_path = /dev/raw/raw1:200MBnewraw;
(This partition was created as 200 MB)