Mysql innodb configures raw to bypass the kernel buffer and realize direct I/O under Linux

(reposted from linux community)

MySQL's InnoDB storage engine can not only cache indexes, but also cache data. If its tables and indexes are stored on raw devices (Raw Device), they can bypass the cache and buffer of the file system and access them directly. disk, it will greatly reduce the burden on the Linux file system and significantly improve system performance.

In addition, from the optimization principles of bare devices in database applications, we can also see a basic direction of optimizing the database, which is how to reduce the heavy burden on the file system caused by the unique I/O-intensive access of the database. . Therefore, even on existing file system-based database storage engines, special file system mounting methods can be considered.
For example, using noatime as a mount parameter for a partition that stores database files can improve system performance to a certain extent because access times are no longer recorded.

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

Refer to the configuration guide of the official website:

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

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

Then bind another raw device, and then give the running user of your MySQL database the right to read and write the raw 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) If necessary, back up the old InnoDB data table
───────────────────────────────────────────── ───────────────────────────────
If your database is already running and the existing data is stored in the old InnoDB engine Among them, if you need to migrate,
please use the mysqldump command to export your data before closing the database, and then import it after the new engine is configured.

You can refer to the following SQL commands 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, you can use mysqldump to export data. Please refer to the following format (please use the corresponding database and table names):

mysqldump -u root -p -h localhost [database].[table] > database.table.sql

Note: You must export the old data table by table.

──────────────────────────────────────────── ──────────────────────────────
(3) MySQL configuration during initialization phase
───── ─────────────────────────────────────────────── ───────────────────────
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=128M
innodb_data_home_dir=
innodb_data_file_path=/dev/raw/raw1:64Mnewraw;/dev/raw/raw2:64Mnewraw
------------- -------------------------------------------------- ------------------

postscript:

mysql also provides another implementation method, configure innodb_flush_method

in my.ini

innodb_flush_method = O_DIRECT

This can also achieve direct I/O

The above is the detailed content of Mysql innodb configures raw to bypass the kernel buffer and realize direct I/O under Linux. For more information, please follow other related articles on the PHP Chinese website!