You can use raw disk partitions as data files in the shared tablespace. By using a raw disk, you can perform nonbuffered I/O on Windows and on some Unix systems without file system overhead. This may improve performance, but you are advised to perform tests with and without raw partitions to verify whether this is actually so on your system.
When you create a new data file, put the keyword
newraw immediately after the data file size in
innodb_data_file_path. 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
The next time you start 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
Then restart the server and InnoDB permits
changes to be made.
On Windows, you can allocate a disk partition as a data file like this:
[mysqld] innodb_data_home_dir= innodb_data_file_path=//./D::10Gnewraw
The //./ corresponds to the Windows syntax of
\\.\ for accessing physical drives.
When you use a raw disk partition, be sure that it has permissions
that enable read and write access by the account used for running
the MySQL server. For example, if you run the server as the
mysql user, the partition must permit read and
write access to mysql. If you run the server with
the --memlock option, the server must
be run as root, so the partition must permit
access to root.
© 2013, Oracle Corporation and/or its affiliates
User Comments
The following does not work on Windows
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw
I think the problem is in "::".
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;
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.
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).
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.
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)
Add your own comment.