Configure the raw device (RawDevice) for the InnoDB engine of the MySQL database)

MySQL's InnoDB Storage engine can not only cache indexes, but also cache data. If you store tables and indexes on a raw device (RawDevice), this bypasses the file

MySQL's InnoDB Storage engine can not only cache indexes, but also cache data. If you store tables and indexes on Raw devices, this bypasses the file

The InnoDB Storage engine of MySQL not only caches indexes, but also caches data. If you store tables and indexes on Raw devices, by bypassing the file system's high-speed cache and buffer, direct access to the disk will greatly reduce the burden on the Linux File System and significantly improve the system performance.

In addition, from the Optimization Principle of bare devices in database applications, we can also see a basic direction for database optimization, is how to reduce the burden on the file system caused by database-specific I/O-intensive access. Therefore, even on the existing file system-based database storage engine, you can also consider using a special file system mount method.
For example, if noatime is used as the Mount parameter for the partitions that store database files, the number of access times is no longer recorded, which can improve the system performance to a certain extent.

── ─
The latest system environment configuration in this note:
── ─
OS: CentOS6
HDD:/dev/sdc/dev/sdd
RAW:/dev/raw/raw1/dev/raw/raw2
MySQL: 5.1.61
USER: mysql

Refer to the Configuration Guide on the official website:

── ─
(1) set the raw device (Prepare the raw device)
── ─
Before using a bare device, you must first bind the disk device to the bare device:

#/Bin/raw/dev/raw/raw1/dev/sdc;
--------------------------------------------------------------------------------
/Dev/raw/raw1: bound to major 8, minor 32
--------------------------------------------------------------------------------

Bind another bare device, and then authorize the running user of your MySQL database to read and write the bare device:

#/Bin/raw/dev/raw/raw2/dev/sdd;
# Chown root: mysql/dev/raw/raw1/dev/raw/raw2;
# Chmod 0660/dev/raw/raw1/dev/raw/raw2;

#/Bin/raw-qa;
#/Bin/raw-q/dev/raw/raw1;
#/Bin/ls-l/dev/raw/raw1;
# Blockdev -- report/dev/raw/raw1;
# Blockdev -- report/dev/sdc;

Vi/etc/udev/rules. d/60-raw.rules;
--------------------------------------------------------------------------------
ACTION = "add", KERNEL = "sdc", GROUP = "mysql", MODE = "0660 ", RUN + = "/bin/raw/dev/raw/raw1% N"
ACTION = "add", KERNEL = "sdd", GROUP = "mysql", MODE = "0660 ", RUN + = "/bin/raw/dev/raw/raw2% N"
--------------------------------------------------------------------------------
Note: this make sure device/dev/sdc and/dev/sdd will bind automatically when server reboot.

# Vi/etc/udev/rules. d/41-local-permissions-rules;

── ─
(2) back up the old InnoDB data table as needed
── ─
If your database is running and the existing data is stored in the old InnoDB engine,
Use the mysqldump command to export your data before shutting down the database, and import the data after the new engine is configured.

You can refer to the following SQL command to view and export your InnoDB data table:

Mysql> SELECT table_schema, table_name, engine FROM INFORMATION_SCHEMA.TABLES;
Mysql> SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb ';

If necessary, use mysqldump to export data. See the following format (use the corresponding database and table name ):

Mysqldump-u root-p-h localhost [database]. [table]> database. table. SQL

Note: You must export old data from tables one by one.

── ─
(3) MySQL configuration during initialization
── ─
When you create a new data file, put the keyword newraw
Immediately after the data file size in innodb_data_file_path:

# Vi/etc/my. cnf;
--------------------------------------------------------------------------------
[Mysqld]
Innodb_buffer_pool_size = 128 M
Innodb_data_home_dir =
Innodb_data_file_path =/dev/raw/raw1: 64 Mnewraw;/dev/raw/raw2: 64 Mnewraw
--------------------------------------------------------------------------------