前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >如何给MySQL共享表空间扩容

如何给MySQL共享表空间扩容

作者头像
星哥玩云
发布2022-08-16 14:23:36
2.4K0
发布2022-08-16 14:23:36
举报
文章被收录于专栏:开源部署

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

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

共享表空间:  某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档