innodb_data_file_path和innodb_data_home_dir

千家信息网最后更新 2025年02月02日innodb_data_file_path和innodb_data_home_dir今天研究下关于mysql数据的相关参数以及innodb引擎下的共享表空间和独立表空间介绍，参数主要包括innodb_data_file_path、innodb_data_home_dir、innodb_buffer_pool_size、innodb_buffer_pool_instances四个参数。 一：首先介绍innodb_buffer_pool_size 我们知道mysql数据库在操作数据页的时候，会先把数据页pin到内存中，之后再做相关的处理，那么mysql可以使用的mysql的内存大小就是由innodb_buffer_pool_size参数控制的，这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲。在高并发高I/O时正确的配置非常重要，可能带来很大的性能提升，这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有 InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。这个参数是非动态的，要修改这个值，需要重启mysqld服务 # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. #innodb，不像myisam，使用缓冲池来缓存索引和 #行数据。您设置的越大，那么访问表中的数据所需的磁盘I /o就越少。 #在专用的数据库服务器上，您可以设置这个 #参数最多80%的计算机物理内存大小。不要设置它 #太大了，因为物理内存的竞争可能 #导致操作系统中的分页。注意，在32位系统上 #可能仅限于每个进程的2-3.5g用户级内存，所以不要 把它定得太高了。 二：innodb_data_home_dir和innodb_data_file_path参数 innodb_data_file_path用来指定innodb 共享 表空间文件，如果我们不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默认会在datadir目录下创建ibdata1 作为innodb tablespace。 虽然不指定innodb_data_home_dir和指定它为空显示是一样的，但是作用确不一样，如果不指定那么所有的innodb tablespace 文件只能存放在datadir目录下。如果显示指定为空，则可以为innodb tablespace 文件指定不同的路径 1. 在同一个路径下指定多个innodb tablespace 文件 1) 在my.cnf文件指定如下 [mysqld] innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M note：The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line. note：因为没有指定innodb_data_home_dir，所以ibdata1和ibdata2都在datadir(innodb_data_home_dir默认值为datadir)目录下创建 2) 在my.cnf文件指定如下 [mysqld] innodb_data_home_dir = /data/mysql/mysql3306/data innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M note：可以显示的指定innodb tablespace文件的路径，如果指定innodb_data_home_dir，那么就不能为innodb tablespace不同文件指定不同路径 2. 在不同路径下指定innodb tablespace 在my.cnf文件下指定 [mysqld] innodb_data_home_dir = innodb_data_file_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend note：如果想为innodb tablespace指定不同目录下的文件，必须指定innodb_data_home_dir =。这个例子中会在datadir下建立ibdata1，在/data/mysql/mysql3306/data1/目录下创建ibdata2 如果我不指定innodb_data_home_dir =，只在my.cnf文件中做如下指定： innodb_data_file_path =/data/data/ibdata1:18m;/data/data/1ibdata2:100m:autoextend:max:2000M bogon:root@~>service mysqld start Starting MySQL. ERROR! The server quit without updating PID file (/tmp/mysql.pid). 查看错误日志。。。如下 bogon:root@/>tail -n 100 /data/log/error.log 171127 21:08:16 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. InnoDB: File name .//data/data/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. ##所以如果想为innodb tablespace文件指定不同路径，那么必须在my.cnf文件中指定innodb_data_home_dir = 注意： 1）设定文件大小时一定要注意你的OS是否有最大文件尺寸为2GB的限制！InnoDB是不会注意你的OS文件尺寸限制的， 在一些文件系统中你可能要设定最大容量限制： innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M 2）如果是修改共享表空间的文件个数，注意在描述原来文件的时候，一定要找到该文件，查看他当前的实际大小，然后写在innodb_data_file_path =哪个文件的后面。否则会报错。 三：innodb_buffer_pool_instances innodb缓冲池被划分为多个内存缓冲池的数量。对于具有多GB范围的缓冲池的系统，将缓冲池划分为多个缓冲池可以提高并发性，减少对不同线程读取和写入到缓存时页面的争用。从缓冲池中存储或读取的每个页面都随机分配给其中一个缓冲池实例，使用哈希函数。每个缓冲池管理自己的自由列表、刷新列表、LRU和连接到缓冲池的所有其他数据结构，并由自己的buffer pool mutex（互斥体）保护。 只有当您将innodb _ buffer _ pool _size设置为1gb或更高的大小时，此选项才会生效。所有缓冲池的大小之和是您指定的总的缓存池大小。为了获得最佳效率，请指定innodb _ buffer _ pool _instances和innodb _ buffer _ pool _size的组合，以便每个缓冲池实例至少为1gb。 在MySQL 5.6.6之前,默认是1， 默认值 在MySQL 5.6.6和更高版本在32位系统取决于innodb_buffer_pool_size的值 1 innodb_buffer_pool_instances可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。 2 innodb_buffer_pool_instances 参数显著的影响测试结果，特别是非常高的 I/O 负载时。 3 实验环境下， innodb_buffer_pool_instances=8 在很小的 buffer_pool 大小时有很大的提升，而使用大的 buffer_pool 时，innodb_buffer_pool_instances=1 的表现最棒。 最后说说mysql的innodb引擎下的表空间： 一、针对innodb引擎，分为两种情况一种是共享表空间存储方式，还有一种是独享表空间存储方式。 共享表空间： Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。 独立表空间： 而当使用独享表空间来存放Innodb的表的时候，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了 针对myasam引擎： MySQL单表最大限制就已经扩大到了64PB了（官方文档显示）。也就是说，从目前的技术环境来看，MySQL数据库的MyISAM存储 引擎单表大小限制已经不是有MySQL数据库本身来决定，而是由所在主机的OS上面的文件系统来决定了。 二、查看数据库的表空间 MySQL [(none)]> show variables like 'innodb_data%';
+-----------------------+----------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------+----------------------------------------------------------------------+
| innodb_data_file_path | /data/data/ibdata1:100m;/data/data1/ibdata2:100m:autoextend:max:2000M |
| innodb_data_home_dir | |
+-----------------------+----------------------------------------------------------------------+
表空间有四个文件组成：ibdata1、ibdata2每个文件的大小为100M，当ibdata1文件满了的时候，ibdata2会自动扩展； 当前的存储空间满的时候，可以在其他的磁盘添加数据文件，语法如下：语法如下所示： pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]] 如果用 autoextend 选项描述最后一个数据文件，当 InnoDB 用尽所有表自由空间后将会自动扩充最后一个数据文件，每次增量为 8 MB。示例： 不管是共享表空间和独立表空间，都会存在innodb_data_file文件，因为这些文件不仅仅要存放数据，而且还要充当着类似于ORACLE的UNDO表空间等一些角色。 三、共享表空间优缺点 既然Innodb有共享表空间和独立表空间两种类型，那么这两种表空间存在肯定都有时候自己的应用的场景，存在即合理。以下是摘自mysql官方的一些介绍： 3.1 共享表空间的优点 表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制，方便扩容，放到多个磁盘磁盘上可以分散io,提高性能。 3.2 共享表空间的缺点 所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间，对于经常删除操作的这类应用最不适合用共享表空间。 共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了（可以理解为oracle的表空间10G，但是才使用10M，但是操作系统显示的表空间为10G），进行数据库的冷备很慢； 四、独立表空间的优缺点 4.1 独立表空间的优点 每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。 空间可以回收（除drop table操作外，表空不能自已回收） Drop table操作自动回收表空间，如果对于统计分析或是日志表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能； 4.2 独立表空间的缺点 单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法，比如做软连接； 五、共享表空间和独立表空间之间的转换 5.1 查看当前数据库的表空间管理类型 mysql> show variables like "innodb_file_per_table"; ON代表独立表空间管理，OFF代表共享表空间管理；（查看单表的表空间管理方式，需要查看每个表是否有单独的数据文件） 5.2 修改数据库的表空间管理方式 修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间； innodb_file_per_table=1 为使用独占表空间 innodb_file_per_table=0 为使用共享表空间 题外话 碎片的产生： MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。 针对oracle行链接和行迁移：新insert的时候，可能发生行链接；update已经存在的行时，可能发生行迁移； 行链接产生在第一次插入数据的时候如果一个block不能存放一行记录的情况下。这种情况下，Oracle将使用链接一个或者多个在这个段中保留的block存储这一行记录，行链接比较容易发生在比较大的行上，例如行上有LONG、LONG RAW、LOB等数据类型的字段，这种时候行链接是不可避免的会产生的。 当一行记录初始插入的时候事可以存储在一个block中的，由于更新操作导致行长增加了，而block的自由空间已经完全满了，这个时候就产生了行迁移。在这种情况下，Oracle将会迁移整行数据到一个新的block中（假设一个block中可以存储下整行数据），Oracle会保留被迁移行的原始指针指向新的存放行数据的block，这就意味着被迁移行的ROW ID是不会改变的。 当发生了行迁移或者行链接，对这行数据操作的性能就会降低，因为Oracle必须要扫描更多的block来获得这行的信息。 总结：原来mysql也有类似于oracle的undo表空间，也就是innodb共享表空间（ibdata1），默认就是自动扩展的，每次扩展8M，共享表空间类似于oracle的表空间，可以实现把mysql的数据和索引放到不同的磁盘下，分散io，进而提高性能，就是不像oracle那样可以动态的给表空间添加文件，mysql如果想要添加共享表空间的文件，需要重启mysql服务才能生效。