如何給MySQL共用資料表空間擴容

如何給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

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151587.htm