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磁盘
　　

• [root@dg53 ~]# lvcreate -n mydata -L 1G VolGroup00
  
•   Volume Groups with the clustered attribute will be inaccessible.
  
•   Logical volume "mydata" created
  
• 
  
• [root@dg53 ~]# lvs
  
•   Skipping clustered volume group new_vg
  
•   LV       VG         Attr   LSize  Origin Snap%  Move Log Copy%  Convert
  
•   LogVol00 VolGroup00 -wi-ao 29.28G
  
•   LogVol01 VolGroup00 -wi-ao 29.28G
  
•   mydata   VolGroup00 -wi-a-  1.00G
  
• 
  
• [root@dg53 ~]# 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
  
• 
  
• [root@dg53 ~]# 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
  
• 
  
• [root@dg53 ~]# 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
  
• 
  
• [root@dg53 ~]# chown mysql.mysql /dev/raw/raw6
  

　　

　　二：修改my.cnf文件，在[mysqld]中添加如下两项！关于数据库的存储是否需要使用裸设备应当在数据库创建前规划好
　　

• [root@dg53 ~]# grep 'innodb_data' /etc/my.cnf
  
• innodb_data_home_dir =
  
• innodb_data_file_path = /dev/raw/raw6:1Gnewraw
  

　　

　　三：初始化mysql数据库，启动mysql服务，在日志中可以看到格式化过程，在未将前面两项配置中的newraw改为raw之前，无法创建innodb类型的表
　　

• [root@dg53 mysql-5.5.25]# sh ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql5.5.25/ --datadir=/mydata/
  
• 
  
• [root@dg53 mysql-5.5.25]# service mysqld start
  
• Starting MySQL....................[  OK  ]
  
• 
  
• [root@dg53 ~]# tail -f /mydata/dg53.yang.com.err
  
• 110601  9:55:51 InnoDB: The InnoDB memory heap is disabled
  
• 110601  9:55:51 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
  
• 110601  9:55:51 InnoDB: Compressed tables use zlib 1.2.3
  
• 110601  9:55:51 InnoDB: Initializing buffer pool, size = 128.0M
  
• 110601  9: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!
  
• 110601  9:55:51  InnoDB: 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
  
• 110601  9:56:09  InnoDB: 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...
  
• 110601  9:56:09  InnoDB: 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
  
• 110601  9:56:10  InnoDB: Waiting for the background threads to start
  
• 110601  9:56:11 InnoDB: 1.1.8 started; log sequence number 0
  
• 110601  9:56:11 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
  
• 110601  9:56:11 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
  
• 110601  9:56:11 [Note] Server socket created on IP: '0.0.0.0'.
  
• 110601  9:56:11 [Note] Event Scheduler: Loaded 0 events
  
• 110601  9:56:11 [Note] /usr/local/mysql5.5.25/bin/mysqld: ready for connections.
  
• Version: '5.5.25-log'  socket: '/tmp/mysql.sock'  port: 3306  Source 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文件，重新启动数据库，建表并插入数据测试
　　

• [root@dg53 ~]# service mysqld stop
  
• Shutting down MySQL.[  OK  ]
  
• 
  
• [root@dg53 ~]# grep 'innodb_data' /etc/my.cnf
  
• innodb_data_home_dir =
  
• innodb_data_file_path = /dev/raw/raw6:1Graw
  
• 
  
• [root@dg53 ~]# service mysqld start
  
• Starting MySQL..[  OK  ]
  
• 
  
• mysql> use bbs;
  
• Database changed
  
• mysql> create table user as select * from mysql.user;
  
• Query OK, 6 rows affected (0.05 sec)
  
• Records: 6  Duplicates: 0  Warnings: 0
  
• 
  
• mysql> insert into user select * from user;
  
• Query OK, 6 rows affected (0.01 sec)
  
• Records: 6  Duplicates: 0  Warnings: 0
  
• 
  
• mysql> insert into user select * from user;
  
• Query OK, 12 rows affected (0.01 sec)
  
• Records: 12  Duplicates: 0  Warnings: 0
  
• 
  
• mysql> insert into user select * from user;
  
• Query OK, 24 rows affected (0.01 sec)
  
• Records: 24  Duplicates: 0  Warnings: 0
  
• 
  
• mysql> insert into user select * from user;
  
• Query OK, 48 rows affected (0.00 sec)
  
• Records: 48  Duplicates: 0  Warnings: 0
  
• 
  
• mysql> insert into user select * from user;
  
• Query OK, 96 rows affected (0.05 sec)
  
• Records: 96  Duplicates: 0  Warnings: 0
  
• 
  
• mysql> insert into user select * from user;
  
• Query OK, 192 rows affected (0.01 sec)
  
• Records: 192  Duplicates: 0  Warnings: 0
  
• 
  
• mysql> insert into user select * from user;
  
• Query OK, 384 rows affected (0.02 sec)
  
• Records: 384  Duplicates: 0  Warnings: 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: 0  Duplicates: 0  Warnings: 0
  

　　

　　五：使用strings命令萃取裸设备中可打印的字符
　　

• [root@dg53 ~]# 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