如何给MySQL共享表空间扩容

一.什么是共享表空间和独占表空间

共享表空间以及独占表空间都是针对数据的存储方式而言的。

共享表空间:  某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1  初始化为10M。

独占表空间:  每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

两者之间的优缺点

共享表空间：

优点：

可以将表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。

缺点：

所有的数据和索引存放到一个文件中以为着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

独立表空间：在配置文件（my.cnf）中设置： innodb_file_per_table

优点：

1.每个表都有自已独立的表空间。

2.每个表的数据和索引都会存在自己的表空间中。

3.可以实现单表在不同的数据库中移动。

4.空间可以回收

a)  Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

b)  对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

c)  对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：

单表增加过大，如超过100个G

二.共享表空间存放什么东西

当你启用了 innodb_file_per_table，表被存储在他们自己的表空间里，但是共享表空间仍然在存储其它的 InnoDB 内部数据：

（1）数据字典，也就是 InnoDB 表的元数据

（2）change缓冲区

（3）双写缓冲区

（4）回滚段

（5）undo空间

（6）外键约束系统表

因此，我们在初始化ibdata1时，最好设置大一些，这样就可以避免因为在高并发情景下导致ibdata1急剧增大，大大影响性能。

三.什么原因引起ibdata1大小迅速增加

(1)出现Bug

(2)清除事务的速度跟不上,主要是磁盘IO

(3)大事务undo，即使kill了，空间也不能回收

主要从如下方面改进：

(1)并发purge线程够不

(2)磁盘IO

(3)不要用32位系统

(4)尽量减少大事务执行，将大事务进行分拆多个小事务执行

当设置innodb_file_per_table=1启用独立表空间后，ibdata1变很大，常见的原因都是有大活动事务执行很久没有完成或是存在回滚空间中的未清除事务数。

可以在show engine innodb status的TRANSACTIONS部分查看正在执行的活动事务或History list length值来确认原因。

四.如何给共享表空间扩容 场景一：在同一磁盘中给共享表空间的ibdata1扩容操作： 检查my.cnf文件配置的ibdata1大小初始值为1000M，自动增长，如下： innodb_data_home_dir=/apps/dbdat/mariadb10_data3306 innodb_data_file_path=ibdata1:1000M:autoextend 检查数据文件目录中ibdata1实际文件大小为1786773504，如下： -rw-r--r-- 1 apps apps 1786773504 Jul 27 21:29 ibdata1 这里扩容有两个注意的地方： 1.若ibdata1的实际大小没有超过1000M，那么扩容的配置文件中直接写1000M; 2.若ibdata1的实际大小超过了1000M，则扩容的配置文件中写实际的精确大小值，如上面这个场景的操作： (product)root@localhost [(none)]> select 1786773504/1024/1024; +----------------------+ | 1786773504/1024/1024 | +----------------------+ |        1704.00000000 | +----------------------+ 1 row in set (0.00 sec) 更改my.cnf配置,增加一个ibdata2,如下 innodb_data_file_path=ibdata1:1704M;ibdata2:1000M:autoextend  ------这里注意格式，分号和冒号 重启MySQL后，检查新增的ibdata2是否生效，下面表示已有生效。 [apps@mvxl0782 mariadb10_data3306]$ ls -l|grep ibd -rw-r--r-- 1 apps apps 1786773504 Jul 31 18:44 ibdata1 -rw-rw---- 1 apps apps 1048576000 Jul 31 18:44 ibdata2

场景二：在不同磁盘中给共享表空间的ibdata1扩容操作： 根据场景一中扩容的两点注意，更改my.cnf配置,在不同磁盘中增加一个ibdata3,如下 innodb_data_file_path=ibdata1:1704M;ibdata2:1000M;/apps2/dbdat/ibdata3:100M:autoextend 重启mysql时，报下面错： 160731 18:53:29 mysqld_safe mysqld from pid file /apps/dbdat/mariadb10_data3306/mysql.pid ended 160731 18:53:38 mysqld_safe Starting mysqld daemon with databases from /apps/dbdat/mariadb10_data3306 160731 18:53:38 [Note] /apps/svr/mariadb10/bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 15681 ... 2016-07-31 18:53:38 7f83161d9760 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 160731 18:53:38 [Note] InnoDB: Using mutexes to ref count buffer pool pages 160731 18:53:38 [Note] InnoDB: The InnoDB memory heap is disabled 160731 18:53:38 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 160731 18:53:38 [Note] InnoDB: Memory barrier is not used 160731 18:53:38 [Note] InnoDB: Compressed tables use zlib 1.2.3 160731 18:53:38 [Note] InnoDB: Using Linux native AIO 160731 18:53:38 [Note] InnoDB: Using CPU crc32 instructions 160731 18:53:38 [Note] InnoDB: Initializing buffer pool, size = 21.0G 160731 18:53:39 [Note] InnoDB: Completed initialization of buffer pool 2016-07-31 18:53:39 7f83161d9760  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. 160731 18:53:39 [ERROR] InnoDB: File /apps/dbdat/mariadb10_data3306//apps2/dbdat/ibdata3: 'create' returned OS error 71. Cannot cont inue operation 160731 18:53:39 mysqld_safe mysqld from pid file /apps/dbdat/mariadb10_data3306/mysql.pid ende

从上面看到mysql实际上是识别 /apps/dbdat/mariadb10_data3306//apps2/dbdat/ibdata3文件，由于innodb_data_home_dir=/apps/dbdat/mariadb10_data3306有设置数据文件目录，所以将设置重新改为如下： innodb_data_home_dir= innodb_data_file_path=/apps/dbdat/mariadb10_data3306/ibdata1:1704M;/apps/dbdat/mariadb10_data3306/ibdata2:1000M;/apps2/dbdat/ibdata3:100M:autoextend

---------这里注意格式，分号和冒号

查看新磁盘中下的ibdat3文件已有产生，如下：

[apps@mvxl0782 mariadb10_data3306]$ cd /apps2/dbdat

[apps@mvxl0782 dbdat]$ ls -lt

total 102404

-rw-rw---- 1 apps apps 104857600 Jul 31 19:00 ibdata3