Mysql5.5使用裸设备存储
Mysql的innodb支持把表和索引存储在裸设备中，存储的I/O等待问题一直是影响数据库性能的关键，裸设备不需要经过文件系统I/O处理，因而在性能上有一定的提升，在使用rhel中的kvm虚拟机的时候有明显的感觉，使用裸设备+virtio的性能基本与物理机没差别；在Oracle中，裸设备从11g开始就不被支持，而10g rac的ocr和votedisk还必须使用裸设备存储，Oracle推荐使用ASM来提升存储的I/O性能，ASM支持条带，冗余和在线添加删除磁盘组等等高级功能，同时有具有一定的可管理性；相比之下Mysql的裸设备就比较脆弱，或许在分布式基础上，Mysql单实例的数据量还不需要用裸设备来提升I/0性能，下面来演示下如何在Mysql上使用裸设备存储innodb存储类型的表和索引一：创建LVM卷，并使用raw来绑定，其中raw1-raw5为oracle的asm磁盘
[*]# lvcreate -n mydata -L 1G VolGroup00
[*]Volume Groups with the clustered attribute will be inaccessible.
[*]Logical volume "mydata" created
[*]
[*]# lvs
[*]Skipping clustered volume group new_vg
[*]LV VG Attr LSizeOrigin Snap%Move Log Copy%Convert
[*]LogVol00 VolGroup00 -wi-ao 29.28G
[*]LogVol01 VolGroup00 -wi-ao 29.28G
[*]mydata VolGroup00 -wi-a-1.00G
[*]
[*]# cat /etc/sysconfig/rawdevices
[*]/dev/raw/raw1 /dev/sdb1
[*]/dev/raw/raw2 /dev/sdc1
[*]/dev/raw/raw3 /dev/sdd1
[*]/dev/raw/raw4 /dev/sde1
[*]/dev/raw/raw5 /dev/sdf1
[*]
[*]/dev/raw/raw6/dev/VolGroup00/mydata
[*]
[*]# service rawdevices restart
[*]Assigning devices:
[*] /dev/raw/raw1--> /dev/sdb1
[*]/dev/raw/raw1:bound to major 8, minor 17
[*] /dev/raw/raw2--> /dev/sdc1
[*]/dev/raw/raw2:bound to major 8, minor 33
[*] /dev/raw/raw3--> /dev/sdd1
[*]/dev/raw/raw3:bound to major 8, minor 49
[*] /dev/raw/raw4--> /dev/sde1
[*]/dev/raw/raw4:bound to major 8, minor 65
[*] /dev/raw/raw5--> /dev/sdf1
[*]/dev/raw/raw5:bound to major 8, minor 81
[*] /dev/raw/raw6--> /dev/VolGroup00/mydata
[*]/dev/raw/raw6:bound to major 253, minor 2
[*]done
[*]
[*]# raw -qa
[*]/dev/raw/raw1:bound to major 8, minor 17
[*]/dev/raw/raw2:bound to major 8, minor 33
[*]/dev/raw/raw3:bound to major 8, minor 49
[*]/dev/raw/raw4:bound to major 8, minor 65
[*]/dev/raw/raw5:bound to major 8, minor 81
[*]/dev/raw/raw6:bound to major 253, minor 2
[*]
[*]# chown mysql.mysql /dev/raw/raw6
二：修改my.cnf文件，在中添加如下两项！关于数据库的存储是否需要使用裸设备应当在数据库创建前规划好
[*]# grep 'innodb_data' /etc/my.cnf
[*]innodb_data_home_dir =
[*]innodb_data_file_path = /dev/raw/raw6:1Gnewraw
三：初始化mysql数据库，启动mysql服务，在日志中可以看到格式化过程，在未将前面两项配置中的newraw改为raw之前，无法创建innodb类型的表
[*]# sh ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql5.5.25/ --datadir=/mydata/
[*]
[*]# service mysqld start
[*]Starting MySQL....................
[*]
[*]# tail -f /mydata/dg53.yang.com.err
[*]1106019:55:51 InnoDB: The InnoDB memory heap is disabled
[*]1106019:55:51 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
[*]1106019:55:51 InnoDB: Compressed tables use zlib 1.2.3
[*]1106019:55:51 InnoDB: Initializing buffer pool, size = 128.0M
[*]1106019:55:51 InnoDB: Completed initialization of buffer pool
[*]InnoDB: The first specified data file /dev/raw/raw6 did not exist:
[*]InnoDB: a new database to be created!
[*]1106019:55:51InnoDB: Setting file /dev/raw/raw6 size to 1024 MB
[*]InnoDB: Database physically writes the file full: wait...
[*]InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
[*]1106019:56:09InnoDB: Log file ./ib_logfile0 did not exist: new to be created
[*]InnoDB: Setting log file ./ib_logfile0 size to 5 MB
[*]InnoDB: Database physically writes the file full: wait...
[*]1106019:56:09InnoDB: Log file ./ib_logfile1 did not exist: new to be created
[*]InnoDB: Setting log file ./ib_logfile1 size to 5 MB
[*]InnoDB: Database physically writes the file full: wait...
[*]InnoDB: Doublewrite buffer not found: creating new
[*]InnoDB: Doublewrite buffer created
[*]InnoDB: 127 rollback segment(s) active.
[*]InnoDB: Creating foreign key constraint system tables
[*]InnoDB: Foreign key constraint system tables created
[*]1106019:56:10InnoDB: Waiting for the background threads to start
[*]1106019:56:11 InnoDB: 1.1.8 started; log sequence number 0
[*]1106019:56:11 Server hostname (bind-address): '0.0.0.0'; port: 3306
[*]1106019:56:11 - '0.0.0.0' resolves to '0.0.0.0';
[*]1106019:56:11 Server socket created on IP: '0.0.0.0'.
[*]1106019:56:11 Event Scheduler: Loaded 0 events
[*]1106019:56:11 /usr/local/mysql5.5.25/bin/mysqld: ready for connections.
[*]Version: '5.5.25-log'socket: '/tmp/mysql.sock'port: 3306Source distribution
[*]InnoDB: A new raw disk partition was initialized:
[*]InnoDB: we do not allow database modifications by the user.
[*]InnoDB: Shut down mysqld and edit my.cnf so that newraw is replaced with raw.
[*]
[*]mysql> create database bbs;
[*]Query OK, 1 row affected (0.00 sec)
[*]
[*]mysql> use bbs;
[*]Database changed
[*]mysql> create table user as select * from mysql.user;
[*]ERROR 1005 (HY000): Can't create table 'bbs.user' (errno: -1)
四：关闭数据库后，修改my.cnf文件，重新启动数据库，建表并插入数据测试
[*]# service mysqld stop
[*]Shutting down MySQL.
[*]
[*]# grep 'innodb_data' /etc/my.cnf
[*]innodb_data_home_dir =
[*]innodb_data_file_path = /dev/raw/raw6:1Graw
[*]
[*]# service mysqld start
[*]Starting MySQL..
[*]
[*]mysql> use bbs;
[*]Database changed
[*]mysql> create table user as select * from mysql.user;
[*]Query OK, 6 rows affected (0.05 sec)
[*]Records: 6Duplicates: 0Warnings: 0
[*]
[*]mysql> insert into user select * from user;
[*]Query OK, 6 rows affected (0.01 sec)
[*]Records: 6Duplicates: 0Warnings: 0
[*]
[*]mysql> insert into user select * from user;
[*]Query OK, 12 rows affected (0.01 sec)
[*]Records: 12Duplicates: 0Warnings: 0
[*]
[*]mysql> insert into user select * from user;
[*]Query OK, 24 rows affected (0.01 sec)
[*]Records: 24Duplicates: 0Warnings: 0
[*]
[*]mysql> insert into user select * from user;
[*]Query OK, 48 rows affected (0.00 sec)
[*]Records: 48Duplicates: 0Warnings: 0
[*]
[*]mysql> insert into user select * from user;
[*]Query OK, 96 rows affected (0.05 sec)
[*]Records: 96Duplicates: 0Warnings: 0
[*]
[*]mysql> insert into user select * from user;
[*]Query OK, 192 rows affected (0.01 sec)
[*]Records: 192Duplicates: 0Warnings: 0
[*]
[*]mysql> insert into user select * from user;
[*]Query OK, 384 rows affected (0.02 sec)
[*]Records: 384Duplicates: 0Warnings: 0
[*]
[*]mysql> commit;
[*]Query OK, 0 rows affected (0.00 sec)
[*]
[*]mysql> create index i_user_host on user(host);
[*]Query OK, 0 rows affected (0.16 sec)
[*]Records: 0Duplicates: 0Warnings: 0
五：使用strings命令萃取裸设备中可打印的字符
[*]# strings /dev/raw/raw6 |grep dg53.yang.com |head
[*]Vdg53.yang.com
[*]rdg53.yang.com root
[*]dg53.yang.com
[*]dg53.yang.com root
[*]dg53.yang.com
[*]dg53.yang.com root
[*]Rdg53.yang.com
[*]dg53.yang.com root
[*]Vdg53.yang.com
[*]rdg53.yang.com root
页:
[1]